Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Won't Fix
-
Affects Version/s: None
-
Fix Version/s: 10.0.0
-
Component/s: None
-
Labels:None
Description
EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT b FROM t2 WHERE b <= a OR b IN ( SELECT a FROM t1, t3 WHERE a != b ) ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 43 1 PRIMARY t2 ALL NULL NULL NULL NULL 12 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables #--------------- # SHOW EXPLAIN output: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 43 1 PRIMARY t2 ALL NULL NULL NULL NULL 12 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
bzr version-info
revision-id: psergey@askmonty.org-20120801115152-qgxtqd0a5vlqrrhx date: 2012-08-01 15:51:52 +0400 build-date: 2012-08-02 13:13:01 +0300 revno: 3461
Default optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
Test case:
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES
(12),(22),(9),(45),(13),(11),(17),(33),(26),(11),(9),(33),(17),(10),(10),
(42),(20),(28),(14),(66),(25),(65),(23),(11),(11),(10),(9),(24),(19),(59),
(9),(14),(9),(14),(29),(10),(10),(10),(11),(9),(9),(12),(18);
CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (1),(86),(1),(1),(1),(0),(0),(0),(0),(8),(0),(0);
CREATE TABLE t3 (c INT);
let $query =
SELECT * FROM t1
WHERE a IN ( SELECT b FROM t2 WHERE b <= a OR b IN ( SELECT a FROM t1, t3 WHERE a != b ) );
eval EXPLAIN $query;
--echo #---------------
--echo # SHOW EXPLAIN output:
--connect (con1,localhost,root,,)
--let $con_id = `SELECT CONNECTION_ID()`
--let $trials = 50
--disable_query_log
while ($trials)
{
--dec $trials
--let $run = 1000
--send_eval $query
--connection default
while ($run)
{
--error 0,1932
eval SHOW EXPLAIN FOR $con_id;
--dec $run
if (!$mysql_errno)
{
--let $run = 0
--let $trials = 0
--let $found = 1
}
}
--disable_result_log
--connection con1
--reap
--enable_result_log
}
if (!$found)
{
--echo ########### Could not catch the query by SHOW EXPLAIN, try again #############
}
DROP TABLE t1, t2, t3;
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-165 MWL#182: Explain running statements: finish coding, submit for review
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I tried the testcase on MariaDB 5.2 (explicitly specified type=MYISAM for the tables to be certain). I've got:
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 43 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 12 Using where
+3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
I've debugged both EXPLAIN and SELECT. Both conclude that the subquery can only produce zero rows, however they arrive at the conclusion through different routes.
EXPLAIN hits these lines in JOIN::optimize:
if (conds && const_table_map != found_const_table_map &&
(select_options & SELECT_DESCRIBE))
Note that the WHERE clause is replaced with Item_int(1) here. This is how correlated subquery becomes un-correlated.
SELECT hits these lines in JOIN::optimize:
if (const_table_map != found_const_table_map &&
!(select_options & SELECT_DESCRIBE) &&
(!conds ||
!(conds->used_tables() & RAND_TABLE_BIT) ||
select_lex->master_unit() == &thd->lex->unit)) // upper level SELECT
The subquery is declared as "no matching row in const table", but its WHERE clause is not removed.
Elena, please add a suppression, so that RQG doesn't consider a difference
between
type=SUBQUERY Extra='Impossible WHERE noticed after reading const tables'
type=DEPENDENT SUBQUERY Extra='no matching row in const table'
meaningful.
Won't fix; natural property of the code.
Repetable only when the tables have type=MyISAM.
Look at the query:
SELECT * FROM t1
WHERE a IN ( SELECT b FROM t2 WHERE b <= a OR b IN ( SELECT a FROM t1, t3 WHERE a != b ) );
the subquery is
( SELECT a FROM t1, t3 WHERE a != b )
t1 has column a, t3 has column c. `b` is only in table t2, which means the subquery IS correlated.