Inserting to an indexed view can fail - by Dave_Ballantyne

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


7
0
Sign in
to vote
ID 717171 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 1/6/2012 12:25:20 PM
Access Restriction Public

Description

Consider the below code :


create table myTable
(
Id integer not null,
InView char(1) not null,
SomeData varchar(255) not null
)
go
Create view vwIxView
with schemabinding
as
Select ID,Somedata,left(SomeData,CHARINDEX('x',SomeData)-1) as leftfromx
from dbo.myTable
Where InView ='Y'
go
create unique clustered index pkvwIxView on vwIxView(Id)
go


under certain circumstances the functionality of the leftfromx calculation can cause an error.

If you now execute :

declare @id integer,
        @inview char(1),
        @Somedata char(50)
select @id = 1, @inview = 'N',@Somedata = 'a'

insert into myTable(Id,InView,SomeData)
select @id,@inview,@Somedata

then sqlserver will error with,

Invalid length parameter passed to the LEFT or SUBSTRING function.

This is due to the compute scalar being executed prior to the filter.

Tested on 2008r2 and 2012 rc0  
Sign in to post a comment.
Posted by Hugo Kornelis on 5/7/2013 at 12:43 PM
Sorry Susan, but your explanation makes no sense.

SQL is indeed a declarative language. The repro code above declares the following things:
1. A table
2. A view that exeposes only a subset of the columns in the view, and that uses a function on those columns.

By implication, the user declares that rows that satisfy the criterium for inclusion in the view will pass the function, and that (by further implication) also implies that rows that are not included in the view will not necessarily pass the function.

If SQL were a procedural language, there would not be an issue, we would write our code so that you don't get an error. But it isn't. It's declarative. The above is all WE can specify, now it's up to the optimizer to create a plan that satisfies the stated intent. Which is to perform the substring function for rows with InView='Y'. It fails at that, and I have no other word for that than "bug".

Your workaround suggestions are ridiculous. The first is to use a regular view instead. First, that would change the intent of the schema (in the example above, the Id column is unique for columns with InView='Y'; in a real case, more elaborate constraints can be enforced with indexed views). Second, indexed views are an important instrument for tuning, so your advice could cause severe performance problems.

The second suggestion, to isnert a copy of the data in another table, is probably even worse. Redundancy all over the place! Every time someone changes the data in one of the tables, I would need to copy the change to the other table - so I would be building a copy of the functionality that indexed views are supposed to offer out of the box.

Please consider reopening this bug, and scheduling it to be fixed in a future release. You have no idea how many hours people have to spend hunting down the cause of what appears to be a completely inexplicable error to the 99.9% of SQL Server users that have no idea of this serious bug.
Posted by Microsoft on 1/27/2012 at 2:27 PM
Hello Dave,

I am resolving this item as won't fix. You are expecting the filter and compute scalar to be executed in a particular order, but SQL is a declarative language that does not provide such a guarantee. You could consider dropping the indexed view and use a regular view instead, or filter the data first, then insert data that qualifies (Where InView ='Y') into another table.

Thank you for providing feedback on SQL Server.

Susan Price
Senior Program Manager
SQL Server Database Engine
Posted by Microsoft on 1/9/2012 at 10:51 AM
Hello Dave,

Thank you for providing feedback about SQL Server. We will investigate this issue regarding inserting into an indexed view and get back to you with our findings.

Best regards,

Susan Price
Senior Program Manager
SQL Server Database Engine