HIVE-14077 : add implicit decimal arithmetic q test, fix issues if found (Sergey Shelukhin, reviewed by Ashutosh Chauhan)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/ae27806e Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/ae27806e Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/ae27806e Branch: refs/heads/master Commit: ae27806e48411a3acb87a481694577c4bd5fe9fa Parents: 0a10f95 Author: Sergey Shelukhin <ser...@apache.org> Authored: Tue Mar 28 13:59:17 2017 -0700 Committer: Sergey Shelukhin <ser...@apache.org> Committed: Tue Mar 28 13:59:33 2017 -0700 ---------------------------------------------------------------------- .../clientpositive/fp_literal_arithmetic.q | 57 ++++ .../clientpositive/fp_literal_arithmetic.q.out | 338 +++++++++++++++++++ 2 files changed, 395 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/ae27806e/ql/src/test/queries/clientpositive/fp_literal_arithmetic.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/fp_literal_arithmetic.q b/ql/src/test/queries/clientpositive/fp_literal_arithmetic.q new file mode 100644 index 0000000..b4f36ef --- /dev/null +++ b/ql/src/test/queries/clientpositive/fp_literal_arithmetic.q @@ -0,0 +1,57 @@ +set hive.mapred.mode=nonstrict; +set hive.explain.user=false; + + +-- Clearly decimal +explain +select sum(l_extendedprice) from lineitem q0 where l_discount +between cast('0.05' as decimal(3,2)) and cast('0.07' as decimal(3,2)); +select sum(l_extendedprice) from lineitem q0 where l_discount +between cast('0.05' as decimal(3,2)) and cast('0.07' as decimal(3,2)); + +-- Unspecified - should be decimal +explain +select sum(l_extendedprice) from lineitem q1 where l_discount +between 0.06 - 0.01 and 0.06 + 0.01; +select sum(l_extendedprice) from lineitem q1 where l_discount +between 0.06 - 0.01 and 0.06 + 0.01; +select sum(l_extendedprice) from lineitem q2 where l_discount +between 0.05 and 0.07; + +-- Just for kicks, to see that it works +select sum(l_extendedprice) from lineitem q3 where l_discount +between (cast('0.06' as decimal(3,2)) - cast('0.01' as decimal(3,2))) + and (cast('0.06' as decimal(3,2)) + cast('0.01' as decimal(3,2))); + +-- This will go to double and produce a different result +select sum(l_extendedprice) from lineitem q4 where l_discount +between (cast('0.06' as decimal(3,2)) - cast('0.01' as double)) + and (cast('0.06' as decimal(3,2)) + cast('0.01' as double)); + + + + +set hive.cbo.enable=false; +set hive.optimize.constant.propagation=false; + +explain +select sum(l_extendedprice) from lineitem q10 where l_discount +between cast('0.05' as decimal(3,2)) and cast('0.07' as decimal(3,2)); +select sum(l_extendedprice) from lineitem q10 where l_discount +between cast('0.05' as decimal(3,2)) and cast('0.07' as decimal(3,2)); + +explain +select sum(l_extendedprice) from lineitem q11 where l_discount +between 0.06 - 0.01 and 0.06 + 0.01; +select sum(l_extendedprice) from lineitem q11 where l_discount +between 0.06 - 0.01 and 0.06 + 0.01; +select sum(l_extendedprice) from lineitem q12 where l_discount +between 0.05 and 0.07; + +select sum(l_extendedprice) from lineitem q13 where l_discount +between (cast('0.06' as decimal(3,2)) - cast('0.01' as decimal(3,2))) + and (cast('0.06' as decimal(3,2)) + cast('0.01' as decimal(3,2))); + +select sum(l_extendedprice) from lineitem q14 where l_discount +between (cast('0.06' as decimal(3,2)) - cast('0.01' as double)) + and (cast('0.06' as decimal(3,2)) + cast('0.01' as double)); http://git-wip-us.apache.org/repos/asf/hive/blob/ae27806e/ql/src/test/results/clientpositive/fp_literal_arithmetic.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/fp_literal_arithmetic.q.out b/ql/src/test/results/clientpositive/fp_literal_arithmetic.q.out new file mode 100644 index 0000000..19b078a --- /dev/null +++ b/ql/src/test/results/clientpositive/fp_literal_arithmetic.q.out @@ -0,0 +1,338 @@ +PREHOOK: query: explain +select sum(l_extendedprice) from lineitem q0 where l_discount +between cast('0.05' as decimal(3,2)) and cast('0.07' as decimal(3,2)) +PREHOOK: type: QUERY +POSTHOOK: query: explain +select sum(l_extendedprice) from lineitem q0 where l_discount +between cast('0.05' as decimal(3,2)) and cast('0.07' as decimal(3,2)) +POSTHOOK: type: QUERY +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Map Operator Tree: + TableScan + alias: q0 + Statistics: Num rows: 100 Data size: 11999 Basic stats: COMPLETE Column stats: NONE + Filter Operator + predicate: l_discount BETWEEN 0.05 AND 0.07 (type: boolean) + Statistics: Num rows: 11 Data size: 1319 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: l_extendedprice (type: double) + outputColumnNames: l_extendedprice + Statistics: Num rows: 11 Data size: 1319 Basic stats: COMPLETE Column stats: NONE + Group By Operator + aggregations: sum(l_extendedprice) + mode: hash + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + Reduce Output Operator + sort order: + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + value expressions: _col0 (type: double) + Reduce Operator Tree: + Group By Operator + aggregations: sum(VALUE._col0) + mode: mergepartial + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + File Output Operator + compressed: false + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat + serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe + + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + ListSink + +PREHOOK: query: select sum(l_extendedprice) from lineitem q0 where l_discount +between cast('0.05' as decimal(3,2)) and cast('0.07' as decimal(3,2)) +PREHOOK: type: QUERY +PREHOOK: Input: default@lineitem +#### A masked pattern was here #### +POSTHOOK: query: select sum(l_extendedprice) from lineitem q0 where l_discount +between cast('0.05' as decimal(3,2)) and cast('0.07' as decimal(3,2)) +POSTHOOK: type: QUERY +POSTHOOK: Input: default@lineitem +#### A masked pattern was here #### +1201714.81 +PREHOOK: query: explain +select sum(l_extendedprice) from lineitem q1 where l_discount +between 0.06 - 0.01 and 0.06 + 0.01 +PREHOOK: type: QUERY +POSTHOOK: query: explain +select sum(l_extendedprice) from lineitem q1 where l_discount +between 0.06 - 0.01 and 0.06 + 0.01 +POSTHOOK: type: QUERY +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Map Operator Tree: + TableScan + alias: q1 + Statistics: Num rows: 100 Data size: 11999 Basic stats: COMPLETE Column stats: NONE + Filter Operator + predicate: l_discount BETWEEN 0.05 AND 0.07 (type: boolean) + Statistics: Num rows: 11 Data size: 1319 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: l_extendedprice (type: double) + outputColumnNames: l_extendedprice + Statistics: Num rows: 11 Data size: 1319 Basic stats: COMPLETE Column stats: NONE + Group By Operator + aggregations: sum(l_extendedprice) + mode: hash + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + Reduce Output Operator + sort order: + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + value expressions: _col0 (type: double) + Reduce Operator Tree: + Group By Operator + aggregations: sum(VALUE._col0) + mode: mergepartial + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + File Output Operator + compressed: false + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat + serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe + + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + ListSink + +PREHOOK: query: select sum(l_extendedprice) from lineitem q1 where l_discount +between 0.06 - 0.01 and 0.06 + 0.01 +PREHOOK: type: QUERY +PREHOOK: Input: default@lineitem +#### A masked pattern was here #### +POSTHOOK: query: select sum(l_extendedprice) from lineitem q1 where l_discount +between 0.06 - 0.01 and 0.06 + 0.01 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@lineitem +#### A masked pattern was here #### +1201714.81 +PREHOOK: query: select sum(l_extendedprice) from lineitem q2 where l_discount +between 0.05 and 0.07 +PREHOOK: type: QUERY +PREHOOK: Input: default@lineitem +#### A masked pattern was here #### +POSTHOOK: query: select sum(l_extendedprice) from lineitem q2 where l_discount +between 0.05 and 0.07 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@lineitem +#### A masked pattern was here #### +1201714.81 +PREHOOK: query: select sum(l_extendedprice) from lineitem q3 where l_discount +between (cast('0.06' as decimal(3,2)) - cast('0.01' as decimal(3,2))) + and (cast('0.06' as decimal(3,2)) + cast('0.01' as decimal(3,2))) +PREHOOK: type: QUERY +PREHOOK: Input: default@lineitem +#### A masked pattern was here #### +POSTHOOK: query: select sum(l_extendedprice) from lineitem q3 where l_discount +between (cast('0.06' as decimal(3,2)) - cast('0.01' as decimal(3,2))) + and (cast('0.06' as decimal(3,2)) + cast('0.01' as decimal(3,2))) +POSTHOOK: type: QUERY +POSTHOOK: Input: default@lineitem +#### A masked pattern was here #### +1201714.81 +PREHOOK: query: select sum(l_extendedprice) from lineitem q4 where l_discount +between (cast('0.06' as decimal(3,2)) - cast('0.01' as double)) + and (cast('0.06' as decimal(3,2)) + cast('0.01' as double)) +PREHOOK: type: QUERY +PREHOOK: Input: default@lineitem +#### A masked pattern was here #### +POSTHOOK: query: select sum(l_extendedprice) from lineitem q4 where l_discount +between (cast('0.06' as decimal(3,2)) - cast('0.01' as double)) + and (cast('0.06' as decimal(3,2)) + cast('0.01' as double)) +POSTHOOK: type: QUERY +POSTHOOK: Input: default@lineitem +#### A masked pattern was here #### +960361.91 +PREHOOK: query: explain +select sum(l_extendedprice) from lineitem q10 where l_discount +between cast('0.05' as decimal(3,2)) and cast('0.07' as decimal(3,2)) +PREHOOK: type: QUERY +POSTHOOK: query: explain +select sum(l_extendedprice) from lineitem q10 where l_discount +between cast('0.05' as decimal(3,2)) and cast('0.07' as decimal(3,2)) +POSTHOOK: type: QUERY +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Map Operator Tree: + TableScan + alias: q10 + Statistics: Num rows: 100 Data size: 11999 Basic stats: COMPLETE Column stats: NONE + Filter Operator + predicate: l_discount BETWEEN CAST( '0.05' AS decimal(3,2)) AND CAST( '0.07' AS decimal(3,2)) (type: boolean) + Statistics: Num rows: 11 Data size: 1319 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: l_extendedprice (type: double) + outputColumnNames: l_extendedprice + Statistics: Num rows: 11 Data size: 1319 Basic stats: COMPLETE Column stats: NONE + Group By Operator + aggregations: sum(l_extendedprice) + mode: hash + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + Reduce Output Operator + sort order: + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + value expressions: _col0 (type: double) + Reduce Operator Tree: + Group By Operator + aggregations: sum(VALUE._col0) + mode: mergepartial + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + File Output Operator + compressed: false + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat + serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe + + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + ListSink + +PREHOOK: query: select sum(l_extendedprice) from lineitem q10 where l_discount +between cast('0.05' as decimal(3,2)) and cast('0.07' as decimal(3,2)) +PREHOOK: type: QUERY +PREHOOK: Input: default@lineitem +#### A masked pattern was here #### +POSTHOOK: query: select sum(l_extendedprice) from lineitem q10 where l_discount +between cast('0.05' as decimal(3,2)) and cast('0.07' as decimal(3,2)) +POSTHOOK: type: QUERY +POSTHOOK: Input: default@lineitem +#### A masked pattern was here #### +1201714.81 +PREHOOK: query: explain +select sum(l_extendedprice) from lineitem q11 where l_discount +between 0.06 - 0.01 and 0.06 + 0.01 +PREHOOK: type: QUERY +POSTHOOK: query: explain +select sum(l_extendedprice) from lineitem q11 where l_discount +between 0.06 - 0.01 and 0.06 + 0.01 +POSTHOOK: type: QUERY +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Map Operator Tree: + TableScan + alias: q11 + Statistics: Num rows: 100 Data size: 11999 Basic stats: COMPLETE Column stats: NONE + Filter Operator + predicate: l_discount BETWEEN (0.06 - 0.01) AND (0.06 + 0.01) (type: boolean) + Statistics: Num rows: 11 Data size: 1319 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: l_extendedprice (type: double) + outputColumnNames: l_extendedprice + Statistics: Num rows: 11 Data size: 1319 Basic stats: COMPLETE Column stats: NONE + Group By Operator + aggregations: sum(l_extendedprice) + mode: hash + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + Reduce Output Operator + sort order: + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + value expressions: _col0 (type: double) + Reduce Operator Tree: + Group By Operator + aggregations: sum(VALUE._col0) + mode: mergepartial + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + File Output Operator + compressed: false + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat + serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe + + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + ListSink + +PREHOOK: query: select sum(l_extendedprice) from lineitem q11 where l_discount +between 0.06 - 0.01 and 0.06 + 0.01 +PREHOOK: type: QUERY +PREHOOK: Input: default@lineitem +#### A masked pattern was here #### +POSTHOOK: query: select sum(l_extendedprice) from lineitem q11 where l_discount +between 0.06 - 0.01 and 0.06 + 0.01 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@lineitem +#### A masked pattern was here #### +1201714.81 +PREHOOK: query: select sum(l_extendedprice) from lineitem q12 where l_discount +between 0.05 and 0.07 +PREHOOK: type: QUERY +PREHOOK: Input: default@lineitem +#### A masked pattern was here #### +POSTHOOK: query: select sum(l_extendedprice) from lineitem q12 where l_discount +between 0.05 and 0.07 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@lineitem +#### A masked pattern was here #### +1201714.81 +PREHOOK: query: select sum(l_extendedprice) from lineitem q13 where l_discount +between (cast('0.06' as decimal(3,2)) - cast('0.01' as decimal(3,2))) + and (cast('0.06' as decimal(3,2)) + cast('0.01' as decimal(3,2))) +PREHOOK: type: QUERY +PREHOOK: Input: default@lineitem +#### A masked pattern was here #### +POSTHOOK: query: select sum(l_extendedprice) from lineitem q13 where l_discount +between (cast('0.06' as decimal(3,2)) - cast('0.01' as decimal(3,2))) + and (cast('0.06' as decimal(3,2)) + cast('0.01' as decimal(3,2))) +POSTHOOK: type: QUERY +POSTHOOK: Input: default@lineitem +#### A masked pattern was here #### +1201714.81 +PREHOOK: query: select sum(l_extendedprice) from lineitem q14 where l_discount +between (cast('0.06' as decimal(3,2)) - cast('0.01' as double)) + and (cast('0.06' as decimal(3,2)) + cast('0.01' as double)) +PREHOOK: type: QUERY +PREHOOK: Input: default@lineitem +#### A masked pattern was here #### +POSTHOOK: query: select sum(l_extendedprice) from lineitem q14 where l_discount +between (cast('0.06' as decimal(3,2)) - cast('0.01' as double)) + and (cast('0.06' as decimal(3,2)) + cast('0.01' as double)) +POSTHOOK: type: QUERY +POSTHOOK: Input: default@lineitem +#### A masked pattern was here #### +960361.91
