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

The statement EXPLAIN running as regular statement and as prepared statement produces different results for UPDATE with subquery

    XMLWordPrintable

    Details

      Description

      Both EXPLAIN and EXPLAIN EXTENDED produce different results set in case it is run in normal way and in PS mode for the statement UPDATE with subquery.

      MariaDB [test]> CREATE TABLE t1 (c1 INT KEY) ENGINE=MyISAM;
      Query OK, 0 rows affected (0,128 sec)
       
      MariaDB [test]> CREATE TABLE t2 (c2 INT) ENGINE=MyISAM;
      Query OK, 0 rows affected (0,023 sec)
       
      MariaDB [test]> CREATE TABLE t3 (c3 INT) ENGINE=MyISAM;
      Query OK, 0 rows affected (0,021 sec)
      

      MariaDB [test]> EXPLAIN EXTENDED UPDATE t3 SET c3 = ( SELECT COUNT(d1.c1) FROM ( SELECT a11.c1 FROM t1 AS a11 STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 JOIN t1 AS a12 ON a12.c1 = a11.c1 ) d1 );
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                               |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
      |    1 | PRIMARY     | t3    | ALL  | NULL          | NULL | NULL    | NULL |    0 |   100.00 |                                                     |
      |    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE noticed after reading const tables |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
      2 rows in set (0,002 sec)
      

      MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED UPDATE t3 SET c3 = ( SELECT COUNT(d1.c1) FROM ( SELECT a11.c1 FROM t1 AS a11 STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 JOIN t1 AS a12 ON a12.c1 = a11.c1 ) d1 )";
      Query OK, 0 rows affected (0,000 sec)
      Statement prepared
       
      MariaDB [test]>  EXECUTE stmt;
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
      |    1 | PRIMARY     | t3    | ALL  | NULL          | NULL | NULL    | NULL |    0 |   100.00 |                                |
      |    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
      2 rows in set (0,000 sec)
      

      Different results are also produced by the statement 'DELETE FROM with subquery' in case it is run in normal way and in PS mode.
      See test case below:

      MariaDB [test]> PREPARE stmt FROM "EXPLAIN DELETE FROM t3 WHERE c3 = ( SELECT COUNT(d1.c1) FROM ( SELECT a11.c1 FROM t1 AS a11 STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 JOIN t1 AS a12 ON a12.c1 = a11.c1 ) d1 )";
      Query OK, 0 rows affected (0,001 sec)
      Statement prepared
       
      MariaDB [test]> EXECUTE stmt;
      +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
      +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
      |    1 | PRIMARY     | t3    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where                    |
      |    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | no matching row in const table |
      +------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
      2 rows in set (0,001 sec)
      
      

      MariaDB [test]> EXPLAIN DELETE FROM t3 WHERE c3 = ( SELECT COUNT(d1.c1) FROM ( SELECT a11.c1 FROM t1 AS a11 STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 JOIN t1 AS a12 ON a12.c1 = a11.c1 ) d1 );
      +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
      +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
      |    1 | PRIMARY     | t3    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where                                         |
      |    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
      +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
      2 rows in set (0,001 sec)
      

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: