Query for columns that do not exist is not interrupted
Strange situation.
If I have these tables:
CREATE TABLE t1 (id INT, title VARCHAR(20), someIntCol INT)
GO
CREATE TABLE t2 (id INT, t1Id INT, someData VARCHAR(20))
GO
and run:
SELECT t1.*
FROM t1
WHERE t1.id IN (SELECT someIntCol FROM t2)
strange thing: the parser doesn't mind if the someIntCol column doesn't exist
weirdest thing: if I change someIntCol to someIntCol2 I get "Invalid column name" someIntCol2 ". error
Can anyone explain this?
FYI, this is not my code. I got it from this link
source to share
Try:
SELECT t1.*
FROM t1
WHERE t1.id IN (SELECT t2.someIntCol FROM t2)
This will now happen when the exception is executed.
Since it someIntCol
exists in t1
, it uses this item from the main request.
Subqueries can use items from the main query. Therefore, to avoid this, specify the table name when entering the item:
SELECT [TableName].[ColumnName]
This will also prevent ambiguity if you have 2 columns with the same name in t1
andt2
Gets MSDN which can give you a better idea of how subqueries work:
http://msdn.microsoft.com/en-us/library/aa213262(v=sql.80).aspx
source to share
In SQL Server, the parsing step only semantically checks the syntax for things like unescaped columns that start with numbers like [2ndColumn]
and not 2ndColumn
(which won't handle parsing), keywords, etc. and ensures that the request can be parsed. This is the process that happens when you click the parsing button in SSMS. This process does not validate the underlying data request.
So I can enter SELECT abc FROM DEF
even though I don't have a column named abc and not even named DEF.
The next step is the algebraizer, which is the binding process. This step (which does not occur when you are just parsing the request) ensures that every object in your request actually exists, and will fail if you refer to objects that do not exist.
Another way to test this is to tell SQL Server to parse only and do nothing else:
SET PARSEONLY ON
SELECT abc FROM def
GO
Above the request, it will say "Command completed successfully."
SET PARSEONLY OFF
SELECT abc FROM def
GO
Above the request, it says "Invalid object name" def "."
In your example question, the someIntCol column exists within the scope of your request because you did not provide a specific origin for it. It doesn't make a lot of sense as a result, but it's not an invalid request yet.
source to share