Bug #52344 Subquery materialization: Assertion if subquery in on-clause of outer join
Submitted: 24 Mar 2010 20:36 Modified: 23 Nov 2010 3:21
Reporter: Patrick Crews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to: Øystein Grøvlen CPU Architecture:Any
Tags: materialization, optimizer_switch, regression, subquery

[24 Mar 2010 20:36] Patrick Crews
Description:
Crash / assertion failing in 6.0-codebase-bugfixing.
Not present in 5.1-bugteam.
Tied to optimizer_switch's materialization switch.  This crash occurs with the switch set to 'on', but disappears when set to 'off'

Appears to be a resurfacing of:
Bug#36133	Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION &&

Caused by this query:
SELECT table1 .`col_varchar_nokey`  
FROM C table1  STRAIGHT_JOIN C table2  ON table2 .`col_int_key`  
WHERE ( table1 .`col_varchar_nokey`  , table2 .`col_varchar_nokey`  )  IN (  
SELECT SUBQUERY2_t1 .`col_varchar_nokey`  , SUBQUERY2_t1 .`col_varchar_nokey`  
FROM B SUBQUERY2_t1  LEFT  JOIN BB  ON SUBQUERY2_t1 .`pk`  AND (  3  ,  7  )  IN (  
SELECT `col_int_nokey`  ,  MIN( `pk`  )  
FROM CC  )  )   ;

Producing this output (full output attached as separate file due to space limitations):
Thread 1 (Thread 7678):
#0  0x002a0422 in __kernel_vsyscall ()
#1  0x006e9e93 in __pthread_kill (threadid=3012171632, signo=6) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:64
#2  0x08d1f00f in my_write_core (sig=6) at stacktrace.c:328
#3  0x08422222 in handle_segfault (sig=6) at mysqld.cc:2843
#4  <signal handler called>
#5  0x002a0422 in __kernel_vsyscall ()
#6  0x0082c4d1 in *__GI_raise (sig=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
#7  0x0082f932 in *__GI_abort () at abort.c:92
#8  0x00825648 in *__GI___assert_fail (
    assertion=0x8fd6afc "exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION && engine->engine_type() == subselect_engine::HASH_SJ_ENGINE)", file=0x8fd69ee "item_subselect.cc", line=303, function=0x8fd7ca0 "virtual bool Item_in_subselect::exec()") at assert.c:81
#9  0x0836fbb2 in Item_in_subselect::exec (this=0xb0a0fb0) at item_subselect.cc:303
#10 0x08373802 in Item_in_subselect::val_bool (this=0xb0a0fb0) at item_subselect.cc:929
#11 0x0827ea8c in Item::val_bool_result (this=0xb0a0fb0) at item.h:846
#12 0x082e70ac in Item_in_optimizer::val_int (this=0xb0b2730) at item_cmpfunc.cc:1825
#13 0x0824d19d in Item::val_bool (this=0xb0b2730) at item.cc:184
#14 0x082f7edf in Item_cond_and::val_int (this=0xb0a10a0) at item_cmpfunc.cc:4491
#15 0x08585a1c in join_read_const_table (tab=0xb0b47cc, pos=0xb0aafd0) at sql_select.cc:17109
#16 0x0854e362 in make_join_statistics (join=0xb0a88f0, tables_arg=0xb09f9a0, conds=0xb0b31d8, keyuse_array=0xb0ad61c) at sql_select.cc:4374
#17 0x0853f303 in JOIN::optimize (this=0xb0a88f0) at sql_select.cc:1662
#18 0x0837abdb in subselect_single_select_engine::exec (this=0xb0a16b0) at item_subselect.cc:2241
#19 0x0836fa0d in Item_subselect::exec (this=0xb0a15e8) at item_subselect.cc:283
#20 0x0836fd99 in Item_in_subselect::exec (this=0xb0a15e8) at item_subselect.cc:333
#21 0x08373802 in Item_in_subselect::val_bool (this=0xb0a15e8) at item_subselect.cc:929
#22 0x0827ea8c in Item::val_bool_result (this=0xb0a15e8) at item.h:846
#23 0x082e70ac in Item_in_optimizer::val_int (this=0xb0b2af8) at item_cmpfunc.cc:1825
#24 0x0824d19d in Item::val_bool (this=0xb0b2af8) at item.cc:184
#25 0x082f7edf in Item_cond_and::val_int (this=0xb0b4008) at item_cmpfunc.cc:4491
#26 0x084cc927 in SQL_SELECT::skip_record (this=0xb0b40c0) at opt_range.h:763
#27 0x084cdf40 in JOIN_CACHE::check_match (this=0xb0b4490, 
    rec_ptr=0xb3850020 "\361\001w\360\001m\360\001m\360\001k\362\001r\360\001t\360\001j\360\001u\361\001h\360\001o\375\360\001k\360\001e\360\001n\360\001t\360\001c\360\001m\360\001y\361\001f\360\001d") at sql_join_cache.cc:1971
