Description:
MySQL UPDATE statement referencing the same temporary table in both the main query and a subquery triggers "ERROR 1137 (HY000): Can't reopen table: 'v0'"
mysql> CREATE TEMPORARY TABLE v0 ( v1 BIGINT UNIQUE NOT NULL );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO v0 ( v1 ) VALUES ( 127 );
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> UPDATE v0 SET v1 = 67
-> WHERE v1 NOT IN (
-> 94, 10, 42,
-> (SELECT calc_value
-> FROM (
-> SELECT v1 AS calc_value FROM v0 WHERE v1 REGEXP '^[0-9.-]+$'
-> UNION
-> SELECT -128 AS calc_value FROM v0
-> ) AS v2
-> WHERE calc_value >= -128 AND calc_value < 100000000
-> LIMIT 1
-> )
-> );
ERROR 1137 (HY000): Can't reopen table: 'v0'
Results from tidb:
mysql> drop table v0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TEMPORARY TABLE v0 ( v1 BIGINT UNIQUE NOT NULL );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO v0 ( v1 ) VALUES ( 127 );
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> UPDATE v0 SET v1 = 67
-> WHERE v1 NOT IN (
-> 94, 10, 42,
-> (SELECT calc_value
-> FROM (
-> SELECT v1 AS calc_value FROM v0 WHERE v1 REGEXP '^[0-9.-]+$'
-> UNION
-> SELECT -128 AS calc_value FROM v0
-> ) AS v2
-> WHERE calc_value >= -128 AND calc_value < 100000000
-> LIMIT 1
-> )
-> );
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
How to repeat:
drop table v0;
CREATE TEMPORARY TABLE v0 ( v1 BIGINT UNIQUE NOT NULL );
INSERT INTO v0 ( v1 ) VALUES ( 127 );
UPDATE v0 SET v1 = 67
WHERE v1 NOT IN (
94, 10, 42,
(SELECT calc_value
FROM (
SELECT v1 AS calc_value FROM v0 WHERE v1 REGEXP '^[0-9.-]+$'
UNION
SELECT -128 AS calc_value FROM v0
) AS v2
WHERE calc_value >= -128 AND calc_value < 100000000
LIMIT 1
)
);
Description: MySQL UPDATE statement referencing the same temporary table in both the main query and a subquery triggers "ERROR 1137 (HY000): Can't reopen table: 'v0'" mysql> CREATE TEMPORARY TABLE v0 ( v1 BIGINT UNIQUE NOT NULL ); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO v0 ( v1 ) VALUES ( 127 ); Query OK, 1 row affected (0.01 sec) mysql> mysql> UPDATE v0 SET v1 = 67 -> WHERE v1 NOT IN ( -> 94, 10, 42, -> (SELECT calc_value -> FROM ( -> SELECT v1 AS calc_value FROM v0 WHERE v1 REGEXP '^[0-9.-]+$' -> UNION -> SELECT -128 AS calc_value FROM v0 -> ) AS v2 -> WHERE calc_value >= -128 AND calc_value < 100000000 -> LIMIT 1 -> ) -> ); ERROR 1137 (HY000): Can't reopen table: 'v0' Results from tidb: mysql> drop table v0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TEMPORARY TABLE v0 ( v1 BIGINT UNIQUE NOT NULL ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO v0 ( v1 ) VALUES ( 127 ); Query OK, 1 row affected (0.00 sec) mysql> mysql> UPDATE v0 SET v1 = 67 -> WHERE v1 NOT IN ( -> 94, 10, 42, -> (SELECT calc_value -> FROM ( -> SELECT v1 AS calc_value FROM v0 WHERE v1 REGEXP '^[0-9.-]+$' -> UNION -> SELECT -128 AS calc_value FROM v0 -> ) AS v2 -> WHERE calc_value >= -128 AND calc_value < 100000000 -> LIMIT 1 -> ) -> ); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 How to repeat: drop table v0; CREATE TEMPORARY TABLE v0 ( v1 BIGINT UNIQUE NOT NULL ); INSERT INTO v0 ( v1 ) VALUES ( 127 ); UPDATE v0 SET v1 = 67 WHERE v1 NOT IN ( 94, 10, 42, (SELECT calc_value FROM ( SELECT v1 AS calc_value FROM v0 WHERE v1 REGEXP '^[0-9.-]+$' UNION SELECT -128 AS calc_value FROM v0 ) AS v2 WHERE calc_value >= -128 AND calc_value < 100000000 LIMIT 1 ) );