Details
Description
If join_buffer_space_limit is small enough and join_cache_level is set to 4 then queries with left joins may return wrong results.
The following test case demonstrates this problem:
set join_cache_level=4; |
set join_buffer_space_limit=4000; |
create table t1 (pk varchar(512), a varchar(512)); |
insert into t1 select seq, seq from seq_1_to_10; |
create table t2 (pk varchar(512), a varchar(512)); |
insert into t2 select seq, seq from seq_1_to_10; |
create table t3 (pk varchar(512), a varchar(512)); |
insert into t3 select seq, seq from seq_1_to_5; |
create table t4 (pk varchar(512), a varchar(512), key(a)); |
insert into t4 select seq, seq from seq_1_to_5; |
select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a; |
MariaDB [test]> select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a;
|
+----------+
|
| count(*) |
|
+----------+
|
| 25 |
|
+----------+
|
With bigger enough join_buffer_space_limit the expected result is returned:
MariaDB [test]> set join_buffer_space_limit=10000;
|
 |
MariaDB [test]> select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a;
|
+----------+
|
| count(*) |
|
+----------+
|
| 5 |
|
+----------+
|
Attachments
Issue Links
- blocks
-
MDEV-30603 Wrong result with non-default JOIN_CACHE_LEVEL=[4|5] and USE_STAT_TABLES=[NEVER|COMPLEMENTARY]
- Stalled
-
MDEV-31348 Assertion `last_key_entry >= end_pos' failed in virtual bool JOIN_CACHE_HASHED::put_record()
- Closed