Description:
Semi-join subquery in ON clause and no WHERE crashes the server

The problem was introduced by BUG#30622. It can be observerd as a crash in
subselect_sj.test (which is currently disabled).

How to repeat:
Run this:

create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1(a int, b int);
insert into t1 values (0,0),(1,1),(2,2);
create table t2 as select * from t1;

create table t11(a int, b int);

create table t10 (pk int, a int, primary key(pk));
insert into t10 select a,a from t0;
create table t12 like t10;
insert into t12 select * from t10;

explain extended select * from t1 where a in (select t10.pk from t10, t12 where
t12.pk=t10.a);

and observe this crash:
  [Switching to Thread 0xad865b90 (LWP 5995)]
  0x08386619 in replace_where_subcondition (join=0x93c15e0, old_cond=0x93b60e8,
new_cond=0x93b6dc0, do_fix_fields=false) at sql_select.cc:14946
(gdb) p join->conds
  $1 = (COND *) 0x0
(gdb) wher
  #0  0x08386619 in replace_where_subcondition (join=0x93c15e0, old_cond=0x93b60e8,
new_cond=0x93b6dc0, do_fix_fields=false) at sql_select.cc:14946
  #1  0x08399291 in JOIN::flatten_subqueries (this=0x93c15e0) at sql_select.cc:3382
  #2  0x083aed7a in mysql_select (thd=0x93534f0, rref_pointer_array=0x9354a78,
tables=0x93b52c0, wild_num=1, fields=@0x9354a08, conds=0x0, og_num=0, order=0x0,
group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0x93b6540,
unit=0x93546ec, select_lex=0x9354974) at sql_select.cc:2930
  #3  0x083af2df in mysql_explain_union (thd=0x93534f0, unit=0x93546ec, result=0x93b6540)
at sql_select.cc:19005
  #4  0x08327e20 in execute_sqlcom_select (thd=0x93534f0, all_tables=0x93b52c0) at
sql_parse.cc:4743
  #5  0x08329440 in mysql_execute_command (thd=0x93534f0) at sql_parse.cc:1961
  #6  0x08331fa8 in mysql_parse (thd=0x93534f0, inBuf=0x93b50f0 "explain extended\nselect
* from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))", length=95,
found_semicolon=0xad865260) at sql_parse.cc:5643
  #7  0x083329a8 in dispatch_command (command=COM_QUERY, thd=0x93534f0, packet=0x93ad091
"", packet_length=95) at sql_parse.cc:1030
  #8  0x08333b14 in do_command (thd=0x93534f0) at sql_parse.cc:722
  #9  0x08320172 in handle_one_connection (arg=0x93534f0) at sql_connect.cc:1122
  #10 0xb7f7718b in start_thread () from /lib/libpthread.so.0
  #11 0xb7d8309e in clone () from /lib/libc.so.6

Suggested fix:
The replace_where_subcondition should be changed to look into ON clauses also so we can
handle the case where the subquery is in the ON clause.

Thank you for the bug report. I tried with latest source tree and could not
repeat. I tested on FC 6.0 32-bit which one have you tested?.

[miguel@amanhecer dbs]$ 5.1/bin/mysqladmin -uroot shutdown
[miguel@amanhecer dbs]$ 6.0/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.5-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t0 (a int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> 
mysql> create table t1(a int, b int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (0,0),(1,1),(2,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table t2 as select * from t1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> create table t11(a int, b int);
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> create table t10 (pk int, a int, primary key(pk));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t10 select a,a from t0;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> create table t12 like t10;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t12 select * from t10;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> 
mysql> explain extended select * from t1 where a in (select t10.pk from t10, t12 where
    -> t12.pk=t10.a);
+----+-------------+-------+--------+---------------+---------+---------+------------+----
--+----------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        |
rows | filtered | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------+----
--+----------+-------------+
|  1 | PRIMARY     | t1    | ALL    | NULL          | NULL    | NULL    | NULL       |   
3 |   100.00 |             | 
|  1 | PRIMARY     | t10   | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.a  |   
1 |   100.00 |             | 
|  1 | PRIMARY     | t12   | eq_ref | PRIMARY       | PRIMARY | 4       | test.t10.a |   
1 |   100.00 | Using index | 
+----+-------------+-------+--------+---------------+---------+---------+------------+----
--+----------+-------------+
3 rows in set, 1 warning (0.01 sec)

Thanks in advance.

Sorry, pasted the wrong query. Instead of 

explain extended select * from t1 where a in (select t10.pk from t10, t12 where
t12.pk=t10.a);

it should be

explain extended select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from
t10));

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/46254

ChangeSet@1.2628, 2008-05-01 07:53:36+04:00, sergefp@mysql.com +7 -0
  BUG#35550 "Semi-join subquery in ON clause and no WHERE crashes the server"
  The bug was a bunch of interrelated issues caused by failure to do the below:
  - Let Item_in_subselect store the clause (WHERE/ON) where it is located, so 
    we can walk that clause and remove the subselect predicate from there if we
    convert it to a semi-join.
  - Let pull_out_semijoin_tables() use statement's MEM_ROOT when operating on 
    TABLE_LIST structures, as the changes it makes should remain there for 
    subsequent statement re-executions.
  - Let make_join_statistics() do constant table processing for tables that are 
    within a semi-join nest (but not within an outer join nest).

Pushed into 6.0.6-alpha

Noted in 6.0.6 changelog.

A semi-join subquery in the ON clause in the absence of a WHERE
clause caused a server crash.