0

I am creating two tables One has a list of temperature sensors (including last measured value) The other one has historical values for each measurement

Tables are defined like this:

CREATE TABLE [dbo].[History](
    [SensorID] [int] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [Temperature] [float] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Sensors](
    [SensorID] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL
) ON [PRIMARY]

Sensors table should also have the Temperature column to store the last value, but I forget to add it and that is how I found this strange behavior

I want to get historical data for 5 sensors with the lowest last value.

SELECT *
FROM History
WHERE SensorID IN (SELECT TOP 5 SensorID FROM Sensors ORDER BY Temperature)

The problem here is that the query works and returns a results (probalby incorrect), but:

  • The internal select should not work, because Sensors table does not contain Temperature column. If I run it separately it fails as expected. The documentation for ORDEr BY states, that the column must be defined in SELECT or in FROM table. - http://technet.microsoft.com/en-us/library/ms188385.aspx
  • When I check the execution plan, there is no Order. There is only a Top and Filter, both applied to Sensors table and a Left Semi Join.

I know that I can easily fix it by using aliases. This will make my query fail as I want. The question here is, is this some kind of bug in SQL Server/Query optimizer?

Tested on SQL Server 11.0.3128 Note: SQL Management studio intellisense also offers Temperature column when writing the ORDER BY clausule

Ondra
  • 1,619
  • 13
  • 27
  • 4
    Is this some kind of bug in SQL Server/Query optimizer? No. a sub query can reference columns from the outer query. If you don't qualify them and there is nothing matching in the inner scope it will be resolved from an outer scope. – Martin Smith Oct 01 '13 at 15:50
  • Subqueries can refer to columns in the outer query - I believe they're called "Correlated subqueries". They're usually useful for restricting data in a where clause for example, but using it in the order by makes no sense. – Bridge Oct 01 '13 at 15:52

0 Answers0