[JBoss JIRA] (TEIID-4120) Teiid on Oracle Spatial generates incorrect SQL
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4120?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-4120:
----------------------------------
Description:
Attempting to run a spatial query using SDO_RELATE in Teiid against an Oracle database seems to generate incorrect SQL. The query in "Steps to Reproduce" results in the following SQL being generated by Teiid:
{code}
SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100
{code}
Note the test for the return from SDO_RELATE = 1. This should be a boolean function, so the test for =1 is incorrect, and should be ='TRUE'. The following warning is in the Teiid log:
{code}
14:37:11,934 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue17) YRwvNQVeMEzW Connector worker process failed for atomic-request=YRwvNQVeMEzW.5.0.3: org.teiid.translator.jdbc.JDBCExecutionException: 1722 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:359)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
at com.sun.proxy.$Proxy56.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
... 18 more
{code}
Running the generated SQL in SQLDeveloper results in the error:
{code}
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:
{code}
Altering the generated SQL in SQLDeveloper to be:
{code}
SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0.footprint, 'mask=contains') = 'TRUE';
{code}
returns the expected results.
was:
Attempting to run a spatial query using SDO_RELATE in Teiid against an Oracle database seems to generate incorrect SQL. The query in "Steps to Reproduce" results in the following SQL being generated by Teiid:
{code}
SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100
{code}
Note the test for the return from SDO_RELATE = 1. This should be a boolean function, so the test for =1 is incorrect, and should be ='TRUE'. The following warning is in the Teiid log:
{code}
14:37:11,934 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue17) YRwvNQVeMEzW Connector worker process failed for atomic-request=YRwvNQVeMEzW.5.0.3: org.teiid.translator.jdbc.JDBCExecutionException: 1722 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:359)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
at com.sun.proxy.$Proxy56.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
... 18 more
{code}
Running the generated SQL in SQLDeveloper results in the error:
{code}
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:
{code}
Altering the generated SQL in SQLDeveloper to be:
{code}
SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0.footprint, 'mask=contains') = 'TRUE';
{code}
returns the expected results.
Fix Version/s: 9.0
8.12.5
8.13.3
The numeric conversion is effectively for the case when a numeric column is used to represent a boolean, so there needs to be a better accounting here since the sdo functions effectively return strings.
> Teiid on Oracle Spatial generates incorrect SQL
> -----------------------------------------------
>
> Key: TEIID-4120
> URL: https://issues.jboss.org/browse/TEIID-4120
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Driver
> Affects Versions: 8.13.3
> Reporter: Mike Houwers
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5, 8.13.3
>
>
> Attempting to run a spatial query using SDO_RELATE in Teiid against an Oracle database seems to generate incorrect SQL. The query in "Steps to Reproduce" results in the following SQL being generated by Teiid:
> {code}
> SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100
> {code}
> Note the test for the return from SDO_RELATE = 1. This should be a boolean function, so the test for =1 is incorrect, and should be ='TRUE'. The following warning is in the Teiid log:
> {code}
> 14:37:11,934 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue17) YRwvNQVeMEzW Connector worker process failed for atomic-request=YRwvNQVeMEzW.5.0.3: org.teiid.translator.jdbc.JDBCExecutionException: 1722 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:359)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:606)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy56.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
> at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
> at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
> at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
> at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
> at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 18 more
> {code}
> Running the generated SQL in SQLDeveloper results in the error:
> {code}
> ORA-01722: invalid number
> 01722. 00000 - "invalid number"
> *Cause:
> *Action:
> {code}
> Altering the generated SQL in SQLDeveloper to be:
> {code}
> SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0.footprint, 'mask=contains') = 'TRUE';
> {code}
> returns the expected results.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 9 months
[JBoss JIRA] (TEIID-4113) Impala translator - Incorrect aggregate replacement in query
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4113?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4113:
---------------------------------------
No problem. Should help with tracking down the next issue that hopefully you won't come across for a while.
> Impala translator - Incorrect aggregate replacement in query
> ------------------------------------------------------------
>
> Key: TEIID-4113
> URL: https://issues.jboss.org/browse/TEIID-4113
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.13.3
> Environment: Ubuntu Trusty
> Reporter: Don Krapohl
> Assignee: Steven Hawkins
> Labels: Impala_Translator
> Attachments: parsetimestamp-plan.txt
>
>
> When PARSETIMESTAMP is included in a query aggregates in other columns may not be written correctly. Example is as below, sum(first_metric) is rewritten to anon_grp0.agg0 in the query sent to Impala.
> Given the schema:
> //source
> SourceTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> //view
> VirtualTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> // Teiid query
> SELECT the_attribute,
> cast(
> sum(
> first_metric
> )
> /
> count(
> distinct case when second_metric >= 0 then second_metric end
> ) as double
> ) as some_alias,
> PARSETIMESTAMP(another_attribute, 'yyyy-MM-dd') as somedate
> FROM VirtualTable
> WHERE a_filter_val=111
> GROUP BY the_attribute, another_attribute
> //Query sent to impala
> SELECT g_0.the_attribute AS c_0,
> cast(
> (
> anon_grp0.agg0
> /
> cast(
> anon_grp0.agg1 AS bigint
> )
> ) AS double
> ) AS c_1,
> g_0.another_attribute AS c_2
> FROM VirtualTable
> WHERE a_filter_val=111
> GROUP BY g_0.the_attribute, g_0.another_attribute
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 9 months
[JBoss JIRA] (TEIID-4113) Impala translator - Incorrect aggregate replacement in query
by Don Krapohl (JIRA)
[ https://issues.jboss.org/browse/TEIID-4113?page=com.atlassian.jira.plugin... ]
Don Krapohl closed TEIID-4113.
------------------------------
Resolution: Rejected
Version info in the description was incorrect. The behavior is in 8.12.4 and fixed in 8.13.3
> Impala translator - Incorrect aggregate replacement in query
> ------------------------------------------------------------
>
> Key: TEIID-4113
> URL: https://issues.jboss.org/browse/TEIID-4113
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.13.3
> Environment: Ubuntu Trusty
> Reporter: Don Krapohl
> Assignee: Steven Hawkins
> Labels: Impala_Translator
> Attachments: parsetimestamp-plan.txt
>
>
> When PARSETIMESTAMP is included in a query aggregates in other columns may not be written correctly. Example is as below, sum(first_metric) is rewritten to anon_grp0.agg0 in the query sent to Impala.
> Given the schema:
> //source
> SourceTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> //view
> VirtualTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> // Teiid query
> SELECT the_attribute,
> cast(
> sum(
> first_metric
> )
> /
> count(
> distinct case when second_metric >= 0 then second_metric end
> ) as double
> ) as some_alias,
> PARSETIMESTAMP(another_attribute, 'yyyy-MM-dd') as somedate
> FROM VirtualTable
> WHERE a_filter_val=111
> GROUP BY the_attribute, another_attribute
> //Query sent to impala
> SELECT g_0.the_attribute AS c_0,
> cast(
> (
> anon_grp0.agg0
> /
> cast(
> anon_grp0.agg1 AS bigint
> )
> ) AS double
> ) AS c_1,
> g_0.another_attribute AS c_2
> FROM VirtualTable
> WHERE a_filter_val=111
> GROUP BY g_0.the_attribute, g_0.another_attribute
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 9 months
[JBoss JIRA] (TEIID-4113) Impala translator - Incorrect aggregate replacement in query
by Don Krapohl (JIRA)
[ https://issues.jboss.org/browse/TEIID-4113?page=com.atlassian.jira.plugin... ]
Don Krapohl commented on TEIID-4113:
------------------------------------
You are correct. 8.13.3 fixes the issue. Thanks for your time and sorry to take up so much of it.
> Impala translator - Incorrect aggregate replacement in query
> ------------------------------------------------------------
>
> Key: TEIID-4113
> URL: https://issues.jboss.org/browse/TEIID-4113
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.13.3
> Environment: Ubuntu Trusty
> Reporter: Don Krapohl
> Assignee: Steven Hawkins
> Labels: Impala_Translator
> Attachments: parsetimestamp-plan.txt
>
>
> When PARSETIMESTAMP is included in a query aggregates in other columns may not be written correctly. Example is as below, sum(first_metric) is rewritten to anon_grp0.agg0 in the query sent to Impala.
> Given the schema:
> //source
> SourceTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> //view
> VirtualTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> // Teiid query
> SELECT the_attribute,
> cast(
> sum(
> first_metric
> )
> /
> count(
> distinct case when second_metric >= 0 then second_metric end
> ) as double
> ) as some_alias,
> PARSETIMESTAMP(another_attribute, 'yyyy-MM-dd') as somedate
> FROM VirtualTable
> WHERE a_filter_val=111
> GROUP BY the_attribute, another_attribute
> //Query sent to impala
> SELECT g_0.the_attribute AS c_0,
> cast(
> (
> anon_grp0.agg0
> /
> cast(
> anon_grp0.agg1 AS bigint
> )
> ) AS double
> ) AS c_1,
> g_0.another_attribute AS c_2
> FROM VirtualTable
> WHERE a_filter_val=111
> GROUP BY g_0.the_attribute, g_0.another_attribute
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 9 months
[JBoss JIRA] (TEIID-4120) Teiid on Oracle Spatial generates incorrect SQL
by Mike Houwers (JIRA)
[ https://issues.jboss.org/browse/TEIID-4120?page=com.atlassian.jira.plugin... ]
Mike Houwers updated TEIID-4120:
--------------------------------
Description:
Attempting to run a spatial query using SDO_RELATE in Teiid against an Oracle database seems to generate incorrect SQL. The query in "Steps to Reproduce" results in the following SQL being generated by Teiid:
{code}
SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100
{code}
Note the test for the return from SDO_RELATE = 1. This should be a boolean function, so the test for =1 is incorrect, and should be ='TRUE'. The following warning is in the Teiid log:
{code}
14:37:11,934 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue17) YRwvNQVeMEzW Connector worker process failed for atomic-request=YRwvNQVeMEzW.5.0.3: org.teiid.translator.jdbc.JDBCExecutionException: 1722 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:359)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
at com.sun.proxy.$Proxy56.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
... 18 more
{code}
Running the generated SQL in SQLDeveloper results in the error:
{code}
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:
{code}
Altering the generated SQL in SQLDeveloper to be:
{code}
SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0.footprint, 'mask=contains') = 'TRUE';
{code}
returns the expected results.
was:
[~tom9729]
Attempting to run a spatial query using SDO_RELATE in Teiid against an Oracle database seems to generate incorrect SQL. The query in "Steps to Reproduce" results in the following SQL being generated by Teiid:
{code}
SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100
{code}
Note the test for the return from SDO_RELATE = 1. This should be a boolean function, so the test for =1 is incorrect, and should be ='TRUE'. The following warning is in the Teiid log:
{code}
14:37:11,934 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue17) YRwvNQVeMEzW Connector worker process failed for atomic-request=YRwvNQVeMEzW.5.0.3: org.teiid.translator.jdbc.JDBCExecutionException: 1722 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:359)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
at com.sun.proxy.$Proxy56.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
... 18 more
{code}
Running the generated SQL in SQLDeveloper results in the error:
{code}
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:
{code}
Altering the generated SQL in SQLDeveloper to be:
{code}
SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0.footprint, 'mask=contains') = 'TRUE';
{code}
returns the expected results.
> Teiid on Oracle Spatial generates incorrect SQL
> -----------------------------------------------
>
> Key: TEIID-4120
> URL: https://issues.jboss.org/browse/TEIID-4120
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Driver
> Affects Versions: 8.13.3
> Reporter: Mike Houwers
> Assignee: Steven Hawkins
>
> Attempting to run a spatial query using SDO_RELATE in Teiid against an Oracle database seems to generate incorrect SQL. The query in "Steps to Reproduce" results in the following SQL being generated by Teiid:
> {code}
> SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100
> {code}
> Note the test for the return from SDO_RELATE = 1. This should be a boolean function, so the test for =1 is incorrect, and should be ='TRUE'. The following warning is in the Teiid log:
> {code}
> 14:37:11,934 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue17) YRwvNQVeMEzW Connector worker process failed for atomic-request=YRwvNQVeMEzW.5.0.3: org.teiid.translator.jdbc.JDBCExecutionException: 1722 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:359)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:606)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy56.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
> at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
> at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
> at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
> at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
> at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 18 more
> {code}
> Running the generated SQL in SQLDeveloper results in the error:
> {code}
> ORA-01722: invalid number
> 01722. 00000 - "invalid number"
> *Cause:
> *Action:
> {code}
> Altering the generated SQL in SQLDeveloper to be:
> {code}
> SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0.footprint, 'mask=contains') = 'TRUE';
> {code}
> returns the expected results.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 9 months
[JBoss JIRA] (TEIID-4120) Teiid on Oracle Spatial generates incorrect SQL
by Mike Houwers (JIRA)
Mike Houwers created TEIID-4120:
-----------------------------------
Summary: Teiid on Oracle Spatial generates incorrect SQL
Key: TEIID-4120
URL: https://issues.jboss.org/browse/TEIID-4120
Project: Teiid
Issue Type: Bug
Components: JDBC Driver
Affects Versions: 8.13.3
Reporter: Mike Houwers
Assignee: Steven Hawkins
[~tom9729]
Attempting to run a spatial query using SDO_RELATE in Teiid against an Oracle database seems to generate incorrect SQL. The query in "Steps to Reproduce" results in the following SQL being generated by Teiid:
{code}
SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100
{code}
Note the test for the return from SDO_RELATE = 1. This should be a boolean function, so the test for =1 is incorrect, and should be ='TRUE'. The following warning is in the Teiid log:
{code}
14:37:11,934 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue17) YRwvNQVeMEzW Connector worker process failed for atomic-request=YRwvNQVeMEzW.5.0.3: org.teiid.translator.jdbc.JDBCExecutionException: 1722 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT c_0 FROM (SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0."FOOTPRINT", 'mask=contains') = 1) WHERE ROWNUM <= 100]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:359)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
at com.sun.proxy.$Proxy56.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
... 18 more
{code}
Running the generated SQL in SQLDeveloper results in the error:
{code}
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:
{code}
Altering the generated SQL in SQLDeveloper to be:
{code}
SELECT g_0."ID" AS c_0 FROM "FOO" g_0 WHERE SDO_RELATE(g_0."FOOTPRINT", g_0.footprint, 'mask=contains') = 'TRUE';
{code}
returns the expected results.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 9 months
[JBoss JIRA] (TEIID-4113) Impala translator - Incorrect aggregate replacement in query
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4113?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-4113:
---------------------------------------
> I can't get a plan WITH parsetimestamp as the statement just errors.
The plan should be there even if there is an error. You would just catch the error and still call show plan.
Just to confirm, what release are you seeing this issue on?
If I run the query shown with slightly modified metadata from your updated test, I still see an appropriate query. Is it possible you are not on 8.13.3?
> Impala translator - Incorrect aggregate replacement in query
> ------------------------------------------------------------
>
> Key: TEIID-4113
> URL: https://issues.jboss.org/browse/TEIID-4113
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.13.3
> Environment: Ubuntu Trusty
> Reporter: Don Krapohl
> Assignee: Steven Hawkins
> Labels: Impala_Translator
> Attachments: parsetimestamp-plan.txt
>
>
> When PARSETIMESTAMP is included in a query aggregates in other columns may not be written correctly. Example is as below, sum(first_metric) is rewritten to anon_grp0.agg0 in the query sent to Impala.
> Given the schema:
> //source
> SourceTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> //view
> VirtualTable
> the_attribute string(255),
> first_metric long,
> second_metric bigdecimal
> another_attribute String(255)
> // Teiid query
> SELECT the_attribute,
> cast(
> sum(
> first_metric
> )
> /
> count(
> distinct case when second_metric >= 0 then second_metric end
> ) as double
> ) as some_alias,
> PARSETIMESTAMP(another_attribute, 'yyyy-MM-dd') as somedate
> FROM VirtualTable
> WHERE a_filter_val=111
> GROUP BY the_attribute, another_attribute
> //Query sent to impala
> SELECT g_0.the_attribute AS c_0,
> cast(
> (
> anon_grp0.agg0
> /
> cast(
> anon_grp0.agg1 AS bigint
> )
> ) AS double
> ) AS c_1,
> g_0.another_attribute AS c_2
> FROM VirtualTable
> WHERE a_filter_val=111
> GROUP BY g_0.the_attribute, g_0.another_attribute
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 9 months