[#MDEV-13296] Incorrect result without an index hint

Comment by Alice Sherepa [ 2017-07-12 ]

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

Comment by Arnaud Adant [ 2017-07-12 ]

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)

Comment by Alice Sherepa [ 2017-07-13 ]

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';

Comment by Elena Stepanova [ 2017-07-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.

[MDEV-13296] Incorrect result without an index hint Created: 2017-07-11 Updated: 2020-08-25 Resolved: 2017-07-13
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	5.5, 10.0, 10.1, 10.2
Fix Version/s:	10.1.25, 5.5.57, 10.0.32, 10.2.7

[MDEV-13296] Incorrect result without an index hint Created: 2017-07-11 Updated: 2020-08-25 Resolved: 2017-07-13