mysql - WHERE 子句的虚假结果的解释

Question

MySQL 8。由于缺少表名规范，我看到查询中返回了一个虚假行。我知道如何修复它，我想了解它发生的原因。

请看下面的 fiddle :

http://sqlfiddle.com/#!9/beb1ed/1/0

目的是找到计划表中也存在于计划表中的任何行，与 SubscriptionID 和日期相匹配。

返回的结果是:

RealWorldVisitDate      CustomerID      SubscriptionID
2019-01-14 00:00:00     5               13  
2019-01-14 00:00:00     17              23  

但是 WHERE 子句应将结果限制为仅第一行 (CustomerID 5/SubscriptionID 13)。

我知道解决此问题并返回正确行的解决方案是根据以下查询在 WHERE 中指定表:

SELECT * FROM tblPlanned WHERE ( SELECT COUNT(1) FROM tblScheduled WHERE tblScheduled.SubscriptionID = **tblPlanned.**SubscriptionID AND tblScheduled.DateScheduled = RealWorldVisitDate ) > 0

但我想了解为什么原始查询(根据 fiddle )为 CustomerID 17/SubscriptionID 23 返回一个虚假行，将日期和 SubscriptionID 组合的行视为不存在在表中预定。明明是2个表的SubscriptionID列名重复，但是mysql的执行逻辑不明白导致的。

(由于之前提示太多细节，问题故意简短。问我是否需要更多数据等)。

谢谢。

---

根据 fiddle 的架构和数据:

CREATE TABLE `tblScheduled` (
  `ScheduledTargetID` bigint(20) NOT NULL,
  `DateScheduled` datetime NOT NULL,
  `CustomerID` int(10) UNSIGNED NOT NULL,
  `SubscriptionID` int(10) UNSIGNED NOT NULL
);

INSERT INTO `tblScheduled` (`ScheduledTargetID`, `DateScheduled`, `CustomerID`, `SubscriptionID`) VALUES
(25, '2018-11-19 00:00:00', 16, 15),
(24, '2018-11-19 00:00:00', 17, 23),
(27, '2018-11-23 00:00:00', 5, 1),
(26, '2018-11-23 00:00:00', 14, 18),
(23, '2019-01-14 00:00:00', 5, 13);

CREATE TABLE `tblPlanned` (
  `RealWorldVisitDate` datetime DEFAULT NULL,
  `CustomerID` int(10) UNSIGNED DEFAULT NULL,
  `SubscriptionID` int(10) UNSIGNED DEFAULT NULL
);

INSERT INTO `tblPlanned` (`RealWorldVisitDate`, `CustomerID`, `SubscriptionID`) VALUES
('2019-01-15 00:00:00', 5, 4),
('2019-01-14 00:00:00', 5, 13),
('2019-01-28 00:00:00', 5, 27),
('2019-01-14 00:00:00', 17, 23),
('2019-02-11 00:00:00', 17, 23);


SELECT * FROM tblPlanned WHERE ( SELECT COUNT(1) FROM tblScheduled WHERE tblScheduled.SubscriptionID = SubscriptionID AND tblScheduled.DateScheduled = RealWorldVisitDate ) > 0

Answer 1

MySQL 将在子查询中查找列的默认位置是您正在查询的表

SELECT COUNT(1) 
FROM tblScheduled
WHERE tblScheduled.SubscriptionID = SubscriptionID AND tblScheduled.DateScheduled = RealWorldVisitDate

实际上等同于:

SELECT COUNT(1) 
FROM tblScheduled
WHERE tblScheduled.SubscriptionID = tblScheduled.SubscriptionID AND tblScheduled.DateScheduled = RealWorldVisitDate

相当于

SELECT COUNT(1) 
FROM tblScheduled 
WHERE tblScheduled.DateScheduled = RealWorldVisitDate

这解释了您得到的结果，因为 tblScheduled 中带有 DateScheduled='2019-01-14 00:00:00' 的行匹配 中的两行>tblPlanned([CustomerID, SubscriptionID] = [5,13] 和 [17,23])。