June 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, 5 days

2
1
0 / 0

[JBoss JIRA] (TEIID-3022) refine removed vdb handling

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-3022: ------------------------------------- Summary: refine removed vdb handling Key: TEIID-3022 URL: https://issues.jboss.org/browse/TEIID-3022 Project: Teiid Issue Type: Quality Risk Security Level: Public (Everyone can see) Components: Server Affects Versions: 8.1 Reporter: Steven Hawkins Assignee: Steven Hawkins With TEIID-2105 the server will reject requests against a non-active vdb, which includes a removed vdb. This is against the original intent of allowing for lazy cleanup. We should either have a vdb remove proactively terminate all sessions and fully cleanup or we should reinstate the lazy logic. At this point it probably makes the most sense to go the first route as there is other logic (materialization management for one) now checking the vdb state and assuming that removal will invalidate the vdb. -- This message was sent by Atlassian JIRA (v6.2.6#6264)

11 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3018) Reflect view transformation sql into SYSTEM schema

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3018?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-3018. ----------------------------------- Fix Version/s: 8.8 Resolution: Done Added VIEWS and STOREDPROCEDURES tables - https://docs.jboss.org/author/display/TEIID/System+Tables This information was not yet added to the SYS TABLE/PROCEDURE since we already established a precedent with TRIGGERS and non-public information, and it would require api changes for us to better calculate the permissions to see the definition body (since we don't minimize projection down to the system tables, we'd currently have to compute all columns). We also left the informational column as "body" rather than "definition" since it will only contain the statement/query expression/etc. rather than the full DDL declaration - this is largely due to how we store the information on the metadata object and don't want to incur the overhead of the DDL generation. It may be necessary at some point though to provide the entire statement. > Reflect view transformation sql into SYSTEM schema > -------------------------------------------------- > > Key: TEIID-3018 > URL: https://issues.jboss.org/browse/TEIID-3018 > Project: Teiid > Issue Type: Feature Request > Security Level: Public(Everyone can see) > Reporter: Mark Addleman > Assignee: Steven Hawkins > Fix For: 8.8 > > > It would nice to reflect view transformation SQL in the SYSTEM schema > See https://community.jboss.org/message/879519?et=watches.email.thread#879519 for more detail -- This message was sent by Atlassian JIRA (v6.2.6#6264)

11 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3020) Materialized View timeout on loading

by Mark Addleman (JIRA)

