November 20, 2008 at 5:13 pm
Hi to all,
i hope there is someone who can give me a hint to solve the problem.
I tried to find a known bug in the KB or a hint in forums, but i had no success.
Here is my problem:
When running a LEFT OUTER JOIN in Sql server SP4 on an view which uses a function as a field, i get instead of NULL the value of the function, if the join does not match.
The same query in SP3 or in Sql Server 2005 does return NULL values.
The following is a sample code which you can use to verify.
I hope, someone has an idea if i make something wrong or if this is a bug in Sql server 2000 SP4.
Thanks in advance,
McShorty
--- code start ---
USE TestDB
go
--- create objects ---
CREATE TABLE dbo.tblXXX (x_val INT, x_dt DATETIME)
go
CREATE TABLE dbo.tblYYY (y_val INT, y_dt DATETIME)
go
CREATE FUNCTION dbo.fnJoinTest(@p_dtValue DATETIME ) RETURNS DATETIME AS
BEGIN
RETURN cast( '19000101' as datetime )
END
GO
CREATE VIEW dbo.vwYYY AS
SELECT y_val, dbo.fnJoinTest(y_dt) AS y_dt FROM tblYYY
go
--- some test data ---
INSERT INTO tblXXX ( x_val, x_dt ) VALUES ( 1, null )
INSERT INTO tblXXX ( x_val, x_dt ) VALUES ( 2, null )
INSERT INTO tblYYY ( y_val, y_dt ) VALUES ( 1, null )
--- left join ---
SELECT x.x_val, x.x_dt, y.y_val, y.y_dt FROM tblXXX x
LEFT JOIN vwYYY y ON x.x_val = y.y_val
go
--- remove objects ---
drop view dbo.vwYYY
drop table dbo.tblXXX
drop table dbo.tblYYY
drop function dbo.fnJoinTest
go
--- code end ---
November 21, 2008 at 9:11 am
Hallo to all,
did not anyone have an idea or perhaps solved this problem?
If i watch the execution plan, i can see the execution of the function is on the top of the plan in SP4.
Not so in SP3.
Any help or hints are welcome,
McShorty
November 24, 2008 at 4:09 am
What are your requeriments exactly?.
Just supposing you are trying to obtain a value only when you have a coincidence between first and second table, you can do something like this:
drop function dbo.fnJoinTest
GO
CREATE FUNCTION dbo.fnJoinTest(@pValue INT ) RETURNS DATETIME AS
BEGIN
declare @rtValue datetime
IF @pValue is null
SET @rtValue = null
else
SET @rtValue = cast( '19000101' as datetime )
RETURN @rtValue
END
GO
SELECT x.x_val, x.x_dt, y.y_val,
dbo.fnJoinTest(y.y_val) y_dt FROM tblXXX x
LEFT JOIN tblYYY y ON x.x_val = y.y_val
This way, the function is evaluated after the left join. And you'll obtain:
x_val x_dt y_val y_dt
----- ------ ----- ------------------------
1 NULL 1 1900-01-01 00:00:00.000
2 NULL NULL NULL
Is it what you are searching for?
November 24, 2008 at 5:18 am
Hi,
thanks for your reply. Sorry, if i did not point clearly to what i am expecting in an answer.
Indeed, the way you showed is the way we solved the problem AFTER we ran into this behaviour AFTER installing the Sql Server SP4. With this code we got the outer join behaviour like we understand this and it is described in the bol.
What i am looking for is something like:
- yes, this is a known bug. it is solved with a hotfix KB123456
- yes, you found a new bug in SqlServer 2000 SP4
- no, you did not understand the outer join. You have to ...
Thanks,
McShorty
November 24, 2008 at 12:24 pm
I was able to duplicate your problem on one of our 2000 baoxes. I don't have an answer for you unfortunately.
Have you searched the Microsoft Knowledge Base or reproted this to Microsoft?
As we are migrating from SQL Server 2000 to SQL Server 2005, and all our production systems are now on SQL Server 2005, I don't really need to pursue this myself but I would be interested about any answers you may find.
November 24, 2008 at 1:17 pm
approaching from a different angle................
what EXACT version of SQL are you on? I would be surprised if you had come across a new bug after all this time. The last roll up for SQL 2000 was HF 2187
http://support.microsoft.com/kb/916287
A scan of the fixes this rollup includes could tell you if your problem is there.
If you are on a lower version than that , say actual SP4 8.00.2039 then it could be worth trying this code on a test server upgraded to this level. SQL 2000 is out of mainstream support now so if you phone microsoft they are likely to tell you to upgrade to SQL 2005!
---------------------------------------------------------------------
November 24, 2008 at 1:21 pm
george sibbald (11/24/2008)
approaching from a different angle................what EXACT version of SQL are you on? I would be surprised if you had come across a new bug after all this time. The last roll up for SQL 2000 was HF 2187
http://support.microsoft.com/kb/916287
A scan of the fixes this rollup includes could tell you if your problem is there.
If you are on a lower version than that , say actual SP4 8.00.2039 then it could be worth trying this code on a test server upgraded to this level. SQL 2000 is out of mainstream support now so if you phone microsoft they are likely to tell you to upgrade to SQL 2005!
True, but if you are like me (unable to ask the right question to find what I'm looking for in the Microsoft Knowledge Base), they may tell you it is a known bug and was fixed in following CU for SQL Server 2000.
November 24, 2008 at 1:31 pm
I'm just popping a first glimps.
Why should a function that only returns a fixed date, return a NULL value if the input is null and the input is not checked.
Correct it like this:
CREATE FUNCTION dbo.fnJoinTest( @p_dtValue DATETIME ) RETURNS DATETIME AS
BEGIN
if @p_dtValue is null
begin
return (NULL)
end
else
begin
RETURN cast( '19000101' as datetime )
end
END
GO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 24, 2008 at 2:16 pm
SP4 resolves all joined views into single join query.
The function is applied on that final "resolved" query.
To all rows returned by that final query, of course.
If I remember right MS aknowledged this bug in SP1 and/or SP2, fixed it in SP3 but have that fix undone in SP4.
_____________
Code for TallyGenerator
November 25, 2008 at 2:09 am
Hi to all,
first of all thanks to everyone who answered.
@george: yes, the version is SP4 8.00.2039. I will have a look to your posted link.
@Lynn: thanks for recapitulating the behaviour. To ask the right question - this is the point. The first step is, that you are running into results, that you are fortunally verify as not ok. The second step is to find the reason for this behaviour - and again fortunally you find this. But with the classification of a behaviour it gets hard to find the right answer. To open a ticket with MS for an outdated version sounds not very promising. But the migration of a production system is not on my decision - i have a problem 🙁
@alzdba: The shown code was only for recapitulating the behaviour.
@sergiy: the different execution plans of SP3 and SP4 are confirming exactly what you sayed. In SP4 is the execution of the joined function filed at the highest level.
OK, my next step is to verify george's posted link. I will report my success.
Thanks,
McShorty
November 25, 2008 at 2:46 am
Hi,
i installed Hotfix 2187 - unfortunally no success. Same behaviour.
Thanks,
McShorty
November 25, 2008 at 2:52 am
McShorty (11/25/2008)
...@alzdba: The shown code was only for recapitulating the behaviour....
Rubbish in .... rubbish out :unsure:
As in many cases, providing exactly what you need may get your problem(s) solved much faster.
You described the problem well, but the provided script doesn't return the expected results;
In many cases pointing to a symantic issue.... what clarifies my response.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 25, 2008 at 3:02 am
Hi ALZDBA,
thanks for your reply.
As i mentioned above, the shown code shows only the behaviour.
As you can imagine, the usage of functions in views is not a singular problem of one function.
Looking for a solutions means, NOT to modify hundreds of views, respectivly functions - but to ensure a behaviour like it is defined by the standard respectivly MS given in the bol (Have a look in bol with the keyword "Left Outer Join-Operator").
The next argument against singular solutions is, that the solve the current state - but do not prevent developers to write (valid) code, which returns invalid values.
Thanks,
McShorty
November 25, 2008 at 3:34 am
another reason to avoid functions as much as possible.
In many cases they can be replaced by another form of joins and that explicitely point to one of the biggest dangers with functions: hidden joins. (and their impact and issues for performance optimization)
I'll try to repro.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 15, 2009 at 1:05 pm
Hello, McShorty
The problem is indeed a known bug in SQL Server 2000 SP4, but it is not documented in any KB (as far as I know). The bug is fixed in SQL Server 2005. See:
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/82c41203b929ca57
I reported the bug when SP4 was in beta and I got a response from Microsoft acknowledging the bug and provinding the workaround of using "COALESCE(1,0) AS A" instead of "1 AS A".
Razvan
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply