Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.7
-
None
Description
CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY); |
INSERT INTO t1 VALUES (1),(2); |
|
CREATE OR REPLACE TABLE t2 (a INT); |
SET @a= 1; |
INSERT INTO t2 SELECT 1 FROM DUAL WHERE EXISTS (SELECT @a, pk FROM t1) RETURNING a; |
|
# Cleanup
|
DROP TABLE t1, t2; |
10.5 7f75acc0 |
mariadbd: /data/src/10.5/sql/sql_base.cc:933: void close_thread_table(THD*, TABLE**): Assertion `!table->file->keyread_enabled()' failed.
|
210406 18:45:41 [ERROR] mysqld got signal 6 ;
|
|
#7 0x00007ff3f4e7bf36 in __GI___assert_fail (assertion=0x55f153149d40 "!table->file->keyread_enabled()", file=0x55f153149878 "/data/src/10.5/sql/sql_base.cc", line=933, function=0x55f153149d60 "void close_thread_table(THD*, TABLE**)") at assert.c:101
|
#8 0x000055f1522d5e1e in close_thread_table (thd=0x7ff3d4000db8, table_ptr=0x7ff3d4000e98) at /data/src/10.5/sql/sql_base.cc:933
|
#9 0x000055f1522d5cd6 in close_thread_tables (thd=0x7ff3d4000db8) at /data/src/10.5/sql/sql_base.cc:919
|
#10 0x000055f15238e916 in mysql_execute_command (thd=0x7ff3d4000db8) at /data/src/10.5/sql/sql_parse.cc:6115
|
#11 0x000055f152394494 in mysql_parse (thd=0x7ff3d4000db8, rawbuf=0x7ff3d40152f0 "INSERT INTO t2 SELECT 1 FROM DUAL WHERE EXISTS (SELECT @a, pk FROM t1) RETURNING a", length=82, parser_state=0x7ff3eb7fd510, is_com_multi=false, is_next_command=false) at /data/src/10.5/sql/sql_parse.cc:8093
|
#12 0x000055f15238035f in dispatch_command (command=COM_QUERY, thd=0x7ff3d4000db8, packet=0x7ff3d400b5a9 "", packet_length=82, is_com_multi=false, is_next_command=false) at /data/src/10.5/sql/sql_parse.cc:1889
|
#13 0x000055f15237eb53 in do_command (thd=0x7ff3d4000db8) at /data/src/10.5/sql/sql_parse.cc:1370
|
#14 0x000055f15252d3e3 in do_handle_one_connection (connect=0x55f15645bcc8, put_in_cache=true) at /data/src/10.5/sql/sql_connect.cc:1410
|
#15 0x000055f15252d146 in handle_one_connection (arg=0x55f156365698) at /data/src/10.5/sql/sql_connect.cc:1312
|
#16 0x000055f152a90b57 in pfs_spawn_thread (arg=0x55f15645b8f8) at /data/src/10.5/storage/perfschema/pfs.cc:2201
|
#17 0x00007ff3f5393609 in start_thread (arg=<optimized out>) at pthread_create.c:477
|
#18 0x00007ff3f4f67293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
Reproducible on 10.5, 10.6.
The test case is not applicable to earlier versions due to the use of INSERT .. RETURNING.
No obvious problem on a non-debug build – INSERT ...RETURNING returns 1 and actually inserts it into the table, as it should.
While the query from the test case fails with a user variable, it works fine with a literal instead of the variable:
MariaDB [test]> INSERT INTO t2 SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1, pk FROM t1) RETURNING a; |
+------+ |
| a |
|
+------+ |
| 1 |
|
+------+ |
1 row in set (0.006 sec) |
EXPLAIN for both INSERT queries is identical (but as usual it's not very informative):
MariaDB [test]> EXPLAIN EXTENDED INSERT INTO t2 SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1, pk FROM t1) RETURNING a; |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+ |
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+ |
1 row in set (0.001 sec) |
|
MariaDB [test]> EXPLAIN EXTENDED INSERT INTO t2 SELECT 1 FROM DUAL WHERE EXISTS (SELECT @a, pk FROM t1) RETURNING a; |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+ |
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+ |
1 row in set (0.001 sec) |
EXPLAIN for the SELECT, however, is different:
MariaDB [test]> EXPLAIN EXTENDED SELECT 1 FROM DUAL WHERE EXISTS (SELECT @a, pk FROM t1); |
+------+----------------------+-------+-------+---------------+---------+---------+------+------+----------+----------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+----------------------+-------+-------+---------------+---------+---------+------+------+----------+----------------+ |
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
| 2 | UNCACHEABLE SUBQUERY | t1 | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Using index | |
+------+----------------------+-------+-------+---------------+---------+---------+------+------+----------+----------------+ |
2 rows in set, 1 warning (0.001 sec) |
|
MariaDB [test]> EXPLAIN EXTENDED SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1, pk FROM t1); |
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+----------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+----------------+ |
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
| 2 | SUBQUERY | t1 | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Using index | |
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+----------------+ |
2 rows in set, 1 warning (0.001 sec) |
A query doesn't necessarily have to have a variable to fail. Here, for example, is another variation:
MariaDB [test]> EXPLAIN EXTENDED SELECT 1 FROM DUAL WHERE EXISTS (SELECT LOAD_FILE('x'), pk FROM t1); |
+------+----------------------+-------+-------+---------------+---------+---------+------+------+----------+----------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+----------------------+-------+-------+---------------+---------+---------+------+------+----------+----------------+ |
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
| 2 | UNCACHEABLE SUBQUERY | t1 | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Using index | |
+------+----------------------+-------+-------+---------------+---------+---------+------+------+----------+----------------+ |
2 rows in set, 1 warning (0.001 sec) |
|
MariaDB [test]> INSERT INTO t2 SELECT 1 FROM DUAL WHERE EXISTS (SELECT LOAD_FILE('x'), pk FROM t1) RETURNING a; |
ERROR 2013 (HY000): Lost connection to MySQL server during query |
All variations which I've seen share the same UNCACHEABLE SUBQUERY specifics, hence it's been added to the summary. Please feel free to edit if it turns out to be unrelated.