[HIVE-24446] Materialized View plan alters explicit cast type in query

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.0.0
• Fix Version/s: 4.0.0-alpha-1
• Component/s: Materialized views, Types
• Labels: pull-request-available
• Assignee: Krisztian Kasa Assign to me
• Reporter: Krisztian Kasa
• Created: 30/Nov/20 14:28
• Updated: 17/Nov/22 08:47
• Resolved: 08/Dec/20 05:47

Description

create materialized view mv_tv_view_data_av2 stored as orc TBLPROPERTIES ('transactional'='true') as
select
  total_views `total_views`,
  sum(cast(1.5 as decimal(9,4))) over (order by total_views) as quartile,
  program
from tv_view_data;

LogicalProject(quartile=[CAST($0):DECIMAL(12, 1)], total=[$1])
  HiveTableScan(table=[[arc_view, mv_tv_view_data_av1]], table:alias=[mv_tv_view_data_av1])

Some constant decimal values are not padded in the result set.

select
POSTHOOK: query: select
  t.quartile,
  t.quartile,
  max(t.total_views) total
  max(t.total_views) total
from wealth t2,
from wealth t2,
(select
(select
  total_views `total_views`,
  total_views `total_views`,
  sum(cast(1.5 as decimal(9,4))) over (order by total_views) as quartile,
  sum(cast(1.5 as decimal(9,4))) over (order by total_views) as quartile,
  program
  program
from tv_view_data) t
from tv_view_data) t
where t.program=t2.watches
where t.program=t2.watches
group by quartile
group by quartile
order by quartile

1.5	1300000
4.5	15000000
6.0	20000000

Issue Links

• is related to: HIVE-24389 Trailing zeros of constant decimal numbers are removed
• links to: GitHub Pull Request #1717