September 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

2 weeks, 4 days

2
1
0 / 0

[JBoss JIRA] (TEIID-3735) Add data source support for reading PDF documents

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3735?page=com.atlassian.jira.plugin... ] Steven Hawkins reassigned TEIID-3735: ------------------------------------- Fix Version/s: 9.x Assignee: (was: Steven Hawkins) Bucketing as further off. Still need direction such as what is indicated in TEIID-3693. > Add data source support for reading PDF documents > -------------------------------------------------- > > Key: TEIID-3735 > URL: https://issues.jboss.org/browse/TEIID-3735 > Project: Teiid > Issue Type: Feature Request > Components: Misc. Connectors > Reporter: Van Halbert > Fix For: 9.x > > > Add data source support for reading PDF documents -- This message was sent by Atlassian JIRA (v6.4.11#64026)

10 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-3733) Add support for web scraping

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3733?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-3733: ---------------------------------- Fix Version/s: 9.x > Add support for web scraping > ---------------------------- > > Key: TEIID-3733 > URL: https://issues.jboss.org/browse/TEIID-3733 > Project: Teiid > Issue Type: Feature Request > Components: Misc. Connectors > Reporter: Van Halbert > Fix For: 9.x > > > Add support for web scraping. > Here's one from CA using JSoup - https://github.com/rokhmanov/teiid-translators/blob/master/translator-scr... -- This message was sent by Atlassian JIRA (v6.4.11#64026)

10 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-3733) Add support for web scraping

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3733?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-3733: --------------------------------------- Note the jsoup example will extract based upon the jsoup selector - http://jsoup.org/apidocs/org/jsoup/select/Selector.html which is a css like selector syntax. This is somewhat idiomatic to jsoup and the results are simple the set of selected elements - and component information such as inner_text, tag name, id, etc. are returned in the result. For any usage scenarios more logic would be needed to transform the result, and this would not handle tabular data well (at best assuming that you could somewhat easily identify a single html table to extract, you would read the rows, then for each row use the soup extraction again to extract the columns - then a pivot would be needed. however that may not work well in practice unless the table is regular. missing or spanning values would likely be an issue). > Add support for web scraping > ---------------------------- > > Key: TEIID-3733 > URL: https://issues.jboss.org/browse/TEIID-3733 > Project: Teiid > Issue Type: Feature Request > Components: Misc. Connectors > Reporter: Van Halbert > Assignee: Steven Hawkins > > Add support for web scraping. > Here's one from CA using JSoup - https://github.com/rokhmanov/teiid-translators/blob/master/translator-scr... -- This message was sent by Atlassian JIRA (v6.4.11#64026)

10 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-3733) Add support for web scraping

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3733?page=com.atlassian.jira.plugin... ] Steven Hawkins reassigned TEIID-3733: ------------------------------------- Assignee: (was: Steven Hawkins) > Add support for web scraping > ---------------------------- > > Key: TEIID-3733 > URL: https://issues.jboss.org/browse/TEIID-3733 > Project: Teiid > Issue Type: Feature Request > Components: Misc. Connectors > Reporter: Van Halbert > > Add support for web scraping. > Here's one from CA using JSoup - https://github.com/rokhmanov/teiid-translators/blob/master/translator-scr... -- This message was sent by Atlassian JIRA (v6.4.11#64026)

10 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-3734) Add data source support for reading DOC documents

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3734?page=com.atlassian.jira.plugin... ] Steven Hawkins reassigned TEIID-3734: ------------------------------------- Component/s: Misc. Connectors Fix Version/s: 9.x Assignee: (was: Steven Hawkins) Bucketing as further off. Still need direction such as what is indicated in TEIID-3693. > Add data source support for reading DOC documents > ------------------------------------------------- > > Key: TEIID-3734 > URL: https://issues.jboss.org/browse/TEIID-3734 > Project: Teiid > Issue Type: Feature Request > Components: Misc. Connectors > Reporter: Van Halbert > Fix For: 9.x > > > Add data source support for reading DOC documents. > The Apache POI could be an option, as it has support for OLE2 document format. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

10 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-3734) Add data source support for reading DOC documents

by Van Halbert (JIRA)

[ https://issues.jboss.org/browse/TEIID-3734?page=com.atlassian.jira.plugin... ] Van Halbert updated TEIID-3734: ------------------------------- Summary: Add data source support for reading DOC documents (was: Add data source support for reading POC documents) > Add data source support for reading DOC documents > ------------------------------------------------- > > Key: TEIID-3734 > URL: https://issues.jboss.org/browse/TEIID-3734 > Project: Teiid > Issue Type: Feature Request > Reporter: Van Halbert > Assignee: Steven Hawkins > > Add data source support for reading POC documents. > The Apache POI could be an option, as it has support for OLE2 document format. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

10 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-3734) Add data source support for reading DOC documents

by Van Halbert (JIRA)

[ https://issues.jboss.org/browse/TEIID-3734?page=com.atlassian.jira.plugin... ] Van Halbert updated TEIID-3734: ------------------------------- Description: Add data source support for reading DOC documents. The Apache POI could be an option, as it has support for OLE2 document format. was: Add data source support for reading POC documents. The Apache POI could be an option, as it has support for OLE2 document format. > Add data source support for reading DOC documents > ------------------------------------------------- > > Key: TEIID-3734 > URL: https://issues.jboss.org/browse/TEIID-3734 > Project: Teiid > Issue Type: Feature Request > Reporter: Van Halbert > Assignee: Steven Hawkins > > Add data source support for reading DOC documents. > The Apache POI could be an option, as it has support for OLE2 document format. -- This message was sent by Atlassian JIRA (v6.4.11#64026)

10 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-3581) Add a system function to return Teiid Server Name

by Ramesh Reddy (JIRA)

[ https://issues.jboss.org/browse/TEIID-3581?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-3581: ------------------------------------- I think we can add as part of the session properties, beyond that it should be up to the user to grab those using the Admin methods etc to get to the Node name IMO > Add a system function to return Teiid Server Name > ------------------------------------------------- > > Key: TEIID-3581 > URL: https://issues.jboss.org/browse/TEIID-3581 > Project: Teiid > Issue Type: Enhancement > Components: Server > Reporter: Van Halbert > Fix For: 8.12.1 > > > A user has a 4 node Cluster and would like to get not only the sessionid, vdb and user but the node of the cluster that the user is connected to -- This message was sent by Atlassian JIRA (v6.4.11#64026)

10 years, 1 month

1
0
0 / 0

[JBoss JIRA] (TEIID-3581) Add a system function to return Teiid Server Name

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-3581?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-3581: --------------------------------------- I don't think I was explicit enough. The main question was whether this seems worth a system function? And if so, then for the embedded case are we saying something like we'll add an EmbeddedConfiguration field for node id or would we expect the system property to be set? > Add a system function to return Teiid Server Name > ------------------------------------------------- > > Key: TEIID-3581 > URL: https://issues.jboss.org/browse/TEIID-3581 > Project: Teiid > Issue Type: Enhancement > Components: Server > Reporter: Van Halbert > Fix For: 8.12.1 > > > A user has a 4 node Cluster and would like to get not only the sessionid, vdb and user but the node of the cluster that the user is connected to -- This message was sent by Atlassian JIRA (v6.4.11#64026)

10 years, 1 month

1
0
0 / 0

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues September 2015