BUG with Join hints

I am having problems with doing what seams to be a very easy query. For some reason the SQL Server is trying to do nested loops instead of hash join. I tried to force the use of the hash join using the join hint.

Query 1

select *
from DIM_DATE DD
inner hash join (
select A.student_key,
CONVERT(int, CONVERT(varchar, COALESCE (A.date_withdrawn, getdate()), 112)) AS date_withdrawn_current
FROM FACT_STUDENT AS A
) SSE on DD.date_key= date_withdrawn_current

This query gives an error:

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN

Second query is not really what I want but it illustrate that it works fine when getdate() is not used.

Query 2

select *
from DIM_DATE DD
inner hash join (
select A.student_key,
CONVERT(int, CONVERT(varchar, COALESCE (A.date_withdrawn, A.date_enrolled), 112)) AS date_withdrawn_current
FROM FACT_STUDENT AS A
) SSE on DD.date_key= date_withdrawn_current

Is there some problem with using function getdate() ? It works fine in SQL Server 2000

This problem occurs on the SQL Server 2005 SP2 ( 9.00.3050.00 (X64) )
and (9.00.2050)

This problem has to do with some subtle differences between the way SQL Server 2000 handles non-deterministic functions and the way SQL Server 2005 handles it. You can get around it by rewriting your query as follows:

Workaround

with CurrentDate(d) AS (select getdate() d)

select *

from DIM_DATE DD

inner hash join (

select A.student_key,

CONVERT(int, CONVERT(varchar, COALESCE(A.date_withdrawn, (select d from CurrentDate)), 112)) AS date_withdrawn_current

FROM FACT_STUDENT AS A

) SSE on DD.date_key= date_withdrawn_current

Hope that helps.

|||Thanks.

The workaround worked. Finally the query behaves the way it is supposed to. Instead of running for hours it finishes in 12 seconds.

I thought that it has something to do with non deterministic nature of the getdate() but still not sure why would it matter in this case. This is the probe of the hash join, it needs to be scanned only once anyway.

Would that be fixed in SP3 or it works like that by design?
|||

I'm glad that worked. We're digging into it further in the query optimizer team to see why exactly the original query failed. I personally doubt that this will change in SP3 (the risk of changing plans in a service pack is too great) but hopefully we will fix this in future releases.

Thanks for the feedback.