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

Wrong query result in join when using an index (Version > "10.2.3")

    XMLWordPrintable

Details

    Description

      Two Tables:

      CREATE TABLE `tage` (
        `datum` date NOT NULL,
        KEY `i_datum` (`datum`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8
       
      CREATE TABLE `mitarbeiter` (
        `pk` int(11) NOT NULL DEFAULT 0,
        `in_aw_liste` int(1) DEFAULT 1,
        `deaktiviert` int(1) NOT NULL DEFAULT 0
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8
      

      Query:

      select  distinct mar.deaktiviert, in_aw_liste from mitarbeiter mar  join  tage t where  t.datum >= '2017-04-01' and  mar.deaktiviert = 0 and in_aw_liste=1;
      

      Result in Version 10.2.5:

      +-------------+-------------+
      | deaktiviert | in_aw_liste |
      +-------------+-------------+
      |           0 |           1 |
      |           1 |           1 |
      |           0 |           0 |
      |           1 |           0 |
      +-------------+-------------+
      

      but should be (as in Version 10.2.3 or/and without the index i_datum):

      +-------------+-------------+
      | deaktiviert | in_aw_liste |
      +-------------+-------------+
      |           0 |           1 |
      +-------------+-------------+
      

      The result of

      explain extended  select distinct mar.deaktiviert, in_aw_liste from mitarbeiter mar join tage t where t.datum >= '2017-04-01' and mar.deaktiviert = 0 and in_aw_liste=1;
      

      with index i_datum

      +------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                     |
      +------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------+
      |    1 | SIMPLE      | t     | range | i_datum       | i_datum | 3       | NULL |  275 |   100.00 | Using where; Using index; Using temporary |
      |    1 | SIMPLE      | mar   | ALL   | NULL          | NULL    | NULL    | NULL |  575 |   100.00 | Using where                               |
      +------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------+
      

      show warnings;
      

      +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                                                                                                                |
      +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select distinct `tmp`.`mar`.`deaktiviert` AS `deaktiviert`,`tmp`.`mar`.`in_aw_liste` AS `in_aw_liste` from `tmp`.`mitarbeiter` `mar` join `tmp`.`tage` `t` where `tmp`.`mar`.`deaktiviert` = 0 and `tmp`.`mar`.`in_aw_liste` = 1 and `tmp`.`t`.`datum` >= '2017-04-01' |
      +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      The same without index on tage (datum):

      +------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
      |    1 | SIMPLE      | mar   | ALL  | NULL          | NULL | NULL    | NULL |  575 |   100.00 | Using where; Using temporary |
      |    1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 4018 |   100.00 | Using where                  |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
      

      Warning:

      +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                                                                                                                |
      +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select distinct `tmp`.`mar`.`deaktiviert` AS `deaktiviert`,`tmp`.`mar`.`in_aw_liste` AS `in_aw_liste` from `tmp`.`mitarbeiter` `mar` join `tmp`.`tage` `t` where `tmp`.`mar`.`deaktiviert` = 0 and `tmp`.`mar`.`in_aw_liste` = 1 and `tmp`.`t`.`datum` >= '2017-04-01' |
      +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      Attachments

        1. mitarbeiter.sql
          9 kB
          Helmut Zeilinger
        2. tage.sql
          61 kB
          Helmut Zeilinger
        3. variables-10.2.3
          17 kB
          Helmut Zeilinger
        4. variables-10.2.5
          17 kB
          Helmut Zeilinger

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              wandazorro Helmut Zeilinger
              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.