VERY strange behaviour with invalid subquery

Question

Post reply

VERY strange behaviour with invalid subquery

Atat Rowcount

Default port

Points: 1453
More actions
September 27, 2010 at 6:38 am

#387870

Select top 20 * from Database1..MY_TABLE (nolock)
Where THE_ID = (Select THE_ID from Database2..MY_TABLE_2 Where OTHER_FIELD = 'VALUE')
Order By MY_COL desc
Now....the subquery is invalid, there is no column THE_ID on Database2..MY_TABLE_2.
But when I run the above query, it gives me 20 rows from Database1..MY_TABLE. ANY 20 rows. The overall query does not fail.
Why? It would have failed in SQL2000 (from which I have recently migrated)

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Is there a CTE in here anywhere?

If there is, then this article is probably worth a read:

http://www.kbalertz.com/2322209/Incorrect-results-parentheses-around-SELECT-statement-common-table-expression-Server.aspx

Edit: found the link.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 2

@Brian: You say this certainly would throw an error on SQL2000. I just tried this on our last remaining SQL2K box, and it does indeeed. However, the error I am receiving isn't exactly what I would have expected:

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

[/color]

@chris-2: there doesn't need to be a CTE involved to produce this behaviour. I first encountered this the hard way in SQL2K5, and it is still there in both SQL2K8 and SQL2K8 R2. My way around has been to start writing my queries from the inside out whenever i need to have a subselect.

Is anyone else aware why SQL behaves this way?

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

steveb. SSC-Forever Points: 46733 More actions · Answer 3

I thought the issue is that the column does not exist in the table in the sub-query. Not that the sub-query returns mulitple rows.

It definetly throws an error in 2k8 r2 if the column does not exist in the sub-query so I am not sure why you are not getting an error.

Unless i have completly missed the point of the question.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

steveb. (9/27/2010)
I thought the issue is that the column does not exist in the table in the sub-query. Not that the sub-query returns mulitple rows.
It definetly throws an error in 2k8 r2 if the column does not exist in the sub-query so I am not sure why you are not getting an error.
Unless i have completly missed the point of the question.

Steve, try this:

WITH computed_table (id) AS

(SELECT id

FROM this_table_does_not_exist)

(SELECT id

FROM dbo.computed_table)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 5

steveb. (9/27/2010)
I thought the issue is that the column does not exist in the table in the sub-query. Not that the sub-query returns mulitple rows.
It definetly throws an error in 2k8 r2 if the column does not exist in the sub-query so I am not sure why you are not getting an error.
Unless i have completly missed the point of the question.

No you did NOT miss the point. The column in the sub-select does indeed not exist, but SQL2K throws the 'More than one rows' error where there should be none. I would have excpected and 'Invalid column' error instead.

How did you test this in SQL2K R2? I tried two versions. In the first case I select from a user table with a sub-select from another user table where the column does not exist. In that case SQL returns all rows from the outer select. In the second case I do the same thing, but the two tables involved are system tables (sysobjects and syscolumns to be precise). In that case I DO receive and 'Invalid column name' error.

All very weird.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

steveb. SSC-Forever Points: 46733 More actions · Answer 6

Chris Morris-439714 (9/27/2010)
steveb. (9/27/2010)
I thought the issue is that the column does not exist in the table in the sub-query. Not that the sub-query returns mulitple rows.
It definetly throws an error in 2k8 r2 if the column does not exist in the sub-query so I am not sure why you are not getting an error.
Unless i have completly missed the point of the question.
Steve, try this:
WITH computed_table (id) AS
(SELECT id
FROM this_table_does_not_exist)
(SELECT id
FROM dbo.computed_table)

Interesting, I have not seen this before...

Atat Rowcount Default port Points: 1453 More actions · Answer 7

Hi

Thanks for the replies.

No CTEs, and yes I would have expected SQL to return the multiple rows error. But I would *not* have expected it to *not* warn me and to return results which do not match my query.

Any other takers?

Brian

steveb. SSC-Forever Points: 46733 More actions · Answer 8

Jan Van der Eecken (9/27/2010)
steveb. (9/27/2010)
I thought the issue is that the column does not exist in the table in the sub-query. Not that the sub-query returns mulitple rows.
It definetly throws an error in 2k8 r2 if the column does not exist in the sub-query so I am not sure why you are not getting an error.
Unless i have completly missed the point of the question.
No you did NOT miss the point. The column in the sub-select does indeed not exist, but SQL2K throws the 'More than one rows' error where there should be none. I would have excpected and 'Invalid column' error instead.
How did you test this in SQL2K R2? I tried two versions. In the first case I select from a user table with a sub-select from another user table where the column does not exist. In that case SQL returns all rows from the outer select. In the second case I do the same thing, but the two tables involved are system tables (sysobjects and syscolumns to be precise). In that case I DO receive and 'Invalid column name' error.
All very weird.

I tested it by running this code in R2.

select top 10 * from FactStock (nolock)

where StockDate = (select ColDoesNotExist from DimDate where [DATE] = '1 jan 2010')

order by StockDate

and i get the error;

Msg 207, Level 16, State 1, Line 2

Invalid column name 'ColDoesNotExist'.

Maybe there is an option that turns this on/off or something?

krishnakumar-293737 SSC Enthusiast Points: 176 More actions · Answer 9

Select top 20 * from Database1..MY_TABLE (nolock)

Where THE_ID = (Select THE_ID from Database2..MY_TABLE_2 Where OTHER_FIELD = 'VALUE')

Order By MY_COL desc

Now....the subquery is invalid, there is no column THE_ID on Database2..MY_TABLE_2.

