Symptoms


Assume that you have a table that contains a DATE data type column, and you have implemented Clustered Column Store Index (CCSI) on this table in Microsoft SQL Server 2014. When you try to query the table by using the DATE data type as a qualifier, incorrect data may be returned.

For example:create table tab2
(c1 int, c2 date, c3 varchar(10))

CREATE CLUSTERED COLUMNSTORE INDEX [CCI_tab2] ON [tab2] WITH (DROP_EXISTING = OFF)
GO

select *
FROM tab2 S -- Table with clustered columnstore index
WHERE S.c2 = @date -- variable of Date datatype

Resolution

Service pack information

To resolve this issue, obtain Service Pack 1 for SQL Server 2014.

For more information about SQL Server 2014 Service Pack 1 (SP1), see bugs that are fixed in SQL Server 2014 Service Pack 1.

Cumulative Update information

This issue was first fixed in the following cumulative update of SQL Server.


Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:


More Information

Use the following code to reproduce this issue:create table tab1
(c1 int, c2 date)

insert into tab1
values (1 , '2000-01-01')

CREATE NONCLUSTERED INDEX [idxtab1c2] ON tab1
(
c2 ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

create table tab2
(c1 int, c2 date, c3 varchar(10))

insert into tab2
values (1, '2000-01-01', 'test')

CREATE CLUSTERED COLUMNSTORE INDEX [CCI_tab2] ON [tab2] WITH (DROP_EXISTING = OFF)
GO

create table t
([runId] int not null, [scalingFactor] float not null)


-- REPRO QUERY. Below batch is expected to return a row but we don’t get it
truncate table t
DECLARE @date DATE, @numDates INT

INSERT INTO t VALUES(1, 1)
DECLARE @date DATE, @numDates INT
SELECT @date = max(R.c2) , @numDates = COUNT(distinct R.c2)
FROM tab1 R
INNER JOIN t D ON R.c1 = D.runId

select *
FROM tab2 S -- CCI
WHERE S.c2 = @date

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×