Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7893

table_elimination works wrong with on computed expression and compound unique key

    XMLWordPrintable

Details

    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

          Activity

            People

              psergei Sergei Petrunia
              sectoid Gleb Golubitsky
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.