revision-id: baec17bc033cb9be02428e2089046df9f530754a (mariadb-10.2.43-66-gbaec17bc033) parent(s): e4e25d2bacc067417c35750f5f6c44cad10c81de author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-04-21 15:03:23 +0300 message: MDEV-25994: Crash with union of my_decimal type in ORDER BY clause When single-row subquery fails with "Subquery reutrns more than 1 row" error, it will raise an error and return NULL. On the other hand, Item_singlerow_subselect sets item->maybe_null=0 for table-less subqueries like "(SELECT not_null_value)" (*) This discrepancy (item with maybe_null=0 returning NULL) causes the code in Type_handler_decimal_result::make_sort_key_part() to crash. Fixed this by allowing inference (*) only when the subquery is NOT a UNION. --- mysql-test/r/order_by.result | 20 ++++++++++++++++++++ mysql-test/r/subselect.result | 2 +- mysql-test/r/subselect_no_exists_to_in.result | 2 +- mysql-test/r/subselect_no_mat.result | 2 +- mysql-test/r/subselect_no_opts.result | 2 +- mysql-test/r/subselect_no_scache.result | 2 +- mysql-test/r/subselect_no_semijoin.result | 2 +- mysql-test/t/order_by.test | 25 +++++++++++++++++++++++++ sql/item_subselect.cc | 15 +++++++++++---- 9 files changed, 62 insertions(+), 10 deletions(-) diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index d1f2d067643..b21e350ac4a 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -3508,4 +3508,24 @@ DELETE FROM t1 ORDER BY c; DROP TABLE t1; SET @@SESSION.max_sort_length=DEFAULT; SET sql_mode=DEFAULT; +# +# MDEV-25994 Crash with union of my_decimal type in ORDER BY clause +# +CREATE TABLE t1 (v1 INTEGER) ; +INSERT INTO t1 (v1) VALUES (8); +UPDATE t1 SET v1 = 1 ORDER BY (SELECT 1.1 UNION SELECT -1); +ERROR 21000: Subquery returns more than 1 row +# This one must be successful +UPDATE t1 SET v1 = 2 ORDER BY (SELECT 1 UNION SELECT 1); +UPDATE t1 SET v1 = 3 ORDER BY (SELECT 'a' UNION SELECT 'b'); +ERROR 21000: Subquery returns more than 1 row +# Insert some more data +INSERT INTO t1 (v1) VALUES (8),(9),(100),(-234),(46584),(0); +UPDATE t1 SET v1 = v1+1 ORDER BY (SELECT 100.122 UNION SELECT -189.2); +ERROR 21000: Subquery returns more than 1 row +# This one must be successful +UPDATE t1 SET v1 = v1-200 ORDER BY (SELECT 1 UNION SELECT 1); +UPDATE t1 SET v1 = v1 ORDER BY (SELECT 'abc' UNION SELECT 'bbb'); +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1; # End of 10.2 tests diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index a03a2cff207..ed3b49ff40d 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1261,7 +1261,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index f7da3fdb2e2..499cad5538a 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -1265,7 +1265,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 6ab304c190b..a6e97636eed 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -1268,7 +1268,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 338ddd5808b..571d0553212 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -1264,7 +1264,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 741b070a38b..b2b25c3efa8 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -1267,7 +1267,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index ebabafb9c77..af8df6a6331 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -1264,7 +1264,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(20) NOT NULL + `a` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 7cb24b7d03b..2187786deb7 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -2331,5 +2331,30 @@ DROP TABLE t1; SET @@SESSION.max_sort_length=DEFAULT; SET sql_mode=DEFAULT; +--echo # +--echo # MDEV-25994 Crash with union of my_decimal type in ORDER BY clause +--echo # + +CREATE TABLE t1 (v1 INTEGER) ; +INSERT INTO t1 (v1) VALUES (8); +--error ER_SUBQUERY_NO_1_ROW +UPDATE t1 SET v1 = 1 ORDER BY (SELECT 1.1 UNION SELECT -1); +--echo # This one must be successful +UPDATE t1 SET v1 = 2 ORDER BY (SELECT 1 UNION SELECT 1); +--error ER_SUBQUERY_NO_1_ROW +UPDATE t1 SET v1 = 3 ORDER BY (SELECT 'a' UNION SELECT 'b'); + +-- echo # Insert some more data +INSERT INTO t1 (v1) VALUES (8),(9),(100),(-234),(46584),(0); +--error ER_SUBQUERY_NO_1_ROW +UPDATE t1 SET v1 = v1+1 ORDER BY (SELECT 100.122 UNION SELECT -189.2); +--echo # This one must be successful +UPDATE t1 SET v1 = v1-200 ORDER BY (SELECT 1 UNION SELECT 1); +--error ER_SUBQUERY_NO_1_ROW +UPDATE t1 SET v1 = v1 ORDER BY (SELECT 'abc' UNION SELECT 'bbb'); + + +DROP TABLE t1; + --echo # End of 10.2 tests diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index e01c92b7a4f..7033d38c07b 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1266,11 +1266,18 @@ bool Item_singlerow_subselect::fix_length_and_dec() } unsigned_flag= value->unsigned_flag; /* - If there are not tables in subquery then ability to have NULL value - depends on SELECT list (if single row subquery have tables then it - always can be NULL if there are not records fetched). + If the subquery has no tables (1) and is not a UNION (2), like: + + (SELECT subq_value) + + then its NULLability is the same as subq_value's NULLability. + + (1): A subquery that uses a table will return NULL when the table is empty. + (2): A UNION subquery will return NULL if it produces a "Subquery returns + more than one row" error. */ - if (engine->no_tables()) + if (engine->no_tables() && + engine->engine_type() != subselect_engine::UNION_ENGINE) maybe_null= engine->may_be_null(); else {