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

Wildly wrong result from subquery in WHERE clause

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.9
    • Fix Version/s: 10.2.11
    • Component/s: Optimizer
    • Labels:
      None

      Description

      This might be the same bug as MDEV-12429, but I'm not sure, so I'm reporting it separately.

      Consider the testcase below:

      CREATE TEMPORARY TABLE t1 (i int, j int, z int,PRIMARY KEY (i,j), KEY (z)) ENGINE=InnoDB;
      CREATE TEMPORARY TABLE t2 (i int, j int, PRIMARY KEY (i,j)) ENGINE=InnoDB;
      CREATE TEMPORARY TABLE t3 (j int, n varchar(5), PRIMARY KEY (j)) ENGINE=InnoDB;
       
      ## z=1 rows
      INSERT INTO t1 VALUES (127,0,1),(188,0,1),(206,0,1),(218,0,1),(292,0,1),(338,0,1),(375,0,1),(381,0,1),(409,0,1),(466,0,1),(469,0,1),(498,0,1),(656,0,1);
      ## z=0 rows
      INSERT INTO t1 VALUES (77,4,0),(86,7,0),(96,6,0),(96,7,0),(99,9,0),(99,10,0),(99,11,0),(104,4,0),(106,5,0),(148,6,0),(177,6,0),(181,5,0),(188,8,0),(218,8,0),(253,7,0),(268,4,0),(338,4,0),(409,7,0),(466,8,0),(469,8,0),(498,8,0),(656,8,0);
      INSERT INTO t2 VALUES (127,7),(188,8),(188,9),(206,6),(218,8),(218,9),(292,7),(338,4),(338,5),(375,6),(381,5),(409,7),(409,8),(466,8),(466,9),(469,8),(469,9),(498,8),(498,9),(656,8),(656,9);
      INSERT INTO t3 VALUES (4,'four'),(5,'five'),(6,'six'),(7,'seven'),(8,'eight'),(9,'nine');
       
      #### This gives a *wildly* incorrect result:
      SELECT i,n
      FROM t1 INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j)
      WHERE i IN (SELECT i FROM t1 WHERE z=1) AND z=0 ORDER BY i;
       
      #### So does this:
      SELECT i,n
      FROM t1 x INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j)
      WHERE EXISTS (SELECT * FROM t1 WHERE i=x.i AND z=1) AND z=0 ORDER BY i;
       
      #### Manually replacing the subquery with equivalent constant data shows what the correct result should be:
      SELECT i,n
      FROM t1 INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j)
      WHERE i IN (127,188,206,218,292,338,375,381,409,466,469,498,656) AND z=0 ORDER BY i;
       
      #### Also correct:
      SELECT i,n
      FROM t1 INNER JOIN (SELECT DISTINCT i FROM t1 WHERE z=1) x USING (i) INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j)
      WHERE z=0 ORDER BY i;
       
      DROP TEMPORARY TABLES t1,t2,t3;
      

      All four SELECTs should produce exactly the same results, but I'm getting:

      i n
      656 eight
      656 eight
      656 eight
      656 eight
      656 eight
      656 eight
      656 eight
      656 eight
      656 eight
      656 eight
      656 eight
      656 eight
      656 eight

      from the first two SELECTs (which is wildly wrong!), and

      i n
      188 eight
      218 eight
      338 four
      409 seven
      466 eight
      469 eight
      498 eight
      656 eight

      from the last two (which is the correct output).

      It's almost as if

      c IN (127,188,206,218,292,338,375,381,409,466,469,498,656)
      

      in the first SELECT is erroneously somehow becoming:

      c IN (656,656,656,656,656,656,656,656,656,656,656,656,656)
      

      ...except that each "656" is still generating a unique row in the output (which the IN (656,656,...) wouldn't actually do).

      This is a pretty basic thing to be going so badly wrong, and it has actually affected my client in production code, so I think it's a pretty darn serious bug!!!

      (I'm not certain and don't have an alternate DB to test against, but since it's only been reported by them since upgrading to v10.2, I think it probably didn't occur in v10.1...?)

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              Dean T Dean Trower
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: