MySQL Bugs: #13180: server accepts sometimes group in update's where condition

Bug #13180	server accepts sometimes group in update's where condition
Submitted:	14 Sep 2005 13:50	Modified:	23 Sep 2005 18:19
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.14/5.0 BK source	OS:	* / freebsd/Suse 9.3
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
The testcase below has been executed on a just started mysql 4.1.14 server.

Mysql executes the following query with no complains:
 update tbl1 set a=3 where b=4 and count(*) = 2;

The error always occurs, if the database has just been changed. executing the query again without changing the DB will result in the proper error.
Changing the DB, or issuing a "use <current_db>;" allows the sql to be re-executed.

If executed the SQL also gets into the binlog, and will subsequently stop a slave server.

Here the complete sql (How to repeat contains the sql, with server feedback), a db named "xxx" must exist:

use xxx;
create table tbl1 select 1 a, 2 b;
update tbl1 set a=3 where b=4 and count(*) = 2;
update tbl1 set a=3 where b=4 and count(*) = 2;
use xxx;
update tbl1 set a=3 where b=4 and count(*) = 2;
drop table tbl1;
show variables like 'version';

How to repeat:
mysql> use xxx;
No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE ***

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table tbl1 select 1 a, 2 b;
ERROR 1050 (42S01): Table 'tbl1' already exists
mysql> update tbl1 set a=3 where b=4 and count(*) = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update tbl1 set a=3 where b=4 and count(*) = 2;
ERROR 1111 (HY000): Invalid use of group function
mysql> use xxx;
Database changed
mysql> update tbl1 set a=3 where b=4 and count(*) = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> drop table tbl1;
Query OK, 0 rows affected (0.20 sec)

mysql> show variables like 'version';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| version       | 4.1.14-log |
+---------------+------------+
1 row in set (0.01 sec)

Suggested fix:
-

Thank you for the bug report.

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create xxx
miguel@hegel:~/dbs/5.0> bin/mysql -uroot 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.13-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use xxx;
Database changed
mysql> create table tbl1 select 1 a, 2 b;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update tbl1 set a=3 where b=4 and count(*) = 2;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update tbl1 set a=3 where b=4 and count(*) = 2;
ERROR 1111 (HY000): Invalid use of group function
mysql> use xxx;
Database changed
mysql> update tbl1 set a=3 where b=4 and count(*) = 2;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

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/internals/30027

also happens in delete:
delete from table where count(*)=1;

as the patch is against sql_update.cc, I wonder if this will be adressed.

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/internals/30075

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/internals/30171

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/internals/30172

thd->allow_sum_func was left 'true' after previous statement thus allowing
sum funcs to be present in conditions.

Fixed in 4.1.14, cset 1.2442.1.1

Fixed in 5.0.14

Noted in 4.1.15, 5.0.14 changelogs.