Bug #21326 Can SELECT from table being updated in a stored function, but can't in subquery
Submitted: 28 Jul 2006 9:26 Modified: 2 Jul 2009 9:57
Reporter: Tomash Brechko Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0.25-BK, 5.0.22/5.1BK OS:Any (any)
Assigned to: CPU Architecture:Any

[28 Jul 2006 9:26] Tomash Brechko
Description:
There is an inconsistency when SELECT is used in a statement that updates a table.

How to repeat:
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1), (1), (1);

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

(somewhat misleading error message is another issue)

But compare:

CREATE FUNCTION f() RETURNS INT RETURN (SELECT MAX(i) FROM t1) + 1;
UPDATE t1 SET i= f();

SELECT * FROM t1;
+------+
| i    |
+------+
|    2 |
|    3 |
|    4 |
+------+

Suggested fix:
To allow plain select from a table being modified.
[28 Jul 2006 13:52] 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 1 to server version: 5.0.25

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

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (i INT);
IQuery OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1), (1), (1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UPDATE t1 SET i= (SELECT MAX(i) FROM t1) + 1;
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM claus
e
mysql> CREATE FUNCTION f() RETURNS INT RETURN (SELECT MAX(i) FROM t1) + 1;
Query OK, 0 rows affected (0.03 sec)

mysql> UPDATE t1 SET i= f();
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM t1;
+------+
| i    |
+------+
|    2 |
|    3 |
|    4 |
+------+
3 rows in set (0.00 sec)

This inconsistency is surely a bug.
[30 Aug 2006 16:22] Konstantin Osipov
Yielding an error for this use case would also allow to simplify the fix for INSERT DELAYED and TRIGGERS/FUNCTIONS significantly.
[2 Jul 2009 9:56] Konstantin Osipov
This is not a locking bug as such, but intentional behavior to ensure data consistency. All correlated subqueries, according to the standard, should read the old version of the table. But we don't create a temporary copy of the old
version of the table if the same statement is updating the table. Thus, inside
locking code, we intentionally prohibit something that can lead to non-standard
results. For correlated subqueries. But not for updates inside a stored functions. Which is an inconsistency, yes.
[2 Jul 2009 9:57] Konstantin Osipov
Look at this bug when looking at WL#4100