[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
1 week, 4 days
[JBoss JIRA] (TEIID-5800) Error in generated links in odata pages
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5800?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5800:
---------------------------------------
Hello Steven,
I have to close the issue. Yesterday, I forgot to update the teiid sources and used sources which were about 3 weeks old. With the current sources I cannot replicate the issue.
> Error in generated links in odata pages
> ---------------------------------------
>
> Key: TEIID-5800
> URL: https://issues.jboss.org/browse/TEIID-5800
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Affects Versions: 13.0
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
>
> The generated urls in the generated odata webpages seem to have a bug. The primary key appears twice here
> expected result:
> http://localhost:18080/odata4/svc/my_nutri_diary/Account(idProfile=1)
> actual result:
> <a:entry>
> <a:id>
> http://localhost:18080/odata4/svc/my_nutri_diary/Account(idProfile=1,idPr...
> </a:id>
> Table definition is given as:
> CREATE FOREIGN TABLE Account (
> idProfile long NOT NULL AUTO_INCREMENT OPTIONS(NAMEINSOURCE '"idProfile"', NATIVE_TYPE 'BIGINT'),
> uuidUser string(36) NOT NULL OPTIONS(NAMEINSOURCE '"uuidUser"', NATIVE_TYPE 'CHAR'),
> CONSTRAINT "PRIMARY" PRIMARY KEY(idProfile),
> CONSTRAINT uuidUser_UNIQUE UNIQUE(uuidUser)
> ) OPTIONS(NAMEINSOURCE '"Account"', UPDATABLE 'TRUE', CARDINALITY '2');
> I have used the github sources from yesterday.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
6 years, 3 months
[JBoss JIRA] (TEIID-5799) GENERATED_KEY cannot resolve primary key, if created via insert trigger on view
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5799?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5799:
---------------------------------------
Hello Steven,
I did assamble a compose file for you with teiid and a mysql database holding test data, to show the issue. I also added 3 curl scripts with POST requests which show the bugs in the three relevant code paths in the "CREATE TRIGGER ON Diary INSTEAD OF INSERT AS" code.
By building the compose file, I observed a quite weird behaviour. Therefore I am currently assuming, that the observed bug might be runtime related.
My full installation at home includes a keycloak server for oauth and permission handling in addition to what I packaged into the compose file. For my home install, the provided scripts 1 and 2 are working fine. Hence, I do not have issues with the first 2 code paths of the INSERT trigger.
The only difference in the vdb, compared to the one provided in this example is, that here user odata has simply all permissions. Hence, the vdb is identical except for the grants part of the vdb.
What I do not understand is, that scripts 1 and 2 in this provided example are not working. Here I also get
"TEIID16016 Insert into Diary success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values."
I furthermore observed, that in my full installation, if I copy the IF part of the code into ELSE IF (new.fkDatabaseKey = 3), (which should be working code if the correct product ID is provided to the insert (attention foreign key constraints)) than I also observe the mentioned error.
To some up, due to these facts I assume something runtime related to be wrong here. You find the compose file alongside with a detail howto README at:
https://c.gmx.net/@328076122686231878/d2XSupdWRYmtR_WOFjhHQQ
> GENERATED_KEY cannot resolve primary key, if created via insert trigger on view
> -------------------------------------------------------------------------------
>
> Key: TEIID-5799
> URL: https://issues.jboss.org/browse/TEIID-5799
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 13.0
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Blocker
> Attachments: svc-vdb-example.ddl
>
>
> Hello Steven,
> I just ran into the next issue with the generated key functionality. Attached you find an example. The relevant section is given in
> CREATE TRIGGER ON Diary INSTEAD OF INSERT AS
> with the block following:
> ELSE IF (new.fkDatabaseKey = 3)
> In the insert trigger I am trying to duplicate a record from table "UserDefinedProducts" and reference it in the row to be created from the trigger.
> I again the the error message:
> POST on 'Diary' failed; will be repeated automatically - Error: TEIID16016 Insert into Diary success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values.
> I am using the most recent sources checked out about an hour ago.
> Do you see a bug in my code, or is it again an issue I have hit? Thanks for your help!
> Let me know if I shall assemble a docker-compose file for you to debug the issue.
> Update: As I seem to be not able to delete attachments here. One further note. My example hat a permission error in one line where I have red from the wrong table. It should instead be:
> SELECT * INTO #tmpItem FROM UserDefinedProductsOfAllUsers WHERE fkProduct = new.fkProduct LIMIT 1;
> However, the previously described error stays the same.
> Best regards,
> Christoph
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
6 years, 3 months
[JBoss JIRA] (TEIID-5799) GENERATED_KEY cannot resolve primary key, if created via insert trigger on view
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5799?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5799:
---------------------------------------
Hello Steven, the relevant post holds all the relevant data
{"fkProfile":"1","AddedDateTime":"2019-07-30T23:07:08+02:00","fkProduct":"657815","fkDatabaseKey":3,"MealNumber":"5","AmountInG":100}
However, it seems there got something messed up with the generated key retrieval in the recent sources. I am just trying to setup a compose file and hope that I will be able to deliver more feedback tomorrow. I am not sure what is going one, but the same ddl file working in one teiid setup, does not work in the next one. More on that tomorrow.
Best regards,
Christoph
> GENERATED_KEY cannot resolve primary key, if created via insert trigger on view
> -------------------------------------------------------------------------------
>
> Key: TEIID-5799
> URL: https://issues.jboss.org/browse/TEIID-5799
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 13.0
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Blocker
> Attachments: svc-vdb-example.ddl
>
>
> Hello Steven,
> I just ran into the next issue with the generated key functionality. Attached you find an example. The relevant section is given in
> CREATE TRIGGER ON Diary INSTEAD OF INSERT AS
> with the block following:
> ELSE IF (new.fkDatabaseKey = 3)
> In the insert trigger I am trying to duplicate a record from table "UserDefinedProducts" and reference it in the row to be created from the trigger.
> I again the the error message:
> POST on 'Diary' failed; will be repeated automatically - Error: TEIID16016 Insert into Diary success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values.
> I am using the most recent sources checked out about an hour ago.
> Do you see a bug in my code, or is it again an issue I have hit? Thanks for your help!
> Let me know if I shall assemble a docker-compose file for you to debug the issue.
> Update: As I seem to be not able to delete attachments here. One further note. My example hat a permission error in one line where I have red from the wrong table. It should instead be:
> SELECT * INTO #tmpItem FROM UserDefinedProductsOfAllUsers WHERE fkProduct = new.fkProduct LIMIT 1;
> However, the previously described error stays the same.
> Best regards,
> Christoph
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
6 years, 3 months
[JBoss JIRA] (TEIID-5800) Error in generated links in odata pages
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5800?page=com.atlassian.jira.plugin... ]
Christoph John updated TEIID-5800:
----------------------------------
Description:
The generated urls in the generated odata webpages seem to have a bug. The primary key appears twice here
expected result:
http://localhost:18080/odata4/svc/my_nutri_diary/Account(idProfile=1)
actual result:
<a:entry>
<a:id>
http://localhost:18080/odata4/svc/my_nutri_diary/Account(idProfile=1,idPr...
</a:id>
Table definition is given as:
CREATE FOREIGN TABLE Account (
idProfile long NOT NULL AUTO_INCREMENT OPTIONS(NAMEINSOURCE '"idProfile"', NATIVE_TYPE 'BIGINT'),
uuidUser string(36) NOT NULL OPTIONS(NAMEINSOURCE '"uuidUser"', NATIVE_TYPE 'CHAR'),
CONSTRAINT "PRIMARY" PRIMARY KEY(idProfile),
CONSTRAINT uuidUser_UNIQUE UNIQUE(uuidUser)
) OPTIONS(NAMEINSOURCE '"Account"', UPDATABLE 'TRUE', CARDINALITY '2');
I have used the github sources from yesterday.
was:
The generated urls in the generated odata webpages seem to have a bug. The primary key appears twice here
expected result:
http://localhost:18080/odata4/svc/my_nutri_diary/Account(idProfile=1)
actual result:
<a:entry>
<a:id>
http://localhost:18080/odata4/svc/my_nutri_diary/Account(idProfile=1,idPr...
</a:id>
I have used the github sources from yesterday.
> Error in generated links in odata pages
> ---------------------------------------
>
> Key: TEIID-5800
> URL: https://issues.jboss.org/browse/TEIID-5800
> Project: Teiid
> Issue Type: Bug
> Components: OData
> Affects Versions: 13.0
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
>
> The generated urls in the generated odata webpages seem to have a bug. The primary key appears twice here
> expected result:
> http://localhost:18080/odata4/svc/my_nutri_diary/Account(idProfile=1)
> actual result:
> <a:entry>
> <a:id>
> http://localhost:18080/odata4/svc/my_nutri_diary/Account(idProfile=1,idPr...
> </a:id>
> Table definition is given as:
> CREATE FOREIGN TABLE Account (
> idProfile long NOT NULL AUTO_INCREMENT OPTIONS(NAMEINSOURCE '"idProfile"', NATIVE_TYPE 'BIGINT'),
> uuidUser string(36) NOT NULL OPTIONS(NAMEINSOURCE '"uuidUser"', NATIVE_TYPE 'CHAR'),
> CONSTRAINT "PRIMARY" PRIMARY KEY(idProfile),
> CONSTRAINT uuidUser_UNIQUE UNIQUE(uuidUser)
> ) OPTIONS(NAMEINSOURCE '"Account"', UPDATABLE 'TRUE', CARDINALITY '2');
> I have used the github sources from yesterday.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
6 years, 3 months