[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 days, 13 hours
[JBoss JIRA] (TEIID-2834) Use FunctionMethod metadata internally for built-in aggregates
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-2834:
-------------------------------------
Summary: Use FunctionMethod metadata internally for built-in aggregates
Key: TEIID-2834
URL: https://issues.jboss.org/browse/TEIID-2834
Project: Teiid
Issue Type: Task
Components: Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
With TEIID-2815 we added aggregate function definitions for consumption by designer, but it would be good to have them consistently handled internally. The metadata needs to accommodate count\(*) and textagg.
--
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
11 years, 8 months
[JBoss JIRA] (TEIID-2833) While importing from Deployed BQT2 VDB, JDBC type of 0 and type name NULL was returned for column with design-time type varbinary
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-2833?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration updated TEIID-2833:
-------------------------------------------
Bugzilla Update: Perform
Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1060335
> While importing from Deployed BQT2 VDB, JDBC type of 0 and type name NULL was returned for column with design-time type varbinary
> ---------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-2833
> URL: https://issues.jboss.org/browse/TEIID-2833
> Project: Teiid
> Issue Type: Bug
> Reporter: Barry LaFond
> Fix For: 8.7
>
> Attachments: TEIID_2829_project.zip
>
>
> 1) Open attached project in Designer
> 2) Deploy the BQT2.vdb
> 3) Create another project
> 4) Import the VDB metadata via JDBC Importer into your second project (will end up being a read-only VDB source model)
> 5) On *finish* you'll get an exception when processing the type_varbinary column because the type returned is "0" and type name of "null"
> {code}
> java.lang.IllegalArgumentException: Expected argument to be non-null but got null
> at org.teiid.core.designer.util.CoreArgCheck.isNotNull(CoreArgCheck.java:142)
> at org.teiid.core.designer.util.CoreArgCheck.isNotNull(CoreArgCheck.java:128)
> at org.teiid.designer.sdt.types.BuiltInTypesManager.getBuiltInDatatype(BuiltInTypesManager.java:477)
> at org.teiid.designer.sdt.types.WorkspaceDatatypeManager.getBuiltInDatatype(WorkspaceDatatypeManager.java:94)
> at org.teiid.designer.jdbc.relational.impl.RelationalModelProcessorImpl.findType(RelationalModelProcessorImpl.java:1450)
> at org.teiid.designer.jdbc.relational.impl.RelationalModelProcessorImpl.setColumnInfo(RelationalModelProcessorImpl.java:1321)
> {code}
--
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
11 years, 8 months
[JBoss JIRA] (TEIID-2833) While importing from Deployed BQT2 VDB, JDBC type of 0 and type name NULL was returned for column with design-time type varbinary
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2833?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-2833.
-----------------------------------
Fix Version/s: 8.7
Resolution: Done
switched to using the same uuid as designer
> While importing from Deployed BQT2 VDB, JDBC type of 0 and type name NULL was returned for column with design-time type varbinary
> ---------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-2833
> URL: https://issues.jboss.org/browse/TEIID-2833
> Project: Teiid
> Issue Type: Bug
> Reporter: Barry LaFond
> Fix For: 8.7
>
> Attachments: TEIID_2829_project.zip
>
>
> 1) Open attached project in Designer
> 2) Deploy the BQT2.vdb
> 3) Create another project
> 4) Import the VDB metadata via JDBC Importer into your second project (will end up being a read-only VDB source model)
> 5) On *finish* you'll get an exception when processing the type_varbinary column because the type returned is "0" and type name of "null"
> {code}
> java.lang.IllegalArgumentException: Expected argument to be non-null but got null
> at org.teiid.core.designer.util.CoreArgCheck.isNotNull(CoreArgCheck.java:142)
> at org.teiid.core.designer.util.CoreArgCheck.isNotNull(CoreArgCheck.java:128)
> at org.teiid.designer.sdt.types.BuiltInTypesManager.getBuiltInDatatype(BuiltInTypesManager.java:477)
> at org.teiid.designer.sdt.types.WorkspaceDatatypeManager.getBuiltInDatatype(WorkspaceDatatypeManager.java:94)
> at org.teiid.designer.jdbc.relational.impl.RelationalModelProcessorImpl.findType(RelationalModelProcessorImpl.java:1450)
> at org.teiid.designer.jdbc.relational.impl.RelationalModelProcessorImpl.setColumnInfo(RelationalModelProcessorImpl.java:1321)
> {code}
--
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
11 years, 8 months
[JBoss JIRA] (TEIID-2833) While importing from Deployed BQT2 VDB, JDBC type of 0 and type name NULL was returned for column with design-time type varbinary
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2833?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-2833:
---------------------------------------
Moved back to Teiid to use a different uuid. I think the reason we are not throwing an exception in the first place when we can't find the type has to do with old user defined scenarios in which it seemed like the referenced datatype was not known.
> While importing from Deployed BQT2 VDB, JDBC type of 0 and type name NULL was returned for column with design-time type varbinary
> ---------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-2833
> URL: https://issues.jboss.org/browse/TEIID-2833
> Project: Teiid
> Issue Type: Bug
> Reporter: Barry LaFond
> Attachments: TEIID_2829_project.zip
>
>
> 1) Open attached project in Designer
> 2) Deploy the BQT2.vdb
> 3) Create another project
> 4) Import the VDB metadata via JDBC Importer into your second project (will end up being a read-only VDB source model)
> 5) On *finish* you'll get an exception when processing the type_varbinary column because the type returned is "0" and type name of "null"
> {code}
> java.lang.IllegalArgumentException: Expected argument to be non-null but got null
> at org.teiid.core.designer.util.CoreArgCheck.isNotNull(CoreArgCheck.java:142)
> at org.teiid.core.designer.util.CoreArgCheck.isNotNull(CoreArgCheck.java:128)
> at org.teiid.designer.sdt.types.BuiltInTypesManager.getBuiltInDatatype(BuiltInTypesManager.java:477)
> at org.teiid.designer.sdt.types.WorkspaceDatatypeManager.getBuiltInDatatype(WorkspaceDatatypeManager.java:94)
> at org.teiid.designer.jdbc.relational.impl.RelationalModelProcessorImpl.findType(RelationalModelProcessorImpl.java:1450)
> at org.teiid.designer.jdbc.relational.impl.RelationalModelProcessorImpl.setColumnInfo(RelationalModelProcessorImpl.java:1321)
> {code}
--
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
11 years, 8 months
[JBoss JIRA] (TEIID-2833) While importing from Deployed BQT2 VDB, JDBC type of 0 and type name NULL was returned for column with design-time type varbinary
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2833?page=com.atlassian.jira.plugin... ]
Steven Hawkins moved TEIIDDES-2038 to TEIID-2833:
-------------------------------------------------
Project: Teiid (was: Teiid Designer)
Key: TEIID-2833 (was: TEIIDDES-2038)
> While importing from Deployed BQT2 VDB, JDBC type of 0 and type name NULL was returned for column with design-time type varbinary
> ---------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-2833
> URL: https://issues.jboss.org/browse/TEIID-2833
> Project: Teiid
> Issue Type: Bug
> Reporter: Barry LaFond
> Attachments: TEIID_2829_project.zip
>
>
> 1) Open attached project in Designer
> 2) Deploy the BQT2.vdb
> 3) Create another project
> 4) Import the VDB metadata via JDBC Importer into your second project (will end up being a read-only VDB source model)
> 5) On *finish* you'll get an exception when processing the type_varbinary column because the type returned is "0" and type name of "null"
> {code}
> java.lang.IllegalArgumentException: Expected argument to be non-null but got null
> at org.teiid.core.designer.util.CoreArgCheck.isNotNull(CoreArgCheck.java:142)
> at org.teiid.core.designer.util.CoreArgCheck.isNotNull(CoreArgCheck.java:128)
> at org.teiid.designer.sdt.types.BuiltInTypesManager.getBuiltInDatatype(BuiltInTypesManager.java:477)
> at org.teiid.designer.sdt.types.WorkspaceDatatypeManager.getBuiltInDatatype(WorkspaceDatatypeManager.java:94)
> at org.teiid.designer.jdbc.relational.impl.RelationalModelProcessorImpl.findType(RelationalModelProcessorImpl.java:1450)
> at org.teiid.designer.jdbc.relational.impl.RelationalModelProcessorImpl.setColumnInfo(RelationalModelProcessorImpl.java:1321)
> {code}
--
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
11 years, 8 months
[JBoss JIRA] (TEIID-2828) ResteasyEnabler fails with OTHER model types
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-2828?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration updated TEIID-2828:
-------------------------------------------
Bugzilla Update: Perform
Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1059917
> ResteasyEnabler fails with OTHER model types
> --------------------------------------------
>
> Key: TEIID-2828
> URL: https://issues.jboss.org/browse/TEIID-2828
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Affects Versions: 8.4
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.7
>
>
> If a vdb contains OTHER type models (typically used for .xsd), then the schema lookup will produce a null and result in an NPE:
> java.lang.NullPointerException
> at org.teiid.jboss.rest.ResteasyEnabler.hasRestMetadata(ResteasyEnabler.java:146) [teiid-jboss-integration-8.4.1-redhat-2.jar:8.4.1-redhat-2]
> at org.teiid.jboss.rest.ResteasyEnabler.finishedDeployment(ResteasyEnabler.java:81) [teiid-jboss-integration-8.4.1-redhat-2.jar:8.4.1-redhat-2]
> at org.teiid.deployers.VDBRepository.notifyFinished(VDBRepository.java:351) [teiid-runtime-8.4.1-redhat-2.jar:8.4.1-redhat-2]
>
--
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
11 years, 8 months