Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.9
-
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
- duplicates
-
-
- Closed
-
Activity
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).
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#### This gives a *wildly* incorrect result:#### So does this:#### Also correct: