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

table_elimination works wrong with on computed expression and compound unique key

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

            Thanks for the report and the test case. Reproducible as described.

            elenst Elena Stepanova added a comment - Thanks for the report and the test case. Reproducible as described.

            See also: MDEV-6892

            psergei Sergei Petrunia added a comment - See also: MDEV-6892
            psergei Sergei Petrunia added a comment - - edited

            Analysis: the problem is caused by Table Elimination optimization. It eliminates table T, despite that the select list has `T`.`Voted` as `Voted`

            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.

            psergei Sergei Petrunia added a comment - - edited Analysis: the problem is caused by Table Elimination optimization. It eliminates table T, despite that the select list has `T`.`Voted` as `Voted` 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.

            psergei Sergei Petrunia added a comment - MDEV-6892 is about the same issue. It has been pushed into 5.5 tree today.

            Pushed a testcase.

            psergei Sergei Petrunia added a comment - Pushed a testcase.

            Thanks for your fix. But what about 10.x branch(es)? Will this bug be fixed in those branches as well?

            sectoid Gleb Golubitsky added a comment - 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

            psergei Sergei Petrunia added a comment - 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

            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.