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

Wrong result: MAX()/MIN() answered from the index ignore the contradiction between "c <= 3" and "c BETWEEN 5 AND 10"

    XMLWordPrintable

Details

    • Unexpected results
    • Hide
      MIN() or MAX() over an indexed column could return a value instead of NULL when the WHERE clause combined a range condition with a BETWEEN on the same column and the two ranges did not overlap, for example "c <= 3 AND c BETWEEN 5 AND 10". It happened when the range condition was written before the BETWEEN.
      Show
      MIN() or MAX() over an indexed column could return a value instead of NULL when the WHERE clause combined a range condition with a BETWEEN on the same column and the two ranges did not overlap, for example "c <= 3 AND c BETWEEN 5 AND 10". It happened when the range condition was written before the BETWEEN.

    Description

      MAX(c0) with WHERE c0 <= 3 AND c0 BETWEEN 5 AND 10 returns 10, although no row satisfies both conditions and COUNT with the same WHERE returns 0. The plan is "Select tables optimized away". With the two conditions in the other order the server reports "Impossible WHERE" and returns NULL, and with IGNORE INDEX the result is NULL. MIN() has the same problem: c0 >= 15 AND c0 BETWEEN 5 AND 10 returns 5.

      The same happens when the BETWEEN is inside a derived table and the other condition outside it, which is how it was found:
      SELECT MAX(c0) FROM (SELECT * FROM t WHERE c0 BETWEEN 5 AND 10) d WHERE c0 <= 3 returns 10.

      MySQL 26.7.1 returns NULL for all of these.

      How to reproduce

      CREATE TABLE t (c0 INT PRIMARY KEY);
      		 
      INSERT INTO t VALUES (1), (5), (10), (20);
      		 
       
      		 
      SELECT COUNT(*) FROM t WHERE c0 <= 3 AND c0 BETWEEN 5 AND 10;
      		 
      -- 0
      		 
       
      		 
      SELECT MAX(c0) FROM t WHERE c0 <= 3 AND c0 BETWEEN 5 AND 10;
      		 
      -- expected: NULL
      		 
      -- actual:   10
      		 
       
      		 
      SELECT MAX(c0) FROM t IGNORE INDEX (PRIMARY) WHERE c0 <= 3 AND c0 BETWEEN 5 AND 10;
      		 
      -- NULL   correct
      		 
       
      		 
      SELECT MAX(c0) FROM t WHERE c0 BETWEEN 5 AND 10 AND c0 <= 3;
      		 
      -- NULL   correct (conditions in the other order)
      		 
       
      		 
      SELECT MIN(c0) FROM t WHERE c0 >= 15 AND c0 BETWEEN 5 AND 10;
      		 
      -- expected: NULL
      		 
      -- actual:   5
      		 
       
      		 
      EXPLAIN SELECT MAX(c0) FROM t WHERE c0 <= 3 AND c0 BETWEEN 5 AND 10;
      		 
      -- Select tables optimized away
      		 
      EXPLAIN SELECT MAX(c0) FROM t WHERE c0 BETWEEN 5 AND 10 AND c0 <= 3;
      		 
      -- Impossible WHERE noticed after reading const tables

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-41377 MIN/MAX index shortcut rounds a DECIMAL bound onto an integer key, wrong result

          • Major - Major loss of function.
          • Confirmed

          Activity

            People

              psergei Sergei Petrunia
              Ann C Ann C
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.