Description:
two table join return Unexpected result when drived table using include join index.
[the wrong result --testb is drived table]
select /*+ JOIN_PREFIX(ord, bat) */bat.id as batchPkId, bat.batch_no, ord.id,ord.history_order_batch_no as orderLevelHistoryBatchNo from testa bat inner join testb ord on ord.current_order_batch_no = bat.batch_no where bat.company_id = 48005 and ord.company_id=48005 and bat.batch_no = 101;
+-----------+----------+----+--------------------------+
| batchPkId | batch_no | id | orderLevelHistoryBatchNo |
+-----------+----------+----+--------------------------+
| 2 | 101 | 1 | NULL |
+-----------+----------+----+--------------------------+
1 row in set (0.00 sec)
[the right result --testa is DRiving Table]
select bat.id as batchPkId, bat.batch_no, ord.id,ord.history_order_batch_no as orderLevelHistoryBatchNo from testa bat inner join testb ord on ord.current_order_batch_no = bat.batch_no where bat.company_id = 48005 and ord.company_id=48005 and bat.batch_no = 101;
+-----------+----------+----+--------------------------+
| batchPkId | batch_no | id | orderLevelHistoryBatchNo |
+-----------+----------+----+--------------------------+
| 2 | 101 | 1 | NULL |
| 2 | 101 | 2 | NULL |
+-----------+----------+----+--------------------------+
2 rows in set (0.00 sec)
How to repeat:
1、create table
CREATE TABLE `testa` (
`id` bigint NOT NULL,
`company_id` bigint DEFAULT NULL,
`batch_no` bigint DEFAULT NULL,
`picker` json DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_picker_query` (`company_id`,(cast(json_extract(`picker`,_utf8mb4'$[*].userName') as char(64) array)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
CREATE TABLE `testb` (
`id` bigint NOT NULL,
`company_id` bigint DEFAULT NULL,
`current_order_batch_no` bigint DEFAULT NULL,
`history_order_batch_no` json DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx01_testb` (`company_id`,`current_order_batch_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
2、insert data into table
insert into testa values(1,48005,100,NULL);
insert into testa values(2,48005,101,'[]');
insert into testa values(3,48005,102,'[{"userId": "123456", "userName": "xiaoxu"}]');
insert into testb values(1,48005,101,NULL);
insert into testb values(2,48005,101,NULL);
insert into testb values(3,48005,102,'[123]');
insert into testb values(4,48005,102,'[124]');
3、check data
select * from testb where company_id=48005 and current_order_batch_no=101;
+----+------------+------------------------+------------------------+
| id | company_id | current_order_batch_no | history_order_batch_no |
+----+------------+------------------------+------------------------+
| 1 | 48005 | 101 | NULL |
| 2 | 48005 | 101 | NULL |
+----+------------+------------------------+------------------------+
2 rows in set (0.00 sec)
select * from testa where company_id=48005 and batch_no=101;
+----+------------+----------+--------+
| id | company_id | batch_no | picker |
+----+------------+----------+--------+
| 2 | 48005 | 101 | [] |
+----+------------+----------+--------+
1 row in set (0.00 sec)
4、two join shuld return two records -but return one record
select /*+ JOIN_PREFIX(ord, bat) */bat.id as batchPkId, bat.batch_no, ord.id,ord.history_order_batch_no as orderLevelHistoryBatchNo from testa bat inner join testb ord on ord.current_order_batch_no = bat.batch_no where bat.company_id = 48005 and ord.company_id=48005 and bat.batch_no = 101;
+-----------+----------+----+--------------------------+
| batchPkId | batch_no | id | orderLevelHistoryBatchNo |
+-----------+----------+----+--------------------------+
| 2 | 101 | 1 | NULL |
+-----------+----------+----+--------------------------+
1 row in set (0.00 sec)
explain select /*+ JOIN_PREFIX(ord, bat) */bat.id as batchPkId, bat.batch_no, ord.id,ord.history_order_batch_no as orderLevelHistoryBatchNo from testa bat inner join testb ord on ord.current_order_batch_no = bat.batch_no where bat.company_id = 48005 and ord.company_id=48005 and bat.batch_no = 101;
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | ord | NULL | ref | idx01_testb | idx01_testb | 18 | const,const | 2 | 100.00 | NULL |
| 1 | SIMPLE | bat | NULL | ref | idx_picker_query | idx_picker_query | 9 | const | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
5、two join return two records -is ok
select bat.id as batchPkId, bat.batch_no, ord.id,ord.history_order_batch_no as orderLevelHistoryBatchNo from testa bat inner join testb ord on ord.current_order_batch_no = bat.batch_no where bat.company_id = 48005 and ord.company_id=48005 and bat.batch_no = 101;
+-----------+----------+----+--------------------------+
| batchPkId | batch_no | id | orderLevelHistoryBatchNo |
+-----------+----------+----+--------------------------+
| 2 | 101 | 1 | NULL |
| 2 | 101 | 2 | NULL |
+-----------+----------+----+--------------------------+
2 rows in set (0.00 sec)
explain select bat.id as batchPkId, bat.batch_no, ord.id,ord.history_order_batch_no as orderLevelHistoryBatchNo from testa bat inner join testb ord on ord.current_order_batch_no = bat.batch_no where bat.company_id = 48005 and ord.company_id=48005 and bat.batch_no = 101;
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | bat | NULL | ref | idx_picker_query | idx_picker_query | 9 | const | 3 | 33.33 | Using where |
| 1 | SIMPLE | ord | NULL | ref | idx01_testb | idx01_testb | 18 | const,const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
Description: two table join return Unexpected result when drived table using include join index. [the wrong result --testb is drived table] select /*+ JOIN_PREFIX(ord, bat) */bat.id as batchPkId, bat.batch_no, ord.id,ord.history_order_batch_no as orderLevelHistoryBatchNo from testa bat inner join testb ord on ord.current_order_batch_no = bat.batch_no where bat.company_id = 48005 and ord.company_id=48005 and bat.batch_no = 101; +-----------+----------+----+--------------------------+ | batchPkId | batch_no | id | orderLevelHistoryBatchNo | +-----------+----------+----+--------------------------+ | 2 | 101 | 1 | NULL | +-----------+----------+----+--------------------------+ 1 row in set (0.00 sec) [the right result --testa is DRiving Table] select bat.id as batchPkId, bat.batch_no, ord.id,ord.history_order_batch_no as orderLevelHistoryBatchNo from testa bat inner join testb ord on ord.current_order_batch_no = bat.batch_no where bat.company_id = 48005 and ord.company_id=48005 and bat.batch_no = 101; +-----------+----------+----+--------------------------+ | batchPkId | batch_no | id | orderLevelHistoryBatchNo | +-----------+----------+----+--------------------------+ | 2 | 101 | 1 | NULL | | 2 | 101 | 2 | NULL | +-----------+----------+----+--------------------------+ 2 rows in set (0.00 sec) How to repeat: 1、create table CREATE TABLE `testa` ( `id` bigint NOT NULL, `company_id` bigint DEFAULT NULL, `batch_no` bigint DEFAULT NULL, `picker` json DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_picker_query` (`company_id`,(cast(json_extract(`picker`,_utf8mb4'$[*].userName') as char(64) array))) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; CREATE TABLE `testb` ( `id` bigint NOT NULL, `company_id` bigint DEFAULT NULL, `current_order_batch_no` bigint DEFAULT NULL, `history_order_batch_no` json DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx01_testb` (`company_id`,`current_order_batch_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; 2、insert data into table insert into testa values(1,48005,100,NULL); insert into testa values(2,48005,101,'[]'); insert into testa values(3,48005,102,'[{"userId": "123456", "userName": "xiaoxu"}]'); insert into testb values(1,48005,101,NULL); insert into testb values(2,48005,101,NULL); insert into testb values(3,48005,102,'[123]'); insert into testb values(4,48005,102,'[124]'); 3、check data select * from testb where company_id=48005 and current_order_batch_no=101; +----+------------+------------------------+------------------------+ | id | company_id | current_order_batch_no | history_order_batch_no | +----+------------+------------------------+------------------------+ | 1 | 48005 | 101 | NULL | | 2 | 48005 | 101 | NULL | +----+------------+------------------------+------------------------+ 2 rows in set (0.00 sec) select * from testa where company_id=48005 and batch_no=101; +----+------------+----------+--------+ | id | company_id | batch_no | picker | +----+------------+----------+--------+ | 2 | 48005 | 101 | [] | +----+------------+----------+--------+ 1 row in set (0.00 sec) 4、two join shuld return two records -but return one record select /*+ JOIN_PREFIX(ord, bat) */bat.id as batchPkId, bat.batch_no, ord.id,ord.history_order_batch_no as orderLevelHistoryBatchNo from testa bat inner join testb ord on ord.current_order_batch_no = bat.batch_no where bat.company_id = 48005 and ord.company_id=48005 and bat.batch_no = 101; +-----------+----------+----+--------------------------+ | batchPkId | batch_no | id | orderLevelHistoryBatchNo | +-----------+----------+----+--------------------------+ | 2 | 101 | 1 | NULL | +-----------+----------+----+--------------------------+ 1 row in set (0.00 sec) explain select /*+ JOIN_PREFIX(ord, bat) */bat.id as batchPkId, bat.batch_no, ord.id,ord.history_order_batch_no as orderLevelHistoryBatchNo from testa bat inner join testb ord on ord.current_order_batch_no = bat.batch_no where bat.company_id = 48005 and ord.company_id=48005 and bat.batch_no = 101; +----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | ord | NULL | ref | idx01_testb | idx01_testb | 18 | const,const | 2 | 100.00 | NULL | | 1 | SIMPLE | bat | NULL | ref | idx_picker_query | idx_picker_query | 9 | const | 3 | 33.33 | Using where | +----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) 5、two join return two records -is ok select bat.id as batchPkId, bat.batch_no, ord.id,ord.history_order_batch_no as orderLevelHistoryBatchNo from testa bat inner join testb ord on ord.current_order_batch_no = bat.batch_no where bat.company_id = 48005 and ord.company_id=48005 and bat.batch_no = 101; +-----------+----------+----+--------------------------+ | batchPkId | batch_no | id | orderLevelHistoryBatchNo | +-----------+----------+----+--------------------------+ | 2 | 101 | 1 | NULL | | 2 | 101 | 2 | NULL | +-----------+----------+----+--------------------------+ 2 rows in set (0.00 sec) explain select bat.id as batchPkId, bat.batch_no, ord.id,ord.history_order_batch_no as orderLevelHistoryBatchNo from testa bat inner join testb ord on ord.current_order_batch_no = bat.batch_no where bat.company_id = 48005 and ord.company_id=48005 and bat.batch_no = 101; +----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | bat | NULL | ref | idx_picker_query | idx_picker_query | 9 | const | 3 | 33.33 | Using where | | 1 | SIMPLE | ord | NULL | ref | idx01_testb | idx01_testb | 18 | const,const | 2 | 100.00 | NULL | +----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)