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
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
