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

Wrong result for correlated EXISTS: equivalent INNER JOIN returns 1 but EXISTS returns 0

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3
    • 10.11, 11.4, 11.8, 12.3, 13.1
    • Optimizer
    • MariaDB 12.3.3
      Default configuration
    • Unexpected results
    • A correlated EXISTS predicate could return an incorrect result for VARCHAR comparison combined with a LIKE condition.

    Description

      A correlated EXISTS query returns an incorrect result compared with an
      equivalent INNER JOIN using the same predicate.

      Test case:

      CREATE TABLE l(x VARCHAR(1));
      INSERT INTO l VALUES (' '), ('');

      SELECT 'INNER_JOIN' AS q, COUNT AS n
      FROM l
      JOIN (SELECT '0' AS x) r
      ON l.x < r.x AND l.x LIKE '';

      SELECT 'EXISTS' AS q, COUNT AS n
      FROM l
      WHERE EXISTS (
      SELECT 1
      FROM (SELECT '0' AS x) r
      WHERE l.x < r.x AND l.x LIKE ''
      );

      Actual result:

      -------------+

      q n

      -------------+

      INNER_JOIN 1

      -------------+

      ---------+

      q n

      ---------+

      EXISTS 0

      ---------+

      Expected result:

      The EXISTS query should also return:

      ---------+

      q n

      ---------+

      EXISTS 1

      ---------+

      Reason:

      The derived table on the right side contains exactly one row:

      ('0')

      Both queries use the same matching condition:

      l.x < r.x AND l.x LIKE ''

      The INNER JOIN finds one matching row.

      Since the right-hand side contains only one row, the corresponding EXISTS
      condition should be TRUE for the same left-hand row. Therefore, counting
      the rows selected by EXISTS should also produce 1.

      However, MariaDB 12.3.3 returns 0 for the EXISTS form while the INNER JOIN
      form returns 1.

      I have not determined the root cause. The issue appears to involve the
      evaluation or optimization of the correlated EXISTS predicate.

      Thanks.

      Attachments

        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.