[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-5395) Improvement of JPA translator join behavior
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5395?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5395:
----------------------------------
Fix Version/s: 10.3.2
> Improvement of JPA translator join behavior
> -------------------------------------------
>
> Key: TEIID-5395
> URL: https://issues.jboss.org/browse/TEIID-5395
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 10.2.1
> Reporter: Harold Campbell
> Assignee: Steven Hawkins
> Fix For: 11.0, 10.3.2
>
>
> The behavior of the JPA connector has a number of issues regarding entity relationships.
> * Implicit joins (those needed to get child entity id's) are made INNER JOINS, preventing getting any rows where the value would be null.
> * Something odd is done with *ToMany relationships which blows up the size of resultsets.
> * If a parent entity and a child entity use the same name for their id property, only one gets mapped.
> * It's not possible to join the same entity more than once.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5395) Improvement of JPA translator join behavior
by Harold Campbell (JIRA)
[ https://issues.jboss.org/browse/TEIID-5395?page=com.atlassian.jira.plugin... ]
Harold Campbell commented on TEIID-5395:
----------------------------------------
Our streams crossed. :)
I think *ToMany probably *can* be supported, but I agree that it's not the highest of priorities.
> Improvement of JPA translator join behavior
> -------------------------------------------
>
> Key: TEIID-5395
> URL: https://issues.jboss.org/browse/TEIID-5395
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 10.2.1
> Reporter: Harold Campbell
> Assignee: Steven Hawkins
> Fix For: 11.0
>
>
> The behavior of the JPA connector has a number of issues regarding entity relationships.
> * Implicit joins (those needed to get child entity id's) are made INNER JOINS, preventing getting any rows where the value would be null.
> * Something odd is done with *ToMany relationships which blows up the size of resultsets.
> * If a parent entity and a child entity use the same name for their id property, only one gets mapped.
> * It's not possible to join the same entity more than once.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5395) Improvement of JPA translator join behavior
by Harold Campbell (JIRA)
[ https://issues.jboss.org/browse/TEIID-5395?page=com.atlassian.jira.plugin... ]
Harold Campbell edited comment on TEIID-5395 at 6/29/18 9:13 PM:
-----------------------------------------------------------------
Our streams crossed. :)
I think ToMany probably *can* be supported, but I agree that it's not the highest of priorities.
was (Author: traivor):
Our streams crossed. :)
I think *ToMany probably *can* be supported, but I agree that it's not the highest of priorities.
> Improvement of JPA translator join behavior
> -------------------------------------------
>
> Key: TEIID-5395
> URL: https://issues.jboss.org/browse/TEIID-5395
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 10.2.1
> Reporter: Harold Campbell
> Assignee: Steven Hawkins
> Fix For: 11.0
>
>
> The behavior of the JPA connector has a number of issues regarding entity relationships.
> * Implicit joins (those needed to get child entity id's) are made INNER JOINS, preventing getting any rows where the value would be null.
> * Something odd is done with *ToMany relationships which blows up the size of resultsets.
> * If a parent entity and a child entity use the same name for their id property, only one gets mapped.
> * It's not possible to join the same entity more than once.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5395) Improvement of JPA translator join behavior
by Harold Campbell (JIRA)
[ https://issues.jboss.org/browse/TEIID-5395?page=com.atlassian.jira.plugin... ]
Harold Campbell commented on TEIID-5395:
----------------------------------------
Both cases need the join table, I believe, which the old code didn't do.
A test harness that exercises JPAMetadataProcessor would be awesome.
> Improvement of JPA translator join behavior
> -------------------------------------------
>
> Key: TEIID-5395
> URL: https://issues.jboss.org/browse/TEIID-5395
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 10.2.1
> Reporter: Harold Campbell
> Assignee: Steven Hawkins
> Fix For: 11.0
>
>
> The behavior of the JPA connector has a number of issues regarding entity relationships.
> * Implicit joins (those needed to get child entity id's) are made INNER JOINS, preventing getting any rows where the value would be null.
> * Something odd is done with *ToMany relationships which blows up the size of resultsets.
> * If a parent entity and a child entity use the same name for their id property, only one gets mapped.
> * It's not possible to join the same entity more than once.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5395) Improvement of JPA translator join behavior
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5395?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5395:
---------------------------------------
Thinking about it more, it seems like fully recreating the relational structure should not be the goal, just to make a best effort at representing the entity structure. So we'll leave things as not exposing many-to-many relationships and unidirectional collections.
> Improvement of JPA translator join behavior
> -------------------------------------------
>
> Key: TEIID-5395
> URL: https://issues.jboss.org/browse/TEIID-5395
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 10.2.1
> Reporter: Harold Campbell
> Assignee: Steven Hawkins
> Fix For: 11.0
>
>
> The behavior of the JPA connector has a number of issues regarding entity relationships.
> * Implicit joins (those needed to get child entity id's) are made INNER JOINS, preventing getting any rows where the value would be null.
> * Something odd is done with *ToMany relationships which blows up the size of resultsets.
> * If a parent entity and a child entity use the same name for their id property, only one gets mapped.
> * It's not possible to join the same entity more than once.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5395) Improvement of JPA translator join behavior
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5395?page=com.atlassian.jira.plugin... ]
Steven Hawkins edited comment on TEIID-5395 at 6/29/18 7:36 PM:
----------------------------------------------------------------
I think things look mostly good also. However it looks like the old import code of adding foreign keys based upon the collection side was for the case of a unidirectional relationship so that may need to come back.
There is also another (old) bug in that many to many relationships aren't generally handled properly as the code won't create a bridge table.
I'll start building up some unit tests around import.
was (Author: shawkins):
I think things look mostly good also. However it looks like the old import code of adding foreign keys based upon the collection side was for the case of a unidirectional relationship so that may need to come back.
There is also another bug in that many to many relationships aren't generally handled properly as the code won't create a bridge table.
I'll start building up some unit tests around import.
> Improvement of JPA translator join behavior
> -------------------------------------------
>
> Key: TEIID-5395
> URL: https://issues.jboss.org/browse/TEIID-5395
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 10.2.1
> Reporter: Harold Campbell
> Assignee: Steven Hawkins
> Fix For: 11.0
>
>
> The behavior of the JPA connector has a number of issues regarding entity relationships.
> * Implicit joins (those needed to get child entity id's) are made INNER JOINS, preventing getting any rows where the value would be null.
> * Something odd is done with *ToMany relationships which blows up the size of resultsets.
> * If a parent entity and a child entity use the same name for their id property, only one gets mapped.
> * It's not possible to join the same entity more than once.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5395) Improvement of JPA translator join behavior
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5395?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5395:
---------------------------------------
I think things look mostly good also. However it looks like the old import code of adding foreign keys based upon the collection side was for the case of a unidirectional relationship so that may need to come back.
There is also another bug in that many to many relationships aren't generally handled properly as the code won't create a bridge table.
I'll start building up some unit tests around import.
> Improvement of JPA translator join behavior
> -------------------------------------------
>
> Key: TEIID-5395
> URL: https://issues.jboss.org/browse/TEIID-5395
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 10.2.1
> Reporter: Harold Campbell
> Assignee: Steven Hawkins
> Fix For: 11.0
>
>
> The behavior of the JPA connector has a number of issues regarding entity relationships.
> * Implicit joins (those needed to get child entity id's) are made INNER JOINS, preventing getting any rows where the value would be null.
> * Something odd is done with *ToMany relationships which blows up the size of resultsets.
> * If a parent entity and a child entity use the same name for their id property, only one gets mapped.
> * It's not possible to join the same entity more than once.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5404) JPA Translator fails embeddeds
by Harold Campbell (JIRA)
Harold Campbell created TEIID-5404:
--------------------------------------
Summary: JPA Translator fails embeddeds
Key: TEIID-5404
URL: https://issues.jboss.org/browse/TEIID-5404
Project: Teiid
Issue Type: Bug
Components: Misc. Connectors
Reporter: Harold Campbell
Assignee: Steven Hawkins
The JPA translator does not properly handle embedded properties. Columns can overlap if the embedded has properties sharing the same name as the parent or other embedded properties. Come query time, embedded columns do not get translated into correct entity path traversals.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months