MySQL Bugs: #48073: Subquery on char columns from view crashes Mysql

Bug #48073	Subquery on char columns from view crashes Mysql
Submitted:	15 Oct 2009 10:14	Modified:	22 Nov 2010 0:34
Reporter:	Harald Groven	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	6.0.11, 6.0.14	OS:	MacOS
Assigned to:	Tor Didriksen	CPU Architecture:	Any
Tags:	optimizer_switch, semijoin, subquery

Description:
WHERE col IN (SELECT col FROM view) crashes mysql

Works fine if join-condition in subquery is an INT, but crashes Mysql if VARCHAR

How to repeat:
-- view listing coutrines beginning with letter A
CREATE OR REPLACE VIEW sakila.v_countriesstartingwith_a AS 
SELECT country_id, country 
FROM sakila.country
WHERE LEFT(country,1) = "A" 
; 

-- this works fine.... 
SELECT city_id, city, country_id
FROM sakila.city
WHERE country_id IN (
	SELECT country_id 
	FROM sakila.v_countriesstartingwith_a
) ;

-- but the following query crashes mysql! 

-- find countries starting with A 
-- with name of capital with same same as country
SELECT 
	city_id, 
	city, 
	country_id
FROM sakila.city
WHERE city IN (
	SELECT country 
	FROM sakila.v_countriesstartingwith_a
);

SELECT VERSION();
6.0.11-alpha-log

Not repeatable with latest 5.1.41 from bzr on Linux.

Verified just as described with recent mysql-6.0-codebase (6.0.14) from bzr:

77-52-242-160:6.0-codebase openxs$ bin/mysql -uroot test <sakila-db/sakila-schema.sql 
77-52-242-160:6.0-codebase openxs$ bin/mysql -uroot test <sakila-db/sakila-data.sql 
77-52-242-160:6.0-codebase openxs$ bin/mysql -uroot sakilaReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 6.0.14-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE OR REPLACE VIEW sakila.v_countriesstartingwith_a AS 
    -> SELECT country_id, country 
    -> FROM sakila.country
    -> WHERE LEFT(country,1) = "A" 
    -> ; 
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT 
    -> city_id, 
    -> city, 
    -> country_id
    -> FROM sakila.city
    -> WHERE city IN (
    -> SELECT country 
    -> FROM sakila.v_countriesstartingwith_a
    -> );
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 
mysql> 091015 19:50:55 mysqld_safe mysqld restarted

Stack trace is:

0   mysqld                              0x005d0f09 my_print_stacktrace + 45
1   mysqld                              0x001014f9 handle_segfault + 915
2   libSystem.B.dylib                   0x940472bb _sigtramp + 43
3   ???                                 0xffffffff 0x0 + 4294967295
4   mysqld                              0x0019a832 _Z15find_item_equalP10COND_EQUALP5FieldPb + 60
5   mysqld                              0x001a93ad _Z24setup_sj_materializationP13st_join_table + 1983
6   mysqld                              0x001a9761 _Z24setup_sj_materializationP13st_join_table + 2931
7   mysqld                              0x001be807 _ZN4JOIN8optimizeEv + 7779
8   mysqld                              0x001c59bd _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex + 707
9   mysqld                              0x001c5ddb _Z13handle_selectP3THDP3LEXP13select_resultm + 563
10  mysqld                              0x001147c2 _Z20prepare_schema_tableP3THDP3LEXP11Table_ident18enum_schema_tables + 2124
11  mysqld                              0x0011b1f9 _Z21mysql_execute_commandP3THD + 3167
12  mysqld                              0x00124f12 _Z11mysql_parseP3THDPKcjPS2_ + 724
13  mysqld                              0x00125b80 _Z16dispatch_command19enum_server_commandP3THDPcj + 2708
14  mysqld                              0x0012707e _Z10do_commandP3THD + 632
15  mysqld                              0x00111f6b handle_one_connection + 443
16  libSystem.B.dylib                   0x9400c095 _pthread_start + 321
17  libSystem.B.dylib                   0x9400bf52 thread_start + 34

So, this is definitely related to semijoin optimization:

mysql> set session optimizer_switch="semijoin=off";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT  city_id,  city,  country_id FROM sakila.city WHERE city IN ( SELECT country  FROM sakila.v_countriesstartingwith_a );
Empty set (0.01 sec)

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

3728 Tor Didriksen	2009-11-23
      Bug #48073  	Subquery on char columns from view crashes Mysql
      
      Server crashed because of a wrong C-style cast.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/sql_select.cc
        When executing the view-based query, we actually iterate through an 
        object which is an Item_ref, rather than an Item_field.
        Ask the item for its real_item() first, and then do the down-cast.

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

3728 Tor Didriksen	2009-11-24
      Bug #48073  	Subquery on char columns from view crashes Mysql
      
      Server crashed because of a wrong C-style cast.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/sql_select.cc
        When executing the view-based query, we actually iterate through an 
        object which is an Item_ref, rather than an Item_field.
        Ask the item for its real_item() first, and then do the down-cast.

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

3729 Tor Didriksen	2009-11-24
      Bug #48073  	Subquery on char columns from view crashes Mysql
      
      Server crashed because of a wrong C-style cast.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/sql_select.cc
        When executing the view-based query, we actually iterate through an 
        object which is an Item_ref, rather than an Item_field.
        Ask the item for its real_item() first, and then do the down-cast.

See also Bug#49097

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

3734 Tor Didriksen	2009-11-24
      Bug #48073  	Subquery on char columns from view crashes Mysql
      
      Server crashed because of a wrong C-style cast.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/sql_select.cc
        When executing the view-based query, we actually iterate through an 
        object which is an Item_ref, rather than an Item_field.
        Ask the item for its real_item() first, and then do the down-cast.

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

3738 Tor Didriksen	2009-11-24
      Bug #48073  	Subquery on char columns from view crashes Mysql
      
      Server crashed because of a wrong C-style cast.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/sql_select.cc
        When executing the view-based query, we actually iterate through an 
        object which is an Item_ref, rather than an Item_field.
        Ask the item for its real_item() first, and then do the down-cast.

pushed to
bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-codebase-bugfixing/

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091211055901-yp18b3c7xuhl87rf) (version source revid:alik@sun.com-20091211055401-43rjwq7gjed6ds83) (merge vers: 6.0.14-alpha) (pib:13)

Noted in 6.0.14 changelog.

Use of a view that selects from character columns in a subquery could
crash the server.

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

3074 Tor Didriksen	2010-04-15
      Bug #48073        Subquery on char columns from view crashes Mysql
      
      Backport of tor.didriksen@sun.com-20091124163106-0xjh4yghtad88n4u
      
      Server crashed because of a wrong C-style cast.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/sql_select.cc
        When executing the view-based query, we actually iterate through an 
        object which is an Item_ref, rather than an Item_field.
        Ask the item for its real_item() first, and then do the down-cast.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

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)

Noted in 5.6.1 changelog.

Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.