Re: Wrong result with enabled index condition pushdown and disabled subquery materialization.
The problem is actually inherited from 5.2. There, one can also observe that

• the first invocation of find_all_keys() is done with quick_select!=NULL, and the quick select is used to read records
• the second invocation of find_all_keys() is done with quick_select==NULL, and full scan is used.

this is clearly not what was intended. However, the effect is only performance, the query produces correct result. MariaDB 5.3/ MySQL 5.6 add IndexConditionPushdown, which is not cleaned up correctly and causes wrong query result.

Re: Wrong result with enabled index condition pushdown and disabled subquery materialization.
This problem also causes MDEV-320 for SHOW EXPLAIN.

Re: Wrong result with enabled index condition pushdown and disabled subquery materialization.
I'm currently considering a solution where create_sort_index() would save/restore the first JOIN_TAB. It looks like this would work, except for Index Condition Pushdown:

Let's assume there is a subquery that uses filesort() on its first table.
filesort() gets its data from quick select, which uses range access, together with IndexConditionPushdown.

This subquery will execute as follows:
> filesort
read matching records with quick select + ICP
< filesort
> do_select
read table with rr_from_pointers(), using handler->rnd_pos() calls.
< do_select

and suppose the subquery is correlated, so we'll need to execute it multiple times.

The problem here is as follows:

• after we've left filesort(), we need to re-initialize the table handler for making rnd_pos() calls.
• when starting a subsequent execution, the table needs to be re-initialized for quick select with ICP.

Re-initializing for quick select is easy: we can remember the index #, and whether index_only should be ON. (Need to check also whether table->read_map should be/is re-initialized).

There is a problem with re-initialization for ICP. Pushed index condition is not saved anywhere. There is no way to remember what was pushed and re-push it later.

Re: Wrong result with enabled index condition pushdown and disabled subquery materialization.
It's possible to fix, there is table->file->pushed_idx_cond. pushed_idx_cond was initially intended to be an internal member, however it is already used from the SQL layer.

Re: Wrong result with enabled index condition pushdown and disabled subquery materialization.
... There are problems with re-initialization of MRR scans, though.

Besides that, we also need to take into account that

• if we've had a quick select that was created by the range optimizer, it should be kept and re-used.
• BUT, if the quick select was created by the get_quick_select_for_ref() call in create_sort_index(), it should be destroyed and re-created again if necessary. This is because 'ref' may contain out-of-subquery references, and so will change for each execution.

Re: Wrong result with enabled index condition pushdown and disabled subquery materialization.
FYI: have a candidate fix, it is being tested together with MDEV-325

Re: Wrong result with enabled index condition pushdown and disabled subquery materialization.
The fix survived some rounds of testing, but then we've figured it has introduced https://mariadb.atlassian.net/browse/MDEV-416

Launchpad bug id: 992942

Currently:

• MDEV-325 (which has a fix for this bug) is only present in 10.0
• for this bug, I get correct query results in the current 5.5 tree. (revision-id: igor@askmonty.org-20130123231805-5vrl79e2nia8vqb3)

I guess, MySQL has fixed it from their side, and we've got a fix merged from them.

Perhaps, we could unify the fixes. But if this bug is not repeatable, it cannot be a stopper for 5.5.29.

The bug disappeared from 5.3 tree since the following revision:

revno: 3586

revision-id: psergey@askmonty.org-20121010052122-1uua5ogs8pyoanbf

parent: sanja@montyprogram.com-20121005092655-1xfcw36gsdyhawu5

committer: Sergey Petrunya <psergey@askmonty.org>

branch nick: 5.3

timestamp: Wed 2012-10-10 09:21:22 +0400

message:

  Backport of: olav.sandstaa@oracle.com-20120516074923-vd0dhp183vqcp2ql

  .. into MariaDB 5.3

  Fix for Bug#12667154 SAME QUERY EXEC AS WHERE SUBQ GIVES DIFFERENT

                       RESULTS ON IN() & NOT IN() COMP #3

  This bug causes a wrong result in mysql-trunk when ICP is used

  and bad performance in mysql-5.5 and mysql-trunk.

...

Thus closing as fixed in 5.3.10, 5.5.28, 10.0.0.