• Dmitry Shulga's avatar
    MDEV-25576: The statement EXPLAIN running as regular statement and as prepared... · 91bde0fb
    Dmitry Shulga authored
    MDEV-25576: The statement EXPLAIN running as regular statement and as prepared statement produces different results for UPDATE with subquery
    
    Both EXPLAIN and EXPLAIN EXTENDED statements produce different results set
    in case it is run in normal way and in PS mode for the statements
    UPDATE/DELETE with subquery.
    
    The use case below reproduces the issue:
    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)
    
    The reason by that different result sets are produced is that on execution
    of the statement 'EXECUTE stmt' the flag SELECT_DESCRIBE not set
    in the data member SELECT_LEX::options for instances of SELECT_LEX that
    correspond to subqueries used in the UPDTAE/DELETE statements.
    
    Initially, these flags were set on parsing the statement
      PREPARE stmt FROM "EXPLAIN EXTENDED UPDATE t3 SET ..."
    but latter they were reset before starting real execution of
    the parsed query during handling the statement 'EXECUTE stmt';
    
    So, to fix the issue the functions mysql_update()/mysql_delete()
    have been modified to set the flag SELECT_DESCRIBE forcibly
    in the data member SELECT_LEX::options for the primary SELECT_LEX
    of the UPDATE/DELETE statement.
    91bde0fb
ps.result 162 KB