Hi Varun, On Thu, Feb 09, 2017 at 10:37:17PM +0530, Varun wrote:

revision-id: 15fde78ce812c8db91340936941b986fb28ce1ad (mariadb-10.1.20-82-g15fde78) parent(s): 99b2de92c6214ddd73eba35c935f490eedf87a26 author: Varun Gupta committer: Varun Gupta timestamp: 2017-02-09 22:36:03 +0530 message:

MDEV-10731: Wrong NULL match results in "Subquery returns more than 1 row" (error code 1242)

Post review fixes

As far as I understand it's not Post-review fixes, it's a complete patch. Please use an appropriate comment.

--- mysql-test/r/update_innodb.result | 26 ++++++++++++++++++++++++++ mysql-test/t/update_innodb.test | 26 ++++++++++++++++++++++++++ sql/sql_select.cc | 19 +++++++++++-------- 3 files changed, 63 insertions(+), 8 deletions(-)

diff --git a/mysql-test/r/update_innodb.result b/mysql-test/r/update_innodb.result index 88c86c5..9ba6699 100644 --- a/mysql-test/r/update_innodb.result +++ b/mysql-test/r/update_innodb.result @@ -29,3 +29,29 @@ CREATE ALGORITHM=UNDEFINED VIEW `v1` AS select `t4`.`c1` AS `c1`,`t4`.`c2` AS `c UPDATE t1 a JOIN t2 b ON a.c1 = b.c1 JOIN v1 vw ON b.c2 = vw.c1 JOIN t3 del ON vw.c2 = del.c2 SET a.c2 = ( SELECT max(t.c1) FROM t3 t, v1 i WHERE del.c2 = t.c2 AND vw.c3 = i.c3 AND t.c3 = 4 ) WHERE a.c2 IS NULL OR a.c2 < '2011-05-01'; drop view v1; drop table t1,t2,t3,t4; +# +# MDEV-10232 Scalar result of subquery changes after adding an outer select stmt +# +CREATE TABLE t1 ( +a_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, +b_id INT(20) UNSIGNED NULL DEFAULT NULL, +c_id VARCHAR(255) NULL DEFAULT NULL, +PRIMARY KEY (a_id))COLLATE = 'utf8_general_ci' ENGINE = InnoDB; +CREATE TABLE t2 ( +b_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, +c_id VARCHAR(255) NULL DEFAULT NULL, +PRIMARY KEY (b_id), +INDEX idx_c_id (c_id))COLLATE = 'utf8_general_ci' ENGINE = InnoDB; +INSERT INTO t1 (b_id, c_id) VALUES (NULL, NULL); +INSERT INTO t2 (c_id) VALUES (NULL); +INSERT INTO t2 (c_id) VALUES (NULL); +SELECT * FROM T1; +a_id b_id c_id +1 NULL NULL +SELECT t2.b_id FROM t1,t2 WHERE t2.c_id = t1.c_id; +b_id +UPDATE t1 SET b_id = (SELECT t2.b_id FROM t2 t2 WHERE t2.c_id = t1.c_id); +SELECT * FROM T1; +a_id b_id c_id +1 NULL NULL +drop table t1,t2; diff --git a/mysql-test/t/update_innodb.test b/mysql-test/t/update_innodb.test index 67c356c..d4fb8b7 100644 --- a/mysql-test/t/update_innodb.test +++ b/mysql-test/t/update_innodb.test @@ -37,3 +37,29 @@ UPDATE t1 a JOIN t2 b ON a.c1 = b.c1 JOIN v1 vw ON b.c2 = vw.c1 JOIN t3 del ON v

drop view v1; drop table t1,t2,t3,t4; + +--echo # +--echo # MDEV-10232 Scalar result of subquery changes after adding an outer select stmt +--echo # + +CREATE TABLE t1 ( + a_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + b_id INT(20) UNSIGNED NULL DEFAULT NULL, + c_id VARCHAR(255) NULL DEFAULT NULL, + PRIMARY KEY (a_id))COLLATE = 'utf8_general_ci' ENGINE = InnoDB; + +CREATE TABLE t2 ( + b_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + c_id VARCHAR(255) NULL DEFAULT NULL, + PRIMARY KEY (b_id), + INDEX idx_c_id (c_id))COLLATE = 'utf8_general_ci' ENGINE = InnoDB; + +INSERT INTO t1 (b_id, c_id) VALUES (NULL, NULL); +INSERT INTO t2 (c_id) VALUES (NULL); +INSERT INTO t2 (c_id) VALUES (NULL); + +SELECT * FROM T1; +SELECT t2.b_id FROM t1,t2 WHERE t2.c_id = t1.c_id; +UPDATE t1 SET b_id = (SELECT t2.b_id FROM t2 t2 WHERE t2.c_id = t1.c_id); +SELECT * FROM T1; +drop table t1,t2; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e37c1b8..bc9a80f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -9367,8 +9367,6 @@ static void add_not_null_conds(JOIN *join) UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1); not_null_item is the t1.f1, but it's referred_tab is 0. */ - if (!referred_tab) - continue; if (!(notnull= new (join->thd->mem_root) Item_func_isnotnull(join->thd, item))) DBUG_VOID_RETURN; @@ -9380,16 +9378,21 @@ static void add_not_null_conds(JOIN *join) */ if (notnull->fix_fields(join->thd, ¬null)) DBUG_VOID_RETURN; - DBUG_EXECUTE("where",print_where(notnull, - referred_tab->table->alias.c_ptr(), - QT_ORDINARY);); + + if(referred_tab) + DBUG_EXECUTE("where",print_where(notnull, + referred_tab->table->alias.c_ptr(), + QT_ORDINARY);); + else + DBUG_EXECUTE("where",print_where(notnull, + "outer_ref_cond",QT_ORDINARY););

Please take the re-factoring further and have one DBUG_EXECUTE call with referred_tab? referred_tab->table->alias.c_ptr(): "outer_ref_cond" expression as the parameter.

if (!tab->first_inner) - { - COND *new_cond= referred_tab->join == join ? + {

Does the above break indentation or it has a tab character?

+ COND *new_cond= (referred_tab && referred_tab->join == join) ? referred_tab->select_cond : join->outer_ref_cond; add_cond_and_fix(join->thd, &new_cond, notnull); - if (referred_tab->join == join) + if (referred_tab && referred_tab->join == join) referred_tab->set_select_cond(new_cond, __LINE__); else join->outer_ref_cond= new_cond;

OK to push after the above is addressed. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog