[MDEV-13502] [DRAFT] Support external references in derived tables Created: 2017-08-11  Updated: 2017-08-11

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Major
Reporter: Sergei Petrunia Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Attachments: File igor-mdev13502-fixed.diff    
Issue Links:
PartOf
is part of MDEV-13428 Oracle-compatible recursive queries w... Open

 Description   

A motivating example of the CONNECT BY task includes a subquery with outer
references:

WHERE description LIKE :1
 
    AND type = ?
 
    AND status = ?
 
    AND EXISTS (
 
        SELECT ?
 
        FROM smt_group
 
        WHERE groupid = :2
 
        START WITH groupid = a.groupid
 
        CONNECT BY PRIOR parentid = groupid
 
    )

A problem with outer references comes from this:

  • Recursive CTEs syntax uses derived tables
  • The internal representation of Recursive CTEs also uses derived tables
  • Derived tables currently do not have/support outer references
  • If CONNECT BY is parsed into the same internal representation as Recursive CTEs, then we will need to internally support derived tables with outer references.

Attached is a patch by igor which adds support for outside references in the parser.

The execution part is not done yet, though. This task is about adding support for query execution.



 Comments   
Comment by Sergei Petrunia [ 2017-08-11 ]

Quoting from Igor's email:

With the above changes I had the following results:

create table t1 (a int, b int);
 
insert into t1 values (5,70), (9,30), (4,42), (2,15), (5,73);
 
create table t2 (a int);
 
insert into t2 values (7), (5), (3), (8), (3), (9);


MariaDB [test]> select * from t2 where exists (select * from (select t1.a, max(t1.b) from t1 where t1.a=t2.a group by t1.a) t);
 
Empty set

delete from t2 where a=7;

MariaDB [test]>select * from t2 where exists (select * from (select t1.a, max(t1.b) from t1 where t1.a=t2.a group by t1.a) t);
 
+------+
 
| a    |
 
+------+
 
|    5 |
 
|    3 |
 
|    8 |
 
|    3 |
 
|    9 |
 
+------+


MariaDB [test]> explain extended  select * from t2 where exists
 
(select * from (select t1.a, max(t1.b) from t1 where t1.a=t2.a group
 
by t1.a) t);
 
+------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
 
| id   | select_type        | table      | type | possible_keys | key
 
| key_len | ref  | rows | filtered | Extra       |
 
+------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
 
|    1 | PRIMARY            | t2         | ALL  | NULL          | NULL
 
| NULL    | NULL |    6 |   100.00 | Using where |
 
|    2 | DEPENDENT SUBQUERY | <derived3> | ALL  | NULL          | NULL
 
| NULL    | NULL |    5 |   100.00 |             |
 
|    3 | DEPENDENT DERIVED  | t1         | ALL  | NULL          | NULL
 
| NULL    | NULL |    5 |   100.00 | Using where |
 
+------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------------+


MariaDB [test]> select * from t2 where exists (with cte as (select
 
t1.a, max(t1.b) from t1 where t1.a=t2.a group by t1.a)select * from cte);
 
+------+
 
| a    |
 
+------+
 
|    5 |
 
|    3 |
 
|    8 |
 
|    3 |
 
|    9 |
 
+------+

Of course the result sets are incorrect because the derived table now is filled only once.

The refill is controlled by the flag JOIN_TAB::preread_init_done.

So when resolving an external reference within the specification of a derived table we have to attach a pointer
to the derived table DT to the table T against which the external reference was resolved. At the execution of the query

every time when we read a new row from the derived table the flag preread_init_done for DT must be set to off.

So the first task would be :

For each table T build the lists of pointers to all derived tables that refer to some fields of T .

Generated at Thu Feb 08 08:06:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.