3

I'm totally confused by this one. I added a covering index to a SQL Server 2017 table and my integration tests broke.

Here is some simple SQL to illustrate the problem:

CREATE TABLE MyColumns (ColumnID INT PRIMARY KEY, LookupCodeTypeID INT)

INSERT INTO MyColumns (ColumnID, LookupCodeTypeID) 
VALUES (18, 37)

CREATE TABLE MyCodes (CodeID INT PRIMARY KEY, CodeTypeID INT, Code NVARCHAR(50), IsDeleted BIT)

INSERT INTO MyCodes (CodeID, CodeTypeID, Code, IsDeleted) 
VALUES (1, 12, 'North', 0), (2, 12, 'South', 0), (19, 37, '0', 0),
       (20, 37, '1', 0), (21, 37, '2', 1), (22, 37, '3', 0)

SELECT CAST(Code AS INT) AS CodeIndex
FROM MyCodes
INNER JOIN MyColumns ON LookupCodeTypeID = CodeTypeID
WHERE ColumnID = 18 AND IsDeleted = 0
ORDER BY CodeIndex

-- This index was created for other reasons but the following SQL broke
CREATE NONCLUSTERED INDEX IX1_MyCodes ON MyCodes (CodeTypeID) INCLUDE (Code)

SELECT CAST(Code AS INT) AS CodeIndex
FROM MyCodes
INNER JOIN MyColumns ON LookupCodeTypeID = CodeTypeID
WHERE ColumnID = 18 AND IsDeleted = 0
ORDER BY CodeIndex

The index was created to solve another problem elsewhere but the failed tests brought me to this code.

From trying to create an example to reproduce the problem I can see that for this code adding IsDeleted to the covering index would solve the problem, but this is not what I was expecting.

Since the tests have highlighted the issue I'll add IsDeleted to the covering index.

My question is WHY did the SQL break and how can I predict what else might break since only some of our code has integration tests?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sixeyes
  • 483
  • 3
  • 14
  • 2
    what do you mean "it broke"? – Martin Smith Oct 17 '19 at 13:29
  • 9
    I'm guessing it broke because you made the unsafe assumption that the server evaluates expressions in some strict order and it's now trying to `CAST` `'North'` to an `int`. The assumption was always unsafe. This looks a bit like you've created an One True Lookup Table (OTLT). – Damien_The_Unbeliever Oct 17 '19 at 13:31
  • i think Without order by it works.... – Thangadurai.B Oct 17 '19 at 13:37
  • Correct, it does work without the order by – sixeyes Oct 17 '19 at 13:38
  • 4
    Use `TRY_CAST` if it is possible. – Denis Rubashkin Oct 17 '19 at 13:39
  • 1
    Thanks. That works – sixeyes Oct 17 '19 at 13:42
  • *"Use TRY_CAST if it is possible."* ? @DenisRubashkin from what i see in the manual, `TRY_CAST` is a very old function and is not limited by any SQL Servers compatibility levels ... – Raymond Nijland Oct 17 '19 at 13:43
  • Yes we do have a OTLT :( My SQL assumptions are wrong but that doesn't fill me with confidence about lots of other SQL expressions we have in the code base – sixeyes Oct 17 '19 at 13:44
  • @RaymondNijland - `TRY_CAST` is newer than `CAST` in so far as that matters for anything – Martin Smith Oct 17 '19 at 13:44
  • @sixeyes keep in mind that `TRY_CAST` returns NULL when data could not be converted.. – Raymond Nijland Oct 17 '19 at 13:44
  • What is not correct? @MartinSmith mine statement or the information in the [manual](https://learn.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-ver15&viewFallbackFrom=sql-server-ver14) ? ... Oh it seams when using older versions filter in the manual it automatic defaults to the first version whcih had the functions which was Server SQL 2015, anyhow 2015 is still "old" – Raymond Nijland Oct 17 '19 at 13:47
  • 1
    @Damien_The_Unbeliever Do you want to create an answer so I can accept it? – sixeyes Oct 17 '19 at 13:47
  • 3
    @RaymondNijland - don't go off the version information in books online. they don't bother backfilling it for unsupported versions, according to that `SELECT` will have been introduced in last 10 years probably. `TRY_CAST` was actually introduced in 2012 – Martin Smith Oct 17 '19 at 13:48
  • *"don't go off the version information in books online. they don't bother backfilling it for unsupported versions"* point taken just started learning SQL Server more or less i never noticed that before the manual worked like that @MartinSmith anyhow usefull comment but i hate broken manuals.. – Raymond Nijland Oct 17 '19 at 13:54
  • your covering index changed the order of evaluation of boolean expressions, so the potential confilct between string and numeric gets calculated before ColumnID = 18 can reject the row. This could change virtually at anytime, so you have to make it bulletproof with try_cast as suggested – Cato Oct 17 '19 at 14:05

0 Answers0