SEO

This looks like a bug - hopefully somebody can explain what is actually
happening. Using SQL Server 2000 SP4.

Here's a repro script with comments:

/* repro table */
CREATE TABLE dbo.T (
ID int NOT NULL,
Time datetime NOT NULL,
CONSTRAINT PK_T PRIMARY KEY (ID, Time)
)
GO

/* the problem does not happen without this index */
CREATE NONCLUSTERED INDEX IX_T ON dbo.T (Time)
GO

/*
sample row - note that
CAST('2006-04-08 13:14:58.870' AS smalldatetime) = '2006-04-08 13:15:00'
*/
INSERT INTO dbo.T (ID, Time)
VALUES (1, '2006-04-08 13:14:58.870')
GO

/*
This does not return any rows - why?
The comparison should evaluate to TRUE.
*/
SELECT *
FROM dbo.T
WHERE CAST(Time as smalldatetime) >= '2006-04-08 13:15:00'
GO

/*
This does return the row.
*/
SELECT *
FROM dbo.T
WHERE CAST(DATEADD(millisecond, 0, Time) as smalldatetime) >=
'2006-04-08 13:15:00'
GO

DROP TABLE dbo.T
GO

The difference between the two SELECT statements is that the first one uses
a non-clustered index seek, whereas the second one uses a scan of the same
index.

--
(remove a 9 to reply by email)

Dimitri Furman wrote:
> This looks like a bug - hopefully somebody can explain what is actually
> happening. Using SQL Server 2000 SP4.
>
> Here's a repro script with comments:
>
> /* repro table */
> CREATE TABLE dbo.T (
> ID int NOT NULL,
> Time datetime NOT NULL,
> CONSTRAINT PK_T PRIMARY KEY (ID, Time)
> )
> GO
>
> /* the problem does not happen without this index */
> CREATE NONCLUSTERED INDEX IX_T ON dbo.T (Time)
> GO
>
> /*
> sample row - note that
> CAST('2006-04-08 13:14:58.870' AS smalldatetime) = '2006-04-08 13:15:00'
> */
> INSERT INTO dbo.T (ID, Time)
> VALUES (1, '2006-04-08 13:14:58.870')
> GO
>
> /*
> This does not return any rows - why?
> The comparison should evaluate to TRUE.
> */
> SELECT *
> FROM dbo.T
> WHERE CAST(Time as smalldatetime) >= '2006-04-08 13:15:00'
> GO

'2006-04-08 13:14:58.870' is not greater than '2006-04-08 13:15:00' - I
would not expect any results here.

> /*
> This does return the row.
> */
> SELECT *
> FROM dbo.T
> WHERE CAST(DATEADD(millisecond, 0, Time) as smalldatetime) >=
> '2006-04-08 13:15:00'
> GO

Maybe casting makes it larger / rounds it? Note that smalldatetime has
minute as precision.

> DROP TABLE dbo.T
> GO
>
> The difference between the two SELECT statements is that the first one uses
> a non-clustered index seek, whereas the second one uses a scan of the same
> index.

Kind regards

robert

It is a bug. For an example of an earlier discussion, see
http://groups.google.nl/group/micros...a?dmode=source

I don't know if there is a knowledge base article about it, or a
proposed fix. The thread does show workarounds.

HTH,
Gert-Jan


Dimitri Furman wrote:
>
> This looks like a bug - hopefully somebody can explain what is actually
> happening. Using SQL Server 2000 SP4.
>
> Here's a repro script with comments:
>
> /* repro table */
> CREATE TABLE dbo.T (
> ID int NOT NULL,
> Time datetime NOT NULL,
> CONSTRAINT PK_T PRIMARY KEY (ID, Time)
> )
> GO
>
> /* the problem does not happen without this index */
> CREATE NONCLUSTERED INDEX IX_T ON dbo.T (Time)
> GO
>
> /*
> sample row - note that
> CAST('2006-04-08 13:14:58.870' AS smalldatetime) = '2006-04-08 13:15:00'
> */
> INSERT INTO dbo.T (ID, Time)
> VALUES (1, '2006-04-08 13:14:58.870')
> GO
>
> /*
> This does not return any rows - why?
> The comparison should evaluate to TRUE.
> */
> SELECT *
> FROM dbo.T
> WHERE CAST(Time as smalldatetime) >= '2006-04-08 13:15:00'
> GO
>
> /*
> This does return the row.
> */
> SELECT *
> FROM dbo.T
> WHERE CAST(DATEADD(millisecond, 0, Time) as smalldatetime) >=
> '2006-04-08 13:15:00'
> GO
>
> DROP TABLE dbo.T
> GO
>
> The difference between the two SELECT statements is that the first one uses
> a non-clustered index seek, whereas the second one uses a scan of the same
> index.
>
> --
> (remove a 9 to reply by email)

Gert-Jan Strik (sorry@toomuchspamalready.nl) writes:
> It is a bug. For an example of an earlier discussion, see
>
http://groups.google.nl/group/micros...amming/msg/fe4
f6ec635260e5a?dmode=source
>
> I don't know if there is a knowledge base article about it, or a
> proposed fix. The thread does show workarounds.


The bug is also in SQL 2005 RTM. (I don't have the CTP of SP1 running
right now.) Do you if has been reported on
http://lab.msdn.microsoft.com/ProductFeedback/ for SQL 2005?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Apr 12 2006, 06:27 pm, Erland Sommarskog <esquel@sommarskog.se> wrote
in news:Xns97A44B2E20CAYazorman@127.0.0.1:

> Gert-Jan Strik (sorry@toomuchspamalready.nl) writes:
>> It is a bug. For an example of an earlier discussion, see
>>
> http://groups.google.nl/group/micros...programming/ms
> g/fe4 f6ec635260e5a?dmode=source
>>
>> I don't know if there is a knowledge base article about it, or a
>> proposed fix. The thread does show workarounds.
>
>
> The bug is also in SQL 2005 RTM. (I don't have the CTP of SP1 running
> right now.) Do you if has been reported on
> http://lab.msdn.microsoft.com/ProductFeedback/ for SQL 2005?
>

I haven't found anything, so I went ahead and opened a bug:
http://lab.msdn.microsoft.com/Produc...px?feedbackId=
FDBK48623

Feel free to add anything that may be important.

--
(remove a 9 to reply by email)

Dimitri Furman (dfurman@cloud99.net) writes:
> I haven't found anything, so I went ahead and opened a bug:
> http://lab.msdn.microsoft.com/Produc...px?feedbackId=
> FDBK48623
>
> Feel free to add anything that may be important.

Good!

I tested it on the CTP of SP1, and the bug appears there as well.

I final blow to anyone who is in doubt over whether this is a bug is
that if I add WITH (INDEX = 1), that is index hint force the clustered
index to be used, the query returns a non-empty result set.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx