Basapuram Kumar created HIVE-27847: -------------------------------------- Summary: Prevent query Failures on Numeric <-> Timestamp Key: HIVE-27847 URL: https://issues.apache.org/jira/browse/HIVE-27847 Project: Hive Issue Type: Bug Components: Hive Environment: master
4.0.0-alpha-1 Reporter: Basapuram Kumar Assignee: Basapuram Kumar In Master/4.0.0-alpha-1 branches, performing the Numeric to Timestamp conversion, its failing with the error as "{color:#de350b}org.apache.hadoop.hive.ql.exec.UDFArgumentException: Casting NUMERIC types to TIMESTAMP is prohibited (hive.strict.timestamp.conversion){color}" . *Repro steps.* # Sample data {noformat} $ hdfs dfs -cat /tmp/tc/t.csv 1653209895687,2022-05-22T15:58:15.931+07:00 1653209938316,2022-05-22T15:58:58.490+07:00 1653209962021,2022-05-22T15:59:22.191+07:00 1653210021993,2022-05-22T16:00:22.174+07:00 1653209890524,2022-05-22T15:58:10.724+07:00 1653210095382,2022-05-22T16:01:35.775+07:00 1653210044308,2022-05-22T16:00:44.683+07:00 1653210098546,2022-05-22T16:01:38.886+07:00 1653210012220,2022-05-22T16:00:12.394+07:00 1653210000376,2022-05-22T16:00:00.622+07:00{noformat} # table with above data [1] {noformat} create external table test_ts_conv(begin string, ts string) row format delimited fields terminated by ',' stored as TEXTFILE LOCATION '/tmp/tc/'; desc test_ts_conv; | col_name | data_type | comment | +-----------+------------+----------+ | begin | string | | | ts | string | | +-----------+------------+----------+{noformat} # Create table with CTAS {noformat} 0: jdbc:hive2://char1000.sre.iti.acceldata.de> set hive.strict.timestamp.conversion; +-----------------------------------------+ | set | +-----------------------------------------+ | hive.strict.timestamp.conversion=true | +-----------------------------------------+ set to false 0: jdbc:hive2://char1000.sre.iti.acceldata.de> set hive.strict.timestamp.conversion=false; +-----------------------------------------+ | set | +-----------------------------------------+ | hive.strict.timestamp.conversion=false | +-----------------------------------------+ #Query: 0: jdbc:hive2://char1000.sre.iti.acceldata.de> CREATE TABLE t_date AS select CAST( CAST( `begin` AS BIGINT) / 1000 AS TIMESTAMP ) `begin`, CAST( DATE_FORMAT(CAST(regexp_replace(`ts`,'(\\d{4})-(\\d{2})-(\\d{2})T(\\d{2}):(\\d{2}):(\\d{2}).(\\d{3})\\+(\\d{2}):(\\d{2})','$1-$2-$3 $4:$5:$6.$7') AS TIMESTAMP ),'yyyyMMdd') as BIGINT ) `par_key` FROM test_ts_conv;{noformat} Error: {code:java} Caused by: org.apache.hadoop.hive.ql.exec.UDFArgumentException: Casting NUMERIC types to TIMESTAMP is prohibited (hive.strict.timestamp.conversion) at org.apache.hadoop.hive.ql.udf.generic.GenericUDFTimestamp.initialize(GenericUDFTimestamp.java:91) at org.apache.hadoop.hive.ql.udf.generic.GenericUDF.initializeAndFoldConstants(GenericUDF.java:149) at org.apache.hadoop.hive.ql.exec.ExprNodeGenericFuncEvaluator.initialize(ExprNodeGenericFuncEvaluator.java:184) at org.apache.hadoop.hive.ql.exec.Operator.initEvaluators(Operator.java:1073) at org.apache.hadoop.hive.ql.exec.Operator.initEvaluatorsAndReturnStruct(Operator.java:1099) at org.apache.hadoop.hive.ql.exec.SelectOperator.initializeOp(SelectOperator.java:74) at org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:360) at org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:549) at org.apache.hadoop.hive.ql.exec.Operator.initializeChildren(Operator.java:503) at org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:369) at org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:549) at org.apache.hadoop.hive.ql.exec.Operator.initializeChildren(Operator.java:503) at org.apache.hadoop.hive.ql.exec.Operator.initialize(Operator.java:369) at org.apache.hadoop.hive.ql.exec.MapOperator.initializeMapOperator(MapOperator.java:508) at org.apache.hadoop.hive.ql.exec.tez.MapRecordProcessor.init(MapRecordProcessor.java:314) ... 17 more {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)