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).
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
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.
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://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').
- is blocked by
PS-6981 QA Ticket for PS-5764 Introduce SEQUENCE_TABLE() table-level SQL function