[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 months, 1 week
[JBoss JIRA] (TEIID-5873) Implement ComplexTypes support in OData EntityType
by Chee Kin Lim (Jira)
Chee Kin Lim created TEIID-5873:
-----------------------------------
Summary: Implement ComplexTypes support in OData EntityType
Key: TEIID-5873
URL: https://issues.redhat.com/browse/TEIID-5873
Project: Teiid
Issue Type: Enhancement
Components: OData
Affects Versions: 13.0.1
Reporter: Chee Kin Lim
Assignee: Steven Hawkins
Given the following document in MongoDB matrix_variable collection:
{
"_id" : ObjectId("5b2367c9c9e77c0007ca0111"),
"createdOn" : ISODate("2018-06-15T07:16:25.459Z"),
"modifiedOn" : ISODate("2018-07-02T02:04:16.661Z"),
"variableId" : "183cf101d2334ac09bd0db9aee96081f",
"name" : "lfzq_test",
"description" : "",
"lastModifiedUserEmail" : "admin1(a)abc.com",
"headerRow" : {
"rowId" : "0b002942a5b4424f98fcc270af0290c3",
"values" : [
"name",
"value"
],
"isEncrypted" : [
false,
true
]
},
"rows" : [
{
"rowId" : "940467f9bb724d1688086baeec05049b",
"values" : [
"lfzq123q",
"751c634ca5e24102a00800b386955a5c"
]
}
],
"deleted" : false
}
The "headerRow" property is exposed as separate entity type in OData schema, but not "rows" property.
I found the following entity types with odata/$metadata:
<EntityType Name="headerRow">...</EntityType>
<EntityType Name="matrix_variable">...</EntityType>
Github repo to showcase the issue:
https://github.com/limcheekin/teiid-spring-boot-mongodb-odata
Related StackOverflow post:
https://stackoverflow.com/a/59385181/303211
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years
[JBoss JIRA] (TEIIDSB-144) Calling setTransactionIsolation on pg driver causes the connection to be closed
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-144?page=com.atlassian.jira.plug... ]
Steven Hawkins commented on TEIIDSB-144:
----------------------------------------
Either way. I was just flipping it to no longer in progress given the break.
> Calling setTransactionIsolation on pg driver causes the connection to be closed
> -------------------------------------------------------------------------------
>
> Key: TEIIDSB-144
> URL: https://issues.redhat.com/browse/TEIIDSB-144
> Project: Teiid Spring Boot
> Issue Type: Bug
> Components: datasource
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 1.3.1
>
> Original Estimate: 4 hours
> Time Spent: 2 hours
> Remaining Estimate: 2 hours
>
> An attempt to set the isolation level on pg 42.2.8 resulted in an exception SQLSTATE 0a000 - unsupported - which causes the connection pool to mark the connection as broken:
> {code}
> 2019-11-25 23:07:50.125 WARN 43993 --- [ProcessorQueue6] com.zaxxer.hikari.pool.ProxyConnection : HikariPool-1 - Connection org.postgresql.jdbc.PgConnection@2ffebc7e marked as broken because of SQLSTATE(0A000), ErrorCode(0)
> org.postgresql.util.PSQLException:
> at org.postgresql.jdbc.PgConnection.setTransactionIsolation(PgConnection.java:851) ~[postgresql-42.2.8.jar:42.2.8]
> at com.zaxxer.hikari.pool.ProxyConnection.setTransactionIsolation(ProxyConnection.java:407) ~[HikariCP-3.2.0.jar:na]
> at com.zaxxer.hikari.pool.HikariProxyConnection.setTransactionIsolation(HikariProxyConnection.java) ~[HikariCP-3.2.0.jar:na]
> at org.teiid.translator.jdbc.JDBCBaseExecution.<init>(JDBCBaseExecution.java:72) ~[translator-jdbc-12.3.0.jar:12.3.0]
> {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years
[JBoss JIRA] (TEIIDSB-144) Calling setTransactionIsolation on pg driver causes the connection to be closed
by Ramesh Reddy (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-144?page=com.atlassian.jira.plug... ]
Ramesh Reddy commented on TEIIDSB-144:
--------------------------------------
Do you want me to take over this, you good?
> Calling setTransactionIsolation on pg driver causes the connection to be closed
> -------------------------------------------------------------------------------
>
> Key: TEIIDSB-144
> URL: https://issues.redhat.com/browse/TEIIDSB-144
> Project: Teiid Spring Boot
> Issue Type: Bug
> Components: datasource
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 1.3.1
>
> Original Estimate: 4 hours
> Time Spent: 2 hours
> Remaining Estimate: 2 hours
>
> An attempt to set the isolation level on pg 42.2.8 resulted in an exception SQLSTATE 0a000 - unsupported - which causes the connection pool to mark the connection as broken:
> {code}
> 2019-11-25 23:07:50.125 WARN 43993 --- [ProcessorQueue6] com.zaxxer.hikari.pool.ProxyConnection : HikariPool-1 - Connection org.postgresql.jdbc.PgConnection@2ffebc7e marked as broken because of SQLSTATE(0A000), ErrorCode(0)
> org.postgresql.util.PSQLException:
> at org.postgresql.jdbc.PgConnection.setTransactionIsolation(PgConnection.java:851) ~[postgresql-42.2.8.jar:42.2.8]
> at com.zaxxer.hikari.pool.ProxyConnection.setTransactionIsolation(ProxyConnection.java:407) ~[HikariCP-3.2.0.jar:na]
> at com.zaxxer.hikari.pool.HikariProxyConnection.setTransactionIsolation(HikariProxyConnection.java) ~[HikariCP-3.2.0.jar:na]
> at org.teiid.translator.jdbc.JDBCBaseExecution.<init>(JDBCBaseExecution.java:72) ~[translator-jdbc-12.3.0.jar:12.3.0]
> {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years
[JBoss JIRA] (TEIIDSB-147) Caching and materialization changes for JDG / OpenShift
by Ramesh Reddy (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-147?page=com.atlassian.jira.plug... ]
Ramesh Reddy updated TEIIDSB-147:
---------------------------------
Fix Version/s: 1.3.1
(was: 1.3.0)
> Caching and materialization changes for JDG / OpenShift
> -------------------------------------------------------
>
> Key: TEIIDSB-147
> URL: https://issues.redhat.com/browse/TEIIDSB-147
> Project: Teiid Spring Boot
> Issue Type: Enhancement
> Components: core, OpenShift
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 1.3.1
>
>
> Started this as a higher level issue in Teiid Spring Boot rather than core Teiid since the work won't align well to Teiid 13.1, but some things may need addressed there as well.
> Things break down roughly into:
> * Single-pod support
> ** Define the expectations for how we'll be configured to hit a JDG instance - will it be a user exercise to create if it doesn't exist, will we need to create, will the be a crd strategy for creation, and will it be multi-tenet? There is also the issue / assumption of cache sharing based upon vdb name - is that sufficient for now?
> ** validate external materialization to JDG likely using a transactional upsert strategy to avoid issues like TEIID-5436
> * Multi-pod support
> ** everything from single-pod support
> ** Result set caching needs to directly write batches / results to JDG rather than relying upon on demand replication.
> ** requires replacing the logic that detect failed materialization loads
> ** internal materialization replacement needs to based upon something more like external materialization, but that can be seen as a next step that is turnkey internal materialization to JDG (little to no user setup required)
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years
[JBoss JIRA] (TEIIDSB-147) Caching and materialization changes for JDG / OpenShift
by Ramesh Reddy (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-147?page=com.atlassian.jira.plug... ]
Ramesh Reddy updated TEIIDSB-147:
---------------------------------
Sprint: (was: DV Sprint 56)
> Caching and materialization changes for JDG / OpenShift
> -------------------------------------------------------
>
> Key: TEIIDSB-147
> URL: https://issues.redhat.com/browse/TEIIDSB-147
> Project: Teiid Spring Boot
> Issue Type: Enhancement
> Components: core, OpenShift
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 1.3.1
>
>
> Started this as a higher level issue in Teiid Spring Boot rather than core Teiid since the work won't align well to Teiid 13.1, but some things may need addressed there as well.
> Things break down roughly into:
> * Single-pod support
> ** Define the expectations for how we'll be configured to hit a JDG instance - will it be a user exercise to create if it doesn't exist, will we need to create, will the be a crd strategy for creation, and will it be multi-tenet? There is also the issue / assumption of cache sharing based upon vdb name - is that sufficient for now?
> ** validate external materialization to JDG likely using a transactional upsert strategy to avoid issues like TEIID-5436
> * Multi-pod support
> ** everything from single-pod support
> ** Result set caching needs to directly write batches / results to JDG rather than relying upon on demand replication.
> ** requires replacing the logic that detect failed materialization loads
> ** internal materialization replacement needs to based upon something more like external materialization, but that can be seen as a next step that is turnkey internal materialization to JDG (little to no user setup required)
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years
[JBoss JIRA] (TEIIDSB-144) Calling setTransactionIsolation on pg driver causes the connection to be closed
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-144?page=com.atlassian.jira.plug... ]
Steven Hawkins commented on TEIIDSB-144:
----------------------------------------
Sounds like the plan is to move back to tomcat-jdbc then and make the appropriate changes at least in teiid-syndesis.
> Calling setTransactionIsolation on pg driver causes the connection to be closed
> -------------------------------------------------------------------------------
>
> Key: TEIIDSB-144
> URL: https://issues.redhat.com/browse/TEIIDSB-144
> Project: Teiid Spring Boot
> Issue Type: Bug
> Components: datasource
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 1.3.1
>
> Original Estimate: 4 hours
> Remaining Estimate: 4 hours
>
> An attempt to set the isolation level on pg 42.2.8 resulted in an exception SQLSTATE 0a000 - unsupported - which causes the connection pool to mark the connection as broken:
> {code}
> 2019-11-25 23:07:50.125 WARN 43993 --- [ProcessorQueue6] com.zaxxer.hikari.pool.ProxyConnection : HikariPool-1 - Connection org.postgresql.jdbc.PgConnection@2ffebc7e marked as broken because of SQLSTATE(0A000), ErrorCode(0)
> org.postgresql.util.PSQLException:
> at org.postgresql.jdbc.PgConnection.setTransactionIsolation(PgConnection.java:851) ~[postgresql-42.2.8.jar:42.2.8]
> at com.zaxxer.hikari.pool.ProxyConnection.setTransactionIsolation(ProxyConnection.java:407) ~[HikariCP-3.2.0.jar:na]
> at com.zaxxer.hikari.pool.HikariProxyConnection.setTransactionIsolation(HikariProxyConnection.java) ~[HikariCP-3.2.0.jar:na]
> at org.teiid.translator.jdbc.JDBCBaseExecution.<init>(JDBCBaseExecution.java:72) ~[translator-jdbc-12.3.0.jar:12.3.0]
> {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years