[KYLIN-5043] support floor() or date_trunc() function for Kylin4

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: v4.0.0-beta
• Component/s: Query Engine
• Assignee: Congling Xia
• Reporter: Congling Xia
• Created: 22/Jul/21 09:52
• Updated: 22/Jul/21 09:52

Description

Dimension tables for date is seldom used in these days. Time function that can round datetimes to coarse grained granularities is very useful when writing queries in SQL.

Kylin 4 use SparkSQL to execute queries, but query SQL is parsed using Calcite.

• in Calcite, a FLOOR function can round datetimes down
• in Spark, the similar function is DATE_TRUNC 

but they cannot be transformed properly for now (fixed in Calcite 1.20, CALCITE-3072):

select 
  floor(PART_DT to year), count(1) 
from KYLIN_SALES 
group by 1;