[JBoss JIRA] (TEIID-2965) wrong rewriting on "insert into ... select" statement - Teiid 8.8.0.Alpha1
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2965?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-2965.
-----------------------------------
Resolution: Done
RuleCollapseSource was over zealously removing the inline view which performed the proper projection here. The correction moves what we consider as the root node for the view removal check in the case of a insert into.
> wrong rewriting on "insert into ... select" statement - Teiid 8.8.0.Alpha1
> --------------------------------------------------------------------------
>
> Key: TEIID-2965
> URL: https://issues.jboss.org/browse/TEIID-2965
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.4
> Reporter: Alex K.
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 8.7.1, 8.8
>
>
> Having created a PostgreSQL Table like:
>
> {code:sql}
> CREATE TABLE Test_Insert
> (
> status character varying(4000)
> )
> {code}
> And then running this statement
> {code:sql}
> INSERT INTO test_tables_pg.Test_Insert
> SELECT
> CASE WHEN (status = '0') AND (cnt > 0) THEN '4' ELSE status END AS status
> FROM
> (SELECT
> (SELECT COUNT(*) FROM test_tables_pg.test_a AS smh2) AS cnt,
> a AS status
> FROM test_tables_pg.test_a AS smh
> ) AS a
> {code}
>
> I get the following exception:
> {code}
> 16:18:34,427 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue16) ii4bbeYlV1WF TEIID30020 Processing exception for request ii4bbeYlV1WF.4 'TEIID30504 test_tables_pg: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: INSERT INTO "public"."test_insert" ("status") SELECT smh."a" AS status, (SELECT COUNT(*) FROM "public"."test_a" AS g_0) AS cnt FROM "public"."test_a" AS smh]'. Originally TeiidProcessingException 'ERROR: INSERT has more expressions than target columns
> Position: 74' QueryExecutorImpl.java:2157. Enable more detailed logging to see the entire stacktrace.
> 16:20:12,498 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue22) ii4bbeYlV1WF Connector worker process failed for atomic-request=ii4bbeYlV1WF.6.2.6: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: INSERT INTO "public"."test_insert" ("status") SELECT
> smh."a" AS status, (SELECT COUNT(*) FROM "public"."test_a" AS g_0) AS cnt FROM "public"."test_a" AS smh]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:247)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:79)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:359) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:326) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_51]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_51]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_51]
> at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]
> Caused by: org.postgresql.util.PSQLException: ERROR: INSERT has more expressions than target columns
> Position: 74
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:218)
> ... 14 more
> {code}
> It looks like the rewriting is completely wrong, since Teiid is trying to insert 2 fields into a single one.
>
> In order to reproduce the bug, it is necessary that test_a and the Test_Insert tables are both in the same schema, so that the full command can be pushed down. Moreover, the bug can be reproduced in PostgreSQL, Oracle, MS SQL Server, but it correctly works in MySQL.
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
10 years, 1 month
[JBoss JIRA] (TEIID-2965) wrong rewriting on "insert into ... select" statement - Teiid 8.8.0.Alpha1
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2965?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-2965:
----------------------------------
Issue Type: Bug (was: Feature Request)
Fix Version/s: 8.7.1
8.8
Priority: Critical (was: Major)
Affects Version/s: 8.4
(was: 8.8)
Component/s: Query Engine
> wrong rewriting on "insert into ... select" statement - Teiid 8.8.0.Alpha1
> --------------------------------------------------------------------------
>
> Key: TEIID-2965
> URL: https://issues.jboss.org/browse/TEIID-2965
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.4
> Reporter: Alex K.
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 8.7.1, 8.8
>
>
> Having created a PostgreSQL Table like:
>
> {code:sql}
> CREATE TABLE Test_Insert
> (
> status character varying(4000)
> )
> {code}
> And then running this statement
> {code:sql}
> INSERT INTO test_tables_pg.Test_Insert
> SELECT
> CASE WHEN (status = '0') AND (cnt > 0) THEN '4' ELSE status END AS status
> FROM
> (SELECT
> (SELECT COUNT(*) FROM test_tables_pg.test_a AS smh2) AS cnt,
> a AS status
> FROM test_tables_pg.test_a AS smh
> ) AS a
> {code}
>
> I get the following exception:
> {code}
> 16:18:34,427 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue16) ii4bbeYlV1WF TEIID30020 Processing exception for request ii4bbeYlV1WF.4 'TEIID30504 test_tables_pg: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: INSERT INTO "public"."test_insert" ("status") SELECT smh."a" AS status, (SELECT COUNT(*) FROM "public"."test_a" AS g_0) AS cnt FROM "public"."test_a" AS smh]'. Originally TeiidProcessingException 'ERROR: INSERT has more expressions than target columns
> Position: 74' QueryExecutorImpl.java:2157. Enable more detailed logging to see the entire stacktrace.
> 16:20:12,498 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue22) ii4bbeYlV1WF Connector worker process failed for atomic-request=ii4bbeYlV1WF.6.2.6: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: INSERT INTO "public"."test_insert" ("status") SELECT
> smh."a" AS status, (SELECT COUNT(*) FROM "public"."test_a" AS g_0) AS cnt FROM "public"."test_a" AS smh]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:247)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:79)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:359) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:326) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_51]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_51]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_51]
> at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]
> Caused by: org.postgresql.util.PSQLException: ERROR: INSERT has more expressions than target columns
> Position: 74
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:218)
> ... 14 more
> {code}
> It looks like the rewriting is completely wrong, since Teiid is trying to insert 2 fields into a single one.
>
> In order to reproduce the bug, it is necessary that test_a and the Test_Insert tables are both in the same schema, so that the full command can be pushed down. Moreover, the bug can be reproduced in PostgreSQL, Oracle, MS SQL Server, but it correctly works in MySQL.
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
10 years, 1 month
[JBoss JIRA] (TEIID-2963) ORDER BY and UNION fails
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2963?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-2963.
-----------------------------------
Fix Version/s: 8.7.1
8.8
Resolution: Done
This affected pushdown set query order bys when a grouping value from the first set branch is used in the ordering. The fix is to ensure that the rewrite of the order by happens when there is a grouping present.
> ORDER BY and UNION fails
> ------------------------
>
> Key: TEIID-2963
> URL: https://issues.jboss.org/browse/TEIID-2963
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7, 8.8
> Reporter: Tom Arnold
> Assignee: Steven Hawkins
> Labels: oracle, order, select, union
> Fix For: 8.7.1, 8.8
>
>
> I can reproduce this in Teiid 8.7.0.Final and nightly build from master (09e396f). The same query works fine when performed directly against Oracle.
> {code:sql}
> select
> "FOO"."FOO_ID" "FOO_ID"
> from (
> (select "ITEMS"."ITEM_ID" "FOO_ID" from "BLAH"."ITEMS" "ITEMS" group by "ITEMS"."ITEM_ID")
> union all
> (select "ITEMS"."ITEM_ID" "FOO_ID" from "BLAH"."ITEMS" "ITEMS")
> ) "FOO"
> order by "FOO_ID" desc;
> {code}
> {code}
> 03:47:21,218 WARN [org.teiid.CONNECTOR] (Worker7_QueryProcessorQueue189) 6w9Z3uBcAONA Connector worker process failed for atomic-request=6w9Z3uBcAONA.8.1.39: org.teiid.translator.jdbc.JDBCExecutionException: 904 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_1."ITEM_ID" AS c_0 FROM "BLAH"."ITEMS" g_1 GROUP BY g_1."ITEM_ID" UNION ALL SELECT g_0."ITEM_ID" AS c_0 FROM "BLAH"."ITEMS" g_0 ORDER BY FOO_ID DESC]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:326) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) [rt.jar:1.7.0_02]
> at java.util.concurrent.FutureTask.run(FutureTask.java:166) [rt.jar:1.7.0_02]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) [rt.jar:1.7.0_02]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) [rt.jar:1.7.0_02]
> at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0_02]
> Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "FOO_ID": invalid identifier
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
> at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
> at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
> at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
> at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
> at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 13 more
> {code}
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
10 years, 1 month
[JBoss JIRA] (TEIID-2964) User can not use '.' for VDB name in order to version the VDB
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2964?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-2964:
----------------------------------
Description:
User can not use '.' for VDB name.
According to the Official document[1], user should be able to use '.' to specify the version for VDB.
[1]Administration_and_Configuration_Guide:11.2. Set the VDB Version
https://access.redhat.com/site/documentation/en-US/Red_Hat_JBoss_Data_Vir...
Version-Release number of selected component (if applicable):
Steps to Reproduce:
1. Create a new VDB named "test1.1"
Actual results:
The version attribute from the vdb.xml is used instead.
Expected results:
User can use '.' for VDB name.
was:
User can not use '.' for VDB name.
According to the Official document[1], user should be able to use '.' to specify the version for VDB.
[1]Administration_and_Configuration_Guide:11.2. Set the VDB Version
https://access.redhat.com/site/documentation/en-US/Red_Hat_JBoss_Data_Vir...
Version-Release number of selected component (if applicable):
Steps to Reproduce:
1. Create a new VDB named "test1.1"
Actual results:
error occurs. Please check attached screenshot named vdb_versioning_error.png
Expected results:
User can use '.' for VDB name.
Additional info:
Teiid Designer 8.3.2.Final-v20140328-2123-B54
Assignee: (was: Barry LaFond)
> User can not use '.' for VDB name in order to version the VDB
> -------------------------------------------------------------
>
> Key: TEIID-2964
> URL: https://issues.jboss.org/browse/TEIID-2964
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Reporter: Van Halbert
> Priority: Critical
>
> User can not use '.' for VDB name.
> According to the Official document[1], user should be able to use '.' to specify the version for VDB.
> [1]Administration_and_Configuration_Guide:11.2. Set the VDB Version
> https://access.redhat.com/site/documentation/en-US/Red_Hat_JBoss_Data_Vir...
> Version-Release number of selected component (if applicable):
> Steps to Reproduce:
> 1. Create a new VDB named "test1.1"
> Actual results:
> The version attribute from the vdb.xml is used instead.
> Expected results:
> User can use '.' for VDB name.
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
10 years, 1 month
[JBoss JIRA] (TEIID-2964) User can not use '.' for VDB name in order to version the VDB
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2964?page=com.atlassian.jira.plugin... ]
Steven Hawkins moved TEIIDDES-2166 to TEIID-2964:
-------------------------------------------------
Project: Teiid (was: Teiid Designer)
Key: TEIID-2964 (was: TEIIDDES-2166)
Affects Version/s: (was: 8.3.2)
Component/s: Server
(was: VDB & Execution)
> User can not use '.' for VDB name in order to version the VDB
> -------------------------------------------------------------
>
> Key: TEIID-2964
> URL: https://issues.jboss.org/browse/TEIID-2964
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Reporter: Van Halbert
> Assignee: Barry LaFond
> Priority: Critical
>
> User can not use '.' for VDB name.
> According to the Official document[1], user should be able to use '.' to specify the version for VDB.
> [1]Administration_and_Configuration_Guide:11.2. Set the VDB Version
> https://access.redhat.com/site/documentation/en-US/Red_Hat_JBoss_Data_Vir...
> Version-Release number of selected component (if applicable):
> Steps to Reproduce:
> 1. Create a new VDB named "test1.1"
> Actual results:
> error occurs. Please check attached screenshot named vdb_versioning_error.png
> Expected results:
> User can use '.' for VDB name.
> Additional info:
> Teiid Designer 8.3.2.Final-v20140328-2123-B54
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
10 years, 1 month
[JBoss JIRA] (TEIID-2965) wrong rewriting on "insert into ... select" statement - Teiid 8.8.0.Alpha1
by Alex K. (JIRA)
Alex K. created TEIID-2965:
------------------------------
Summary: wrong rewriting on "insert into ... select" statement - Teiid 8.8.0.Alpha1
Key: TEIID-2965
URL: https://issues.jboss.org/browse/TEIID-2965
Project: Teiid
Issue Type: Feature Request
Affects Versions: 8.8
Reporter: Alex K.
Assignee: Steven Hawkins
Having created a PostgreSQL Table like:
{code:sql}
CREATE TABLE Test_Insert
(
status character varying(4000)
)
{code}
And then running this statement
{code:sql}
INSERT INTO test_tables_pg.Test_Insert
SELECT
CASE WHEN (status = '0') AND (cnt > 0) THEN '4' ELSE status END AS status
FROM
(SELECT
(SELECT COUNT(*) FROM test_tables_pg.test_a AS smh2) AS cnt,
a AS status
FROM test_tables_pg.test_a AS smh
) AS a
{code}
I get the following exception:
{code}
16:18:34,427 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue16) ii4bbeYlV1WF TEIID30020 Processing exception for request ii4bbeYlV1WF.4 'TEIID30504 test_tables_pg: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: INSERT INTO "public"."test_insert" ("status") SELECT smh."a" AS status, (SELECT COUNT(*) FROM "public"."test_a" AS g_0) AS cnt FROM "public"."test_a" AS smh]'. Originally TeiidProcessingException 'ERROR: INSERT has more expressions than target columns
Position: 74' QueryExecutorImpl.java:2157. Enable more detailed logging to see the entire stacktrace.
16:20:12,498 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue22) ii4bbeYlV1WF Connector worker process failed for atomic-request=ii4bbeYlV1WF.6.2.6: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: INSERT INTO "public"."test_insert" ("status") SELECT
smh."a" AS status, (SELECT COUNT(*) FROM "public"."test_a" AS g_0) AS cnt FROM "public"."test_a" AS smh]
at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:247)
at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:79)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:359) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:326) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_51]
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_51]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_51]
at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]
Caused by: org.postgresql.util.PSQLException: ERROR: INSERT has more expressions than target columns
Position: 74
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:363)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493)
at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:218)
... 14 more
{code}
It looks like the rewriting is completely wrong, since Teiid is trying to insert 2 fields into a single one.
In order to reproduce the bug, it is necessary that test_a and the Test_Insert tables are both in the same schema, so that the full command can be pushed down. Moreover, the bug can be reproduced in PostgreSQL, Oracle, MS SQL Server, but it correctly works in MySQL.
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
10 years, 1 month
[JBoss JIRA] (TEIID-2963) ORDER BY and UNION fails
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2963?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-2963:
---------------------------------------
Yes, I can reproduce that we have the wrong name in the order by clause. I should have a fix tomorrow.
> ORDER BY and UNION fails
> ------------------------
>
> Key: TEIID-2963
> URL: https://issues.jboss.org/browse/TEIID-2963
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7, 8.8
> Reporter: Tom Arnold
> Assignee: Steven Hawkins
> Labels: oracle, order, select, union
>
> I can reproduce this in Teiid 8.7.0.Final and nightly build from master (09e396f). The same query works fine when performed directly against Oracle.
> {code:sql}
> select
> "FOO"."FOO_ID" "FOO_ID"
> from (
> (select "ITEMS"."ITEM_ID" "FOO_ID" from "BLAH"."ITEMS" "ITEMS" group by "ITEMS"."ITEM_ID")
> union all
> (select "ITEMS"."ITEM_ID" "FOO_ID" from "BLAH"."ITEMS" "ITEMS")
> ) "FOO"
> order by "FOO_ID" desc;
> {code}
> {code}
> 03:47:21,218 WARN [org.teiid.CONNECTOR] (Worker7_QueryProcessorQueue189) 6w9Z3uBcAONA Connector worker process failed for atomic-request=6w9Z3uBcAONA.8.1.39: org.teiid.translator.jdbc.JDBCExecutionException: 904 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_1."ITEM_ID" AS c_0 FROM "BLAH"."ITEMS" g_1 GROUP BY g_1."ITEM_ID" UNION ALL SELECT g_0."ITEM_ID" AS c_0 FROM "BLAH"."ITEMS" g_0 ORDER BY FOO_ID DESC]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:326) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) [rt.jar:1.7.0_02]
> at java.util.concurrent.FutureTask.run(FutureTask.java:166) [rt.jar:1.7.0_02]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) [rt.jar:1.7.0_02]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) [rt.jar:1.7.0_02]
> at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0_02]
> Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "FOO_ID": invalid identifier
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
> at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
> at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
> at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
> at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
> at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 13 more
> {code}
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
10 years, 1 month
[JBoss JIRA] (TEIID-2963) ORDER BY and UNION fails
by Tom Arnold (JIRA)
Tom Arnold created TEIID-2963:
---------------------------------
Summary: ORDER BY and UNION fails
Key: TEIID-2963
URL: https://issues.jboss.org/browse/TEIID-2963
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 8.7, 8.8
Reporter: Tom Arnold
Assignee: Steven Hawkins
I can reproduce this in Teiid 8.7.0.Final and nightly build from master (09e396f). The same query works fine when performed directly against Oracle.
{code:sql}
select
"FOO"."FOO_ID" "FOO_ID"
from (
(select "ITEMS"."ITEM_ID" "FOO_ID" from "BLAH"."ITEMS" "ITEMS" group by "ITEMS"."ITEM_ID")
union all
(select "ITEMS"."ITEM_ID" "FOO_ID" from "BLAH"."ITEMS" "ITEMS")
) "FOO"
order by "FOO_ID" desc;
{code}
{code}
03:47:21,218 WARN [org.teiid.CONNECTOR] (Worker7_QueryProcessorQueue189) 6w9Z3uBcAONA Connector worker process failed for atomic-request=6w9Z3uBcAONA.8.1.39: org.teiid.translator.jdbc.JDBCExecutionException: 904 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_1."ITEM_ID" AS c_0 FROM "BLAH"."ITEMS" g_1 GROUP BY g_1."ITEM_ID" UNION ALL SELECT g_0."ITEM_ID" AS c_0 FROM "BLAH"."ITEMS" g_0 ORDER BY FOO_ID DESC]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:326) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) [rt.jar:1.7.0_02]
at java.util.concurrent.FutureTask.run(FutureTask.java:166) [rt.jar:1.7.0_02]
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.8.0.Alpha2-SNAPSHOT.jar:8.8.0.Alpha2-SNAPSHOT]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) [rt.jar:1.7.0_02]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) [rt.jar:1.7.0_02]
at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0_02]
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "FOO_ID": invalid identifier
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
... 13 more
{code}
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
10 years, 1 month
[JBoss JIRA] (TEIID-2481) common table (with) push down improvements
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2481?page=com.atlassian.jira.plugin... ]
Work on TEIID-2481 started by Steven Hawkins.
> common table (with) push down improvements
> ------------------------------------------
>
> Key: TEIID-2481
> URL: https://issues.jboss.org/browse/TEIID-2481
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.8
>
>
> The general strategy for with pushdown should better handle:
> - a with clause that can be pushed against a source that does not support common table. If a single reference or some other sufficient condition is met then we should just inline the pushdown rather than inhibiting the whole query from pushing down.
> - even if a source supports with pushdown, it may be more performant in some circumstances to inline the common table reference (such as only a single reference). This is complicated slightly by subqueries.
> - alternatively in some situations even if a with clause can be pushed, it may be better to not perform the pushdown -e.g. two separate pushdown accesses to a common table that is expensive to compute (this may need to be hint driven).
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
10 years, 1 month