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

[14 Sep 2005 13:50] Martin Friebe
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:
-
[14 Sep 2005 14:42] Miguel Solorzano
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
[18 Sep 2005 23:26] 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/internals/30027
[19 Sep 2005 18:38] Martin Friebe
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.
[19 Sep 2005 23:52] 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/internals/30075
[21 Sep 2005 21:32] 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/internals/30171
[21 Sep 2005 21:38] 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/internals/30172
[21 Sep 2005 22:50] Evgeny Potemkin
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
[21 Sep 2005 23:30] Evgeny Potemkin
Fixed in 5.0.14
[23 Sep 2005 18:19] Paul Dubois
Noted in 4.1.15, 5.0.14 changelogs.