#28 0x084c8c8b in JOIN_CACHE::generate_full_extensions (this=0xb0b4490, 
    rec_ptr=0xb3850020 "\361\001w\360\001m\360\001m\360\001k\362\001r\360\001t\360\001j\360\001u\361\001h\360\001o\375\360\001k\360\001e\360\001n\360\001t\360\001c\360\001m\360\001y\361\001f\360\001d") at sql_join_cache.cc:1921
#29 0x084c8929 in JOIN_CACHE_BNL::join_matching_records (this=0xb0b4490, skip_last=false) at sql_join_cache.cc:1820
#30 0x084c7d2c in JOIN_CACHE::join_records (this=0xb0b4490, skip_last=false) at sql_join_cache.cc:1627
#31 0x0858303f in sub_select_cache (join=0xb0a3a90, join_tab=0xb0b3cfc, end_of_records=true) at sql_select.cc:16405
#32 0x085834ad in sub_select (join=0xb0a3a90, join_tab=0xb0b3b40, end_of_records=true) at sql_select.cc:16568
#33 0x08582127 in do_select (join=0xb0a3a90, fields=0xafeb5c8, table=0x0, procedure=0x0) at sql_select.cc:16159
#34 0x085483c7 in JOIN::exec (this=0xb0a3a90) at sql_select.cc:2987
#35 0x085490d1 in mysql_select (thd=0xafea028, rref_pointer_array=0xafeb638, tables=0xaf1df20, wild_num=0, fields=..., conds=0xb0a15e8, 
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0xb0a1750, unit=0xafeb074, 
    select_lex=0xafeb534) at sql_select.cc:3184
#36 0x0853944d in handle_select (thd=0xafea028, lex=0xafeb018, result=0xb0a1750, setup_tables_done_option=0) at sql_select.cc:304
#37 0x08459ca5 in execute_sqlcom_select (thd=0xafea028, all_tables=0xaf1df20) at sql_parse.cc:5032
#38 0x08448b0c in mysql_execute_command (thd=0xafea028) at sql_parse.cc:2295
#39 0x0845dd5a in mysql_parse (thd=0xafea028, 
    inBuf=0xaf1da88 "SELECT table1 .`col_varchar_nokey`  \nFROM C table1  STRAIGHT_JOIN C table2  ON table2 .`col_int_key`  \nWHERE ( table1 .`col_varchar_nokey`  , table2 .`col_varchar_nokey`  )  IN (  \nSELECT SUBQUERY2_t1"..., length=401, found_semicolon=0xb38a0910)
    at sql_parse.cc:6060
#40 0x084439ad in dispatch_command (command=COM_QUERY, thd=0xafea028, packet=0xafb8eb1 "", packet_length=404) at sql_parse.cc:1091
#41 0x084421c7 in do_command (thd=0xafea028) at sql_parse.cc:775
#42 0x0843e551 in do_handle_one_connection (thd_arg=0xafea028) at sql_connect.cc:1173
#43 0x0843e238 in handle_one_connection (arg=0xafea028) at sql_connect.cc:1113
#44 0x08e7a303 in pfs_spawn_thread (arg=0xb02b688) at pfs.cc:1011
#45 0x006e480e in start_thread (arg=0xb38a1770) at pthread_create.c:300
#46 0x008ce8de in clone () at ../sysdeps/unix/sysv/linux/i386/clone.S:130

How to repeat:
Simplified MTR test case (full test case with original and simplified queries attached as separate file)
Toggle the materialization setting to observe the bug come and go (crash=on, pass=off)

#/* Server0: MySQL 6.0.14-alpha-gcov-debug-log */

