Strange Join Behavior present in SQL 2008, but not

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Strange Join Behavior present in SQL 2008, but not

Author

Topic

stroebele
Starting Member

5 Posts

Posted - 2009-07-08 : 19:57:12

I posted this over in the msdn forums and there was talk about perhaps I've found a new SQL bug, I'm hoping that's not the case, and someone here can point me in the right direction

We're in the process of upgrading from SQL2000 to SQL2008 SP1, currently SQL2008 is installed on our development server, and we're testing our in house applications against it. I've come across a problem in one of our applications that I've traced back to the following query:

SELECT
c.t_Charge_pk
,tmp.t_Charge_pk
,c.t_Driver_pk
FROM
x_jjs tmp
JOIN vw_Charge c ON tmp.t_Charge_pk = c.t_Charge_pk
JOIN t_ClientChargeType ct ON ct.t_Client_pk = c.t_System_pk AND ct.t_ChargeType_pk = c.t_ChargeType_pk AND ISNULL(ISDriverWage,0) = 0
WHERE
c.t_ChargeType_pk <> 333
ORDER BY
c.t_Driver_pk

When I run this I'll get results back where tmp.t_Charge_pk is not equal to c.t_Charge_pk, here are the top 10 rows out the 58 that get returned, you'll notice the first two are the offenders.
1067992 1067991 NULL
1069299 1069297 NULL
1023268 1023268 1538
1055441 1055441 1554
1059873 1059873 5387
1059062 1059062 1554
1059912 1059912 1440
1045548 1045548 5482
1040801 1040801 1634
1052667 1052667 5470

I can change a number of things in this query and the issue goes away things I've done that "fix" this problem

* Cast both t_Charge_pk(s) to an int in the JOINS (they're both already defined as int, so casting should do nothing)
* Remove the WHERE Cluase
* Take out the final JOIN
* Adding option(MAXDOP 1)
* Add columns I'm selecting out

In the interested of full disclosure I'll mention the test server is running on Hyper-V with a guest OS of Server 2003 R2 SP3, however I've been able to recreate the issue on my workstation running XP, without SP1 on SQL2008.

This problem doesn't present itself on our SQL2000 box, any thoughts on what could be causing this is greatly appreciated.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-08 : 23:51:27

try recompiling the view

also, rebuild all indexes

if that doesn't resolve it, post the results of DBCC Checkdb

stroebele
Starting Member

5 Posts

Posted - 2009-07-09 : 11:28:04

quote:
Originally posted by russell

try recompiling the view

also, rebuild all indexes

if that doesn't resolve it, post the results of DBCC Checkdb

I've tried recompling the view (many times...) I rebuilt all indexes on all tables.

I don't know if you want to see all 719 lines of dbcc checkdb, if so let me know, here are the bottom two lines

CHECKDB found 0 allocation errors and 0 consistency errors in database 'db_System'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-09 : 11:59:27

To which table does ISDriverWage column belong?

N 56°04'39.26"
E 12°55'05.63"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-09 : 12:02:44

Can you give any more details about the View? Does it use a CTE or soemthing?

Also, you should be able to change:
AND ISNULL(ISDriverWage,0) = 0

to

AND IsDriverWage IS NULL

Unless of corse you want NULLs and Zeros.. I wasn't sure.

stroebele
Starting Member

5 Posts

Posted - 2009-07-09 : 12:11:27

IsDriverWage belongs to cct, the view vw_Charge joins about half a dozen tables in to our charge table. All Joins in the view are done on primary keys.

IsDriverWage can be NULL or 0 the for query I'm running.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-09 : 12:14:05

1. There is no table or view cct, neither by name nor alias.
2. Does the view vw_Charge include NOLOCK query hint in it's definition?

N 56°04'39.26"
E 12°55'05.63"

stroebele
Starting Member

5 Posts

Posted - 2009-07-09 : 12:20:32

quote:
Originally posted by Peso

1. There is no table or view cct, neither by name nor alias.
2. Does the view vw_Charge include NOLOCK query hint in it's definition?

1. Sorry about that, I meant to say it's part of ct
2. Yes vw_Charge does include a NOLOCK

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-09 : 12:29:25

There is nothing is this query that can be suspect

SELECT		c.t_Charge_pk,
		tmp.t_Charge_pk,
		c.t_Driver_pk
FROM		x_jjs AS tmp
INNER JOIN	vw_Charge AS c ON c.t_Charge_pk = tmp.t_Charge_pk
INNER JOIN	t_ClientChargeType AS ct ON ct.t_Client_pk = c.t_System_pk
			AND ct.t_ChargeType_pk = c.t_ChargeType_pk
			AND ISNULL(ct.ISDriverWage, 0) = 0
WHERE		c.t_ChargeType_pk <> 333
ORDER BY	c.t_Driver_pk

I would investigate the need for having NOLOCK at all in the view.
I also suspect the view vw_Charge has base table(s) including one or both in the above query.

Can you post the link to MSDN forum topic about same issue?
Did anyone at MSDN suspect NOLOCK hint?

N 56°04'39.26"
E 12°55'05.63"

stroebele
Starting Member

5 Posts

Posted - 2009-07-09 : 13:22:03

Removing the NOLOCKs from the view does "fix" the problem, but I don't understand how this is different than any of the other fixes in the original post. None of the tables that make up the view are directly in query.

Here’s a link to the MSDN forum, there was no mention of NOLOCKs there.
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1da447f4-e2b5-4d26-8d3c-39256bf418d2/

Thanks for your help!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-09 : 13:55:30

nolock allows dirty reads and phantom reads -- never guaranteed to return correct result. should only be used on tables certain to be static

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-09 : 14:01:04

I assume your workstation didn't have any transactions going on. So, it seems strange that a NOLOCK would have any affect. Just for fun you could try forcing a checkpoint and see if that does anything...?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-09 : 14:39:53

I know Itzik Ben-Gan not long ago posted an article about NOLOCK (with sample code), and how it can both return duplicate records and also leave out records that should be there.

Can't find the sample code, but here is a similar article
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=92888&DisplayTab=Article

N 56°04'39.26"
E 12°55'05.63"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-09 : 15:36:28

Thanks for the link, I didn't realize that could happen. Scary...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-09 : 15:42:46

Yes, but now you know of it.

N 56°04'39.26"
E 12°55'05.63"

russell
Pyro-ma-ni-yak

5072 Posts