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

Wrong results in COUNT() query with EXISTS and exists_to_in

Details

    • 10.2.10, 5.5.58, 10.1.29, 10.1.30, 10.2.12, 5.5.59

    Description

      A COUNT() query containing EXISTS and subqueries returns wrong results.
      Sometimes disabling the optimizer switch exists_to_in works as expected, I can't always reproduce this behaviour thou.
      Dropping an index (see sample.txt) also seems to fix, but there it's still 1 row off: 468 vs 469)

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          claudio.nanni Claudio Nanni created issue -
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          Comment [ {code:sql|title=Test case 1 (where switching exists_to_in off fixes the problem)}
          DROP TABLE IF EXISTS t1, t2, t3;

          CREATE TABLE t1 (a INT NOT NULL);
          INSERT INTO t1 VALUES (1),(1),(1),(5),(5);

          CREATE TABLE t2 (b INT);
          INSERT INTO t2 VALUES (5),(1);

          CREATE TABLE t3 (c INT, KEY(c));
          INSERT INTO t3 VALUES (5),(5);

          SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE b = t1.a) AND EXISTS (SELECT 1 FROM t3 WHERE c = t1.a);

          SET optimizer_switch='exists_to_in=off';

          SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE b = t1.a) AND EXISTS (SELECT 1 FROM t3 WHERE c = t1.a);

          DROP TABLE t1, t2, t3;
          {code}
          {noformat:title=Result with the default switch (wrong)}
          +---+
          | a |
          +---+
          | 5 |
          | 5 |
          | 5 |
          | 5 |
          +---+
          4 rows in set (0.00 sec)
          {noformat}
          {noformat:title=Result with exists_to_in=OFF (correct)}
          +---+
          | a |
          +---+
          | 5 |
          | 5 |
          +---+
          2 rows in set (0.00 sec)
          {noformat} ]
          elenst Elena Stepanova made changes -
          Fix Version/s 10.1 [ 16100 ]
          Fix Version/s 10.2 [ 14601 ]
          Affects Version/s 10.0 [ 16000 ]
          Affects Version/s 10.1 [ 16100 ]
          Affects Version/s 10.2 [ 14601 ]
          Assignee Oleksandr Byelkin [ sanja ]
          Summary Wrong results in COUNT() query with EXISTS Wrong results in COUNT() query with EXISTS and exists_to_in
          elenst Elena Stepanova made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          serg Sergei Golubchik made changes -
          Sprint 10.2.10 [ 183 ]
          sanja Oleksandr Byelkin made changes -
          Status Confirmed [ 10101 ] In Progress [ 3 ]
          sanja Oleksandr Byelkin made changes -
          Affects Version/s 5.5 [ 15800 ]
          sanja Oleksandr Byelkin made changes -
          Fix Version/s 5.5 [ 15800 ]
          Fix Version/s 10.0 [ 16000 ]
          sanja Oleksandr Byelkin made changes -
          Assignee Oleksandr Byelkin [ sanja ] Sergei Petrunia [ psergey ]
          Status In Progress [ 3 ] In Review [ 10002 ]
          serg Sergei Golubchik made changes -
          Sprint 10.2.10 [ 183 ] 10.2.10, 5.5.58 [ 183, 197 ]
          serg Sergei Golubchik made changes -
          Sprint 10.2.10, 5.5.58 [ 183, 197 ] 10.2.10, 5.5.58, 10.1.29 [ 183, 197, 202 ]
          serg Sergei Golubchik made changes -
          Sprint 10.2.10, 5.5.58, 10.1.29 [ 183, 197, 202 ] 10.2.10, 5.5.58, 10.1.29, 10.1.30 [ 183, 197, 202, 215 ]
          psergei Sergei Petrunia made changes -
          Assignee Sergei Petrunia [ psergey ] Oleksandr Byelkin [ sanja ]
          Status In Review [ 10002 ] Stalled [ 10000 ]
          sanja Oleksandr Byelkin made changes -
          Assignee Oleksandr Byelkin [ sanja ] Alice Sherepa [ alice ]
          serg Sergei Golubchik made changes -
          Sprint 10.2.10, 5.5.58, 10.1.29, 10.1.30 [ 183, 197, 202, 215 ] 10.2.10, 5.5.58, 10.1.29, 10.1.30, 10.2.12 [ 183, 197, 202, 215, 216 ]
          sanja Oleksandr Byelkin made changes -
          Sprint 10.2.10, 5.5.58, 10.1.29, 10.1.30, 10.2.12 [ 183, 197, 202, 215, 216 ] 10.2.10, 5.5.58, 10.1.29, 10.1.30, 10.2.12, 5.5.59 [ 183, 197, 202, 215, 216, 221 ]
          sanja Oleksandr Byelkin made changes -
          Assignee Alice Sherepa [ alice ] Oleksandr Byelkin [ sanja ]
          sanja Oleksandr Byelkin made changes -
          Fix Version/s 5.5.59 [ 22612 ]
          Fix Version/s 10.0.34 [ 22613 ]
          Fix Version/s 10.3.4 [ 22904 ]
          Fix Version/s 10.1.31 [ 22907 ]
          Fix Version/s 10.2.13 [ 22910 ]
          Fix Version/s 10.2 [ 14601 ]
          Fix Version/s 5.5 [ 15800 ]
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 10.1 [ 16100 ]
          Resolution Fixed [ 1 ]
          Status Stalled [ 10000 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 82827 ] MariaDB v4 [ 152902 ]
          mariadb-jira-automation Jira Automation (IT) made changes -
          Zendesk Related Tickets 145664

          People

            sanja Oleksandr Byelkin
            claudio.nanni Claudio Nanni
            Votes:
            0 Vote for this issue
            Watchers:
            6 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.