[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
13 hours, 7 minutes
[JBoss JIRA] (TEIID-4484) Random test failure with cancelled query
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4484?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4484.
-----------------------------------
Resolution: Done
Removed the interrupt entirely - as my last commit was a mistake in that isInterrrupted doesn't clear.
> Random test failure with cancelled query
> ----------------------------------------
>
> Key: TEIID-4484
> URL: https://issues.jboss.org/browse/TEIID-4484
> Project: Teiid
> Issue Type: Bug
> Components: Integration Tests
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 9.1, 9.0.5
>
>
> Due to interrupt handling we can have spurious test failures such as:
> {code}
> Tests run: 6, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 1.39 sec <<< FAILURE! - in org.teiid.jdbc.TestResultsCache
> testScope(org.teiid.jdbc.TestResultsCache) Time elapsed: 0.116 sec <<< ERROR!
> org.teiid.jdbc.TeiidSQLException: 57014 TEIID30160 The request 1kMLfcEKVhMX.0 has been cancelled.
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:146)
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years
[JBoss JIRA] (TEIID-4485) import-vdb VDB name case-sensitive
by Chandra Akkinepalli (JIRA)
[ https://issues.jboss.org/browse/TEIID-4485?page=com.atlassian.jira.plugin... ]
Chandra Akkinepalli reassigned TEIID-4485:
------------------------------------------
Assignee: Steven Hawkins (was: Chandra Akkinepalli)
> import-vdb VDB name case-sensitive
> ----------------------------------
>
> Key: TEIID-4485
> URL: https://issues.jboss.org/browse/TEIID-4485
> Project: Teiid
> Issue Type: Bug
> Components: VDB
> Affects Versions: 8.7
> Reporter: Chandra Akkinepalli
> Assignee: Steven Hawkins
> Fix For: 9.1, 9.0.5
>
>
> I have built couple of VDBs using designer, their names are all CAPS and wanted to import these 2 VDBs as views in my 3rd VDB. As the VDB name is cases insensitive in connect string i used lower case to connect to the VDB to do JDBC import. After i completed my 3rd VDB when i tried to deploy the VDB onto the server it is going into INACTIVE state, after changing my connection string to upper case and reimporting the VDB i was able to deploy the 3rd VDB and it state turned to ACTIVE.
> The VDB import definition seem's to be case sensitive and this may be the cause of the errors.
> I haven't tested this in Dynamic VDB.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years
[JBoss JIRA] (TEIID-4485) import-vdb VDB name case-sensitive
by Chandra Akkinepalli (JIRA)
[ https://issues.jboss.org/browse/TEIID-4485?page=com.atlassian.jira.plugin... ]
Work on TEIID-4485 stopped by Chandra Akkinepalli.
--------------------------------------------------
> import-vdb VDB name case-sensitive
> ----------------------------------
>
> Key: TEIID-4485
> URL: https://issues.jboss.org/browse/TEIID-4485
> Project: Teiid
> Issue Type: Bug
> Components: VDB
> Affects Versions: 8.7
> Reporter: Chandra Akkinepalli
> Assignee: Chandra Akkinepalli
> Fix For: 9.1, 9.0.5
>
>
> I have built couple of VDBs using designer, their names are all CAPS and wanted to import these 2 VDBs as views in my 3rd VDB. As the VDB name is cases insensitive in connect string i used lower case to connect to the VDB to do JDBC import. After i completed my 3rd VDB when i tried to deploy the VDB onto the server it is going into INACTIVE state, after changing my connection string to upper case and reimporting the VDB i was able to deploy the 3rd VDB and it state turned to ACTIVE.
> The VDB import definition seem's to be case sensitive and this may be the cause of the errors.
> I haven't tested this in Dynamic VDB.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years
[JBoss JIRA] (TEIID-4485) import-vdb VDB name case-sensitive
by Chandra Akkinepalli (JIRA)
[ https://issues.jboss.org/browse/TEIID-4485?page=com.atlassian.jira.plugin... ]
Work on TEIID-4485 started by Chandra Akkinepalli.
--------------------------------------------------
> import-vdb VDB name case-sensitive
> ----------------------------------
>
> Key: TEIID-4485
> URL: https://issues.jboss.org/browse/TEIID-4485
> Project: Teiid
> Issue Type: Bug
> Components: VDB
> Affects Versions: 8.7
> Reporter: Chandra Akkinepalli
> Assignee: Chandra Akkinepalli
> Fix For: 9.1, 9.0.5
>
>
> I have built couple of VDBs using designer, their names are all CAPS and wanted to import these 2 VDBs as views in my 3rd VDB. As the VDB name is cases insensitive in connect string i used lower case to connect to the VDB to do JDBC import. After i completed my 3rd VDB when i tried to deploy the VDB onto the server it is going into INACTIVE state, after changing my connection string to upper case and reimporting the VDB i was able to deploy the 3rd VDB and it state turned to ACTIVE.
> The VDB import definition seem's to be case sensitive and this may be the cause of the errors.
> I haven't tested this in Dynamic VDB.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years
[JBoss JIRA] (TEIID-4486) Improve performance of st_extent
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-4486:
-------------------------------------
Summary: Improve performance of st_extent
Key: TEIID-4486
URL: https://issues.jboss.org/browse/TEIID-4486
Project: Teiid
Issue Type: Quality Risk
Components: Query Engine
Affects Versions: 9.0, 8.12.5
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 9.1, 9.0.5
The implementation of st_extent is very costly. It should instead be based upon the Envelope class. Also it is not being pushed to pg.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years
[JBoss JIRA] (TEIID-4485) import-vdb VDB name case-sensitive
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4485?page=com.atlassian.jira.plugin... ]
Steven Hawkins reassigned TEIID-4485:
-------------------------------------
Fix Version/s: 9.1
9.0.5
Affects Version/s: 8.7
(was: 9.0.4)
Assignee: Steven Hawkins (was: Barry LaFond)
This issue here is that on deploy the vdb is creating service dependencies to the imported vdb, which are case sensitive.
> import-vdb VDB name case-sensitive
> ----------------------------------
>
> Key: TEIID-4485
> URL: https://issues.jboss.org/browse/TEIID-4485
> Project: Teiid
> Issue Type: Bug
> Components: VDB
> Affects Versions: 8.7
> Reporter: Chandra Akkinepalli
> Assignee: Steven Hawkins
> Fix For: 9.1, 9.0.5
>
>
> I have built couple of VDBs using designer, their names are all CAPS and wanted to import these 2 VDBs as views in my 3rd VDB. As the VDB name is cases insensitive in connect string i used lower case to connect to the VDB to do JDBC import. After i completed my 3rd VDB when i tried to deploy the VDB onto the server it is going into INACTIVE state, after changing my connection string to upper case and reimporting the VDB i was able to deploy the 3rd VDB and it state turned to ACTIVE.
> The VDB import definition seem's to be case sensitive and this may be the cause of the errors.
> I haven't tested this in Dynamic VDB.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years
[JBoss JIRA] (TEIID-4485) import-vdb VDB name case-sensitive
by Chandra Akkinepalli (JIRA)
Chandra Akkinepalli created TEIID-4485:
------------------------------------------
Summary: import-vdb VDB name case-sensitive
Key: TEIID-4485
URL: https://issues.jboss.org/browse/TEIID-4485
Project: Teiid
Issue Type: Bug
Components: VDB
Affects Versions: 9.0.4
Reporter: Chandra Akkinepalli
Assignee: Barry LaFond
I have built couple of VDBs using designer, their names are all CAPS and wanted to import these 2 VDBs as views in my 3rd VDB. As the VDB name is cases insensitive in connect string i used lower case to connect to the VDB to do JDBC import. After i completed my 3rd VDB when i tried to deploy the VDB onto the server it is going into INACTIVE state, after changing my connection string to upper case and reimporting the VDB i was able to deploy the 3rd VDB and it state turned to ACTIVE.
The VDB import definition seem's to be case sensitive and this may be the cause of the errors.
I haven't tested this in Dynamic VDB.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years
[JBoss JIRA] (TEIID-3630) Support merge statement pushdown
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3630?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3630.
-----------------------------------
Resolution: Done
Marking as resolved with pushdown support to hbase and h2. Also change the primary terminology to be upsert rather than merge, since the merge statement does have a standard meaning that we do not implement.
For hbase we may also want to consider a translator setting to disallow the conversion of a regular insert to an upsert - to force the semantics of insert to be respected.
Other sources will be added as needed.
> Support merge statement pushdown
> --------------------------------
>
> Key: TEIID-3630
> URL: https://issues.jboss.org/browse/TEIID-3630
> Project: Teiid
> Issue Type: Enhancement
> Components: Connector API, Grammar, Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 9.1
>
>
> We should add support for the merge statement to be pushded down.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years