February 2015 - 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

3 weeks, 1 day

2
1
0 / 0

[JBoss JIRA] (TEIID-3361) Add source query details to the command log

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-3361: ------------------------------------- Summary: Add source query details to the command log Key: TEIID-3361 URL: https://issues.jboss.org/browse/TEIID-3361 Project: Teiid Issue Type: Enhancement Components: Misc. Connectors Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 8.11 The command log for convenience only contains the Teiid sql form of the source query. It would be good to have the translators also add their actual form of the source query when possible to the command log just to have a single place to track things down. Currently this info is logged to the CONNECTOR context inconsistently in each translator. -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3360) Provide an option to virtualize source exceptions

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-3360: ------------------------------------- Summary: Provide an option to virtualize source exceptions Key: TEIID-3360 URL: https://issues.jboss.org/browse/TEIID-3360 Project: Teiid Issue Type: Enhancement Components: Query Engine Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 8.11, Open To Community Currently we'll pass the source exception along with the Teiid exception. And from source sql exceptions we'll also rely the sql state/code. When connecting to databases of different types it would be best to return a common set of codes. There is exception mapping logic in Hibernate that we could reuse, but this could apply to the non-jdbc sources such that a predictable code will seen by Teiid clients. -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3359) Allow more control over odata layer caching

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-3359: ------------------------------------- Summary: Allow more control over odata layer caching Key: TEIID-3359 URL: https://issues.jboss.org/browse/TEIID-3359 Project: Teiid Issue Type: Enhancement Components: OData Affects Versions: 8.7 Reporter: Steven Hawkins Fix For: 8.11 The caching is always performed at a user level and for each query. Consumers may need to localize the caching affect only to paging results and scope only to that interaction, rather than for all users. -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3358) Issues with entity set names

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-3358: ------------------------------------- Summary: Issues with entity set names Key: TEIID-3358 URL: https://issues.jboss.org/browse/TEIID-3358 Project: Teiid Issue Type: Bug Components: OData Affects Versions: 8.7 Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 8.11 The entity set name used for sql generation should be the fully qualified name. If there is for example two table with the same names in schemas visible to odata, but one of the tables does not have a key, then an ambiguous name exception will be thrown if an odata url is used with only the base table name. It also appears that the URI link in the results metadata uses the non-qualified table name, which will have issues in the scenario above. We may also need to document or add an additional check for ambiguity as the OData4j findEdmEntitySet logic will return the first matching entity, which is generally against our approach to resolving. -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3357) Add the final processor plan to the command log

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-3357: ------------------------------------- Summary: Add the final processor plan to the command log Key: TEIID-3357 URL: https://issues.jboss.org/browse/TEIID-3357 Project: Teiid Issue Type: Enhancement Components: Query Engine Affects Versions: 8.11 Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 8.11 We should add the plan output not just once planning has completed, but when processing has completed as well so that the node statistics have been populated. -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-1741) Record level audit trail in teiid log files

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-1741?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-1741. ----------------------------------- Assignee: Steven Hawkins Fix Version/s: (was: Open To Community) Resolution: Done Added a sandbox translator as another demonstration of what can be done with the delegating translator. https://github.com/teiid/teiid/blob/master/connectors/sandbox/translator-... It is only an example. From the request here it looks like additional considerations are - not all results would need to be logged (only columns considered auditable - although that can be a somewhat problematic concept for a more complex pushdown query), what information to include from the execution context, etc. > Record level audit trail in teiid log files > ------------------------------------------- > > Key: TEIID-1741 > URL: https://issues.jboss.org/browse/TEIID-1741 > Project: Teiid > Issue Type: Feature Request > Components: Connector API, Query Engine > Reporter: Monika Ahuja > Assignee: Steven Hawkins > Fix For: 8.11 > > > In order to track the audit trail for patient identified information access, we would like to track the executed queries, results as well as the timestamps for each user's query and its result. Essentially we need a report providing the list of Medical Record Numbers accessed by each user id and I think we can use the Logs to track that (unless you suggest a better way). -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3355) Improve recommendations so that they translate to configuring server

by Thomas Hauser (JIRA)

[ https://issues.jboss.org/browse/TEIID-3355?page=com.atlassian.jira.plugin... ] Thomas Hauser commented on TEIID-3355: -------------------------------------- It would be great to have this information, since if we also have an API / recommend that the user use the tool first, the values can be tweaked during installation. This reduces post-install configuration and streamlines the experience, whether in a production setting or not. > Improve recommendations so that they translate to configuring server > -------------------------------------------------------------------- > > Key: TEIID-3355 > URL: https://issues.jboss.org/browse/TEIID-3355 > Project: Teiid > Issue Type: Enhancement > Components: Sizing Application > Reporter: Van Halbert > Assignee: Kylin Soong > > Improve the answers so that any configuration recommendations are provided. This might include (not all encompassing): > - teiid max threads > - teiid max plans > - data source connection max pool size > ?? > The goal here being that this information can be directly translated for configuring the server that we're recommending. -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3356) Minor text issues with the sizing tool

by Van Halbert (JIRA)

[ https://issues.jboss.org/browse/TEIID-3356?page=com.atlassian.jira.plugin... ] Van Halbert reassigned TEIID-3356: ---------------------------------- Assignee: Kylin Soong (was: Steven Hawkins) > Minor text issues with the sizing tool > -------------------------------------- > > Key: TEIID-3356 > URL: https://issues.jboss.org/browse/TEIID-3356 > Project: Teiid > Issue Type: Bug > Components: Sizing Application > Affects Versions: 8.9.1 > Reporter: Thomas Hauser > Assignee: Kylin Soong > Priority: Minor > > There are a few minor issues with some of the text in the tool. > I'll enumerate them below: > CPU Core Estimation: > 2. What is the average row could from each physical source? > The word "could" is probably meant to be "count". Whatever the solution, it's not meant to be "could" :D > 2nd Paragraph of the "Red Hat Enterprise Linux is Recommended" section at the end, 2nd sentence: > Red Hat Ente on other modern processor architectures. > This is a sentence fragment and looks like a cut-off or something. > I didn't see any others, sweet tool! -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3355) Improve recommendations so that they translate to configuring server

by Van Halbert (JIRA)

[ https://issues.jboss.org/browse/TEIID-3355?page=com.atlassian.jira.plugin... ] Van Halbert reassigned TEIID-3355: ---------------------------------- Assignee: Kylin Soong (was: Steven Hawkins) > Improve recommendations so that they translate to configuring server > -------------------------------------------------------------------- > > Key: TEIID-3355 > URL: https://issues.jboss.org/browse/TEIID-3355 > Project: Teiid > Issue Type: Enhancement > Components: Sizing Application > Reporter: Van Halbert > Assignee: Kylin Soong > > Improve the answers so that any configuration recommendations are provided. This might include (not all encompassing): > - teiid max threads > - teiid max plans > - data source connection max pool size > ?? > The goal here being that this information can be directly translated for configuring the server that we're recommending. -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues February 2015