tpch-kit icon indicating copy to clipboard operation
tpch-kit copied to clipboard

Fix broken LIMIT clause in query templates.

Open lmwnshn opened this issue 3 years ago • 1 comments

Because qgen is essentially a find-and-replace, it was outputting PostgreSQL SQL like

order by
    l_returnflag, l_linestatus;
limit -1;

order by
    revenue desc;
limit 20;

which has the following problems:

  1. negative limits don't make sense and are unsupported in most DBMSs, e.g., PostgreSQL mailing list thread asking if this was a joke, PostgreSQL commit rejecting negative limits in 2008.
  2. the limit clause is not attached to the select.

I have not tested this behavior with the other DBMSs in tpcd.h, although a number of those seem to be outdated anyway (e.g., SQLSERVER should use TOP instead of rowcount now).

lmwnshn avatar Jul 20 '22 14:07 lmwnshn

Would be nice to merge this to master @gregrahn.

qgen for PostgreSQL is quite useless otherwise, needing a bunch of manual work post generation.

LinuzJ avatar Jun 28 '24 16:06 LinuzJ