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

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

    XMLWordPrintable

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

          Activity

            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.