Details
Description
Here is the test case
the dataset:
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; |
Now i run the query
MariaDB [test]> SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
|
+------+------+------+------+
|
| c1 | c2 | c1 | c2 |
|
+------+------+------+------+
|
| c | 3 | c | 3 |
|
| c | 3 | c | 3 |
|
+------+------+------+------+
|
2 rows in set (0.006 sec)
|
|
MariaDB [test]> set @@join_cache_level=4;
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1;
|
+------+------+------+------+
|
| c1 | c2 | c1 | c2 |
|
+------+------+------+------+
|
| a | 1 | b | 2 |
|
| a | 1 | c | 3 |
|
| c | 3 | b | 2 |
|
| c | 3 | c | 3 |
|
| g | 7 | b | 2 |
|
| g | 7 | c | 3 |
|
| d | 4 | b | 2 |
|
| d | 4 | c | 3 |
|
| c | 3 | b | 2 |
|
| c | 3 | c | 3 |
|
+------+------+------+------+
|
10 rows in set (0.005 sec)
|
So we see here that raising the value of join_cache_level from 2 to 4 gives incorrect result
Attachments
Issue Links
- blocks
-
MDEV-15253 Default optimizer setting changes for MariaDB 10.4
- Closed
- relates to
-
MDEV-16714 Derived table for hash join is created with a key
- Stalled
-
MDEV-16337 Setting join_cache_level=4 changes efficient ref access plan to an inefficient hash join
- Open