Details

    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

      Attachments

        Issue Links

          Activity

            igor Igor Babaev added a comment -

            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
            

            igor Igor Babaev added a comment - 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
            AntoineC CARME Antoine added a comment -

            Great.

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

            AntoineC CARME Antoine added a comment - Great. What a precision. This is a kind of surgical pattern extraction !!!!
            igor Igor Babaev added a comment -

            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;
            

            igor Igor Babaev added a comment - 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;
            AntoineC CARME Antoine added a comment -

            We have a fix. Excellent.

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

            AntoineC CARME Antoine added a comment - We have a fix. Excellent. Tests are not optional !! It's business as usual.
            igor Igor Babaev added a comment -

            A fix for this bug was pushed into 10.2

            igor Igor Babaev added a comment - A fix for this bug was pushed into 10.2
            AntoineC CARME Antoine added a comment -

            Excellent. Thanks a lot.

            Waiting for the 10.2.11 release.

            Antoine

            AntoineC CARME Antoine added a comment - Excellent. Thanks a lot. Waiting for the 10.2.11 release. Antoine

            People

              igor Igor Babaev
              AntoineC CARME Antoine
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.