[MDEV-24767] Wrong result when forced BNLH is used for join supported by compound index Created: 2021-02-02  Updated: 2021-03-24  Resolved: 2021-03-24

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.38, 10.3.29, 10.4.19, 10.5.10

Type: Bug Priority: Major
Reporter: chu huaxing Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: innodb


 Description   

SET JOIN_CACHE_LEVEL=4
CREATE TABLE `t1` (
`bill_no` bigint(20) not NULL,
`resource_id` bigint(20) not NULL,
`item_code` int(11) not NULL,
`acct_id` bigint(20) not NULL
) ENGINE=innodb ;

CREATE TABLE `t2` (
`ACCT_ID` bigint(20) NOT NULL,
`RESOURCE_ID` bigint(20) NOT NULL,
`BILL_NO` bigint(20) NOT NULL,
`ITEM_CODE` int(11) NOT NULL,
`BILL_FEE` bigint(20) NOT NULL,
PRIMARY KEY (`ACCT_ID`,`RESOURCE_ID`,`BILL_NO`,`ITEM_CODE`,`BILL_FEE`)
) ENGINE=innodb DEFAULT CHARSET=utf8 ;
insert into t1 values(200003728758091,1000040460,1314831,10000026910);
insert into t2 values(10000026910,1000040460,200003728758091,1314831,100);

SELECT a.bill_no, b.bill_no,a.item_code, b.item_code ,a.acct_id, b.acct_id, a.resource_id,b.resource_id FROM t2 a , t1 b where a.bill_no = b.bill_no and a.item_code = b.item_code AND a.acct_id = b.acct_id AND a.resource_id = b.resource_id limit 1;

Empty set (0.004 sec)

SELECT a.bill_no, b.bill_no,a.item_code, b.item_code ,a.acct_id, b.acct_id, a.resource_id,b.resource_id FROM t2 a , t1 b where a.bill_no = b.bill_no and a.item_code = b.item_code AND a.resource_id = b.resource_id limit 1;

bill_no bill_no item_code item_code acct_id acct_id resource_id resource_id
200003728758091 200003728758091 1314831 1314831 10000026910 10000026910 1000040460 1000040460


 Comments   
Comment by chu huaxing [ 2021-02-02 ]

find hash key order is not same
MariaDB [test]> explain SELECT a.bill_no, b.bill_no,a.item_code, b.item_code ,a.acct_id, b.`acct_id`, a.resource_id,b.resource_id FROM t2 a , t1 b where a.bill_no = b.bill_no and a.item_code = b.item_code AND a.acct_id = b.acct_id AND a.resource_id = b.resource_id limit 1;;

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a index PRIMARY PRIMARY 36 NULL 1 Using index
1 SIMPLE b hash_ALL NULL #hash#$hj 28 test.a.BILL_NO,test.a.RESOURCE_ID,test.a.ITEM_CODE,test.a.ACCT_ID 1 Using where; Using join buffer (flat, BNLH join)

put record (build hash table)
Breakpoint 18, JOIN_CACHE_HASHED::key_search (this=0x629000b68290, key=0x617000864675 "K\325\064\377", <incomplete sequence \346\265>, key_len=28, key_ref_ptr=0x7fc4fb077b20)
at /data01/chuhx_mdb4/mariadb-10.4.8/sql/sql_join_cache.cc:3096
3096 bool is_found= FALSE;
(gdb) p *(long long *)(key)
$395 = 200003728758091 BILL_NO
(gdb) p *(long long *)(key+8)
$401 = 1000040460 RESOURCE_ID
(gdb) p *(long long *)(key+8+8)
$402 = 10000026910 ACCT_ID
(gdb) p *(int *)(key+8+8+8)
$404 = 1314831 ITEM_CODE
(gdb) c

get_matching_chain_by_join_key (match )
(gdb) p *(long long *)(key)
$406 = 200003728758091 BILL_NO
(gdb) p *(long long *)(key+8)
$408 = 1000040460 RESOURCE_ID
(gdb) p *(int *)(key+8+8)
$410 = 1314831 ITEM_CODE
(gdb) p *(long long *)(key+8+8+4)
$412 = 10000026910 ACCT_ID

order is not same

Comment by Alice Sherepa [ 2021-02-02 ]

Thanks a lot! Reproducible on 5.5-10.5 

-- source include/have_innodb.inc
 
 
 
create table t1 (a int, b int, c int ) engine=innodb ;
 
