MySQL Bugs: #21354: (COUNT(*) = 1) not working in SELECT inside prepared statement

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:	None
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

Description:
The test case pretty much explains the problem.
The result of the 3rd call on my server (as well as a few people on freenode #mysql) is 1, 0 which wouldn't seem to be the expected output.
It should return 0, 0 just like the 1st call.

The behavior was experienced on both Windows 2000 and Debian Linux, so i guess it's platform independent.

I'm using the client that comes with the server.

How to repeat:
DELIMITER |
DROP TABLE IF EXISTS testTbl|
CREATE TABLE IF NOT EXISTS testTbl (
  a INT UNSIGNED NOT NULL PRIMARY KEY
) ENGINE = MyISAM|
INSERT INTO testTbl (a) VALUES (1), (2), (3)|
DROP PROCEDURE IF EXISTS testProc|
CREATE PROCEDURE testProc (
  i_a INT UNSIGNED
)
BEGIN
  SELECT (COUNT(*) = 1), COUNT(*) FROM testTbl WHERE a = i_a;
END|
call testProc(22)| /* 1st call */
call testProc(2)|  /* 2nd call */
call testProc(33)| /* 3rd call */
DROP TABLE testTbl|
DROP PROCEDURE testProc|
DELIMITER ;

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

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)

Tomash, this is likely also a 4.1 bug.
Please check it there too.

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.

Approved by email with a few comments.

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.

Queued to 4.1-runtime, 5.0-runtime, 5.1-runtime.

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.

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.