Details
Major
Description
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
See also: MDEV-6892
Analysis: the problem is caused by Table Elimination optimization. It eliminates table T, despite that the select list has
{monospaced}`T`.`Voted` as `Voted`{monospaced}.
Debugging, I can see that it is an Item_direct_view_ref() object, which has item->used_tables()=0. Because of that, table elimination assumes that nobody cares about whether table T has null-complemented row or not.
MDEV-6892 is about the same issue. It has been pushed into 5.5 tree today.
Pushed a testcase.
Thanks for your fix. But what about 10.x branch(es)? Will this bug be fixed in those branches as well?
Yes. There will be a 5.5->10.0 merge before the next 10.0.x release (10.0.18). The same goes for 10.1
Thanks for the report and the test case. Reproducible as described.