Between Clause Does Not Work In CDH 5 / Beeline Test Cluster (Doc ID 1900997.1)

Last updated on JUNE 29, 2014

Applies to:

Big Data Appliance Integrated Software - Version 3.0 and later
Linux x86-64

Symptoms

Trying to run an existing Hive CLI query (that runs fine on BDA v2.2.1/CDH 4.3) in Beeline / CDH 5.0 gives errors on the 'between clause'.

The query is like:

select count(*) from <table name> where year='2014' and month between '03' and '05' and day between '07' and '10';

Where this is a partitioned external table with partitioned columns of: year, month and day.

The error raised with CDH 5.0 is as follows:

0: jdbc:hive2://hadoop05:10000/default> select count(*) from <table name> where year='2014' and month between '03' and '05'  and day = '10';
Error: Error while compiling statement: FAILED: SemanticException MetaException(message:javax.jdo.JDOException: Invocation of method "substring" on "StringExpression" requires argument 1 of type "NumericExpression"
       at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:600)
       at org.datanucleus.api.jdo.JDOQuery.executeWithMap(JDOQuery.java:351)
       at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsViaOrmFilter(ObjectStore.java:2008)
       at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByExprInternal(ObjectStore.java:1884)
       at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByExpr(ObjectStore.java:1822)
       at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
       at java.lang.reflect.Method.invoke(Method.java:606)
       at org.apache.hadoop.hive.metastore.RetryingRawStore.invoke(RetryingRawStore.java:122)
       at com.sun.proxy.$Proxy0.getPartitionsByExpr(Unknown Source)
       at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_partitions_by_expr(HiveMetaStore.java:3328)
       at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
       at java.lang.reflect.Method.invoke(Method.java:606)
       at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:103)
       at com.sun.proxy.$Proxy5.get_partitions_by_expr(Unknown Source)
       at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_partitions_by_expr.getResult(ThriftHiveMetastore.java:7340)
       at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_partitions_by_expr.getResult(ThriftHiveMetastore.java:7324)
       at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
       at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:110)
       at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:107)
       at java.security.AccessController.doPrivileged(Native Method)
       at javax.security.auth.Subject.doAs(Subject.java:415)
       at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1548)
       at org.apache.hadoop.hive.shims.HadoopShimsSecure.doAs(HadoopShimsSecure.java:554)
       at org.apache.hadoop.hive.metastore.TUGIBasedProcessor.process(TUGIBasedProcessor.java:118)
       at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:244)
       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:744)
NestedThrowablesStackTrace:
Invocation of method "substring" on "StringExpression" requires argument 1 of type "NumericExpression"
org.datanucleus.exceptions.NucleusException: Invocation of method "substring" on "StringExpression" requires argument 1 of type "NumericExpression"
       at org.datanucleus.store.rdbms.sql.method.StringSubstringMethod.getExpression(StringSubstringMethod.java:85)
       at org.datanucleus.store.rdbms.sql.expression.SQLExpressionFactory.invokeMethod(SQLExpressionFactory.java:328)
       at org.datanucleus.store.rdbms.sql.expression.StringExpression.invoke(StringExpression.java:494)
       at org.datanucleus.store.rdbms.query.QueryToSQLMapper.processInvokeExpression(QueryToSQLMapper.java:2900)
       at org.datanucleus.query.evaluator.AbstractExpressionEvaluator.compilePrimaryExpression(AbstractExpressionEvaluator.java:200)
       at org.datanucleus.query.evaluator.AbstractExpressionEvaluator.compileUnaryExpression(AbstractExpressionEvaluator.java:169)
       at org.datanucleus.query.evaluator.AbstractExpressionEvaluator.compileAdditiveMultiplicativeExpression(AbstractExpressionEvaluator.java:148)
       at org.datanucleus.query.evaluator.AbstractExpressionEvaluator.compileRelationalExpression(AbstractExpressionEvaluator.java:123)
       at org.datanucleus.query.evaluator.AbstractExpressionEvaluator.compileOrAndExpression(AbstractExpressionEvaluator.java:65)
       at org.datanucleus.query.evaluator.AbstractExpressionEvaluator.evaluate(AbstractExpressionEvaluator.java:46)
       at org.datanucleus.query.expression.Expression.evaluate(Expression.java:337)
       at org.datanucleus.query.expression.DyadicExpression.evaluate(DyadicExpression.java:67)
       at org.datanucleus.query.expression.DyadicExpression.evaluate(DyadicExpression.java:67)
       at org.datanucleus.query.expression.DyadicExpression.evaluate(DyadicExpression.java:70)
       at org.datanucleus.query.expression.DyadicExpression.evaluate(DyadicExpression.java:67)
       at org.datanucleus.query.expression.DyadicExpression.evaluate(DyadicExpression.java:70)
       at org.datanucleus.store.rdbms.query.QueryToSQLMapper.compileFilter(QueryToSQLMapper.java:460)
       at org.datanucleus.store.rdbms.query.QueryToSQLMapper.compile(QueryToSQLMapper.java:380)
       at org.datanucleus.store.rdbms.query.JDOQLQuery.compileQueryFull(JDOQLQuery.java:922)
       at org.datanucleus.store.rdbms.query.JDOQLQuery.compileInternal(JDOQLQuery.java:356)
       at org.datanucleus.store.query.Query.executeQuery(Query.java:1744)
       at org.datanucleus.store.query.Query.executeWithMap(Query.java:1690)
       at org.datanucleus.api.jdo.JDOQuery.executeWithMap(JDOQuery.java:334)
       at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsViaOrmFilter(ObjectStore.java:2008)
       at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByExprInternal(ObjectStore.java:1884)
       at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByExpr(ObjectStore.java:1822)
       at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
       at java.lang.reflect.Method.invoke(Method.java:606)
       at org.apache.hadoop.hive.metastore.RetryingRawStore.invoke(RetryingRawStore.java:122)
       at com.sun.proxy.$Proxy0.getPartitionsByExpr(Unknown Source)
       at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_partitions_by_expr(HiveMetaStore.java:3328)
       at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
       at java.lang.reflect.Method.invoke(Method.java:606)
       at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:103)
       at com.sun.proxy.$Proxy5.get_partitions_by_expr(Unknown Source)
       at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_partitions_by_expr.getResult(ThriftHiveMetastore.java:7340)
       at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_partitions_by_expr.getResult(ThriftHiveMetastore.java:7324)
       at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
       at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:110)
       at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:107)
       at java.security.AccessController.doPrivileged(Native Method)
       at javax.security.auth.Subject.doAs(Subject.java:415)
       at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1548)
       at org.apache.hadoop.hive.shims.HadoopShimsSecure.doAs(HadoopShimsSecure.java:554)
       at org.apache.hadoop.hive.metastore.TUGIBasedProcessor.process(TUGIBasedProcessor.java:118)
       at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:244)
       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:744)
) (state=42000,code=40000)





Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms