Hi all,

This appears to be two separate bugs in SQL Server 2014, but somewhat related.  I'm running the latest cumulative update (version 12.0.2342).

Basically, I have a simple table that I partition and create a clustered columnstore index on.  Then, I create an indexed view with some of the columns.  The actual creation of the index fails with:

"Internal Query Processor Error: The query processor could not obtain access to a required interface."

If I reverse the order (i.e. create the indexed view on the table first, then create the columnstore index), it works fine.

Secondly, with or without partitions, if I create a clustered columnstore indexed table, then create an indexed view that contains an NVARCHAR column in the view, bulk inserts will fail with:

"SQL Server Assertion: File: <valrow.cpp>, line=415 Failed Assertion = '*((ULONG*)(pb + x_ibLengthInBuffer)) <= m_cbMaxLen'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted."

Of course, I have done a DBCC CHECKDB to no success.

The script to reproduce the former is below.  To test the bulk insert, simply create the structure without the partitioning and then use an SSIS package or what not to try to bulk load some data.

SET NOCOUNT ON

CREATE PARTITION FUNCTION [SalesByDate](datetime2(7)) AS RANGE LEFT FOR VALUES (N'1970-01-01T00:00:00.000', N'2013-01-01T00:00:00.000', N'2014-01-01T00:00:00.000')
CREATE PARTITION SCHEME [SalesByDate] AS PARTITION [SalesByDate] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

CREATE TABLE [dbo].[Test](
	[TestId] [bigint] NOT NULL IDENTITY(1,1),
	[Key] nvarchar(50) NOT NULL,
	[Date] [datetime2](7) NOT NULL,
	[Num] [int] NOT NULL
) ON SalesByDate (Date)
GO

DECLARE @i INT = 0

WHILE @i < 100000
BEGIN
INSERT INTO dbo.Test
SELECT 'Blah!', DATEADD(DAY, CAST(RAND() * 720 as INT), '2013-01-01'), CAST(RAND() * 100 as INT)
UNION ALL
SELECT 'Blah!', DATEADD(DAY, CAST(RAND() * 720 as INT), '2013-01-01'), CAST(RAND() * 100 as INT)
UNION ALL
SELECT 'Blah!', DATEADD(DAY, CAST(RAND() * 720 as INT), '2013-01-01'), CAST(RAND() * 100 as INT)
UNION ALL
SELECT 'Blah!', DATEADD(DAY, CAST(RAND() * 720 as INT), '2013-01-01'), CAST(RAND() * 100 as INT)
UNION ALL
SELECT 'Blah!', DATEADD(DAY, CAST(RAND() * 720 as INT), '2013-01-01'), CAST(RAND() * 100 as INT)
UNION ALL
SELECT 'Blah!', DATEADD(DAY, CAST(RAND() * 720 as INT), '2013-01-01'), CAST(RAND() * 100 as INT)
UNION ALL
SELECT 'Blah!', DATEADD(DAY, CAST(RAND() * 720 as INT), '2013-01-01'), CAST(RAND() * 100 as INT)
UNION ALL
SELECT 'Blah!', DATEADD(DAY, CAST(RAND() * 720 as INT), '2013-01-01'), CAST(RAND() * 100 as INT)
UNION ALL
SELECT 'Blah!', DATEADD(DAY, CAST(RAND() * 720 as INT), '2013-01-01'), CAST(RAND() * 100 as INT)
UNION ALL
SELECT 'Blah!', DATEADD(DAY, CAST(RAND() * 720 as INT), '2013-01-01'), CAST(RAND() * 100 as INT)

SET @i = @i + 10
END

GO

CREATE CLUSTERED COLUMNSTORE INDEX PK_Test ON dbo.Test ON SalesByDate (Date)
GO

CREATE VIEW [dbo].[TestView]  WITH SCHEMABINDING
AS

SELECT [Date],
	[Key],
	[TestId]
  FROM [dbo].[Test]
GO

