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

Wrong result in loose index scan

Details

    Description

      Noticed this while working on MDEV-32732. With

      CREATE TABLE t1 (a int, b int, KEY (a, b));
      		 
      insert into t1 values (1, 3), (1, 1);
      		 
      SELECT MAX(b) FROM t1 WHERE (b > 2 AND b < 4) OR (b = 5) GROUP BY a;
      		 
      drop table t1;

      At the current main c92add291e636c797e6d6ddca605905541b2a441 the SELECT statement outputs empty but it should be 3.

      It outputs the correct result at the current 10.5 5ebff6e15a0c2e1b8a8e41943540332c757a6d03.

      In fact, the bug reproduces at the current 11.4 ef966af801afc2a07222b5df65dddd52c77431dd, but not at (an ancestor) 10.11.11 e69f8cae1a15e15b9e4f5e0f8497e1f17bdc81a4

      Attachments

        Issue Links

          is part of

          New Feature - A new feature of the product, which has yet to be developed. MDEV-32732 Support DESC indexes in loose scan optimization

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 9 older comments
            psergei Sergei Petrunia added a comment -

            Well, the comment is the same as in QUICK_GROUP_MIN_MAX_SELECT::next_min_in_range, despite
            that the condition is different after the patch.
            How about 

              If we've moved the current row when looking at the previous range (result==0),
            		 
              and the current row's value for the MAX argument is smaller than the left
            		 
              boundary of cur_range, there is no need to check this range.

            psergei Sergei Petrunia added a comment - Well, the comment is the same as in QUICK_GROUP_MIN_MAX_SELECT::next_min_in_range, despite that the condition is different after the patch. How about If we've moved the current row when looking at the previous range (result==0), and the current row's value for the MAX argument is smaller than the left boundary of cur_range, there is no need to check this range.
            psergei Sergei Petrunia added a comment -

            Ok to push after the clarification is made.

            psergei Sergei Petrunia added a comment - Ok to push after the clarification is made.
            ycp Yuchen Pei added a comment - - edited

            Thanks for the review psergei. I've adapted the comment from the MDEV-32732 patch which has been approved:

                /*
            		 
                  If the key has already been "moved" by a successful call to
            		 
                  ha_index_read_map, and the current value for the max argument
            		 
                  comes before the range, there is no need to check this range.
            		 
                */

            I also changed the initial result value from 1 to HA_ERR_KEY_NOT_FOUND, same as in the MDEV-32732 patch too.

            55efe47d3ce upstream/bb-10.11-mdev-36118 MDEV-36118 Fix wrong result with MAX in loose index scan

            Let me know if there are any problems, otherwise I'll push this patch tomorrow.

            ycp Yuchen Pei added a comment - - edited Thanks for the review psergei . I've adapted the comment from the MDEV-32732 patch which has been approved: /* If the key has already been "moved" by a successful call to ha_index_read_map, and the current value for the max argument comes before the range, there is no need to check this range. */ I also changed the initial result value from 1 to HA_ERR_KEY_NOT_FOUND, same as in the MDEV-32732 patch too. 55efe47d3ce upstream/bb-10.11-mdev-36118 MDEV-36118 Fix wrong result with MAX in loose index scan Let me know if there are any problems, otherwise I'll push this patch tomorrow.
            psergei Sergei Petrunia added a comment -

            ycp, Looks good now!

            psergei Sergei Petrunia added a comment - ycp , Looks good now!
            ycp Yuchen Pei added a comment -

            Thanks for the review - pushed 55efe47d3ce0d563f32d6dbfffd1c80b6e0630c5 to 10.11

            ycp Yuchen Pei added a comment - Thanks for the review - pushed 55efe47d3ce0d563f32d6dbfffd1c80b6e0630c5 to 10.11

            People

              ycp Yuchen Pei
              ycp Yuchen Pei
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.