|
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
| 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)
| 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;
|
|