maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #10292
Re: [Commits] 14747e4: MDEV-10731: Wrong NULL match results in "Subquery returns more than 1 row" (error code 1242)
Hi Varun,
On Tue, Jan 17, 2017 at 03:24:39PM +0530, Varun wrote:
> revision-id: 14747e4b84f68ee7e10a6e5a24f4b7ec6f3240f9 (mariadb-10.1.20-38-g14747e4)
> parent(s): b56f726e42e8bc0427a0c33cfffb7e1c5399ea16
> author: Varun Gupta
> committer: Varun Gupta
> timestamp: 2017-01-17 15:22:22 +0530
> message:
>
> MDEV-10731: Wrong NULL match results in "Subquery returns more than 1 row" (error code 1242)
>
> NOT NULL predicate was not added to tables in case of an update query having a subquery.
>
> ---
> mysql-test/r/update_innodb.result | 30 +++++++++++++++++
> mysql-test/t/update_innodb.test | 31 +++++++++++++++++
> sql/sql_select.cc | 70 +++++++++++++++++++++++++--------------
> 3 files changed, 106 insertions(+), 25 deletions(-)
>
> diff --git a/mysql-test/r/update_innodb.result b/mysql-test/r/update_innodb.result
> index 88c86c5..6a02dfd 100644
> --- a/mysql-test/r/update_innodb.result
> +++ b/mysql-test/r/update_innodb.result
> @@ -29,3 +29,33 @@ 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
> +#
The MDEV number and title are wrong! Please fix.
> +CREATE TABLE a (
Please make tables names to follow the t1,t2,t3,... naming convention used in
the testcase.
> +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 b (
> +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 a (b_id, c_id) VALUES (1, NULL);
> +INSERT INTO b (c_id) VALUES (NULL);
> +INSERT INTO b (c_id) VALUES (NULL);
> +select b_id from a;
> +b_id
> +1
> +UPDATE a SET b_id = (SELECT b.b_id FROM b b WHERE b.c_id = a.c_id);
> +select b_id from a;
> +b_id
> +NULL
> +drop table a,b;
> diff --git a/mysql-test/t/update_innodb.test b/mysql-test/t/update_innodb.test
> index 67c356c..f2b7ade 100644
> --- a/mysql-test/t/update_innodb.test
> +++ b/mysql-test/t/update_innodb.test
> @@ -37,3 +37,34 @@ 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 a (
> + 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 b (
> + 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 a (b_id, c_id) VALUES (1, NULL);
> +INSERT INTO b (c_id) VALUES (NULL);
> +INSERT INTO b (c_id) VALUES (NULL);
> +
> +select b_id from a;
> +UPDATE a SET b_id = (SELECT b.b_id FROM b b WHERE b.c_id = a.c_id);
> +select b_id from a;
> +drop table a,b;
> diff --git a/sql/sql_select.cc b/sql/sql_select.cc
> index 85bfb6b..3543cc2 100644
> --- a/sql/sql_select.cc
> +++ b/sql/sql_select.cc
> @@ -9363,34 +9363,54 @@ static void add_not_null_conds(JOIN *join)
> 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)
> + {
> + if (!(notnull= new (join->thd->mem_root)
> Item_func_isnotnull(join->thd, item)))
> - DBUG_VOID_RETURN;
> - /*
> - We need to do full fix_fields() call here in order to have correct
> - notnull->const_item(). This is needed e.g. by test_quick_select
> - when it is called from make_join_select after this function is
> - called.
> - */
Note this as (LOC1)
> - 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 (!tab->first_inner)
> - {
> - COND *new_cond= 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)
> - referred_tab->set_select_cond(new_cond, __LINE__);
> - else
> - join->outer_ref_cond= new_cond;
> + DBUG_VOID_RETURN;
> + /*
> + We need to do full fix_fields() call here in order to have correct
> + notnull->const_item(). This is needed e.g. by test_quick_select
> + when it is called from make_join_select after this function is
> + called.
> + */
> + if (notnull->fix_fields(join->thd, ¬null))
> + DBUG_VOID_RETURN;
> +
> + if (!tab->first_inner)
> + add_cond_and_fix(join->thd, &join->outer_ref_cond, notnull);
> + else
> + add_cond_and_fix(join->thd, tab->first_inner->on_expr_ref, notnull);
> }
> else
> - add_cond_and_fix(join->thd, tab->first_inner->on_expr_ref, notnull);
> + {
> + if (!(notnull= new (join->thd->mem_root)
> + Item_func_isnotnull(join->thd, item)))
> + DBUG_VOID_RETURN;
> + /*
> + We need to do full fix_fields() call here in order to have correct
> + notnull->const_item(). This is needed e.g. by test_quick_select
> + when it is called from make_join_select after this function is
> + called.
> + */
This is LOC2.
After the patch, there is a lot of code duplication between LOC1 and LOC2:
- creation of Item_func_isnotnull
- fix_fields call and its comment
- etc
Please factor out the common code.
The "print_where" call can print "outer_ref_table_cond" when referred_tab=NULL.
> + 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 (!tab->first_inner)
> + {
> + COND *new_cond= 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)
> + referred_tab->set_select_cond(new_cond, __LINE__);
> + else
> + join->outer_ref_cond= new_cond;
> + }
> + else
> + add_cond_and_fix(join->thd, tab->first_inner->on_expr_ref, notnull);
> + }
> }
> }
> }
BR
Sergei
--
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
