November 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

2 days, 6 hours

2
1
0 / 0

[JBoss JIRA] (TEIID-3236) Copy criteria within a left outer join removes predicates

by Steven Hawkins (JIRA)

Steven Hawkins created TEIID-3236: ------------------------------------- Summary: Copy criteria within a left outer join removes predicates Key: TEIID-3236 URL: https://issues.jboss.org/browse/TEIID-3236 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 8.4 Reporter: Steven Hawkins Assignee: Steven Hawkins Fix For: 8.10 With a query such as: select bqt1.smalla.intkey from bqt1.smalla inner join (select bqt3.smalla.intkey from bqt2.smalla left outer join bqt3.smalla on bqt2.smalla.intkey = bqt3.smalla.intkey and bqt3.smalla.intkey = 1) foo on bqt1.smalla.intkey = foo.intkey where the left outer join is not pushed to the source, we will process it as if bqt2.smalla.intkey = bqt3.smalla.intkey doesn't exist. This is somewhat unlikely to occur for user queries as the join predicates are not likely to occur and could easily be simplified. -- This message was sent by Atlassian JIRA (v6.3.8#6338)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3165) TeiidDataSource ignores jassName

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-3165?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration commented on TEIID-3165: ------------------------------------------------ Juraj Duráni <jdurani(a)redhat.com> changed the Status of [bug 1151034|https://bugzilla.redhat.com/show_bug.cgi?id=1151034] from ON_QA to VERIFIED > TeiidDataSource ignores jassName > -------------------------------- > > Key: TEIID-3165 > URL: https://issues.jboss.org/browse/TEIID-3165 > Project: Teiid > Issue Type: Bug > Affects Versions: 8.7.1 > Environment: OS: fedora 20 > arch: x86_64 > java: sun 1.7 > Reporter: Juraj Duráni > Assignee: Steven Hawkins > Fix For: 8.7.1, 8.9 > > > I am trying to connect to VDB through Kerberos authentication using TeiidDataSource: > TeiidDataSource tds = new TeiidDataSource(); > tds.setServerName("localhost"); > tds.setPortNumber(31000); > tds.setDatabaseName(<vdb>); > tds.setJaasName(<jassName>); > tds.setKerberosServicePrincipleName(<principalName>); > but tds.getConnection(); ends with: > TEIID20005 Client URL connection property missing "jaasName". Please add the property to connection URL > Connection through TeiidDriver works fine: > url: jdbc:teiid:<vdb>@mm://<host>:31000;jaasName=<jassName>;kerberosServicePrincipleName=<principalName> -- This message was sent by Atlassian JIRA (v6.3.8#6338)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2919) BigInteger and BigDecimal datatypes are converted into string in MongoDB translator

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-2919?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration commented on TEIID-2919: ------------------------------------------------ Filip Elias <felias(a)redhat.com> changed the Status of [bug 1084373|https://bugzilla.redhat.com/show_bug.cgi?id=1084373] from ON_QA to VERIFIED > BigInteger and BigDecimal datatypes are converted into string in MongoDB translator > ------------------------------------------------------------------------------------- > > Key: TEIID-2919 > URL: https://issues.jboss.org/browse/TEIID-2919 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.4 > Reporter: Filip Elias > Assignee: Steven Hawkins > Fix For: 8.8, 8.7.1 > > > Description of problem: > BigInteger and BigDecimal datatypes are converted into string in MongoDB translator (MongoDBExecutionFactory.java). User can't use these datatypes in the WHERE clause. > For example > The query: > > SELECT IntKey FROM SmallA where BIGINTEGERVALUE > 1 > returns no results because BIGINTEGERVALUE > 1 is converted into > { "BIGINTEGERVALUE" : { "$gt" : "1"}} > but is should be > { "BIGINTEGERVALUE" : { "$gt" : 1}} -- This message was sent by Atlassian JIRA (v6.3.8#6338)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2984) MongoDB: Usage of function "mod()" in SQL query throws an exception

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-2984?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration commented on TEIID-2984: ------------------------------------------------ Filip Elias <felias(a)redhat.com> changed the Status of [bug 1104758|https://bugzilla.redhat.com/show_bug.cgi?id=1104758] from ON_QA to VERIFIED > MongoDB: Usage of function "mod()" in SQL query throws an exception > ------------------------------------------------------------------- > > Key: TEIID-2984 > URL: https://issues.jboss.org/browse/TEIID-2984 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.4.1 > Reporter: Filip Elias > Assignee: Ramesh Reddy > Fix For: 8.8, 8.7.1 > > Attachments: server.log > > > Exception[1] is thrown when function modulo(mod) is used in SQL query. > Sample query: > SELECT LongNum, 11, MOD(LongNum, 11) FROM BQT1.SmallA ORDER BY LONGNUM > Server log is in the attachment. > [1] 16:36:59,645 ERROR [org.teiid.CONNECTOR] (Worker10_QueryProcessorQueue71) Connector worker process failed for atomic-request=hRCP8hHjrmZb.25.0.13: com.mongodb.CommandFailureException: { "serverUsed" : "localhost/127.0.0.1:27017" , "errmsg" : "exception: disallowed field type Array in object expression (at 'mod')" , "code" : 15992 , "ok" : 0.0} > at com.mongodb.CommandResult.getException(CommandResult.java:71) > at com.mongodb.CommandResult.throwOnError(CommandResult.java:110) > at com.mongodb.DBCollection.aggregate(DBCollection.java:1308) -- This message was sent by Atlassian JIRA (v6.3.8#6338)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2983) MongoDB: Function "count()" returns sum instead of number of rows

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-2983?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration commented on TEIID-2983: ------------------------------------------------ Filip Elias <felias(a)redhat.com> changed the Status of [bug 1104614|https://bugzilla.redhat.com/show_bug.cgi?id=1104614] from ON_QA to VERIFIED > MongoDB: Function "count()" returns sum instead of number of rows > ----------------------------------------------------------------- > > Key: TEIID-2983 > URL: https://issues.jboss.org/browse/TEIID-2983 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.7.1 > Reporter: Van Halbert > Assignee: Ramesh Reddy > Labels: Beta1 > Fix For: 8.8, 8.7.1 > > > Function "count()" returns sum of rows instead of number of rows > Query [1] returns sum of values of all rows of IntKey column. > Query [2] correctly returns number of rows > > [1] SELECT COUNT(IntKey) FROM BQT1.SmallA > [2] SELECT COUNT(*) FROM BQT1.SmallA -- This message was sent by Atlassian JIRA (v6.3.8#6338)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2992) MongoDB: Offset clause causes MongoDB to crash

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-2992?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration commented on TEIID-2992: ------------------------------------------------ Filip Elias <felias(a)redhat.com> changed the Status of [bug 1105473|https://bugzilla.redhat.com/show_bug.cgi?id=1105473] from ON_QA to VERIFIED > MongoDB: Offset clause causes MongoDB to crash > ---------------------------------------------- > > Key: TEIID-2992 > URL: https://issues.jboss.org/browse/TEIID-2992 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.4.1 > Environment: MongoDB < 2.5.2 > Reporter: Filip Elias > Assignee: Ramesh Reddy > Labels: Beta1 > Fix For: 8.8, 8.7.1 > > > MongoDB(version < 2.5.2) will crash when offset clause is used in SQL query. > Althought the bug[1] is in MongoDB (Adding big number into $limit causes Mongodb to shutdown), it is also caused by teiid because it adds max integer into $limit which seems to be unnecessary. > Sample query: > {code} > SELECT INTKEY FROM bqt1.smalla ORDER BY INTKEY OFFSET 45 ROWS > {code} > Generated mongo command: > {code} > { aggregate: "smalla", pipeline: [ { $project: { c_0: "$INTKEY" } }, { $sort: { c_0: 1 } }, { $skip: 45 }, { $limit: 2147483647 } ] } > {code} > [1] https://jira.mongodb.org/browse/SERVER-10136 -- This message was sent by Atlassian JIRA (v6.3.8#6338)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-2987) MongoDB: No rows are returned when a function is used in a where clause

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-2987?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration commented on TEIID-2987: ------------------------------------------------ Filip Elias <felias(a)redhat.com> changed the Status of [bug 1105087|https://bugzilla.redhat.com/show_bug.cgi?id=1105087] from ON_QA to VERIFIED > MongoDB: No rows are returned when a function is used in a where clause > ----------------------------------------------------------------------- > > Key: TEIID-2987 > URL: https://issues.jboss.org/browse/TEIID-2987 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.4.1 > Environment: Teiid 8.4.1 > Reporter: Filip Elias > Assignee: Ramesh Reddy > Labels: Beta1 > Fix For: 8.8, 8.7.1 > > > Teiid will return no rows when a function which is pushed down is used in a where clause. > These queries should return few rows, but the result is empty: > Query: > {code} > SELECT intkey, stringnum FROM BQT1.SmallA WHERE concat(stringkey, stringnum) = '7-17' ORDER BY intkey > {code} > Generated mongo command: > {code} > {aggregate: "smalla", pipeline: [ { $match: { _m0: "7-17" } }, { $project: { c_0: "$INTKEY", c_1: "$STRINGNUM" } }, { $sort: { c_0: 1 } } ] } > {code} > Query: > {code} > select intKey from bqt1.smalla where intKey+intKey >20 > {code} > Generated mongo command: > {code} > {aggregate: "smalla", pipeline: [ { $match: { _m0: { $gt: 20 } } }, { $project: { _m1: "$INTKEY" } } ] } > {code} -- This message was sent by Atlassian JIRA (v6.3.8#6338)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3093) SOLR: Translator ignores "Name In Source" attribute of table columns

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-3093?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration commented on TEIID-3093: ------------------------------------------------ Filip Elias <felias(a)redhat.com> changed the Status of [bug 1131504|https://bugzilla.redhat.com/show_bug.cgi?id=1131504] from ON_QA to VERIFIED > SOLR: Translator ignores "Name In Source" attribute of table columns > -------------------------------------------------------------------- > > Key: TEIID-3093 > URL: https://issues.jboss.org/browse/TEIID-3093 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.7 > Reporter: Filip Elias > Assignee: Ramesh Reddy > Labels: Alpha2 > Fix For: 8.7.1, 8.9 > > > When I create a source model in teiid designer and I specify "Name In Source" attribute in one of the table columns, SOLR translator will ignore the "Name In Source" attribute and it will use a column name to create SOLR query. -- This message was sent by Atlassian JIRA (v6.3.8#6338)

10 years, 11 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3077) SOLR: Type date should be converted into Timestamp

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-3077?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration commented on TEIID-3077: ------------------------------------------------ Filip Elias <felias(a)redhat.com> changed the Status of [bug 1129171|https://bugzilla.redhat.com/show_bug.cgi?id=1129171] from ON_QA to VERIFIED > SOLR: Type date should be converted into Timestamp > -------------------------------------------------- > > Key: TEIID-3077 > URL: https://issues.jboss.org/browse/TEIID-3077 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.7 > Reporter: Filip Elias > Assignee: Steven Hawkins > Fix For: 8.7.1, 8.9 > > > SOLR translator converts solr's date type into java.sql.Date, but SOLR's date format is rather timestamp[1]. When the solr's date is converted into java.sql.Date, the information about hours,minutes and seconds will be lost. > [1] "TimestampValue": "1999-12-31T23:00:00Z", -- This message was sent by Atlassian JIRA (v6.3.8#6338)

10 years, 11 months

1
0
0 / 0

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues November 2014