CREATETABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam ;
INSERTINTO 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);
SELECTcount(t3.c1)
FROM t3 LEFTJOIN t1 LEFTJOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1
WHERE t2.pk < 13 OR t3.i1 ISNULL;
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
The problem is already reproduced with the following population of the tables:
INSERTINTO t2 VALUES (7,'a',-912),(8,'a',5);
INSERTINTO 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 )
Igor Babaev (Inactive)
added a comment - - edited 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 )
The bug is reproducible on 5.5,10.1,10.2,10.3 with the setting
set optimizer_switch='optimize_join_buffer_size=on';
Igor Babaev (Inactive)
added a comment - The bug is reproducible on 5.5,10.1,10.2,10.3 with the setting
set optimizer_switch='optimize_join_buffer_size=on';
Bug
Major
The problem is already reproduced with the following population of the tables:
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 )