Bug #21354 | (COUNT(*) = 1) not working in SELECT inside prepared statement | ||
---|---|---|---|
Submitted: | 30 Jul 2006 23:14 | Modified: | 13 Nov 2006 19:30 |
Reporter: | Nicolai Bloch | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
Version: | 4.1.22, 5.0.25-BK, 5.0.22 | OS: | Any (ALL) |
Assigned to: | Tomash Brechko | CPU Architecture: | Any |
[30 Jul 2006 23:14]
Nicolai Bloch
[30 Jul 2006 23:24]
Michael Rose
I was one of the ones from FreeNode's #mysql who tested it and received 1, 0 as the last result. Here's version numbers: GNU/Linux: 2.6.15-26-386 (x86) MySQL: 5.0.22-Debian_0ubuntu6.06-log
[31 Jul 2006 14:40]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.25-BK on Linux: openxs@suse:~/dbs/5.0> bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.25 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> delimiter | mysql> DROP TABLE IF EXISTS testTbl| Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS testTbl ( -> a INT UNSIGNED NOT NULL PRIMARY KEY -> ) ENGINE = MyISAM| |Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO testTbl (a) VALUES (1), (2), (3)| Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> DROP PROCEDURE IF EXISTS testProc| CREATE PROCEDURE testProc ( Query OK, 0 rows affected (0.09 sec) mysql> CREATE PROCEDURE testProc ( -> i_a INT UNSIGNED -> ) -> BEGIN -> SELECT (COUNT(*) = 1), COUNT(*) FROM testTbl WHERE a = i_a; -> END| Query OK, 0 rows affected (0.01 sec) mysql> call testProc(22)| +----------------+----------+ | (COUNT(*) = 1) | COUNT(*) | +----------------+----------+ | 0 | 0 | +----------------+----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> call testProc(2)| +----------------+----------+ | (COUNT(*) = 1) | COUNT(*) | +----------------+----------+ | 1 | 1 | +----------------+----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> call testProc(33)| +----------------+----------+ | (COUNT(*) = 1) | COUNT(*) | +----------------+----------+ | 1 | 0 | +----------------+----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
[14 Aug 2006 22:05]
Konstantin Osipov
Tomash, this is likely also a 4.1 bug. Please check it there too.
[31 Aug 2006 13: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/11162 ChangeSet@1.2538, 2006-08-31 17:22:42+04:00, kroki@moonlight.intranet +5 -0 BUG#21354: (COUNT(*) = 1) not working in SELECT inside prepared statement. The problem was that during statement re-execution if the result was empty the old result could be returned for group functions. The solution is to implement proper cleanup() method in group functions.
[22 Sep 2006 12:50]
Konstantin Osipov
Approved by email with a few comments.
[10 Oct 2006 13:10]
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/13393 ChangeSet@1.2538, 2006-10-10 17:08:47+04:00, kroki@moonlight.intranet +5 -0 BUG#21354: (COUNT(*) = 1) not working in SELECT inside prepared statement. The problem was that during statement re-execution if the result was empty the old result could be returned for group functions. The solution is to implement proper cleanup() method in group functions.
[10 Oct 2006 18:35]
Tomash Brechko
Queued to 4.1-runtime, 5.0-runtime, 5.1-runtime.
[18 Oct 2006 11:22]
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/13855 ChangeSet@1.2286, 2006-10-18 15:20:34+04:00, kroki@moonlight.intranet +2 -0 Fix for valgrind warning introduced by the fix for bug#21354: (COUNT(*) = 1) not working in SELECT inside prepared statement. Note: the warning was introduced in 5.0 and 5.1, 4.1 is OK with the original fix. The problem was that in 5.0 and 5.1 clear() for group functions may access hybrid_type member, and this member is initialized in fix_fields(). So we should not call clear() from item cleanup() methods, as cleanup() may be called for unfixed items.
[13 Nov 2006 19:30]
Paul DuBois
Noted in 4.1.22, 5.0.30 (not 5.0.29), 5.1.13 changelogs. Within a prepared statement, SELECT (COUNT(*) = 1) (or similar use of other aggregate functions) did not return the correct result for statement re-execution.