[MDEV-14071] Wildly wrong result from subquery in WHERE clause Created: 2017-10-14  Updated: 2017-11-11  Resolved: 2017-11-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2.9
Fix Version/s: 10.2.11

Type: Bug Priority: Major
Reporter: Dean Trower Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-13994 Bad join results with orderby_uses_eq... Closed

 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...?)

 Comments   
Comment by Elena Stepanova [ 2017-10-23 ]

Dean T, thanks for the report and test case.

The workaround could be using orderby_uses_equalities=off

Reproducible on 10.2 with InnoDB, not reproducible with MyISAM.
TEMPORARY tables are not important for the scenario. Here is the same test case, but without TEMPORARY tables (so that results can be compared with previous versions), and with MTR-friendly include:

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (i int, j int, z int,PRIMARY KEY (i,j), KEY (z)) ENGINE=InnoDB;
 
CREATE TABLE t2 (i int, j int, PRIMARY KEY (i,j)) ENGINE=InnoDB;
 
CREATE 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 TABLES t1,t2,t3;

Comment by Dean Trower [ 2017-10-23 ]

I just tested this in MariaDB v10.2.7 under Windows 10 x64, and IT DID NOT OCCUR.
So it's a recently introduced (v10.2.8 or v10.2.9) bug.
(Either that or it's OS specific, since I originally discovered it on a Linux server).

Comment by Igor Babaev [ 2017-11-11 ]

A test case for this bug was pushed into 10.2

Generated at Thu Feb 08 08:10:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.