Skip to content
  • Guilhem Bichot's avatar
    4880f977
    WL#883 non-recursive CTE · 4880f977
    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).
    4880f977
    WL#883 non-recursive CTE
    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).
Loading