Request For Confirmation Of SQL 2005 Optimiser Bug With Derived Table And Convert()

I have discovered what looks like a bug in the optimiser. I've posted it at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=288243 but I wonder if any of you with SQL 2005 RTM, 2005 SP1 or 2008 CTP could confirm when this was introduced and whether it is still an issue?






Code Snippet

-- Bug report

-- 2007/07/19

-- Alasdair Cunningham-Smith

-- alasdair at acs-solutions dot co dot uk

set nocount on

go

-- example date in in British date format

set dateformat dmy

go



use tempdb

go



create table foo( bar varchar( 30 ) not null )

go



insert into foo( bar ) values ( 'fishy' )

insert into foo( bar ) values ( '19/07/2007' )

go



-- this works fine in all versions - only valid dates are passed to the convert function

select

convert( smalldatetime, bar, 103 ) as bardate

from

foo

where

bar like '__/__/____'

go



-- this works on SQL 2000, but fails on SQL 2005 SP2 (I've not tried other SPs of SQL 2005):

-- Msg 295, Level 16, State 3, Line 2

-- Conversion failed when converting character string to smalldatetime data type.

--

-- I believe the query is rewritten as if the derived table query contained

-- "and convert( smalldatetime, bar, 103 ) < getdate()"

-- which would expose the convert to the invalid data

select

*

from

(

select

convert( smalldatetime, bar, 103 ) as bardate

from

foo

where

bar like '__/__/____'

) as derived

where

bardate < getdate()

go



-- Workaround:

-- Use a case statement to protect the convert operator from the invalid data

select

*

from

(

select

case when bar like '__/__/____' then

convert( smalldatetime, bar, 103 )

else

null

end as bardate

from

foo

where

bar like '__/__/____'

) as derived

where

bardate < getdate()

go



drop table foo

go



The workaround I discovered is simple but ugly. I invite your comments...



alasdair.