Details
Major 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 |
Attachments
Activity
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`
|
|
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))
{
chu huaxing,
Thank you very much. We will apply your patch as soon as possible.
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;;
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