-
Guilhem Bichot authored
WL#3634 recursive CTE As single patch. Adds support for: WITH query_name AS (subquery) SELECT * FROM query_name; and WITH RECURSIVE query_name AS (recursive subquery) SELECT * FROM query_name; The objects introduced after WITH are called "common table expressions" (CTEs). There can be several, separated with ','. A CTE can reference CTEs defined before it in the WITH list. A CTE is materialized or merged, which can be influenced with the Merge/No_merge hint. A CTE can be referenced multiple times in the query; if it's materialized it's materialized only once in the query. Specific of WITH RECURSIVE: 'recursive subquery' must be of the form SELECT ... UNION [ALL] ... SELECT ... etc where the union is formed of a head of SELECTs which don't reference query_name (are non-recursive) followed by a tail of SELECTs which reference it. UNION DISTINCT and UNION ALL can be used. This allows traversing trees, hierarchies, finding transitive closures, computing numbers recursively, applying algorithms... CTEs can be defined in SELECT, UPDATE, DELETE, INSERT SELECT, REPLACE SELECT, CREATE SELECT, CREATE VIEW. After 'query_name' one can specify column names in parentheses: WITH query_name(a,b) AS... Such feature is also added to derived tables i.e. FROM (SELECT ...) AS dt(a,b) Such feature already existed for views but is rewritten (it had a bug, see at the end); to support this rewrite, a column VIEW_COLUMN_NAMES is added to the TABLES table of the Data Dictionary; this will prevent on-the-fly upgrades from 8.0.0 to 8.0.1, which mgmt has approved. Like derived tables: - a CTE may not reference an outer table - functional dependencies in (non-recursive) CTEs are recognized. - relevant indexes are automatically added to the materialized CTE if the Optimizer thinks that they will speed up the top query's access to the CTE. Also fixes: Bug#23265335 SPECIFYING A NAME FOR VIEW'S COLUMN IN CREATE VIEW MAKES SELECT FAIL Bug#23024178 WRITES TO INNODB INTERNAL TEMPORARY TABLE DOESN'T INCREASE "HANDLER_WRITE" Partially fixes: BUG#23022426 UNION ALL STILL USES TEMPORARY TABLE WITH INSERT SELECT i.e. with this WL, UNION ALL doesn't create a temporary table with INSERT SELECT iff the UNION ALL is not the top query expression after INSERT (i.e. is a subquery).
Guilhem Bichot authoredWL#3634 recursive CTE As single patch. Adds support for: WITH query_name AS (subquery) SELECT * FROM query_name; and WITH RECURSIVE query_name AS (recursive subquery) SELECT * FROM query_name; The objects introduced after WITH are called "common table expressions" (CTEs). There can be several, separated with ','. A CTE can reference CTEs defined before it in the WITH list. A CTE is materialized or merged, which can be influenced with the Merge/No_merge hint. A CTE can be referenced multiple times in the query; if it's materialized it's materialized only once in the query. Specific of WITH RECURSIVE: 'recursive subquery' must be of the form SELECT ... UNION [ALL] ... SELECT ... etc where the union is formed of a head of SELECTs which don't reference query_name (are non-recursive) followed by a tail of SELECTs which reference it. UNION DISTINCT and UNION ALL can be used. This allows traversing trees, hierarchies, finding transitive closures, computing numbers recursively, applying algorithms... CTEs can be defined in SELECT, UPDATE, DELETE, INSERT SELECT, REPLACE SELECT, CREATE SELECT, CREATE VIEW. After 'query_name' one can specify column names in parentheses: WITH query_name(a,b) AS... Such feature is also added to derived tables i.e. FROM (SELECT ...) AS dt(a,b) Such feature already existed for views but is rewritten (it had a bug, see at the end); to support this rewrite, a column VIEW_COLUMN_NAMES is added to the TABLES table of the Data Dictionary; this will prevent on-the-fly upgrades from 8.0.0 to 8.0.1, which mgmt has approved. Like derived tables: - a CTE may not reference an outer table - functional dependencies in (non-recursive) CTEs are recognized. - relevant indexes are automatically added to the materialized CTE if the Optimizer thinks that they will speed up the top query's access to the CTE. Also fixes: Bug#23265335 SPECIFYING A NAME FOR VIEW'S COLUMN IN CREATE VIEW MAKES SELECT FAIL Bug#23024178 WRITES TO INNODB INTERNAL TEMPORARY TABLE DOESN'T INCREASE "HANDLER_WRITE" Partially fixes: BUG#23022426 UNION ALL STILL USES TEMPORARY TABLE WITH INSERT SELECT i.e. with this WL, UNION ALL doesn't create a temporary table with INSERT SELECT iff the UNION ALL is not the top query expression after INSERT (i.e. is a subquery).
Loading