Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-5764

Introduce SEQUENCE_TABLE() table-level SQL function

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 8.0.20-11
    • Component/s: None
    • Labels:
      None

      Description

      Introduction

      Implement a new table-level function SEQUENCE_TABLE(expr) which would generate a zero-based sequence of integers that are less than expr in a form of a row set.

      +-------+
      | value |
      +-------+
      | 0     |
      | 1     |
      | ...   |
      | N-1   |
      +-------+
      

      The result set should include a single column with predefined name 'value' that will be defined as 'BIGINT UNSIGNED NOT NULL'. In other words, the following two statements

      CREATE TABLE t1 AS SELECT * FROM SEQUENCE_TABLE(...) AS tt;
      SHOW CREATE TABLE t1;
      

      will result in

      Table	Create Table
      t1	CREATE TABLE `t1` (
        `value` bigint unsigned NOT NULL DEFAULT '0'
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
      

      Please notice that as for other table functions (like 'JSON_TABLE()') table alias ('AS tt' in the example) is NOT optional.

      If expr is <=0, NULL or cannot be converted to an integer, an empty row set should be returned.
      For instance, SELECT * FROM SEQUENCE_TABLE(3) AS tt / SELECT value FROM SEQUENCE_TABLE(3) AS tt should return

      +-------+
      | value |
      +-------+
      | 0     |
      | 1     |
      | 2     |
      +-------+
      

      Whereas SELECT * FROM SEQUENCE_TABLE('abc') AS tt / SELECT * FROM SEQUENCE_TABLE(-5) AS tt should return

      +-------+
      | value |
      +-------+
      +-------+
      

      Please also notice that max value passed to 'SEQUENCE_TABLE()' should not exceed @@global.tf_sequence_table_max_upper_bound. Otherwise, 'ER_SEQUENCE_TABLE_SIZE_LIMIT' error will be reported.
      @@global.tf_sequence_table_max_upper_bound is a new global dynamic variable with default value of 1048576 (2^20), min value 1024, max value 2^64 - 1 which can be changed to increase this limit (SUPER privilege is required to perform this action).

      Use cases

      Generating a sequence from 5 to 7 inclusive

      SELECT value + 5 FROM SEQUENCE_TABLE(3) AS tt

      +-------+
      | value |
      +-------+
      | 5     |
      | 6     |
      | 7     |
      +-------+
      

      Generating even numbers from 2 to 6 inclusive

      SELECT 2 * value + 2 FROM SEQUENCE_TABLE(3) AS tt

      +-------+
      | value |
      +-------+
      | 2     |
      | 4     |
      | 6     |
      +-------+
      

      Values from 7 to 5 in reverse order

      SELECT 7 - value FROM SEQUENCE_TABLE(3) AS tt

      +-------+
      | value |
      +-------+
      | 7     |
      | 6     |
      | 5     |
      +-------+
      

      Generating a set of strings as a row set

      SELECT ELT(value + 1, 'a', 'b', 'c') FROM SEQUENCE_TABLE(3) AS tt

      +-------+
      | value |
      +-------+
      | 'a'   |
      | 'b'   |
      | 'c'   |
      +-------+
      

      Filling an existing table with sequential numbers

      INSERT INTO tbl SELECT * FROM SEQUENCE_TABLE(3) AS tt

      Creating a new table and filling it with sequential numbers

      CREATE TABLE tbl AS SELECT * FROM SEQUENCE_TABLE(3) AS tt

      Notes

      Please notice that SEQUENCE_TABLE() is NOT a SEQUENCE object defined by the SQL standard (like, for instance, as in Oracle Database Server where you can CREATE SEQUENCE seq and then SELECT * FROM seq.NEXTVAL)

      CREATE SEQUENCE employees_seq START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;
      SELECT employees_seq.NEXTVAL FROM DUAL;
      

      Internally in Percona Server this function should behave exactly as any other table function (currently, only JSON_TABLE()) or any other derived table (like SELECT * FROM (SELECT * FROM t) AS tt).
      In other words, its result set is first materialized into an implicit temporary table (Internal Temporary Storage Engine by default) and only then takes part in the SQL statement execution.
      SEQUENCE_TABLE() is deterministic - when called with the same argument, it always produces the same result set in the same order.
      There are no concurrency issues between different sessions executing statements involving SEQUENCE_TABLE() as their record sets are materialized into different per-session temp storage areas.

      Implementation

      The implementation should be similar to the 'JSON_TABLE()' function (https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html) that was added by Oracle in MySQL Server 8.0.11,

      WL#8867 "Add JSON table functions"
      https://dev.mysql.com/worklog/task/?id=8867

      https://github.com/mysql/mysql-server/commit/e344d5c6c60 WL#8867: Add JSON_TABLE function
      https://github.com/mysql/mysql-server/commit/e50e654a560 WL#8867: Add JSON_TABLE function
      https://github.com/mysql/mysql-server/commit/cbf8e3e2493 WL#8867: Add JSON_TABLE function
      https://github.com/mysql/mysql-server/commit/a5b124a41cd WL#8867: Add JSON_TABLE function
      https://github.com/mysql/mysql-server/commit/f92e8554f28 WL#8867: Add JSON_TABLE function
      https://github.com/mysql/mysql-server/commit/f1131e4d4f8 WL#8867: Add JSON_TABLE function

      Similarly to 'Table_function_json' inherited from 'Table_function' we should introduce a new class 'Table_function_sequence', inherit it from 'Table_function' class and implement its interface.

      A new keyword should also be registered in the SQL grammar ('sql_yacc.yy').

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              yura.sorokin Yura Sorokin
              Reporter:
              yura.sorokin Yura Sorokin
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 2 weeks, 3 days, 6 hours, 30 minutes
                  2w 3d 6h 30m

                    Smart Checklist