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