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)
|
Bug
Major
MDEV-25845 Remove one SELECT_DESCRIBE check in JOIN::optimize_inner()