/*!50400 SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on' */;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
DROP TABLE /*! IF EXISTS */ BB;
DROP TABLE /*! IF EXISTS */ B;
--enable_warnings

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,7,8,'v','v');
INSERT INTO `CC` VALUES (11,1,9,'r','r');
INSERT INTO `CC` VALUES (12,5,9,'a','a');
INSERT INTO `CC` VALUES (13,3,186,'m','m');
INSERT INTO `CC` VALUES (14,6,NULL,'y','y');
INSERT INTO `CC` VALUES (15,92,2,'j','j');
INSERT INTO `CC` VALUES (16,7,3,'d','d');
INSERT INTO `CC` VALUES (17,NULL,0,'z','z');
INSERT INTO `CC` VALUES (18,3,133,'e','e');
INSERT INTO `CC` VALUES (19,5,1,'h','h');
INSERT INTO `CC` VALUES (20,1,8,'b','b');
INSERT INTO `CC` VALUES (21,2,5,'s','s');
INSERT INTO `CC` VALUES (22,NULL,5,'e','e');
INSERT INTO `CC` VALUES (23,1,8,'j','j');
INSERT INTO `CC` VALUES (24,0,6,'e','e');
INSERT INTO `CC` VALUES (25,210,51,'f','f');
INSERT INTO `CC` VALUES (26,8,4,'v','v');
INSERT INTO `CC` VALUES (27,7,7,'x','x');
INSERT INTO `CC` VALUES (28,5,6,'m','m');
INSERT INTO `CC` VALUES (29,NULL,4,'c','c');
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL,2,'w','w');
INSERT INTO `C` VALUES (2,7,9,'m','m');
INSERT INTO `C` VALUES (3,9,3,'m','m');
INSERT INTO `C` VALUES (4,7,9,'k','k');
INSERT INTO `C` VALUES (5,4,NULL,'r','r');
INSERT INTO `C` VALUES (6,2,9,'t','t');
INSERT INTO `C` VALUES (7,6,3,'j','j');
INSERT INTO `C` VALUES (8,8,8,'u','u');
INSERT INTO `C` VALUES (9,NULL,8,'h','h');
INSERT INTO `C` VALUES (10,5,53,'o','o');
INSERT INTO `C` VALUES (11,NULL,0,NULL,NULL);
INSERT INTO `C` VALUES (12,6,5,'k','k');
INSERT INTO `C` VALUES (13,188,166,'e','e');
INSERT INTO `C` VALUES (14,2,3,'n','n');
INSERT INTO `C` VALUES (15,1,0,'t','t');
INSERT INTO `C` VALUES (16,1,1,'c','c');
INSERT INTO `C` VALUES (17,0,9,'m','m');
INSERT INTO `C` VALUES (18,9,5,'y','y');
INSERT INTO `C` VALUES (19,NULL,6,'f','f');
INSERT INTO `C` VALUES (20,4,2,'d','d');
CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,8,NULL,NULL);
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,1,7,'f','f');

 
SELECT table1 .`col_varchar_nokey`  
FROM C table1  STRAIGHT_JOIN C table2  ON table2 .`col_int_key`  
WHERE ( table1 .`col_varchar_nokey`  , table2 .`col_varchar_nokey`  )  IN (  
SELECT SUBQUERY2_t1 .`col_varchar_nokey`  , SUBQUERY2_t1 .`col_varchar_nokey`  
FROM B SUBQUERY2_t1  LEFT  JOIN BB  ON SUBQUERY2_t1 .`pk`  AND (  3  ,  7  )  IN (  
SELECT `col_int_nokey`  ,  MIN( `pk`  )  
FROM CC  )  )   ;

DROP TABLE CC;
DROP TABLE C;
DROP TABLE BB;
DROP TABLE B;
#/* End of test case for query 0 */
[24 Mar 2010 20:37] Patrick Crews
full MTR test case with original and simplified queries

Attachment: bug52344_test.txt (text/plain), 9.35 KiB.

[24 Mar 2010 20:38] Patrick Crews
full crash output

Attachment: bug52344_backtrace.txt (text/plain), 16.90 KiB.

[25 Mar 2010 11:58] Jørgen Løland
Looks similar to BUG#46680, i.e., it would be that JOIN::optimize exited without calling setup_subquery_materialization() after deciding that the materialization strategy should be used
[26 Apr 2010 20:20] Guilhem Bichot
The final crashing SELECT of "how-to-repeat" can be simplified to
SELECT 1
FROM dual
WHERE 1  IN (  
SELECT B.`col_varchar_nokey`    
FROM B  LEFT JOIN BB  ON (  7  )  IN (SELECT MIN( `pk`  ) FROM CC )  )   ;
What happens is that:
- in top query's JOIN::prepare(), JOIN::prepare() is called for the IN(...) predicate above, and this predicate is marked with exec_method=MATERIALIZATION
- later, top query's JOIN::optimize() runs and:
- BB is recognized as a const table
- join_read_const_table() reads it and does:
  if (*tab->on_expr_ref && !table->null_row)
  {
    ...
    if ((table->null_row= test((*tab->on_expr_ref)->val_int() == 0)))
      mark_as_null_row(table);  
i.e. it evaluates the ON condition, to see whether this condition is false in which case this table could be replaced with NULL. The evaluation of ON... causes execution of the subquery inside it. All this happens before setup_subquery_materialization(), hence the crash.
[29 Apr 2010 7:54] Øystein Grøvlen
Below is a minimal test case.  Note that this is run with semijoin=off.  One can get the same result with semijoin=on, if  subquery contains an aggregate function. (Because semijoin is not used with aggregation). In other words, this
is an issue with subquery materialization.

set optimizer_switch='semijoin=off';

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

SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);

DROP TABLE t1, t2, t3;
[29 Apr 2010 9:55] Øystein Grøvlen
The problem is limited to the case where both tables of the outer join is 
const tables.  In that case, one will try to evaluate the on-clause during
optimization.  (See join_read_const_table() which is called from make_join_statistics()).  At that point, it has been decided that the subquery
should be materialized, but the materialization has not yet been set up. Hence, the assert.

In this scenario, there is really no point in doing materialization since the
subquery will only be needed once anyway.  Hence, the solution is probably to find some way to tell subquery not to use materialization when it is executed during optimization.
[28 May 2010 6:22] John Embretsen
This issue is also observed during RQG testing (optimizer_subquery.yy grammar) for the mysql-next-mr-opt-backporting branch, may 2010.
[26 Jul 2010 12:11] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114340

3217 Oystein Grovlen	2010-07-26
      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: If a subquery is executed before subquery
      materialization is properly set up, revert to traditional
      execution of this subquery.
     @ 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/item_subselect.cc
        If subquery materialization is to be used, but the proper
        execution engine has not been set up when the subquery is
        executed, revert to traditional exeuction based on IN=>EXISTS
        transformation.
        
        Moved the code to revert to IN=>EXISTS transformation to
        a new method, revert_to_exists_transformation(), in order to
        be able to re-use this code for the scenario in this bug.
     @ sql/item_subselect.h
        Added new method revert_to_exists_transformation() to be used
        when it is detected that one has to revert the decision to do
        subquery materialization for IN-subquery.  IN=>EXISTS
        transformation will be used for the query instead.
[27 Jul 2010 11:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114430

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.)
[3 Aug 2010 11:46] Øystein Grøvlen
I am "re-proposing" the latest committed patch
[17 Aug 2010 11:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/115924

3227 Oystein Grovlen	2010-08-17
      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.)
[17 Aug 2010 11:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/115928

3227 Oystein Grovlen	2010-08-17
      BUG#52344 - Subquery materialization:
                  Assertion if subquery in on-clause of outer join
      
      Problem: If tables of an outer join are 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.)
[17 Aug 2010 13:17] Øystein Grøvlen
Pushed into mysql-next-mr-opt-backporting with revision id oystein.grovlen@oracle.com-20100817115345-erpngrr8v0yxpt65
[8 Sep 2010 7:45] Timour Katchaounov
The patch looks OK to me.
[8 Sep 2010 8:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117753

3238 Oystein Grovlen	2010-09-08
      BUG#52344 - Subquery materialization:
                  Assertion if subquery in on-clause of outer join
      
      Problem: If tables of an outer join are 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.)
[8 Sep 2010 8:30] Øystein Grøvlen
Seems I had forgotten to push.  It is now pushed to mysql-next-mr-opt-backporting with revision id oystein.grovlen@oracle.com-20100908081903-ny1hot0nd2tk749w
[2 Oct 2010 18:15] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101002181053-6iotvl26uurcoryp) (version source revid:alexander.nozdrin@oracle.com-20101002180917-h0n62akupm3z20nt) (pib:21)
[11 Nov 2010 14:48] Timour Katchaounov
After some analysis of a related problem, I believe that this patch
is a bad idea in the long run, because once the meaning of is_expensive
changes (e.g. different kinds of expressions become expensive), then
the patch will introduce worse plans. In addition, this patch makes
it hard to introduce non-expensive materialized subqueries.
[12 Nov 2010 15:39] Øystein Grøvlen
Timour,

Note that this patch only makes the handling of on-clauses match how where-clauses has been handled wrt materialized subqueries all the time since WL#1110 was added.  If that was a bad idea in the first place, we might need to do something about that some time, but I am not convinced.

I do not understand your comment "once the meaning of is_expensive changes (e.g. different kinds of expressions become expensive), then the patch will introduce worse plans."  According to the code documentation, the main idea behind is_expensive is to avoid evaluating expressions in the optimization phase. Why would that change if more expressions become expensive?

I recognize that things will break if "non-expensive materialized subqueries" are introduced.  But what would the motivation be for such subqueries? I am not convinced that executing subqueries in the optimization phase will ever be a good idea.
[13 Nov 2010 16:25] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[23 Nov 2010 3:21] Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.
[25 Nov 2010 8:01] Timour Katchaounov
Oystein, what I meant is that if we make is_expensive() more intelligent,
and let it consider the actual cost of executing an expression, then
this would definitely lead to a crashes because suddenly some subqueries
may become cheap. For instance it is definitely cheap to execute subqueries
that would perform only one lookup/read per table, and thus would allow
cheap constant substitution.

On the other hand, not allowing subquery execution during optimization at
all removes one dependency from the optimizer, thus making it less complex.

I agree that the patch makes things in sync with how we currently process
the WHERE clause. Given we have adequate test cases, if someone ever
decides to make is_expensive based on actual cost, then these test cases
will immediately break, so such a change cannot creep unnoticed.

So I will implement the same solution for now :).