Between ... and Formatting Exception

[qiqingli]

Example:

select * from ids_order
where create_time between current_timestamp - interval'10 day' and current_timestamp;

Error Message:

["ERROR" - 9:28:13 AM] Syntax Error: Unexpected ";"
Was expecting to see: "!=", "%", "(", "*", "+", "-", ".", "/", "::", "<", "<=", "<>", "=", ">", ">=", "AND", "AT", "BETWEEN", "COLLATE", "DAY", "HOUR", "ILIKE", "IN", "IS", "ISNULL", "LIKE", "MINUTE", "MONTH", "NOT", "NOTNULL", "OPERATOR", "OR", "SECOND", "SIMILAR", "YEAR", "[", "^", [-,+,*,/,<,>,=,~,!,@,#,%,^,&,|,`,?], or whitespace
  |
4 | where create_time between current_timestamp - interval'10 day' and current_timestamp;
  |                                                                                     ^
Error: Syntax Error: Unexpected ";"

Plugin Verison: 0.17.3

[nene]

Thanks for reporting.

Turns out I relaxed the INTERVAL literal parsing too much by allowing any expression on the right hand of INTERVAL keyword. AS a result it tries to parse it like so:

INTERVAL ('10 day' AND current_timestamp)

Because in e.g. MySQL the following is valid:

SELECT NOW() + INTERVAL 9 - 3 DAY;

So I need to support some expressions, but not the logical expressions (AND, OR).

[nene]

As a temporary workaround you can use parenthesis to help it parse the SQL correctly:

select * from ids_order
where create_time between current_timestamp - (interval '10 day') and current_timestamp;

[nene]

Fixed in plugin version 0.17.4