qgen query rejected by hsql's DATE handling

[cdmh]

The following query generated by TPC-H qgen -d 7 yields syntax error, unexpected DATE from the HSQL parser. The error is in Line 30 and l_shipdate between date '1995-01-01' and date '1996-12-31'

I think this is valid SQL-92, and [1] seems to suggest it is, too.

select
        supp_nation,
        cust_nation,
        l_year,
        sum(volume) as revenue
from
        (
                select
                        n1.n_name as supp_nation,
                        n2.n_name as cust_nation,
                        extract(year from l_shipdate) as l_year,
                        l_extendedprice * (1 - l_discount) as volume
                from
                        supplier,
                        lineitem,
                        orders,
                        customer,
                        nation n1,
                        nation n2
                where
                        s_suppkey = l_suppkey
                        and o_orderkey = l_orderkey
                        and c_custkey = o_custkey
                        and s_nationkey = n1.n_nationkey
                        and c_nationkey = n2.n_nationkey
                        and (
                                (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
                                or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
                        )
                        and l_shipdate between date '1995-01-01' and date '1996-12-31'
        ) as shipping
group by
        supp_nation,
        cust_nation,
        l_year
order by
        supp_nation,
        cust_nation,
        l_year;

1. https://www.ibphoenix.com/resources/documents/design/doc_169

[klauck]

Hi Craig,
Thanks for making us aware of this issue.
We try to fix this on friday: From my current understanding (after looking into the code), the parser only recognizes the DATE keyword in create table statements, but not DATE literals in queries.
Best regards
Stefan

[klauck]

Hi @cdmh,
I created a pull request #174, which should fix the issue. (I also tested your query and it works now.)
A colleague will review (and then merge) the code in the next days.
Best regards