From e74181e3c2eae882a382d532eefdc71156974ae2 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 14 May 2018 14:38:17 -0700 Subject: 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. --- mysql-test/main/cte_recursive.test | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) (limited to 'mysql-test/main/cte_recursive.test') 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; -- cgit v1.2.1