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

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-2056?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16254548#comment-16254548 ] 

Julian Hyde commented on CALCITE-2056:
--------------------------------------

I disagree. It's simplest to assume that a database is standards-compliant, because most are, or will become so over time. For instance, the latest HSQLDB supports both syntax (LIMIT, OFFSET, FETCH): http://hsqldb.org/doc/guide/dataaccess-chapt.html#dac_slicing 

We can add exceptions (i.e. override the method) for those that aren't.

Maybe we should list which databases are supported by Calcite's JDBC adapter. We can only add something to the list if we have run the test suite against it.

> 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, 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
(v6.4.14#64029)