Harsh Maheshwari created HIVE-22759:
---------------------------------------
Summary: Default decimal round off producing incorrect results in
"Vectorized" mode
Key: HIVE-22759
URL: https://issues.apache.org/jira/browse/HIVE-22759
Project: Hive
Issue Type: Bug
Components: Vectorization
Affects Versions: 3.1.1
Environment: Hive on top of hadoop, operating on top of parquet files.
Issue reproducible with both MR and Tez execution.
Reporter: Harsh Maheshwari
Premise:
External table on top of a parquet file. The table contains decimal with a
fixed scale, and there could be entries in parquet with higher scale. Hive, by
default rounds off the values to the scale defined in table schema and uses a
view of translated values while applying filters.
Steps to reproduce -
a. Create a new HDFS path and upload attached parquet files within the folder.
b. Create an external table managing these parquet files
{noformat}
create table lineitem_15_3(l_orderkey int, l_suppkey int, l_linenumber int,
l_quantity decimal(15,3), l_extendedprice decimal(15,3), l_discount
decimal(15,3), l_tax decimal(15,3), l_returnflag string, l_linestatus string,
l_shipdate date, l_commitdate date, l_receiptdate date, l_shipinstruct string,
l_shipmode string, l_comment string) stored as parquet location
'hdfs://<location>';
{noformat}
c. Execute following query in vectorized and non-vectorized mode -
{noformat}
select * from lineitem_15_3 where l_extendedprice <= 918.031 and l_discount >=
0.0 and l_tax>= 0.001 and l_orderkey = 107695104;
{noformat}
----
Non vectorized mode produces correct result (1 row) -
{noformat}
hive> set hive.vectorized.execution.enabled = false;
hive> select * from lineitem_15_3 where l_extendedprice <= 918.031 and
l_discount >= 0.0 and l_tax >= 0.001 and l_orderkey = 107695104; Query ID =
hive_20200122101124_3734cfab-6cbb-4562-97ee-71e15775babe Total jobs = 1
Launching Job 1 out of 1 Number of reduce tasks is set to 0 since there's no
reduce operator Starting Job = job_1579669891475_0048, Tracking URL = <...>
Kill Command = /opt/hadoop/bin/mapred job -kill job_1579669891475_0048 Hadoop
job information for Stage-1: number of mappers: 2; number of reducers: 0
2020-01-22 10:11:30,661 Stage-1 map = 0%, reduce = 0% 2020-01-22 10:11:39,833
Stage-1 map = 50%, reduce = 0%, Cumulative CPU 7.45 sec 2020-01-22
10:11:46,961 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 25.22 sec
MapReduce Total cumulative CPU time: 25 seconds 220 msec Ended Job =
job_1579669891475_0048 MapReduce Jobs Launched: Stage-Stage-1: Map: 2
Cumulative CPU: 25.22 sec HDFS Read: 318375885 HDFS Write: 310 SUCCESS Total
MapReduce CPU Time Spent: 25 seconds 220 msec
OK
107695104 263020 6 1.000 918.026 0.006 0.001 N O 1995-06-21 1995-08-08
1995-07-14 TAKE BACK RETURN FOB thely express reques
Time taken: 23.24 seconds, Fetched: 1 row(s)
{noformat}
----
Vectorized mode produces incorrect result (no rows) -
{noformat}
hive> set hive.vectorized.execution.enabled = true;
hive> select * from lineitem_15_3 where l_extendedprice <= 918.031 and
l_discount >= 0.0 and l_tax >= 0.001 and l_orderkey = 107695104;
Query ID = hive_20200122101058_3cda8d56-0885-4c31-aff4-6686a84f639e
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1579669891475_0047, Tracking URL = <...>
Kill Command = /opt/hadoop/bin/mapred job -kill job_1579669891475_0047
Hadoop job information for Stage-1: number of mappers: 2; number of reducers: 0
2020-01-22 10:11:04,474 Stage-1 map = 0%, reduce = 0%
2020-01-22 10:11:12,651 Stage-1 map = 50%, reduce = 0%, Cumulative CPU 5.29 sec
2020-01-22 10:11:16,735 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 20.6
sec
MapReduce Total cumulative CPU time: 20 seconds 600 msec
Ended Job = job_1579669891475_0047
MapReduce Jobs Launched:
Stage-Stage-1: Map: 2 Cumulative CPU: 20.6 sec HDFS Read: 318378365 HDFS
Write: 174 SUCCESS
Total MapReduce CPU Time Spent: 20 seconds 600 msec
OK
Time taken: 19.822 seconds{noformat}
Similar behaviour is observed for many other queries. The record in parquet
file has a value of 0.00075 for l_tax. Since, schema defines 3 digits of scale
in decimal points, it should match with 0.001 after rounding off and return the
row.
If we explicitly apply round() function to scale of 3 on l_tax column, the row
shows up correctly.
{noformat}
hive> select * from lineitem_15_3 where l_extendedprice <= 918.031 and
l_discount >= 0.0 and round(l_tax,3) >= 0.001 and l_orderkey = 107695104; Query
ID = hive_20200122103950_c5d579b8-770f-441d-bfaa-74c84ac3c515 Total jobs = 1
Launching Job 1 out of 1 Number of reduce tasks is set to 0 since there's no
reduce operator Starting Job = job_1579669891475_0049, Tracking URL = <...>
Kill Command = /opt/hadoop/bin/mapred job -kill job_1579669891475_0049 Hadoop
job information for Stage-1: number of mappers: 2; number of reducers: 0
2020-01-22 10:39:56,751 Stage-1 map = 0%, reduce = 0% 2020-01-22 10:40:03,894
Stage-1 map = 50%, reduce = 0%, Cumulative CPU 8.63 sec 2020-01-22
10:40:12,044 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 27.01 sec
MapReduce Total cumulative CPU time: 27 seconds 10 msec Ended Job =
job_1579669891475_0049 MapReduce Jobs Launched: Stage-Stage-1: Map: 2
Cumulative CPU: 27.01 sec HDFS Read: 318376023 HDFS Write: 310 SUCCESS Total
MapReduce CPU Time Spent: 27 seconds 10 msec
OK
107695104 263020 6 1.000 918.026 0.006 0.001 N O 1995-06-21 1995-08-08
1995-07-14 TAKE BACK RETURN FOB thely express reques
Time taken: 22.888 seconds, Fetched: 1 row(s)
{noformat}
----
This proves, there's an issue with default round up, in vectorisation mode.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
