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