[MDEV-3528] LP:714808 - JOIN::choose_subquery_plan(table_map): Assertion `outer_lookup_keys <= outer_record_count' failed with materialization in maria-5.3-mwl89 Created: 2011-02-07  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug714808.xml    

 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

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2011-02-10 ]

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.

Comment by Timour Katchaounov (Inactive) [ 2011-02-10 ]

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

{ /* quick_range couldn't use key! */ records= (double) s->records/rec; }

----------------------------------------
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.

Comment by Rasmus Johansson (Inactive) [ 2011-02-10 ]

Launchpad bug id: 714808

Generated at Thu Feb 08 06:49:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.