[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
4 days, 8 hours
[JBoss JIRA] (TEIID-2977) class cast exception with odata json format function results
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2977?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-2977.
-----------------------------------
Resolution: Done
Changed the collection builders to use the component type rather than the collection type.
> class cast exception with odata json format function results
> ------------------------------------------------------------
>
> Key: TEIID-2977
> URL: https://issues.jboss.org/browse/TEIID-2977
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Affects Versions: 8.4
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.7.1, 8.8
>
>
> If the json format is requested for function/stored procedure results, the json formatting logic will through a class cast:
> 14:56:38,642 ERROR [org.teiid.ODATA] (http-localhost/127.0.0.1:8080-1) TEIID16013 Error occured producing OData result.: java.lang.ClassCastException: org.odata4j.core.OComplexObjects$OComplexObjectImpl cannot be cast to org.odata4j.core.OCollection at org.odata4j.format.json.JsonFormatWriter.writeValue(JsonFormatWriter.java:145) [odata4j-core-0.8.0-SNAPSHOT-redhat.jar:] at org.odata4j.format.json.JsonCollectionFormatWriter.writeContent(JsonCollectionFormatWriter.java:38) [odata4j-core-0.8.0-SNAPSHOT-redhat.jar:] at org.odata4j.format.json.JsonCollectionFormatWriter.writeContent(JsonCollectionFormatWriter.java:14) [odata4j-core-0.8.0-SNAPSHOT-redhat.jar:] at
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 4 months
[JBoss JIRA] (TEIID-2977) class cast exception with odata json format function results
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-2977:
-------------------------------------
Summary: class cast exception with odata json format function results
Key: TEIID-2977
URL: https://issues.jboss.org/browse/TEIID-2977
Project: Teiid
Issue Type: Bug
Components: OData
Affects Versions: 8.4
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 8.7.1, 8.8
If the json format is requested for function/stored procedure results, the json formatting logic will through a class cast:
14:56:38,642 ERROR [org.teiid.ODATA] (http-localhost/127.0.0.1:8080-1) TEIID16013 Error occured producing OData result.: java.lang.ClassCastException: org.odata4j.core.OComplexObjects$OComplexObjectImpl cannot be cast to org.odata4j.core.OCollection at org.odata4j.format.json.JsonFormatWriter.writeValue(JsonFormatWriter.java:145) [odata4j-core-0.8.0-SNAPSHOT-redhat.jar:] at org.odata4j.format.json.JsonCollectionFormatWriter.writeContent(JsonCollectionFormatWriter.java:38) [odata4j-core-0.8.0-SNAPSHOT-redhat.jar:] at org.odata4j.format.json.JsonCollectionFormatWriter.writeContent(JsonCollectionFormatWriter.java:14) [odata4j-core-0.8.0-SNAPSHOT-redhat.jar:] at
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 4 months
[JBoss JIRA] (TEIID-2976) Using new database logging extension, getting error that indicates column isn't long enough
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-2976?page=com.atlassian.jira.plugin... ]
Van Halbert commented on TEIID-2976:
------------------------------------
That should be good. This will get some good user testing next week.
> Using new database logging extension, getting error that indicates column isn't long enough
> -------------------------------------------------------------------------------------------
>
> Key: TEIID-2976
> URL: https://issues.jboss.org/browse/TEIID-2976
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Reporter: Van Halbert
> Assignee: Ramesh Reddy
> Priority: Critical
> Fix For: 8.7.1
>
>
> Getting the following sporadically:
> 11:57:57,538 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (pool-2-thread-1) Value too long for column "TRANSACTIONID VARCHAR(50)": "'TransactionImple < ac, BasicAction: 0:ffffc0a80102:307704ce:5388aa39:169 status: ActionStatus.RUNNING >' (103)"; SQL statement:
> insert into commandlog (id, applicationname, executionid, modelname, plan, principal, requestid, rowcount, sessionid, sourcecommand, sourcerequestid, sqlcmd, eventtype, logtime, transactionid, translatorname, vdbname, vdbversion) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [22001-168]
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 5 months
[JBoss JIRA] (TEIID-2976) Using new database logging extension, getting error that indicates column isn't long enough
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2976?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-2976.
---------------------------------
Fix Version/s: 8.7.1
Resolution: Done
Changed the tansactionId column size to 100
> Using new database logging extension, getting error that indicates column isn't long enough
> -------------------------------------------------------------------------------------------
>
> Key: TEIID-2976
> URL: https://issues.jboss.org/browse/TEIID-2976
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Reporter: Van Halbert
> Assignee: Ramesh Reddy
> Priority: Critical
> Fix For: 8.7.1
>
>
> Getting the following sporadically:
> 11:57:57,538 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (pool-2-thread-1) Value too long for column "TRANSACTIONID VARCHAR(50)": "'TransactionImple < ac, BasicAction: 0:ffffc0a80102:307704ce:5388aa39:169 status: ActionStatus.RUNNING >' (103)"; SQL statement:
> insert into commandlog (id, applicationname, executionid, modelname, plan, principal, requestid, rowcount, sessionid, sourcecommand, sourcerequestid, sqlcmd, eventtype, logtime, transactionid, translatorname, vdbname, vdbversion) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [22001-168]
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 5 months
[JBoss JIRA] (TEIID-2976) Using new database logging extension, getting error that indicates column isn't long enough
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2976?page=com.atlassian.jira.plugin... ]
Ramesh Reddy reassigned TEIID-2976:
-----------------------------------
Assignee: Ramesh Reddy (was: Steven Hawkins)
> Using new database logging extension, getting error that indicates column isn't long enough
> -------------------------------------------------------------------------------------------
>
> Key: TEIID-2976
> URL: https://issues.jboss.org/browse/TEIID-2976
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Reporter: Van Halbert
> Assignee: Ramesh Reddy
> Priority: Critical
>
> Getting the following sporadically:
> 11:57:57,538 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (pool-2-thread-1) Value too long for column "TRANSACTIONID VARCHAR(50)": "'TransactionImple < ac, BasicAction: 0:ffffc0a80102:307704ce:5388aa39:169 status: ActionStatus.RUNNING >' (103)"; SQL statement:
> insert into commandlog (id, applicationname, executionid, modelname, plan, principal, requestid, rowcount, sessionid, sourcecommand, sourcerequestid, sqlcmd, eventtype, logtime, transactionid, translatorname, vdbname, vdbversion) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [22001-168]
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 5 months
[JBoss JIRA] (TEIID-2976) Using new database logging extension, getting error that indicates column isn't long enough
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2976?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-2976:
-------------------------------------
Do you think changing the column size to 100 will suffice?
> Using new database logging extension, getting error that indicates column isn't long enough
> -------------------------------------------------------------------------------------------
>
> Key: TEIID-2976
> URL: https://issues.jboss.org/browse/TEIID-2976
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Priority: Critical
>
> Getting the following sporadically:
> 11:57:57,538 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (pool-2-thread-1) Value too long for column "TRANSACTIONID VARCHAR(50)": "'TransactionImple < ac, BasicAction: 0:ffffc0a80102:307704ce:5388aa39:169 status: ActionStatus.RUNNING >' (103)"; SQL statement:
> insert into commandlog (id, applicationname, executionid, modelname, plan, principal, requestid, rowcount, sessionid, sourcecommand, sourcerequestid, sqlcmd, eventtype, logtime, transactionid, translatorname, vdbname, vdbversion) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [22001-168]
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 5 months
[JBoss JIRA] (TEIID-2976) Using new database logging extension, getting error that indicates column isn't long enough
by Van Halbert (JIRA)
Van Halbert created TEIID-2976:
----------------------------------
Summary: Using new database logging extension, getting error that indicates column isn't long enough
Key: TEIID-2976
URL: https://issues.jboss.org/browse/TEIID-2976
Project: Teiid
Issue Type: Bug
Components: Server
Reporter: Van Halbert
Assignee: Steven Hawkins
Priority: Critical
Getting the following sporadically:
11:57:57,538 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (pool-2-thread-1) Value too long for column "TRANSACTIONID VARCHAR(50)": "'TransactionImple < ac, BasicAction: 0:ffffc0a80102:307704ce:5388aa39:169 status: ActionStatus.RUNNING >' (103)"; SQL statement:
insert into commandlog (id, applicationname, executionid, modelname, plan, principal, requestid, rowcount, sessionid, sourcecommand, sourcerequestid, sqlcmd, eventtype, logtime, transactionid, translatorname, vdbname, vdbversion) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [22001-168]
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 5 months
[JBoss JIRA] (TEIID-2959) Revert Salesforce DDL Option keys
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2959?page=com.atlassian.jira.plugin... ]
Ramesh Reddy resolved TEIID-2959.
---------------------------------
Labels: Beta1 (was: )
Resolution: Done
Reverted to original names
> Revert Salesforce DDL Option keys
> ---------------------------------
>
> Key: TEIID-2959
> URL: https://issues.jboss.org/browse/TEIID-2959
> Project: Teiid
> Issue Type: Bug
> Components: AdminApi
> Affects Versions: 8.7
> Reporter: Mark Drilling
> Assignee: Ramesh Reddy
> Labels: Beta1
> Fix For: 8.7.1, 8.8
>
>
> The OPTION keys for Salesforce DDL changed going from 8.6 to 8.7. Example below.
> =================
> 8.6 version
> CONSTRAINT Id_PK PRIMARY KEY(Id),
> CONSTRAINT FK_ApexPage_ScontrolId FOREIGN KEY(ScontrolId) REFERENCES ApexPage
> ) OPTIONS (UPDATABLE TRUE, Custom 'false', "Supports Create" 'true', "Supports Delete" 'true', "Supports Merge" 'false', "Supports Query" 'true', "Supports Replicate" 'true', "Supports Retrieve" 'true', "Supports Search" 'false');
> =================
> 8.7 version
> CONSTRAINT Id_PK PRIMARY KEY(Id),
> CONSTRAINT FK_ApexPage_ScontrolId FOREIGN KEY(ScontrolId) REFERENCES importVDBSrcModel.ApexPage (Id)
> ) OPTIONS (UPDATABLE TRUE, custom 'false', supportsCreate 'true', supportsDelete 'true', supportsMerge 'false', supportsQuery 'true', supportsReplicate 'true', supportsRetrieve 'true', supportsSearch 'false');
> =================
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 5 months