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

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-3696?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-3696.
-----------------------------------
    Resolution: Done


Thanks Salvatore for highlighting this so well.  Addressed by expanding the check to ensure that the grouping is over all the projected columns from the union.

> 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
>          Components: Query Engine
>    Affects Versions: 8.1
>            Reporter: Salvatore R
>            Assignee: Steven Hawkins
>            Priority: Blocker
>             Fix For: 8.12, 8.11.4
>
>
> 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)