Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1886

Support "LIMIT [offset,] row_count", per MySQL

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.14.0
    • Component/s: None
    • Labels:
      None

      Description

      CALCITE support limit offset SQL grammar in CALCITE-43:

        [LIMIT { count | ALL}]
        [OFFSET start { ROW | ROWS}]
      

      Also

      LIMIT {[offset,] row_count}
      

      is popular used, especially in MySQL, such as:

      select * from tableA order by id limit 100,10;
      

      User should transform the existing SQLs until calcite support

       LIMIT {[offset,] row_count}

        Issue Links

          Activity

          Hide
          michaelmior Michael Mior added a comment -

          Resolved in release 1.14.0 (2017-10-01)

          Show
          michaelmior Michael Mior added a comment - Resolved in release 1.14.0 (2017-10-01)
          Hide
          julianhyde Julian Hyde added a comment -

          Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/bdb953fa. It is enabled in the MYSQL_5 and LENIENT conformances. Thanks for the fix, and the additional test cases, Kaiwang Chen!

          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/bdb953fa . It is enabled in the MYSQL_5 and LENIENT conformances. Thanks for the fix, and the additional test cases, Kaiwang Chen !
          Hide
          kaiwangchen Kaiwang Chen added a comment -

          the production is revised to disallow "all" in "start, count", additional tests added.

          BTW, "limit 2, 3 fetch next 4 rows only" behave same as "limit 4 offset 2"

          Show
          kaiwangchen Kaiwang Chen added a comment - the production is revised to disallow "all" in "start, count", additional tests added. BTW, "limit 2, 3 fetch next 4 rows only" behave same as "limit 4 offset 2"
          Hide
          julianhyde Julian Hyde added a comment -

          I have reviewed https://github.com/apache/calcite/pull/498/commits/d747787eb6f93583e7cb4f20ed8d8a82c2ae9859. It looks good, but we need additional tests:

          • "limit 2, all" (currently allowed, should probably be allowed)
          • "limit all, 2" (currently allowed, should be an error)
          • "limit 2, 3 offset 4" (currently allowed, should probably behave same as "limit 3 offset 4"
          • "limit 2, 3 fetch next 4 rows only" (currently allowed, should probably behave same as "limit 4 offset 3"
          Show
          julianhyde Julian Hyde added a comment - I have reviewed https://github.com/apache/calcite/pull/498/commits/d747787eb6f93583e7cb4f20ed8d8a82c2ae9859 . It looks good, but we need additional tests: "limit 2, all" (currently allowed, should probably be allowed) "limit all, 2" (currently allowed, should be an error) "limit 2, 3 offset 4" (currently allowed, should probably behave same as "limit 3 offset 4" "limit 2, 3 fetch next 4 rows only" (currently allowed, should probably behave same as "limit 4 offset 3"
          Hide
          julianhyde Julian Hyde added a comment -

          Sounds good. Contributions welcome!

          Show
          julianhyde Julian Hyde added a comment - Sounds good. Contributions welcome!
          Hide
          qihanycx Chen Xin Yu added a comment -

          Besides MySQL, CUBRID support this syntax.
          It would be helpful if Calcite support this by setting SqlConformance to MYSQL or LENIENT in future.

          Show
          qihanycx Chen Xin Yu added a comment - Besides MySQL, CUBRID support this syntax. It would be helpful if Calcite support this by setting SqlConformance to MYSQL or LENIENT in future.
          Hide
          julianhyde Julian Hyde added a comment - - edited

          I see, this is a non-standard MySQL syntax. (Jeez, I wish those guys had read the standard, or at least looked at what other DBs were doing, once in a while.)

          Out of interest, are there any other databases that support this?

          I don't see a problem with supporting this, enabled if SqlConformance is MYSQL or LENIENT.

          Show
          julianhyde Julian Hyde added a comment - - edited I see, this is a non-standard MySQL syntax. (Jeez, I wish those guys had read the standard, or at least looked at what other DBs were doing, once in a while.) Out of interest, are there any other databases that support this? I don't see a problem with supporting this, enabled if SqlConformance is MYSQL or LENIENT.
          Hide
          qihanycx Chen Xin Yu added a comment - - edited

          Calcite supports
          INSERT into db_sink SELECT id,name,price + 1 as price FROM kafka_source limit 100 offset 10

          But what's expected is :
          INSERT into db_sink SELECT id,name,price + 1 as price FROM kafka_source limit 10, 100

          Show
          qihanycx Chen Xin Yu added a comment - - edited Calcite supports INSERT into db_sink SELECT id,name,price + 1 as price FROM kafka_source limit 100 offset 10 But what's expected is : INSERT into db_sink SELECT id,name,price + 1 as price FROM kafka_source limit 10, 100
          Hide
          yuqi yuqi added a comment -

          Calcite has already support this grammar, as far as a i know
          for example the sql:

          INSERT into db_sink SELECT id,name,price + 1 as price FROM kafka_source limit 100 offset 10
          

          can be translated as

          'LogicalTableModify(table=[[DB_SINK]], operation=[INSERT], flattened=[true])
            LogicalSort(offset=[10], fetch=[100])
              LogicalProject(ID=[$0], NAME=[$1], PRICE=[+($2, 1)])
                EnumerableTableScan(table=[[KAFKA_SOURCE]])
          
          Show
          yuqi yuqi added a comment - Calcite has already support this grammar, as far as a i know for example the sql: INSERT into db_sink SELECT id,name,price + 1 as price FROM kafka_source limit 100 offset 10 can be translated as 'LogicalTableModify(table=[[DB_SINK]], operation=[INSERT], flattened=[ true ]) LogicalSort(offset=[10], fetch=[100]) LogicalProject(ID=[$0], NAME=[$1], PRICE=[+($2, 1)]) EnumerableTableScan(table=[[KAFKA_SOURCE]])

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              qihanycx Chen Xin Yu
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development