What’s Wrong With This Query?

Words: 375
Time to read: ~2 minutes

Hey!

…

Now that I have your attention, can you see what’s wrong with this query?

Prep

Like every food recipe out there, I’m going to make you sit through backstory before you can get to the good stuff.

One of the fun and annoying aspects of performance tuning is making sure that the results of the tuned version match the results of the pre-tuned version.

In this case, tuning went great – it’s amazing how much faster you can get things done when you don’t waste resources – but the results didn’t match.

Now, I was sure that the queries were logically the same, so I pinged a developer who had worked on the query last, and asked them to confirm what the query was supposed to do:

We want all records where the second table T2 has the specific value in ID2, or the first table T1 has the specific value in ID
– Said developer

Well, great news for me! I made your query better and fixed a previous bug.

Now, can you figure out the bug?

Example

Here are your example tables: T1 and T2

USE [tempdb];
GO
DROP TABLE IF EXISTS
	[dbo].[T1],
	[dbo].[T2];
CREATE TABLE [dbo].[T1]
(
	[ID] INT NOT NULL
);
CREATE TABLE [dbo].[T2]
(
	[ID1] INT NOT NULL,
	[ID2] INT NULL
);
GO

Here’s some records for you:

USE [tempdb];
GO
INSERT INTO [dbo].[T1]
(
	[ID]
)
VALUES 
	(1),
	(2),
	(3),
	(4);
INSERT INTO [dbo].[T2]
(
	[ID1],
	[ID2]
)
VALUES
	(1, 1),
	(1, 2),
	(2, 2),
	(3, 1),
	(3, 2),
	(4, NULL);
GO

Here’s what the expected outcome should be (and what it was post-tuning):

And here’s what the outcome actually was pre-tuning:

Buggy Code

DECLARE @ID INT = 1;
SELECT
	[T1].[ID] AS [T1_ID],
	[T2].[ID1] AS [T2_ID1],
	[T2].[ID2] AS [T2_ID2]
FROM [dbo].[T1]
LEFT JOIN [dbo].[T2]
	ON
	(
		[T1].[ID] = [T2].[ID1]
		AND 
		[T2].[ID2] = @ID
	)
	OR 
	[T1].[ID] = @ID
WHERE
	(
		[T1].[ID] = @ID
		OR 
		[T2].[ID1] IS NOT NULL
	);

Sin É

So here’s the assignment!

• Which line or lines in the code causes the bug?
• Why?

It feels strange to sign off without explaining what the issue is, but I’ll return after a few days and fill this in.

Once people who love these kinds of questions get a chance to give it a go!

Ádh mór!