Eric Guo's blog.cloud-mes.com

in HTML5, JavaScript, Ruby & Rails, Python, and Cloud MES!

Bug or Correct Behavior but Very Miss Leading LEFT JOIN in MySQL 8

Permalink

Today I found a missing rows in MySQL 8 problem in production, which SQL LEFT JOIN should be added at join condition instead of where.

Missing data version:

SELECT SUM(markettotal) markettotal
FROM `SUB_COMPANY_REAL_RECEIVE`
LEFT JOIN ORG_REPORT_DEPT_ORDER on ORG_REPORT_DEPT_ORDER.编号 = SUB_COMPANY_REAL_RECEIVE.deptcode_sum
WHERE `SUB_COMPANY_REAL_RECEIVE`.`realdate` BETWEEN '2020-01-01' AND '2020-06-30'
AND (ORG_REPORT_DEPT_ORDER.开始时间 <= '2020-06-11')
AND (ORG_REPORT_DEPT_ORDER.结束时间 IS NULL OR ORG_REPORT_DEPT_ORDER.结束时间 >= '2020-06-11')
AND `SUB_COMPANY_REAL_RECEIVE`.`orgcode_sum` IN ('H000109', '000109')

Correct version:

SELECT sum(markettotal)
FROM `SUB_COMPANY_REAL_RECEIVE`
LEFT JOIN ORG_REPORT_DEPT_ORDER ON SUB_COMPANY_REAL_RECEIVE.deptcode = ORG_REPORT_DEPT_ORDER.编号
AND ((ORG_REPORT_DEPT_ORDER.开始时间 <= '2020-06-11') AND (ORG_REPORT_DEPT_ORDER.结束时间 IS NULL OR ORG_REPORT_DEPT_ORDER.结束时间 >= '2020-06-11'))
WHERE `SUB_COMPANY_REAL_RECEIVE`.`realdate` BETWEEN '2020-01-01' AND '2020-06-30'
AND `SUB_COMPANY_REAL_RECEIVE`.`orgcode_sum` IN ('H000109')

Comments