Bug #39 Aggregate function inside susbselect works in strange manner.

• Submitted: 25 Jan 2003 16:26
• Modified: 28 Jan 2003 10:56
• Reporter: Alexander Keremidarski
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1
• OS: Any (any)
• CPU Architecture: Any

[25 Jan 2003 16:26] Alexander Keremidarski

Description:
UPDATE t SET i=i+1 WHERE i=(SELECT MAX(i));

is accepted silently (does nothing, but no erroir) and refuses to execute second time. Then after SELECT this behaviour repeats. 

I am not sure what is semantics of (SELECT MAX(i)) here as there is No FROM and if there is:

mysql> UPDATE t SET i=i+1 WHERE i=(SELECT MAX(i) FROM t);
ERROR 1093: You can't specify target table 't' for update in FROM clause

Which is nice error message.
Question is: Why does first UPDATE works in this strange manner?

How to repeat:
mysql> CREATE TABLE `t` (
    ->   `i` int(11) NOT NULL default '0',
    ->   PRIMARY KEY  (`i`)
    -> ) TYPE=MyISAM CHARSET=latin1
    -> ;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t VALUES (1);
Query OK, 1 row affected (0.02 sec)

mysql> UPDATE t SET i=i+1 WHERE i=(SELECT MAX(i));
Query OK, 0 rows affected (0.02 sec)
Rows matched: 0  Changed: 0  Warnings: 0
 
# Note zeroes here!

mysql> UPDATE t SET i=i+1 WHERE i=(SELECT MAX(i));
ERROR 1111: Invalid use of group function

# Sounds better than silence above.