[teiid-issues] [JBoss JIRA] (TEIID-2775) HIVE2: cannot run ORDER BY SQL without LIMIT

Ivan Chan (JIRA) issues at jboss.org
Fri Dec 13 19:28:32 EST 2013 

     [ https://issues.jboss.org/browse/TEIID-2775?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Ivan Chan updated TEIID-2775:
-----------------------------

    Description: 
Cannot run SQL with order by, but without limit clause.

If I run a sql like the following with HIVE2:

select "account_type"
from "hive2DS"."accounts"
group by "account_type"
order by "account_type"

Teiid would convert it to:

SELECT g_0.account_type AS c_0 FROM accounts g_0 GROUP BY g_0.account_type ORDER BY c_0

And I will get the following exception:

[3:51:04 PM] Natalia: org.teiid.jdbc.TeiidSQLException: Error trying to obtain metadata information for the tables that match %: TEIID30489 Unable to load metadata for VDB name

Caused by: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.account_type AS c_0 FROM accounts g_0 GROUP BY g_0.account_type ORDER BY c_0]
 at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
 at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:317)
 at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
 at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
 at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
 at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
 at java.util.concurrent.FutureTask.run(FutureTask.java:166)
 at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
 at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:273)
 at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
 at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
 ... 3 more
Caused by: java.sql.SQLException: NotImplementedException: ORDER BY without LIMIT currently not supported
 at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:165)
 at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:153)
 at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:198)
 at org.apache.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:300)
 at org.apache.hive.jdbc.HivePreparedStatement.executeQuery(HivePreparedStatement.java:106)
 at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
 at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
 at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
 ... 13 more


Maybe you guys should consider to do order by in memory if LIMIT is not presented.  Thanks.

  was:
Cannot run SQL with order by, but without limit clause.

If I run a sql like the following with HIVE2:

select "account_type"
from "hive2DS"."accounts"
group by "account_type"
order by "account_type"

Teiid would convert it to:

SELECT g_0.account_type AS c_0 FROM accounts g_0 GROUP BY g_0.account_type ORDER BY c_0

And I will get the following exception:

[3:51:04 PM] Natalia: org.teiid.jdbc.TeiidSQLException: Error trying to obtain metadata information for the tables that match %: TEIID30489 Unable to load metadata for VDB name

Caused by: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.account_type AS c_0 FROM accounts g_0 GROUP BY g_0.account_type ORDER BY c_0]
 at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
 at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:317)
 at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
 at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
 at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
 at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
 at java.util.concurrent.FutureTask.run(FutureTask.java:166)
 at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
 at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:273)
 at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
 at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
 ... 3 more
Caused by: java.sql.SQLException: NotImplementedException: ORDER BY without LIMIT currently not supported
 at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:165)
 at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:153)
 at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:198)
 at org.apache.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:300)
 at org.apache.hive.jdbc.HivePreparedStatement.executeQuery(HivePreparedStatement.java:106)
 at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
 at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
 at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
 ... 13 more


    
> HIVE2:  cannot run ORDER BY SQL without LIMIT
> ---------------------------------------------
>
>                 Key: TEIID-2775
>                 URL: https://issues.jboss.org/browse/TEIID-2775
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 8.6
>         Environment: HIVE2 with Teiid
>            Reporter: Ivan Chan
>            Assignee: Steven Hawkins
>              Labels: teiid
>
> Cannot run SQL with order by, but without limit clause.
> If I run a sql like the following with HIVE2:
> select "account_type"
> from "hive2DS"."accounts"
> group by "account_type"
> order by "account_type"
> Teiid would convert it to:
> SELECT g_0.account_type AS c_0 FROM accounts g_0 GROUP BY g_0.account_type ORDER BY c_0
> And I will get the following exception:
> [3:51:04 PM] Natalia: org.teiid.jdbc.TeiidSQLException: Error trying to obtain metadata information for the tables that match %: TEIID30489 Unable to load metadata for VDB name
> Caused by: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.account_type AS c_0 FROM accounts g_0 GROUP BY g_0.account_type ORDER BY c_0]
>  at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
>  at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:317)
>  at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
>  at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
>  at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
>  at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
>  at java.util.concurrent.FutureTask.run(FutureTask.java:166)
>  at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
>  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:273)
>  at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
>  at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
>  ... 3 more
> Caused by: java.sql.SQLException: NotImplementedException: ORDER BY without LIMIT currently not supported
>  at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:165)
>  at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:153)
>  at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:198)
>  at org.apache.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:300)
>  at org.apache.hive.jdbc.HivePreparedStatement.executeQuery(HivePreparedStatement.java:106)
>  at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
>  at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
>  at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
>  ... 13 more
> Maybe you guys should consider to do order by in memory if LIMIT is not presented.  Thanks.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

More information about the teiid-issues mailing list