Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25350

Assertion `!table->file->keyread_enabled()' failed upon INSERT .. RETURNING with uncacheable subquery

    XMLWordPrintable

    Details

      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.

        Attachments

          Activity

            People

            Assignee:
            rucha174 Rucha Deodhar
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.