Datetime conversion fails when filtered index is placed on partition column

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

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

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.

StackExchange Database Administrators Q#230035, answer score: 9