MDEV-15159 NULL is treated as 0 in CTE

Forced columns of recursive CTEs to be nullable. SQL standard
requires this only from recursive columns, but in our code
so far we do not differentiate between recursive and non-recursive
columns when aggregating types of the union that specifies a
recursive CTE.

1 files changed, 18 insertions, 0 deletions

diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test
index 9d7f23a2258..b0b38a28b34 100644
--- a/mysql-test/main/cte_recursive.test
+++ b/mysql-test/main/cte_recursive.test
@@ -2458,3 +2458,21 @@ with recursive destinations (city) as
 select * from destinations;
 
 drop table flights, distances;
+
+--echo #
+--echo # MDEV-15159: Forced nullability of columns in recursive CTE
+--echo #
+
+WITH RECURSIVE cte AS (
+	SELECT 1 AS a UNION ALL
+	SELECT NULL FROM cte WHERE a IS NOT NULL)
+SELECT * FROM cte;
+
+CREATE TABLE t1 (a int NOT NULL);
+INSERT INTO t1 VALUES (0);
+
+WITH RECURSIVE cte AS
+  (SELECT a FROM t1 where a=0 UNION SELECT NULL FROM cte)
+SELECT * FROM cte;
+
+DROP TABLE t1;