[JBoss JIRA] (TEIID-2822) Not a Group By expression witn non-equi join
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2822?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-2822.
---------------------------------
> Not a Group By expression witn non-equi join
> --------------------------------------------
>
> Key: TEIID-2822
> URL: https://issues.jboss.org/browse/TEIID-2822
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 7.7
> Reporter: Alex K.
> Assignee: Steven Hawkins
> Fix For: 8.7
>
>
> I'm facing a problem with the following statement on Teiid 8.1 and 8.6 and Oracle
> {code:sql}
> SELECT
> A.a
> FROM
> oracle_db.TEST_A A, (select B from mysql.TEST_B) B
> WHERE
> A.b = '1' OR B.b IS NULL
> GROUP BY a
> {code}
> The exception is "ora-00979 not a group by expression" like following:
> {code:sql}
> 23:21:47,810 WARN [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue28) ffPh7k6iRVl7 Connector worker process failed for atomic-request=ffPh7k6iRVl7.9.6.2: org.teiid.tra
> nslator.jdbc.JDBCExecutionException: 979 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."B", g_0."A" FROM "ORACLE"."TEST_A" g_0
> GROUP BY g_0."A"]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:317) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:135) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:369) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.MergeJoinStrategy.process(MergeJoinStrategy.java:149) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:211) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:89) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.SortUtility.initialSort(SortUtility.java:269) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.SortUtility.sort(SortUtility.java:202) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.GroupingNode.sortPhase(GroupingNode.java:392) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:331) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:102) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:159) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:141) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:435) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:320) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:273) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.6.0.Final.jar:8.6.0.Final]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_25]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_25]
> at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_25]
> Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: Not a GROUP BY expression
>
>
> at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
> at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
> at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
> at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
> at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
> at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
> at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
> at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
> at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 37 more {code}
> It looks like the group by clause is wrongly pushed down to Oracle (should be processed directly by Teiid, as Oracle cannot handle g_0.B as it is not in the group by clause):
> {code}
> ============================================================================
> CONVERTING PLAN TREE TO PROCESS TREE
>
>
> PROCESS PLAN =
> ProjectNode(2) output=[anon_grp0.gcol0 AS A] [anon_grp0.gcol0 AS A]
> LimitNode(3) output=[anon_grp0.gcol0] limit 100
> GroupingNode(4) output=[anon_grp0.gcol0] [anon_grp1.gcol0]{anon_grp0.gcol0=anon_grp1.gcol0}
> JoinNode(5) [NESTED LOOP JOIN] [INNER JOIN] criteria=[(A.B = 1) OR (mysql.test_b.b IS NULL)] output=[anon_grp1.gcol0]
> AccessNode(6) output=[A.B, anon_grp1.gcol0] SELECT g_0.B, g_0.A FROM oracle_DB.TEST_A AS g_0 GROUP BY g_0.A
> AccessNode(7) output=[mysql.test_b.b] SELECT g_0.b FROM mysql.test_b AS g_0
> {code}
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
11 years, 11 months
[JBoss JIRA] (TEIID-2864) With select fails on Teiid 8.7-beta1 on MS SQL Server
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2864?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-2864.
---------------------------------
> With select fails on Teiid 8.7-beta1 on MS SQL Server
> -----------------------------------------------------
>
> Key: TEIID-2864
> URL: https://issues.jboss.org/browse/TEIID-2864
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.4
> Reporter: Alex K.
> Assignee: Steven Hawkins
> Fix For: 8.7
>
>
> following query fails for me on Teiid 8.7-beta1 on MS SQL Server in queries containing WITH, UNION ALL and LIMIT clauses (The exception is being thrown only when having LIMIT clause, without the LIMIT it seems working)
> {code:sql}
> WITH qry_0 as
> (SELECT t1.f a FROM test_tables_ms.test_e AS t1 GROUP BY t1.f)
> SELECT a as a1 from qry_0
> union all
> SELECT a from qry_0
> LIMIT 10
> {code}
> Exception:
> {code:java}
> WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue30) beKbgt/pu4f4 Connector worker process failed for atomic-request=beKbgt/pu4f4.11.2.6: org.teiid.translator.jdbc.JDBCExecutionException: 156 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT TOP 10 * FROM (WITH qry_0 (a) AS (SELECT g_0."f" FROM "test_tables"."dbo"."test_e" g_0 GROUP BY g_0."f") SELECT g_1.a AS c_0 FROM qry_0 g_1 UNION ALL SELECT g_0.a AS c_0 FROM qry_0 g_0) AS X]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:317) [teiid-engine-8.7.0.Beta1.jar:8.7.0.Beta1]
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.7.0.Beta1.jar:8.7.0.Beta1]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.7.0.Beta1.jar:8.7.0.Beta1]
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.7.0.Beta1.jar:8.7.0.Beta1]
> at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) [rt.jar:1.7.0_25]
> at java.util.concurrent.FutureTask.run(FutureTask.java:166) [rt.jar:1.7.0_25]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.7.0.Beta1.jar:8.7.0.Beta1]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:273) [teiid-engine-8.7.0.Beta1.jar:8.7.0.Beta1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.0.Beta1.jar:8.7.0.Beta1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.7.0.Beta1.jar:8.7.0.Beta1]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_25]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_25]
> at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_25]
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'WITH'.
> at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
> at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
> at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
> at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
> at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
> ... 13 more
> 19:23:44,606 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue31) beKbgt/pu4f4 TEIID30020 Processing exception for request beKbgt/pu4f4.11 'TEIID30504 test_tables_ms: 156 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT TOP 10 * FROM (WITH qry_0 (a) AS (SELECT g_0."f" FROM "test_tables"."dbo"."test_e" g_0 GROUP BY g_0."f") SELECT g_1.a AS c_0 FROM qry_0 g_1 UNION ALL SELECT g_0.a AS c_0 FROM qry_0 g_0) AS X]'. Originally TeiidProcessingException 'Incorrect syntax near the keyword 'WITH'.' SQLServerException.java:216. Enable more detailed logging to see the entire stacktrace.
> {code}
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
11 years, 11 months
[JBoss JIRA] (TEIID-2843) MongoDB: ClassCastException while doing JOIN based query
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2843?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-2843.
---------------------------------
> MongoDB: ClassCastException while doing JOIN based query
> --------------------------------------------------------
>
> Key: TEIID-2843
> URL: https://issues.jboss.org/browse/TEIID-2843
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.4
> Reporter: Ramesh Reddy
> Assignee: Ramesh Reddy
> Labels: Alpha3, mongodb
> Fix For: 8.7
>
>
> With Schema like
> {code}
> CREATE FOREIGN TABLE Company (
> name varchar(50) PRIMARY KEY,
> description varchar(256)
> ) OPTIONS(UPDATABLE 'TRUE',"teiid_mongo:EMBEDDABLE" 'true');
> CREATE FOREIGN TABLE Job (
> jobId integer PRIMARY KEY,
> description varchar(256),
> companyname varchar(50),
> FOREIGN KEY (companyname) REFERENCES Company (name)
> ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:EMBEDDABLE" 'true');
>
> CREATE FOREIGN TABLE Employee (
> ename varchar(50) PRIMARY KEY,
> jobId integer,
> FOREIGN KEY (jobId) REFERENCES Job (jobId)
> ) OPTIONS(UPDATABLE 'TRUE');
> {code}
> then doing a single JOIN Query
> {code}
> select e.ename, c.name, j.description from Employee as e JOIN Job j ON e.jobId = j.jobId JOIN Company c ON j.companyname = c.name
> {code}
> fails with
> {code}
> 14:22:05,271 ERROR [org.teiid.PROCESSOR] (Worker23_QueryProcessorQueue432) 258Se2CWMytI TEIID30019 Unexpected exception for request 258Se2CWMytI.28: java.lang.ClassCastException: org.teiid.language.Join cannot be cast to org.teiid.language.NamedTable
> at org.teiid.translator.mongodb.MongoDBSelectVisitor.visit(MongoDBSelectVisitor.java:419)
> at org.teiid.language.Join.acceptVisitor(Join.java:66) [teiid-api-8.7.0.Alpha3-SNAPSHOT.jar:8.7.0.Alpha3-SNAPSHOT]
> at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51) [teiid-api-8.7.0.Alpha3-SNAPSHOT.jar:8.7.0.Alpha3-SNAPSHOT]
> at org.teiid.translator.mongodb.MongoDBSelectVisitor.append(MongoDBSelectVisitor.java:95)
> at org.teiid.translator.mongodb.MongoDBSelectVisitor.append(MongoDBSelectVisitor.java:106)
> at org.teiid.translator.mongodb.MongoDBSelectVisitor.visit(MongoDBSelectVisitor.java:482)
> at org.teiid.language.Select.acceptVisitor(Select.java:103) [teiid-api-8.7.0.Alpha3-SNAPSHOT.jar:8.7.0.Alpha3-SNAPSHOT]
> at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51) [teiid-api-8.7.0.Alpha3-SNAPSHOT.jar:8.7.0.Alpha3-SNAPSHOT]
> {code}
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
11 years, 11 months
[JBoss JIRA] (TEIID-2859) Provide identity generation for TEXTTABLE and OBJECTTABLE
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2859?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-2859.
---------------------------------
> Provide identity generation for TEXTTABLE and OBJECTTABLE
> ---------------------------------------------------------
>
> Key: TEIID-2859
> URL: https://issues.jboss.org/browse/TEIID-2859
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Ramesh Reddy
> Assignee: Steven Hawkins
> Fix For: 8.7
>
>
> Extend the functionality FOR ORDINALITY defined on XMLTABLE, to TEXTTABLE and OBJECTTABLE.
> This will useful in providing a automatic identity key if the data is being read from sources where there is identity column provided. The faced the issue when loading a text file like
> {code}
> VA,F,1910,Mary,848
> VA,F,1911,Mary,747
> {code}
> Where name each line is a separate row, but they do not have unique identifier attached with them. Also helpful with interfaces like OData where they require a PK on a table. This provides an easier alternative.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
11 years, 11 months