MySQL Bugs: #112192: In MySQL 8.0, one queries need 1000 times more data than MySQL 5.7

Bug #112192	In MySQL 8.0, one queries need 1000 times more data than MySQL 5.7
Submitted:	28 Aug 2023 2:55	Modified:	30 Aug 2023 8:36
Reporter:	linkang zhang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Optimizer bug

Description:
# When we select some data in MySQL 8.0.28, we need to scan 370 million lines of data.
# But in MySQL 5.7, we just need 370,000 lines of data.
# a thousand times the difference.

# the query is :

SELECT
distinct a.*
FROM
sp_supplier AS a
LEFT JOIN sp_productline_partner AS b on a.supplier_code = b.supplier_code
LEFT JOIN sp_supplier_payinfo AS d on d.supplier_code = a.supplier_code
LEFT JOIN sp_product_line AS c ON b.productId = c.id
LEFT JOIN sp_supplier_op_log AS e on a.supplier_code = e.supplier_code
LEFT JOIN sp_user_relation AS f on a.supplier_code = f.supplier_code
WHERE
b.is_delete = 0
order by
a.addtime desc,
a.supplier_code desc
LIMIT
0, 20;

# the table structure is in the next module 'How to repeat'

How to repeat:
// show create table sp_supplier

CREATE TABLE `sp_supplier` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `supplier_cate` tinyint(1) DEFAULT NULL COMMENT '?????(1:???2:???3:???)',
  `supplier_code` int NOT NULL COMMENT '???code',
  `supplier_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '?????',
  `supplier_alias` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '?????',
  `supplier_source` tinyint(1) NOT NULL COMMENT '???????',
  `supplier_degreen` tinyint(1) NOT NULL COMMENT '???????',
  `supplier_level` tinyint(1) NOT NULL DEFAULT '1' COMMENT '??(1,2,3,4...)',
  `supplier_status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '?????(1:??,2:??,3:?????,4:?????,5:?????,6:??,7:???)',
  `product_line` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '???',
  `category_id` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '??',
  `express_currency` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '??????????????????',
  `legalman` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '????',
  `regcapital` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '????',
  `delivery` int NOT NULL DEFAULT '0' COMMENT '??(?)',
  `priceadvantage` decimal(10,3) NOT NULL DEFAULT '0.000' COMMENT '????(%)',
  `linkman` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '???',
  `link_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?????',
  `link_province` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?????',
  `link_city` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?????',
  `link_district` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?????',
  `link_address` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '???????',
  `tel` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '??(????)',
  `mobile` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '??',
  `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '??',
  `wechat` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '???',
  `QQ` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'QQ?',
  `aliWW` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?????',
  `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '??',
  `province` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?',
  `city` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?',
  `district` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?',
  `address` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '????',
  `shop_link` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '????(?json????)',
  `taxpayer_date` bigint DEFAULT NULL COMMENT '?????????',
  `sku_prefix` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '??????(??Id)',
  `limitmoney` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '??,??',
  `alertmoney` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '????,??',
  `is_signed` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
  `expired` bigint NOT NULL DEFAULT '0' COMMENT '??????',
  `limitleft` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '????',
  `is_sendsms` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
  `is_sendemail` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
  `auditor` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '???',
  `audittime` int NOT NULL DEFAULT '0' COMMENT '????',
  `auditnote` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '????',
  `note` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '??????',
  `overseaAddr` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '?????????,json???',
  `is_taxpayer` tinyint(1) NOT NULL COMMENT '?????(1:?,0:?)',
  `is_brand` tinyint(1) DEFAULT NULL COMMENT '?????(1:?,0:?)',
  `pur_patterns` tinyint DEFAULT NULL COMMENT '????id',
  `adduser` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '???',
  `addtime` int NOT NULL DEFAULT '0' COMMENT '????',
  `updateuser` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '???????',
  `updatetime` int NOT NULL DEFAULT '0' COMMENT '????????',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
  `inspect_time` int NOT NULL DEFAULT '0' COMMENT '????????',
  `is_insalert` tinyint(1) NOT NULL DEFAULT '0' COMMENT '??????(1:?,0:?)',
  `module_id` tinyint NOT NULL DEFAULT '0' COMMENT '????',
  `cate_manager` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '????',
  `main_manager` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '?????',
  `main_manager_info` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '???????',
  `second_manager_info` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '???????',
  `pur_status` tinyint unsigned NOT NULL COMMENT '????',
  `manage_type` int unsigned NOT NULL COMMENT '????',
  `supplier_shortName` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '?????',
  `is_entry_date` tinyint(1) DEFAULT NULL COMMENT '??????????(0:?,1:?)',
  `supplier_data_submitter` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '????????',
  `corporatemobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '??????',
  `supplierStore` tinyint DEFAULT '1' COMMENT '1:??? 2:???',
  `oalink` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'oa??',
  PRIMARY KEY (`id`),
  UNIQUE KEY `supplier_code` (`supplier_code`),
  KEY `supplier_status` (`supplier_status`) USING BTREE,
  KEY `pur_status` (`pur_status`) USING BTREE,
  KEY `supplier_name` (`supplier_name`)
) ENGINE=InnoDB AUTO_INCREMENT=49726 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

