[teiid-issues] [JBoss JIRA] (TEIID-5354) Different results for a query with GROUP BY and SUM(1) (summation of the number 1) when there is a LEFT or INNER JOIN involved

[RH Bugzilla Integration (JIRA)]

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

RH Bugzilla Integration updated TEIID-5354:
-------------------------------------------
    Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1584391
        Bugzilla Update: Perform


> Different results for a query with GROUP BY and SUM(1) (summation of the number 1) when there is a LEFT or INNER JOIN involved
> ------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-5354
>                 URL: https://issues.jboss.org/browse/TEIID-5354
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 10.2
>         Environment: teiid-10.2.0 on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
>            Reporter: dalex dalex
>            Assignee: Steven Hawkins
>            Priority: Blocker
>             Fix For: 11.0, 10.3.2, 10.2.3
>
>         Attachments: address_pg.sql, stateprovince_mysql.sql
>
>
> Teiid returns different results for a query with GROUP BY and SUM(1) (summation of the number 1) when there is a LEFT or INNER JOIN involved.
> There are two problems: a) incorrect check in MergeJoinStrategy.compareToPrevious method which generates TEIID31202 exception and b) a bug in algorithm of join itself which leads to incorrect results.
> To reproduce the bug, please, run the following queries and compare theSum and theCount column values:
> {code:sql}
> select  r.city,
> 		sum(1) as theSum
> 		,count(*) as theCount
> from "dsp.address_pg" r
> 	left join "adventureworks.stateprovince" c on "r.stateprovinceid" = "c.stateprovinceid" 
> group by r.city
> order by r.city ;;
> {code}
> {code:sql}
> select  r.city,
> 		sum(1) as theSum
> 		,count(*) as theCount
> from "dsp.address_pg" r
> 	inner join "adventureworks.stateprovince" c on "r.stateprovinceid" = "c.stateprovinceid" 
> group by r.city
> order by r.city ;;
> {code}
> but the following queries return correct results:
> {code:sql}
> select  r.city,
> 		sum(1) as theSum
> 		,count(*) as theCount
> from "dsp.address_pg" r
> 	right join "adventureworks.stateprovince" c on "r.stateprovinceid" = "c.stateprovinceid" 
> group by r.city
> order by r.city ;;
> {code}
> the second one is also correct (LEFT JOIN) but uses window function:
> {code:sql}
> select  distinct city,
> 		sum(1) OVER (PARTITION BY city) as theSum
> 		,count(*) OVER (PARTITION BY city) as theCount
> from "dsp.address_pg" r
> 	left join "adventureworks.stateprovince" c on "r.stateprovinceid" = "c.stateprovinceid" 
> order by r.city ;;
> {code}



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)