MySQL Bugs: #67359: case/when statement with mixed collation makes mysqld crash

Bug #67359	case/when statement with mixed collation makes mysqld crash
Submitted:	24 Oct 2012 13:51	Modified:	24 Oct 2012 16:31
Reporter:	Sandro Souza	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S2 (Serious)
Version:	5.5.13	OS:	Linux (CentOS release 5.4)
Assigned to:		CPU Architecture:	Any

Description:
If you write a stored procedure containing a case/when statement resulting mixed charsets, mysqld crashes after the second consecutive call to that procedure.

How to repeat:
The script below makes mysqld crash.

drop procedure if exists my_crashing_procedure;
CREATE PROCEDURE my_crashing_procedure(p int)
BEGIN
	  select 	   
	    case when p=1 then convert('RESULT 1' using latin1)
	         when p=2 then convert('RESULT 2' using latin1)
	         else  convert('RESULT 3' using utf8)
      end result;	
END;

call my_crashing_procedure(1);
call my_crashing_procedure(1); -- server crashes here

Stack trace:

121024  9:40:26 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=3999997952
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=2
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4236542 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x1961b6b0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x4a3990d0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x39)[0x796d89]
/usr/sbin/mysqld(handle_segfault+0x380)[0x4fd110]
/lib64/libpthread.so.0[0x314a80eb70]
/lib64/libc.so.6(gsignal+0x35)[0x314a030265]
/lib64/libc.so.6(abort+0x110)[0x314a031d10]
/lib64/libc.so.6(__assert_fail+0xf6)[0x314a0296e6]
/usr/sbin/mysqld[0x79efe3]
/usr/sbin/mysqld(_ZN14Item_func_case18fix_length_and_decEv+0xe6)[0x696916]
/usr/sbin/mysqld(_ZN9Item_func10fix_fieldsEP3THDPP4Item+0x19d)[0x6b24ad]
/usr/sbin/mysqld(_ZN14Item_func_case10fix_fieldsEP3THDPP4Item+0x1b)[0x68e64b]
/usr/sbin/mysqld(_Z12setup_fieldsP3THDPP4ItemR4ListIS1_E17enum_mark_columnsPS5_b+0x123)[0x536c63]
/usr/sbin/mysqld(_ZN4JOIN7prepareEPPP4ItemP10TABLE_LISTjS1_jP8st_orderS7_S1_S7_P13st_select_lexP18st_select_lex_unit+0x21d)[0x59f00d]
/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x6c9)[0x5ac759]
/usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x179)[0x5b1fb9]
/usr/sbin/mysqld[0x5703d1]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x226f)[0x5759ff]
/usr/sbin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x1e)[0x73ac6e]
/usr/sbin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x94)[0x73adf4]
/usr/sbin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x13d)[0x73feed]
/usr/sbin/mysqld(_ZN7sp_head7executeEP3THDb+0x658)[0x73df28]
/usr/sbin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x671)[0x73f5d1]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0xa29)[0x5741b9]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x15a)[0x577dea]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1443)[0x579a83]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x337)[0x60d917]
/usr/sbin/mysqld(handle_one_connection+0x54)[0x60d984]
/lib64/libpthread.so.0[0x314a80673d]
/lib64/libc.so.6(clone+0x6d)[0x314a0d44bd]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x196c5180): select
            case when  NAME_CONST('p',1)=1 then convert('RESULT 1' using latin1)
                 when  NAME_CONST('p',1)=2 then convert('RESULT 2' using latin1)
                 else  convert('RESULT 3' using utf8)
      end result
Connection ID (thread ID): 7
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Not reproducible for me with MySQL 5.5.28 64-bit Windows server

DROP PROCEDURE IF EXISTS my_crashing_procedure;

DELIMITER &&

CREATE PROCEDURE my_crashing_procedure(p INT)
BEGIN
	  SELECT 	   
	    CASE WHEN p=1 THEN CONVERT('RESULT 1' USING latin1)
	         WHEN p=2 THEN CONVERT('RESULT 2' USING latin1)
	         ELSE  CONVERT('RESULT 3' USING utf8)
	    END result;
END &&

DELIMITER ;

CALL my_crashing_procedure(1);
CALL my_crashing_procedure(1);
CALL my_crashing_procedure(2);
CALL my_crashing_procedure(2);
CALL my_crashing_procedure(3);
CALL my_crashing_procedure(3);
.. etc. No crash here with neither SET NAMES latin1 nor utf8 for the connection.

Peter (not a MySQL/Oracle person)

Thank you for the bug report. Please try with latest version. Thanks.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use test
Database changed
mysql> delimiter $$
mysql> drop procedure if exists my_crashing_procedure;
    -> CREATE PROCEDURE my_crashing_procedure(p int)
    -> BEGIN
    ->    select
    ->      case when p=1 then convert('RESULT 1' using latin1)
    ->           when p=2 then convert('RESULT 2' using latin1)
    ->           else  convert('RESULT 3' using utf8)
    ->       end result;
    -> END$$
Query OK, 0 rows affected, 1 warning (0.21 sec)

Query OK, 0 rows affected (0.21 sec)

mysql> delimiter ;
mysql> call my_crashing_procedure(1);
+----------+
| result   |
+----------+
| RESULT 1 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> call my_crashing_procedure(1); -- server crashes here
+----------+
| result   |
+----------+
| RESULT 1 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

I upgraded to a newer version and it's working now. 

It looks like this bug is present only on 5.5.13. May I close it?

Thank you for the feedback.