[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
1 day, 15 hours
[JBoss JIRA] (TEIID-4422) Impala Translator - add pushdown support for concat and concat_ws functions
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4422?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4422:
---------------------------------------
Our concat system function does currently push down to impala, but does not expect varargs - are you requesting that the Teiid system function supports varargs?
Similarly are you requesting that a concat_ws system function be added? Or is it sufficient to just add concat_ws to the pushdown functions exposed by the translator?
For group_concat, we can map that to the pushdown of the Teiid string_agg function.
> Impala Translator - add pushdown support for concat and concat_ws functions
> ---------------------------------------------------------------------------
>
> Key: TEIID-4422
> URL: https://issues.jboss.org/browse/TEIID-4422
> Project: Teiid
> Issue Type: Feature Request
> Components: JDBC Connector
> Affects Versions: 9.0.2
> Reporter: Don Krapohl
> Assignee: Steven Hawkins
>
> As a consumer I need the concat and concat_ws function definitions with matching signatures in line with http://www.cloudera.com/documentation/archive/impala/2-x/2-0-x/topics/imp...
> concat:
> concat(string a, string b...)
> Purpose: Returns a single string representing all the argument values joined together.
> Return type: string
> Usage notes: concat() and concat_ws() are appropriate for concatenating the values of multiple columns within the same row, while group_concat() joins together values from different rows.
> concat_ws:
> concat_ws(string sep, string a, string b...)
> Purpose: Returns a single string representing the second and following argument values joined together, delimited by a specified separator.
> Return type: string
> Usage notes: concat() and concat_ws() are appropriate for concatenating the values of multiple columns within the same row, while group_concat() joins together values from different rows.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 1 month
[JBoss JIRA] (TEIID-4422) Impala Translator - add pushdown support for concat and concat_ws functions
by Don Krapohl (JIRA)
Don Krapohl created TEIID-4422:
----------------------------------
Summary: Impala Translator - add pushdown support for concat and concat_ws functions
Key: TEIID-4422
URL: https://issues.jboss.org/browse/TEIID-4422
Project: Teiid
Issue Type: Feature Request
Components: JDBC Connector
Affects Versions: 9.0.2
Reporter: Don Krapohl
Assignee: Steven Hawkins
As a consumer I need the concat and concat_ws function definitions with matching signatures in line with http://www.cloudera.com/documentation/archive/impala/2-x/2-0-x/topics/imp...
concat:
concat(string a, string b...)
Purpose: Returns a single string representing all the argument values joined together.
Return type: string
Usage notes: concat() and concat_ws() are appropriate for concatenating the values of multiple columns within the same row, while group_concat() joins together values from different rows.
concat_ws:
concat_ws(string sep, string a, string b...)
Purpose: Returns a single string representing the second and following argument values joined together, delimited by a specified separator.
Return type: string
Usage notes: concat() and concat_ws() are appropriate for concatenating the values of multiple columns within the same row, while group_concat() joins together values from different rows.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 1 month
[JBoss JIRA] (TEIID-4418) Pushdown of a correlated with clause contains wrong correlated reference
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4418?page=com.atlassian.jira.plugin... ]
Work on TEIID-4418 started by Steven Hawkins.
---------------------------------------------
> Pushdown of a correlated with clause contains wrong correlated reference
> ------------------------------------------------------------------------
>
> Key: TEIID-4418
> URL: https://issues.jboss.org/browse/TEIID-4418
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 9.1
>
>
> A query such as:
> {code}
> select e1, e2 from pm1.g2 where e1 = (with CTE1 as /*+ no_inline */ (SELECT pm1.g2.e1 from pm1.g1) select e1 from CTE1)
> {code}
> will pushdown as:
> {code}
> SELECT g_0.e1, g_0.e2 FROM g2 AS g_0 WHERE g_0.e1 = (WITH CTE1 (e1) AS (SELECT g2.e1 AS expr1 FROM g1 AS g_0) SELECT g_1.e1 FROM CTE1 AS g_1)
> {code}
> Note the incorrect g2.e1 reference.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 1 month
[JBoss JIRA] (TEIID-4021) MSSQL Pagination
by Mark Tawk (JIRA)
[ https://issues.jboss.org/browse/TEIID-4021?page=com.atlassian.jira.plugin... ]
Mark Tawk commented on TEIID-4021:
----------------------------------
The workaround you suggested is better then assigning the database version property on the executionfactory to the lowest version.
New feature added : [Teiid Translator per source/version|https://issues.jboss.org/browse/TEIID-4421]
> MSSQL Pagination
> ----------------
>
> Key: TEIID-4021
> URL: https://issues.jboss.org/browse/TEIID-4021
> Project: Teiid
> Issue Type: Enhancement
> Components: JDBC Connector
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Fix For: 9.1
>
>
> I'm using Teiid 8.11.3 with mssql translator.
> I have a huge table over sql server 2008 on which i'm applying pagination.
> I noticed slowliness in the query execution over this table at each time I increase the pagination.
> I monitored the JDBC queries execute by teiid and found out that it is using top n according to the limit used into the query.
> For example:
> in teiid : Select * from mytable LIMIT 90 , 10
> is translated in JDBC: select top 100 from mytable
> Since my table contains millions of records, when fetching the last page, the executed jdbc query is retrieving the whole table top n, to return in result the last 10 records. And the execution is taking too much time.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 1 month
[JBoss JIRA] (TEIID-4421) Teiid Translator per source/version
by Mark Tawk (JIRA)
Mark Tawk created TEIID-4421:
--------------------------------
Summary: Teiid Translator per source/version
Key: TEIID-4421
URL: https://issues.jboss.org/browse/TEIID-4421
Project: Teiid
Issue Type: Feature Request
Reporter: Mark Tawk
Assignee: Steven Hawkins
Teiid actually uses a single translator to connect to a single source/version. However in the case of translators that auto-configure to the given version it is expected to manage creating new instances when necessary.
Case reproduced when connecting to 2 DBs, one on SQL Server 2012 and another on SQL Server 2008. The pagination SQL translation of SQL Server 2012 does not work on 2008.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 1 month
[JBoss JIRA] (TEIID-3795) Order of VARIADIC parameters is not preserved
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-3795?page=com.atlassian.jira.plugin... ]
Johnathon Lee updated TEIID-3795:
---------------------------------
Fix Version/s: 8.7.9.6_2
> Order of VARIADIC parameters is not preserved
> ---------------------------------------------
>
> Key: TEIID-3795
> URL: https://issues.jboss.org/browse/TEIID-3795
> Project: Teiid
> Issue Type: Bug
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.12.2, 8.13, 8.7.9.6_2
>
>
> It seems that the order of VARIADIC arguments passed to a stored procedure is not always preserved.
> For example, I defined a procedure in a virtual model in the VDB as follows:
> {code:sql}
> <model visible = "true" type = "VIRTUAL" name = "test_variadic">
> <metadata type = "DDL"><![CDATA[
> CREATE PROCEDURE p1(VARIADIC parameters string)
> AS BEGIN
> declare integer i = 1;
> WHILE(i <= array_length(parameters))
> BEGIN
> exec "SYSADMIN.logMsg"("level" => 'INFO', "context" => 'test', "msg" => 'param '|| parameters[i]);
> i = i + 1;
> END
> END;
> ]]>
> </metadata>
> </model>
> {code}
> The procedure just prints to the console the passed arguments.
> When I call the procedure:
> {code:sql}
> exec "test_variadic.p1"('1' , '2', '3' , '4', '5' , '6', '7' , '8', '9' , '10', '11' , '12', '13' , '14', '15' , '16', '17' , '18', '19' , '20')
> {code}
> only the first 15 parameters are printed in the same order as they are passed, as shown in the log below:
> {code}
> 22:44:26,117 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 1
> 22:44:26,118 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 2
> 22:44:26,119 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 3
> 22:44:26,122 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 4
> 22:44:26,123 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 5
> 22:44:26,125 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 6
> 22:44:26,127 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 7
> 22:44:26,130 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 8
> 22:44:26,132 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 9
> 22:44:26,134 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 10
> 22:44:26,138 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 11
> 22:44:26,138 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 12
> 22:44:26,140 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 13
> 22:44:26,143 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 14
> 22:44:26,144 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 15
> 22:44:26,147 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 17
> 22:44:26,149 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 16
> 22:44:26,151 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 19
> 22:44:26,153 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 18
> 22:44:26,156 INFO [test] (Worker2_QueryProcessorQueue21) AbdPCQQAsiSL param 20
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 1 month