April 2014 - 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

22 hours, 17 minutes

2
1
0 / 0

[JBoss JIRA] (TEIID-2943) getIndexInfo is not correct

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-2943?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-2943. ----------------------------------- Resolution: Done Corrected the type and unique flag handling. TEIID-2944 was logged to add cardinality information as well. > getIndexInfo is not correct > --------------------------- > > Key: TEIID-2943 > URL: https://issues.jboss.org/browse/TEIID-2943 > Project: Teiid > Issue Type: Bug > Components: JDBC Driver > Affects Versions: 7.7 > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Fix For: 8.7.1, 8.8 > > > DatabaseMetaData.getIndexInfo returns 0 (statistical index) for all indexes. Also the unique flag is not properly honored. -- This message was sent by Atlassian JIRA (v6.2.3#6260)

11 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2944) getIndexInfo should return statistical information

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-2944: ------------------------------------- Summary: getIndexInfo should return statistical information Key: TEIID-2944 URL: https://issues.jboss.org/browse/TEIID-2944 Project: Teiid Issue Type: Enhancement Components: JDBC Driver Reporter: Steven Hawkins Assignee: Steven Hawkins DatabaseMetaData.getIndexInfo can return statistical indexes with cardinality information and/or cardinality can be returned on the other index info for the table. -- This message was sent by Atlassian JIRA (v6.2.3#6260)

11 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2922) Extra unique constraint in dynamic vdb schema

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-2922?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-2922. ----------------------------------- Resolution: Done Added a validation that constraint names (if given) need to be unique. Also added logic to the jdbc import to prevent the primary key from also being imported as an index. > Extra unique constraint in dynamic vdb schema > --------------------------------------------- > > Key: TEIID-2922 > URL: https://issues.jboss.org/browse/TEIID-2922 > Project: Teiid > Issue Type: Bug > Components: AdminApi > Affects Versions: 8.4 > Reporter: Mark Drilling > Assignee: Steven Hawkins > Fix For: 8.8 > > > I'm getting an extra Unique Constraint in the dynamic VDB schema - for a MySQL table with only a primary key. See steps to reproduce. -- This message was sent by Atlassian JIRA (v6.2.3#6260)

11 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2943) getIndexInfo is not correct

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-2943: ------------------------------------- Summary: getIndexInfo is not correct Key: TEIID-2943 URL: https://issues.jboss.org/browse/TEIID-2943 Project: Teiid Issue Type: Bug Components: JDBC Driver Affects Versions: 7.7 Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 8.7.1, 8.8 DatabaseMetaData.getIndexInfo returns 0 (statistical index) for all indexes. Also the unique flag is not properly honored. -- This message was sent by Atlassian JIRA (v6.2.3#6260)

11 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2814) HIVE: quote is removed for TIMESTAMP field in where clause

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-2814?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-2814: --------------------------------------- Ivan, If you are still having issues with timestamp comparison after 0.13, please open up a new issue and we'll figure out what translator changes would be needed. > HIVE: quote is removed for TIMESTAMP field in where clause > ----------------------------------------------------------- > > Key: TEIID-2814 > URL: https://issues.jboss.org/browse/TEIID-2814 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 8.6 > Environment: HIVE and Teiid 8.6 > Reporter: Ivan Chan > Assignee: Steven Hawkins > Labels: teiid > Fix For: 8.4.2, 8.7 > > > I am running teiid 8.6 against HIVE. And I found out that quote was removed for TIMESTAMP field in where clause. And it caused java.sql.SQLException: Error while processing statement: FAILED: ParseException line 1:88 missing EOF > Original SQL: > select "the_date", > "store_sales" > from "hive2"."sales_seq" > where ("the_date" > '1970-05-16 00:10:04') > Teiid generated SQL: > SELECT g_0.the_date, g_0.store_sales FROM sales_seq g_0 WHERE g_0.the_date > 1970-05-16 00:10:04.0 > Exception: > Caused by: java.sql.SQLException: Error while processing statement: FAILED: > ParseException line 1:88 missing EOF at '00' near '16' -- This message was sent by Atlassian JIRA (v6.2.3#6260)

