April 2020 - teiid-issues - Jboss List Archives

[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

2
1
0 / 0

[JBoss JIRA] (TEIID-5943) Status table not found when model is not visible

by Ramesh Reddy (Jira)

[ https://issues.redhat.com/browse/TEIID-5943?page=com.atlassian.jira.plugi... ] Ramesh Reddy commented on TEIID-5943: ------------------------------------- They were the calls for loading the cache at startup. I do not remember the cross-section between admin and user calls. > Status table not found when model is not visible > ------------------------------------------------ > > Key: TEIID-5943 > URL: https://issues.redhat.com/browse/TEIID-5943 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Reporter: Ramesh Reddy > Assignee: Steven Hawkins > Priority: Major > Time Spent: 1 hour > Remaining Estimate: 0 minutes > > When materialization schema is hidden and the Materilization Status table is part of it, then references to it from Materialization scripts is not found and load auto management fails. > A workaround is simple to make model visible -- This message was sent by Atlassian Jira (v7.13.8#713008)

5 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIIDSB-188) Change rest properties prefix to "spring.teiid.data.rest"

by Ramesh Reddy (Jira)

[ https://issues.redhat.com/browse/TEIIDSB-188?page=com.atlassian.jira.plug... ] Ramesh Reddy resolved TEIIDSB-188. ---------------------------------- Resolution: Done > Change rest properties prefix to "spring.teiid.data.rest" > --------------------------------------------------------- > > Key: TEIIDSB-188 > URL: https://issues.redhat.com/browse/TEIIDSB-188 > Project: Teiid Spring Boot > Issue Type: Quality Risk > Components: core > Reporter: Ramesh Reddy > Assignee: Ramesh Reddy > Priority: Major > Fix For: 1.5.0 > > > To match with other data sources in terms of property prefixes this needs to changed from "spring.teiid.rest" to "spring.teiid.data.rest". -- This message was sent by Atlassian Jira (v7.13.8#713008)

5 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIIDSB-188) Change rest properties prefix to "spring.teiid.data.rest"

by Ramesh Reddy (Jira)

[ https://issues.redhat.com/browse/TEIIDSB-188?focusedWorklogId=12450921&pa... ] Ramesh Reddy logged work on TEIIDSB-188: ---------------------------------------- Author: Ramesh Reddy Created on: 30/Apr/20 1:20 PM Start Date: 30/Apr/20 1:20 PM Worklog Time Spent: 2 hours Issue Time Tracking ------------------- Remaining Estimate: 0 minutes Time Spent: 2 hours Worklog Id: (was: 12450921) > Change rest properties prefix to "spring.teiid.data.rest" > --------------------------------------------------------- > > Key: TEIIDSB-188 > URL: https://issues.redhat.com/browse/TEIIDSB-188 > Project: Teiid Spring Boot > Issue Type: Quality Risk > Components: core > Reporter: Ramesh Reddy > Assignee: Ramesh Reddy > Priority: Major > Fix For: 1.5.0 > > Time Spent: 2 hours > Remaining Estimate: 0 minutes > > To match with other data sources in terms of property prefixes this needs to changed from "spring.teiid.rest" to "spring.teiid.data.rest". -- This message was sent by Atlassian Jira (v7.13.8#713008)

5 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIIDSB-188) Change rest properties prefix to "spring.teiid.data.rest"

by Ramesh Reddy (Jira)

[ https://issues.redhat.com/browse/TEIIDSB-188?page=com.atlassian.jira.plug... ] Work on TEIIDSB-188 started by Ramesh Reddy. -------------------------------------------- > Change rest properties prefix to "spring.teiid.data.rest" > --------------------------------------------------------- > > Key: TEIIDSB-188 > URL: https://issues.redhat.com/browse/TEIIDSB-188 > Project: Teiid Spring Boot > Issue Type: Quality Risk > Components: core > Reporter: Ramesh Reddy > Assignee: Ramesh Reddy > Priority: Major > Fix For: 1.5.0 > > > To match with other data sources in terms of property prefixes this needs to changed from "spring.teiid.rest" to "spring.teiid.data.rest". -- This message was sent by Atlassian Jira (v7.13.8#713008)

5 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5943) Status table not found when model is not visible

by Steven Hawkins (Jira)

[ https://issues.redhat.com/browse/TEIID-5943?page=com.atlassian.jira.plugi... ] Steven Hawkins commented on TEIID-5943: --------------------------------------- I tested this too much in isolation. Without a stacktrace it wasn't clear you were actually talking about queries directly issued by the MaterializationManager - not called through the system admin procedures. Since those are wired in through executeQuery, they appear as user level queries subject to the visibility rules. > Status table not found when model is not visible > ------------------------------------------------ > > Key: TEIID-5943 > URL: https://issues.redhat.com/browse/TEIID-5943 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Reporter: Ramesh Reddy > Assignee: Steven Hawkins > Priority: Major > Time Spent: 1 hour > Remaining Estimate: 0 minutes > > When materialization schema is hidden and the Materilization Status table is part of it, then references to it from Materialization scripts is not found and load auto management fails. > A workaround is simple to make model visible -- This message was sent by Atlassian Jira (v7.13.8#713008)

5 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5943) Status table not found when model is not visible

by Steven Hawkins (Jira)

[ https://issues.redhat.com/browse/TEIID-5943?page=com.atlassian.jira.plugi... ] Steven Hawkins reopened TEIID-5943: ----------------------------------- > Status table not found when model is not visible > ------------------------------------------------ > > Key: TEIID-5943 > URL: https://issues.redhat.com/browse/TEIID-5943 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Reporter: Ramesh Reddy > Assignee: Steven Hawkins > Priority: Major > Time Spent: 1 hour > Remaining Estimate: 0 minutes > > When materialization schema is hidden and the Materilization Status table is part of it, then references to it from Materialization scripts is not found and load auto management fails. > A workaround is simple to make model visible -- This message was sent by Atlassian Jira (v7.13.8#713008)

5 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5947) When hidden resolvable is false user anon dynamic sql should not be able to see hidden objects

by Steven Hawkins (Jira)

Steven Hawkins created TEIID-5947: ------------------------------------- Summary: When hidden resolvable is false user anon dynamic sql should not be able to see hidden objects Key: TEIID-5947 URL: https://issues.redhat.com/browse/TEIID-5947 Project: Teiid Issue Type: Bug Components: Query Engine Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 14.0 Dynamic sql is always resolved against the design time metadata, so hidden objects are effectively visible. -- This message was sent by Atlassian Jira (v7.13.8#713008)

5 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIIDSB-163) Provide pooling for BaseConnectionFactory sources

