Alice Sherepa added a comment - 2017-07-12 13:20 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

Arnaud Adant added a comment - 2017-07-12 13:27 - edited please note that the a test case is random ... in 10.1.20. MariaDB [test]> source a.sql; Show warnings enabled. Query OK, 0 rows affected (0.02 sec)   Query OK, 0 rows affected (0.03 sec)   Query OK, 347 rows affected (0.03 sec) Records: 347 Duplicates: 0 Warnings: 0   Query OK, 0 rows affected (0.02 sec)   Query OK, 16302 rows affected (0.20 sec) Records: 16302 Duplicates: 0 Warnings: 0   Query OK, 0 rows affected (0.03 sec) Query OK, 6 rows affected (0.02 sec) Records: 6 Duplicates: 0 Warnings: 0   +-----------+ | count(id) | +-----------+ | 2 | +-----------+ 1 row in set (0.02 sec)   +-----------+ | count(id) | +-----------+ | 6 | +-----------+ 1 row in set (0.02 sec) MariaDB [test]> source a.sql; Show warnings enabled. Query OK, 0 rows affected (0.02 sec)   Query OK, 0 rows affected (0.02 sec)   Query OK, 347 rows affected (0.03 sec) Records: 347 Duplicates: 0 Warnings: 0   Query OK, 0 rows affected (0.02 sec)   Query OK, 16302 rows affected (0.19 sec) Records: 16302 Duplicates: 0 Warnings: 0   Query OK, 0 rows affected (0.03 sec) Query OK, 6 rows affected (0.02 sec) Records: 6 Duplicates: 0 Warnings: 0   +-----------+ | count(id) | +-----------+ | 6 | +-----------+ 1 row in set (0.02 sec)   +-----------+ | count(id) | +-----------+ | 6 | +-----------+ 1 row in set (0.02 sec)

Alice Sherepa added a comment - 2017-07-13 12:02 - edited I think it is related to this bug https://jira.mariadb.org/browse/MDEV-12838 and was fixed with this revision #c258ca2463. Results of queries are the same if materialization is off. set optimizer_switch= 'materialization=off' ;

Elena Stepanova added a comment - 2017-07-13 22:13 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.