qgen query rejected by hsql's DATE handling
cdmh opened this issue · 2 comments
cdmh commented
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;
klauck commented
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