6

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

-----------------------------------------------------------------------------
9

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.
  • @sepupic - because then the statement isn't executed. It just generates the estimated plan but the problem operator is never executed. It happens at runtime inside a compute scalar. – Martin Smith Feb 18 at 13:31
-1

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

  • set language russian ;select CONVERT(datetime, '15.12.1999 00:00:00.000') works fine and does not produce an error. Demo code is provided in the question that more or less just needs to be executed to see if the filtered index is responsible or not. The OP will get an error inserting with russian language with any date however generated with that filtered index definition present. – Martin Smith Feb 18 at 13:11
  • Example dbfiddle.uk/… – Martin Smith Feb 18 at 13:17
  • Thanks, @MartinSmith, I've already seen and try to sort out. – Denis Rubashkin Feb 18 at 13:21
  • Martin's example operates 1 jan 1999 only. You can use DATEPART to check it. The difference is that a select with a WHERE clause uses the filtered index while a select without any filter doesn't. – V. Kisel Feb 18 at 16:48
  • 1
    I have checked my assumption, rebuilding doesn't affect results, but drop-create commands work as I expected - if index is created with one language (English on my PC) query returns two rows, if it's created with another (Russian), I get an empty dataset. – Denis Rubashkin Feb 19 at 7:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.