But when I run the above query, it gives me 20 rows from Database1..MY_TABLE. ANY 20 rows. The overall query does not fail.

Why? It would have failed in SQL2000 (from which I have recently migrated)

The subquery Select THE_ID from Database2..MY_TABLE_2 Where OTHER_FIELD = 'VALUE'

my_table_2 might contain one row with other_field='value' . I think other_field to my_table_2.

if outer query field is referenced in a subquery query the subquery will fire for n outer no of rows.

So that is the reason why you get 20 rows. If the subquery returns 2 rows then your query will fail.

Atat Rowcount Default port Points: 1453 More actions · Answer 10

"my_table_2 might contain one row with other_field='value' . I think other_field to my_table_2.

if outer query field is referenced in a subquery query the subquery will fire for n outer no of rows.

So that is the reason why you get 20 rows. If the subquery returns 2 rows then your query will fail."

-----> No, this is not the case.

MY_TABLE_2 does not have any rows with OTHER_FIELD = 'VALUE'. The subquery is nonsense, as it is selecting an invalid column. My query should not have succeeded and returned 20 random rows of data.

Hence the question, which thus far no-one has been able to answer! Closest thing I've seen to an answer is the poster who says that there might be a setting in terms of behaviour.... If anyone was able to shed any light on it, that would be great.

I'm writing SQL for over 15 years and have never seen an incorrect result like this from a prominent database engine.

Brian

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

krishnakumar-293737 (9/27/2010)
Select top 20 * from Database1..MY_TABLE (nolock)
Where THE_ID = (Select THE_ID from Database2..MY_TABLE_2 Where OTHER_FIELD = 'VALUE')
Order By MY_COL desc
Now....the subquery is invalid, there is no column THE_ID on Database2..MY_TABLE_2.
But when I run the above query, it gives me 20 rows from Database1..MY_TABLE. ANY 20 rows. The overall query does not fail.
Why? It would have failed in SQL2000 (from which I have recently migrated)
The subquery Select THE_ID from Database2..MY_TABLE_2 Where OTHER_FIELD = 'VALUE'
my_table_2 might contain one row with other_field='value' . I think other_field to my_table_2.
if outer query field is referenced in a subquery query the subquery will fire for n outer no of rows.
So that is the reason why you get 20 rows. If the subquery returns 2 rows then your query will fail.

In the absence of table aliases to determine the table source for each column, SQL Server assumes that THE_ID in the subquery is sourced from the outer FROM list. Hence the filter becomes WHERE THE_ID = THE_ID AND EXISTS (SELECT 1 FROM Database2..MY_TABLE_2 t2 Where t2.OTHER_FIELD = 'VALUE').

Look at this statement:

WHERE EXISTS (Select THE_ID, t2.OTHER_FIELD from Database2..MY_TABLE_2 t2 Where t2.OTHER_FIELD = 'VALUE'). While not particularly useful, it's valid.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Chris Morris-439714 (9/27/2010)
krishnakumar-293737 (9/27/2010)
Select top 20 * from Database1..MY_TABLE (nolock)
Where THE_ID = (Select THE_ID from Database2..MY_TABLE_2 Where OTHER_FIELD = 'VALUE')
Order By MY_COL desc
Now....the subquery is invalid, there is no column THE_ID on Database2..MY_TABLE_2.
But when I run the above query, it gives me 20 rows from Database1..MY_TABLE. ANY 20 rows. The overall query does not fail.
Why? It would have failed in SQL2000 (from which I have recently migrated)
The subquery Select THE_ID from Database2..MY_TABLE_2 Where OTHER_FIELD = 'VALUE'
my_table_2 might contain one row with other_field='value' . I think other_field to my_table_2.
if outer query field is referenced in a subquery query the subquery will fire for n outer no of rows.
So that is the reason why you get 20 rows. If the subquery returns 2 rows then your query will fail.
In the absence of table aliases to determine the table source for each column, SQL Server assumes that THE_ID in the subquery is sourced from the outer FROM list. Hence the filter becomes WHERE THE_ID = THE_ID AND EXISTS (SELECT 1 FROM Database2..MY_TABLE_2 t2 Where t2.OTHER_FIELD = 'VALUE').
Look at this statement:
WHERE EXISTS (Select THE_ID, t2.OTHER_FIELD from Database2..MY_TABLE_2 t2 Where t2.OTHER_FIELD = 'VALUE'). While not particularly useful, it's valid.

Precisely!

Completely useless, yet valid hence making that query run without errors.

Another reason to ALWAYS alias your tables and columns.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Brian McGee-355525 (9/27/2010)
MY_TABLE_2 does not have any rows with OTHER_FIELD = 'VALUE'. ...

Check that this is indeed the case. Using 2k8R1, the outer query returns 20 rows as if the subquery does not exist, provided that the subquery returns rows i.e. the filter within it finds a match. With no matches in the subquery, the outer query returns no data.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

steveb. (9/27/2010)
Chris Morris-439714 (9/27/2010)
steveb. (9/27/2010)
I thought the issue is that the column does not exist in the table in the sub-query. Not that the sub-query returns mulitple rows.
It definetly throws an error in 2k8 r2 if the column does not exist in the sub-query so I am not sure why you are not getting an error.
Unless i have completly missed the point of the question.
Steve, try this:
WITH computed_table (id) AS
(SELECT id
FROM this_table_does_not_exist)
(SELECT id
FROM dbo.computed_table)
Interesting, I have not seen this before...

If the column exists in the OUTER query , but not in the inner query, the subquery will pull form the outer query to field this request. It's in the same way it would be used in a correlated sub-query. In this kind of scenario, the only way you get the error is to fully qualify the columns in the sub-query.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?