revision-id: e74181e3c2eae882a382d532eefdc71156974ae2 (mariadb-10.3.6-175-ge74181e) parent(s): 4a5e23e257e229b548599133dbed5162af9df6d9 author: Igor Babaev committer: Igor Babaev timestamp: 2018-05-14 14:38:17 -0700 message: 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.result | 19 +++++++++++++++++++ mysql-test/main/cte_recursive.test | 18 ++++++++++++++++++ sql/sql_union.cc | 12 +++++++++++- 3 files changed, 48 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 6fccd39..19a2e96 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -3509,3 +3509,22 @@ Beijing Bangkok Paris drop table flights, distances; +# +# MDEV-15159: Forced nullability of columns in recursive CTE +# +WITH RECURSIVE cte AS ( +SELECT 1 AS a UNION ALL +SELECT NULL FROM cte WHERE a IS NOT NULL) +SELECT * FROM cte; +a +1 +NULL +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; +a +0 +NULL +DROP TABLE t1; diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index 9d7f23a..b0b38a2 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; diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 9b9418f..971bb64 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -788,18 +788,28 @@ bool st_select_lex_unit::join_union_item_types(THD *thd_arg, join_union_type_attributes(thd_arg, holders, count)) DBUG_RETURN(true); + bool is_recursive= with_element && with_element->is_recursive; types.empty(); List_iterator_fast<Item> it(first_sl->item_list); Item *item_tmp; for (uint pos= 0; (item_tmp= it++); pos++) { + /* + SQL standard requires forced nullability only for + recursive columns. However type aggregation in our + implementation so far does not differentiate between + recursive and non-recursive columns of a recursive CTE. + TODO: this should be fixed. + */ + bool pos_maybe_null= is_recursive ? true : holders[pos].get_maybe_null(); + /* Error's in 'new' will be detected after loop */ types.push_back(new (thd_arg->mem_root) Item_type_holder(thd_arg, item_tmp, holders[pos].type_handler(), &holders[pos]/*Type_all_attributes*/, - holders[pos].get_maybe_null())); + pos_maybe_null)); } if (unlikely(thd_arg->is_fatal_error)) DBUG_RETURN(true); // out of memory