kokila-19 commented on code in PR #6575:
URL: https://github.com/apache/hive/pull/6575#discussion_r3600427813
##########
ql/src/test/queries/clientpositive/vector_decimal64_col_multiply_case_subquery_join.q:
##########
@@ -0,0 +1,111 @@
+SET hive.support.concurrency=TRUE;
+SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
+set hive.cbo.enable=true;
+
+-- HIVE-28329: subquery + LEFT JOIN + CAST(decimal) with col * CASE returns
wrong result (0.00000000)
+
+-- Test tables
+create EXTERNAL table test1(
+col1 varchar(28),
+col2 varchar(28),
+col3 decimal(26,6),
+col4 varchar(28)
+);
+
+insert into test1 values('12345678','abc','1.00','DEF');
+
+create EXTERNAL table test2(
+col1 varchar(28),
+col2 varchar(28),
+col3 decimal(26,6),
+col4 varchar(28),
+col5 varchar(28)
+);
+
+insert into test2 values('12345678','abc','1.00','DEF','22222222');
+
+explain vectorization detail
+SELECT
+ int_cost
+FROM
+ (
+ SELECT
+ a.col4,
+ CAST(
+ CASE when a.col1 = '12345678' then a.col3 * case when a.col2 = '1'
then 1.77 else 0.72 end / 100 / 365 * 10 else a.col3 * 10 / 365 / 100 END AS
DECIMAL(26, 9)
+ ) AS int_cost
+ FROM
+ test1 a
+ ) aa
+ LEFT JOIN (
+ SELECT
+ col4
+ FROM
+ test2
+ WHERE
+ col5 = '22222222'
+ ) bb ON trim(aa.col4) = trim(bb.col4);
+
+SELECT
+ int_cost
+FROM
+ (
+ SELECT
+ a.col4,
+ CAST(
+ CASE when a.col1 = '12345678' then a.col3 * case when a.col2 = '1'
then 1.77 else 0.72 end / 100 / 365 * 10 else a.col3 * 10 / 365 / 100 END AS
DECIMAL(26, 9)
+ ) AS int_cost
+ FROM
+ test1 a
+ ) aa
+ LEFT JOIN (
+ SELECT
+ col4
+ FROM
+ test2
+ WHERE
+ col5 = '22222222'
+ ) bb ON trim(aa.col4) = trim(bb.col4);
+
+
+explain vectorization detail
+SELECT
+ int_cost
+FROM
+ (
+ SELECT
+ a.col4,
+ a.col3 * case when a.col2 = 'bc' then 1.77 else 0.72 end
+ AS int_cost
+ FROM
+ test1 a
+ ) aa
+ LEFT JOIN (
+ SELECT
+ col4
+ FROM
+ test2
+ ) bb ON trim(aa.col4) = trim(bb.col4);
Review Comment:
Used inner join and optimized the q file
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
