Bug #104629 wrong result when outer join prune partition tables with is null predicate

• Submitted: 16 Aug 2021 3:08
• Modified: 16 Aug 2021 7:07
• Reporter: lou shuai (OCA)
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.25,5.7.35, 8.0.26
• OS: Any
• CPU Architecture: Any

[16 Aug 2021 3:08] lou shuai

Description:
select t1 left join (select * from t2) b on xx where b.c is null.

t2 is a partition table, and the where condition `b.c is null` will used to prune_partitions which make the inner table empty.

so the result set will be `select t1 left join empty table`.

How to repeat:
CREATE TABLE `bmsql_district` (
   `d_w_id` int NOT NULL,
   `d_id` int NOT NULL,
   `d_ytd` decimal(12,2) DEFAULT NULL,
   `d_tax` decimal(4,4) DEFAULT NULL,
   `d_next_o_id` int DEFAULT NULL,
   `d_name` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `d_street_1` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `d_street_2` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `d_city` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `d_state` char(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `d_zip` char(9) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`d_w_id`,`d_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 /*!50100 PARTITION BY HASH (`d_w_id`)
 PARTITIONS 24 */;

CREATE TABLE `bmsql_warehouse` (
   `w_id` int NOT NULL,
   `w_ytd` decimal(12,2) DEFAULT NULL,
   `w_tax` decimal(4,4) DEFAULT NULL,
   `w_name` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `w_street_1` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `w_street_2` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `w_city` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `w_state` char(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `w_zip` char(9) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`w_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 /*!50100 PARTITION BY HASH (`w_id`)
 PARTITIONS 24 */;

insert into bmsql_warehouse(w_id, w_ytd) values(1,  1010227919.37);
insert into bmsql_district(d_w_id, d_id, d_ytd) values(1,1, 1010227919.37);

select * from (
SELECT d_w_id,
          sum(d_ytd) sdytd
   FROM bmsql_district
   GROUP BY d_w_id
) a left join (
SELECT w_id, w_ytd
   FROM bmsql_warehouse) b
on a.d_w_id = b.w_id and a.sdytd=b.w_ytd where b.w_id is null;

Suggested fix:
Maybe two ways to fix it:

1. for the inner table of an outer join, not call prune_partitions. 
2. inside prune_partitions, disable is null condition prune.

[16 Aug 2021 6:17] lou shuai

select * from (
SELECT d_w_id,
          sum(d_ytd) sdytd
   FROM bmsql_district
   GROUP BY d_w_id
) a left join (
SELECT w_id, w_ytd
   FROM bmsql_warehouse) b
on a.d_w_id = b.w_id and a.sdytd=b.w_ytd where b.w_id is null;

should return empty, but got one row.

[16 Aug 2021 7:07] MySQL Verification Team

Hello lou shuai,

Thank you for the report and contribution.

regards,
Umesh