[teiid-issues] [JBoss JIRA] (TEIID-3696) Duplicates are not always removed when UNION and GROUP BY clauses are used in a subquery

[Salvatore R (JIRA)]

Salvatore R created TEIID-3696:
----------------------------------

             Summary: Duplicates are not always removed when UNION and GROUP BY clauses are used in a subquery
                 Key: TEIID-3696
                 URL: https://issues.jboss.org/browse/TEIID-3696
             Project: Teiid
          Issue Type: Bug
            Reporter: Salvatore R
            Assignee: Steven Hawkins


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:
{code:sql}
CREATE view v1 as 
select 'a' as col1
UNION
SELECT '' as col1;

CREATE view v2 as 
select 'b' as col1
UNION
SELECT '' as col1;
{code}

running the following query (both col1 and col2 are projected by the main query):
{code:sql}
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
{code}

only 1 row is returned as expected:
||col2||col1||
|1| |

but if only "col2" is projected by the main query:
{code:sql}
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
{code}
three rows are returned:
||col2||
|1|
|1|
|1|

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



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)