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

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

          blocks

          Task - A task that needs to be done. MDEV-16708 Unsupported commands for prepared statements

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            shulga Dmitry Shulga created issue -
            shulga Dmitry Shulga made changes -
            Field Original Value New Value
            shulga Dmitry Shulga made changes -
            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.

            MariaDB [test]> CREATE TABLE t1 (c int);
            MariaDB [test]> CREATE TABLE t2 (d int);
            MariaDB [test]> 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
            MariaDB [test]> SHOW WARNINGS;
            | 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` |

            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1";

            MariaDB [test]> 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)

            MariaDB [test]> SHOW WARNINGS;
            +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Level | Code | Message |
            +-------+------+--------------------------------------------------------------------------+
            | Note | 1276 | Field or reference 'c' of SELECT #2 was resolved in SELECT #1 |
                ^^^^^^^^^^^^^^^^ The extra warning
            | 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:

            MariaDB [test]> CREATE TABLE t1 (a INT, b INT);
            MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL));
            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL))";
            MariaDB [test]> EXECUTE stmt;
            		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.

            {code:SQL}
            MariaDB [test]> CREATE TABLE t1 (c int);
            MariaDB [test]> CREATE TABLE t2 (d int);
            MariaDB [test]> EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
            {code}
            || 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
            MariaDB [test]> SHOW WARNINGS;
            | 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` |

            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1";

            MariaDB [test]> 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)

            MariaDB [test]> SHOW WARNINGS;
            +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Level | Code | Message |
            +-------+------+--------------------------------------------------------------------------+
            | Note | 1276 | Field or reference 'c' of SELECT #2 was resolved in SELECT #1 |
                ^^^^^^^^^^^^^^^^ The extra warning
            | 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:

            MariaDB [test]> CREATE TABLE t1 (a INT, b INT);
            MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL));
            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL))";
            MariaDB [test]> EXECUTE stmt;
            shulga Dmitry Shulga made changes -
            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.

            {code:SQL}
            MariaDB [test]> CREATE TABLE t1 (c int);
            MariaDB [test]> CREATE TABLE t2 (d int);
            MariaDB [test]> EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
            {code}
            || 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
            MariaDB [test]> SHOW WARNINGS;
            | 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` |

            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1";

            MariaDB [test]> 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)

            MariaDB [test]> SHOW WARNINGS;
            +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | Level | Code | Message |
            +-------+------+--------------------------------------------------------------------------+
            | Note | 1276 | Field or reference 'c' of SELECT #2 was resolved in SELECT #1 |
                ^^^^^^^^^^^^^^^^ The extra warning
            | 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:

            MariaDB [test]> CREATE TABLE t1 (a INT, b INT);
            MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL));
            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL))";
            MariaDB [test]> EXECUTE stmt;
            		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.

            {code:SQL}
            MariaDB [test]> CREATE TABLE t1 (c int);
            MariaDB [test]> CREATE TABLE t2 (d int);
            MariaDB [test]> EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
            {code}
            || 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
            {code:SQL}
            MariaDB [test]> SHOW WARNINGS;
            {code}
            || 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` |

            {code:SQL}
            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1";
            MariaDB [test]> EXECUTE stmt;
            {code}
            || 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)

            {code:SQL}
            MariaDB [test]> SHOW WARNINGS;
            {code}
            || 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:

            {code:SQL}
            MariaDB [test]> CREATE TABLE t1 (a INT, b INT);
            MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL));
            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL))";
            MariaDB [test]> EXECUTE stmt;
            {code}
            shulga Dmitry Shulga made changes -
            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.

            {code:SQL}
            MariaDB [test]> CREATE TABLE t1 (c int);
            MariaDB [test]> CREATE TABLE t2 (d int);
            MariaDB [test]> EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
            {code}
            || 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
            {code:SQL}
            MariaDB [test]> SHOW WARNINGS;
            {code}
            || 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` |

            {code:SQL}
            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1";
            MariaDB [test]> EXECUTE stmt;
            {code}
            || 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)

            {code:SQL}
            MariaDB [test]> SHOW WARNINGS;
            {code}
            || 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:

            {code:SQL}
            MariaDB [test]> CREATE TABLE t1 (a INT, b INT);
            MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL));
            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL))";
            MariaDB [test]> EXECUTE stmt;
            {code}             		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.

            {code:SQL}
            MariaDB [test]> CREATE TABLE t1 (c int);
            MariaDB [test]> CREATE TABLE t2 (d int);
            MariaDB [test]> EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
            {code}
            || 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
            {code:SQL}
            MariaDB [test]> SHOW WARNINGS;
            {code}
            || 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` |

            {code:SQL}
            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1";
            MariaDB [test]> EXECUTE stmt;
            {code}
            || 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)

            {code:SQL}
            MariaDB [test]> SHOW WARNINGS;
            {code}
            || 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:

            {code:SQL}
            MariaDB [test]> CREATE TABLE t1 (a INT, b INT);
            MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL));
            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL))";
            MariaDB [test]> EXECUTE stmt;
            {code}
            shulga Dmitry Shulga made changes -
            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.

            {code:SQL}
            MariaDB [test]> CREATE TABLE t1 (c int);
            MariaDB [test]> CREATE TABLE t2 (d int);
            MariaDB [test]> EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
            {code}
            || 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
            {code:SQL}
            MariaDB [test]> SHOW WARNINGS;
            {code}
            || 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` |

            {code:SQL}
            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1";
            MariaDB [test]> EXECUTE stmt;
            {code}
            || 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)

            {code:SQL}
            MariaDB [test]> SHOW WARNINGS;
            {code}
            || 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:

            {code:SQL}
            MariaDB [test]> CREATE TABLE t1 (a INT, b INT);
            MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL));
            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL))";
            MariaDB [test]> EXECUTE stmt;
            {code}             		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.

            {code:SQL}
            CREATE TABLE t1 (c int);
            CREATE TABLE t2 (d int);
            EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
            {code}
            || 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
            {code:SQL}
            MariaDB [test]> SHOW WARNINGS;
            {code}
            || 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` |

            {code:SQL}
            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1";
            MariaDB [test]> EXECUTE stmt;
            {code}
            || 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)

            {code:SQL}
            MariaDB [test]> SHOW WARNINGS;
            {code}
            || 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:

            {code:SQL}
            MariaDB [test]> CREATE TABLE t1 (a INT, b INT);
            MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL));
            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL))";
            MariaDB [test]> EXECUTE stmt;
            {code}
            shulga Dmitry Shulga made changes -
            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.

            {code:SQL}
            CREATE TABLE t1 (c int);
            CREATE TABLE t2 (d int);
            EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
            {code}
            || 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
            {code:SQL}
            MariaDB [test]> SHOW WARNINGS;
            {code}
            || 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` |

            {code:SQL}
            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1";
            MariaDB [test]> EXECUTE stmt;
            {code}
            || 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)

            {code:SQL}
            MariaDB [test]> SHOW WARNINGS;
            {code}
            || 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:

            {code:SQL}
            MariaDB [test]> CREATE TABLE t1 (a INT, b INT);
            MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL));
            MariaDB [test]> PREPARE stmt FROM "EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t1 AS x1 WHERE (t1.b IS NULL))";
            MariaDB [test]> EXECUTE stmt;
            {code}             		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.

            {code:SQL}
            CREATE TABLE t1 (c int);
            CREATE TABLE t2 (d int);
            EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1;
            {code}
            || 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
            {code:SQL}
            SHOW WARNINGS;
            {code}
            || 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` |

            {code:SQL}
            PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1";
            EXECUTE stmt;
            {code}
            || 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)

            {code:SQL}
            SHOW WARNINGS;
            {code}
            || 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:

            {code:SQL}
            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;
            {code}
            shulga Dmitry Shulga made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            shulga Dmitry Shulga made changes -
            Assignee Dmitry Shulga [ JIRAUSER47315 ] Oleksandr Byelkin [ sanja ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            shulga Dmitry Shulga made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            sanja Oleksandr Byelkin added a comment -

            OK to push

            sanja Oleksandr Byelkin added a comment - OK to push
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Dmitry Shulga [ JIRAUSER47315 ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            shulga Dmitry Shulga made changes -
            Fix Version/s 10.2.38 [ 25207 ]
            Fix Version/s 10.3.29 [ 25206 ]
            Fix Version/s 10.4.19 [ 25205 ]
            Fix Version/s 10.5.10 [ 25204 ]
            Fix Version/s 10.6.0 [ 24431 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 119992 ] MariaDB v4 [ 159011 ]

            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.