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