[#MDEV-7893] table_elimination works wrong with on computed expression and compound unique key

Hey all,

Soon after switching my project from MySQL 5.5 to MariaDB 10.0 I found an issue in optimizer. Please find below minimum sample reproducing an issue:

CREATE TABLE `TBlogPosts` (

  `PostID` int(10) unsigned NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `TBlogPosts` (`PostID`)

VALUES (1), (2);

CREATE TABLE IF NOT EXISTS `TVotes` (

  `VoteID` int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,

  `EntityID` int(10) unsigned NOT NULL,

  `UserID` int(10) unsigned NOT NULL,

  UNIQUE KEY `EntityID` (`EntityID`,`UserID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `TVotes` (`EntityID`, `UserID`)

VALUES (1,  30), (2, 30);

Schema may seem silly but it's just because I stripped away on unrelated columns.

MariaDB [test1]> SELECT 1 AS `Voted`, `EntityID` FROM `TVotes` WHERE `TVotes`.`UserID` = '20';

Empty set (0.00 sec)

Here is ACTUAL behavior:

MariaDB [test1]> SET @@optimizer_switch='table_elimination=on';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test1]> SELECT `TBlogPosts`.*, `T`.`Voted` as `Voted`

    -> FROM `TBlogPosts`

    -> LEFT JOIN (

    ->   SELECT 1 AS `Voted`, `EntityID` FROM `TVotes` WHERE `TVotes`.`UserID` = '20' ) AS `T` ON `T`.`EntityID` = `TBlogPosts`.`PostID`

    -> WHERE `TBlogPosts`.`PostID`='1'

    -> LIMIT 1;

+--------+-------+

| PostID | Voted |

+--------+-------+

|      1 |     1 |

+--------+-------+

1 row in set (0.00 sec)

MariaDB [test1]> EXPLAIN SELECT `TBlogPosts`.*, `T`.`Voted` as `Voted`

    -> FROM `TBlogPosts`

    -> LEFT JOIN (

    ->   SELECT 1 AS `Voted`, `EntityID` FROM `TVotes` WHERE `TVotes`.`UserID` = '20' ) AS `T` ON `T`.`EntityID` = `TBlogPosts`.`PostID`

    -> WHERE `TBlogPosts`.`PostID`='1'

    -> LIMIT 1;

+------+-------------+------------+------+---------------+------+---------+------+------+-------------+

| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+------+-------------+------------+------+---------------+------+---------+------+------+-------------+

|    1 | SIMPLE      | TBlogPosts | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |

+------+-------------+------------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

And now EXPECTED behaviour:

MariaDB [test1]> set optimizer_switch='table_elimination=off';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test1]> EXPLAIN SELECT `TBlogPosts`.*, `T`.`Voted` as `Voted`

    -> FROM `TBlogPosts`

    -> LEFT JOIN (

    ->   SELECT 1 AS `Voted`, `EntityID` FROM `TVotes` WHERE `TVotes`.`UserID` = '20' ) AS `T` ON `T`.`EntityID` = `TBlogPosts`.`PostID`

    -> WHERE `TBlogPosts`.`PostID`='1'

    -> LIMIT 1;

+------+-------------+------------+--------+---------------+----------+---------+-------------------------------+------+-------------+

| id   | select_type | table      | type   | possible_keys | key      | key_len | ref                           | rows | Extra       |

+------+-------------+------------+--------+---------------+----------+---------+-------------------------------+------+-------------+

|    1 | SIMPLE      | TBlogPosts | ALL    | NULL          | NULL     | NULL    | NULL                          |    2 | Using where |

|    1 | SIMPLE      | TVotes     | eq_ref | EntityID      | EntityID | 8       | test1.TBlogPosts.PostID,const |    1 | Using index |

+------+-------------+------------+--------+---------------+----------+---------+-------------------------------+------+-------------+

2 rows in set (0.00 sec)

MariaDB [test1]> SELECT `TBlogPosts`.*, `T`.`Voted` as `Voted` FROM `TBlogPosts` LEFT JOIN (   SELECT 1 AS `Voted`, `EntityID` FROM `TVotes` WHERE `TVotes`.`UserID` = '20' ) AS `T` ON `T`.`EntityID` = `TBlogPosts`.`PostID` WHERE `TBlogPosts`.`PostID`='1' LIMIT 1;

+--------+-------+

| PostID | Voted |

+--------+-------+

|      1 |  NULL |

+--------+-------+

1 row in set (0.00 sec)

If you require any additional information - please let me know.

[MDEV-7893] table_elimination works wrong with on computed expression and compound unique key Created: 2015-04-01 Updated: 2015-10-07 Resolved: 2015-04-23
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	5.3.13, 5.5, 10.0, 10.1
Fix Version/s:	5.5.43

[MDEV-7893] table_elimination works wrong with on computed expression and compound unique key Created: 2015-04-01 Updated: 2015-10-07 Resolved: 2015-04-23