[#MDEV-154] LIMIT_ROWS_EXAMINED: query with OUTER JOIN in view or derived table, NOT IN examines 5 times more rows than the limit allows: limit ~ 30K, examined ~150K

The following query

SELECT * FROM v, t2

WHERE ( c, c ) NOT IN ( SELECT c, d FROM t2, t3 )

LIMIT_ROWS_EXAMINED 30000;

where v is a view

CREATE VIEW v AS

  SELECT alias1.*

  FROM t1 AS alias1 LEFT OUTER JOIN t1 AS alias2

    ON alias1.a = alias2.b;

finishes with the warning "Warning 1930 Query execution was interrupted. The query examined at least 150491 rows" (the number is somewhat greater with InnoDB). It is confirmed by the status variables:

Handler_read_key        1

Handler_read_rnd        143742

Handler_read_rnd_next   3461

Handler_tmp_write       3289

EXPLAIN:

id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered    Extra

1       PRIMARY alias1  system  NULL    NULL    NULL    NULL    1       100.00

1       PRIMARY alias2  system  NULL    NULL    NULL    NULL    1       100.00

1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    274     100.00  Using where

2       MATERIALIZED    t3      ALL     NULL    NULL    NULL    NULL    12      100.00

2       MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    274     100.00  Using join buffer (flat, BNL join)

Warnings:

Note    1003    select 'USAEnglish' AS `a`,86 AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` `alias1` left join `test`.`t1` `alias2` on(('USAEnglish' = NULL)) join `test`.`t2` where (not(<expr_cache><`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`c`,`test`.`t2`.`c`),(`test`.`t2`.`c`,`test`.`t2`.`c`) in ( <materialize> (select `test`.`t2`.`c`,`test`.`t3`.`d` from `test`.`t2` join `test`.`t3` ), <primary_index_lookup>(`test`.`t2`.`c` in <temporary table> on distinct_key where ((`test`.`t2`.`c` = `<subquery2>`.`c`) and (`test`.`t2`.`c` = `<subquery2>`.`d`))))))))

Optimizer switch (default):

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=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

bzr version-info

revision-id: timour@askmonty.org-20120213145248-tlvsd794o83odf2r

date: 2012-02-13 16:52:48 +0200

build-date: 2012-02-15 05:49:21 +0400

revno: 3417

Test case is attached.

[MDEV-154] LIMIT_ROWS_EXAMINED: query with OUTER JOIN in view or derived table, NOT IN examines 5 times more rows than the limit allows: limit ~ 30K, examined ~150K Created: 2012-02-15 Updated: 2012-02-16 Resolved: 2012-02-16
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	None
Fix Version/s:	None

[MDEV-154] LIMIT_ROWS_EXAMINED: query with OUTER JOIN in view or derived table, NOT IN examines 5 times more rows than the limit allows: limit ~ 30K, examined ~150K Created: 2012-02-15 Updated: 2012-02-16 Resolved: 2012-02-16