[jira] [Commented] (HIVE-8293) Metastore direct SQL failed for Oracle becasue ORA-01722: invalid number
[
https://issues.apache.org/jira/browse/HIVE-8293?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14162533#comment-14162533
]
Sergey Shelukhin commented on HIVE-8293:
1) Can you please retain the quotes? they are necessary for Postgres.
2) Can you update comment to say it affects Oracle too?
Derby guys seem to agree this is actually ANSI SQL violation to do this, so
they have a bug. I wonder if one can report bug to Oracle... :)
Metastore direct SQL failed for Oracle becasue ORA-01722: invalid number
Key: HIVE-8293
URL: https://issues.apache.org/jira/browse/HIVE-8293
Project: Hive
Issue Type: Bug
Affects Versions: 0.13.1
Reporter: Selina Zhang
Assignee: Selina Zhang
Attachments: HIVE-8293.1.patch
The direct SQL route of retrieve partition objects through filters failed for
Oracle. Similar as DERBY-6358, Oracle tries to cast PART_KEY_VALUE in
PARTITION_KEY_VALs table to decimal before evaluate the condition.
Here is the stack trace:
{code}
2014-09-29 18:53:53,490 ERROR [pool-1-thread-1] metastore.ObjectStore
(ObjectStore.java:handleDirectSqlError(2248)) - Direct SQL failed, falling
back to ORM
javax.jdo.JDODataStoreException: Error executing SQL query select
PARTITIONS.PART_ID from PARTITIONS inner join TBLS on
PARTITIONS.TBL_ID = TBLS.TBL_ID and TBLS.TBL_NAME = ? inner
join DBS on TBLS.DB_ID = DBS.DB_ID and DBS.NAME = ? inner
join PARTITION_KEY_VALS FILTER0 on FILTER0.PART_ID =
PARTITIONS.PART_ID and FILTER0.INTEGER_IDX = 0 where (((case when
TBLS.TBL_NAME = ? and DBS.NAME = ? then cast(FILTER0.PART_KEY_VAL
as decimal(21,0)) else null end) ?)).
at
org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:422)
at org.datanucleus.api.jdo.JDOQuery.executeWithArray(JDOQuery.java:321)
at
org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getPartitionsViaSqlFilterInternal(MetaStoreDirectSql.java:300)
at
org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getPartitionsViaSqlFilter(MetaStoreDirectSql.java:211)
at
org.apache.hadoop.hive.metastore.ObjectStore$3.getSqlResult(ObjectStore.java:1920)
at
org.apache.hadoop.hive.metastore.ObjectStore$3.getSqlResult(ObjectStore.java:1914)
at
org.apache.hadoop.hive.metastore.ObjectStore$GetHelper.run(ObjectStore.java:2213)
at
org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByExprInternal(ObjectStore.java:1914)
at
org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByExpr(ObjectStore.java:1887)
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.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:98)
at com.sun.proxy.$Proxy8.getPartitionsByExpr(Unknown Source)
at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_partitions_by_expr(HiveMetaStore.java:3800)
at
org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_partitions_by_expr.getResult(ThriftHiveMetastore.java:9366)
at
org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_partitions_by_expr.getResult(ThriftHiveMetastore.java:9350)
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
at
org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge20S$Server$TUGIAssumingProcessor$1.run(HadoopThriftAuthBridge20S.java:617)
at
org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge20S$Server$TUGIAssumingProcessor$1.run(HadoopThriftAuthBridge20S.java:613)
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:1637)
at
org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge20S$Server$TUGIAssumingProcessor.process(HadoopThriftAuthBridge20S.java:613)
at
org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run_aroundBody0(TThreadPoolServer.java:206)
at
org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run_aroundBody1$advice(TThreadPoolServer.java:101)
at
org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:1)
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)
[jira] [Commented] (HIVE-8293) Metastore direct SQL failed for Oracle becasue ORA-01722: invalid number
[
https://issues.apache.org/jira/browse/HIVE-8293?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14152087#comment-14152087
]
Selina Zhang commented on HIVE-8293:
It is easy to reproduce:
{block}
hive create table a (col string) partitioned by (dt string);
hive create table b (col string) partitioned by (idx int);
hive alter table a add partition(dt='20140808');
hive alter table b add partition(idx=50);
hive select * from b where idx 10;
{block}
Metastore direct SQL failed for Oracle becasue ORA-01722: invalid number
Key: HIVE-8293
URL: https://issues.apache.org/jira/browse/HIVE-8293
Project: Hive
Issue Type: Bug
Affects Versions: 0.13.1
Reporter: Selina Zhang
Assignee: Selina Zhang
The direct SQL route of retrieve partition objects through filters failed for
Oracle. Similar as DERBY-6358, Oracle tries to cast PART_KEY_VALUE in
PARTITION_KEY_VALs table to decimal before evaluate the condition.
Here is the stack trace:
{quote}
2014-09-29 18:53:53,490 ERROR [pool-1-thread-1] metastore.ObjectStore
(ObjectStore.java:handleDirectSqlError(2248)) - Direct SQL failed, falling
back to ORM
javax.jdo.JDODataStoreException: Error executing SQL query select
PARTITIONS.PART_ID from PARTITIONS inner join TBLS on
PARTITIONS.TBL_ID = TBLS.TBL_ID and TBLS.TBL_NAME = ? inner
join DBS on TBLS.DB_ID = DBS.DB_ID and DBS.NAME = ? inner
join PARTITION_KEY_VALS FILTER0 on FILTER0.PART_ID =
PARTITIONS.PART_ID and FILTER0.INTEGER_IDX = 0 where (((case when
TBLS.TBL_NAME = ? and DBS.NAME = ? then cast(FILTER0.PART_KEY_VAL
as decimal(21,0)) else null end) ?)).
at
org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:422)
at org.datanucleus.api.jdo.JDOQuery.executeWithArray(JDOQuery.java:321)
at
org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getPartitionsViaSqlFilterInternal(MetaStoreDirectSql.java:300)
at
org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getPartitionsViaSqlFilter(MetaStoreDirectSql.java:211)
at
org.apache.hadoop.hive.metastore.ObjectStore$3.getSqlResult(ObjectStore.java:1920)
at
org.apache.hadoop.hive.metastore.ObjectStore$3.getSqlResult(ObjectStore.java:1914)
at
org.apache.hadoop.hive.metastore.ObjectStore$GetHelper.run(ObjectStore.java:2213)
at
org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByExprInternal(ObjectStore.java:1914)
at
org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByExpr(ObjectStore.java:1887)
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.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:98)
at com.sun.proxy.$Proxy8.getPartitionsByExpr(Unknown Source)
at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_partitions_by_expr(HiveMetaStore.java:3800)
at
org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_partitions_by_expr.getResult(ThriftHiveMetastore.java:9366)
at
org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_partitions_by_expr.getResult(ThriftHiveMetastore.java:9350)
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
at
org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge20S$Server$TUGIAssumingProcessor$1.run(HadoopThriftAuthBridge20S.java:617)
at
org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge20S$Server$TUGIAssumingProcessor$1.run(HadoopThriftAuthBridge20S.java:613)
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:1637)
at
org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge20S$Server$TUGIAssumingProcessor.process(HadoopThriftAuthBridge20S.java:613)
at
org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run_aroundBody0(TThreadPoolServer.java:206)
at
org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run_aroundBody1$advice(TThreadPoolServer.java:101)
at
org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:1)
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)
NestedThrowablesStackTrace:
[jira] [Commented] (HIVE-8293) Metastore direct SQL failed for Oracle becasue ORA-01722: invalid number
[
https://issues.apache.org/jira/browse/HIVE-8293?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14152745#comment-14152745
]
Hive QA commented on HIVE-8293:
---
{color:red}Overall{color}: -1 at least one tests failed
Here are the results of testing the latest attachment:
https://issues.apache.org/jira/secure/attachment/12671854/HIVE-8293.1.patch
{color:red}ERROR:{color} -1 due to 1 failed/errored test(s), 6371 tests executed
*Failed tests:*
{noformat}
org.apache.hive.hcatalog.pig.TestOrcHCatLoader.testReadDataPrimitiveTypes
{noformat}
Test results:
http://ec2-174-129-184-35.compute-1.amazonaws.com/jenkins/job/PreCommit-HIVE-TRUNK-Build/1044/testReport
Console output:
http://ec2-174-129-184-35.compute-1.amazonaws.com/jenkins/job/PreCommit-HIVE-TRUNK-Build/1044/console
Test logs:
http://ec2-174-129-184-35.compute-1.amazonaws.com/logs/PreCommit-HIVE-TRUNK-Build-1044/
Messages:
{noformat}
Executing org.apache.hive.ptest.execution.PrepPhase
Executing org.apache.hive.ptest.execution.ExecutionPhase
Executing org.apache.hive.ptest.execution.ReportingPhase
Tests exited with: TestsFailedException: 1 tests failed
{noformat}
This message is automatically generated.
ATTACHMENT ID: 12671854
Metastore direct SQL failed for Oracle becasue ORA-01722: invalid number
Key: HIVE-8293
URL: https://issues.apache.org/jira/browse/HIVE-8293
Project: Hive
Issue Type: Bug
Affects Versions: 0.13.1
Reporter: Selina Zhang
Assignee: Selina Zhang
Attachments: HIVE-8293.1.patch
The direct SQL route of retrieve partition objects through filters failed for
Oracle. Similar as DERBY-6358, Oracle tries to cast PART_KEY_VALUE in
PARTITION_KEY_VALs table to decimal before evaluate the condition.
Here is the stack trace:
{code}
2014-09-29 18:53:53,490 ERROR [pool-1-thread-1] metastore.ObjectStore
(ObjectStore.java:handleDirectSqlError(2248)) - Direct SQL failed, falling
back to ORM
javax.jdo.JDODataStoreException: Error executing SQL query select
PARTITIONS.PART_ID from PARTITIONS inner join TBLS on
PARTITIONS.TBL_ID = TBLS.TBL_ID and TBLS.TBL_NAME = ? inner
join DBS on TBLS.DB_ID = DBS.DB_ID and DBS.NAME = ? inner
join PARTITION_KEY_VALS FILTER0 on FILTER0.PART_ID =
PARTITIONS.PART_ID and FILTER0.INTEGER_IDX = 0 where (((case when
TBLS.TBL_NAME = ? and DBS.NAME = ? then cast(FILTER0.PART_KEY_VAL
as decimal(21,0)) else null end) ?)).
at
org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:422)
at org.datanucleus.api.jdo.JDOQuery.executeWithArray(JDOQuery.java:321)
at
org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getPartitionsViaSqlFilterInternal(MetaStoreDirectSql.java:300)
at
org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getPartitionsViaSqlFilter(MetaStoreDirectSql.java:211)
at
org.apache.hadoop.hive.metastore.ObjectStore$3.getSqlResult(ObjectStore.java:1920)
at
org.apache.hadoop.hive.metastore.ObjectStore$3.getSqlResult(ObjectStore.java:1914)
at
org.apache.hadoop.hive.metastore.ObjectStore$GetHelper.run(ObjectStore.java:2213)
at
org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByExprInternal(ObjectStore.java:1914)
at
org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByExpr(ObjectStore.java:1887)
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.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:98)
at com.sun.proxy.$Proxy8.getPartitionsByExpr(Unknown Source)
at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_partitions_by_expr(HiveMetaStore.java:3800)
at
org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_partitions_by_expr.getResult(ThriftHiveMetastore.java:9366)
at
org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_partitions_by_expr.getResult(ThriftHiveMetastore.java:9350)
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
at
org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge20S$Server$TUGIAssumingProcessor$1.run(HadoopThriftAuthBridge20S.java:617)
at
org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge20S$Server$TUGIAssumingProcessor$1.run(HadoopThriftAuthBridge20S.java:613)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:415)
at
