[
https://issues.apache.org/jira/browse/CALCITE-2056?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16960217#comment-16960217
]
Julian Hyde commented on CALCITE-2056:
--------------------------------------
[~lfkauer], Are you still working on this? It might be useful to run your tests
against the latest Calcite. We've done some work on LIMIT (e.g. CALCITE-1010,
CALCITE-3344), so I think most of the tests will pass, or could easily be fixed.
> 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
> Priority: Major
>
> 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, but most
> of them also accept LIMIT syntax.
> 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
(v8.3.4#803005)
