You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Interval type does not accept non-literal value #21
Here's a little follow up on this syntax. I think Postgres's interval syntax is (mostly) standard. This interval syntax in ANSI can only be used to create literals, it is not a general interval constructor.
This is a postgres extension as far as I can tell:
select interval '1 hour';
This is the ANSI literal syntax, which postgres also supports.
select interval '1' hour;
Both of these are literals, and you can only put a literal string to them, not a string valued expression. This is ANSI standard behaviour for the second example. '1 ' || 'hour' is an expression, and would only work anywhere in the syntax where an expression is allowed, and never where only a string literal is allowed.
There doesn't appear to be any constructor for intervals in Postgres or ANSI, you have to do some variation of converting to a string then parsing it. As you mentioned, BigQuery has filled this gap by extending the literal syntax to be a general constructor:
select interval an_expression hour;
It should be trivial to add support for this syntax in simple-sql-parser if desired.
Activity
JakeWheat commentedon Aug 5, 2019
It looks like it isn't allowed in ANSI SQL, here is the relevant grammar from the standard:
Did you see a SQL system supporting this syntax with expressions?
agentm commentedon Aug 5, 2019
We are using BigQuery with this convenience.
PostgreSQL interval syntax is definitely non-standard as it accepts a string argument but I can't even make it accept concatenated strings:
I worked around this by allowing the
IntervalLittype to take aScalarExpr(instead of aString) and parse it withidenExpr.We'll likely have other changes for BigQuery support in the pipeline.
JakeWheat commentedon Aug 5, 2019
There is a straightforward alternative which I think is standard, and you can use any expression in it:
JakeWheat commentedon Aug 6, 2019
Here's a little follow up on this syntax. I think Postgres's interval syntax is (mostly) standard. This interval syntax in ANSI can only be used to create literals, it is not a general interval constructor.
This is a postgres extension as far as I can tell:
This is the ANSI literal syntax, which postgres also supports.
Both of these are literals, and you can only put a literal string to them, not a string valued expression. This is ANSI standard behaviour for the second example.
'1 ' || 'hour'is an expression, and would only work anywhere in the syntax where an expression is allowed, and never where only a string literal is allowed.There doesn't appear to be any constructor for intervals in Postgres or ANSI, you have to do some variation of converting to a string then parsing it. As you mentioned, BigQuery has filled this gap by extending the literal syntax to be a general constructor:
It should be trivial to add support for this syntax in simple-sql-parser if desired.
lorenzo commentedon Sep 16, 2019
In mysql this is valid too:
select * where date >= (now() - interval 3 DAY)(there are no quotes around the number.