Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.13, 5.5(EOL), 10.0(EOL), 10.1(EOL)
-
Debian Linux Jessie (amd64)
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.
Attachments
Issue Links
- relates to
-
MDEV-6892 WHERE does not apply
- Closed