create table t2 (a int, b int, c int, primary key (c,a,b)) engine=innodb ;
 
 
 
insert into t1 values(3,4,2);
 
insert into t2 values(3,4,2);
 
 
 
SELECT 1 FROM t1,t2 WHERE t2.b = t1.b AND t2.c = t1.c AND t2.a = t1.a;
 
set join_cache_level=3;
 
SELECT 1 FROM t1,t2 WHERE t2.b = t1.b AND t2.c = t1.c AND t2.a = t1.a;
 
 
 
drop table t1,t2;


MariaDB [test]> explain extended SELECT 1 FROM t1,t2 WHERE t2.b = t1.b AND t2.c = t1.c AND t2.a = t1.a;
 
+------+-------------+-------+--------+---------------+---------+---------+-------------------------------+------+----------+-------------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                           | rows | filtered | Extra       |
 
+------+-------------+-------+--------+---------------+---------+---------+-------------------------------+------+----------+-------------+
 
|    1 | SIMPLE      | t1    | ALL    | NULL          | NULL    | NULL    | NULL                          | 1    |   100.00 | Using where |
 
|    1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 12      | test.t1.c,test.t1.a,test.t1.b | 1    |   100.00 | Using index |
 
+------+-------------+-------+--------+---------------+---------+---------+-------------------------------+------+----------+-------------+
 
2 rows in set, 1 warning (0.001 sec)
 
 
 
Note (Code 1003): select 1 AS `1` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t1`.`b` and `test`.`t2`.`c` = `test`.`t1`.`c` and `test`.`t2`.`a` = `test`.`t1`.`a`
 
MariaDB [test]> SELECT 1 FROM t1,t2 WHERE t2.b = t1.b AND t2.c = t1.c AND t2.a = t1.a;
 
+---+
 
| 1 |
 
+---+
 
| 1 |
 
+---+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> set join_cache_level=3;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> SELECT 1 FROM t1,t2 WHERE t2.b = t1.b AND t2.c = t1.c AND t2.a = t1.a;
 
Empty set (0.001 sec)
 
 
 
MariaDB [test]> explain extended SELECT 1 FROM t1,t2 WHERE t2.b = t1.b AND t2.c = t1.c AND t2.a = t1.a;
 
+------+-------------+-------+------------+---------------+-----------------------+---------+-------------------------------+------+----------+--------------------------------------------------+
 
| id   | select_type | table | type       | possible_keys | key                   | key_len | ref                           | rows | filtered | Extra                                            |
 
+------+-------------+-------+------------+---------------+-----------------------+---------+-------------------------------+------+----------+--------------------------------------------------+
 
|    1 | SIMPLE      | t1    | ALL        | NULL          | NULL                  | NULL    | NULL                          | 1    |   100.00 | Using where                                      |
 
|    1 | SIMPLE      | t2    | hash_index | PRIMARY       | #hash#PRIMARY:PRIMARY | 12:12   | test.t1.c,test.t1.a,test.t1.b | 1    |   100.00 | Using index; Using join buffer (flat, BNLH join) |
 
+------+-------------+-------+------------+---------------+-----------------------+---------+-------------------------------+------+----------+--------------------------------------------------+
 
2 rows in set, 1 warning (0.001 sec)
 
 
 
Note (Code 1003): select 1 AS `1` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t1`.`b` and `test`.`t2`.`c` = `test`.`t1`.`c` and `test`.`t2`.`a` = `test`.`t1`.`a`

Comment by chu huaxing [ 2021-02-03 ]

after change ,it can work

JOIN_CACHE::check_emb_key_usage

===================================================================
— sql/sql_join_cache.cc (版本 1035902)
+++ sql/sql_join_cache.cc (工作副本)
@@ -1200,7 +1200,7 @@
Item *item= ref->items[i]->real_item();
Field *fld= ((Item_field *) item)->field;
CACHE_FIELD *init_copy= field_descr+flag_fields+i;

  • for (j= i, copy= init_copy; i < local_key_arg_fields; i+, copy+)
    + for (j= i, copy= init_copy; j < local_key_arg_fields; j+, copy+)
    {
    if (fld->eq(copy->field))
    {
Comment by Igor Babaev [ 2021-02-04 ]

chu huaxing,

Thank you very much. We will apply your patch as soon as possible.

Comment by Igor Babaev [ 2021-03-24 ]

A fix for this bug was pushed into 10.2

Generated at Thu Feb 08 09:32:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.