CODEWITHSUNDEEP
sqloracle-databasejoin
Sirs
Sirs

Reputation: 1297

Unexpected results when performing joins

I've got a query more or less like the following:

select node from NODES node 
inner join SIGNATURES signature on node.NODE_ID=signature.NODE_ID and (... some non-important, AND separated restrictions on signature fields) 
left outer join OTHERS other on signature.ID=other.SIGNATURE_ID 
where node.DISABLED=0 and (some non-important, AND separated restrictions on node fields);

NODES has a 1..n relationship with SIGNATURES, and SIGNATURES has a 1..n relationship with OTHERS

The thing is, I've been told in some strange as of yet unidentified corner cases, this query returns, in production, a result containing nodes that have the DISABLED field set to 1. How is that possible?

Upvotes: 0

Views: 63

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

You are not showing the complete query. It is possible that the WHERE clause contains a mix of ANDs and ORs and you have simply forgotten appropriate parentheses.

Example: where node.DISABLED=0 AND blabla OR thisnthat AND somestuff

Upvotes: 2

Robert Sheahan
Robert Sheahan

Reputation: 2100

One way that could happen is a dirty read. In a production environment somebody could change the DISABLED value of a record after the record was selected by your query. This behavior is controlled through the Isolation Level, for a quick overview see

http://msdn.microsoft.com/en-us/vstudio/ms709374%28v=vs.103%29.aspx

or any of the many excellent interpretations by numerous bloggers.

Isolation level is usually set at the database level, but in T-SQL, at least, you can request it in a query using hints. See READCOMMITTED, REPEATABLEREAD, etc. in http://msdn.microsoft.com/en-us/library/ms187373.aspx

You might want to add what your isolation level is to your post, it could help refine further answers.

Upvotes: 1

Related Questions