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

LP:993718 - Wrong result from a query with GROUP BY, ORDER BY and LIMIT

    XMLWordPrintable

    Details

      Description

      The following sequence of commands gives a wrong result set:

      CREATE TABLE t1 (
      id BIGINT(20) , member_id_to INT(11) , r_date DATE ,
      PRIMARY KEY (id,r_date), KEY r_date_idx (r_date), KEY t1_idx01 (member_id_to)
      ) ENGINE=InnoDB;

      INSERT INTO t1 VALUES
      (107924526,518491,'2011-05-01'), (107924527,518491,'2011-05-01'), (107924528,518491,'2011-05-01'),
      (107924529,518491,'2011-05-01'), (107924530,518491,'2011-05-01'), (107924531,518491,'2011-05-01'),
      (107924532,518491,'2011-05-01'), (107924534,518491,'2011-06-21'), (107924535,518491,'2011-06-21'), (107924536,518491,'2011-06-21'), (107924537,518491,'2011-06-21'), (107924538,518491,'2011-06-21'),
      (107924542,1601319,'2011-06-21'), (107924543,1601319,'2011-06-21'), (107924544,1601319,'2011-06-21'),
      (107924545,1601319,'2011-06-21'), (107924546,1601319,'2011-06-21'), (107924547,1601319,'2011-06-21'),
      (107924548,1601319,'2011-06-21'), (107924549,1601319,'2011-06-21'), (107924550,1601319,'2011-06-21');

      SELECT member_id_to, COUNT FROM t1
      WHERE r_date = '2011-06-21' GROUP BY member_id_to ORDER BY 2 LIMIT 1;

      MariaDB [test]> SELECT member_id_to, COUNT FROM t1 WHERE r_date = '2011-06-21' GROUP BY member_id_to ORDER BY 2 LIMIT 1;
      ----------------------+

      member_id_to COUNT

      ----------------------+

      NULL 2

      ----------------------+

      (see also bug #12713907 reported for mysql-5.1)

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: