[CALCITE-4089] In Babel, allow 'CAST(integer AS DATE)' even though it is illegal in Calcite SQL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.24.0
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 24/Jun/20 21:23
• Updated: 25/Jul/20 14:59
• Resolved: 26/Jun/20 17:15

Description

In Babel, allow 'CAST(integer AS DATE)', even though it is illegal in Calcite SQL (and standard SQL).

Map it to valid standard SQL. BigQuery CAST(n AS DATE) is equivalent to Calcite DATE '1970-01-01' + n * INTERVAL '1' DAY.

Also, add functions DATE_FROM_UNIX_DATE(INTEGER), UNIX_SECONDS(TIMESTAMP), UNIX_MILLIS(TIMESTAMP), UNIX_MICROS(TIMESTAMP), TIMESTAMP_SECONDS(BIGINT), TIMESTAMP_MILLIS(BIGINT), TIMESTAMP_MICROS(BIGINT) in the BigQuery library. They are well-named and well-specified, so they might be useful to people working in dialects besides BigQuery. DATE_FROM_UNIX_DATE(0) should return DATE '1970-01-01'.

Issue Links

• is blocked by: CALCITE-3224 New RexNode-to-Expression CodeGen Implementation
• is related to: CALCITE-4134 Interval expressions