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

["Miguel Oliveira (JIRA)" <ji...@apache.org>]

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

Miguel Oliveira edited comment on CALCITE-2056 at 7/21/18 3:55 PM:
-------------------------------------------------------------------

Thanks to [~lfkauer] for identifying this issue. 

I think that we have two problems here and you have already advanced in an implementation to write the correct LIMIT/OFFSET syntax for each database.

The another problem is that the wrap select doesn't save the context of inner alias.

In this example (I already wrote here: https://issues.apache.org/jira/browse/CALCITE-1906?focusedCommentId=16551346&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-16551346).
{code:java}
SELECT emp.deptno `Emp Dept`, dept.dname `Dept Name`, dept.deptno `Dept Dept` 
FROM `Scott`.`emp` emp 
JOIN `Scott`.`dept` dept ON emp.deptno = dept.deptno 
WHERE dept.dname LIKE '%E%' 
ORDER BY 1 desc  
{code}
 The generated query:
{code:java}
SELECT "t2"."deptno0" AS "Emp Dept", "t2"."dname" "Dept Name", "t2"."deptno" AS "Dept Dept" 
FROM (SELECT * 
FROM (SELECT "deptno", "dname" 
FROM "public"."dept" 
WHERE "dname" LIKE '%E%') AS "t0" 
INNER JOIN (SELECT "deptno" 
FROM "public"."emp") AS "t1" ON "t0"."deptno" = "t1"."deptno" 
ORDER BY "t1"."deptno" DESC) AS "t2"
{code}
 Could be solved by changing the "SELECT *" generated by the Sort Clause to something like this:
{code:java}
SELECT "t2"."deptno0" AS "Emp Dept", "t2"."dname" "Dept Name", "t2"."deptno" AS "Dept Dept" 
FROM (SELECT "t0"."deptno" AS "deptno", "t0"."dname" as "dname", "t1"."deptno" as "deptno0" 
FROM (SELECT "deptno", "dname" 
FROM "public"."dept" 
WHERE "dname" LIKE '%E%') AS "t0" 
INNER JOIN (SELECT "deptno" 
FROM "public"."emp") AS "t1" ON "t0"."deptno" = "t1"."deptno" 
ORDER BY "t1"."deptno" DESC) AS "t2"
{code}
So, basically I didn't understand if the bug resides in the fact that the Sort clause is below the last Project or the fact that the fields alias are not preserved from "t0", "t1" to the "t2". 

I really appreciate if you can clarify me this doubt.

Thank you.


was (Author: migueltaoliveira):
Thanks to [~lfkauer] for identifying this issue. 

I think that we have two problems here and you have already advanced in an implementation to write the correct LIMIT/OFFSET syntax for each database.

The another problem is that the wrap select doesn't save the context of inner alias.

In this example (I already wrote here: https://issues.apache.org/jira/browse/CALCITE-1906?focusedCommentId=16551346&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-16551346).

 
{code:java}
SELECT emp.deptno `Emp Dept`, dept.dname `Dept Name`, dept.deptno `Dept Dept` 
FROM `Scott`.`emp` emp 
JOIN `Scott`.`dept` dept ON emp.deptno = dept.deptno 
WHERE dept.dname LIKE '%E%' 
ORDER BY 1 desc  
{code}
 

 The generated query:

 
{code:java}
SELECT "t2"."deptno0" AS "Emp Dept", "t2"."dname" "Dept Name", "t2"."deptno" AS "Dept Dept" 
FROM (SELECT * 
FROM (SELECT "deptno", "dname" 
FROM "public"."dept" 
WHERE "dname" LIKE '%E%') AS "t0" 
INNER JOIN (SELECT "deptno" 
FROM "public"."emp") AS "t1" ON "t0"."deptno" = "t1"."deptno" 
ORDER BY "t1"."deptno" DESC) AS "t2"
{code}
 

Could be solved by changing the "SELECT *" generated by the Sort Clause to something like this:
{code:java}
SELECT "t2"."deptno0" AS "Emp Dept", "t2"."dname" "Dept Name", "t2"."deptno" AS "Dept Dept" 
FROM (SELECT "t0"."deptno" AS "deptno", "t0"."dname" as "dname", "t1"."deptno" as "deptno0" 
FROM (SELECT "deptno", "dname" 
FROM "public"."dept" 
WHERE "dname" LIKE '%E%') AS "t0" 
INNER JOIN (SELECT "deptno" 
FROM "public"."emp") AS "t1" ON "t0"."deptno" = "t1"."deptno" 
ORDER BY "t1"."deptno" DESC) AS "t2"
{code}
So, basically I didn't understand if the bug resides in the fact that the Sort clause is below the last Project or the fact that the fields alias are not preserved from "t0", "t1" to the "t2". 

I really appreciate if you can clarify me this doubt.

Thank you.

> 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
>            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
(v7.6.3#76005)