Description:
We have encountered a bug in MySQL 8.0.22. When executing the following two SQL statements, we get different results: SQL1 returns 1 row, which is correct, while SQL2 returns over 10,000 rows, which is incorrect.
sql_1:
SELECT
s.id AS id,
s.im_organization AS imOrganization,
s.cw_code AS cwCode,
s.location_code AS locationCode,
s.qty AS qty,
s.channel_code AS channelCode,
k.extensionCode3 AS aggregationCode,
s.sku_code AS skuCode,
l.location_type AS locationType,
'bda245e6-9f94-42d9-82e7-1c9ad206d47e' AS batchCode,
1 AS status,
0 AS isManual,
IFNULL(cf.ship_date, 0) AS shipDate,
IFNULL(cf.inventory_type, 1) AS inventoryType
FROM t_channel_sale_detail_4 s
LEFT JOIN bi_sku k ON s.sku_code = k.code
AND k.saas_tenant_code = 'baozun'
AND s.saas_tenant_code = 'baozun'
LEFT JOIN t_location_info l ON l.location_code = s.location_code
AND l.im_organization = s.im_organization
AND l.saas_tenant_code = 'baozun'
LEFT JOIN t_inventory_presets_config cf ON cf.im_organization = s.im_organization
AND cf.channel_code = s.channel_code
AND cf.location_code = s.location_code
AND cf.saas_tenant_code = 'baozun'
WHERE
s.channel_code = 'STONEISLAND0S01'
AND s.im_organization = 'STONEISLANDKG'
AND k.extensionCode3 IS NOT NULL
AND k.extensionCode3 IN (
'K1S150100002S0098V0065L',
'K1S150100002S0098V0065XL',
'K1S150100002S0098V0065M',
'K1S150100002S0098V0065XXL',
'K1S150100002S0098V0065S',
'K1S150100002S0098V00653X'
)
AND s.id > 0
AND s.saas_tenant_code = 'baozun';
sql_2: only add force index()
SELECT
s.id AS id,
s.im_organization AS imOrganization,
s.cw_code AS cwCode,
s.location_code AS locationCode,
s.qty AS qty,
s.channel_code AS channelCode,
k.extensionCode3 AS aggregationCode,
s.sku_code AS skuCode,
l.location_type AS locationType,
'bda245e6-9f94-42d9-82e7-1c9ad206d47e' AS batchCode,
1 AS status,
0 AS isManual,
IFNULL(cf.ship_date, 0) AS shipDate,
IFNULL(cf.inventory_type, 1) AS inventoryType
FROM t_channel_sale_detail_4 s force index(uniq_sale)
LEFT JOIN bi_sku k ON s.sku_code = k.code
AND k.saas_tenant_code = 'baozun'
AND s.saas_tenant_code = 'baozun'
LEFT JOIN t_location_info l ON l.location_code = s.location_code
AND l.im_organization = s.im_organization
AND l.saas_tenant_code = 'baozun'
LEFT JOIN t_inventory_presets_config cf ON cf.im_organization = s.im_organization
AND cf.channel_code = s.channel_code
AND cf.location_code = s.location_code
AND cf.saas_tenant_code = 'baozun'
WHERE
s.channel_code = 'STONEISLAND0S01'
AND s.im_organization = 'STONEISLANDKG'
AND k.extensionCode3 IS NOT NULL
AND k.extensionCode3 IN (
'K1S150100002S0098V0065L',
'K1S150100002S0098V0065XL',
'K1S150100002S0098V0065M',
'K1S150100002S0098V0065XXL',
'K1S150100002S0098V0065S',
'K1S150100002S0098V00653X'
)
AND s.id > 0
AND s.saas_tenant_code = 'baozun'
How to repeat:
We observed the execution plan and found that a filter condition was missing : AND s.im_organization = 'STONEISLANDKG'.
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
SQL EXPALIN
sql_1 explain:
+----+-------------+-------+------------+--------+-----------------------------------------------------------+------------------------+---------+--------------------------------+-------+----------+-----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-----------------------------------------------------------+------------------------+---------+--------------------------------+-------+----------+-----------------------------------------------------+
| 1 | SIMPLE | k | NULL | range | uk_sku_code,idx_sku_extensioncode3 | idx_sku_extensioncode3 | 753 | NULL | 6 | 10 | Using index condition; Using where; Using MRR |
| 1 | SIMPLE | s | NULL | ALL | PRIMARY,uniq_sale,idx_query1,idx_sku,idx_query_optimize_1 | NULL | NULL | NULL | 30780 | 10 | Range checked for each record (index map: 0x73) |
| 1 | SIMPLE | l | NULL | eq_ref | uniq_locationName,uniq_locationCode | uniq_locationCode | 62 | db_im2_01_prod.s.location_code | 1 | 100 | Using where; Using join buffer (Batched Key Access) |
| 1 | SIMPLE | cf | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+--------+-----------------------------------------------------------+------------------------+---------+--------------------------------+-------+----------+-----------------------------------------------------+
4 rows in set, 3 warnings (0.0016 sec)
Warning (code 1739): Cannot use ref access on index 'idx_sku' due to type or collation conversion on field 'sku_code'
Warning (code 1739): Cannot use ref access on index 'idx_query_optimize_1' due to type or collation conversion on field 'sku_code'
Note (code 1003): /* select#1 */ select `db_im2_01_prod`.`s`.`id` AS `id`,`db_im2_01_prod`.`s`.`im_organization` AS `imOrganization`,`db_im2_01_prod`.`s`.`cw_code` AS `cwCode`,`db_im2_01_prod`.`s`.`location_code` AS `locationCode`,`db_im2_01_prod`.`s`.`qty` AS `qty`,`db_im2_01_prod`.`s`.`channel_code` AS `channelCode`,`db_im2_01_prod`.`k`.`extensionCode3` AS `aggregationCode`,`db_im2_01_prod`.`s`.`sku_code` AS `skuCode`,`db_im2_01_prod`.`l`.`location_type` AS `locationType`,'bda245e6-9f94-42d9-82e7-1c9ad206d47e' AS `batchCode`,1 AS `status`,0 AS `isManual`,ifnull(`db_im2_01_prod`.`cf`.`ship_date`,0) AS `shipDate`,ifnull(`db_im2_01_prod`.`cf`.`inventory_type`,1) AS `inventoryType` from `db_im2_01_prod`.`t_channel_sale_detail_4` `s` join `db_im2_01_prod`.`bi_sku` `k` left join `db_im2_01_prod`.`t_location_info` `l` on(((`db_im2_01_prod`.`l`.`location_code` = `db_im2_01_prod`.`s`.`location_code`) and (`db_im2_01_prod`.`l`.`im_organization` = `db_im2_01_prod`.`s`.`im_organization`) and (`db_im2_01_prod`.`l`.`saas_tenant_code` = 'baozun'))) left join `db_im2_01_prod`.`t_inventory_presets_config` `cf` on(((`db_im2_01_prod`.`cf`.`im_organization` = convert(`db_im2_01_prod`.`s`.`im_organization` using utf8mb4)) and (`db_im2_01_prod`.`cf`.`channel_code` = convert(`db_im2_01_prod`.`s`.`channel_code` using utf8mb4)) and (`db_im2_01_prod`.`cf`.`location_code` = convert(`db_im2_01_prod`.`s`.`location_code` using utf8mb4)) and (`db_im2_01_prod`.`cf`.`saas_tenant_code` = 'baozun'))) where ((`db_im2_01_prod`.`s`.`channel_code` = 'STONEISLAND0S01') and (`db_im2_01_prod`.`s`.`im_organization` = 'STONEISLANDKG') and (`db_im2_01_prod`.`k`.`extensionCode3` is not null) and (`db_im2_01_prod`.`k`.`extensionCode3` in ('K1S150100002S0098V0065L','K1S150100002S0098V0065XL','K1S150100002S0098V0065M','K1S150100002S0098V0065XXL','K1S150100002S0098V0065S','K1S150100002S0098V00653X')) and (`db_im2_01_prod`.`s`.`id` > 0) and (`db_im2_01_prod`.`s`.`saas_tenant_code` = 'baozun') and (`db_im2_01_prod`.`s`.`sku_code` = `db_im2_01_prod`.`k`.`code`) and (`db_im2_01_prod`.`k`.`saas_tenant_code` = 'baozun'))
sql_1 explain analyze:
*************************** 1. row ***************************
EXPLAIN: -> Nested loop left join (cost=2406.86 rows=1847) (actual time=0.351..0.583 rows=1 loops=1)
-> Nested loop left join (cost=2221.92 rows=1847) (actual time=0.343..0.574 rows=1 loops=1)
-> Nested loop inner join (cost=2024.58 rows=1847) (actual time=0.326..0.557 rows=1 loops=1)
-> Filter: (k.saas_tenant_code = 'baozun') (cost=4.13 rows=1) (actual time=0.142..0.171 rows=6 loops=1)
-> Index range scan on k using idx_sku_extensioncode3, with index condition: ((k.extensionCode3 is not null) and (k.extensionCode3 in ('K1S150100002S0098V0065L','K1S150100002S0098V0065XL','K1S150100002S0098V0065M','K1S150100002S0098V0065XXL','K1S150100002S0098V0065S','K1S150100002S0098V00653X'))) (cost=4.13 rows=6) (actual time=0.141..0.168 rows=6 loops=1)
-> Filter: ((s.channel_code = 'STONEISLAND0S01') and (s.im_organization = 'STONEISLANDKG') and (s.id > 0) and (s.saas_tenant_code = 'baozun') and (s.sku_code = k.`code`)) (cost=29263.53 rows=3078) (actual time=0.064..0.064 rows=0 loops=6)
-> Index range scan on s (re-planned for each iteration) (cost=29263.53 rows=30780) (actual time=0.064..0.064 rows=0 loops=6)
-> Filter: ((l.location_code = s.location_code) and (l.im_organization = s.im_organization) and (l.saas_tenant_code = 'baozun')) (cost=0.25 rows=1) (actual time=0.016..0.016 rows=1 loops=1)
-> Single-row index lookup on l using uniq_locationCode (location_code=s.location_code) (cost=0.25 rows=1) (actual time=0.015..0.015 rows=1 loops=1)
-> Filter: ((cf.im_organization = convert(s.im_organization using utf8mb4)) and (cf.channel_code = convert(s.channel_code using utf8mb4)) and (cf.location_code = convert(s.location_code using utf8mb4)) and (cf.saas_tenant_code = 'baozun')) (cost=0.01 rows=1) (actual time=0.007..0.007 rows=0 loops=1)
-> Table scan on cf (cost=0.01 rows=1) (actual time=0.006..0.006 rows=0 loops=1)
1 row in set, 2 warnings (0.0018 sec)
Warning (code 1739): Cannot use ref access on index 'idx_sku' due to type or collation conversion on field 'sku_code'
Warning (code 1739): Cannot use ref access on index 'idx_query_optimize_1' due to type or collation conversion on field 'sku_code'
sql_2 explain:
+----+-------------+-------+------------+--------+-------------------------------------+------------------------+---------+--------------------------------+------+----------+---------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-------------------------------------+------------------------+---------+--------------------------------+------+----------+---------------------------------------------------------------+
| 1 | SIMPLE | k | NULL | range | uk_sku_code,idx_sku_extensioncode3 | idx_sku_extensioncode3 | 753 | NULL | 6 | 10 | Using index condition; Using where; Using MRR |
| 1 | SIMPLE | s | NULL | ref | uniq_sale | uniq_sale | 455 | const,const | 3076 | 0.33 | Using index condition; Using join buffer (Batched Key Access) |
| 1 | SIMPLE | l | NULL | eq_ref | uniq_locationName,uniq_locationCode | uniq_locationCode | 62 | db_im2_01_prod.s.location_code | 1 | 100 | Using where; Using join buffer (Batched Key Access) |
| 1 | SIMPLE | cf | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+--------+-------------------------------------+------------------------+---------+--------------------------------+------+----------+---------------------------------------------------------------+
4 rows in set, 1 warning (0.0011 sec)
Note (code 1003): /* select#1 */ select `db_im2_01_prod`.`s`.`id` AS `id`,`db_im2_01_prod`.`s`.`im_organization` AS `imOrganization`,`db_im2_01_prod`.`s`.`cw_code` AS `cwCode`,`db_im2_01_prod`.`s`.`location_code` AS `locationCode`,`db_im2_01_prod`.`s`.`qty` AS `qty`,`db_im2_01_prod`.`s`.`channel_code` AS `channelCode`,`db_im2_01_prod`.`k`.`extensionCode3` AS `aggregationCode`,`db_im2_01_prod`.`s`.`sku_code` AS `skuCode`,`db_im2_01_prod`.`l`.`location_type` AS `locationType`,'bda245e6-9f94-42d9-82e7-1c9ad206d47e' AS `batchCode`,1 AS `status`,0 AS `isManual`,ifnull(`db_im2_01_prod`.`cf`.`ship_date`,0) AS `shipDate`,ifnull(`db_im2_01_prod`.`cf`.`inventory_type`,1) AS `inventoryType` from `db_im2_01_prod`.`t_channel_sale_detail_4` `s` FORCE INDEX (`uniq_sale`) join `db_im2_01_prod`.`bi_sku` `k` left join `db_im2_01_prod`.`t_location_info` `l` on(((`db_im2_01_prod`.`l`.`location_code` = `db_im2_01_prod`.`s`.`location_code`) and (`db_im2_01_prod`.`l`.`im_organization` = `db_im2_01_prod`.`s`.`im_organization`) and (`db_im2_01_prod`.`l`.`saas_tenant_code` = 'baozun'))) left join `db_im2_01_prod`.`t_inventory_presets_config` `cf` on(((`db_im2_01_prod`.`cf`.`im_organization` = convert(`db_im2_01_prod`.`s`.`im_organization` using utf8mb4)) and (`db_im2_01_prod`.`cf`.`channel_code` = convert(`db_im2_01_prod`.`s`.`channel_code` using utf8mb4)) and (`db_im2_01_prod`.`cf`.`location_code` = convert(`db_im2_01_prod`.`s`.`location_code` using utf8mb4)) and (`db_im2_01_prod`.`cf`.`saas_tenant_code` = 'baozun'))) where ((`db_im2_01_prod`.`s`.`channel_code` = 'STONEISLAND0S01') and (`db_im2_01_prod`.`s`.`im_organization` = 'STONEISLANDKG') and (`db_im2_01_prod`.`k`.`extensionCode3` is not null) and (`db_im2_01_prod`.`k`.`extensionCode3` in ('K1S150100002S0098V0065L','K1S150100002S0098V0065XL','K1S150100002S0098V0065M','K1S150100002S0098V0065XXL','K1S150100002S0098V0065S','K1S150100002S0098V00653X')) and (`db_im2_01_prod`.`s`.`id` > 0) and (`db_im2_01_prod`.`s`.`saas_tenant_code` = 'baozun') and (`db_im2_01_prod`.`s`.`sku_code` = `db_im2_01_prod`.`k`.`code`) and (`db_im2_01_prod`.`k`.`saas_tenant_code` = 'baozun'))
sql_2 explain analyze:
*************************** 1. row ***************************
EXPLAIN: -> Nested loop left join (cost=55907.42 rows=1846) (actual time=0.379..32.557 rows=10128 loops=1)
-> Nested loop left join (cost=55722.61 rows=1846) (actual time=0.371..21.898 rows=10128 loops=1)
-> Nested loop inner join (cost=55536.52 rows=1846) (actual time=0.357..14.918 rows=10128 loops=1)
-> Filter: (k.saas_tenant_code = 'baozun') (cost=4.13 rows=1) (actual time=0.117..0.149 rows=6 loops=1)
-> Index range scan on k using idx_sku_extensioncode3, with index condition: ((k.extensionCode3 is not null) and (k.extensionCode3 in ('K1S150100002S0098V0065L','K1S150100002S0098V0065XL','K1S150100002S0098V0065M','K1S150100002S0098V0065XXL','K1S150100002S0098V0065S','K1S150100002S0098V00653X'))) (cost=4.13 rows=6) (actual time=0.116..0.145 rows=6 loops=1)
-> Index lookup on s using uniq_sale (saas_tenant_code='baozun', channel_code='STONEISLAND0S01') (cost=266.46 rows=3076) (actual time=0.138..2.379 rows=1688 loops=6)
-> Filter: ((l.location_code = s.location_code) and (l.im_organization = s.im_organization) and (l.saas_tenant_code = 'baozun')) (cost=0.27 rows=1) (actual time=0.000..0.001 rows=1 loops=10128)
-> Single-row index lookup on l using uniq_locationCode (location_code=s.location_code) (cost=0.27 rows=1) (actual time=0.000..0.000 rows=1 loops=10128)
-> Filter: ((cf.im_organization = convert(s.im_organization using utf8mb4)) and (cf.channel_code = convert(s.channel_code using utf8mb4)) and (cf.location_code = convert(s.location_code using utf8mb4)) and (cf.saas_tenant_code = 'baozun')) (cost=0.06 rows=1) (actual time=0.001..0.001 rows=0 loops=10128)
-> Table scan on cf (cost=0.06 rows=1) (actual time=0.001..0.001 rows=0 loops=10128)
Description: We have encountered a bug in MySQL 8.0.22. When executing the following two SQL statements, we get different results: SQL1 returns 1 row, which is correct, while SQL2 returns over 10,000 rows, which is incorrect. sql_1: SELECT s.id AS id, s.im_organization AS imOrganization, s.cw_code AS cwCode, s.location_code AS locationCode, s.qty AS qty, s.channel_code AS channelCode, k.extensionCode3 AS aggregationCode, s.sku_code AS skuCode, l.location_type AS locationType, 'bda245e6-9f94-42d9-82e7-1c9ad206d47e' AS batchCode, 1 AS status, 0 AS isManual, IFNULL(cf.ship_date, 0) AS shipDate, IFNULL(cf.inventory_type, 1) AS inventoryType FROM t_channel_sale_detail_4 s LEFT JOIN bi_sku k ON s.sku_code = k.code AND k.saas_tenant_code = 'baozun' AND s.saas_tenant_code = 'baozun' LEFT JOIN t_location_info l ON l.location_code = s.location_code AND l.im_organization = s.im_organization AND l.saas_tenant_code = 'baozun' LEFT JOIN t_inventory_presets_config cf ON cf.im_organization = s.im_organization AND cf.channel_code = s.channel_code AND cf.location_code = s.location_code AND cf.saas_tenant_code = 'baozun' WHERE s.channel_code = 'STONEISLAND0S01' AND s.im_organization = 'STONEISLANDKG' AND k.extensionCode3 IS NOT NULL AND k.extensionCode3 IN ( 'K1S150100002S0098V0065L', 'K1S150100002S0098V0065XL', 'K1S150100002S0098V0065M', 'K1S150100002S0098V0065XXL', 'K1S150100002S0098V0065S', 'K1S150100002S0098V00653X' ) AND s.id > 0 AND s.saas_tenant_code = 'baozun'; sql_2: only add force index() SELECT s.id AS id, s.im_organization AS imOrganization, s.cw_code AS cwCode, s.location_code AS locationCode, s.qty AS qty, s.channel_code AS channelCode, k.extensionCode3 AS aggregationCode, s.sku_code AS skuCode, l.location_type AS locationType, 'bda245e6-9f94-42d9-82e7-1c9ad206d47e' AS batchCode, 1 AS status, 0 AS isManual, IFNULL(cf.ship_date, 0) AS shipDate, IFNULL(cf.inventory_type, 1) AS inventoryType FROM t_channel_sale_detail_4 s force index(uniq_sale) LEFT JOIN bi_sku k ON s.sku_code = k.code AND k.saas_tenant_code = 'baozun' AND s.saas_tenant_code = 'baozun' LEFT JOIN t_location_info l ON l.location_code = s.location_code AND l.im_organization = s.im_organization AND l.saas_tenant_code = 'baozun' LEFT JOIN t_inventory_presets_config cf ON cf.im_organization = s.im_organization AND cf.channel_code = s.channel_code AND cf.location_code = s.location_code AND cf.saas_tenant_code = 'baozun' WHERE s.channel_code = 'STONEISLAND0S01' AND s.im_organization = 'STONEISLANDKG' AND k.extensionCode3 IS NOT NULL AND k.extensionCode3 IN ( 'K1S150100002S0098V0065L', 'K1S150100002S0098V0065XL', 'K1S150100002S0098V0065M', 'K1S150100002S0098V0065XXL', 'K1S150100002S0098V0065S', 'K1S150100002S0098V00653X' ) AND s.id > 0 AND s.saas_tenant_code = 'baozun' How to repeat: We observed the execution plan and found that a filter condition was missing : AND s.im_organization = 'STONEISLANDKG'. ------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------ SQL EXPALIN sql_1 explain: +----+-------------+-------+------------+--------+-----------------------------------------------------------+------------------------+---------+--------------------------------+-------+----------+-----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+-----------------------------------------------------------+------------------------+---------+--------------------------------+-------+----------+-----------------------------------------------------+ | 1 | SIMPLE | k | NULL | range | uk_sku_code,idx_sku_extensioncode3 | idx_sku_extensioncode3 | 753 | NULL | 6 | 10 | Using index condition; Using where; Using MRR | | 1 | SIMPLE | s | NULL | ALL | PRIMARY,uniq_sale,idx_query1,idx_sku,idx_query_optimize_1 | NULL | NULL | NULL | 30780 | 10 | Range checked for each record (index map: 0x73) | | 1 | SIMPLE | l | NULL | eq_ref | uniq_locationName,uniq_locationCode | uniq_locationCode | 62 | db_im2_01_prod.s.location_code | 1 | 100 | Using where; Using join buffer (Batched Key Access) | | 1 | SIMPLE | cf | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+--------+-----------------------------------------------------------+------------------------+---------+--------------------------------+-------+----------+-----------------------------------------------------+ 4 rows in set, 3 warnings (0.0016 sec) Warning (code 1739): Cannot use ref access on index 'idx_sku' due to type or collation conversion on field 'sku_code' Warning (code 1739): Cannot use ref access on index 'idx_query_optimize_1' due to type or collation conversion on field 'sku_code' Note (code 1003): /* select#1 */ select `db_im2_01_prod`.`s`.`id` AS `id`,`db_im2_01_prod`.`s`.`im_organization` AS `imOrganization`,`db_im2_01_prod`.`s`.`cw_code` AS `cwCode`,`db_im2_01_prod`.`s`.`location_code` AS `locationCode`,`db_im2_01_prod`.`s`.`qty` AS `qty`,`db_im2_01_prod`.`s`.`channel_code` AS `channelCode`,`db_im2_01_prod`.`k`.`extensionCode3` AS `aggregationCode`,`db_im2_01_prod`.`s`.`sku_code` AS `skuCode`,`db_im2_01_prod`.`l`.`location_type` AS `locationType`,'bda245e6-9f94-42d9-82e7-1c9ad206d47e' AS `batchCode`,1 AS `status`,0 AS `isManual`,ifnull(`db_im2_01_prod`.`cf`.`ship_date`,0) AS `shipDate`,ifnull(`db_im2_01_prod`.`cf`.`inventory_type`,1) AS `inventoryType` from `db_im2_01_prod`.`t_channel_sale_detail_4` `s` join `db_im2_01_prod`.`bi_sku` `k` left join `db_im2_01_prod`.`t_location_info` `l` on(((`db_im2_01_prod`.`l`.`location_code` = `db_im2_01_prod`.`s`.`location_code`) and (`db_im2_01_prod`.`l`.`im_organization` = `db_im2_01_prod`.`s`.`im_organization`) and (`db_im2_01_prod`.`l`.`saas_tenant_code` = 'baozun'))) left join `db_im2_01_prod`.`t_inventory_presets_config` `cf` on(((`db_im2_01_prod`.`cf`.`im_organization` = convert(`db_im2_01_prod`.`s`.`im_organization` using utf8mb4)) and (`db_im2_01_prod`.`cf`.`channel_code` = convert(`db_im2_01_prod`.`s`.`channel_code` using utf8mb4)) and (`db_im2_01_prod`.`cf`.`location_code` = convert(`db_im2_01_prod`.`s`.`location_code` using utf8mb4)) and (`db_im2_01_prod`.`cf`.`saas_tenant_code` = 'baozun'))) where ((`db_im2_01_prod`.`s`.`channel_code` = 'STONEISLAND0S01') and (`db_im2_01_prod`.`s`.`im_organization` = 'STONEISLANDKG') and (`db_im2_01_prod`.`k`.`extensionCode3` is not null) and (`db_im2_01_prod`.`k`.`extensionCode3` in ('K1S150100002S0098V0065L','K1S150100002S0098V0065XL','K1S150100002S0098V0065M','K1S150100002S0098V0065XXL','K1S150100002S0098V0065S','K1S150100002S0098V00653X')) and (`db_im2_01_prod`.`s`.`id` > 0) and (`db_im2_01_prod`.`s`.`saas_tenant_code` = 'baozun') and (`db_im2_01_prod`.`s`.`sku_code` = `db_im2_01_prod`.`k`.`code`) and (`db_im2_01_prod`.`k`.`saas_tenant_code` = 'baozun')) sql_1 explain analyze: *************************** 1. row *************************** EXPLAIN: -> Nested loop left join (cost=2406.86 rows=1847) (actual time=0.351..0.583 rows=1 loops=1) -> Nested loop left join (cost=2221.92 rows=1847) (actual time=0.343..0.574 rows=1 loops=1) -> Nested loop inner join (cost=2024.58 rows=1847) (actual time=0.326..0.557 rows=1 loops=1) -> Filter: (k.saas_tenant_code = 'baozun') (cost=4.13 rows=1) (actual time=0.142..0.171 rows=6 loops=1) -> Index range scan on k using idx_sku_extensioncode3, with index condition: ((k.extensionCode3 is not null) and (k.extensionCode3 in ('K1S150100002S0098V0065L','K1S150100002S0098V0065XL','K1S150100002S0098V0065M','K1S150100002S0098V0065XXL','K1S150100002S0098V0065S','K1S150100002S0098V00653X'))) (cost=4.13 rows=6) (actual time=0.141..0.168 rows=6 loops=1) -> Filter: ((s.channel_code = 'STONEISLAND0S01') and (s.im_organization = 'STONEISLANDKG') and (s.id > 0) and (s.saas_tenant_code = 'baozun') and (s.sku_code = k.`code`)) (cost=29263.53 rows=3078) (actual time=0.064..0.064 rows=0 loops=6) -> Index range scan on s (re-planned for each iteration) (cost=29263.53 rows=30780) (actual time=0.064..0.064 rows=0 loops=6) -> Filter: ((l.location_code = s.location_code) and (l.im_organization = s.im_organization) and (l.saas_tenant_code = 'baozun')) (cost=0.25 rows=1) (actual time=0.016..0.016 rows=1 loops=1) -> Single-row index lookup on l using uniq_locationCode (location_code=s.location_code) (cost=0.25 rows=1) (actual time=0.015..0.015 rows=1 loops=1) -> Filter: ((cf.im_organization = convert(s.im_organization using utf8mb4)) and (cf.channel_code = convert(s.channel_code using utf8mb4)) and (cf.location_code = convert(s.location_code using utf8mb4)) and (cf.saas_tenant_code = 'baozun')) (cost=0.01 rows=1) (actual time=0.007..0.007 rows=0 loops=1) -> Table scan on cf (cost=0.01 rows=1) (actual time=0.006..0.006 rows=0 loops=1) 1 row in set, 2 warnings (0.0018 sec) Warning (code 1739): Cannot use ref access on index 'idx_sku' due to type or collation conversion on field 'sku_code' Warning (code 1739): Cannot use ref access on index 'idx_query_optimize_1' due to type or collation conversion on field 'sku_code' sql_2 explain: +----+-------------+-------+------------+--------+-------------------------------------+------------------------+---------+--------------------------------+------+----------+---------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+-------------------------------------+------------------------+---------+--------------------------------+------+----------+---------------------------------------------------------------+ | 1 | SIMPLE | k | NULL | range | uk_sku_code,idx_sku_extensioncode3 | idx_sku_extensioncode3 | 753 | NULL | 6 | 10 | Using index condition; Using where; Using MRR | | 1 | SIMPLE | s | NULL | ref | uniq_sale | uniq_sale | 455 | const,const | 3076 | 0.33 | Using index condition; Using join buffer (Batched Key Access) | | 1 | SIMPLE | l | NULL | eq_ref | uniq_locationName,uniq_locationCode | uniq_locationCode | 62 | db_im2_01_prod.s.location_code | 1 | 100 | Using where; Using join buffer (Batched Key Access) | | 1 | SIMPLE | cf | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+--------+-------------------------------------+------------------------+---------+--------------------------------+------+----------+---------------------------------------------------------------+ 4 rows in set, 1 warning (0.0011 sec) Note (code 1003): /* select#1 */ select `db_im2_01_prod`.`s`.`id` AS `id`,`db_im2_01_prod`.`s`.`im_organization` AS `imOrganization`,`db_im2_01_prod`.`s`.`cw_code` AS `cwCode`,`db_im2_01_prod`.`s`.`location_code` AS `locationCode`,`db_im2_01_prod`.`s`.`qty` AS `qty`,`db_im2_01_prod`.`s`.`channel_code` AS `channelCode`,`db_im2_01_prod`.`k`.`extensionCode3` AS `aggregationCode`,`db_im2_01_prod`.`s`.`sku_code` AS `skuCode`,`db_im2_01_prod`.`l`.`location_type` AS `locationType`,'bda245e6-9f94-42d9-82e7-1c9ad206d47e' AS `batchCode`,1 AS `status`,0 AS `isManual`,ifnull(`db_im2_01_prod`.`cf`.`ship_date`,0) AS `shipDate`,ifnull(`db_im2_01_prod`.`cf`.`inventory_type`,1) AS `inventoryType` from `db_im2_01_prod`.`t_channel_sale_detail_4` `s` FORCE INDEX (`uniq_sale`) join `db_im2_01_prod`.`bi_sku` `k` left join `db_im2_01_prod`.`t_location_info` `l` on(((`db_im2_01_prod`.`l`.`location_code` = `db_im2_01_prod`.`s`.`location_code`) and (`db_im2_01_prod`.`l`.`im_organization` = `db_im2_01_prod`.`s`.`im_organization`) and (`db_im2_01_prod`.`l`.`saas_tenant_code` = 'baozun'))) left join `db_im2_01_prod`.`t_inventory_presets_config` `cf` on(((`db_im2_01_prod`.`cf`.`im_organization` = convert(`db_im2_01_prod`.`s`.`im_organization` using utf8mb4)) and (`db_im2_01_prod`.`cf`.`channel_code` = convert(`db_im2_01_prod`.`s`.`channel_code` using utf8mb4)) and (`db_im2_01_prod`.`cf`.`location_code` = convert(`db_im2_01_prod`.`s`.`location_code` using utf8mb4)) and (`db_im2_01_prod`.`cf`.`saas_tenant_code` = 'baozun'))) where ((`db_im2_01_prod`.`s`.`channel_code` = 'STONEISLAND0S01') and (`db_im2_01_prod`.`s`.`im_organization` = 'STONEISLANDKG') and (`db_im2_01_prod`.`k`.`extensionCode3` is not null) and (`db_im2_01_prod`.`k`.`extensionCode3` in ('K1S150100002S0098V0065L','K1S150100002S0098V0065XL','K1S150100002S0098V0065M','K1S150100002S0098V0065XXL','K1S150100002S0098V0065S','K1S150100002S0098V00653X')) and (`db_im2_01_prod`.`s`.`id` > 0) and (`db_im2_01_prod`.`s`.`saas_tenant_code` = 'baozun') and (`db_im2_01_prod`.`s`.`sku_code` = `db_im2_01_prod`.`k`.`code`) and (`db_im2_01_prod`.`k`.`saas_tenant_code` = 'baozun')) sql_2 explain analyze: *************************** 1. row *************************** EXPLAIN: -> Nested loop left join (cost=55907.42 rows=1846) (actual time=0.379..32.557 rows=10128 loops=1) -> Nested loop left join (cost=55722.61 rows=1846) (actual time=0.371..21.898 rows=10128 loops=1) -> Nested loop inner join (cost=55536.52 rows=1846) (actual time=0.357..14.918 rows=10128 loops=1) -> Filter: (k.saas_tenant_code = 'baozun') (cost=4.13 rows=1) (actual time=0.117..0.149 rows=6 loops=1) -> Index range scan on k using idx_sku_extensioncode3, with index condition: ((k.extensionCode3 is not null) and (k.extensionCode3 in ('K1S150100002S0098V0065L','K1S150100002S0098V0065XL','K1S150100002S0098V0065M','K1S150100002S0098V0065XXL','K1S150100002S0098V0065S','K1S150100002S0098V00653X'))) (cost=4.13 rows=6) (actual time=0.116..0.145 rows=6 loops=1) -> Index lookup on s using uniq_sale (saas_tenant_code='baozun', channel_code='STONEISLAND0S01') (cost=266.46 rows=3076) (actual time=0.138..2.379 rows=1688 loops=6) -> Filter: ((l.location_code = s.location_code) and (l.im_organization = s.im_organization) and (l.saas_tenant_code = 'baozun')) (cost=0.27 rows=1) (actual time=0.000..0.001 rows=1 loops=10128) -> Single-row index lookup on l using uniq_locationCode (location_code=s.location_code) (cost=0.27 rows=1) (actual time=0.000..0.000 rows=1 loops=10128) -> Filter: ((cf.im_organization = convert(s.im_organization using utf8mb4)) and (cf.channel_code = convert(s.channel_code using utf8mb4)) and (cf.location_code = convert(s.location_code using utf8mb4)) and (cf.saas_tenant_code = 'baozun')) (cost=0.06 rows=1) (actual time=0.001..0.001 rows=0 loops=10128) -> Table scan on cf (cost=0.06 rows=1) (actual time=0.001..0.001 rows=0 loops=10128)