Ngtjyty

I've got a mysterious error of char data conversion at the moment of insertion a new row into a table. Troubleshooting reveals that a filtered index is the source of the problem.

The index's filter uses a DateTime column, which also is a partitioning column of the table. Some clients connect with Russian by default and get the error.
I've managed to reproduce it on a test table.

Does anyone have any ideas on why this happens?

use YOUR_DATABASE ;



go



select @@VERSION ;



-- Microsoft SQL Server 2016 (SP2-CU1) ...



-----------------------------------------------------------------------------



-- CREATE DATETIME PARTITIONED TABLE



set language english ;



drop table if exists dbo.test_of_filtered_idx ;



drop partition scheme ps_test_of_filtered_idx ;



drop partition function pf_test_of_filtered_idx ;



go







set language english ;



go



create partition function pf_test_of_filtered_idx (datetime)



   as range right 



   for values ('1999-11-01 00:00:00.000' , '1999-12-01 00:00:00.000' , '2000-01-01 00:00:00.000' , '2000-02-01 00:00:00.000')







create partition scheme ps_test_of_filtered_idx



  as partition pf_test_of_filtered_idx



  ALL to ([primary])







create table dbo.test_of_filtered_idx



(



  id int not null identity (1,1) ,



  dt datetime not null ,



  payload char(127) not null default(replicate('A' , 127)) ,



  constraint PK__test_of_filtered_idx primary key clustered (id , dt)



) on [ps_test_of_filtered_idx] (dt) ;



go



-----------------------------------------------------------------------------



-- INSERT TEST ROW AND CREATE DATETIME FILTERED INDEX



set language russian ;



go



insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;



go



set language english ;



go



create index IXF__test_of_filtered_idx__dt



  on dbo.test_of_filtered_idx



  (dt) include (id)



  where dt >= '1999-12-10 00:00:00.000' 



    and dt < '2000-01-20 00:00:00.000' ;



go



-----------------------------------------------------------------------------



-- CHECK 



set language english ;



go



insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('12/15/1999 00:00:00.000' /* native american datetime format MDY */) ;



go



-- GET ERROR



set language russian ;



go



insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;



-- Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.



-- The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.



go



-----------------------------------------------------------------------------



-- DROP INDEX AND GET ERRORFREE INSERTION



set language english ;



go



drop index IXF__test_of_filtered_idx__dt



  on dbo.test_of_filtered_idx



;



go



set language russian ;



go



insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;



go



-----------------------------------------------------------------------------

The error comes from converting the string 2000-01-20 00:00:00.000 (in the filtered index definition)

Changing the filtered index to use unambigous datetime format works.

create index IXF__test_of_filtered_idx__dt

  on dbo.test_of_filtered_idx

  (dt) include (id)

  where dt >= '19991210 00:00:00.000' 

    and dt < '20000120 00:00:00.000' ;

It definitely seems buggy to me that the filtered index range is interpreted according to the settings of the client and so the same date can be either included or not included in the filtered index dependant on the settings of the client at insert time (as in below demo)

CREATE TABLE T

  (

     dt DATETIME

  )



CREATE INDEX IXF__test_of_filtered_idx__dt

  ON T (dt)

  WHERE dt >= '1999-01-06' AND dt < '1999-02-06';



SET LANGUAGE ENGLISH



GO



INSERT INTO T

VALUES     ( DATEADD(DAY,5,'1999-01-01')); --6th Jan



GO



SET LANGUAGE RUSSIAN



INSERT INTO T

VALUES     ( DATEADD(DAY,5,'1999-01-01')); --Still 6th Jan



GO



SET LANGUAGE ENGLISH



GO



SELECT *

FROM   T

Returns two rows - both with the same date

+-------------------------+

|           dt            |

+-------------------------+

| 1999-01-06 00:00:00.000 |

| 1999-01-06 00:00:00.000 |

+-------------------------+

But

SET LANGUAGE ENGLISH



GO



SELECT *

FROM   T

WHERE  dt >= '1999-01-06'

       AND dt < '1999-02-06';

Uses the filtered index and just returns one of them

+-------------------------+

|           dt            |

+-------------------------+

| 1999-01-06 00:00:00.000 |

+-------------------------+

Running DBCC CHECKTABLE (T) WITH EXTENDED_LOGICAL_CHECKS at this point then fails for either language.

Msg 8951, Level 16, State 1, Line 4

Table error: table 'T' (ID 1045578763). Data row does not have a matching index row in the index 'IXF__test_of_filtered_idx__dt' (ID 2). Possible missing or invalid keys for the index row matching:

Msg 8955, Level 16, State 1, Line 4

Data row (4:24:1) identified by (HEAP RID = (4:24:1)) with index values 'dt = '1999-01-06 00:00:00.000' and HEAP RID = (4:24:1)'.

DBCC results for 'T'.

There are 2 rows in 1 pages for object "T".

CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'T' (object ID 1045578763).

repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (tempdb.dbo.T).

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

In your filter condition you have varchar values so before working with datetime values they must be converted to datetime (Data type precedence). And this conversion depends on language settings.

That's why you get error when the command below is implicitly run:

set language russian ;

SELECT CONVERT(DATETIME, '2000-01-20 00:00:00.000');

To avoid this sort of errors explicitly use the datetime type in your filter expression or use an ISO 8601 date time format for your constants:

create index IXF__test_of_filtered_idx__dt

on dbo.test_of_filtered_idx

    (dt) include (id)

where   dt >= CONVERT(DATETIME, '1999-12-10 00:00:00.000', 120)

    and dt <  CONVERT(DATETIME, '2000-01-20 00:00:00.000', 120);

ADD:
Why isn't presented one value in filtered index from @MartinSmith's answer?

CREATE TABLE T

  (

     dt DATETIME

  )



CREATE INDEX IXF__test_of_filtered_idx__dt

  ON T (dt)

  WHERE dt >= '1999-01-06' AND dt < '1999-02-06';



SET LANGUAGE ENGLISH



GO



INSERT INTO T

VALUES     ( DATEADD(DAY,5,'1999-01-01')); --6th Jan



GO

Because after conversion from varchar to datetime:

'1999-01-06' = 1st JUN

'1999-02-06' = 2nd JUN