November 2016 - teiid-issues - Jboss List Archives

[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 months

2
1
0 / 0

[JBoss JIRA] (TEIID-4610) Impala regexp_replace function colliding with prepared values

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4610?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-4610: --------------------------------------- I don't quite follow what is going on here. In the user query you have concat('&quot',char(59)), which will be replaced with the literal value '"'. In the source query you are showing ''' - but there is no where in our logic where the xml escape value would be changed into a the replacement character. Can you confirm this is your exact user query and/or provide a log? > Impala regexp_replace function colliding with prepared values > ------------------------------------------------------------- > > Key: TEIID-4610 > URL: https://issues.jboss.org/browse/TEIID-4610 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.13.3 > Reporter: Scott Wallace > Assignee: Steven Hawkins > > Impala regexp_replace function appears to collide with prepared values only when attemping to replace apostrophe. > Error: > {{org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30504 warehouse: 500051 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: ['2016-01-01 00:00:00.0'] SQL: SELECT regexp_replace(g_0.`string_column`, '\'', ''') FROM some_impala_table g_0 WHERE g_0.`date_key` = ? AND g_0.`customer_id` = 112]}} -- This message was sent by Atlassian JIRA (v7.2.3#72005)

9 years

1
0
0 / 0

[JBoss JIRA] (TEIID-4596) Teiid does not return genereted keys after executeBatch

by Ramesh Reddy (JIRA)

[ https://issues.jboss.org/browse/TEIID-4596?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-4596: ------------------------------------- [~jdurani] can you attach your test please? > Teiid does not return genereted keys after executeBatch > ------------------------------------------------------- > > Key: TEIID-4596 > URL: https://issues.jboss.org/browse/TEIID-4596 > Project: Teiid > Issue Type: Enhancement > Components: Documentation > Reporter: Juraj Duráni > Assignee: Ramesh Reddy > Priority: Minor > > Currently, Teiid does not return genereted keys after inserting value using PreparedStatement.executeBatch() method. JDBC ([4.1|http://download.oracle.com/otndocs/jcp/jdbc-4_1-mrel-spec/index.html]) does not require this : > {quote} > It is implementation-defined as to whether _getGeneratedKeys_ will return generated values after invoking the _executeBatch_ method. > {quote} > However, it would be nice to have this behavior documented - https://teiid.gitbooks.io/documents/content/client-dev/JDBC_Support.html -- This message was sent by Atlassian JIRA (v7.2.3#72005)

9 years

1
0
0 / 0

[JBoss JIRA] (TEIID-4596) Teiid does not return genereted keys after executeBatch

by Ramesh Reddy (JIRA)

[ https://issues.jboss.org/browse/TEIID-4596?page=com.atlassian.jira.plugin... ] Ramesh Reddy reassigned TEIID-4596: ----------------------------------- Assignee: Ramesh Reddy (was: Steven Hawkins) > Teiid does not return genereted keys after executeBatch > ------------------------------------------------------- > > Key: TEIID-4596 > URL: https://issues.jboss.org/browse/TEIID-4596 > Project: Teiid > Issue Type: Enhancement > Components: Documentation > Reporter: Juraj Duráni > Assignee: Ramesh Reddy > Priority: Minor > > Currently, Teiid does not return genereted keys after inserting value using PreparedStatement.executeBatch() method. JDBC ([4.1|http://download.oracle.com/otndocs/jcp/jdbc-4_1-mrel-spec/index.html]) does not require this : > {quote} > It is implementation-defined as to whether _getGeneratedKeys_ will return generated values after invoking the _executeBatch_ method. > {quote} > However, it would be nice to have this behavior documented - https://teiid.gitbooks.io/documents/content/client-dev/JDBC_Support.html -- This message was sent by Atlassian JIRA (v7.2.3#72005)

9 years

1
0
0 / 0

[JBoss JIRA] (TEIID-4582) Enable flexibiliy in use of colon delimiter when defining cacheTypeMap property

by Van Halbert (JIRA)

[ https://issues.jboss.org/browse/TEIID-4582?page=com.atlassian.jira.plugin... ] Van Halbert updated TEIID-4582: ------------------------------- Git Pull Request: https://github.com/teiid/teiid/pull/840, https://github.com/teiid/teiid-documents/pull/80 (was: https://github.com/teiid/teiid/pull/840) > Enable flexibiliy in use of colon delimiter when defining cacheTypeMap property > ------------------------------------------------------------------------------- > > Key: TEIID-4582 > URL: https://issues.jboss.org/browse/TEIID-4582 > Project: Teiid > Issue Type: Enhancement > Components: Misc. Connectors > Affects Versions: 9.2 > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Trivial > Fix For: 9.2 > > > The cacheTypeMap property syntax is the following: > cacheName:className;pkFieldName > where the colon and semi-colon are used. Change to be flexibile to also support colon for the second delimiter. -- This message was sent by Atlassian JIRA (v7.2.3#72005)

9 years

1
0
0 / 0

[JBoss JIRA] (TEIID-4582) Enable flexibiliy in use of colon delimiter when defining cacheTypeMap property

by Van Halbert (JIRA)

[ https://issues.jboss.org/browse/TEIID-4582?page=com.atlassian.jira.plugin... ] Van Halbert updated TEIID-4582: ------------------------------- Fix Version/s: 9.2 > Enable flexibiliy in use of colon delimiter when defining cacheTypeMap property > ------------------------------------------------------------------------------- > > Key: TEIID-4582 > URL: https://issues.jboss.org/browse/TEIID-4582 > Project: Teiid > Issue Type: Enhancement > Components: Misc. Connectors > Affects Versions: 9.2 > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Trivial > Fix For: 9.2 > > > The cacheTypeMap property syntax is the following: > cacheName:className;pkFieldName > where the colon and semi-colon are used. Change to be flexibile to also support colon for the second delimiter. -- This message was sent by Atlassian JIRA (v7.2.3#72005)

9 years

1
0
0 / 0

[JBoss JIRA] (TEIID-4582) Enable flexibiliy in use of colon delimiter when defining cacheTypeMap property

by Van Halbert (JIRA)

[ https://issues.jboss.org/browse/TEIID-4582?page=com.atlassian.jira.plugin... ] Van Halbert updated TEIID-4582: ------------------------------- Git Pull Request: https://github.com/teiid/teiid/pull/840 > Enable flexibiliy in use of colon delimiter when defining cacheTypeMap property > ------------------------------------------------------------------------------- > > Key: TEIID-4582 > URL: https://issues.jboss.org/browse/TEIID-4582 > Project: Teiid > Issue Type: Enhancement > Components: Misc. Connectors > Affects Versions: 9.2 > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Trivial > > The cacheTypeMap property syntax is the following: > cacheName:className;pkFieldName > where the colon and semi-colon are used. Change to be flexibile to also support colon for the second delimiter. -- This message was sent by Atlassian JIRA (v7.2.3#72005)

9 years

1
0
0 / 0

[JBoss JIRA] (TEIID-4558) Query Plan Analyzer

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4558?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-4558: ---------------------------------- Fix Version/s: 9.2 Assigning to 9.2 to improve text plan. Any additional information about problem nodes should be handled as annotations that will be available on the xml plan as well. > Query Plan Analyzer > ------------------- > > Key: TEIID-4558 > URL: https://issues.jboss.org/browse/TEIID-4558 > Project: Teiid > Issue Type: Feature Request > Components: Tools > Affects Versions: 9.2 > Reporter: Van Halbert > Fix For: 9.2 > > > Provide a tool that would take a query plan and provide feed back. > Example: > - indicate where there maybe a problem in the structure of the query that may impact performance > - indicate in the plan where there maybe issues (like table scan, no rows, etc.) > - provide recommendations for improving performance > etc.. -- This message was sent by Atlassian JIRA (v7.2.3#72005)

9 years

1
0
0 / 0

[JBoss JIRA] (TEIID-4603) Insert issue when cache contains multiple pojo's with the same key value

by Van Halbert (JIRA)

[ https://issues.jboss.org/browse/TEIID-4603?page=com.atlassian.jira.plugin... ] Work on TEIID-4603 stopped by Van Halbert. ------------------------------------------ > Insert issue when cache contains multiple pojo's with the same key value > ------------------------------------------------------------------------ > > Key: TEIID-4603 > URL: https://issues.jboss.org/browse/TEIID-4603 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 9.2 > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Blocker > Fix For: 9.2 > > > There is an INSERT issue when there are multiple pojo's stored in the same cache and the keys' overlap (meaning the different types of pojo's have the same key). -- This message was sent by Atlassian JIRA (v7.2.3#72005)

9 years

1
0
0 / 0

[JBoss JIRA] (TEIID-4603) Insert issue when cache contains multiple pojo's with the same key value

by Van Halbert (JIRA)

[ https://issues.jboss.org/browse/TEIID-4603?page=com.atlassian.jira.plugin... ] Work on TEIID-4603 started by Van Halbert. ------------------------------------------ > Insert issue when cache contains multiple pojo's with the same key value > ------------------------------------------------------------------------ > > Key: TEIID-4603 > URL: https://issues.jboss.org/browse/TEIID-4603 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 9.2 > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Blocker > Fix For: 9.2 > > > There is an INSERT issue when there are multiple pojo's stored in the same cache and the keys' overlap (meaning the different types of pojo's have the same key). -- This message was sent by Atlassian JIRA (v7.2.3#72005)

9 years

1
0
0 / 0

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues November 2016