[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
2 days, 17 hours
[JBoss JIRA] (TEIID-5168) PrestoDB translator - Convert to float not pushed correctly
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-5168?page=com.atlassian.jira.plugin... ]
Johnathon Lee updated TEIID-5168:
---------------------------------
Fix Version/s: (was: 8.12.12.6_4)
> PrestoDB translator - Convert to float not pushed correctly
> -----------------------------------------------------------
>
> Key: TEIID-5168
> URL: https://issues.jboss.org/browse/TEIID-5168
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.11.6_4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Fix For: 10.1, 10.0.2
>
>
> Running query such as
> {code:sql}
> SELECT StringKey, (convert(StringKey, float)+3) FROM BQT1.SmallA
> {code}
> Fails with the following exception:
> {noformat}
> org.teiid.translator.jdbc.JDBCExecutionException: 1 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.stringkey AS c_0, (g_0.stringkey + 3.0) AS c_1 FROM smalla AS g_0 LIMIT 100]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.11.6_4-redhat-64-7.jar:8.12.11.6_4-redhat-64-7]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:363)
> at sun.reflect.GeneratedMethodAccessor171.invoke(Unknown Source) [:1.8.0_141]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_141]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_141]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy80.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_141]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> 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:1149) [rt.jar:1.8.0_141]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_141]
> at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_141]
> Caused by: java.sql.SQLException: Query failed (#20171121_110032_00034_8zhqn): line 1:45: '+' cannot be applied to varchar(10), double
> at com.facebook.presto.jdbc.PrestoResultSet.resultsException(PrestoResultSet.java:1799)
> at com.facebook.presto.jdbc.PrestoResultSet.getColumns(PrestoResultSet.java:1747)
> at com.facebook.presto.jdbc.PrestoResultSet.<init>(PrestoResultSet.java:125)
> at com.facebook.presto.jdbc.PrestoStatement.execute(PrestoStatement.java:212)
> at com.facebook.presto.jdbc.PrestoStatement.executeQuery(PrestoStatement.java:69)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:344)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119) [translator-jdbc-8.12.11.6_4-redhat-64-7.jar:8.12.11.6_4-redhat-64-7]
> ... 17 more
> {noformat}
> The query is translated as (note missing convert)
> {code:sql}
> SELECT g_0.stringkey AS c_0, (g_0.stringkey + 3.0) AS c_1 FROM smalla AS g_0
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 6 months
[JBoss JIRA] (TEIID-4651) SybaseIQ translator: different order of arguments in LOCATE function
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-4651?page=com.atlassian.jira.plugin... ]
Johnathon Lee updated TEIID-4651:
---------------------------------
Fix Version/s: 8.12.13.6_4
> SybaseIQ translator: different order of arguments in LOCATE function
> --------------------------------------------------------------------
>
> Key: TEIID-4651
> URL: https://issues.jboss.org/browse/TEIID-4651
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.8.6_3
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 9.1.2, 9.2, 8.12.11.6_4, 8.12.13.6_4
>
>
> Teiid's and SAP IQ's LOCATE function differs in order of arguments. Translator is pushing the LOCATE function as is into the source, which results in undesirable results. This is critical issue, as the queries seemingly work, but are not producing correct results.
> See [SAP IQ LOCATE function docs|http://help.sap.com/saphelp_iq1611_iqrefbb/helpdata/en/a5/5fae8484f2...]
> Query:
> {code:sql}
> SELECT INTKEY, STRINGNUM, LOCATE(1,STRINGNUM) FROM BQT1.SmallA ORDER BY intkey
> {code}
> being pushed as:
> {code:sql}
> [SELECT g_0."intkey" AS c_0, g_0."stringnum" AS c_1, locate('1', g_0."stringnum") AS c_2 FROM "bqt-server"."dvqe"."SmallA" AS g_0 ORDER BY c_0]
> {code}
> Results in:
> || intkey || stringnum || expr3 ||
> |0 | -24 | 0|
> |1 | <null> | <null>|
> |2 | -22 | 0|
> |3 | -21 | 0|
> |4 | -20 | 0|
> |5 | -19 | 0|
> |6 | -18 | 0|
> |7 | -17 | 0|
> |8 | -16 | 0|
> |9 | -15 | 0|
> |10 | -14 | 0|
> |11 | -13 | 0|
> |12 | -12 | 0|
> |13 | -11 | 0|
> |14 | -10 | 0|
> |15 | -9 | 0|
> |16 | -8 | 0|
> |17 | <null> | <null>|
> |18 | -6 | 0|
> |19 | -5 | 0|
> |20 | -4 | 0|
> |21 | -3 | 0|
> |22 | -2 | 0|
> |23 | -1 | 0|
> |24 | 0 | 0|
> |25 | 1 | 1|
> |26 | 2 | 0|
> |27 | 3 | 0|
> |28 | 4 | 0|
> |29 | 5 | 0|
> |30 | 6 | 0|
> |31 | 7 | 0|
> |32 | 8 | 0|
> |33 | <null> | <null>|
> |34 | 10 | 0|
> |35 | 11 | 0|
> |36 | 12 | 0|
> |37 | 13 | 0|
> |38 | 14 | 0|
> |39 | 15 | 0|
> |40 | 16 | 0|
> |41 | 17 | 0|
> |42 | 18 | 0|
> |43 | 19 | 0|
> |44 | 20 | 0|
> |45 | 21 | 0|
> |46 | 22 | 0|
> |47 | 23 | 0|
> |48 | 24 | 0|
> |49 | <null> | <null> |
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 6 months
[JBoss JIRA] (TEIID-5301) With MongoDB, a query with subquery in HAVING clause doesn't return any results
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-5301?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration updated TEIID-5301:
-------------------------------------------
Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1562182
Bugzilla Update: Perform
> With MongoDB, a query with subquery in HAVING clause doesn't return any results
> -------------------------------------------------------------------------------
>
> Key: TEIID-5301
> URL: https://issues.jboss.org/browse/TEIID-5301
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.12.6_4
> Reporter: Jan Martiska
> Assignee: Steven Hawkins
> Fix For: 10.2, 10.3, 10.1.3, 10.0.5
>
>
> Example:
> {noformat}
> SELECT INTKEY FROM BQT1.SMALLA GROUP BY INTKEY HAVING INTKEY = (SELECT INTKEY FROM BQT1.SMALLA WHERE INTKEY = 20)
> {noformat}
> This will not return any results even if there are rows with INTKEY=20.
> If I use a value instead of subquery, it works as expected, this will return a result:
> {noformat}
> SELECT INTKEY FROM BQT1.SMALLA GROUP BY INTKEY HAVING INTKEY = 20;
> {noformat}
> Furthermore, if the subquery returns null, the whole query fails with a NPE:
> {noformat}
> SELECT INTKEY FROM BQT1.SMALLA GROUP BY INTKEY HAVING INTKEY = (SELECT null);
> 13:43:50,250 ERROR [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) Connector worker process failed for atomic-request=mo0zm9PiOEre.0.3.0: java.lang.NullPointerException
> at java.util.concurrent.ConcurrentHashMap.get(ConcurrentHashMap.java:936) [rt.jar:1.8.0_151]
> at org.teiid.translator.mongodb.MongoDBSelectVisitor.getExpressionAlias(MongoDBSelectVisitor.java:808) [translator-mongodb-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
> at org.teiid.translator.mongodb.MongoDBSelectVisitor.visit(MongoDBSelectVisitor.java:641) [translator-mongodb-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
> at org.teiid.language.Comparison.acceptVisitor(Comparison.java:110) [teiid-api-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
> at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51) [teiid-api-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
> at org.teiid.translator.mongodb.MongoDBSelectVisitor.append(MongoDBSelectVisitor.java:81) [translator-mongodb-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
> at org.teiid.translator.mongodb.MongoDBSelectVisitor.visit(MongoDBSelectVisitor.java:574) [translator-mongodb-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
> at org.teiid.language.Select.acceptVisitor(Select.java:110) [teiid-api-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
> at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51) [teiid-api-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
> at org.teiid.translator.mongodb.MongoDBQueryExecution.execute(MongoDBQueryExecution.java:60) [translator-mongodb-8.12.11.6_4-redhat-64-12.jar:8.12.11.6_4-redhat-64-12]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:361)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_151]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_151]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_151]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_151]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy79.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_151]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> 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:1149) [rt.jar:1.8.0_151]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_151]
> at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_151]
> 13:43:50,257 WARN [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2) TEIID30020 Processing exception for request mo0zm9PiOEre.0 'TEIID30504 local: null'. Originally TeiidProcessingException ConcurrentHashMap.java:936. Enable more detailed logging to see the entire stacktrace.
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 6 months
[JBoss JIRA] (TEIID-5303) MongoDB translator issues with aggregate function expressions
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5303?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5303:
----------------------------------
Component/s: Misc. Connectors
Fix Version/s: 10.3
Affects Version/s: 8.7
> MongoDB translator issues with aggregate function expressions
> -------------------------------------------------------------
>
> Key: TEIID-5303
> URL: https://issues.jboss.org/browse/TEIID-5303
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.7
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 10.3
>
>
> MongoDB queries involing expressions with aggregate functions in the select or having clauses will fail. It appears that another projection phase is needed to handling those computations as they are not valid in the group operation.
> For example, these result in exceptions:
> SELECT min(e1)+1, e2 FROM TIME_TEST GROUP BY e2
> SELECT min(e1), e2 FROM TIME_TEST GROUP BY e2 HAVING year(e2) + count(\*) = 2
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 6 months
[JBoss JIRA] (TEIID-5300) ClassCastException during query Optimization
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5300?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5300:
----------------------------------
Component/s: Query Engine
Fix Version/s: 10.3
Affects Version/s: 10.1
> ClassCastException during query Optimization
> --------------------------------------------
>
> Key: TEIID-5300
> URL: https://issues.jboss.org/browse/TEIID-5300
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 10.1
> Environment: Teiid 10.1
> Reporter: Mike Higgins
> Assignee: Steven Hawkins
> Fix For: 10.3
>
>
> Using Teiid 10.1 against an Oracle database, if I submit a query with exactly two bind variables such as the following:
>
> SELECT "Small Molecules"."MOLREGNO", "Small Molecules"."CHEMBL_ID",
> "Small Molecules_2"."FULL_MWT", CHEMBL_20.ACTIVITIES."ACTIVITY_ID",
> CHEMBL_20.ACTIVITIES."ACTIVITY_ID", ((CHEMBL_20.ACTIVITIES."ASSAY_ID" ||
> '#') || CHEMBL_20.ACTIVITIES."STANDARD_TYPE") "ID_RESULT",
> CHEMBL_20.ACTIVITIES."STANDARD_UNITS",
> CHEMBL_20.ACTIVITIES."ACTIVITY_COMMENT",
> CHEMBL_20.ACTIVITIES."STANDARD_VALUE"
> FROM (SELECT CHEMBL_20.COMPOUND_STRUCTURES."MOLREGNO",
> SM_DICTIONARY_PRIME."CHEMBL_ID" FROM chembl_20.compound_structures INNER
> JOIN chembl_20.molecule_dictionary SM_DICTIONARY_PRIME ON (
> CHEMBL_20.COMPOUND_STRUCTURES."MOLREGNO" = SM_DICTIONARY_PRIME."MOLREGNO")
> UNION ALL SELECT CHEMBL_20.SM_REPOSITORY."MOLREGNO",
> CHEMBL_20.SM_REPOSITORY."CHEMBL_ID" FROM chembl_20.sm_repository)
> "Small Molecules" LEFT OUTER JOIN chembl_20.compound_properties
> "Small Molecules_2" ON ("Small Molecules"."MOLREGNO" =
> "Small Molecules_2"."MOLREGNO") INNER JOIN chembl_20.activities ON ((
> "Small Molecules"."MOLREGNO" = CHEMBL_20.ACTIVITIES."MOLREGNO") AND ((
> CHEMBL_20.ACTIVITIES."STANDARD_TYPE" = 'IC50') AND (
> CHEMBL_20.ACTIVITIES."ASSAY_ID" IN ('654926', '654933'))))
> WHERE ("Small Molecules"."MOLREGNO" IN (?, ?)
>
> I get the following traceback, indicating that a class that does not implement Comparable is being stored in a TreeMap:
> [exec] 23 Mar 2018 15:04:28,282 [Worker67_QueryProcessorQueue3267] ERROR PROCESSOR- TEIID30019 Unexpected exception for request RkVKlFhEnEgf.0: java.lang.ClassCastException: org.teiid.query.sql.symbol.Reference cannot be cast to java.lang.Comparable
> [exec] at java.util.TreeMap.getEntry(TreeMap.java:349)
> [exec] at java.util.TreeMap.containsKey(TreeMap.java:232)
> [exec] at java.util.TreeSet.contains(TreeSet.java:234)
> [exec] at java.util.AbstractCollection.containsAll(AbstractCollection.java:318)
> [exec] at org.teiid.query.sql.lang.SetCriteria.equals(SetCriteria.java:136)
> [exec] at org.teiid.query.optimizer.relational.rules.RuleCopyCriteria.copyCriteria(RuleCopyCriteria.java:169)
> [exec] at org.teiid.query.optimizer.relational.rules.RuleCopyCriteria.createCriteria(RuleCopyCriteria.java:341)
> [exec] at org.teiid.query.optimizer.relational.rules.RuleCopyCriteria.tryToCopy(RuleCopyCriteria.java:268)
> [exec] at org.teiid.query.optimizer.relational.rules.RuleCopyCriteria.visitChildern(RuleCopyCriteria.java:384)
> [exec] at org.teiid.query.optimizer.relational.rules.RuleCopyCriteria.tryToCopy(RuleCopyCriteria.java:294)
> [exec] at org.teiid.query.optimizer.relational.rules.RuleCopyCriteria.visitChildern(RuleCopyCriteria.java:384)
> [exec] at org.teiid.query.optimizer.relational.rules.RuleCopyCriteria.tryToCopy(RuleCopyCriteria.java:294)
> [exec] at org.teiid.query.optimizer.relational.rules.RuleCopyCriteria.execute(RuleCopyCriteria.java:99)
> [exec] at org.teiid.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:995)
> [exec] at org.teiid.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:228)
> [exec] at org.teiid.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:179)
> [exec] at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:458)
> [exec] at org.teiid.dqp.internal.process.PreparedStatementRequest.generatePlan(PreparedStatementRequest.java:124)
> [exec] at org.teiid.dqp.internal.process.Request.processRequest(Request.java:486)
> [exec] at org.teiid.dqp.internal.process.PreparedStatementRequest.processRequest(PreparedStatementRequest.java:345)
> [exec] at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:660)
> [exec] at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:339)
> [exec] at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
> [exec] at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:276)
> [exec] at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:280)
> [exec] at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> [exec] at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> [exec] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> [exec] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> [exec] at java.lang.Thread.run(Thread.java:745)
> [exec]
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 6 months
[JBoss JIRA] (TEIID-5295) Comparisons of time values don't work correctly for MongoDB
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5295?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5295.
-----------------------------------
Resolution: Won't Fix
Marking as won't fix.
> Comparisons of time values don't work correctly for MongoDB
> -----------------------------------------------------------
>
> Key: TEIID-5295
> URL: https://issues.jboss.org/browse/TEIID-5295
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.11.6_4
> Reporter: Jan Martiska
> Assignee: Steven Hawkins
>
> Examples of queries which don't behave as expected when running against MongoDB:
> {noformat}
> SELECT BQT1.SmallA.TimeValue FROM BQT1.SmallA WHERE BQT1.SmallA.TimeValue > '17:00:00'
> {noformat}
> returns ALL timevalues which are not null
> {noformat}
> SELECT BQT1.SmallA.TimeValue FROM BQT1.SmallA WHERE BQT1.SmallA.TimeValue < '17:00:00'
> {noformat}
> returns NO timevalues even though there are some less than 17:00
> {noformat}
> SELECT BQT1.SmallA.TimeValue FROM BQT1.SmallA WHERE BQT1.SmallA.TimeValue = '15:00:00'
> {noformat}
> returns nothing
> {noformat}
> SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA WHERE BQT1.SmallA.TimeValue IN (convert('05:00:00', time), convert('15:00:00', time))
> {noformat}
> returns nothing
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 6 months
[JBoss JIRA] (TEIID-5297) With MongoDB, null is returned from timestamp functions if the same function is part of WHERE clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5297?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5297.
-----------------------------------
Fix Version/s: 10.3
10.1.3
10.0.5
10.2.1
Resolution: Done
Updated the logic to reuse the implicit alias of functions.
> With MongoDB, null is returned from timestamp functions if the same function is part of WHERE clause
> ----------------------------------------------------------------------------------------------------
>
> Key: TEIID-5297
> URL: https://issues.jboss.org/browse/TEIID-5297
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.11.6_4
> Reporter: Jan Martiska
> Assignee: Steven Hawkins
> Fix For: 10.3, 10.1.3, 10.0.5, 10.2.1
>
>
> For example:
> {noformat}
> SELECT timestampvalue, second(timestampvalue) as sec FROM bqt1.hugeA WHERE second(timestampvalue) >= 0
> {noformat}
> This will correctly select rows where {{second(timestampvalue) >= 0}}, but the {{sec}} column in the result will contain only nulls.
> If I remove the WHERE clause:
> {noformat}
> SELECT timestampvalue, second(timestampvalue) as sec FROM bqt1.hugeA;
> {noformat}
> Then the {{sec}} column will be computed correctly.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 6 months
[JBoss JIRA] (TEIID-5296) With MongoDB, timestamp operations throw exceptions when called on null or missing values
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5296?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5296.
-----------------------------------
Fix Version/s: 10.3
10.1.3
10.0.5
10.2.1
Resolution: Done
Updated the translator to use the $cond approach to handle null/missing.
> With MongoDB, timestamp operations throw exceptions when called on null or missing values
> -----------------------------------------------------------------------------------------
>
> Key: TEIID-5296
> URL: https://issues.jboss.org/browse/TEIID-5296
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.11.6_4
> Reporter: Jan Martiska
> Assignee: Steven Hawkins
> Fix For: 10.3, 10.1.3, 10.0.5, 10.2.1
>
>
> When a query contains timestamp operations like {{DAYOFMONTH}}, {{DAYOFWEEK}},.. and these are executed when some cells contain null (or missing field), MongoDB throws an exception. This exception is not handled by Teiid in any way and will fail the whole VDB query:
> {noformat}
> Remote com.mongodb.CommandFailureException:
> { "serverUsed" : "localhost:27017" ,
> "ok" : 0.0 , "errmsg" : "can't convert from BSON type null to Date" ,
> "code" : 16006 , "
> codeName" : "Location16006"}
> {noformat}
> Perhaps Teiid could work around this somehow so that the VDB query will not fail and affected cells will contain null in the result?
> For example, this Mongo aggregation pipeline which extracts hours from timestamps:
> {noformat}
> db.collection.aggregate([
> {
> $project : {
> hour: {$hour: "$DATEVALUE"}
> }
> }
> ])
> {noformat}
> could be transformed to this:
> {noformat}
> db.collection.aggregate([
> {
> $project : {
> hour: { $cond:
> [ { $or: [
> { $eq: [ "$DATEVALUE", null ] },
> { $eq: [ { $type: "$DATEVALUE" }, "missing" ] }
> ]
> },
> null,
> { $hour: "$DATEVALUE" }
> ]
> }
> }
> }
> ])
> {noformat}
> after this transformation, the {{hour}} field will be null as expected for documents where {{DATEVALUE}} is null or missing completely
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 6 months