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

MIN/MAX aggregation over an indexed column may return wrong result

    XMLWordPrintable

    Details

      Description

      For a DB Table having a id field which is the table’s auto-incremented primary key
      SQL-query:
      SELECT max(id) FROM _between_bug2 WHERE id < 303 and (id BETWEEN 267 AND 287)

      gives the result:
      max(id)
      267
      , which is incorrect as id BETWEEN 267 AND 287 should be equivalent to (id >= 267 AND id <= 287 and the max id value that satisfies the condition is 287, not 267

      At the same time SQL-query
      SELECT max(id) FROM _between_bug2 WHERE id < 303 and (id >= 267 AND id <= 287)
      max(id)

      gives the correct result:
      max(id)
      287

      here is a minimal example to create DB table and reproduce the issue

      CREATE TABLE _between_bug2 (id int(13) auto_increment primary key);
      INSERT INTO _between_bug2 (id) VALUES (267);
      INSERT INTO _between_bug2 (id) VALUES (287);
      INSERT INTO _between_bug2 (id) VALUES (303);

      SELECT max(id) FROM _between_bug2 WHERE id < 303 and (id BETWEEN 267 AND 287)

      and here is an SQL fiddle to reproduce it

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            sergeylebedev Sergey Lebedev
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: