[JBoss JIRA] (TEIID-2605) Optimization substitutes wrong column in where clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2605?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-2605.
---------------------------------
> Optimization substitutes wrong column in where clause
> -----------------------------------------------------
>
> Key: TEIID-2605
> URL: https://issues.jboss.org/browse/TEIID-2605
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.1
> Environment: z/OS
> Reporter: Jeff Hayes
> Assignee: Steven Hawkins
> Attachments: query_plan.txt, views.xml
>
>
> Optimization results in a query with a different column in the WHERE clause producing an empty result set.
> Full query plan is attached but the beginning and ending queries are shown below. Note that the subject column of the IN clause is SCOPEID but optimization changes it to AUTHID for some reason.
> USER COMMAND:
> SELECT * FROM SECURITY.SCPXREF AS CHORUS_B WHERE (CHORUS_B.SYSID = 'DE29') AND ((CHORUS_B.SCOPEID IN (SELECT SN5.SCOPEID FROM SECURI
> TY.SCPNEXT AS SN5 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN4.SCOPEID FROM SECURITY.SCPNEXT AS SN4 WHERE (SYSID = 'DE29') AND
> (NEXTREC IN (SELECT SN3.SCOPEID FROM SECURITY.SCPNEXT AS SN3 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN2.SCOPEID FROM SECURI
> TY.SCPNEXT AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))))))))) OR (CHORUS_B.SCOPEID IN (SELECT SN4.SCOPEID FROM SECURIT
> Y.SCPNEXT AS SN4 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN3.SCOPEID FROM SECURITY.SCPNEXT AS SN3 WHERE (SYSID = 'DE29') AND
> (NEXTREC IN (SELECT SN2.SCOPEID FROM SECURITY.SCPNEXT AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))))))) OR (CHORUS_B.SC
> OPEID IN (SELECT SN3.SCOPEID FROM SECURITY.SCPNEXT AS SN3 WHERE (SYSID = 'DE29') AND (NEXTREC IN (SELECT SN2.SCOPEID FROM SECURITY.S
> CPNEXT AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))))) OR (CHORUS_B.SCOPEID IN (SELECT SN2.SCOPEID FROM SECURITY.SCPNEX
> T AS SN2 WHERE (SYSID = 'DE29') AND (NEXTREC = 'CHRDEPT1'))) OR (CHORUS_B.SCOPEID = 'CHRDEPT1'))
> OPTIMIZATION COMPLETE:
> PROCESSOR PLAN:
> AccessNode(10) output=[x.sysid AS sysid, x.scopeid AS authid, x.authid AS scopeid, x.authtype AS authtype] SELECT g_0.SYSID, g_0.SCO
> PEID, g_0.AUTHID, g_0.AUTHTYPE FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPXREF AS g_0 WHERE (g_0.SYSID = 'DE29') AND ((g_0.AUTHID IN
> (SELECT g_1.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_1 WHERE (g_1.SYSID = 'DE29') AND (g_1.NEXTREC IN (SELECT g_2
> .SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_2 WHERE (g_2.SYSID = 'DE29') AND (g_2.NEXTREC IN (SELECT g_3.SCOPEID FR
> OM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_3 WHERE (g_3.SYSID = 'DE29') AND (g_3.NEXTREC IN (SELECT g_4.SCOPEID FROM SECURITY
> _CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_4 WHERE (g_4.SYSID = 'DE29') AND (g_4.NEXTREC = 'CHRDEPT1'))))))))) OR (g_0.AUTHID IN (SELECT
> g_5.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_5 WHERE (g_5.SYSID = 'DE29') AND (g_5.NEXTREC IN (SELECT g_6.SCOPEI
> D FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_6 WHERE (g_6.SYSID = 'DE29') AND (g_6.NEXTREC IN (SELECT g_7.SCOPEID FROM SECU
> RITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_7 WHERE (g_7.SYSID = 'DE29') AND (g_7.NEXTREC = 'CHRDEPT1'))))))) OR (g_0.AUTHID IN (SELE
> CT g_8.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_8 WHERE (g_8.SYSID = 'DE29') AND (g_8.NEXTREC IN (SELECT g_9.SCOP
> EID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_9 WHERE (g_9.SYSID = 'DE29') AND (g_9.NEXTREC = 'CHRDEPT1'))))) OR (g_0.AUTH
> ID IN (SELECT g_10.SCOPEID FROM SECURITY_CIA_DB2_D91BPTIB_CIADB01.SCPNEXT AS g_10 WHERE (g_10.SYSID = 'DE29') AND (g_10.NEXTREC = 'C
> HRDEPT1'))) OR (g_0.AUTHID = 'CHRDEPT1'))
> The view definitions are shown below:
> <view name="SCPNEXT">
> <columns>
> <column name="sysid" type="varchar"/>
> <column name="scopeid" type="varchar"/>
> <column name="nextrec" type="varchar"/>
> </columns>
> <definition>
> #if ($db.count("select count(*) from sys.tables where Name='config' and SchemaName = 'security_db'") > 0)
> #set ($count = 0)
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> #if ($db.count("select count(*) from sys.tables where SchemaName = 'SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}'") == 0)
> #set ($count = $count + 1)
> #end
> #end
> #if ($count == 0)
> SELECT t.sysid, t.scopeid, t.nextrec
> FROM (
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> SELECT n.sysid, n.scopeid, n.nextrec
> FROM SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}.SCPNEXT n
> #if( $velocityHasNext ) UNION #end
> #end
> ) AS t
> #end
> #end
> </definition>
> </view>
> <view name="SCPXREF">
> <columns>
> <column name="sysid" type="varchar"/>
> <column name="authid" type="varchar"/>
> <column name="scopeid" type="varchar"/>
> <column name="authtype" type="varchar"/>
> </columns>
> <definition>
> #if ($db.count("select count(*) from sys.tables where Name='config' and SchemaName = 'security_db'") > 0)
> #set ($count = 0)
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> #if ($db.count("select count(*) from sys.tables where SchemaName = 'SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}'") == 0)
> #set ($count = $count + 1)
> #end
> #end
> #if ($count == 0)
> SELECT sysid, scopeid, authid, authtype
> FROM (
> #foreach($t in $db.query("select DB_TYPE, DB_LOC, DB_QUAL from security_db.config WHERE TYPE = 'CIA'"))
> SELECT x.sysid, x.scopeid, x.authid, x.authtype
> FROM SECURITY_CIA_${t.DB_TYPE}_${t.DB_LOC}_${t.DB_QUAL}.SCPXREF x
> #if( $velocityHasNext ) UNION #end
> #end
> ) AS t
> #end
> #end
> </definition>
> </view>
> I guess the best way to test this is to define these views and run the input query with SHOWPLAN=DEBUG and see if the AUTHID substitution is occurring.
> Thanks!
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
4 days, 8 hours
[JBoss JIRA] (TEIID-5184) Clob not supported in dynamic VDB
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-5184?page=com.atlassian.jira.plugin... ]
Johnathon Lee updated TEIID-5184:
---------------------------------
Fix Version/s: 8.12.14.6_4
> Clob not supported in dynamic VDB
> ---------------------------------
>
> Key: TEIID-5184
> URL: https://issues.jboss.org/browse/TEIID-5184
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Reporter: Aditi Patel
> Assignee: Steven Hawkins
> Fix For: 10.1, 9.3.6, 10.0.2, 8.12.14.6_4
>
>
> I have updated the Jconnect driver. The current version is 7.07. Still I am facing some differnt error.
>
> {color:red}07:47:01,145 INFO [org.teiid.CONNECTOR] (Worker10_async-teiid-threads10) SybaseExecutionFactory Commit=true;DatabaseProductName=Sybase IQ;DatabaseProductVersion=SAP IQ/16.1.020.528/10528/P/sp02/Sun_x64/OS 5.11/64bit/2017-07-14 13:57:25;DriverMajorVersion=7;DriverMajorVersion=0;DriverName=jConnect (TM) for JDBC (TM);DriverVersion=jConnect (TM) for JDBC(TM)/7.07 SP133 (Build 27244)/P/EBF24376/JDK 1.6.0/jdbcmain/DEBUG/Thu Mar 26 04:57:02 PDT 2015;IsolationLevel=1
> 07:47:02,529 INFO [org.teiid.RUNTIME] (Worker9_async-teiid-threads9) TEIID50030 VDB ST_VDB.1 model "SrcModel_6" metadata loaded. End Time: 12/14/17 7:47 AM
> 07:47:07,194 INFO [org.teiid.RUNTIME] (Worker8_async-teiid-threads8) TEIID50030 VDB ST_VDB.1 model "SrcModel_2" metadata loaded. End Time: 12/14/17 7:47 AM
> 07:47:58,354 INFO [org.teiid.RUNTIME] (Worker10_async-teiid-threads10) TEIID50030 VDB ST_VDB.1 model "SrcModel_4" metadata loaded. End Time: 12/14/17 7:47 AM
> 07:48:03,510 INFO [org.teiid.RUNTIME.VDBLifeCycleListener] (Worker10_async-teiid-threads10) TEIID40003 VDB ST_VDB.1 is set to ACTIVE
>
> Error Log:
> 07:52:45,561 ERROR [org.teiid.PROCESSOR] (Worker7_QueryProcessorQueue425) Settto6t5XMi TEIID30019 Unexpected exception for request Settto6t5XMi.32: java.lang.NullPointerException
> at org.teiid.core.types.ClobImpl.<init>(ClobImpl.java:116)
> at org.teiid.common.buffer.LobManager.persistLob(LobManager.java:230)
> at org.teiid.common.buffer.LobManager.updateReferences(LobManager.java:141)
> at org.teiid.common.buffer.TupleBuffer.addTupleBatch(TupleBuffer.java:203)
> at org.teiid.query.processor.BatchCollector.flushBatchDirect(BatchCollector.java:230)
> at org.teiid.dqp.internal.process.RequestWorkItem$1.flushBatchDirect(RequestWorkItem.java:686)
> at org.teiid.query.processor.BatchCollector.flushBatch(BatchCollector.java:224)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:195)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:477)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:349)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:275)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> {color}
> That looks to be a bug with the getSubString method of Sybase clobs. They are returning null when it is not expected.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 4 months
[JBoss JIRA] (TEIID-4756) Improve log messages when using an UDAF without the required classes defined in VDB
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-4756?page=com.atlassian.jira.plugin... ]
Johnathon Lee updated TEIID-4756:
---------------------------------
Fix Version/s: 8.12.14.6_4
> Improve log messages when using an UDAF without the required classes defined in VDB
> -----------------------------------------------------------------------------------
>
> Key: TEIID-4756
> URL: https://issues.jboss.org/browse/TEIID-4756
> Project: Teiid
> Issue Type: Enhancement
> Components: Server
> Affects Versions: 9.1.2
> Environment: * WildFly 10
> * Teiid 9.1.2
> * CentOs 7
> Reporter: Pedro Inácio
> Assignee: Steven Hawkins
> Fix For: 9.2, 8.12.14.6_4
>
>
> When using an _UDAF_ in a _VDB_, the required *JAVA_CLASS* and *JAVA_METHOD* are specified in the _VDB_.
> When for some reason the _JAVA_CLASS_ or _JAVA_METHOD_ do not exist in the library, the message from the system is quite imperceptible.
> In the following log excerpt, _GlobalTitleRangeCheck_ class does not exist in the _jar_ _rangecreator_. The model name that is defining the _VIRTUAL FUNCTION_ is _RangeGroupConcat_. The _VDB_ name is _CountryServiceListVDB_.
> {panel:title=Log excerpt}
> DEBUG [org.teiid.RUNTIME] (Worker53_async-teiid-threads203) TEIID50104 VDB CountryServiceListVDB.1 model "RangeGroupConcat" Using translator null and connection null to load metadata.
> ...
> INFO [org.teiid.RUNTIME] (Worker53_async-teiid-threads203) TEIID40073 The metadata for the VDB CountryServiceListVDB.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.
> INFO [org.teiid.RUNTIME.VDBLifeCycleListener] (Worker53_async-teiid-threads203) TEIID40003 VDB CountryServiceListVDB.1 is set to FAILED
> {panel}
> As observed from the log excerpt, the only possible hint to know what the problem is with the _FAILED_ deploy might be:
> _model "RangeGroupConcat" Using translator null and connection null to load metadata._
> But this is a normal logging message that doesn't have nothing to do with the real problem.
> When putting the entire system logging _ALL_ levels, then is easy to see what the problem is:
> {panel:title=Log excerpt}
> DEBUG [org.teiid.RUNTIME] (Worker52_async-teiid-threads202) TEIID50104 VDB CountryServiceListVDB.1 model "RangeGroupConcat" Using translator null and connection null to load metadata.
> INFO [org.teiid.RUNTIME] (Worker52_async-teiid-threads202) TEIID50030 VDB CountryServiceListVDB.1 model "RangeGroupConcat" metadata loaded. End Time: 10/02/17 10:56
> TRACE [org.jboss.modules] (Worker52_async-teiid-threads202) Finding class com.truphone.teiid.udaf.GlobalTitleRangeCheck from Module "deployment.countryServiceList-vdb.xml:main" from Service Module Loader
> TRACE [org.jboss.modules] (Worker52_async-teiid-threads202) Finding local class com.truphone.teiid.udaf.GlobalTitleRangeCheck from Module "mnom.rangecreator:main" from local module loader @33e5ccce (finder: local module finder @5a42bbf4 (roots: /home/mnom/teiid/teiid-9.1.2/modules,/home/mnom/teiid/teiid-9.1.2/modules/system/layers/dv,/home/mnom/teiid/teiid-9.1.2/modules/system/layers/base))
> TRACE [org.jboss.modules] (Worker52_async-teiid-threads202) Loading class com.truphone.teiid.udaf.GlobalTitleRangeCheck locally from Module "mnom.rangecreator:main" from local module loader @33e5ccce (finder: local module finder @5a42bbf4 (roots: /home/mnom/teiid/teiid-9.1.2/modules,/home/mnom/teiid/teiid-9.1.2/modules/system/layers/dv,/home/mnom/teiid/teiid-9.1.2/modules/system/layers/base))
> TRACE [org.jboss.modules] (Worker52_async-teiid-threads202) No local specification found for class com.truphone.teiid.udaf.GlobalTitleRangeCheck in Module "mnom.rangecreator:main" from local module loader @33e5ccce (finder: local module finder @5a42bbf4 (roots: /home/mnom/teiid/teiid-9.1.2/modules,/home/mnom/teiid/teiid-9.1.2/modules/system/layers/dv,/home/mnom/teiid/teiid-9.1.2/modules/system/layers/base))
> TRACE [org.jboss.modules] (Worker52_async-teiid-threads202) Class com.truphone.teiid.udaf.GlobalTitleRangeCheck not found from Module "deployment.countryServiceList-vdb.xml:main" from Service Module Loader
> INFO [org.teiid.RUNTIME] (Worker52_async-teiid-threads202) TEIID40073 The metadata for the VDB CountryServiceListVDB.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.
> INFO [org.teiid.RUNTIME.VDBLifeCycleListener] (Worker52_async-teiid-threads202) TEIID40003 VDB CountryServiceListVDB.1 is set to FAILED
> {panel}
> Here it is observable what the real problem is:
> _No local specification found for class com.truphone.teiid.udaf.GlobalTitleRangeCheck in Module "mnom.rangecreator:main"_
> So, to avoid unnecessarily losing time to investigate what the problem is, the message that is currently in *TRACE* level should be in, at least, *INFO* level.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 4 months
[JBoss JIRA] (TEIID-5355) Allow for additional partitioning columns in multisource
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-5355:
-------------------------------------
Summary: Allow for additional partitioning columns in multisource
Key: TEIID-5355
URL: https://issues.jboss.org/browse/TEIID-5355
Project: Teiid
Issue Type: Enhancement
Components: Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
In multi-source planning only the multi-source column is recognized as partitioned. It would be beneficial to allow other source columns to be seen as fully partitioned across their respective sources - even if we don't fully capture the partitioning scheme.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 4 months
[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
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-5354?page=com.atlassian.jira.plugin... ]
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)
7 years, 4 months
[JBoss JIRA] (TEIID-5351) Upgrade to WildFly 13
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5351?page=com.atlassian.jira.plugin... ]
Steven Hawkins reassigned TEIID-5351:
-------------------------------------
Fix Version/s: 11.x
(was: 11.0)
Assignee: (was: Steven Hawkins)
Evaluated for 11.0, but this is not needed for thorntail. There are also significant api changes with JGroups, so we may want to evaluate moving away from that direct integration before doing this upgrade.
> Upgrade to WildFly 13
> ---------------------
>
> Key: TEIID-5351
> URL: https://issues.jboss.org/browse/TEIID-5351
> Project: Teiid
> Issue Type: Task
> Components: Server
> Reporter: Steven Hawkins
> Fix For: 11.x
>
>
> We should move to WildFly 13, which should be at GA by the Teiid 11.0 release.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 4 months
[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
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-5354?page=com.atlassian.jira.plugin... ]
dalex dalex commented on TEIID-5354:
------------------------------------
[~shawkins] thx a lot for the quick fix, I've just checked it and these changes helped me.
> 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)
7 years, 4 months