[ https://issues.jboss.org/browse/TEIID-3020?page=com.atlassian.jira.plugin... ] Mark Addleman commented on TEIID-3020: -------------------------------------- Thanks. The existing behavior is exactly what we want. > Materialized View timeout on loading > ------------------------------------ > > Key: TEIID-3020 > URL: https://issues.jboss.org/browse/TEIID-3020 > Project: Teiid > Issue Type: Feature Request > Security Level: Public(Everyone can see) > Reporter: Mark Addleman > Assignee: Steven Hawkins > > Materialized view should timeout on loading if the translator/data source takes too long to respond. > Perhaps time out when the initiating JDBC statement times out or is cancelled? > See https://community.jboss.org/thread/242372 for more info -- This message was sent by Atlassian JIRA (v6.2.6#6264)

11 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3020) Materialized View timeout on loading

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3020?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-3020: --------------------------------------- With TEIID-2840 the default materialization load behavior is tied to the initiating query by default (previously. If it is timed out, the session is ended, etc. then the load will be aborted. > Materialized View timeout on loading > ------------------------------------ > > Key: TEIID-3020 > URL: https://issues.jboss.org/browse/TEIID-3020 > Project: Teiid > Issue Type: Feature Request > Security Level: Public(Everyone can see) > Reporter: Mark Addleman > Assignee: Steven Hawkins > > Materialized view should timeout on loading if the translator/data source takes too long to respond. > Perhaps time out when the initiating JDBC statement times out or is cancelled? > See https://community.jboss.org/thread/242372 for more info -- This message was sent by Atlassian JIRA (v6.2.6#6264)

11 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3020) Materialized View timeout on loading

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3020?page=com.atlassian.jira.plugin... ] Steven Hawkins edited comment on TEIID-3020 at 6/30/14 9:55 AM: ---------------------------------------------------------------- With TEIID-2840 the default materialization load behavior is tied to the initiating query by default (previously the default behavior typically would fork the load without invalidating). If it is timed out, the session is ended, etc. then the load will be aborted. was (Author: shawkins): With TEIID-2840 the default materialization load behavior is tied to the initiating query by default (previously. If it is timed out, the session is ended, etc. then the load will be aborted. > Materialized View timeout on loading > ------------------------------------ > > Key: TEIID-3020 > URL: https://issues.jboss.org/browse/TEIID-3020 > Project: Teiid > Issue Type: Feature Request > Security Level: Public(Everyone can see) > Reporter: Mark Addleman > Assignee: Steven Hawkins > > Materialized view should timeout on loading if the translator/data source takes too long to respond. > Perhaps time out when the initiating JDBC statement times out or is cancelled? > See https://community.jboss.org/thread/242372 for more info -- This message was sent by Atlassian JIRA (v6.2.6#6264)

11 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3021) Serialization of long sqlexception/warning next chains can lead to oom

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3021?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-3021. ----------------------------------- Resolution: Done Changed the logic to not output the chain when it is a nested exception holder. > Serialization of long sqlexception/warning next chains can lead to oom > ---------------------------------------------------------------------- > > Key: TEIID-3021 > URL: https://issues.jboss.org/browse/TEIID-3021 > Project: Teiid > Issue Type: Bug > Security Level: Public(Everyone can see) > Components: Server > Affects Versions: 8.1 > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 8.7.1, 8.8 > > > The serialization logic for exception chains is not correct and will output exponentially too many instances of chained exceptions, which can lead to out of memory conditions. -- This message was sent by Atlassian JIRA (v6.2.6#6264)

11 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3021) Serialization of long sqlexception/warning next chains can lead to oom

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-3021?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration updated TEIID-3021: ------------------------------------------- Bugzilla Update: Perform Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1114589 > Serialization of long sqlexception/warning next chains can lead to oom > ---------------------------------------------------------------------- > > Key: TEIID-3021 > URL: https://issues.jboss.org/browse/TEIID-3021 > Project: Teiid > Issue Type: Bug > Security Level: Public(Everyone can see) > Components: Server > Affects Versions: 8.1 > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 8.7.1, 8.8 > > > The serialization logic for exception chains is not correct and will output exponentially too many instances of chained exceptions, which can lead to out of memory conditions. -- This message was sent by Atlassian JIRA (v6.2.6#6264)

11 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3021) Serialization of long sqlexception/warning next chains can lead to oom

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-3021: ------------------------------------- Summary: Serialization of long sqlexception/warning next chains can lead to oom Key: TEIID-3021 URL: https://issues.jboss.org/browse/TEIID-3021 Project: Teiid Issue Type: Bug Security Level: Public (Everyone can see) Components: Server Affects Versions: 8.1 Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 8.7.1, 8.8 The serialization logic for exception chains is not correct and will output exponentially too many instances of chained exceptions, which can lead to out of memory conditions. -- This message was sent by Atlassian JIRA (v6.2.6#6264)

11 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3020) Materialized View timeout on loading

by Mark Addleman (JIRA)

Mark Addleman created TEIID-3020: ------------------------------------ Summary: Materialized View timeout on loading Key: TEIID-3020 URL: https://issues.jboss.org/browse/TEIID-3020 Project: Teiid Issue Type: Feature Request Security Level: Public (Everyone can see) Reporter: Mark Addleman Assignee: Steven Hawkins Materialized view should timeout on loading if the translator/data source takes too long to respond. Perhaps time out when the initiating JDBC statement times out or is cancelled? See https://community.jboss.org/thread/242372 for more info -- This message was sent by Atlassian JIRA (v6.2.6#6264)

11 years, 4 months

1
0
0 / 0

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues June 2014