[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

[Jan Stastny (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-5354?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13589651#comment-13589651 ] 

Jan Stastny commented on TEIID-5354:
------------------------------------

Hello [~shawkins], 
> b) a bug in algorithm of join itself which leads to incorrect results.
what was the issue about? with the example query:
{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}
were there different values for sum(1) and count\(*\) showing?

> 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, 8.12.14.6_4
>
>         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)