CREATE UNIQUE CLUSTERED INDEX PK_TestView ON dbo.TestView (Date, TestId) ON SalesByDate (Date)
GO

I do not have the time to investigate the case for the moment, but I would suggest that both issues are bugs.

However, the nature of the bugs may be that 1) the error message should say that this is not supported. 2) you should not have been able to create an indexed view on top of the columnstore index at all. Please note that this is just my speculation. But the error messages indicate that this is something that has fallen through the cracks in the test matrix.

If this is a pressing issue that you need a fix for, to be able to carry out a business case, by all means open a case with Microsoft.

If you can live with it, please submit a bug report on http://connect.microsoft.com/SqlServer/feedback
to make Microsoft aware of the issue.

Thanks - I suppose I'll open a case.  I checked MSDN and I can't find anything definitive on whether indexed views on clustered columnstore indexed tables are allowed/disallowed, but there are certainly a number of blogs online where people have used the same trick.

The funny thing is, they work very well (when they work).  The engine is intelligent enough to expand to the indexed view when querying the table and use the indexed view's indexes when it'd be cheaper than columnstore.

As a side note, upon further investigation, the second issue only occurs when the indexed view contains a char, varchar, nchar, nvarchar or varbinary (at least those are the ones with which I've tested).  If the indexed view contains things like numeric and date values only, the error is not reproducible.

Furthermore, the error is sporadic.  For example:

BULK INSERT dbo.Test
   FROM 'c:\work\testimport2.txt'
   WITH 
      (
         FIELDTERMINATOR =',',
         ROWTERMINATOR ='\n'
      );

Works about 60-70% of the time, the rest get that error.

Just thought I'd add that information in case anyone was interested.  I'll open a case with Microsoft.

Please report back what the outcome is!

I definitely get the sense this is a feature combination they have overlooked. Some stuff works by chance, others break.

I will try to find some time to play with the scenario, but it has been a busy week - World Cup games every evening!

Sure - that'd be great.  Let me know if you'd like me to send some other scripts to reproduce the second issue - it's quite easy to produce in a very simple database.

I've looked into the issue, and it is not as bad as I got the impression from your initial post.

For the error could not obtain access to a required interface to appear several things have to apply:

1) The table must be partitioned.
2) You create the view index after the columnstore index.
3) And the table must be populated to some degree.

I found when testing that if slashed one zero from the WHILE condition, the error did not appear. The error message made me believe that it was a case of "not implemented", but maybe it is more resource-related. At least I can see how this could slip through a test suite.

I was also able to reproduce the assertion error, but as you say it does not happen every time. Then again, the error message does suggest that it may be timing-related. It is worth noting that my test file was a plain one-row file; that is, no volumes to talk about.

I ran the tests on SQL Server 2014 CU2 which was released yesterday just to see if they would happen to have been fixed.

I back down from my initial suspicion that this is a scenario that Microsoft had overlooked. There is no reason to assume anything else that this is supported, and the two issues you have seend are bugs that should be fixed. I guess whether they will be fixed depends on 1) how difficult they are to fix 2) the business impact these issues have for you.

Hi Erland,

That's good to hear that others can reproduce the bug.

I've opened a case with Microsoft and hopefully they will be able to resolve the bug, although I'm not sure how long it'll take.

• Edited by Adam PD Monday, June 30, 2014 6:42 AM

thanks for identifying this issue. This basic scenario was missed... We will track it as a hotfix and make it available in one of the cumulative updates

thanks

Sunil

Thanks Sunil.  Do you have any idea on an ETA for a hotfix?  We're obviously very keen to migrate to 2014, but this is holding us back.

Is this fixed in cumulative update 3? 

I encounter the same issue just now.

I was used to have a single clustered columnstore index table which has about 160 million records. the Merge works find.

Yesterday, I implemented the table paritioning on this table  and each partition is in clustered columnstore index.  Since then, the Merge stop working. the error message shows:

The statement has been terminated.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.