September 2014 - 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 weeks, 6 days

2
1
0 / 0

[JBoss JIRA] (TEIID-3152) result set close after session close reported as an error

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-3152: ------------------------------------- Summary: result set close after session close reported as an error Key: TEIID-3152 URL: https://issues.jboss.org/browse/TEIID-3152 Project: Teiid Issue Type: Bug Components: Server Affects Versions: 8.7 Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 8.7.1, 8.9 An invalid session exception will be recorded as an error rather than a warning. This is due to the exceptionutil converting inappropriately converting the exception into a teiid component exception. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

11 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-3150) Add query plan info

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3150?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-3150. ----------------------------------- Resolution: Done Updated the getDescriptionProperties of the relevant nodes and made some cleanups to the query plan documentation. > Add query plan info > ------------------- > > Key: TEIID-3150 > URL: https://issues.jboss.org/browse/TEIID-3150 > Project: Teiid > Issue Type: Quality Risk > Components: Query Engine > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 8.9 > > > Table function nodes (array/xml/object/text table) should include their respective language object in the plan. Also batched update node is not showing its commands. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

11 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-3151) Add Support for SAP HANA Sources

by Ted Jones (JIRA)

Ted Jones created TEIID-3151: -------------------------------- Summary: Add Support for SAP HANA Sources Key: TEIID-3151 URL: https://issues.jboss.org/browse/TEIID-3151 Project: Teiid Issue Type: Feature Request Components: Misc. Connectors Affects Versions: 8.8 Reporter: Ted Jones Assignee: Steven Hawkins Need to add support SAP HANA. SAP provides a JDBC and ODBC driver for it. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

11 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-3150) Add query plan info

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-3150: ------------------------------------- Summary: Add query plan info Key: TEIID-3150 URL: https://issues.jboss.org/browse/TEIID-3150 Project: Teiid Issue Type: Quality Risk Components: Query Engine Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 8.9 Table function nodes (array/xml/object/text table) should include their respective language object in the plan. Also batched update node is not showing its commands. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

11 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-3147) Add information about the amount of data (in bytes) returned by each query

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3147?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-3147. ----------------------------------- Fix Version/s: 8.9 Resolution: Done Went the route of pre-serialization engine logic. This will specifically track the lob and row data bytes, but excludes exceptions/warnings, message overhead, etc. This will be available in the query plan when show plan is enabled (for non-embededded connections) or when command log trace level logging is enabled. > Add information about the amount of data (in bytes) returned by each query > -------------------------------------------------------------------------- > > Key: TEIID-3147 > URL: https://issues.jboss.org/browse/TEIID-3147 > Project: Teiid > Issue Type: Feature Request > Components: Query Engine > Reporter: Joao Viragine > Assignee: Steven Hawkins > Fix For: 8.9 > > > It would be nice if we had the information about the amount of data (in bytes) returned by each query. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

11 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-3147) Add information about the amount of data (in bytes) returned by each query

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3147?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-3147: ---------------------------------- Component/s: Query Engine > Add information about the amount of data (in bytes) returned by each query > -------------------------------------------------------------------------- > > Key: TEIID-3147 > URL: https://issues.jboss.org/browse/TEIID-3147 > Project: Teiid > Issue Type: Feature Request > Components: Query Engine > Reporter: Joao Viragine > Assignee: Steven Hawkins > > It would be nice if we had the information about the amount of data (in bytes) returned by each query. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

11 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-2448) Oracle driver translation error

by Johnathon Lee (JIRA)

[ https://issues.jboss.org/browse/TEIID-2448?page=com.atlassian.jira.plugin... ] Johnathon Lee closed TEIID-2448. -------------------------------- Resolution: Done > Oracle driver translation error > ------------------------------- > > Key: TEIID-2448 > URL: https://issues.jboss.org/browse/TEIID-2448 > Project: Teiid > Issue Type: Bug > Components: JDBC Connector > Affects Versions: 7.7 > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 7.7.11, 8.4 > > > Even procedures without resultsets have ?= added to them by the Oracle translator when it assumes the oracle driver is being used (the default). This will lead to a source error complaining that the function cannot be found. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

11 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-2448) Oracle driver translation error

by Johnathon Lee (JIRA)

[ https://issues.jboss.org/browse/TEIID-2448?page=com.atlassian.jira.plugin... ] Johnathon Lee updated TEIID-2448: --------------------------------- Fix Version/s: 7.7.11 > Oracle driver translation error > ------------------------------- > > Key: TEIID-2448 > URL: https://issues.jboss.org/browse/TEIID-2448 > Project: Teiid > Issue Type: Bug > Components: JDBC Connector > Affects Versions: 7.7 > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 8.4, 7.7.11 > > > Even procedures without resultsets have ?= added to them by the Oracle translator when it assumes the oracle driver is being used (the default). This will lead to a source error complaining that the function cannot be found. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

11 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-2448) Oracle driver translation error

by Johnathon Lee (JIRA)

[ https://issues.jboss.org/browse/TEIID-2448?page=com.atlassian.jira.plugin... ] Johnathon Lee reopened TEIID-2448: ---------------------------------- version fix update > Oracle driver translation error > ------------------------------- > > Key: TEIID-2448 > URL: https://issues.jboss.org/browse/TEIID-2448 > Project: Teiid > Issue Type: Bug > Components: JDBC Connector > Affects Versions: 7.7 > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 8.4 > > > Even procedures without resultsets have ?= added to them by the Oracle translator when it assumes the oracle driver is being used (the default). This will lead to a source error complaining that the function cannot be found. -- This message was sent by Atlassian JIRA (v6.3.1#6329)

11 years, 1 month

1
0
0 / 0

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues September 2014