Skip to content

Interval type does not accept non-literal value #21

Open

Description

@agentm

The interval data type, as implied by ilLiteral, does not support scalar expressions. Is this a limitation of SQL2011?

This basic expression fails to parse:

SELECT INTERVAL t.numeric_field HOUR FROM t;

Activity

  1. JakeWheat commented on Aug 5, 2019

    @JakeWheat
    Owner

    It looks like it isn't allowed in ANSI SQL, here is the relevant grammar from the standard:

    <interval literal> ::=
      INTERVAL [ <sign>  ] <interval string>  <interval qualifier> 
    
    <interval string> ::=
      <quote>  <unquoted interval string>  <quote> 
    

    Did you see a SQL system supporting this syntax with expressions?

  2. agentm commented on Aug 5, 2019

    @agentm
    Author

    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:

    select interval ('1 ' || 'hour');
    

    I worked around this by allowing the IntervalLit type to take a ScalarExpr (instead of a String) and parse it with idenExpr.

    We'll likely have other changes for BigQuery support in the pipeline.

  3. JakeWheat commented on Aug 5, 2019

    @JakeWheat
    Owner

    There is a straightforward alternative which I think is standard, and you can use any expression in it:

    select cast('1 hour' as interval);
    
  4. JakeWheat commented on Aug 6, 2019

    @JakeWheat
    Owner

    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.

  5. lorenzo commented on Sep 16, 2019

    @lorenzo

    In mysql this is valid too: select * where date >= (now() - interval 3 DAY) (there are no quotes around the number.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions