LEFT OUTER JOIN Teaser

Question

LEFT OUTER JOIN Teaser

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 1

The WHERE clause in the view has nothing to do with it, and neither does the fact that it is a self-join.

If you create the view on a second table (with four rows), and LEFT JOIN the view to the original 8-row table, you get the same result.

If you change the WHERE clause from "< 5" to "> 4" or even "id % 2 = 1", you still get the wrong value from ISNULL on the null rows.

But if the view has any kind of expression for val (not just the CASE function mentioned earlier), the ISNULL results are correct. Try "id + 1 as val" in the view definition.

It looks like the query optimizer has figured out that the val column has a constant value, and ISNULL is using it instead of checking to see if the row exists.

Andy Fox SSC Journeyman Points: 93 More actions · Answer 2

Scott, you are quite right. I had it in my head that it was the self join that was throwing the spanner in the works. I did try it on two tables, but used a column value instead of the constant.

Good one.

It looks even more like a bug to me now.

fcallawa Mr or Mrs. 500 Points: 531 More actions · Answer 3

When creating the objects as described, I am getting the

following result set:

ID Val Val2

----------- ----------- -----------

1 1 1

2 1 1

3 1 1

4 1 1

5 1 1

6 1 1

7 1 1

8 1 1

I am curious as to what collation you folks are using?

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

I don't see what collation has to do with it, there are no character fields.

It doesn't look like you used the WHERE clause in the view definition. Rows 5-8 should have val=NULL.

fcallawa Mr or Mrs. 500 Points: 531 More actions · Answer 5

Well, that is why I posted the reply. I did have the where clause in the view.

A select * from vw returned ids 1 - 4.

Which is why I considered collation differences. Based on the collation, the RTS can have different rules for evaluating numerics.

When I created a temp table to hold the view results, I got the correct result set.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 6

It is an interesting behavior but it is not a bug because ANSI SQL OUTER JOINS default mathematically to a NULL. There are also known issues with the ISNULL function so it is just an anomaly. Try the link below for more on the SQL Server ISNULL function. The ANSI SQL OUTER JOIN limit is four but there is not restriction on INNER JOIN.

And you cannot compare CASE statement to a JOIN because a CASE statment is an iteration while a JOIN is a SET operation. Hope this helps.

http://www.akadia.com/services/dealing_with_null_values.html

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

g-168250 Valued Member Points: 70 More actions · Answer 7

Doing what Andy did, I first got:

Server: Msg 209, Level 16, State 1, Line 1

Ambiguous column name 'Val'.

Server: Msg 209, Level 16, State 1, Line 1

Ambiguous column name 'Val'.

Then changed this:

SELECT tbl.ID, Val, ISNULL(Val,0) As [Val2]

FROM tbl LEFT OUTER JOIN vw ON tbl.ID=vw.ID

TO:

SELECT tbl.ID, tbl.Val, ISNULL(tbl.Val,0) As [Val2]

FROM tbl LEFT OUTER JOIN vw ON tbl.ID=vw.ID

And got the correct results. I dunno why you did not get the error. That to me says "BUG!'

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 8

You don't get the error if you follow the directions. There is no val column in the table, only in the view.

Gift Peddie: I don't think the rest of us are the ones that need to read up on the ISNULL function. Having ISNULL(val,0) return 1 when val is null is not "interesting behavior", it's just wrong. Did you miss the 0 in there?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 9

The following is from the BOL (books online) about the ISNULL function and yes this is a remark but there are known issues with that function. The point I was making when you use a view a query rewrite with OUTER JOIN you can get funny results.

So the question is who decides what is the replacement value in this case SQL Server did.

(The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned.)

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 10

So what does your book say in what cases the returned value is neither check_expression nor replacement_value?

Does it maybe say to go back and reread the original post because you still haven't figured out what the bug is?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 11

The unexpected results of OUTER JOIN is covered by ANSI SQL and Peter Gultuzan, unexpected results of VIEWS was covered by Joe Celko and unexpected results of ISNULL is covered by Microsoft documentation and many people.

Andy Fox:

( COALESCE(Val,0) returns the expected value of 0 for the missing rows in vw. Thats something Ive learnt today - I didnt know about COALESCE.)

Rajesh Kumaran:

(Instead of view if the result set is stored in a # table, isnull is working fine. )

Ken Lee:

(That is interesting behavior, especially when COALESCE works. Note that if you change the constant in the view to a case statement, the isnull join works as expected.)

The above are from the first page of the thread and you don't have to accept my reasoning. I see an anomaly and you see a bug, different strokes.

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Can we have more details about those anomalies??? I can confirm that this looks like a "bug" but I'd like to know more about those known problems .

Gift Peddie SSC Guru Points: 73570 More actions · Answer 13

The known problems with Outer Joins in SQL Server before version 2000 giving just wrong answers is covered in SQL Performance Tuning by Peter Gulutzan, Trudy Pelzer. And known problems with View is covered in Advanced SQL Database programmers Handbook by Donald K Burleson, Joe Celko, John Paul Cook and Peter Gulutzan.

I have also recently interacted with someone in the Sqljunkies forum with a query on a View working fine in Oracle but is crashing SQL Server. The first book also covered how most cost based query processors start with Cartesian Product. Microsoft documentation of the ISNULL function is very clear.

I have known since 1999 that Money can round to less than a dollar and it continue to this day as a known issue. The answer is change the data type to Decimal or Numeric and in this case use temp table or don't use a Join. All three Outer Join, Views and ISNULL can be the reason for the results.

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

Andy Fox SSC Journeyman Points: 93 More actions · Answer 14

I have just patched to SP4 and now get the same results as fcallawa.

Val always comes back as the constant 1 even if there is a missing row in the outer join. Given this, ISNULL(Val,0) is now behaving as documented.

SP3 displayed 'interesting behaviour' in the way ISNULL() was working. This has apparently been fixed by introducing some 'interesting behaviour' in the LEFT OUTER JOIN. Why didnt they just fix ISNULL and eliminate all the 'interesting behaviour'?