Affects Version/s: None
Fix Version/s: 8.0.20-11
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.
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
will result in
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
Whereas SELECT * FROM SEQUENCE_TABLE('abc') AS tt / SELECT * FROM SEQUENCE_TABLE(-5) AS tt should return
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).
SELECT value + 5 FROM SEQUENCE_TABLE(3) AS tt
SELECT 2 * value + 2 FROM SEQUENCE_TABLE(3) AS tt
SELECT 7 - value FROM SEQUENCE_TABLE(3) AS tt
SELECT ELT(value + 1, 'a', 'b', 'c') FROM SEQUENCE_TABLE(3) AS tt
INSERT INTO tbl SELECT * FROM SEQUENCE_TABLE(3) AS tt
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)
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').