| Bug #52160 | crash and inconsistent results when grouping by a function and column | ||
|---|---|---|---|
| Submitted: | 17 Mar 2010 21:24 | Modified: | 14 Dec 2010 20:15 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 5.1+ | OS: | Any |
| Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
[17 Mar 2010 21:31]
MySQL Verification Team
mysql> select count(*) from `t1` group by `f1`(),`a`; ERROR 1062 (23000): Duplicate entry '0' for key 'group_key' mysql> show variables like "version"; +---------------+--------------+ | Variable_name | Value | +---------------+--------------+ | version | 5.1.46-debug | +---------------+--------------+ 1 row in set (0.00 sec) mysql> select count(*) from `t1` group by `f1`(),`a`; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.01 sec) mysql> show variables like "version"; +---------------+--------------+ | Variable_name | Value | +---------------+--------------+ | version | 5.0.91-debug | +---------------+--------------+ 1 row in set (0.00 sec) mysql> show variables like "version"; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | version | 5.6.99-m4-debug | +---------------+-----------------+ 1 row in set (0.00 sec) mysql> select count(*) from `t1` group by `f1`(),`a`; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql>
[18 Mar 2010 16:03]
MySQL Verification Team
Here's another testcase using builtin functions: drop table if exists t1; create table t1(a blob not null)engine=innodb; insert into t1 values (),(); set names utf8; select count(*) from t1 group by convert(`a`,char(1)),time_to_sec(`a`);
[29 Mar 2010 7:28]
MySQL Verification Team
6.0.14 crashes.
[3 Sep 2010 12:17]
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/117512 3498 Gleb Shchepa 2010-09-03 Bug #52160: crash and inconsistent results when grouping by a function and column Grouping by the TIME_TO_SEC function result may cause a server crash or wrong results. TIME_TO_SEC() returns NULL if its argument is invalid (empty string for example). Thus its nullability depends not only on the nullability of its arguments but also on their values. Fixed by (overoptimistically) setting TIME_TO_SEC() to be nullable despite the nullability of its arguments. @ mysql-test/r/func_time.result Test case for bug #52160. @ mysql-test/t/func_time.test Test case for bug #52160. @ sql/item_timefunc.h Bug #52160: crash and inconsistent results when grouping by a function and column Fixed by (overoptimistically) setting TIME_TO_SEC() to be nullable despite the nullability of its arguments.
[3 Sep 2010 12:30]
MySQL Verification Team
hi! original testcase in "how to repeat" didn't use time_to_sec function. is there a fix for that too ?
[3 Sep 2010 15:02]
Gleb Shchepa
Shane, No :-) It seems like you reported two different bugs in one bug page.
[26 Oct 2010 19:08]
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/121965 3547 Gleb Shchepa 2010-10-26 Bug #52160: crash and inconsistent results when grouping by a function and column The bugreport reveals two different bugs about grouping on a function: 1) grouping by the TIME_TO_SEC function result caused a server crash or wrong results and 2) grouping by the function returning a blob caused an unexpected "Duplicate entry" error and wrong result. Details for the 1st bug: TIME_TO_SEC() returns NULL if its argument is invalid (empty string for example). Thus its nullability depends not only on the nullability of its arguments but also on their values. Fixed by (overoptimistically) setting TIME_TO_SEC() to be nullable despite the nullability of its arguments. Details for the 2nd bug: The server is unable to create indices on blobs without explicit blob key part length. However, this fact was ignored for blob function result fields of GROUP BY intermediate tables. Fixed by disabling GROUP BY index creation for blob function result fields like regular blob fields. @ mysql-test/r/func_time.result Test case for bug #52160. @ mysql-test/r/type_blob.result Test case for bug #52160. @ mysql-test/t/func_time.test Test case for bug #52160. @ mysql-test/t/type_blob.test Test case for bug #52160. @ sql/item_timefunc.h Bug #52160: crash and inconsistent results when grouping by a function and column TIME_TO_SEC() returns NULL if its argument is invalid (empty string for example). Thus its nullability depends not only Fixed by (overoptimistically) setting TIME_TO_SEC() to be nullable despite the nullability of its arguments. @ sql/sql_select.cc Bug #52160: crash and inconsistent results when grouping by a function and column The server is unable to create indices on blobs without explicit blob key part length. However, this fact was ignored for blob function result fields of GROUP BY intermediate tables. Fixed by disabling GROUP BY index creation for blob function result fields like regular blob fields.
[1 Nov 2010 2:56]
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/122388 3549 Gleb Shchepa 2010-10-31 Bug #52160: crash and inconsistent results when grouping by a function and column The bugreport reveals two different bugs about grouping on a function: 1) grouping by the TIME_TO_SEC function result caused a server crash or wrong results and 2) grouping by the function returning a blob caused an unexpected "Duplicate entry" error and wrong result. Details for the 1st bug: TIME_TO_SEC() returns NULL if its argument is invalid (empty string for example). Thus its nullability depends not only on the nullability of its arguments but also on their values. Fixed by (overoptimistically) setting TIME_TO_SEC() to be nullable despite the nullability of its arguments. Details for the 2nd bug: The server is unable to create indices on blobs without explicit blob key part length. However, this fact was ignored for blob function result fields of GROUP BY intermediate tables. Fixed by disabling GROUP BY index creation for blob function result fields like regular blob fields. @ mysql-test/r/func_time.result Test case for bug #52160. @ mysql-test/r/type_blob.result Test case for bug #52160. @ mysql-test/t/func_time.test Test case for bug #52160. @ mysql-test/t/type_blob.test Test case for bug #52160. @ sql/item_timefunc.h Bug #52160: crash and inconsistent results when grouping by a function and column TIME_TO_SEC() returns NULL if its argument is invalid (empty string for example). Thus its nullability depends not only Fixed by (overoptimistically) setting TIME_TO_SEC() to be nullable despite the nullability of its arguments. @ sql/sql_select.cc Bug #52160: crash and inconsistent results when grouping by a function and column The server is unable to create indices on blobs without explicit blob key part length. However, this fact was ignored for blob function result fields of GROUP BY intermediate tables. Fixed by disabling GROUP BY index creation for blob function result fields like regular blob fields.
[13 Nov 2010 16:26]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:39]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:alexander.nozdrin@oracle.com-20101113152540-gxro4g0v29l27f5x) (pib:21)
[14 Dec 2010 20:15]
Paul DuBois
Noted in 5.1.54, 5.5.8 changelogs. Grouping by a TIME_TO_SEC() function result could cause a server crash or incorrect results. Grouping by a function returning a BLOB could cause an unexpected "Duplicate entry" error and incorrect result.
[15 Dec 2010 5:50]
Bugs System
Pushed into mysql-5.1 5.1.55 (revid:sunanda.menon@oracle.com-20101215054055-vgwki317xg1wphhh) (version source revid:sunanda.menon@oracle.com-20101215054055-vgwki317xg1wphhh) (merge vers: 5.1.55) (pib:23)
[16 Dec 2010 22:32]
Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)
Description: Version: '5.6.99-m4-debug' socket: '' port: 3306 Source distribution 100317 23:20:05 - mysqld got exception 0xc0000005 ; mysqld.exe!key_unpack()[key.cc:356] mysqld.exe!handler::print_keydup_error()[handler.cc:2601] mysqld.exe!handler::print_error()[handler.cc:2653] mysqld.exe!create_myisam_from_heap()[sql_select.cc:10969] mysqld.exe!end_update()[sql_select.cc:12693] mysqld.exe!evaluate_join_record()[sql_select.cc:11598] mysqld.exe!sub_select()[sql_select.cc:11479] mysqld.exe!do_select()[sql_select.cc:11225] mysqld.exe!JOIN::exec()[sql_select.cc:1899] mysqld.exe!mysql_select()[sql_select.cc:2508] mysqld.exe!handle_select()[sql_select.cc:271] mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4703] mysqld.exe!mysql_execute_command()[sql_parse.cc:2191] mysqld.exe!mysql_parse()[sql_parse.cc:5735] mysqld.exe!dispatch_command()[sql_parse.cc:1024] mysqld.exe!do_command()[sql_parse.cc:710] mysqld.exe!do_handle_one_connection()[sql_connect.cc:1174] mysqld.exe!handle_one_connection()[sql_connect.cc:1113] mysqld.exe!pthread_start()[my_winthread.c:61] mysqld.exe!_callthreadstartex()[threadex.c:348] mysqld.exe!_threadstartex()[threadex.c:331] kernel32.dll!FlsSetValue() 5.6.99 crashes. 5.1.45 gives error: ERROR 1062 (23000): Duplicate entry '0' for key 'group_key' 5.1.90 gives result: count(*): 2 So, which one is correct? How to repeat: drop function if exists `f1`; create function `f1`() returns tinyblob return 1; drop table if exists `t1`; create table `t1`(`a` char(1)charset utf8)engine=myisam; insert into `t1` values ('0'),('0'); select count(*) from `t1` group by `f1`(),`a`;