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

Running of the EXPLAIN EXTENDED statement produces extra warning in case it is executed in PS (prepared statement) mode

    XMLWordPrintable

Details

    Description

      The following EXPLAIN EXTENDED statement produces different number of warning in case it is executed as a regular statement or as a prepared statement. In the later case one extra warning is generated.

      CREATE TABLE t1 (c int);
      CREATE TABLE t2 (d int);
      EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
      

      id select_type table type possible_keys key key_len ref rows filtered Extra
      1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00  
      2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00 Using where

      2 rows in set, 2 warnings (0,020 sec)
      ^^^^^^^^ 2 Warnings produced

      SHOW WARNINGS;
      

      Level Code Message
      Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
      Note 1003 select <expr_cache><`test`.`t1`.`c`>((select 1 from `test`.`t2` where `test`.`t2`.`d` = `test`.`t1`.`c`)) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1`

      PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1";
      EXECUTE stmt;
      

      id select_type table type possible_keys key key_len ref rows filtered Extra
      1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00  
      2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 100.00 Using where

      2 rows in set, 3 warnings (0,001 sec)
      ^^^^^^^^ 3 Warnings produced (1 extra warning)

      SHOW WARNINGS;
      

      Level Code Message
      Note 1276 Field or reference 'c' of SELECT #2 was resolved in SELECT #1
      Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
      Note 1003 select <expr_cache><`test`.`t1`.`c`>((select 1 from `test`.`t2` where `test`.`t2`.`d` = `test`.`t1`.`c`)) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1`

      The same behavior (one extra warning generated in case the statement executed in PS mode) can be observed if the following test case is run:

      CREATE TABLE t1 (a INT, b INT);
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL));
      PREPARE stmt FROM "EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL))";
      EXECUTE stmt;
      

      Attachments

        Issue Links

          Activity

            People

              shulga Dmitry Shulga
              shulga Dmitry Shulga
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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