Bug #44793 Character sets: case clause, ucs2 or utf32, failure
Submitted: 11 May 2009 15:16 Modified: 6 Apr 2011 17:43
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S1 (Critical)
Version:5.5.8-debug, 6.0.12-alpha OS:Any (SUSE 11 64-bit)
Assigned to: Alexander Barkov CPU Architecture:Any

[11 May 2009 15:16] Peter Gulutzan
Description:
Expressions like column1 = 'a',
where column1 is UCS2 or UTF32,
don't return TRUE in CASE clauses.

How to repeat:
drop table if exists t;
create table t (s1 char(5) character set utf32);
insert into t values ('a');
select * from t where s1 = 'a'; /* works */
select case s1 when 'a' then 'b' else 'c' end from t; /* fails */

Example run:
 
mysql> drop table if exists t;
Query OK, 0 rows affected (0.42 sec)

mysql> create table t (s1 char(5) character set utf32);
Query OK, 0 rows affected (0.23 sec)                   

mysql> insert into t values ('a');
Query OK, 1 row affected (0.05 sec)

mysql> select * from t where s1 = 'a'; /* works */
+------+
| s1   |
+------+
| a    |
+------+
1 row in set (0.08 sec)

mysql> select case s1 when 'a' then 'b' else 'c' end from t; /* fails */
+----------------------------------------+
| case s1 when 'a' then 'b' else 'c' end |
+----------------------------------------+
| c                                      |
+----------------------------------------+
1 row in set (0.04 sec)
[11 May 2009 16:02] MySQL Verification Team
Thank you for the bug report. I got a server crash instead with latest source server. Have you tested with latest source?. Thanks in advance.

miguel@hegel:~/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.12-alpha-debug Source distribution

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

mysql> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t (s1 char(5) character set utf32);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t values ('a');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t where s1 = 'a'; /* works */
+------+
| s1   |
+------+
| a    |
+------+
1 row in set (0.01 sec)

mysql> select case s1 when 'a' then 'b' else 'c' end from t; /* fails */
ERROR 2013 (HY000): Lost connection to MySQL server during query

090511 12:55:30 [Note] 6.0/libexec/mysqld: ready for connections.               
Version: '6.0.12-alpha-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld: ctype-ucs2.c:2044: my_strnncollsp_utf32: Assertion `(tlen % 4) == 0' failed.     
090511 12:55:40 - 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=8384512
read_buffer_size=131072
max_used_connections=1 
max_threads=151        
thread_count=1         
connection_count=1     
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338446 K
bytes of memory                                                               
Hope that's ok; if not, decrease some variables in the equation.              