11 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2922) Extra unique constraint in dynamic vdb schema

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-2922?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-2922: ---------------------------------- Fix Version/s: 8.8 I think that it does make sense to ensure named constraints have a unique name (at the table scope), so we'll add a validation rule on our side for that. This also means that we will need to address mysql returning the primary key in two ways. > Extra unique constraint in dynamic vdb schema > --------------------------------------------- > > Key: TEIID-2922 > URL: https://issues.jboss.org/browse/TEIID-2922 > Project: Teiid > Issue Type: Bug > Components: AdminApi > Affects Versions: 8.4 > Reporter: Mark Drilling > Assignee: Steven Hawkins > Fix For: 8.8 > > > I'm getting an extra Unique Constraint in the dynamic VDB schema - for a MySQL table with only a primary key. See steps to reproduce. -- This message was sent by Atlassian JIRA (v6.2.3#6260)

11 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2942) Enhanced sort join produces duplicate results

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-2942?page=com.atlassian.jira.plugin... ] Steven Hawkins resolved TEIID-2942. ----------------------------------- Resolution: Done Corrected the indexing logic to also account for sort distinct. > Enhanced sort join produces duplicate results > --------------------------------------------- > > Key: TEIID-2942 > URL: https://issues.jboss.org/browse/TEIID-2942 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 8.4 > Reporter: Steven Hawkins > Assignee: Steven Hawkins > Priority: Critical > Fix For: 8.7.1, 8.8 > > > In a plan where an enhanced sort join chooses to create an index on a child that should be SORT_DISTINCT, the distinct processing does not occur. > For example this would occur with a query such as: > select a.e1, b.e2 from pm1.g1 as a, (select e1, e2 from pm2.g2 union select e1, e2 from pm2.g2) as b where a.e1 = b.e1 > where the source does not support union. -- 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

11 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2937) Change Apache POI version to 3.10

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-2937?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-2937: --------------------------------------- Now I'm seeing that the tech preview repository redirects to https, but they have a bad cert which will choke the build as well: Failed to read artifact descriptor for org.apache.poi:poi:jar:3.10: Could not transfer artifact org.apache.poi:poi:pom:3.10 from/to redhat-techpreview-all-repository (http://maven.repository.redhat.com/techpreview/all/): hostname in certificate didn't match: <maven.repository.redhat.com> != <a248.e.akamai.net> OR <*.akamaihd.net> OR <*.akamaihd-staging.net> OR <a248.e.akamai.net> > Change Apache POI version to 3.10 > --------------------------------- > > Key: TEIID-2937 > URL: https://issues.jboss.org/browse/TEIID-2937 > Project: Teiid > Issue Type: Enhancement > Components: Misc. Connectors > Affects Versions: 8.7 > Reporter: Van Halbert > Assignee: Ramesh Reddy > Fix For: 8.7.1, 8.8 > > > Upgrade the Apache POI version used in accessing Excel file to use POI 3.10. -- 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

11 years, 5 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2942) Enhanced sort join produces duplicate results

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-2942: ------------------------------------- Summary: Enhanced sort join produces duplicate results Key: TEIID-2942 URL: https://issues.jboss.org/browse/TEIID-2942 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 8.4 Reporter: Steven Hawkins Assignee: Steven Hawkins Priority: Critical Fix For: 8.7.1, 8.8 In a plan where an enhanced sort join chooses to create an index on a child that should be SORT_DISTINCT, the distinct processing does not occur. For example this would occur with a query such as: select a.e1, b.e2 from pm1.g1 as a, (select e1, e2 from pm2.g2 union select e1, e2 from pm2.g2) as b where a.e1 = b.e1 where the source does not support union. -- 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

11 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 2014