List:Commits« Previous MessageNext Message »
From:Evgeny Potemkin Date:August 11 2010 3:42pm
Subject:Re: bzr commit into mysql-next-mr-opt-backporting branch
(oystein.grovlen:3219) Bug#52344
View as plain text  
Hi Oystein,

The fix is ok, with small addition. Please, see below.

Regards, Evgen.

On 27/07/10 15:05, Oystein Grovlen wrote:
> #At file:///home/og136792/mysql/mysql-next-mr-opt-backporting/ based on
> revid:epotemkin@stripped
>
>   3219 Oystein Grovlen	2010-07-27
>        BUG#52344 - Subquery materialization:
>                    Assertion if subquery in on-clause of outer join
>
>        Problem: If tables of an outer join is constant tables,
>        the associated on-clause will be evaluated in the optimization
>        phase.  If the on-clause contains a query that is to be
>        executed with subquery materialization, this will not work
>        since the infrastructure for such execution is not yet set up.
>
>        Solution: Do not evaluate on-clause in optimization phase if
>        is_expensive() returns true for this clause.  This is how the
>        problem is currently avoided for where-clauses.  This works
>        because, Item_in_subselect::is_expensive_processor returns true
>        if query is to be executed with subquery materialization.
>       @ mysql-test/include/subquery_mat.inc
>          Added test case for BUG#52344.
>       @ mysql-test/r/subquery_mat.result
>          Updated result file with test case for BUG#52344.
>       @ mysql-test/r/subquery_mat_all.result
>          Updated result file with test case for BUG#52344.
>       @ mysql-test/r/subquery_mat_none.result
>          Updated result file with test case for BUG#52344.
>       @ sql/sql_select.cc
>          Do not evaluate on-clause in optimization phase if
>          is_expensive() returns true for this clause. This prevents
>          executing materialized subqueries in optimization phase.
>          (Proper setup for such execution has not been done at this
>          stage.)
>
>      modified:
>        mysql-test/include/subquery_mat.inc
>        mysql-test/r/subquery_mat.result
>        mysql-test/r/subquery_mat_all.result
>        mysql-test/r/subquery_mat_none.result
>        sql/sql_select.cc
> === modified file 'mysql-test/include/subquery_mat.inc'
> --- a/mysql-test/include/subquery_mat.inc	2010-06-30 06:35:23 +0000
> +++ b/mysql-test/include/subquery_mat.inc	2010-07-27 11:05:20 +0000
> @@ -1008,6 +1008,33 @@ FROM t2);
>   --echo #
>   DROP TABLE IF EXISTS t1,t2,t3,empty1;
>
> +
> +--echo #
> +--echo # BUG#52344 - Subquery materialization:
> +--echo #  	     Assertion if subquery in on-clause of outer join
> +--echo #
> +
> +CREATE TABLE t1 (i INTEGER);
> +INSERT INTO t1 VALUES (10);
> +
> +CREATE TABLE t2 (j INTEGER);
> +INSERT INTO t2 VALUES (5);
> +
> +CREATE TABLE t3 (k INTEGER);
> +
> +EXPLAIN
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
> +
> +EXPLAIN
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
> +
> +DROP TABLE t1, t2, t3;
> +
> +--echo # End BUG#52344
> +
> +
>   #
>   # Bug #52538 Valgrind bug: Item_in_subselect::init_left_expr_cache()
>   #
> @@ -1113,3 +1140,5 @@ DROP TABLE t1,t2,t3;
>
>   --echo # End BUG#54511
>
> +
> +
>
> === modified file 'mysql-test/r/subquery_mat.result'
> --- a/mysql-test/r/subquery_mat.result	2010-06-30 06:35:23 +0000
> +++ b/mysql-test/r/subquery_mat.result	2010-07-27 11:05:20 +0000
> @@ -1355,6 +1355,35 @@ id	select_type	table	type	possible_keys	
>   # Cleanup for BUG#46680
>   #
>   DROP TABLE IF EXISTS t1,t2,t3,empty1;
> +#
> +# BUG#52344 - Subquery materialization:
> +#  	     Assertion if subquery in on-clause of outer join
> +#
> +CREATE TABLE t1 (i INTEGER);
> +INSERT INTO t1 VALUES (10);
> +CREATE TABLE t2 (j INTEGER);
> +INSERT INTO t2 VALUES (5);
> +CREATE TABLE t3 (k INTEGER);
> +EXPLAIN
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
> +1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
> +2	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
> +i
> +10
> +EXPLAIN
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
> +1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
> +2	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
> +i
> +10
> +DROP TABLE t1, t2, t3;
> +# End BUG#52344
>   CREATE TABLE t1 (
>   pk INTEGER AUTO_INCREMENT,
>   col_int_nokey INTEGER,
>
> === modified file 'mysql-test/r/subquery_mat_all.result'
> --- a/mysql-test/r/subquery_mat_all.result	2010-06-30 06:35:23 +0000
> +++ b/mysql-test/r/subquery_mat_all.result	2010-07-27 11:05:20 +0000
> @@ -1354,6 +1354,35 @@ id	select_type	table	type	possible_keys	
>   # Cleanup for BUG#46680
>   #
>   DROP TABLE IF EXISTS t1,t2,t3,empty1;
> +#
> +# BUG#52344 - Subquery materialization:
> +#  	     Assertion if subquery in on-clause of outer join
> +#
> +CREATE TABLE t1 (i INTEGER);
> +INSERT INTO t1 VALUES (10);
> +CREATE TABLE t2 (j INTEGER);
> +INSERT INTO t2 VALUES (5);
> +CREATE TABLE t3 (k INTEGER);
> +EXPLAIN
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
> +1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
> +2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const
> table
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
> +i
> +10
> +EXPLAIN
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
> +1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
> +2	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
> +i
> +10
> +DROP TABLE t1, t2, t3;
> +# End BUG#52344
>   CREATE TABLE t1 (
>   pk INTEGER AUTO_INCREMENT,
>   col_int_nokey INTEGER,
>
> === modified file 'mysql-test/r/subquery_mat_none.result'
> --- a/mysql-test/r/subquery_mat_none.result	2010-07-13 17:29:44 +0000
> +++ b/mysql-test/r/subquery_mat_none.result	2010-07-27 11:05:20 +0000
> @@ -1354,6 +1354,35 @@ id	select_type	table	type	possible_keys	
>   # Cleanup for BUG#46680
>   #
>   DROP TABLE IF EXISTS t1,t2,t3,empty1;
> +#
> +# BUG#52344 - Subquery materialization:
> +#  	     Assertion if subquery in on-clause of outer join
> +#
> +CREATE TABLE t1 (i INTEGER);
> +INSERT INTO t1 VALUES (10);
> +CREATE TABLE t2 (j INTEGER);
> +INSERT INTO t2 VALUES (5);
> +CREATE TABLE t3 (k INTEGER);
> +EXPLAIN
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
> +1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
> +2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const
> table
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
> +i
> +10
> +EXPLAIN
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
> +id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
> +1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
> +1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
> +2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const
> table
> +SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
> +i
> +10
> +DROP TABLE t1, t2, t3;
> +# End BUG#52344
>   CREATE TABLE t1 (
>   pk INTEGER AUTO_INCREMENT,
>   col_int_nokey INTEGER,
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc	2010-07-26 11:34:07 +0000
> +++ b/sql/sql_select.cc	2010-07-27 11:05:20 +0000
> @@ -17517,7 +17517,14 @@ join_read_const_table(JOIN_TAB *tab, POS
>   	DBUG_RETURN(error);
>       }
>     }
> -  if (*tab->on_expr_ref&&  !table->null_row)
> +  /* We will evaluate on-expressions here only if it is not considered
> +     expensive.  This also prevents executing materialized subqueries
> +     in optimization phase.  This is necessary since proper setup for
> +     such execution has not been done at this stage.  (See comment in
> +     internal_remove_eq_conds() tagged DontEvaluateMaterializedSubqueryTooEarly).
> +  */
> +  if (*tab->on_expr_ref&&  !table->null_row&&
> +      !(*tab->on_expr_ref)->is_expensive())
IMO, the proper check would be:

  if (*tab->on_expr_ref&&  !table->null_row &&
      !((*tab->on_expr_ref)->is_expensive() &&
         (*tab->on_expr_ref)->with_subselect)))

The reason is that is_expensive() returns true also for UDF and SP functions.
Using it to detect a subselect is actually abuse. Look:
SELECT ... FROM t1 LEFT JOIN t2 ON an_expensive_sp_func() = 2 ...
And lets the function always return 0. In this case 'impossible where' will be 
found much later and some resource would be wasted.
Same, btw, applies to WHERE clause.

>     {
>       if ((table->null_row= test((*tab->on_expr_ref)->val_int() == 0)))
>         mark_as_null_row(table);
>
>
>
>
>
Thread
bzr commit into mysql-next-mr-opt-backporting branch (oystein.grovlen:3219)Bug#52344Oystein Grovlen27 Jul
Re: bzr commit into mysql-next-mr-opt-backporting branch(oystein.grovlen:3219) Bug#52344Roy Lyseng5 Aug
Re: bzr commit into mysql-next-mr-opt-backporting branch(oystein.grovlen:3219) Bug#52344Evgeny Potemkin11 Aug
  • Re: bzr commit into mysql-next-mr-opt-backporting branch (oystein.grovlen:3219)Bug#52344Øystein Grøvlen16 Aug