Details

    Description

      testcase:

      WITH RECURSIVE cte AS (
      	SELECT 1 AS a UNION ALL
      	SELECT NULL FROM cte WHERE a IS NOT NULL)
      SELECT * FROM cte;
      

      it produces an infinite loop, so will get (max_recursive_iterations) strings with 0 (plus 1 sting with 1)
      if we use union distinct:

      MariaDB [test]> WITH RECURSIVE cte AS (
          -> SELECT 1 AS a UNION 
          -> SELECT NULL FROM cte WHERE a IS NOT NULL)
          -> SELECT * FROM cte;
      +---+
      | a |
      +---+
      | 1 |
      | 0 |
      +---+
      2 rows in set (0.001 sec)
      

      Attachments

        Issue Links

          Activity

            igor Igor Babaev added a comment -

            Alice,
            The type of CTE is determined only by the type of anchor parts.
            In your case the type of the anchor part is INT NOT NULL.
            The recursive part returns NULL that is converted to this type.
            Please compare with:

            MariaDB [test]> create table t1 (a int);
             
            MariaDB [test]> insert into t1 values (1);
             
            MariaDB [test]> WITH RECURSIVE cte AS (SELECT a FROM t1 UNION SELECT NULL FROM cte WHERE a IS NOT NULL) SELECT * FROM cte;
            +------+
            | a    |
            +------+
            |    1 |
            | NULL |
            +------+
            

            igor Igor Babaev added a comment - Alice, The type of CTE is determined only by the type of anchor parts. In your case the type of the anchor part is INT NOT NULL. The recursive part returns NULL that is converted to this type. Please compare with: MariaDB [test]> create table t1 (a int);   MariaDB [test]> insert into t1 values (1);   MariaDB [test]> WITH RECURSIVE cte AS (SELECT a FROM t1 UNION SELECT NULL FROM cte WHERE a IS NOT NULL) SELECT * FROM cte; +------+ | a | +------+ | 1 | | NULL | +------+
            alice Alice Sherepa added a comment -

            Igor, please reconsider this case.
            My point is that it should be nullable or else it can be án easy source of mistakes.
            Instead of NULL can be some complicated expression/function, that will return NULL. Then it truncates to 0, and we can get wrong results. If the CTE's recursive part produces wider values for a column than the CTE's nonrecursive part, it is possible to use CAST, but what should one do in the case with nullability. At least it should be warning about it.

            Comparing to SQL Server: "All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements." The same behavior is implemented in Mysql 8.0

            create table t1 (a int not null);
            insert into t1 values (0);
            WITH RECURSIVE cte AS (SELECT a FROM t1 where a=0 UNION ALL SELECT NULL FROM cte) 
            SELECT * FROM cte;
            

            alice Alice Sherepa added a comment - Igor, please reconsider this case. My point is that it should be nullable or else it can be án easy source of mistakes. Instead of NULL can be some complicated expression/function, that will return NULL. Then it truncates to 0, and we can get wrong results. If the CTE's recursive part produces wider values for a column than the CTE's nonrecursive part, it is possible to use CAST, but what should one do in the case with nullability. At least it should be warning about it. Comparing to SQL Server: "All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements." The same behavior is implemented in Mysql 8.0 create table t1 (a int not null ); insert into t1 values (0); WITH RECURSIVE cte AS ( SELECT a FROM t1 where a=0 UNION ALL SELECT NULL FROM cte) SELECT * FROM cte;
            igor Igor Babaev added a comment -

            This bug cannot be reproduced in 10.2 anymore due to a changed rule of type aggregations in recursive units. (BTW this rule is not in line with SQL standard).
            As this rule was not applied to 10.3 the bug still can be reproduced there.

            igor Igor Babaev added a comment - This bug cannot be reproduced in 10.2 anymore due to a changed rule of type aggregations in recursive units. (BTW this rule is not in line with SQL standard). As this rule was not applied to 10.3 the bug still can be reproduced there.
            igor Igor Babaev added a comment -

            A fix for this bug was pushed into 10.3

            igor Igor Babaev added a comment - A fix for this bug was pushed into 10.3

            People

              igor Igor Babaev
              alice Alice Sherepa
              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.