by Steven Hawkins (Jira)

[ https://issues.redhat.com/browse/TEIIDSB-163?page=com.atlassian.jira.plug... ] Steven Hawkins commented on TEIIDSB-163: ---------------------------------------- Unfortunately there's a lot of other feature work going on, so this does not have sufficient priority yet. > Provide pooling for BaseConnectionFactory sources > ------------------------------------------------- > > Key: TEIIDSB-163 > URL: https://issues.redhat.com/browse/TEIIDSB-163 > Project: Teiid Spring Boot > Issue Type: Feature Request > Components: datasource > Reporter: Steven Hawkins > Priority: Major > Fix For: 1.5.0 > > > It will depend on the source and the cost of creating a connection, but in general we should provide pooling for connections rather than creating the for every operation. For example salesforce is a heavy weight connection. > The pooling will need to be tied into spring security or jca like logic to properly manage connections by identity once we re-establish that logic. -- This message was sent by Atlassian Jira (v7.13.8#713008)

5 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5943) Status table not found when model is not visible

by Steven Hawkins (Jira)

[ https://issues.redhat.com/browse/TEIID-5943?page=com.atlassian.jira.plugi... ] Steven Hawkins resolved TEIID-5943. ----------------------------------- Resolution: Rejected Other than if the status table is left as unqualified, I am unable to reproduce this. If it's qualified, you can reopen. The only incongruity with access restrictions that I see is that when hidden resolvable is false, a normal user query cannot see anything hidden, but an anon dynamic sql can. I'm not sure if that is worth addressing. > Status table not found when model is not visible > ------------------------------------------------ > > Key: TEIID-5943 > URL: https://issues.redhat.com/browse/TEIID-5943 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Reporter: Ramesh Reddy > Assignee: Steven Hawkins > Priority: Major > > When materialization schema is hidden and the Materilization Status table is part of it, then references to it from Materialization scripts is not found and load auto management fails. > A workaround is simple to make model visible -- This message was sent by Atlassian Jira (v7.13.8#713008)

5 years, 5 months

1
0
0 / 0

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues April 2020