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

    Details

      Description

      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.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                fox123 Salvatore R
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: