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