Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-3696

Duplicates are not always removed when UNION and GROUP BY clauses are used in a subquery

XMLWordPrintable

      In some cases, duplicates are not correctly removed when a UNION clause and a GROUP BY are used in a subquery.

      Given, for example, these two views:

      CREATE view v1 as 
select 'a' as col1
UNION
SELECT '' as col1;

CREATE view v2 as 
select 'b' as col1
UNION
SELECT '' as col1;

      running the following query (both col1 and col2 are projected by the main query):

      select 
    y.col2, y.col1   
from (
	select x.col2, min(x.col1) as col1
	from (
		select 1 as col2, col1 from "views.v1" 
		union
		select 1 as col2, col1 from "views.v2"  
	) x 
	group by x.col2
) y

      only 1 row is returned as expected:

      col2 col1
      1  

      but if only "col2" is projected by the main query:

      select 
    y.col2 
from (
	select x.col2, min(x.col1) as col1
	from (
		select 1 as col2, col1 from "views.v1" 
		union
		select 1 as col2, col1 from "views.v2"  
	) x 
	group by x.col2
) y

      three rows are returned:

      col2
      1
      1
      1

      This behavior can be reproduced in Teiid-8.12-Beta1.

              rhn-engineering-shawkins Steven Hawkins
              redfox999 Salvatore R.
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: