View Column Resolving Issue in Sub-Query

A view is a powerful tool in SQL Server to simplify privilege configurations, isolate schema to developers, and tailor accessible data columns to individual users. However if a view is used in sub-queries, sometimes you may get unexpected results.

The following code is to check whether the SID of the current database owner is a valid server principal:

Run the query in SSMS, it succeeds with no error messages. However, actually the query has a problem. If you run the sub-query alone:

SELECT [sid] FROMsys.databases a WHERE a.name=DB_NAME())

Msg 207, Level 16, State 1, Line 1
Invalid column name 'sid'

The catalog view sys.databases does not have the column sid, it is called owner_sid in the catalog view. But SQL Server does not discover the issue in the first query. If we change [sid] to [any_col] in the first query, we will get the same error as previous one:

The sid is a column in the base table. If a column from a base table is renamed in the view definition, and the base column name is used in a sub-query against the view, SQL Server cannot discover the problem and it looks like it gets data from the base column.

This behavior is not just for system views. User defined views have the same behavior.

USE AdventureWorks
GO
CREATE VIEW Production.TestProduct AS
SELECT B.[Name] AS CatName,A.Name AS ProdName, C.Name AS SubCatName
FROM Production.Product A,Production.ProductCategory B,Production.ProductSubcategory C
WHERE A.ProductSubcategoryID=C.ProductSubcategoryID
AND C.ProductCategoryID=B.ProductCategoryID
GO

Run the following queries, they all return right results. It looks that SQL Server "knows" from which base tables [Name] column to get data:

SELECT * FROM Production.ProductCategory
WHERE [Name] IN (SELECT [Name] FROM Production.TestProduct)
SELECT * FROM Production.Product
WHERE [Name] IN(SELECT [Name] FROM Production.TestProduct)
SELECT * FROM Production.ProductSubcategory
WHERE [Name] IN (SELECT [Name] FROM Production.TestProduct)

So it seems not too bad at all. However let’s change the view a little bit:

ALTER VIEW Production.TestProduct AS
SELECT B.[Name] AS CatName,A.Name AS Prodname, C.Name AS SubCatName
FROM Production.Product A,Production.ProductCategory B,Production.ProductSubcategory C
WHERE A.ProductSubcategoryID=C.ProductSubcategoryID
AND C.ProductCategoryID=B.ProductCategoryID
AND B.ProductCategoryID>1

The category with ProductCategoryID=1 is not included in the view. Run the following query, you will see all categories still returned:

SELECT * FROM Production.ProductCategory
WHERE [Name] IN (SELECT [Name] FROM Production.TestProduct)

So actually, SQL Server just discarded the sub-query that contains the invalid column name in the view. If we make a mistake in the sub-query using the base table column instead of the column name in the view, SQL Server processes the query as if the sub-query does not exist without giving any error messages. This is true too in the first query about sys.databases view. The query can never find the database with an invalid sid because SQL Server handles it the same as:

This behavior sounds a bug to me. SQL Server should report that the column does not exist in the view regardless it is used in a sub-query or not. Yes, if we do not misuse the base table column in the view, the problem can never happen. But we are human and easy to make this kind of mistakes, which will be hard to figure out.

The problem applies to both SQL Server 2005 [tested on SP2, Developer Edition] and 2000 [tested on SP4, Developer Edition].

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.