October 2018 - 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

1 week, 2 days

2
1
0 / 0

[JBoss JIRA] (TEIID-5525) add a flag to revert to the prior behavior (TEIID-4557)

by Steven Hawkins (Jira)

[ https://issues.jboss.org/browse/TEIID-5525?page=com.atlassian.jira.plugin... ] Work on TEIID-5525 started by Steven Hawkins. --------------------------------------------- > add a flag to revert to the prior behavior (TEIID-4557) > ------------------------------------------------------- > > Key: TEIID-5525 > URL: https://issues.jboss.org/browse/TEIID-5525 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 8.12.14.6_4, 8.12.15.6_4 > Reporter: Debbie Steigner > Assignee: Steven Hawkins > Priority: Major > Fix For: 12.0, 11.2.1 > > > Several customers are seeing errors[1] after https://issues.jboss.org/browse/TEIID-4557 was implemented and generally have to drop the max-active-plans to 1 for their queries to succeed. Can a flag be added to revert to the behavior prior to TEIID-4557? > [1] > ERROR [org.teiid.PROCESSOR] TEIID30019 Unexpected exception for request rcX003UtoyEg.0: org.teiid.core.TeiidComponentException: TEIID31261 Max estimated size 4,963,628,721 for a single operation/table id 85 has been exceeded. The server may need to increase the amount of disk or memory available, or decrease the number of max active plans. -- This message was sent by Atlassian Jira (v7.12.1#712002)

7 years

1
0
0 / 0

[JBoss JIRA] (TEIID-5525) add a flag to revert to the prior behavior (TEIID-4557)

by Steven Hawkins (Jira)

[ https://issues.jboss.org/browse/TEIID-5525?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-5525: ---------------------------------- Fix Version/s: 12.0 11.2.1 > add a flag to revert to the prior behavior (TEIID-4557) > ------------------------------------------------------- > > Key: TEIID-5525 > URL: https://issues.jboss.org/browse/TEIID-5525 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 8.12.14.6_4, 8.12.15.6_4 > Reporter: Debbie Steigner > Assignee: Steven Hawkins > Priority: Major > Fix For: 12.0, 11.2.1 > > > Several customers are seeing errors[1] after https://issues.jboss.org/browse/TEIID-4557 was implemented and generally have to drop the max-active-plans to 1 for their queries to succeed. Can a flag be added to revert to the behavior prior to TEIID-4557? > [1] > ERROR [org.teiid.PROCESSOR] TEIID30019 Unexpected exception for request rcX003UtoyEg.0: org.teiid.core.TeiidComponentException: TEIID31261 Max estimated size 4,963,628,721 for a single operation/table id 85 has been exceeded. The server may need to increase the amount of disk or memory available, or decrease the number of max active plans. -- This message was sent by Atlassian Jira (v7.12.1#712002)

7 years

1
0
0 / 0

[JBoss JIRA] (TEIID-5525) add a flag to revert to the prior behavior (TEIID-4557)

by Debbie Steigner (Jira)

Debbie Steigner created TEIID-5525: -------------------------------------- Summary: add a flag to revert to the prior behavior (TEIID-4557) Key: TEIID-5525 URL: https://issues.jboss.org/browse/TEIID-5525 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 8.12.15.6_4, 8.12.14.6_4 Reporter: Debbie Steigner Assignee: Steven Hawkins Several customers are seeing errors[1] after https://issues.jboss.org/browse/TEIID-4557 was implemented and generally have to drop the max-active-plans to 1 for their queries to succeed. Can a flag be added to revert to the behavior prior to TEIID-4557? [1] ERROR [org.teiid.PROCESSOR] TEIID30019 Unexpected exception for request rcX003UtoyEg.0: org.teiid.core.TeiidComponentException: TEIID31261 Max estimated size 4,963,628,721 for a single operation/table id 85 has been exceeded. The server may need to increase the amount of disk or memory available, or decrease the number of max active plans. -- This message was sent by Atlassian Jira (v7.12.1#712002)

7 years

1
0
0 / 0

[JBoss JIRA] (TEIID-5524) Expose JMX metrics in Thorntail

by Steven Hawkins (Jira)

[ https://issues.jboss.org/browse/TEIID-5524?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-5524: ---------------------------------- Description: Teiid staus and any metrics need to be exposed through JMX, such that they can be exposed later using HAWT.IO console in OpenShift. Given THORN-1918, it may be easiest to just register a few of own mbeans. (was: Teiid staus and any metrics need to be exposed through JMX, such that they can be exposed later using HAWT.IO console in OpenShift.) > Expose JMX metrics in Thorntail > ------------------------------- > > Key: TEIID-5524 > URL: https://issues.jboss.org/browse/TEIID-5524 > Project: Teiid > Issue Type: Enhancement > Components: Embedded > Reporter: Ramesh Reddy > Assignee: Steven Hawkins > Priority: Major > Fix For: 12.0, 11.2.1 > > > Teiid staus and any metrics need to be exposed through JMX, such that they can be exposed later using HAWT.IO console in OpenShift. Given THORN-1918, it may be easiest to just register a few of own mbeans. -- This message was sent by Atlassian Jira (v7.12.1#712002)

7 years

1
0
0 / 0

[JBoss JIRA] (TEIID-5515) Teiid metrics needs to be exposed through JMX

by Steven Hawkins (Jira)

[ https://issues.jboss.org/browse/TEIID-5515?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-5515. ----------------------------------- Resolution: Done Marking the initial issue as resolved as it represents the work necessary in wildfly. TEIID-5524 will take it from here for thorntail. > Teiid metrics needs to be exposed through JMX > --------------------------------------------- > > Key: TEIID-5515 > URL: https://issues.jboss.org/browse/TEIID-5515 > Project: Teiid > Issue Type: Enhancement > Components: Embedded > Reporter: Ramesh Reddy > Assignee: Steven Hawkins > Priority: Major > Fix For: 12.0, 11.2.1 > > > Teiid staus and any metrics need to be exposed through JMX, such that they can be exposed later using HAWT.IO console in OpenShift. -- This message was sent by Atlassian Jira (v7.12.1#712002)

7 years

1
0
0 / 0

[JBoss JIRA] (TEIID-5524) Expose JMX metrics in Thorntail

by Steven Hawkins (Jira)

Steven Hawkins created TEIID-5524: ------------------------------------- Summary: Expose JMX metrics in Thorntail Key: TEIID-5524 URL: https://issues.jboss.org/browse/TEIID-5524 Project: Teiid Issue Type: Enhancement Components: Embedded Reporter: Ramesh Reddy Assignee: Steven Hawkins Fix For: 12.0, 11.2.1 Teiid staus and any metrics need to be exposed through JMX, such that they can be exposed later using HAWT.IO console in OpenShift. -- This message was sent by Atlassian Jira (v7.12.1#712002)

7 years

1
0
0 / 0

[JBoss JIRA] (TEIID-5515) Teiid metrics needs to be exposed through JMX

by Ramesh Reddy (Jira)

[ https://issues.jboss.org/browse/TEIID-5515?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-5515: ------------------------------------- Given my non-Thorntail centric view of Teiid in containerization world nowadays, I vote for independent mbeans. > Teiid metrics needs to be exposed through JMX > --------------------------------------------- > > Key: TEIID-5515 > URL: https://issues.jboss.org/browse/TEIID-5515 > Project: Teiid > Issue Type: Enhancement > Components: Embedded > Reporter: Ramesh Reddy > Assignee: Steven Hawkins > Priority: Major > Fix For: 12.0, 11.2.1 > > > Teiid staus and any metrics need to be exposed through JMX, such that they can be exposed later using HAWT.IO console in OpenShift. -- This message was sent by Atlassian Jira (v7.12.1#712002)

7 years

1
0
0 / 0

[JBoss JIRA] (TEIID-5515) Teiid metrics needs to be exposed through JMX

by Steven Hawkins (Jira)

[ https://issues.jboss.org/browse/TEIID-5515?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-5515: ---------------------------------- Fix Version/s: 11.2.1 > Teiid metrics needs to be exposed through JMX > --------------------------------------------- > > Key: TEIID-5515 > URL: https://issues.jboss.org/browse/TEIID-5515 > Project: Teiid > Issue Type: Enhancement > Components: Embedded > Reporter: Ramesh Reddy > Assignee: Steven Hawkins > Priority: Major > Fix For: 12.0, 11.2.1 > > > Teiid staus and any metrics need to be exposed through JMX, such that they can be exposed later using HAWT.IO console in OpenShift. -- This message was sent by Atlassian Jira (v7.12.1#712002)

7 years

1
0
0 / 0

[JBoss JIRA] (TEIID-5515) Teiid metrics needs to be exposed through JMX

by Steven Hawkins (Jira)

[ https://issues.jboss.org/browse/TEIID-5515?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-5515: --------------------------------------- There is no functioning jmx bridge to wildfly management mbeans in thorntail. This is described in THORN-1918 and I still see that behavior with thorntail 2.3 adding both the jmx and management fractions. So either we can work THORN-1918 or we can just in a targeted way expose some new mbeans. > Teiid metrics needs to be exposed through JMX > --------------------------------------------- > > Key: TEIID-5515 > URL: https://issues.jboss.org/browse/TEIID-5515 > Project: Teiid > Issue Type: Enhancement > Components: Embedded > Reporter: Ramesh Reddy > Assignee: Steven Hawkins > Priority: Major > Fix For: 12.0 > > > Teiid staus and any metrics need to be exposed through JMX, such that they can be exposed later using HAWT.IO console in OpenShift. -- This message was sent by Atlassian Jira (v7.12.1#712002)

7 years

1
0
0 / 0

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues October 2018