[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 weeks, 4 days
[JBoss JIRA] (TEIID-4251) Built in support for Postgres DB as materialization target
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4251?page=com.atlassian.jira.plugin... ]
Work on TEIID-4251 stopped by Steven Hawkins.
---------------------------------------------
> Built in support for Postgres DB as materialization target
> ----------------------------------------------------------
>
> Key: TEIID-4251
> URL: https://issues.jboss.org/browse/TEIID-4251
> Project: Teiid
> Issue Type: Sub-task
> Components: Server
> Reporter: Ramesh Reddy
> Assignee: Steven Hawkins
> Fix For: 11.1
>
>
> If Postgres database is available along with install or assumed that it is available, then some of the materialization task can be automated, like
> - Creation of a common STATUS table
> - Creation of the materilization targets (create views on dbms)
> - On load, on undeploy and load scripts for all the materialization views
> We need to device a way this to be pluggable, such that based on success of this, we can provide additional support for other sources.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 2 months
[JBoss JIRA] (TEIID-5461) Add parsing and push down support for the frame clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5461?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5461:
---------------------------------------
With this issue I can mark Hive/Impala as having initial support for the window frame clause, but their actual support is more nuanced. Only aggregates and the first/last_value analytical functions seem to be allowed. Once we have engine support we'll have to add additional restrictions.
> Add parsing and push down support for the frame clause
> ------------------------------------------------------
>
> Key: TEIID-5461
> URL: https://issues.jboss.org/browse/TEIID-5461
> Project: Teiid
> Issue Type: Sub-task
> Components: Connector API, Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 11.2
>
>
> To make it possible to introduce these changes into older branches, we should first implement the parsing and push down logic - such that engine processing would initially fail.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 2 months
[JBoss JIRA] (TEIID-5446) Retrieving clob values from Exasol prints stacktrace to server log
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-5446?page=com.atlassian.jira.plugin... ]
Johnathon Lee updated TEIID-5446:
---------------------------------
Fix Version/s: 8.12.15.6_4
> Retrieving clob values from Exasol prints stacktrace to server log
> ------------------------------------------------------------------
>
> Key: TEIID-5446
> URL: https://issues.jboss.org/browse/TEIID-5446
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 11.1, 8.12.15.6_4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Fix For: 11.1, 8.12.15.6_4
>
>
> In Exasol, CLOB is [an alias|https://www.exasol.com/support/secure/attachment/49848/EXASOL_User_...] for VARCHAR. When querying a foreign table with a CLOB column defined as
> {code:sql}
> ObjectValue clob OPTIONS (NATIVE_TYPE 'varchar(4000)', NAMEINSOURCE 'objectvalue'))
> {code},
> an exception is logged in the server output for each row.
> The query itself finishes successfully and the returned results are correct.
> {noformat}
> 12:44:20,959 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr java.lang.Throwable
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at com.exasol.jdbc.Column.type_error(Column.java:26)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at com.exasol.jdbc.Column.getClob(Column.java:137)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at com.exasol.jdbc.EXAResultSet.getClob(EXAResultSet.java:686)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at org.jboss.jca.adapters.jdbc.WrappedResultSet.getClob(WrappedResultSet.java:1060)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at org.teiid.translator.jdbc.JDBCExecutionFactory.retrieveValue(JDBCExecutionFactory.java:1146)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:340)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:455)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:241)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at java.lang.reflect.Method.invoke(Method.java:498)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at com.sun.proxy.$Proxy36.more(Unknown Source)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:305)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> 12:44:20,960 ERROR [stderr] (Worker6_QueryProcessorQueue42) axblivLlwkmr at java.lang.Thread.run(Thread.java:748)
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 2 months
[JBoss JIRA] (TEIID-5461) Add parsing and push down support for the frame clause
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-5461:
-------------------------------------
Summary: Add parsing and push down support for the frame clause
Key: TEIID-5461
URL: https://issues.jboss.org/browse/TEIID-5461
Project: Teiid
Issue Type: Sub-task
Components: Connector API, Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 11.2
To make it possible to introduce these changes into older branches, we should first implement the parsing and push down logic - such that engine processing would initially fail.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 2 months
[JBoss JIRA] (TEIID-5414) LISTAGG support
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5414?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5414:
----------------------------------
Fix Version/s: 11.2
(was: 11.x)
> LISTAGG support
> ---------------
>
> Key: TEIID-5414
> URL: https://issues.jboss.org/browse/TEIID-5414
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Reporter: Lukáš Svačina
> Assignee: Steven Hawkins
> Fix For: 11.2
>
>
> It would be useful to have supported new SQL2016 syntax for *LISTAGG*. Currently TEIID suport only *STRING_AGG *keyword, so I have to regexp replace it in SQL syntax generated by JOOQ sql builder as it uses new *LISTAGG *keyword in default.
> Parameteres syntax should be the same in both so the easiest option is to add just alias into grammar. However LISTAGG supports also some advanced syntax which could be useful too.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 2 months