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:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.1+ OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[17 Mar 2010 21:24] Shane Bester
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`;
[17 Mar 2010 21:31] Miguel Solorzano
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] Shane Bester
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] Shane Bester
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] Shane Bester
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)