Bug #44399 crash with statement using TEXT columns, aggregates, GROUP BY, and HAVING
Submitted: 22 Apr 2009 3:40 Modified: 26 Jun 2009 2:21
Reporter: Shane Harrelson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.1.34-community, 5.1.35-bzr OS:Windows (Windows XP SP3)
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: crash, regression

[22 Apr 2009 3:40] Shane Harrelson
Description:
The attached example crashes the mysqld.exe service.  The SQL is a simplified (and contrived) statement from a much more complex SELECT.

How to repeat:
CREATE DATABASE testZ;
USE testZ;
CREATE TABLE tabX(colY TEXT);
INSERT INTO tabX VALUES('iynfj');
SELECT SUM( DISTINCT colY ) FROM tabX GROUP BY colY HAVING colY IN ( AVG( 1 ), 1 + colY );
[22 Apr 2009 4:34] Valeriy Kravchuk
Thank you for the bug report. Verified just as described also with latest 5.1.35 from bzr on Mac OS X:

Assertion failed: (in_item), function val_int, file item_cmpfunc.cc, line 3796.
090422  7:28:47 - mysqld got signal 6 ;

This query works without problems in 5.0.80, so we have a regression bug here.
[12 May 2009 13:59] 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/73822

2874 Georgi Kodinov	2009-05-12
      Bug #44399: crash with statement using TEXT columns, aggregates, GROUP BY, 
        and HAVING
      
      When calculating GROUP BY the server caches some expressions. It does
      that by allocating a string slot (Item_copy_string) and assigning the 
      value of the expression to it. This effectively means that the result
      type of the expression can be changed from whatever it was to a string.
      As this substitution takes place after the compile-time result type 
      calculation for IN but before the run-time type calculations, 
      it causes the type calculations in the IN function done at run time 
      to get unexpected results different from what was prepared at compile time.
      
      In the CASE ... WHEN ... THEN ... statement there was a similar problem
      and it was solved by artificially adding a STRING argument to the matrix
      at compile time, so if any of the arguments of the CASE function changes 
      its type to a string it will still be covered by the information prepared 
      at compile time.
      Extended the CASE fix for cover the IN case.
      An alternative way of fixing this problem is by caching the result type of 
      the arguments at compile time and using the cached information at run time
      instead of re-calculating the result types.
      Preferred the CASE approach for uniformity and fix localization.
     @ mysql-test/r/func_in.result
        Bug #44399: test case
     @ mysql-test/t/func_in.test
        Bug #44399: test case
     @ sql/item_cmpfunc.cc
        Bug #44399: assume at compile time there's an extra string argument
        in the IN function (similar to CASE) to cater for possible string 
        conversions in the process of calculating the GROUP BY/aggregates.
[20 May 2009 8:23] 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/74543

2894 Georgi Kodinov	2009-05-20
      Addendum to bug #44399: add the string type to the mix only of 
      it will actually be used. 
      The result of this procedure is used to determine if there will be
      typecasting needed and if there will be this may prevent index usage.
     @ sql/item_cmpfunc.cc
        Bug #44399: add the string type to the comparison mix only if
        needed.
[20 May 2009 11:23] 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/74587

2895 Georgi Kodinov	2009-05-20
      Revert of the fix for bug #44399 (joro@sun.com-20090512135917-kal1dvtqpqgnj3yc).
[20 May 2009 12:33] 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/74596

2893 Georgi Kodinov	2009-05-12
      Bug #44399: crash with statement using TEXT columns, aggregates, GROUP BY, 
        and HAVING
      
      When calculating GROUP BY the server caches some expressions. It does
      that by allocating a string slot (Item_copy_string) and assigning the 
      value of the expression to it. This effectively means that the result
      type of the expression can be changed from whatever it was to a string.
      As this substitution takes place after the compile-time result type 
      calculation for IN but before the run-time type calculations, 
      it causes the type calculations in the IN function done at run time 
      to get unexpected results different from what was prepared at compile time.
      
      In the CASE ... WHEN ... THEN ... statement there was a similar problem
      and it was solved by artificially adding a STRING argument to the matrix
      at compile time, so if any of the arguments of the CASE function changes 
      its type to a string it will still be covered by the information prepared 
      at compile time.
      Extended the CASE fix for cover the IN case.
      An alternative way of fixing this problem is by caching the result type of 
      the arguments at compile time and using the cached information at run time
      instead of re-calculating the result types.
      Preferred the CASE approach for uniformity and fix localization.
     @ mysql-test/r/func_in.result
        Bug #44399: test case
     @ mysql-test/t/func_in.test
        Bug #44399: test case
     @ sql/item_cmpfunc.cc
        Bug #44399: assume at compile time there's an extra string argument
        in the IN function (similar to CASE) to cater for possible string 
        conversions in the process of calculating the GROUP BY/aggregates.
[21 May 2009 13:54] 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/74706

2899 Georgi Kodinov	2009-05-21
      Bug #44399 : crash with statement using TEXT columns, aggregates, GROUP BY, and HAVING
      
      When calculating GROUP BY the server caches some expressions. It does
      that by allocating a string slot (Item_copy_string) and assigning the 
      value of the expression to it. This effectively means that the result
      type of the expression can be changed from whatever it was to a string.
      As this substitution takes place after the compile-time result type 
      calculation for IN but before the run-time type calculations, 
      it causes the type calculations in the IN function done at run time 
      to get unexpected results different from what was prepared at compile time.
            
      In the CASE ... WHEN ... THEN ... statement there was a similar problem
      and it was solved by artificially adding a STRING argument to the matrix
      at compile time, so if any of the arguments of the CASE function changes 
      its type to a string it will still be covered by the information prepared 
      at compile time.
      
      Fixed by:
      1. removing the special case for CASE ... WHEN
      2. implemented typed caching in GROUP BY.
     @ mysql-test/r/func_in.result
        Bug #44399: test case
     @ mysql-test/t/func_in.test
        Bug #44399: test case
     @ sql/item.cc
        Bug #44399: Implement typed cashing for GROUP BY
     @ sql/item.h
        Bug #44399: Implement typed cashing for GROUP BY
     @ sql/item_cmpfunc.cc
        Bug #44399: remove the special case
     @ sql/sql_select.cc
        Bug #44399: Implement typed cashing for GROUP BY
[22 May 2009 11:47] 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/74781

2899 Georgi Kodinov	2009-05-22
      Bug #44399 : crash with statement using TEXT columns, aggregates, GROUP BY, and
      HAVING
            
      When calculating GROUP BY the server caches some expressions. It does
      that by allocating a string slot (Item_copy_string) and assigning the 
      value of the expression to it. This effectively means that the result
      type of the expression can be changed from whatever it was to a string.
      As this substitution takes place after the compile-time result type 
      calculation for IN but before the run-time type calculations, 
      it causes the type calculations in the IN function done at run time 
      to get unexpected results different from what was prepared at compile time.
                  
      In the CASE ... WHEN ... THEN ... statement there was a similar problem
      and it was solved by artificially adding a STRING argument to the matrix
      at compile time, so if any of the arguments of the CASE function changes 
      its type to a string it will still be covered by the information prepared 
      at compile time.
     @ mysql-test/include/mix1.inc
        Bug #44399: extended the test to cover the different types
     @ mysql-test/r/func_in.result
        Bug #44399: test case
     @ mysql-test/r/innodb_mysql.result
        Bug #44399: extended the test to cover the different types
     @ mysql-test/t/func_in.test
        Bug #44399: test case
     @ sql/item.cc
        Bug #44399: Implement typed cashing for GROUP BY
     @ sql/item.h
        Bug #44399: Implement typed cashing for GROUP BY
     @ sql/item_cmpfunc.cc
        Bug #44399: remove the special case
     @ sql/sql_select.cc
        Bug #44399: Implement typed cashing for GROUP BY
[25 May 2009 7:54] Timour Katchaounov
Approved with minor the comments on IRC.
[25 May 2009 8:01] 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/74869

2899 Georgi Kodinov	2009-05-25
      Bug #44399 : crash with statement using TEXT columns, aggregates, GROUP BY, and
      HAVING
                  
      When calculating GROUP BY the server caches some expressions. It does
      that by allocating a string slot (Item_copy_string) and assigning the 
      value of the expression to it. This effectively means that the result
      type of the expression can be changed from whatever it was to a string.
      As this substitution takes place after the compile-time result type 
      calculation for IN but before the run-time type calculations, 
      it causes the type calculations in the IN function done at run time 
      to get unexpected results different from what was prepared at compile time.
                        
      In the CASE ... WHEN ... THEN ... statement there was a similar problem
      and it was solved by artificially adding a STRING argument to the set of 
      types of the IN/CASE arguments at compile time, so if any of the 
      arguments of the CASE function changes its type to a string it will 
      still be covered by the information prepared at compile time.
     @ mysql-test/include/mix1.inc
        Bug #44399: extended the test to cover the different types
     @ mysql-test/r/func_in.result
        Bug #44399: test case
     @ mysql-test/r/innodb_mysql.result
        Bug #44399: extended the test to cover the different types
     @ mysql-test/t/func_in.test
        Bug #44399: test case
     @ sql/item.cc
        Bug #44399: Implement typed caching for GROUP BY
     @ sql/item.h
        Bug #44399: Implement typed caching for GROUP BY
     @ sql/item_cmpfunc.cc
        Bug #44399: remove the special case
     @ sql/sql_select.cc
        Bug #44399: Implement typed caching for GROUP BY
[25 May 2009 8:04] 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/74870

2908 Georgi Kodinov	2009-05-25
      Bug #44399 : crash with statement using TEXT columns, aggregates, GROUP BY, and
      HAVING
                  
      When calculating GROUP BY the server caches some expressions. It does
      that by allocating a string slot (Item_copy_string) and assigning the 
      value of the expression to it. This effectively means that the result
      type of the expression can be changed from whatever it was to a string.
      As this substitution takes place after the compile-time result type 
      calculation for IN but before the run-time type calculations, 
      it causes the type calculations in the IN function done at run time 
      to get unexpected results different from what was prepared at compile time.
                        
      In the CASE ... WHEN ... THEN ... statement there was a similar problem
      and it was solved by artificially adding a STRING argument to the set of 
      types of the IN/CASE arguments at compile time, so if any of the 
      arguments of the CASE function changes its type to a string it will 
      still be covered by the information prepared at compile time.
     @ mysql-test/include/mix1.inc
        Bug #44399: extended the test to cover the different types
     @ mysql-test/r/func_in.result
        Bug #44399: test case
     @ mysql-test/r/innodb_mysql.result
        Bug #44399: extended the test to cover the different types
     @ mysql-test/t/func_in.test
        Bug #44399: test case
     @ sql/item.cc
        Bug #44399: Implement typed caching for GROUP BY
     @ sql/item.h
        Bug #44399: Implement typed caching for GROUP BY
     @ sql/item_cmpfunc.cc
        Bug #44399: remove the special case
     @ sql/sql_select.cc
        Bug #44399: Implement typed caching for GROUP BY
[28 May 2009 8:16] Bugs System
Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:joro@sun.com-20090525080040-lk10u57xw4wp4bcc) (merge vers: 5.1.36) (pib:6)
[2 Jun 2009 16:48] Paul DuBois
Noted in 5.1.36 changelog.

Caching of GROUP BY expressions could lead to mismatches between 
compile-time and runtime calculations and cause a server crash.

Setting report to NDI pending push into 6.0.x.
[17 Jun 2009 19:26] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:joro@sun.com-20090525082853-9drm8asl8smh9rbq) (merge vers: 6.0.12-alpha) (pib:11)
[26 Jun 2009 2:21] Paul DuBois
Noted in 5.4.4 changelog.
[12 Aug 2009 22:35] Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 1:52] Paul DuBois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48] Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:32] Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[7 Oct 2009 20:25] Paul DuBois
The 5.4 fix has been pushed to 5.4.2.