|
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (10);
|
|
CREATE TABLE t2 (pk INT, b INT, PRIMARY KEY(pk), KEY(b)) ENGINE=MyISAM;
|
CREATE VIEW v2 AS SELECT * FROM t2;
|
|
CREATE TABLE t3 (c INT, KEY(c)) ENGINE=MyISAM;
|
INSERT INTO t3 VALUES (1),(2);
|
|
SELECT * FROM t1 LEFT JOIN v2 RIGHT JOIN t3 ON b = c ON a = c;
|
ALTER TABLE t3 DISABLE KEYS;
|
SELECT * FROM t1 LEFT JOIN v2 RIGHT JOIN t3 ON b = c ON a = c;
|
|
Actual result
|
MariaDB [test]> SELECT * FROM t1 LEFT JOIN v2 RIGHT JOIN t3 ON b = c ON a = c;
|
Empty set (0.002 sec)
|
|
MariaDB [test]> ALTER TABLE t3 DISABLE KEYS;
|
Query OK, 0 rows affected (0.001 sec)
|
|
MariaDB [test]> SELECT * FROM t1 LEFT JOIN v2 RIGHT JOIN t3 ON b = c ON a = c;
|
+------+------+------+------+
|
| a | pk | b | c |
|
+------+------+------+------+
|
| 10 | NULL | NULL | 1 |
|
| 10 | NULL | NULL | 2 |
|
+------+------+------+------+
|
2 rows in set (0.003 sec)
|
The difference already indicates a problem, but both results appear to be incorrect.
|
Expected result
|
MariaDB [test]> SELECT * FROM t1 LEFT JOIN v2 RIGHT JOIN t3 ON b = c ON a = c;
|
+------+------+------+------+
|
| a | pk | b | c |
|
+------+------+------+------+
|
| 10 | NULL | NULL | NULL |
|
+------+------+------+------+
|
1 row in set (0.002 sec)
|
The problem appeared in 10.4 branch with this commit:
commit 658128af43b4d7c6db445164f8ed25ed4d1e3109
|
Author: Igor Babaev
|
Date: Sun Feb 3 14:56:12 2019 -0800
|
|
MDEV-16188 Use in-memory PK filters built from range index scans
|
|