New index removes rows from result set. Bug? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

New index removes rows from result set. Bug?

Author

Topic

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2005-10-18 : 10:39:17

Ok, if you run the following simple piece of code:

CREATE TABLE #Temp
(Record_Date DATETIME
)
INSERT #Temp VALUES('2005-06-29 12:17:48.140')

--CREATE INDEX [IX_Record_Date] ON #Temp (Record_Date) 

DECLARE @Begin_Time AS SMALLDATETIME
DECLARE @End_Time AS SMALLDATETIME

SET @Begin_Time = '2005-06-29 12:18:00'
SET @End_Time = '2005-06-29 12:18:00'

SELECT *
FROM #Temp AS T 
WHERE CAST(T.Record_Date AS SMALLDATETIME) BETWEEN @Begin_Time AND @End_Time

DROP TABLE #Temp

You get a resultset with the one row, as expected. However, if you uncomment the CREATE INDEX line, which means the compiler will now use this index, the row does not appear! The absense or presence of an index should not dictate the final results of a query, should it?

I even tried a derived table, which to my surprise still did not work:


SELECT d.Small_Date
FROM
(
SELECT CAST(T.Record_Date AS SMALLDATETIME) AS 'Small_Date'
FROM #Temp AS T 
) AS d
WHERE d.Small_Date BETWEEN @Begin_Time AND @End_Time

If you comment out the WHERE clause you will see that d.Small_Date is exactly what you expect, but it still is not in the result set.

Finally, if you do use the date values directly in the query instead of the @Begin/End_Time variables, it works as expected.

I tested this on 2000 sp3, 2000 sp4, and 2005 September CTP, and the behavior is identical on all. Any ideas? As you can probably guess, this is actually happening on live production data - in one particular sproc if a certain index is chosen, rows disappear from the result set, much to the customer's frustration (and ours). We had to hardcode an index hint to prevent the offending index from screwing the results.

Thanks.

3P

Kristen
Test

22859 Posts

Posted - 2005-10-18 : 10:51:37

Presumably the INDEX plan is CASTing the @Begin_Time to DATETIME (i.e. NOT the "small" version) and then the Record_Date is actually earlier than the @Begin_Time.

Here's the SHOWPLAN

StmtText

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1001], [Expr1002], [Expr1003]))
|--Compute Scalar(DEFINE:([Expr1001]=Convert([@Begin_Time]), [Expr1002]=Convert([@End_Time]), [Expr1003]=If (Convert([@Begin_Time])=NULL) then 0 else 22|If (Convert([@End_Time])=NULL) then 0 else 42))
| |--Constant Scan
|--Index Seek(OBJECT:([tempdb].[dbo].[#Temp__000000001AB4].[IX_Record_Date] AS [T]), SEEK:([T].[Record_Date] > [Expr1001] AND [T].[Record_Date] < [Expr1002]), WHERE:(Convert([T].[Record_Date])<=[@End_Time] AND Convert([T].[Record_Date])>=[@Begin_Time]) ORDERED FORWARD)

This looks like trouble:

SEEK:([T].[Record_Date] > [Expr1001] AND [T].[Record_Date] < [Expr1002])

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-10-18 : 11:58:44

Another data point... the following isn't being affected by the whether or not there's an index.


SELECT *
FROM #Temp AS T 
WHERE CAST(T.Record_Date AS SMALLDATETIME)
 BETWEEN CAST('2005-06-29 12:18:00' AS smalldatetime)
     AND CAST('2005-06-29 12:18:00' AS smalldatetime)

It is still doing an index seek, but there's an obvious difference in the Compute Scalar part of the execution plan. Notice the offsets being applied to the literal values before they're used in the seek:

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1001], [Expr1002], [Expr1003]))
|--Compute Scalar(DEFINE:([Expr1001]=Convert('Jun 29 2005 12:18PM')-'Jan 1 1900 12:01AM', [Expr1002]=Convert('Jun 29 2005 12:18PM')+'Jan 1 1900 12:01AM', [Expr1003]=If (Convert('Jun 29 2005 12:18PM')-'Jan 1 1900 12:01AM'=NULL) then 0 else 6|If (Convert('Jun 29 2005 12:18PM')+'Jan 1 1900 12:01AM'=NULL) then 0 else 10))
| |--Constant Scan
|--Index Seek(OBJECT:([tempdb].[dbo].[Temp].[IX_Record_Date] AS [T]), SEEK:([T].[Record_Date] > [Expr1001] AND [T].[Record_Date] < [Expr1002]), WHERE:(Convert([T].[Record_Date])='Jun 29 2005 12:18PM') ORDERED FORWARD)

These aren't appearing in the version with the @Begin_Time, @End_Time variables.

However, if you change the end literal to, say 12:20, it behaves like the version with the variables, i.e.


SELECT *
FROM #Temp AS T 
WHERE CAST(T.Record_Date AS SMALLDATETIME)
  BETWEEN CAST('2005-06-29 12:18:00' AS smalldatetime)
      AND CAST('2005-06-29 12:20:00' AS smalldatetime)

is affected by the index. With the index the plan is:

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1001], [Expr1002], [Expr1003]))
|--Compute Scalar(DEFINE:([Expr1001]=Convert('Jun 29 2005 12:18PM'), [Expr1002]=Convert('Jun 29 2005 12:20PM'), [Expr1003]=If (Convert('Jun 29 2005 12:18PM')=NULL) then 0 else 22|If (Convert('Jun 29 2005 12:20PM')=NULL) then 0 else 42))
| |--Constant Scan
|--Index Seek(OBJECT:([tempdb].[dbo].[Temp].[IX_Record_Date] AS [T]), SEEK:([T].[Record_Date] > [Expr1001] AND [T].[Record_Date] < [Expr1002]), WHERE:(Convert([T].[Record_Date])<='Jun 29 2005 12:20PM' AND Convert([T].[Record_Date])>='Jun 29 2005 12:18PM') ORDERED FORWARD)

The offsets have disappeared again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources