[HIVE-27847] Prevent query Failures on Numeric <-> Timestamp

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.0.0-alpha-1, 4.0.0-alpha-2
• Fix Version/s: 4.1.0, 4.0.1
• Component/s: Hive
• Labels: hive-4.0.1-merged hive-4.0.1-must pull-request-available

Environment

master

4.0.0-alpha-1

• Release Note: This issue occured in Master and 4.0.0-alpha-1 and alpha-2
• Assignee: Shohei Okumiya
• Reporter: Basapuram Kumar
• Created: 03/Nov/23 15:35
• Updated: 24/Sep/24 02:15
• Resolved: 06/Jun/24 01:46

Description

In Master/4.0.0-alpha-1 branches, performing the Numeric to Timestamp conversion, its failing with the error as "org.apache.hadoop.hive.ql.exec.UDFArgumentException: Casting NUMERIC types to TIMESTAMP is prohibited (hive.strict.timestamp.conversion)" .

 

Repro steps.

1. Sample data

$ 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

1. table with above data [1]

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     |          |
+-----------+------------+----------+

1.  Create table with CTAS

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;

Error:

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 

Issue Links

• links to: GitHub Pull Request #4851
• links to: GitHub Pull Request #5278