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;
createtable t1 (pk varchar(512), a varchar(512));
insertinto t1 select seq, seq from seq_1_to_10;
createtable t2 (pk varchar(512), a varchar(512));
insertinto t2 select seq, seq from seq_1_to_10;
createtable t3 (pk varchar(512), a varchar(512));
insertinto t3 select seq, seq from seq_1_to_5;
createtable t4 (pk varchar(512), a varchar(512), key(a));
insertinto t4 select seq, seq from seq_1_to_5;
selectcount(*) from t1,t2,t3 leftjoin 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-30603Wrong result with non-default JOIN_CACHE_LEVEL=[4|5] and USE_STAT_TABLES=[NEVER|COMPLEMENTARY]
Stalled
MDEV-31348Assertion `last_key_entry >= end_pos' failed in virtual bool JOIN_CACHE_HASHED::put_record()
A strange thing that we can observe here is that t1 and t2 are joined with hash join but no usage of join buffer is shown for these joins.
Igor Babaev
added a comment - Let's use the settings:
set join_cache_level=4;
set join_buffer_space_limit;
and look at the output of EXPLAIN for the query:
MariaDB [test]> explain select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a;
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
| 1 | SIMPLE | t4 | hash_index | a | #hash#a:a | 515:515 | test.t3.pk | 5 | Using where; Using index; Using join buffer (flat, BNLH join) |
| 1 | SIMPLE | t1 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where |
| 1 | SIMPLE | t2 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where |
MariaDB [test]> explain select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a;
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
| 1 | SIMPLE | t4 | hash_index | a | #hash#a:a | 515:515 | test.t3.pk | 5 | Using where; Using index; Using join buffer (flat, BNLH join) |
| 1 | SIMPLE | t1 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where |
| 1 | SIMPLE | t2 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where |
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
A strange thing that we can observe here is that t1 and t2 are joined with hash join but no usage of join buffer is shown for these joins.
Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.
JiraAutomate
added a comment - Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.
People
Igor Babaev
Igor Babaev
Votes:
0Vote for this issue
Watchers:
2Start watching this issue
Dates
Created:
Updated:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
Let's use the settings:
and look at the output of EXPLAIN for the query:
MariaDB [test]> explain select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a;
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
| 1 | SIMPLE | t4 | hash_index | a | #hash#a:a | 515:515 | test.t3.pk | 5 | Using where; Using index; Using join buffer (flat, BNLH join) |
| 1 | SIMPLE | t1 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where |
| 1 | SIMPLE | t2 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where |
MariaDB [test]> explain select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a;
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
| 1 | SIMPLE | t4 | hash_index | a | #hash#a:a | 515:515 | test.t3.pk | 5 | Using where; Using index; Using join buffer (flat, BNLH join) |
| 1 | SIMPLE | t1 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where |
| 1 | SIMPLE | t2 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where |
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
A strange thing that we can observe here is that t1 and t2 are joined with hash join but no usage of join buffer is shown for these joins.