February 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

6 days, 10 hours

2
1
0 / 0

[JBoss JIRA] (TEIID-2874) HIVE2: can't get the table list if using other than default database

by Ivan Chan (JIRA)

[ https://issues.jboss.org/browse/TEIID-2874?page=com.atlassian.jira.plugin... ] Ivan Chan commented on TEIID-2874: ---------------------------------- Got it. Thanks for looking into it. =) > HIVE2: can't get the table list if using other than default database > --------------------------------------------------------------------- > > Key: TEIID-2874 > URL: https://issues.jboss.org/browse/TEIID-2874 > Project: Teiid > Issue Type: Bug > Components: Connector API > Affects Versions: 8.7 > Environment: Teiid with Hive2 > Reporter: Ivan Chan > Assignee: Ramesh Reddy > Labels: Beta2, teiid > Fix For: 8.7 > > > I am not able to retrieve the table list other than default database. > I have create a database other than default. And I named it as "Testing". > And I put the database name in the URL: > jdbc:hive2://machine_name:port/testing > And it always returns a list of tables from default database. Nothing from "Testing" database -- 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, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2877) Add support for MariaDB as a data source

by Ramesh Reddy (JIRA)

[ https://issues.jboss.org/browse/TEIID-2877?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-2877: ------------------------------------- I did find that their JDBC driver is not quite same, when I used it I ended up some errors (did not capture), so reverted back to mysql version of driver. BQT needs to be run on it with mysql5 and see what it brings up. > Add support for MariaDB as a data source > ---------------------------------------- > > Key: TEIID-2877 > URL: https://issues.jboss.org/browse/TEIID-2877 > Project: Teiid > Issue Type: Feature Request > Components: Misc. Connectors > Reporter: Van Halbert > Fix For: Open To Community > > > Red Hat will be making MariaDB its default DBMS in its next major Red Hat Enterprise Linux (RHEL) release. -- 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, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2877) Add support for MariaDB as a data source

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-2877?page=com.atlassian.jira.plugin... ] Steven Hawkins updated TEIID-2877: ---------------------------------- Fix Version/s: Open To Community Assignee: (was: Steven Hawkins) More than likely the MySQL5 translator will work just fine here. > Add support for MariaDB as a data source > ---------------------------------------- > > Key: TEIID-2877 > URL: https://issues.jboss.org/browse/TEIID-2877 > Project: Teiid > Issue Type: Feature Request > Components: Misc. Connectors > Reporter: Van Halbert > Fix For: Open To Community > > > Red Hat will be making MariaDB its default DBMS in its next major Red Hat Enterprise Linux (RHEL) release. -- 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, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2874) HIVE2: can't get the table list if using other than default database

by Ramesh Reddy (JIRA)

[ https://issues.jboss.org/browse/TEIID-2874?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-2874: ------------------------------------- Found this https://issues.apache.org/jira/browse/HIVE-4256 Fixed in 0.13 version of Hive Driver > HIVE2: can't get the table list if using other than default database > --------------------------------------------------------------------- > > Key: TEIID-2874 > URL: https://issues.jboss.org/browse/TEIID-2874 > Project: Teiid > Issue Type: Bug > Components: Connector API > Affects Versions: 8.7 > Environment: Teiid with Hive2 > Reporter: Ivan Chan > Assignee: Ramesh Reddy > Labels: Beta2, teiid > Fix For: 8.7 > > > I am not able to retrieve the table list other than default database. > I have create a database other than default. And I named it as "Testing". > And I put the database name in the URL: > jdbc:hive2://machine_name:port/testing > And it always returns a list of tables from default database. Nothing from "Testing" database -- 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, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2874) HIVE2: can't get the table list if using other than default database

by Ramesh Reddy (JIRA)

[ https://issues.jboss.org/browse/TEIID-2874?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-2874: ------------------------------------- The issue is not specific to metadata retrieval, it is also affects all the queries issued on it. That means, Teiid would need to execute "use database" statement before each and every statement execution as it does not know if it is getting a new connection vs a pooled connection. This can add to query performance. This is just a workaround. The real bug need to be logged through Hive to fix this error. > HIVE2: can't get the table list if using other than default database > --------------------------------------------------------------------- > > Key: TEIID-2874 > URL: https://issues.jboss.org/browse/TEIID-2874 > Project: Teiid > Issue Type: Bug > Components: Connector API > Affects Versions: 8.7 > Environment: Teiid with Hive2 > Reporter: Ivan Chan > Assignee: Ramesh Reddy > Labels: Beta2, teiid > Fix For: 8.7 > > > I am not able to retrieve the table list other than default database. > I have create a database other than default. And I named it as "Testing". > And I put the database name in the URL: > jdbc:hive2://machine_name:port/testing > And it always returns a list of tables from default database. Nothing from "Testing" database -- 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, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2874) HIVE2: can't get the table list if using other than default database

by Ivan Chan (JIRA)

[ https://issues.jboss.org/browse/TEIID-2874?page=com.atlassian.jira.plugin... ] Ivan Chan commented on TEIID-2874: ---------------------------------- Is it possible to make this fix more generic? Stick with the database name with url approach? > HIVE2: can't get the table list if using other than default database > --------------------------------------------------------------------- > > Key: TEIID-2874 > URL: https://issues.jboss.org/browse/TEIID-2874 > Project: Teiid > Issue Type: Bug > Components: Connector API > Affects Versions: 8.7 > Environment: Teiid with Hive2 > Reporter: Ivan Chan > Assignee: Ramesh Reddy > Labels: Beta2, teiid > Fix For: 8.7 > > > I am not able to retrieve the table list other than default database. > I have create a database other than default. And I named it as "Testing". > And I put the database name in the URL: > jdbc:hive2://machine_name:port/testing > And it always returns a list of tables from default database. Nothing from "Testing" database -- 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, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2874) HIVE2: can't get the table list if using other than default database

by Ramesh Reddy (JIRA)

[ https://issues.jboss.org/browse/TEIID-2874?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-2874: ------------------------------------- If Tomcat has similar functionality in their connection pool then it will work the same. > HIVE2: can't get the table list if using other than default database > --------------------------------------------------------------------- > > Key: TEIID-2874 > URL: https://issues.jboss.org/browse/TEIID-2874 > Project: Teiid > Issue Type: Bug > Components: Connector API > Affects Versions: 8.7 > Environment: Teiid with Hive2 > Reporter: Ivan Chan > Assignee: Ramesh Reddy > Labels: Beta2, teiid > Fix For: 8.7 > > > I am not able to retrieve the table list other than default database. > I have create a database other than default. And I named it as "Testing". > And I put the database name in the URL: > jdbc:hive2://machine_name:port/testing > And it always returns a list of tables from default database. Nothing from "Testing" database -- 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, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2877) Add support for MariaDB as a data source

by Van Halbert (JIRA)

Van Halbert created TEIID-2877: ---------------------------------- Summary: Add support for MariaDB as a data source Key: TEIID-2877 URL: https://issues.jboss.org/browse/TEIID-2877 Project: Teiid Issue Type: Feature Request Components: Misc. Connectors Reporter: Van Halbert Assignee: Steven Hawkins Red Hat will be making MariaDB its default DBMS in its next major Red Hat Enterprise Linux (RHEL) release. -- 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, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2874) HIVE2: can't get the table list if using other than default database

by Ivan Chan (JIRA)

[ https://issues.jboss.org/browse/TEIID-2874?page=com.atlassian.jira.plugin... ] Ivan Chan commented on TEIID-2874: ---------------------------------- Will this fix work with JNDI connection from other application server, too? (Like Tomcat) Because I am using the embedded version. > HIVE2: can't get the table list if using other than default database > --------------------------------------------------------------------- > > Key: TEIID-2874 > URL: https://issues.jboss.org/browse/TEIID-2874 > Project: Teiid > Issue Type: Bug > Components: Connector API > Affects Versions: 8.7 > Environment: Teiid with Hive2 > Reporter: Ivan Chan > Assignee: Ramesh Reddy > Labels: Beta2, teiid > Fix For: 8.7 > > > I am not able to retrieve the table list other than default database. > I have create a database other than default. And I named it as "Testing". > And I put the database name in the URL: > jdbc:hive2://machine_name:port/testing > And it always returns a list of tables from default database. Nothing from "Testing" database -- 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, 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 2014