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

wrong result with an index and a partially null-rejecting condition

Details

    Description

      Hello,

      when I run same query with different indexes (USE INDEX to force another index), I get completely different results. I discovered this issue after upgrade to version 10.11, some queries started to return nonsense. Does work correctly in <10.11.

      This fiddle shows the code but does not exhibit the problem (version 10.9 is not affected) - https://dbfiddle.uk/eJwzXdgt both queries returns the same value. But when I run this in 10.11 or 11.1, first query returns all NULLs (invalid result), second query returns valid results.

      Queries differ only in index used.

      SELECT 
          (
      		 
          SELECT
      		 
              SUM(ps3.quantity)
      		 
          FROM
      		 
              products_stores ps3 USE INDEX (products_stores_id_store_id_product_id_variation_uindex)
      		 
          WHERE
      		 
              ps3.id_product = p.id AND ps3.id_variation <=> ps.id_variation AND ps3.id_store = 1
      		 
      ) AS store3_quantity
      		 
      FROM
      		 
          products_stores ps
      		 
      LEFT JOIN products p ON
      		 
          p.id = ps.id_product;

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32090 Index does not handle null-safe equals operator correctly in join

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            cuchac Cuchac created issue -
            cuchac Cuchac made changes -
            Field Original Value New Value
            Description Hello,

            when I run same query with different indexes (USE INDEX to force another index), I get completely different results. I discovered this issue after version 10.11 upgrade, some queries started to return nonsense. Does work correctly in <10.11.

            This fiddle shows the code but does not exhibit the problem (version 10.9 is not affected) - https://dbfiddle.uk/eJwzXdgt both queries returns the same value. But when I run this in 10.11 or 11.1, first query returns all NULLs (invalid result), second query returns valid results.

            Queries differ only in index used.


            {code:sql}
            SELECT
                (
                SELECT
                    SUM(ps3.quantity)
                FROM
                    products_stores ps3 USE INDEX (products_stores_id_store_id_product_id_variation_uindex)
                WHERE
                    ps3.id_product = p.id AND ps3.id_variation <=> ps.id_variation AND ps3.id_store = 1
            ) AS store3_quantity
            FROM
                products_stores ps
            LEFT JOIN products p ON
                p.id = ps.id_product;
            {code}
            		Hello,

            when I run same query with different indexes (USE INDEX to force another index), I get completely different results. I discovered this issue after upgrade to version 10.11, some queries started to return nonsense. Does work correctly in <10.11.

            This fiddle shows the code but does not exhibit the problem (version 10.9 is not affected) - https://dbfiddle.uk/eJwzXdgt both queries returns the same value. But when I run this in 10.11 or 11.1, first query returns all NULLs (invalid result), second query returns valid results.

            Queries differ only in index used.


            {code:sql}
            SELECT
                (
                SELECT
                    SUM(ps3.quantity)
                FROM
                    products_stores ps3 USE INDEX (products_stores_id_store_id_product_id_variation_uindex)
                WHERE
                    ps3.id_product = p.id AND ps3.id_variation <=> ps.id_variation AND ps3.id_store = 1
            ) AS store3_quantity
            FROM
                products_stores ps
            LEFT JOIN products p ON
                p.id = ps.id_product;
            {code}
            cuchac Cuchac made changes -
            Affects Version/s 11.3.0 [ 29302 ]
            cuchac Cuchac added a comment -

            Same happens with very latest - 11.3.0 - quay.io/mariadb-foundation/mariadb-devel:verylatest

            cuchac Cuchac added a comment - Same happens with very latest - 11.3.0 - quay.io/mariadb-foundation/mariadb-devel:verylatest
            alice Alice Sherepa made changes -
            Affects Version/s 10.10 [ 27530 ]
            alice Alice Sherepa made changes -
            Labels index wrong_result index regression-10.10 wrong_result
            alice Alice Sherepa made changes -
            Fix Version/s 10.10 [ 27530 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.0 [ 28320 ]
            Fix Version/s 11.1 [ 28549 ]
            Fix Version/s 11.2 [ 28603 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Summary Different results when using different indexes wrong result with join, index, and a partially null-rejecting condition
            serg Sergei Golubchik made changes -
            Summary wrong result with join, index, and a partially null-rejecting condition wrong result with an index and a partially null-rejecting condition
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Sergei Petrunia [ psergey ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            psergei Sergei Petrunia added a comment -

            serg, the patch looks good to me.

            Please add a comment with a note that

            Note that there are no store_key objects for const ref parts.

            before this piece of code.

            +    while (map & ref->const_ref_part_map)
            		 
            +      map <<= 1;

            Ok to push after that.

            psergei Sergei Petrunia added a comment - serg , the patch looks good to me. Please add a comment with a note that Note that there are no store_key objects for const ref parts. before this piece of code. + while (map & ref->const_ref_part_map) + map <<= 1; Ok to push after that.
            serg Sergei Golubchik made changes -
            Status In Review [ 10002 ] In Testing [ 10301 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Petrunia [ psergey ] Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.10.7 [ 29018 ]
            Fix Version/s 10.11.6 [ 29020 ]
            Fix Version/s 11.0.4 [ 29021 ]
            Fix Version/s 11.1.3 [ 29023 ]
            Fix Version/s 11.2.2 [ 29035 ]
            Fix Version/s 10.10 [ 27530 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.0 [ 28320 ]
            Fix Version/s 11.1 [ 28549 ]
            Fix Version/s 11.2 [ 28603 ]
            Resolution Fixed [ 1 ]
            Status In Testing [ 10301 ] Closed [ 6 ]
            alice Alice Sherepa made changes -

            People

              serg Sergei Golubchik
              cuchac Cuchac
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.