I agree with Alice's finding, the initial test case (which appears to be deterministic or close to it) also returns wrong results before the patch for MDEV-12838 and correct results after it.
commit c258ca2463947fcc3d69bb50a8b5cf6906778508
|
Author: Igor Babaev <igor@askmonty.org>
|
Date: Wed Jun 7 12:45:09 2017 -0700
|
|
Fixed the bug mdev-12838.
|
|
If the optimizer chose an execution plan where
|
a semi-join nest were materialized and the
|
result of materialization was scanned to access
|
other tables by ref access it could build a key
|
over columns of the tables from the nest that
|
were actually inaccessible.
|
The patch performs a proper check whether a key
|
that uses columns of the tables from a materialized
|
semi-join nest can be employed to access outer tables.
|
Reproduced on 10.1.19- 10.1.24, not reproducible on versions >=10.1.25.
Little bit shorter test case in a.sql. a.sql