Luis Fernando Kauer created CALCITE-2056:
--------------------------------------------
Summary: Limit/Offset in generated Sql does not work for many
databases
Key: CALCITE-2056
URL: https://issues.apache.org/jira/browse/CALCITE-2056
Project: Calcite
Issue Type: Bug
Reporter: Luis Fernando Kauer
Assignee: Julian Hyde
SqlDialect.supportsOffsetFetch() defines whether the dialect supports
OFFSET/FETCH clauses introduced by SQL:2008, and by default returns true.
SqlPrettyWriter.fetchOffset(SqlNode, SqlNode) uses that information to insert
"FETCH NEXT x ROWS" if it supportsOffsetFetch.
However, I noticed that many of the databases support it in different ways and
some do not support at all.
For example, Hsqldb, which is used in test cases does not accept FETCH NEXT x
ROWS, but it accepts LIMIT and FETCH FIRST x ROWS.
Some databases accept TOP x and others use something even different (like
PostgreSQL).
I suggest we make using LIMIT the default, because it is the most accepted and
allow each SqlDialect do define its own way of using fetch and offset.
If we check how Hibernate sets the dialects to deal with limit/offset for each
database, we see that most use limit syntax and some use special syntax, but
very few are configured to use SQL 2008 syntax.
This hasn't been a problem until now because sort/limit/offset was not being
pushed to the database. But now that I'm trying to fix it, the generated SQL
is not working in Hsqldb.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
