List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:August 5 2010 12:34pm
Subject:Re: bzr commit into mysql-next-mr-opt-backporting branch
(oystein.grovlen:3219) Bug#52344
View as plain text  
Hi Øystein,

I think this change is good enough to be pushed.

However, I do question the sanity of the implementation of the subquery 
materialization feature. It is strange that we do algorithm selection so early 
in the query preparation phase, and then keep the Item_in_subselect object in an 
inconsistent state for so long. The inconsistency is cleared when 
setup_subquery_materialization() is called, but the need for this makes 
JOIN::optimize() unnecessarily clumsy.

It would have been better if algorithm selection was done later in the 
preparation phase, or if setup_engine() could be called on-demand in execution.

I realize that some of this is mandated by the "transformation" of the IN 
subquery into an EXISTS execution plan. However, I think that this could be 
better handled by proper code generation that did not involve generating 
additional special Item objects.

This is in contrast to the semijoin transformation which is a true 
transformation and keeps its data structures consistent throughout the query 
preparation phase.

There are a pair of nit-picking comments below, feel free to ignore them if you 
like.

Thanks,
Roy

On 27.07.10 13.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,

is -> are

>        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).

Please break up this line.
> +  */
> +  if (*tab->on_expr_ref&&  !table->null_row&&
> +      !(*tab->on_expr_ref)->is_expensive())
>     {
>       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