Queries with typed literals fail to parse

[pydolan]

In at least MySQL (e.g., with dates), literals can be assigned a type, such as in this query:

SELECT * FROM t1 WHERE col1 > DATE '2011-01-01' - INTERVAL 5 DAYS

This results in a ParseException ("Expecting one of (and, gt, is, union all...").

I might have time to help with a PR. If you have any thoughts on how best to implement this, let me know.

[klahnakoski]

You found a big limitation: The moz-sql-parser only parses string and number literals.

I would like

DATE '2011-01-01'

to parse as

{"date": "2011-01-01"}  

the parser should make no attempt to parse the string; it will be left to whatever post-parser deals with the JSON.

I have not given much thought to the two-parameter INTERVAL:

INTERVAL 5 DAYS

the parser might emit any of the following (I put them in best-first order, but I am open to suggestions)

{"interval": 5, "unit":"day"}           # interval(5, unit='day')
{"mul":[5, {"interval":"day"}]}         # 5 * interval('day')
{"interval":"day", "factor": 5}         # interval('day', factor=5)
{"interval": [5, "days"]}               # interval(5, 'days')
{"interval": {"value":5, "unit":"day"}} # interval(value=5, unit='day')
{"mul":[5, {"date":"day"}]}             # 5 * data('day')
{"date": "5day"}                        # date('5day')     
{"interval": "5days"}                   # interval('5days')

When deciding on a parsed output, remember the JSON object represents a function call; I added the "natural python interpretation" as a comment to each line.

If you decide to go with an interval function, then I will add it to the long list of operators https://github.com/mozilla/ActiveData/blob/dev/docs/jx_expressions.md

[klahnakoski]

As a side note, to explain how we survived without this: Our query interpreter has a date function that can parse the strings. We craft pathological SQL function calls that can make it through the parser and then to the query interpreter:

WHERE col1 > DATE("2011-01-01")

parses to

{"gt": {"col1": {"date": "2011-01-01"}}}

which works. Same with interval calculations,

WHERE col1 > DATE("2011-01-01") - DATE("5day")

Notice the use of double quotes; which are parsed as a column name. This is confusing to all users: People new the SQL must learn single and double quotes mean two different things, and experts are rightly disturbed to pretend column names are strings.

Solving this issue would be appreciated.

[klahnakoski]

@pydolan Do you have feedback on this issue? I am currently leaning toward

INTERVAL 5 DAYS

being parsed to

{"interval": "5days"}

with the understanding there is a time_interval (duration) datatype.

[pydolan]

@klahnakoski – I think that would work for me, thanks

[klahnakoski]

I have decided on {"interval": [5, "days"]}, if only to keep the parameters separate.

[klahnakoski]

1bc6d77