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

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.5, 10.5.9, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.2.38, 10.3.29, 10.4.19, 10.5.10
    • Optimizer
    • None

    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

            igor Igor Babaev
            sergeylebedev Sergey Lebedev
            Votes:
            1 Vote for this issue
            Watchers:
            6 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.