// show create table sp_productline_partner

CREATE TABLE `sp_productline_partner` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `productId` int NOT NULL COMMENT '??id,sp_product_line?id',
  `partnerId` int DEFAULT '0' COMMENT '???id',
  `supplier_code` int NOT NULL COMMENT '?????',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(0:?,1:?)',
  PRIMARY KEY (`id`),
  KEY `catid` (`productId`),
  KEY `partnerid` (`partnerId`),
  KEY `isdel` (`is_delete`),
  KEY `sucode` (`supplier_code`)
) ENGINE=InnoDB AUTO_INCREMENT=58173 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

// show create table sp_supplier_payinfo

CREATE TABLE `sp_supplier_payinfo` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `supplier_code` int NOT NULL COMMENT '???code',
  `entity` smallint DEFAULT NULL COMMENT '??id',
  `billtype` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:??,2:??,3:??)',
  `paycycle` int NOT NULL DEFAULT '0' COMMENT '????(sp_supplier_paycycle?id)',
  `currency` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '????',
  `isDrawback` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
  `invoicepoint` decimal(4,2) NOT NULL DEFAULT '0.00' COMMENT '????,??',
  `compoint` decimal(4,2) NOT NULL DEFAULT '0.00' COMMENT '??????,??',
  `paymethod` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '????(??)',
  `is_default` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????????(1:?,0:?)',
  `receiveInfo` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '??????(json)',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
  `is_available` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
  `idcardInfo` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '?????',
  `whiteList` tinyint DEFAULT '0' COMMENT '??? 0:? 1:?',
  PRIMARY KEY (`id`),
  KEY `code` (`supplier_code`),
  KEY `entity` (`entity`) USING BTREE,
  KEY `is_delete` (`is_delete`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=117465 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

// show create table sp_product_line

CREATE TABLE `sp_product_line` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '????',
  `lineName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0' COMMENT '?????',
  `lineCode` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0' COMMENT '?????',
  `response` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0' COMMENT '???',
  `members` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '????',
  `type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1soft,2hard',
  `is_delete` tinyint NOT NULL DEFAULT '0' COMMENT '????',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

// show create table sp_supplier_op_log
CREATE TABLE `sp_supplier_op_log` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `partnerId` int DEFAULT NULL COMMENT '??????id',
  `supplier_code` int NOT NULL COMMENT '?????',
  `newstatus` tinyint(1) NOT NULL COMMENT '?????(???)',
  `oldstatus` tinyint(1) NOT NULL COMMENT '?????(???)',
  `optype` tinyint(1) NOT NULL COMMENT '????(1:?????,2??????,3:??,4:??,5:??)',
  `opuser` int NOT NULL COMMENT '???',
  `optime` int NOT NULL DEFAULT '0' COMMENT '????',
  `opnote` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '????(?????, ????)',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '????(1:?,0:?)',
  `change` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '??????',
  PRIMARY KEY (`id`),
  KEY `optype` (`optype`),
  KEY `optime` (`optime`),
  KEY `opuser` (`opuser`),
  KEY `suppliercode` (`supplier_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1308054 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

// show create table sp_user_relation
CREATE TABLE `sp_user_relation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `supplier_code` int NOT NULL,
  `module_id` tinyint NOT NULL,
  `user_id` int NOT NULL,
  `user_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_special` tinyint NOT NULL DEFAULT '0',
  `is_delete` tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `supplier_code` (`supplier_code`,`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1610881 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

// the count(*) of tables is as follows:

select count(*) from sp_supplier;
+----------+
| count(*) |
+----------+
|    49259 |
+----------+
1 row in set (0.00 sec)

select count(*) from sp_productline_partner;
+----------+
| count(*) |
+----------+
|    57957 |
+----------+
1 row in set (0.01 sec)

select count(*) from sp_supplier_payinfo;
+----------+
| count(*) |
+----------+
|   116058 |
+----------+
1 row in set (0.02 sec)

select count(*) from sp_product_line;
+----------+
| count(*) |
+----------+
|       34 |
+----------+
1 row in set (0.00 sec)

select count(*) from sp_supplier_op_log;
+----------+
| count(*) |
+----------+
|  1237805 |
+----------+
1 row in set (0.12 sec)

select count(*) from sp_user_relation;
+----------+
| count(*) |
+----------+
|  1598289 |
+----------+
1 row in set (0.18 sec)

// the explain of MySQL 5.7 and 8.0 is the same:

mysql> explain SELECT
    -> distinct a.*
    -> FROM
    -> sp_supplier AS a
    -> LEFT JOIN sp_productline_partner AS b on a.supplier_code = b.supplier_code
    -> LEFT JOIN sp_supplier_payinfo AS d on d.supplier_code = a.supplier_code
    -> LEFT JOIN sp_product_line AS c ON b.productId = c.id
    -> LEFT JOIN sp_supplier_op_log AS e on a.supplier_code = e.supplier_code
    -> LEFT JOIN sp_user_relation AS f on a.supplier_code = f.supplier_code
    -> WHERE
    -> b.is_delete = 0
    -> order by
    -> a.addtime desc,
    -> a.supplier_code desc
    -> LIMIT
    -> 0, 20;
+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------------------+-------+----------+---------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key           | key_len | ref                          | rows  | filtered | Extra                           |
+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------------------+-------+----------+---------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | supplier_code | NULL          | NULL    | NULL                         | 48331 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | b     | NULL       | ref    | isdel,sucode  | sucode        | 4       | supplier_new.a.supplier_code |     1 |    50.00 | Using where; Distinct           |
|  1 | SIMPLE      | d     | NULL       | ref    | code          | code          | 4       | supplier_new.a.supplier_code |     2 |   100.00 | Using index; Distinct           |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY       | PRIMARY       | 4       | supplier_new.b.productId     |     1 |   100.00 | Using index; Distinct           |
|  1 | SIMPLE      | e     | NULL       | ref    | suppliercode  | suppliercode  | 4       | supplier_new.a.supplier_code |    25 |   100.00 | Using index; Distinct           |
|  1 | SIMPLE      | f     | NULL       | ref    | supplier_code | supplier_code | 4       | supplier_new.a.supplier_code |    44 |   100.00 | Using index; Distinct           |
+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------------------+-------+----------+---------------------------------+

// But in fact, MySQL 8.0 needs scan 370 million lines, but MySQL 5.7 needs 0.37 million lines.

// The optimizer_switch in MySQL 8.0 is:
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=off,hash_join=off,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=off

// The optimizer_switch in MySQL 5.7 is:
SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on

// we can see, the optimizer_switch is the same in MySQL 8.0 and MySQL 5.7
// and the query plan is the same.
// but in fact, MySQL 8.0 need 1000 times more data than MySQL 5.7

// The trace of MySQL 5.7 and 8.0 is in the .txt file.

// why the query plan is consistent, but the trace is inconsistent

// I think this is a bug of optimizer

This is the trace of MySQL 5.7

Attachment: 5.7 optimizer_trace.txt (text/plain), 66.32 KiB.

The trace of MySQL 8.0

Attachment: MySQL 8.0 trace.txt (text/plain), 65.84 KiB.

Hello linkang zhang,

Thank you for the report and feedback.
Could you please provide exact test case(logical dump from mysqldump, If the data you need to attach is more than 3MB, you should create a compressed archive of the data and a README file that describes the data with a filename that includes the bug number (recommended filename: mysql-bug-data-112192.zip) and upload one to sftp.oracle.com). Thank you.

regards,
Umesh

For now verifying but please make sure to upload requested details which require us in order to confirm the issue at our end.

Also, Dev's have suggested that you can use the below workaround -

SELECT a.*
FROM sp_supplier AS a
WHERE a.supplier_code IN
  (SELECT b.supplier_code
  FROM sp_productline_partner AS b
WHERE b.is_delete = 0
  )
order by a.addtime desc, a.supplier_code desc
LIMIT 0, 20;

Could you please check and confirm if this workaround works for you? Thank you.

regards,
Umesh Shastry

Yes, Thank you.
It works in MySQL 8.0, and it just cost 0.28s

Thank you for confirming that dev's recommended workaround works for you.

regards,
Umesh Shastry

I want to know the reason why this problem happen ?
Please developer give a reason, thank you very much.

The query follows a special pattern:

  SELECT distinct a.*
  FROM a LEFT JOIN b ON ...
         LEFT JOIN d ON ...
         LEFT JOIN c ON ...
         LEFT JOIN e ON ...
         LEFT JOIN f ON ...
  WHERE b.is_delete = 0
  ...

Notice that columns are only selected from table a, effectively this means that we only need to check for existence of any qualifying rows in b, d, c, e and f.

In addition, these other tables are left-joined to table a, which further means that they are null-extended, thus they will always supply at least one row, meaning that the previous condition is always fulfilled (they will always have at least one qualifying row).

Except that table b has a condition in the WHERE clause (b.is_delete = 0) which cannot be true for the null-extended row, and effectively means that the LEFT JOIN on b can be rewritten as an INNER JOIN.

But now we have determined that all other LEFT JOIN operations are redundant, leaving us with:

  SELECT distinct a.*
  FROM a INNER JOIN b ON ...
  WHERE b.is_delete = 0

This is very close to a semi-join since no columns are selected from table b, so a further rewrite is

  SELECT distinct a.* FROM a WHERE a.x IN (SELECT b.y FROM b WHERE b.is_delete=0)

If the rows from a are all distinct, we can also eliminate the distinct clause:

  SELECT a.* FROM a WHERE a.x IN (SELECT b.y FROM b WHERE b.is_delete = 0)