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

LP:778434 - Wrong result with in_to_exists=on in maria-5.3-mwl89

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      Not repeatable with maria-5.3. The following query:

      SELECT * FROM t1 INNER JOIN t2 ON t2.f10 = t1.f11
      WHERE (6, 234) IN (
      SELECT t3.f1, t3.f1
      FROM t3 JOIN t4 ON t4.f11 = t3.f10
      );

      returns rows even though there is no value of 234 in the database and therefore there is no way for the IN predicate to be TRUE.

      EXPLAIN:

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY t1 system NULL NULL NULL NULL 1
      1 PRIMARY t2 system NULL NULL NULL NULL 1
      2 DEPENDENT SUBQUERY t3 const PRIMARY PRIMARY 4 const 1 Using index condition
      2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 21 Using where; Using join buffer (flat, BNL join)

      test case:

      CREATE TABLE t1 ( f11 int) ;
      INSERT IGNORE INTO t1 VALUES (0);

      CREATE TABLE t2 ( f10 int) ;
      INSERT IGNORE INTO t2 VALUES (0);

      CREATE TABLE t3 ( f1 int NOT NULL , f10 int, PRIMARY KEY (f1)) ;
      INSERT IGNORE INTO t3 VALUES (6,0),(10,0);

      CREATE TABLE t4 ( f11 int) ;
      INSERT IGNORE INTO t4 VALUES
      (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(NULL),
      (0),(0),(0),(0),(0),(0),(0),(0),(0),(0);

      SET SESSION optimizer_switch='materialization=off,in_to_exists=on,subquery_cache=off,semijoin=off';

      SELECT * FROM t1 INNER JOIN t2 ON t2.f10 = t1.f11
      WHERE (6, 234) IN (
      SELECT t3.f1, t3.f1
      FROM t3 JOIN t4 ON t4.f11 = t3.f10
      );

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            philipstoev Philip Stoev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 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.