[teiid-issues] [JBoss JIRA] (TEIID-5550) DISTINCT pushed down incorrectly in some cases

[Steven Hawkins (Jira)]

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

Steven Hawkins updated TEIID-5550:
----------------------------------
    Description: 
The parent DISTINCT is being removed over joins in particular situations.  This is a regression from TEIID-3063 TEIID-3062.

Conditions:
- basic query structure is select distinct col from a join b on a.col2 = b.col2
- a sort merge join variant must have been planned
- the sort must not be pushed to one of the children
- the projection related conditions of TEIID-3062 / TEIID-3063 must be met
- there is a one to many relationship

The result is that the parent distinct will be inappropriately removed, such that the output of the join can contain repetition of the col value when it should be distinct.

  was:
DISTINCT is pushing down to the wrong side of a JOIN (applies towards a different column than the user query).  Adding an ORDER BY or GROUP BY to the query results in the query engine performing the dupremoval rather than pushing it down to a source.   Should either not push the distinct down when performing federated join or push distinct down to the source that is relevant to the end user query.

Example:
SELECT DISTINCT nameValue FROM table1
Returns multiple rows as DISTINCT is pushed down (and to the source unrelated to nameValue)

SELECT DISTINCT nameValue FROM table1 GROUP BY nameValue 
SELECT DISTINCT nameValue FROM table1 ORDER BY nameValue
Returns expected results of 1 row in test



> DISTINCT pushed down incorrectly in some cases
> ----------------------------------------------
>
>                 Key: TEIID-5550
>                 URL: https://issues.jboss.org/browse/TEIID-5550
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 8.12.15.6_4
>         Environment: JDV 6.4.5
>            Reporter: Marc Shirley
>            Assignee: Steven Hawkins
>            Priority: Major
>
> The parent DISTINCT is being removed over joins in particular situations.  This is a regression from TEIID-3063 TEIID-3062.
> Conditions:
> - basic query structure is select distinct col from a join b on a.col2 = b.col2
> - a sort merge join variant must have been planned
> - the sort must not be pushed to one of the children
> - the projection related conditions of TEIID-3062 / TEIID-3063 must be met
> - there is a one to many relationship
> The result is that the parent distinct will be inappropriately removed, such that the output of the join can contain repetition of the col value when it should be distinct.



--
This message was sent by Atlassian Jira
(v7.12.1#712002)