Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
5.5, 10.0, 10.1, 10.2
-
None
-
10.2.6-3
Description
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.
Attachments
Issue Links
- duplicates
-
-
- Closed
-