[CALCITE-3218] Syntax error while parsing DATEADD function (which is valid on Redshift)

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.21.0
• Labels: pull-request-available
• Assignee: Julian Hyde
• Reporter: Lindsey Meyer
• Created: 29/Jul/19 21:57
• Updated: 06/Jun/22 23:00
• Resolved: 04/Aug/19 04:17

Description

Syntax error while parsing the DATEADD function:

SELECT 
	DATE(CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', events.event_date )) AS "events.event_date",
	COALESCE(SUM(events.daily_user_count ), 0) AS "events.daily_active_users",
	COALESCE(SUM(events.monthly_user_count ), 0) AS "events.monthly_active_users"
FROM public.events_proto  AS events

WHERE 
	(((events.event_date ) >= ((CONVERT_TIMEZONE('America/Los_Angeles', 'UTC', DATEADD(day,-364, DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', GETDATE())) )))) AND (events.event_date ) < ((CONVERT_TIMEZONE('America/Los_Angeles', 'UTC', DATEADD(day,365, DATEADD(day,-364, DATE_TRUNC('day',CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', GETDATE())) ) ))))))
GROUP BY 1
HAVING 
	NOT (COALESCE(SUM(events.monthly_user_count ), 0) = 0)
ORDER BY 1 DESC
LIMIT 500

throws 

`Column 'year' not found in any table`
`DATEADD(year,1,...`

This query is valid on Redshift. Redshift's parser understands that the first argument (DAY) is intended to be a time unit, not an identifier.

Issue Links

• relates to: CALCITE-5135 Planner#parse can't parse DAY() function
• relates to: CALCITE-5180 Implement BigQuery functions for DATE, TIME, TIMESTAMP, DATETIME
• relates to: CALCITE-5143 Allow custom time unit abbreviations in FLOOR, CEIL, EXTRACT, DATE_PART, DATEADD, DATEDIFF and similar functions
• links to: GitHub Pull Request #1352