[
https://issues.apache.org/jira/browse/CALCITE-2056?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Luis Fernando Kauer updated CALCITE-2056:
-----------------------------------------
Description:
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.
was:
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.
> 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)
