[jira] [Created] (HIVE-28329) Query with sub-query + Left Join + cast decimal producing incorrect results with CBO

[Taraka Rama Rao Lethavadla (Jira)]

Taraka Rama Rao Lethavadla created HIVE-28329:
-------------------------------------------------

             Summary: Query with sub-query + Left Join + cast decimal producing 
incorrect results with CBO
                 Key: HIVE-28329
                 URL: https://issues.apache.org/jira/browse/HIVE-28329
             Project: Hive
          Issue Type: Bug
          Components: CBO, Hive
            Reporter: Taraka Rama Rao Lethavadla
         Attachments: test_dec.q, test_decimal.q

The below query gives incorrect results
{noformat}
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);{noformat}
returning 0.000000000 instead of 0.000197260

Attaching the complete test case with various cases like without 
join/sub-query/column names

The same query gives correct output without cbo



--
This message was sent by Atlassian Jira
(v8.20.10#820010)