March 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

1 month, 2 weeks

2
1
0 / 0

[JBoss JIRA] (TEIID-3058) Cassandra: cannot compare timestamp field in where clause

by Ivan Chan (JIRA)

[ https://issues.jboss.org/browse/TEIID-3058?page=com.atlassian.jira.plugin... ] Ivan Chan commented on TEIID-3058: ---------------------------------- I think the issue is that Teiid translates date format from '2013-05-01 00:00:00' to ts'2013-05-01 00:00:00.0'. And cassandra doesn't recognize this kind of format, ts'2013-05-01 00:00:00.0'. Thanks. > Cassandra: cannot compare timestamp field in where clause > ---------------------------------------------------------- > > Key: TEIID-3058 > URL: https://issues.jboss.org/browse/TEIID-3058 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.7, 8.10 > Environment: Cassandra with Teiid 8.7 > Reporter: Ivan Chan > Assignee: Steven Hawkins > Fix For: 8.7.1, 8.9 > > > It seems like Teiid is having problem to compare translate CQL if there is timestamp comparison in where clause: > ORIGINAL SQL: > select "cassandrads_sales"."product_family" as "cassandrads_sales_product_family", > "cassandrads_sales"."product_family" as "cassandrads_sales_product_family1" > from "cassandrads"."sales" "cassandrads_sales" > where "cassandrads_sales"."the_date" <= '2013-05-01 00:00:00' > limit 1000 > TRANSLATED CQL: > SELECT cassandrads.sales.product_family FROM cassandrads.sales WHERE cassandrads.sales.the_date <= {ts'2013-05-01 00:00:00.0'} LIMIT 1000 > And I got this error: > Caused by: org.teiid.translator.TranslatorException: line 1:55 no viable alternative at input '2013-05-01 00:00:0 > at org.teiid.translator.cassandra.CassandraQueryExecution.execute(CassandraQueryExecution.java:80) > at org.teiid.translator.cassandra.CassandraQueryExecution.execute(CassandraQueryExecution.java:72) > at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:325) > at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) > at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:135) > If it is not possible to do time/ data comparison for CQL, maybe you guys should consider to do it in memory instead. Thanks. -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3058) Cassandra: cannot compare timestamp field in where clause

by Ivan Chan (JIRA)

[ https://issues.jboss.org/browse/TEIID-3058?page=com.atlassian.jira.plugin... ] Ivan Chan updated TEIID-3058: ----------------------------- Affects Version/s: 8.10 > Cassandra: cannot compare timestamp field in where clause > ---------------------------------------------------------- > > Key: TEIID-3058 > URL: https://issues.jboss.org/browse/TEIID-3058 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.7, 8.10 > Environment: Cassandra with Teiid 8.7 > Reporter: Ivan Chan > Assignee: Steven Hawkins > Fix For: 8.7.1, 8.9 > > > It seems like Teiid is having problem to compare translate CQL if there is timestamp comparison in where clause: > ORIGINAL SQL: > select "cassandrads_sales"."product_family" as "cassandrads_sales_product_family", > "cassandrads_sales"."product_family" as "cassandrads_sales_product_family1" > from "cassandrads"."sales" "cassandrads_sales" > where "cassandrads_sales"."the_date" <= '2013-05-01 00:00:00' > limit 1000 > TRANSLATED CQL: > SELECT cassandrads.sales.product_family FROM cassandrads.sales WHERE cassandrads.sales.the_date <= {ts'2013-05-01 00:00:00.0'} LIMIT 1000 > And I got this error: > Caused by: org.teiid.translator.TranslatorException: line 1:55 no viable alternative at input '2013-05-01 00:00:0 > at org.teiid.translator.cassandra.CassandraQueryExecution.execute(CassandraQueryExecution.java:80) > at org.teiid.translator.cassandra.CassandraQueryExecution.execute(CassandraQueryExecution.java:72) > at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:325) > at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) > at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:135) > If it is not possible to do time/ data comparison for CQL, maybe you guys should consider to do it in memory instead. Thanks. -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3058) Cassandra: cannot compare timestamp field in where clause

by Ivan Chan (JIRA)

[ https://issues.jboss.org/browse/TEIID-3058?page=com.atlassian.jira.plugin... ] Ivan Chan reopened TEIID-3058: ------------------------------ I still can reproduce this issue using Teiid 8.10 > Cassandra: cannot compare timestamp field in where clause > ---------------------------------------------------------- > > Key: TEIID-3058 > URL: https://issues.jboss.org/browse/TEIID-3058 > Project: Teiid > Issue Type: Bug > Components: Misc. Connectors > Affects Versions: 8.7, 8.10 > Environment: Cassandra with Teiid 8.7 > Reporter: Ivan Chan > Assignee: Steven Hawkins > Fix For: 8.7.1, 8.9 > > > It seems like Teiid is having problem to compare translate CQL if there is timestamp comparison in where clause: > ORIGINAL SQL: > select "cassandrads_sales"."product_family" as "cassandrads_sales_product_family", > "cassandrads_sales"."product_family" as "cassandrads_sales_product_family1" > from "cassandrads"."sales" "cassandrads_sales" > where "cassandrads_sales"."the_date" <= '2013-05-01 00:00:00' > limit 1000 > TRANSLATED CQL: > SELECT cassandrads.sales.product_family FROM cassandrads.sales WHERE cassandrads.sales.the_date <= {ts'2013-05-01 00:00:00.0'} LIMIT 1000 > And I got this error: > Caused by: org.teiid.translator.TranslatorException: line 1:55 no viable alternative at input '2013-05-01 00:00:0 > at org.teiid.translator.cassandra.CassandraQueryExecution.execute(CassandraQueryExecution.java:80) > at org.teiid.translator.cassandra.CassandraQueryExecution.execute(CassandraQueryExecution.java:72) > at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:325) > at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) > at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:135) > If it is not possible to do time/ data comparison for CQL, maybe you guys should consider to do it in memory instead. Thanks. -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3393) Add an adminapi interface impl for embedded

by Kylin Soong (JIRA)

[ https://issues.jboss.org/browse/TEIID-3393?page=com.atlassian.jira.plugin... ] Kylin Soong updated TEIID-3393: ------------------------------- Git Pull Request: https://github.com/teiid/teiid/pull/436 > Add an adminapi interface impl for embedded > ------------------------------------------- > > Key: TEIID-3393 > URL: https://issues.jboss.org/browse/TEIID-3393 > Project: Teiid > Issue Type: Feature Request > Components: Embedded > Reporter: Steven Hawkins > Assignee: Kylin Soong > Fix For: 8.11 > > > For as many methods as seems reasonable we should implement the adminapi interface for embedded. -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3408) Update the quickstarts that refer to using user .properties files and user roles files

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-3408?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration updated TEIID-3408: ------------------------------------------- Bugzilla Update: Perform Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1190678 > Update the quickstarts that refer to using user .properties files and user roles files > -------------------------------------------------------------------------------------- > > Key: TEIID-3408 > URL: https://issues.jboss.org/browse/TEIID-3408 > Project: Teiid > Issue Type: Bug > Components: Quick Starts > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Minor > > Update the quoickstarts that still refer to using user security .properties and user roles files. -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3408) Update the quickstarts that refer to using user .properties files and user roles files

by Van Halbert (JIRA)

Van Halbert created TEIID-3408: ---------------------------------- Summary: Update the quickstarts that refer to using user .properties files and user roles files Key: TEIID-3408 URL: https://issues.jboss.org/browse/TEIID-3408 Project: Teiid Issue Type: Bug Components: Quick Starts Reporter: Van Halbert Assignee: Van Halbert Priority: Minor Update the quoickstarts that still refer to using user security .properties and user roles files. -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3407) Clarify in the README's for each quickstart regarding how to start the server

by Van Halbert (JIRA)

[ https://issues.jboss.org/browse/TEIID-3407?page=com.atlassian.jira.plugin... ] Van Halbert resolved TEIID-3407. -------------------------------- Resolution: Done > Clarify in the README's for each quickstart regarding how to start the server > ----------------------------------------------------------------------------- > > Key: TEIID-3407 > URL: https://issues.jboss.org/browse/TEIID-3407 > Project: Teiid > Issue Type: Enhancement > Components: Quick Starts > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Trivial > > Change the start command in the README's to be the following: > To start the server, open a command line and navigate to the "bin" directory under the root directory of the JBoss server and run: > > For Linux: ./standalone.sh > for Windows: standalone.bat > append the following to the command to indicate which configuration to use if Teiid isn't configured in the default configuration > > -c standalone-teiid.xml -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3407) Clarify in the README's for each quickstart regarding how to start the server

by RH Bugzilla Integration (JIRA)

[ https://issues.jboss.org/browse/TEIID-3407?page=com.atlassian.jira.plugin... ] RH Bugzilla Integration updated TEIID-3407: ------------------------------------------- Bugzilla Update: Perform Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1190678 > Clarify in the README's for each quickstart regarding how to start the server > ----------------------------------------------------------------------------- > > Key: TEIID-3407 > URL: https://issues.jboss.org/browse/TEIID-3407 > Project: Teiid > Issue Type: Enhancement > Components: Quick Starts > Reporter: Van Halbert > Assignee: Van Halbert > Priority: Trivial > > Change the start command in the README's to be the following: > To start the server, open a command line and navigate to the "bin" directory under the root directory of the JBoss server and run: > > For Linux: ./standalone.sh > for Windows: standalone.bat > append the following to the command to indicate which configuration to use if Teiid isn't configured in the default configuration > > -c standalone-teiid.xml -- This message was sent by Atlassian JIRA (v6.3.11#6341)

10 years, 8 months

1
0
0 / 0

[JBoss JIRA] (TEIID-3407) Clarify in the README's for each quickstart regarding how to start the server

by Van Halbert (JIRA)

Van Halbert created TEIID-3407: ---------------------------------- Summary: Clarify in the README's for each quickstart regarding how to start the server Key: TEIID-3407 URL: https://issues.jboss.org/browse/TEIID-3407 Project: Teiid Issue Type: Enhancement Components: Quick Starts Reporter: Van Halbert Assignee: Van Halbert Priority: Trivial Change the start command in the README's to be the following: To start the server, open a command line and navigate to the "bin" directory under the root directory of the JBoss server and run: For Linux: ./standalone.sh for Windows: standalone.bat append the following to the command to indicate which configuration to use if Teiid isn't configured in the default configuration -c standalone-teiid.xml -- 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 March 2015