[MDEV-19588] Wrong results from query, using left join Created: 2019-05-24  Updated: 2019-05-29  Resolved: 2019-05-29

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 5.5.65

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None


 Description    

CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam;
 
 
 
CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam;
 
INSERT INTO t2 VALUES (1,'a',NULL),(2,'a',777),(3,'a',192),(4,'a',284),(5,'a',2),(6,'a',NULL),(7,'a',-912),(8,'a',5),(9,'a',NULL),(10,'a',-184);
 
 
 
CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam ;
 
INSERT INTO t3 VALUES (1,'a',-145),(2,'a',6),(3,'a',1),(4,'a',NULL),(5,'a',NULL),(6,'a',NULL),(7,'a',NULL),(8,'a',889),(9,'a',146),(10,'a',177),(11,'a',NULL),(12,'a',-183),(13,'a',-137),(14,'a',NULL),(15,'a',NULL),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5),(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL),(27,'a',NULL),(28,'a',1),(29,'a',NULL),(30,'a',NULL),(31,'a',127),(32,'a',-424),(33,'a',3),(34,'a',-144),(35,'a',5),(36,'a',913),(37,'a',NULL),(38,'a',6),(39,'a',-173),(40,'a',4),(41,'a',101),(42,'a',-795),(43,'a',201),(44,'a',103),(45,'a',NULL),(46,'a',NULL),(47,'a',6),(48,'a',NULL),(49,'a',9),(50,'a',NULL);
 
 
 
SELECT count(t3.c1) 
FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
 
WHERE t2.pk < 13 OR t3.i1 IS NULL;

The expected result is 17 rows. It is correct in versions 5.5-10.3 (and in Mysql 8.0.15), and also while using Innodb engine in 10.4
The current result in 10.4 is 21 rows (with MyIsam)

10.4 592fe954ef82be1b

 
MariaDB [test]> SELECT count(t3.c1) 
    -> FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
 
    -> WHERE t2.pk < 13 OR t3.i1 IS NULL;
 
+--------------+
 
| count(t3.c1) |
 
+--------------+
 
|           21 |
 
+--------------+
 
1 row in set (0.003 sec)
 
 
 
 
 
MariaDB [test]> SELECT t3.*
 
    -> FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
 
    -> WHERE t2.pk < 13 OR t3.i1 IS NULL;
 
+------+------+------+
 
| pk   | c1   | i1   |
 
+------+------+------+
 
|    4 | a    | NULL |
 
|    5 | a    | NULL |
 
|    6 | a    | NULL |
 
|    7 | a    | NULL |
 
|   11 | a    | NULL |
 
|   14 | a    | NULL |
 
|   15 | a    | NULL |
 
|   18 | a    |    2 |
 
|   20 | a    |    5 |
 
|   25 | a    |    5 |
 
|   17 | a    | NULL |
 
|   26 | a    | NULL |
 
|   27 | a    | NULL |
 
|   29 | a    | NULL |
 
|   30 | a    | NULL |
 
|   35 | a    |    5 |
 
|   37 | a    | NULL |
 
|   45 | a    | NULL |
 
|   46 | a    | NULL |
 
|   48 | a    | NULL |
 
|   50 | a    | NULL |
 
+------+------+------+
 
21 rows in set (0.005 sec)

10.3 592dc59d7a5f9bd80bf

 
MariaDB [test]> SELECT count(t3.c1) 
    -> FROM t3
 
    -> LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
 
    -> WHERE t2.pk < 13 OR t3.i1 IS NULL;
 
+--------------+
 
| count(t3.c1) |
 
+--------------+
 
|           17 |
 
+--------------+
 
1 row in set (0.001 sec)



 Comments   
Comment by Igor Babaev [ 2019-05-24 ]

The problem is already reproduced with the following population of the tables:

INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5);
 
INSERT INTO t3 VALUES (1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146),(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5),(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL);


MariaDB [test]> SELECT t3.* FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1 WHERE t2.pk < 13 OR t3.i1 IS NULL;
 
+------+------+------+
 
| pk   | c1   | i1   |
 
+------+------+------+
 
|    7 | a    | NULL |
 
|   17 | a    | NULL |
 
|   25 | a    |    5 |
 
|   26 | a    | NULL |
 
+------+------+------+

(As t1 is empty t3.i1 cannot be NOT NULL in the result set )

Comment by Igor Babaev [ 2019-05-25 ]

The bug is reproducible on 5.5,10.1,10.2,10.3 with the setting

set optimizer_switch='optimize_join_buffer_size=on';

Comment by Igor Babaev [ 2019-05-29 ]

A fix for this bug was pushed into 5.5. It should be merged upstream as it is.

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