Anomaly or bad design and coding? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Anomaly or bad design and coding?

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-06 : 17:18:54

Ok this is a strange thing I encountered. Why would SQL do this? I haev 2 tables: WorkOrders, WorkOrderItems. For each workorder there can be 0,1 or many WorkOrderItems. I had created a table WorkOrderItems with 4 fields. But then another of my SQL comrades added a new BIT field that Allows NULL but with no DEFAULT value or Biding. So all the rows entered since the addition of this new BIT field have NULL for this BIT field right?
Later on without changing anything in the table structure I updated all the values of this bit field for all rows to 0 (zero)

So any queries that did the following would return 0 rows
Select fld1, fld2 from table WorkOrderItems
Where WorkOrderItems.WID NOT IN (Select WID from WorkOrderItems )

But another query with outer left join returns the rows I want. WHY!?!?!?
SELECT my1, my1, my1, what, a , pieceofWork
FROM WorkOrders a
LEFT OUTER JOIN WorkOrderItems b
ON a.WorkOrderID = b.WorkOrderID
AND b.WorkOrderID IS NULL
Does the subquery behave differently than a left outer join. Do you have to be explicit in a subquery?

Thanks!!!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-06 : 18:26:01

Your first query should be like this:

Select fld1, fld2 from table WorkOrder
Where WorkOrder.WID NOT IN (Select WID from WorkOrderItems )

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-06 : 18:31:25

sodeep I am sorry a typo there that is exactly what I have

Select fld1, fld2 from table WorkOrder
Where WorkOrder.WID NOT IN (Select WID from WorkOrderItems )

Still no go

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-06 : 18:44:45

Its because of this:

http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-02-06 : 18:45:56

If there is a single row in WorkOrderItems where WorkOrderID is null, your existing query will return no rows.

If it is possible for column WorkOrderItems.WorkOrderID to be null, you should change your query to this:


Select
	a.*
from
	WorkOrder a
Where
	a.WorkOrderID not in
	(
	Select
		b.WorkOrderID
	from
		WorkOrderItems b
	where
		b.WorkOrderID is not null
	)

This code shows what happens when you use NOT IN and the subquery returns a null row. Notice that test 3 fails even though the subquery does not return a 1.

select
	TEST1 = 1
where
	1 not in ( select 1 union all select 2 )

select
	TEST2 = 1
where
	1 not in ( select 0 union all select 2 )

select
	TEST3 = 1
where
	1 not in ( select 0 union all select null )

select
	TEST4 = 1
where
	1 not in ( select 1 union all select null )


Results:
TEST1       
----------- 

(0 row(s) affected)

TEST2       
----------- 
1

(1 row(s) affected)

TEST3       
----------- 

(0 row(s) affected)

TEST4       
----------- 

(0 row(s) affected)

CODO ERGO SUM

Bodestone
Starting Member

18 Posts

Posted - 2009-02-06 : 20:04:27

In general no bit field should be designed as nullable. It's then not a bit. It had 3 possible values. I would update to 0 where null then change the design to not null default 0.

On top of the SQL issues there is the minefield of different behaviour of presentation layer stuff when it comes to that.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-09 : 17:49:56

thanks a lot for all yor input....lesson learned OUCH!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources