Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.1, 5.5.29, 5.3.12
-
None
-
None
Description
The following test case returns a wrong result in MariaDB 5.3/5.5:
CREATE TABLE t1 (c1 text, c2 int);
|
INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3);
|
CREATE TABLE t2 (c1 text, c2 int);
|
INSERT INTO t2 VALUES ('b',2), ('c',3);
|
CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
|
SELECT v1.c1, v1.c2 FROM v1, t2 WHERE v1.c1=t2.c1 AND v1.c2=t2.c2;
|
The last query from the test case returns:
MariaDB [test]> SELECT v1.c1, v1.c2 FROM v1, t2 WHERE v1.c1=t2.c1 AND v1.c2=t2.c2;
|
Empty set (0.00 sec)
|
The correct result is:
MariaDB [test]> SELECT v1.c1, v1.c2 FROM v1, t2 WHERE v1.c1=t2.c1 AND v1.c2=t2.c2;
|
+------+------+
|
| c1 | c2 |
|
+------+------+
|
| c | 3 |
|
| c | 3 |
|
+------+------+
|
2 rows in set (0.01 sec)
|
This result can be obtained if
SET optimizer_switch = 'derived_with_keys=off';