thd: 0x47bf518
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 = 0x7fa705db80f0 thread_stack 0x40000                     
6.0/libexec/mysqld(my_print_stacktrace+0x32) [0xdf583c]                
6.0/libexec/mysqld(handle_segfault+0x2a6) [0x771470]                   
/lib/libpthread.so.0 [0x7fa7055c2080]                                  
/lib/libc.so.6(gsignal+0x35) [0x7fa704475fb5]                          
/lib/libc.so.6(abort+0x183) [0x7fa704477bc3]                           
/lib/libc.so.6(__assert_fail+0xe9) [0x7fa70446ef09]                    
6.0/libexec/mysqld [0xe753be]                                          
6.0/libexec/mysqld(sortcmp(String const*, String const*, charset_info_st*)+0x6f) [0x7686bb]
6.0/libexec/mysqld(cmp_item_sort_string::cmp(Item*)+0xa6) [0x6eba5a]
6.0/libexec/mysqld(Item_func_case::find_item(String*)+0x2d1) [0x6de9f7]
6.0/libexec/mysqld(Item_func_case::val_str(String*)+0x42) [0x6deaf0]
6.0/libexec/mysqld(Item::send(Protocol*, String*)+0x8b) [0x691d09]
6.0/libexec/mysqld(Protocol::send_result_set_row(List<Item>*)+0xb8) [0x761f2a]
6.0/libexec/mysqld(select_send::send_data(List<Item>&)+0xae) [0x7576e6]
6.0/libexec/mysqld [0x7f57ef]
6.0/libexec/mysqld [0x80ee54]
6.0/libexec/mysqld(JOIN::exec()+0x2401) [0x828f45]
6.0/libexec/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x319) [0x82391f]
6.0/libexec/mysqld(handle_select(THD*, LEX*, select_result*, unsigned long)+0x1de) [0x829263]
6.0/libexec/mysqld [0x7830a4]
6.0/libexec/mysqld(mysql_execute_command(THD*)+0x9c3) [0x784999]
6.0/libexec/mysqld(mysql_parse(THD*, char const*, unsigned int, char const**)+0x276) [0x78cf2b]
6.0/libexec/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xa65) [0x78db24]
6.0/libexec/mysqld(do_command(THD*)+0x22b) [0x78f0ab]
6.0/libexec/mysqld(handle_one_connection+0x11c) [0x77b3d5]
/lib/libpthread.so.0 [0x7fa7055ba3ba]
/lib/libc.so.6(clone+0x6d) [0x7fa704528fcd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x480bdb0 = select case s1 when 'a' then 'b' else 'c' end from t
thd->thread_id=1
thd->killed=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.
miguel@hegel:~/dbs$

miguel@hegel:~/bzr/mysql-6.0$ bzr revno
3182
miguel@hegel:~/bzr/mysql-6.0$ cat /etc/issue
Ubuntu 9.04 \n \l
[11 May 2009 18:14] Peter Gulutzan
Yes, I tested with the latest source.
I did not get a crash.
[12 May 2009 12:41] MySQL Verification Team
Thank you for the bug report. Verified in release binary and an assertion in debug binary:

c:\dbs>c:\dbs\6.0\bin\mysql -uroot --port=3600 --prompt="mysql 6.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 6.0.12-alpha-Win X64 revno: 3253-log Source distribution

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

mysql 6.0 > use test
Database changed
mysql 6.0 > drop table if exists t;
Query OK, 0 rows affected (0.01 sec)

mysql 6.0 > create table t (s1 char(5) character set utf32);
Query OK, 0 rows affected (0.13 sec)

mysql 6.0 > insert into t values ('a');
Query OK, 1 row affected (0.00 sec)

mysql 6.0 > select * from t where s1 = 'a'; /* works */
+------+
| s1   |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql 6.0 > select case s1 when 'a' then 'b' else 'c' end from t; /* fails */
+----------------------------------------+
| case s1 when 'a' then 'b' else 'c' end |
+----------------------------------------+
| c                                      |
+----------------------------------------+
1 row in set (0.00 sec)

c:\dbs>c:\dbs\6.0\bin\mysql -uroot --port=3600 --prompt="mysql 6.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.12-alpha-Win X64 revno: 3253-debug-log Source distribution

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

mysql 6.0 > use test
Database changed
mysql 6.0 > drop table if exists t;
Query OK, 0 rows affected (0.00 sec)

mysql 6.0 > create table t (s1 char(5) character set utf32);
Query OK, 0 rows affected (0.17 sec)

mysql 6.0 > insert into t values ('a');
Query OK, 1 row affected (0.00 sec)

mysql 6.0 > select * from t where s1 = 'a'; /* works */
+------+
| s1   |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql 6.0 > select case s1 when 'a' then 'b' else 'c' end from t; /* fails */
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql 6.0 >

000000014082F645    mysqld-debug.exe!my_sigabrt_handler()[my_thr_init.c:510]
000000014000BB21    mysqld-debug.exe!raise()[winsig.c:597]
000000014000F5A3    mysqld-debug.exe!__crtMessageWindowA()[dbgrpt.c:434]
000000014001787C    mysqld-debug.exe!_VCrtDbgReportA()[dbgrptt.c:420]
000000014000EFE3    mysqld-debug.exe!_CrtDbgReportV()[dbgrpt.c:301]
000000014000EF7D    mysqld-debug.exe!_CrtDbgReport()[dbgrpt.c:317]
000000014000945B    mysqld-debug.exe!_NMSG_WRITE()[crt0msg.c:197]
00000001400140D7    mysqld-debug.exe!abort()[abort.c:68]
00000001407F3F24    mysqld-debug.exe!_wassert()[assert.c:212]
00000001408A7D97    mysqld-debug.exe!my_strnncollsp_utf32()[ctype-ucs2.c:2044]
0000000140229CF8    mysqld-debug.exe!sortcmp()[sql_string.cc:667]
00000001400C59CC    mysqld-debug.exe!cmp_item_sort_string::cmp()[item_cmpfunc.h:984]
00000001400B74A3    mysqld-debug.exe!Item_func_case::find_item()[item_cmpfunc.cc:2630]
00000001400B7590    mysqld-debug.exe!Item_func_case::val_str()[item_cmpfunc.cc:2643]
00000001401ED174    mysqld-debug.exe!Item::send()[item.cc:5524]
00000001401C6DAA    mysqld-debug.exe!Protocol::send_result_set_row()[protocol.cc:820]
00000001400F8950    mysqld-debug.exe!select_send::send_data()[sql_class.cc:1788]
000000014029877A    mysqld-debug.exe!end_send()[sql_select.cc:17276]
00000001402950AF    mysqld-debug.exe!do_select()[sql_select.cc:15822]
00000001402716FD    mysqld-debug.exe!JOIN::exec()[sql_select.cc:2886]
000000014027202D    mysqld-debug.exe!mysql_select()[sql_select.cc:3069]
0000000140268664    mysqld-debug.exe!handle_select()[sql_select.cc:310]
00000001402149ED    mysqld-debug.exe!execute_sqlcom_select()[sql_parse.cc:4949]
000000014020B529    mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2157]
00000001402171EB    mysqld-debug.exe!mysql_parse()[sql_parse.cc:5964]
0000000140208D4F    mysqld-debug.exe!dispatch_command()[sql_parse.cc:1051]
0000000140208368    mysqld-debug.exe!do_command()[sql_parse.cc:731]
00000001400D4FF6    mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1146]
000000014082EC3B    mysqld-debug.exe!pthread_start()[my_winthread.c:62]
0000000140959775    mysqld-debug.exe!_callthreadstartex()[threadex.c:348]
0000000140959744    mysqld-debug.exe!_threadstartex()[threadex.c:331]
000000007768C3BD    kernel32.dll!BaseThreadInitThunk()
0000000077A24581    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 00000000071B7A98=select case s1 when 'a' then 'b' else 'c' end from t
thd->thread_id=1
[18 Oct 2010 17:35] MySQL Verification Team
still crashes latest 5.5.8-debug ...

Attachment: bug44793_5.5.8-debug_stack.txt (text/plain), 3.91 KiB.

[10 Nov 2010 10:36] 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/123387

3110 Alexander Barkov	2010-11-10
      Bug#44793 Character sets: case clause, ucs2 or utf32, failure
      
      Problem: character set converters where not installed between
      case_operand and when_operands, so comparison might give
      wrong results, or even crash (on DBUG_ASSER in utf32 routines).
      
      Fix: rewrite <simple case> to <searched case> as follows:
      
        CASE case_operand
          WHEN when_operand1 THEN result1
          WHEN when_operand2 THEN result2
          ...
        END
        
        to
        
        CASE
          WHEN case_operand = when_operand1 THEN result1
          WHEN case_operand = when_operand2 THEN result2
          ...
        END
      
      This change fixes a few problems:
      1. Character set converters are now automatically properly installed
      in fix_dec_and_length() for the individual Item_func_case_condition,
      which fixes the reported problem.
      
      2. Collation is now set for pairs (case_operand, when_operandN),
        rather than for all items at once:
          (case_operand, when_operand1,..., when_operandN),
        which makes CASE work more SQL Standard complient
        and helps to resolve "Illegal mix of collations" in more cases.
      
      3. Getting rid of duplicate code in Item_func_case::fix_length_and_dec()
        responsible for aggregating data types (and collations).
        Now it is automatically done for pairs (case_operand, when_opernadN)
        in individual Item_func_case_condition::fix_length_and_dec().
      
      4. Getting rid of heavy code in Item_func_case::find_item() which
        aggregates data types for every pair (case_operand, when_operandN)
        over and over again in every val_xxx() call.
        Now aggregation is fully done at fix_field() time,
        so the code at execution time is much simplier.
      
        In fact, benchmarking shows 15% performance improvement for this test query:
        
        SELECT benchmark(10000000,
        CASE 10 WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 ELSE 5 END);
        
        changed from 3.61 to 3.06 seconds.
      
        Per file comments:
        @mysql-test/r/case.result
        Changing error from "Illegal mix of 3 collations" to
        "Illegal mix of 2 collations", as now aggregation is done on pairs.
      
        @mysql-test/r/ctype_collate.result
        Fixing and adding tests
      
        @mysql-test/r/ctype_utf32.result
        Adding test (from the report)
      
        @mysql-test/t/case.test
        Changing error from "Illegal mix of 3 collations" to
        "Illegal mix of 2 collations", as now aggregation is done on pairs.
      
        @mysql-test/t/ctype_collate.test
        Fixing and adding tests
      
        @mysql-test/t/ctype_utf32.test
        Adding test (from the report)
      
        @sql/item_cmpfunc.h
        - Introducing helper class Item_func_case_condition.
        - Adding more comments how it works.
        - Changing some termilology:
          using SQL Standard terms "case operand" and "when operand" instead of
          "case expression" and "where expression".
        - Removing unnesessary members from Item_func_case.
        - Adding convert_simple_case_to_searched_case() nethod.
      
        @sql/item_cmpfunc.cc
        - Implementing convert_simple_case_to_searched_case() method,
          to install Item_func_case_condition items between 
          case_operand and when_operands.
        - Simplifying Item_func_case::find_item() significantly.
        - Simplifying Item_func_case::fix_length_and_dec() significantly.
        - Minor changes in Item_func_case::print().
          We want it to output CASE in its original format,
          before conversion from <simple case> to <searched case>.
        - Simplifying Item_func_case::cleanup()
[12 Nov 2010 8:24] Alexander Barkov
As a good side effect, row operands nows work:

mysql> select case (1,2) when (1,2) then 1 else 2 end;
+-----------------------------------------+
| case (1,2) when (1,2) then 1 else 2 end |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.00 sec)

Previously it returned error:
mysql> select case (1,2) when (1,2) then 1 else 2 end;
ERROR 1241 (21000): Operand should contain 1 column(s)
[19 Feb 2011 13:36] MySQL Verification Team
still patch pending after 4 months?
[25 Feb 2011 5:18] Alexander Barkov
Shane, I found a big problem in the patch.
It does not cache the CASE switch value any more.
Thinking what can be done.
[6 Apr 2011 17:43] Paul DuBois
Noted in 5.5.11, 5.6.2 changelogs.

When CASE ... WHEN arguments had different character sets, 8-bit
values could be referenced as utf16 or utf32 values, causing an
assertion to be raised. 

CHANGESET - http://lists.mysql.com/commits/132192