[jira] [Created] (CALCITE-2056) Limit/Offset in generated Sql does not work for many databases

["Luis Fernando Kauer (JIRA)" <ji...@apache.org>]

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)