[MDEV-13780] CTE not found Created: 2017-09-11  Updated: 2021-07-08  Resolved: 2017-11-06

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.2
Fix Version/s: 10.2.11

Type: Bug Priority: Major
Reporter: CARME Antoine Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-14184 recursive CTE not found Closed

 Description   

Hi,

When trying to find a workaround for jira MDEV-13730, I generated a new code which does not use too many CTEs in the same scope.

This is a sample new SQL code :

https://raw.githubusercontent.com/antoinecarme/sklearn2sql-demo/master/tests/mariadb/MDEV-13730/new/GradientBoostingClassifier/iris/mysql/demo2_GradientBoostingClassifier_mysql.sql

and when executed, it outputs the error :

1146, "Table 'db.DT_node_lookup' doesn't exist"

There is no such table, but a CTE is defined in the SQL code with the same name.

At first, the last error seems to be a CTE resolution issue when CTEs are defined inside CTEs.

I am working on debian, with a mariadb version 10.2.8.

Thanks in advance

Antoine

 Comments   
Comment by Igor Babaev [ 2017-09-12 ]

The following simple test case demonstrates this problem:

create table t1 (a int);
 
insert into t1 values (3), (2), (4), (7), (1), (2), (5);
 
with cte_o as ( with cte_i as (select * from t1 where a < 7) select * from cte_i where a > 1 )
 
select * from cte_o as cto_o1 where a < 3 union select * from cte_o as cto_o2 where a > 4;

When running the above query we get:

MariaDB [test]> with cte_o as ( with cte_i as (select * from t1 where a < 7) select * from cte_i where a > 1 )
 
    -> select * from cte_o as cto_o1 where a < 3 union select * from cte_o as cto_o2 where a > 4;
 
ERROR 1146 (42S02): Table 'test.cte_i' doesn't exist

Comment by CARME Antoine [ 2017-09-12 ]

Great.

What a precision. This is a kind of surgical pattern extraction !!!!

Comment by Igor Babaev [ 2017-09-13 ]

Antoine,
The good thing is that I've fixed the problem with name resolution in your query.
The bad thing that the bugs were really serious, especially one of them though the fix of this one required
removing only one line. Anyway we need a good testing for the fix.
By the way your problem is simulated rather by the following query (not by the above one)

with cte_e as 
  ( with cte_o as 
    ( with cte_i as (select * from t1 where a < 7) select * from cte_i where a > 1 )
 
    select * from cte_o as cto_o1 where a < 3 union select * from cte_o as cto_o2 where a > 4)
 
select * from cte_e as cte_e1 where a > 1 union select * from cte_e as cte_2 where a < 7;

Comment by CARME Antoine [ 2017-09-13 ]

We have a fix. Excellent.

Tests are not optional !! It's business as usual.

Comment by Igor Babaev [ 2017-11-06 ]

A fix for this bug was pushed into 10.2

Comment by CARME Antoine [ 2017-11-06 ]

Excellent. Thanks a lot.

Waiting for the 10.2.11 release.

Antoine

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