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

A simple select query returns random data (upstream bug#68473)

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.1, 5.5.29, 5.1.67, 5.2.14, 5.3.12
    • 5.5.32
    • None

    Description

      Originally filed at http://bugs.mysql.com/bug.php?id=68473 by Balázs Hinel:

      Here's the SQL query to create and fill the table:

      CREATE TABLE `faulty` (
      `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `b` int(11) unsigned NOT NULL,
      `c` datetime NOT NULL,
      PRIMARY KEY (`a`),
      UNIQUE KEY `b_and_c` (`b`,`c`)
      ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;
       
      INSERT INTO `faulty` (`b`, `c`) VALUES
      (1801, '2013-02-15 09:00:00'),
      (1802, '2013-02-28 09:00:00'),
      (1802, '2013-03-01 09:00:00'),
      (5,    '1990-02-15 09:00:00'),
      (5,    '2013-02-15 09:00:00'),
      (5,    '2009-02-15 17:00:00');

      After this is done, try running this query:

      SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;

      It should return the rows where the b attribute is 1802, but it returns random data instead:

      mysql> SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
      +------------+---------------------+
      | b          | c                   |
      +------------+---------------------+
      | 2795961176 | 0000-42-94 96:72:97 |
      | 2795961176 | 0000-42-94 96:72:97 |
      +------------+---------------------+
      2 rows in set (0.00 sec)

      If you change the number 1802 to number 5 in the query, it is working properly:

      mysql> SELECT DISTINCT b, c FROM faulty WHERE b='5' ORDER BY c;
      +---+---------------------+
      | b | c                   |
      +---+---------------------+
      | 5 | 1990-02-15 09:00:00 |
      | 5 | 2009-02-15 17:00:00 |
      | 5 | 2013-02-15 09:00:00 |
      +---+---------------------+
      3 rows in set (0.00 sec)

      Some more info...
      I tested various versions including mysql 5.5.0, 5.5.8, 5.0.92, mariadb 5.1, 5.2, 10.0.1 and all have some form of this bug. (sometimes random data, sometimes it's repeated row:

      +------+---------------------+
      | b    | c                   |
      +------+---------------------+
      | 1802 | 2013-03-01 09:00:00 |
      | 1802 | 2013-03-01 09:00:00 |
      +------+---------------------+

      Similar queries and 3 different explains:

      mysql [localhost] {msandbox} (test) > explain SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
      +------+-------------+--------+-------+---------------+---------+---------+------+------+-------------------------------------------------------+
      | id   | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                                                 |
      +------+-------------+--------+-------+---------------+---------+---------+------+------+-------------------------------------------------------+
      |    1 | SIMPLE      | faulty | range | b_and_c       | b_and_c | 12      | NULL |    2 | Using where; Using index for group-by; Using filesort |
      +------+-------------+--------+-------+---------------+---------+---------+------+------+-------------------------------------------------------+
      1 row in set (0.00 sec)
       
      mysql [localhost] {msandbox} (test) > explain SELECT DISTINCT b, c FROM faulty WHERE b='5' ORDER BY c;
      +------+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
      | id   | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra                    |
      +------+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
      |    1 | SIMPLE      | faulty | ref  | b_and_c       | b_and_c | 4       | const |    3 | Using where; Using index |
      +------+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
      1 row in set (0.00 sec)
       
      mysql [localhost] {msandbox} (test) > explain SELECT DISTINCT b, c FROM faulty WHERE b=1802 ORDER BY c;
      +------+-------------+--------+-------+---------------+---------+---------+------+------+---------------------------------------+
      | id   | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                                 |
      +------+-------------+--------+-------+---------------+---------+---------+------+------+---------------------------------------+
      |    1 | SIMPLE      | faulty | range | b_and_c       | b_and_c | 12      | NULL |    2 | Using where; Using index for group-by |
      +------+-------------+--------+-------+---------------+---------+---------+------+------+---------------------------------------+
      1 row in set (0.00 sec)
       
      mysql [localhost] {msandbox} (test) > explain SELECT DISTINCT b, c FROM faulty WHERE b=5 ORDER BY c;
      +------+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
      | id   | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra                    |
      +------+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
      |    1 | SIMPLE      | faulty | ref  | b_and_c       | b_and_c | 4       | const |    3 | Using where; Using index |
      +------+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
      1 row in set (0.00 sec)

      With order by c desc yet another plan and the result is ok:

      mysql [localhost] {msandbox} (test) > explain SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c DESC;
      +------+-------------+--------+-------+---------------+---------+---------+------+------+------------------------------------------------------------------------+
      | id   | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                                                                  |
      +------+-------------+--------+-------+---------------+---------+---------+------+------+------------------------------------------------------------------------+
      |    1 | SIMPLE      | faulty | range | b_and_c       | b_and_c | 12      | NULL |    2 | Using where; Using index for group-by; Using temporary; Using filesort |
      +------+-------------+--------+-------+---------------+---------+---------+------+------+------------------------------------------------------------------------+
      1 row in set (0.00 sec)

      Attachments

        Activity

          People

            timour Timour Katchaounov (Inactive)
            pomyk Patryk Pomykalski
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.