?
Solved

Different results depending on clustered or unclustered index

Posted on 2008-11-18
12
Medium Priority
?
728 Views
Last Modified: 2012-05-05
Hello,

Short story: I get different results in SQL 2008 depending on if a index is clustered or not. As well as the amount of rows (size) in that table. Same data on SQL Server 2005 works fine. The database is set to be compatible with version 80 (2000), changing that does not effect the result.

The problem is, the where clause is not processed as a filter.

Long (reproduce):
Please execute the following SQL-Statements on a 2008 Server. They create two tables and fill them with some rows.

After that, please execute the following select statement

declare @myID int;
set @myID=15;
SELECT * FROM tLeft LEFT OUTER JOIN tRight
             ON tLeft.GroupID = tRight.GroupID
             WHERE tLeft.bView=1 AND (tRight.ForeignKeyID=@myID OR tLeft.UserID = @myID)

You will get 3 result-rows. But UserID and ForeignKeyID are NOT 15 as I define it in the where clause!

If you change the index "IX_tRight_GroupID" to clustered (or reduce the rows in that table massively) it works as expected (no results are returned).
If you execute that on a SQL 2005 everything works as expected from the beginning.

I added an image of the execution plans.

How can that be? Is this a bug or a feature? Is there any setting to set
/****** Object:  Table [dbo].[tLeft]    Script Date: 11/18/2008 07:09:27 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tLeft]') AND type in (N'U'))
DROP TABLE [dbo].[tLeft]
GO
CREATE TABLE [dbo].[tLeft](
	[FileID] [int] NOT NULL,
	[UserID] [int] NULL,
	[GroupID] [int] NULL,
	[isTempACE] [bit] NOT NULL,
	[bView] [bit] NOT NULL,
	[sRights] [varchar](100) NULL,
	[ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tLeft] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[tLeft] ADD  CONSTRAINT [DF_tLeft_isTempACE]  DEFAULT ((0)) FOR [isTempACE]
GO
 
ALTER TABLE [dbo].[tLeft] ADD  CONSTRAINT [DF_tLeft_bView]  DEFAULT ((0)) FOR [bView]
GO
 
 
/****** Object:  Index [IX_roxFileACE]    Script Date: 11/18/2008 07:21:54 ******/
CREATE NONCLUSTERED INDEX [IX_tLeft_FileID] ON [dbo].[tLeft] 
(
	[FileID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
 
insert into tLeft (fileid, userid, groupid, istempace,bview, srights) values(589,68,NULL,0,1,'R1R3V1V2V6E1E3E4X1X2X4X5X6W1W2W3W9')
insert into tLeft (fileid, userid, groupid, istempace,bview, srights) values(564,68,NULL,0,1,'R1R3V1V2V6E1E3E4X1X2X4X5X6W1W2W3W9')
insert into tLeft (fileid, userid, groupid, istempace,bview, srights) values(594,68,NULL,0,1,'R1R3V1V2V6E1E3E4X1X2X4X5X6W1W2W3W9')
 
 
/****** Object:  Table [dbo].[tRight]    Script Date: 11/18/2008 07:10:56 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tRight]') AND type in (N'U'))
DROP TABLE [dbo].[tRight]
GO
CREATE TABLE [dbo].[tRight](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[GroupID] [int] NULL,
	[ForeignKeyID] [int] NULL,
	[SubgroupID] [int] NULL,
	[Type] [varchar](10) NULL,
	[isAutomatic] [bit] NOT NULL,
 CONSTRAINT [PK_tRight] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[tRight] ADD  CONSTRAINT [DF_tRight_SubgroupID]  DEFAULT (NULL) FOR [SubgroupID]
GO
 
ALTER TABLE [dbo].[tRight] ADD  CONSTRAINT [DF_tRight_isAutomatic]  DEFAULT ((0)) FOR [isAutomatic]
GO
 
 
 
/****** Object:  Index [IX_roxGroupMembers_GroupID]    Script Date: 11/18/2008 07:19:31 ******/
CREATE NONCLUSTERED INDEX [IX_tRight_GroupID] ON [dbo].[tRight] 
(
	[GroupID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
declare @i int;
set @i=0;
while @i<500
begin
insert into tRight (GroupID, ForeignKeyID,SubgroupID,Type,isAutomatic) VALUES (1, NULL, 2, 'Con', 0)
	set @i=@i+1;
end

Open in new window

sql.png
0
Comment
Question by:moon44
  • 6
  • 4
  • 2
12 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 22986198
Not sure what specific values you are getting in the result set -- that would have been nice to see -- but you are doing a LEFT OUTER JOIN, so you should get a back *every row* "WHERE tLeft.bView=1", with the tRight columns NULL if a match was not found.
0
 

Author Comment

by:moon44
ID: 22986341
I attach a screenshot of the results.

As you can see, the where clause is not processes correctly. I 2005 it works. As I wrote.
sql2.png
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 22986844
That *is* a valid result.  You did a LEFT OUTER JOIN, not an INNER JOIN.  SQL does not need to find a match on the tRight table to include the row.  I think the wrong result is from 2005, not 2008.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 22991227
Agree with scott.

the left outer join means "i don't care about tright having matching entries"

so long as tleft = 1, then the second qualifier is failing because of  NULL and you can choose to ignore NULL conditions in some settings...

try this to test the NULL, or non-join of tright:

declare @myID int;
set @myID=15;
SELECT *
FROM tLeft
LEFT OUTER JOIN tRight ON tLeft.GroupID = tRight.GroupID
WHERE tLeft.bView=1 AND (isnull(tRight.ForeignKeyID,0)=@myID OR tLeft.UserID = @myID)


0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22991231
Bit strange that it only manifests with non-clustered I do agree wholeheartedly with you on that score...
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22991419
One thought...

SET ANSI_DEFAULTS can control SET ANSI_NULL and there are 7 such settings that can affect indexes (and more)... ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, and CONCAT_NULL_YIELDS_NULL must also be set to ON, and NUMERIC_ROUNDABORT must be set to OFF when creating indexes.

have a look in BOL : null values [SQL Server]  and the subcategory "about..." has a decision table in there - kind of describes what is happening to you. But again, would not have thought there was a difference from a clustered index and a non-clustered index unless some of the ansi settings have impacted somewhere along the line - if not, then I would say it is a bug, and then would also go on to say, that you really must manage NULL yourself. I did have a SQL2008 NULL behaviour where in batch mode it will always fail despite some of the settings (it was an EE question and was "fixed") - seemed at the time that 2008 does in fact handle NULL in a lot more stringent fashion.
0
 

Author Comment

by:moon44
ID: 22992070
I know what a left join does. And it is what I want to use.
But why is the where-clause not used as a filter?

The tLeft.userid does not match with the parameter value.

If I leave one of the ORs away the result is correct (no rows).
Doing so it does not matter if I leave the
tRight.ForeignKeyID=@myID
or the
tLeft.UserID = @myID
out of the statement. If it is anything about the NULLs it should not matter if there is a
"tLeft.UserID = @myID" in the statement  or not, correct?

And why does it matter if there are more or less than 235 (equal) rows in the tRight table? This should not get into the result either.

I think I have to open a support case at microsoft for that.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22993289
Did you try the isnull bit in the where clause ?

when you are doing an AND on a condition, the entire condition is treated as a whole - ie a single result then within the bracket, we have the OR clause , but if there is NULL, then it can change the behaviour of the condition. 2008 does evaluate a bit differently that earlier (you say 2000 compatability mode), so, it can produce a subtley different result depending on how and when it actuall evaluates filter operations, and determines the likely outcomes before retrieving any data at all. Meaning, it may well have determined that an "UNKNOWN" condition is possible from the left join and used in the filter conditions of the where clause, and as soon as an "unknown" condition partially exists, then the entire condition set (ie within the brackets) is also likely to yield an unknown condition.

Some of the above is in fact described reasonably well in Books On Line :

Also, 2008 has this to say :

Sometimes, SQL Server 2008 evaluates expressions in queries sooner than SQL Server 2000. This behavior provides the following important benefits:

Indexes on computed columns can be matched to expressions in a query that are the same as the computed column expression.
Redundant computation of the expression results is prevented.

However, depending on the nature of the query and the data in the database, run-time exceptions could occur in SQL Server 2008 if the query has an existing unsafe expression. These run-time exceptions include the following:

Arithmetic exceptions: zero-divide, overflow, and underflow.
Conversion failures such as loss of precision and an attempt to convert a nonnumeric string to a number.
Aggregation over a set of values that are not all guaranteed to be nonnull.

In SQL Server 2000, these exceptions may not occur in a particular application that uses specific data. However, a query plan that is changed because of changing statistics potentially could lead to an exception in SQL Server 2008. You can prevent these run-time exceptions by modifying the query to include conditional expressions such as NULLIF or CASE.

Important:  
Expressions that appear in a search condition, select list, or any other location within a query may be broken down and reorganized into one or more independent expressions. SQL Server may evaluate these independent expressions in any order with respect to each other. Filtering operations, including joins, are not necessarily applied before the result columns are computed.
 

I would suggest that with the introduction of a clustered index, and the size of the table, does in fact change the query plan, and whilst not an exception per se, has created an event where by the "reasonably expect" behaviour is a bit different. We all know that SQL 2008 has been updated in some of these areas, and possibly never really manifested itself until now.

Also, have a look at that reference : http://msdn.microsoft.com/en-us/library/ms191504.aspx

look at the tables between two boolean operands (one is horizontal, the other is vertical and the grid is the intersection of those outcomes).

Looking at the "OR" condition we had operand 1 = unknown and operand 2 = false  -- net result = UNKNOWN

Now, looking at the AND condition, we have operand 1 = true and operand 2 (ie the above) = unknown -- net result = unknown, but I think being bracketed it is evaluating the first operand to be true.

Bottom line for me is it simply re-inforces the need to explicitly manage NULLs, and just another example (assuming that the query with isnull worked) and that's what the book says as well, and the reason being is to avoid unpredictable results such as you are experiencing.

However, would be worth raising a support case - mainly because the behaviour should be the same in a clustered or non clustered index, apart from the disclaimer that BOL says above (about changing query plans).

0
 

Accepted Solution

by:
moon44 earned 0 total points
ID: 23034099
Thank you all for your replies.

Talking to MS they pointed out that this is a known bug. Solved int he Cumulative Update 2 for SQL-Server 2008: found here: http://support.microsoft.com/kb/958186

The bug itself is described here: http://support.microsoft.com/kb/956254/

After installing the fix, the result was correct.

I never thought such a thing could happen. But my imagination not to install any SQL-Server before the SP1 is out manifests in this.

Regards.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23038769
Glad you found it, it was highly suspect that it behaves differently for clustered vs non-clustered. I do stand by my postings, in so much as if you manage NULLs, then sometimes these things never surface.

Had a quick look at the KB - Is that the correct link ?  Says it happens in 2005 as well, and is talking about pages being flushed...

Cheers,
Mark Wills
0
 

Author Comment

by:moon44
ID: 23066603
Mark,

yes thats the correct link. I did never experience that behavior in SQL 2005. Perhaps it came out in different situations only.

Thanks for your participation!
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23066753
And thanks for all the updates in this thread - I am sure it will prove valuable...
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question