Explanation of spurious result from WHERE clause

user_8243753 0
Viewed 70

MySQL 8. I'm seeing a spurious row returned in a query, due to missing specification of table name. I know how to fix it, I'd like to understand why it occurs.

Please see following fiddle:

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

The purpose is to find any rows in the Scheduled Table that also exist in the Planned table, matched by SubscriptionID and date.

The results returned are:

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

However the WHERE clause should limit the results to just the first row (CustomerID 5 / SubscriptionID 13).

I know the solution to fix this and return the correct rows is to specify the table in the WHERE as per following query:

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

But I want to understand why the original query (as per fiddle) returns a spurious row for CustomerID 17 / SubscriptionID 23, seeing as a row with this combo of date and SubscriptionID doesn't exist in tblScheduled. It's clearly to do with the duplicate SubscriptionID column name in the 2 tables, but I can't understand the mysql execution logic that causes it.

(question is deliberately short due to previous complaints about too much detail. Ask me if you need further data etc).

Thank you.

Schema and data as per 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
subquery mysql sql
2 Answers

The default place MySQL will look for a column in a subquery is the table from which you are querying so 

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

is effectively the same as:

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

which is equivalent to

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

which explains the result you are getting since the row in tblScheduled with DateScheduled='2019-01-14 00:00:00' matches two rows in tblPlanned (those with [CustomerID, SubscriptionID] = [5,13] and [17,23]).

user_94737640

"find any rows in the Scheduled Table that also exist in the Planned table"

This is precisely what an INNER JOIN is for:

SELECT * 
FROM tblPlanned 
INNER JOIN tblScheduled 
  ON tblScheduled.SubscriptionID = tblPlanned.SubscriptionID 
  AND tblScheduled.DateScheduled = tblPlanned.RealWorldVisitDate;

This is much clearer and a more efficient way to match the rows, it's the standard thing to do in SQL in this kind of scenario.

Your original attempt was flawed partly by not using this approach, but also because of the ambiguity over which SubscriptionID field to use in the subquery - by default it matches to the table in the subquery, so you're just getting results where the field matches itself.

user_59470430
Related