[MDEV-15159] NULL is treated as 0 in CTE - Jira

XML

Word

Printable

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)

relates to

MDEV-16505 NULL is treated as 0 in CTE

MDEV-15447 Import CTE tests from MySQL 8

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