June 2019 - 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

6 days, 6 hours

2
1
0 / 0

[JBoss JIRA] (TEIID-5785) Remove Infinispan as a datasource from the community

by Ramesh Reddy (Jira)

[ https://issues.jboss.org/browse/TEIID-5785?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-5785: ------------------------------------- I did meant for product, why cve fix for community? BTW what is the issue with upgrade? > Remove Infinispan as a datasource from the community > ---------------------------------------------------- > > Key: TEIID-5785 > URL: https://issues.jboss.org/browse/TEIID-5785 > Project: Teiid > Issue Type: Task > Components: Misc. Connectors > Affects Versions: 12.3 > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Major > -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5785) Remove Infinispan as a datasource from the community

by Van Halbert (Jira)

[ https://issues.jboss.org/browse/TEIID-5785?page=com.atlassian.jira.plugin... ] Van Halbert commented on TEIID-5785: ------------------------------------ Ok, so you're saying just remove infinispan from the community build? This is about community, not product. The product build does exclude it. But when I'm trying to push the CVE fix upstream, it now impacts infinispan and that needs to be upgraded. > Remove Infinispan as a datasource from the community > ---------------------------------------------------- > > Key: TEIID-5785 > URL: https://issues.jboss.org/browse/TEIID-5785 > Project: Teiid > Issue Type: Task > Components: Misc. Connectors > Affects Versions: 12.3 > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Major > -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5785) Remove Infinispan as a datasource from the community

by Ramesh Reddy (Jira)

[ https://issues.jboss.org/browse/TEIID-5785?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-5785: ------------------------------------- I do not understand why can't you simply exclude the modules from the build, then that should solve your issue right? Am I missing some detail perhaps? > Remove Infinispan as a datasource from the community > ---------------------------------------------------- > > Key: TEIID-5785 > URL: https://issues.jboss.org/browse/TEIID-5785 > Project: Teiid > Issue Type: Task > Components: Misc. Connectors > Affects Versions: 12.3 > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Major > -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5785) Remove Infinispan as a datasource from the community

by Van Halbert (Jira)

[ https://issues.jboss.org/browse/TEIID-5785?page=com.atlassian.jira.plugin... ] Van Halbert commented on TEIID-5785: ------------------------------------ Then that changes what we discussed. I'm trying to fix the cve upstream so that the next 12.x version doesn't have the issue. However, in order to fix it, I need to upgrade infinispan to 9.4. Unfortunately, in doing so, its now generating an exception that was seen before (i.e., marshaller not found). > Remove Infinispan as a datasource from the community > ---------------------------------------------------- > > Key: TEIID-5785 > URL: https://issues.jboss.org/browse/TEIID-5785 > Project: Teiid > Issue Type: Task > Components: Misc. Connectors > Affects Versions: 12.3 > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Major > -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5785) Remove Infinispan as a datasource from the community

by Van Halbert (Jira)

[ https://issues.jboss.org/browse/TEIID-5785?page=com.atlassian.jira.plugin... ] Van Halbert edited comment on TEIID-5785 at 6/28/19 4:17 PM: ------------------------------------------------------------- [~rareddy] Then that changes what we discussed. I'm trying to fix the cve upstream so that the next 12.x version doesn't have the issue. However, in order to fix it, I need to upgrade infinispan to 9.4. Unfortunately, in doing so, its now generating an exception that was seen before (i.e., marshaller not found). was (Author: rhn-engineering-vhalbert): Then that changes what we discussed. I'm trying to fix the cve upstream so that the next 12.x version doesn't have the issue. However, in order to fix it, I need to upgrade infinispan to 9.4. Unfortunately, in doing so, its now generating an exception that was seen before (i.e., marshaller not found). > Remove Infinispan as a datasource from the community > ---------------------------------------------------- > > Key: TEIID-5785 > URL: https://issues.jboss.org/browse/TEIID-5785 > Project: Teiid > Issue Type: Task > Components: Misc. Connectors > Affects Versions: 12.3 > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Major > -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5785) Remove Infinispan as a datasource from the community

by Ramesh Reddy (Jira)

[ https://issues.jboss.org/browse/TEIID-5785?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-5785: ------------------------------------- [~rhn-engineering-vhalbert] you should exclude from the product profile build not remove it. > Remove Infinispan as a datasource from the community > ---------------------------------------------------- > > Key: TEIID-5785 > URL: https://issues.jboss.org/browse/TEIID-5785 > Project: Teiid > Issue Type: Task > Components: Misc. Connectors > Affects Versions: 12.3 > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Major > -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4251) Built in support for Postgres DB as materialization target

by Steven Hawkins (Jira)

[ https://issues.jboss.org/browse/TEIID-4251?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-4251: --------------------------------------- > I thought they were based on the status table times Not at start-up, we just schedule a job based upon the ttl. Obviously that wouldn't be too hard to correct. > We really do not need coordination among nodes as if you use cental PG store right? That is correct. You make the single pg instance the master / owner of the materialization process. Things may get more complicated if at scale (or when considering multi-tenant) we need a pg cluster. > Built in support for Postgres DB as materialization target > ---------------------------------------------------------- > > Key: TEIID-4251 > URL: https://issues.jboss.org/browse/TEIID-4251 > Project: Teiid > Issue Type: Feature Request > Components: Server > Reporter: Ramesh Reddy > Assignee: Steven Hawkins > Priority: Major > Fix For: 12.3 > > > If Postgres database is available along with install or assumed that it is available, then some of the materialization task can be automated, like > - Creation of a common STATUS table > - Creation of the materilization targets (create views on dbms) > - On load, on undeploy and load scripts for all the materialization views > We need to device a way this to be pluggable, such that based on success of this, we can provide additional support for other sources. -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5785) Remove Infinispan as a datasource from the community

by Van Halbert (Jira)

[ https://issues.jboss.org/browse/TEIID-5785?page=com.atlassian.jira.plugin... ] Van Halbert reassigned TEIID-5785: ---------------------------------- Assignee: Van Halbert (was: Steven Hawkins) > Remove Infinispan as a datasource from the community > ---------------------------------------------------- > > Key: TEIID-5785 > URL: https://issues.jboss.org/browse/TEIID-5785 > Project: Teiid > Issue Type: Task > Components: Misc. Connectors > Affects Versions: 12.3 > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Major > -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 4 months

1
0
0 / 0

[JBoss JIRA] (TEIID-5785) Remove Infinispan as a datasource from the community

by Van Halbert (Jira)

[ https://issues.jboss.org/browse/TEIID-5785?page=com.atlassian.jira.plugin... ] Work on TEIID-5785 started by Van Halbert. ------------------------------------------ > Remove Infinispan as a datasource from the community > ---------------------------------------------------- > > Key: TEIID-5785 > URL: https://issues.jboss.org/browse/TEIID-5785 > Project: Teiid > Issue Type: Task > Components: Misc. Connectors > Affects Versions: 12.3 > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Major > -- This message was sent by Atlassian Jira (v7.12.1#712002)

6 years, 4 months

1
0
0 / 0

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues June 2019