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, 8.7.5
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.