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
-
-
-
- Open
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue blocks |
| Description |
Here is the test case
the dataset: {code:sql} 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; {code} 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 |
| Description |
Here is the test case
the dataset: {code:sql} 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; {code} 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 |
Here is the test case
the dataset: {code:sql} 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; {code} Now i run the query {noformat} 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) {noformat} So we see here that raising the value of join_cache_level from 2 to 4 gives incorrect result |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Link | This issue relates to MDEV-16337 [ MDEV-16337 ] |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] |
| Assignee | Varun Gupta [ varun ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Link | This issue relates to MDEV-16714 [ MDEV-16714 ] |
| Fix Version/s | 5.5.61 [ 22914 ] | |
| Fix Version/s | 10.0.36 [ 22916 ] | |
| Fix Version/s | 10.1.35 [ 23116 ] | |
| Fix Version/s | 10.2.17 [ 23111 ] | |
| Fix Version/s | 10.3.9 [ 23114 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 5.5 [ 15800 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Assignee | Sergei Petrunia [ psergey ] | Varun Gupta [ varun ] |
| Resolution | Fixed [ 1 ] | |
| Status | In Review [ 10002 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 87471 ] | MariaDB v4 [ 154431 ] |