Bug #21326 Can SELECT from table being updated in a stored function, but can't in subquery

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.

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.

Yielding an error for this use case would also allow to simplify the fix for INSERT DELAYED and TRIGGERS/FUNCTIONS significantly.

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.

Look at this bug when looking at WL#4100