|
Results are different before and after ANALYZE TABLE.
|
Result before ANALYZE (wrong)
|
MariaDB [test]> SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) );
|
Empty set (0.00 sec)
|
|
Execution plan before ANALYZE
|
+------+--------------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------------------------------------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
|
| 2 | DEPENDENT SUBQUERY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
|
| 2 | DEPENDENT SUBQUERY | t2 | index | i2 | i2 | 5 | NULL | 3 | 100.00 | Using where; Using index; Using join buffer (flat, BNL join) |
|
| 2 | DEPENDENT SUBQUERY | t3 | ref | i3 | i3 | 5 | test.t2.i2 | 4 | 100.00 | Using where; Using index |
|
| 3 | MATERIALIZED | t3 | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using where |
|
| 3 | MATERIALIZED | t4 | ref | i4 | i4 | 5 | test.t3.f3 | 1 | 100.00 | Using where |
|
+------+--------------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------------------------------------------+
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1276 | Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 |
|
| Note | 1003 | select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3` join `test`.`t4`) join `test`.`t3` where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t4`.`i4` = `test`.`t3`.`f3`) and (`test`.`t4`.`f4` <> `test`.`t3`.`f3`) and (`test`.`t1`.`f1` = `test`.`t3`.`f3`)))) |
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
2 rows in set (0.00 sec)
|
|
Result after ANALYZE (correct)
|
MariaDB [test]> ANALYZE TABLE t1, t2, t3, t4;
|
...
|
MariaDB [test]> SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) );
|
+------+
|
| f1 |
|
+------+
|
| 6 |
|
+------+
|
1 row in set (0.01 sec)
|
|
Execution plan after ANALYZE
|
+------+--------------------+-------+-------+---------------+------+---------+------------+------+----------+--------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+-------+-------+---------------+------+---------+------------+------+----------+--------------------------------------------------------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
|
| 2 | DEPENDENT SUBQUERY | t3 | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using where |
|
| 2 | DEPENDENT SUBQUERY | t4 | ref | i4 | i4 | 5 | test.t3.f3 | 1 | 100.00 | Using where; FirstMatch |
|
| 2 | DEPENDENT SUBQUERY | t2 | index | i2 | i2 | 5 | NULL | 3 | 100.00 | Using where; Using index; Using join buffer (flat, BNL join) |
|
| 2 | DEPENDENT SUBQUERY | t3 | ref | i3 | i3 | 5 | test.t2.i2 | 1 | 100.00 | Using where; Using index |
|
+------+--------------------+-------+-------+---------------+------+---------+------------+------+----------+--------------------------------------------------------------+
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1276 | Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 |
|
| Note | 1003 | select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where <expr_cache><`test`.`t1`.`f1`>(exists(select 1 from `test`.`t2` semi join (`test`.`t3` join `test`.`t4`) join `test`.`t3` where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t4`.`i4` = `test`.`t3`.`f3`) and (`test`.`t4`.`f4` <> `test`.`t3`.`f3`) and (`test`.`t1`.`f1` = `test`.`t3`.`f3`)))) |
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
Test case
|
--source include/have_innodb.inc
|
|
CREATE TABLE t1 (f1 INT) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (4),(6);
|
|
CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=InnoDB;
|
INSERT INTO t2 VALUES (8),(7),(1);
|
|
CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=InnoDB;
|
CREATE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3;
|
INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5);
|
|
CREATE TABLE t4 (i4 INT, f4 INT, KEY(i4)) ENGINE=InnoDB;
|
INSERT INTO t4 VALUES (0,0),(4,0),(0,2),(1,0),(2,1),(2,7),(6,3),(8,7),(8,1),(1,0),(7,2),(6,0),(8,1);
|
|
SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) );
|
ANALYZE TABLE t1,t2,t3,t4;
|
SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) );
|
|
DROP VIEW v3;
|
DROP TABLE t1, t2, t3, t4;
|
The problem isn't always reproducible right away. Here is the alternative technical test case (only to reproduce it more easily, don't put it into the test suite!)
--source include/have_innodb.inc
|
|
CREATE TABLE t1 (f1 INT) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (4),(6);
|
|
CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=InnoDB;
|
INSERT INTO t2 VALUES (8),(7),(1);
|
|
CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=InnoDB;
|
CREATE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3;
|
INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5);
|
|
CREATE TABLE t4 (i4 INT, f4 INT, KEY(i4)) ENGINE=InnoDB;
|
INSERT INTO t4 VALUES (0,0),(4,0),(0,2),(1,0),(2,1),(2,7),(6,3),(8,7),(8,1),(1,0),(7,2),(6,0),(8,1);
|
|
--let $query = SELECT COUNT(*) FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) )
|
|
--let $try = 100
|
--let $found_zero= 0
|
--let $found_non_zero = 0
|
--let $printed_zero_plan= 0
|
--let $printed_non_zero_plan= 0
|
|
while ($try)
|
{
|
--let $res= `$query`
|
dec $try;
|
--echo #
|
if ($res)
|
{
|
--let $found_non_zero= $res
|
if (!$printed_non_zero_plan)
|
{
|
--echo #------- NON-ZERO PLAN -------
|
eval EXPLAIN EXTENDED $query;
|
--let $printed_non_zero_plan= 1
|
--echo #-----------------------------
|
}
|
}
|
if (!$res)
|
{
|
--let $found_zero= 1
|
if (!$printed_zero_plan)
|
{
|
--echo #------- ZERO PLAN -------
|
eval EXPLAIN EXTENDED $query;
|
--let $printed_zero_plan= 1
|
--echo #-------------------------
|
}
|
}
|
if ($found_zero)
|
{
|
if ($found_non_zero)
|
{
|
--let $try= 0
|
}
|
}
|
--disable_query_log
|
--disable_result_log
|
ANALYZE TABLE t1,t2,t3,t4;
|
--enable_result_log
|
--enable_query_log
|
}
|
|
--echo #-------- RESULT --------
|
if ($found_zero)
|
{
|
--echo # Found 0
|
}
|
if ($found_non_zero)
|
{
|
--echo # Found $found_non_zero
|
}
|
--echo #------------------------
|
|
DROP VIEW v3;
|
DROP TABLE t1, t2, t3, t4;
|
The indication that the problem was reproduced is this output at the end of the test:
#-------- RESULT --------
|
# Found 0
|
# Found 1
|
#------------------------
|
Before it, there will also be plans for the zero- and non-zero results.
|
diff-12145.diff
