Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
mysqld: opt_subselect.cc:3740: bool JOIN::choose_subquery_plan(table_map): Assertion `outer_lookup_keys <= outer_record_count' failed.
backtrace:
JOIN::choose_subquery_plan(unsigned long long)+0x35e) [0x83c358c]
) [0x830dcd2]
JOIN::optimize()+0x999) [0x830620b]
st_select_lex::optimize_unflattened_subqueries()+0x104) [0x81aec14]
JOIN::optimize_unflattened_subqueries()+0x17) [0x83c322b]
JOIN::optimize()+0x2212) [0x8307a84]
mysql_select(THD*, Item**, TABLE_LIST, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x33d) [0x830ba4c]
mysql_derived_filling(THD*, st_lex*, TABLE_LIST*)+0x1e3) [0x844985c]
mysql_handle_derived(st_lex*, bool 
(THD*, st_lex*, TABLE_LIST*))+0x73) [0x8449197]
open_and_lock_tables_derived(THD*, TABLE_LIST*, bool)+0x1c4) [0x82ecf7e]
open_and_lock_tables(THD*, TABLE_LIST*)+0x20) [0x82a8a07]
) [0x82a1dd8]
mysql_execute_command(THD*)+0x719) [0x8298ffb]
mysql_parse(THD*, char*, unsigned int, char const**)+0x26f) [0x82a4584]
dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x8bc) [0x8296c93]
do_command(THD*)+0x271) [0x8296140]
handle_one_connection+0x152) [0x829321e]
test case:
SET SESSION optimizer_switch = 'materialization=on';
CREATE TABLE t1 ( pk int(11), PRIMARY KEY (pk)) ;
CREATE TABLE t2 ( f2 int(11)) ;
CREATE TABLE t3 ( f1 int(11), f3 varchar(1), KEY (f1)) ;
INSERT INTO t3 VALUES (7,'f');
SELECT * FROM (
SELECT t1.* FROM t3
RIGHT JOIN t1 ON t1.pk = t3.f1
WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 )
) AS a2;
revision-id: <email address hidden>
date: 2011-02-03 17:00:28 +0200
build-date: 2011-02-07 22:07:32 +0200
revno: 2883
branch-nick: maria-5.3-mwl89
not repeatable with maria-5.3
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: JOIN::choose_subquery_plan(table_map): Assertion `outer_lookup_keys <= outer_record_count' failed with materialization in maria-5.3-mwl89
Analysis:
The function best_access_path() computes the number or records as
follows:
----------------------------------------
...
if (rec < MATCHING_ROWS_IN_OTHER_TABLE)
rec= MATCHING_ROWS_IN_OTHER_TABLE; // Fix for small tables
...
if (table->quick_keys.is_set(key))
records= (double) table->quick_rows[key];
else
----------------------------------------
Above MATCHING_ROWS_IN_OTHER_TABLE == 10, and s->records == 1,
thus we get an estimated 0.1 records. As a result JOIN::get_partial_join_cost()
for the outer query computes outer_record_count == 0.1 records, which is
meaningless in this context.
Solution:
Round estimates that are < 1 to 1.
Launchpad bug id: 714808
Re: JOIN::choose_subquery_plan(table_map): Assertion `outer_lookup_keys <= outer_record_count' failed with materialization in maria-5.3-mwl89
The problem query was reduced to:
SELECT t1.*
FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1
WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 )
Both the RIGHT JOIN and the OR are needed because
otherwise the optimizer detects earlier that the subquery
result is empty.