4

I have the following query:

SELECT apps.Field4,
       ISNULL(apps.field4, '-1')
FROM   applications apps
WHERE  apps.OBJECT_ID = 1727847
       AND ISNULL(apps.Field4, -1) = -1

apps.field4 is an integer, and no record has a value less than 0 for field4.

The return values for the above query are: 

+------+----+
| NULL | -1 |
+------+----+

But if I add AND apps.field4 is NULL to the where clause, no records are returned:

SELECT apps.Field4,
       ISNULL(apps.field4, '-1')
FROM   applications apps
WHERE  apps.OBJECT_ID = 1727847
       AND apps.field4 IS NULL
       AND ISNULL(apps.Field4, -1) = -1

Why does ISNULL seem to correctly identify the NULL value, but IS NULL does not?

Nizam
  • 4,569
  • 3
  • 43
  • 60
Developer Webs
  • 983
  • 9
  • 29
  • 1
    Why do you have "and ISNULL(apps.Field4, -1) = -1" in the WHERE clause? Is -1 a legitimate column value, so you don't want to just say "WHERE (apps.Field4 IS NULL or apps.Field4 = -1)"? – pmbAustin May 11 '15 at 19:49
  • 5
    Check for corruption. – Martin Smith May 11 '15 at 19:52
  • 1
    Both seem to work on a [fiddle](http://sqlfiddle.com/#!6/ce0e0/5/1). Are you sure you don't have a problem with this SQL being passed in? or is this directly being executed through SSMS? – xQbert May 11 '15 at 20:01
  • @MartinSmith How do you check and how do you fix? – UnhandledExcepSean May 11 '15 at 20:03
  • 2
    @Ghost - `DBCC CHECK...` commands - And the fix depends on the problem. If it is in a non clustered index then dropping and recreating would work. – Martin Smith May 11 '15 at 20:05
  • @pmbAustin It's just there for testing purposes. Every record in column field4 is >= 0. – Developer Webs May 11 '15 at 20:11
  • @xQbert I'm manually executing the statement using Microsoft SQL Server Management Studio. The database is 2008 R2. – Developer Webs May 11 '15 at 20:11
  • Then I'm with Martin, we've proven the logic works though a fiddle statement. Some sort of non-standard bug (possibly corruption) is being encountered. – xQbert May 11 '15 at 20:17
  • So just use "apps.Field4 IS NULL" instead of jumping through ISNULL hoops? – pmbAustin May 11 '15 at 20:19
  • 1
    @pmbAustin But my code shows that "apps.Field4 IS NULL" is not causing any records to be returned. The first query proves that the record with object_id = 1727847 does in fact have a NULL value in column Field4 :) – Developer Webs May 11 '15 at 20:59
  • 1
    @DeveloperWebs Do you try just with only apps.field4 IS NULL clause? – Beto May 11 '15 at 22:44
  • Are there any `ANSI_NULLS` settings at play here? – DaveShaw May 11 '15 at 22:58
  • @DaveShaw - No. There is no setting of relevance to this. – Martin Smith May 12 '15 at 06:10
  • Check the execution plan difference between the two. Maybe a different index is being used and it is corrupted. – UnhandledExcepSean May 12 '15 at 12:38
  • @Beto Yes, this returns 0 records: "WHERE apps.Field4 is NULL" with no other conditions, but this returns 1 result: "WHERE ISNULL(apps.Field4, -1) = -1". If I select apps.field4 the value appears as NULL. Column field4 contains no rows with a value of less than 0. – Developer Webs May 12 '15 at 13:40
  • 1
    This is on a live database. DBCC CHECKTABLE reports 56 issues on the table. After-hours I'll run REPAIR_REBUILD and see if that fixes the error and report back. – Developer Webs May 12 '15 at 13:45
  • 1
    @DeveloperWebs probably yes. My guess: The query uses an index to satisfy the Field4 IS NULL predicate. The index does not agree with the CI. Even if it is the CI that is corrupt you can likely recover all data from the NCIs. Any idea why your database might be inconsistent? Do you have checksums enabled for pages and backups? Corruption should have been found that way. – usr May 12 '15 at 15:09
  • 1
    Is [this answer](http://stackoverflow.com/a/7519490/4519059) helpful? ;). – shA.t May 21 '15 at 09:31

2 Answers2

1

I know that I'm not answering your question. However, I found something interesting in MSDN :

C. Testing for NULL in a WHERE clause Do not use ISNULL to find NULL values. Use IS NULL instead. The following example finds all products that have NULL in the weight column. Note the space between IS and NULL.

USE AdventureWorks2012;
GO
SELECT Name, Weight
FROM Production.Product
WHERE Weight IS NULL;
GO

https://msdn.microsoft.com/en-us/library/ms184325.aspx

Mincong Huang
  • 5,284
  • 8
  • 39
  • 62
  • ISNULL is being used because I wish to treat NULL values and -1 values the same. Or in other words it's a short form, rather than doing: AND (field4 IS NULL OR field4 = -1) – Developer Webs Dec 01 '15 at 19:48
0

It looks like database corruption was the issue. In the end I'd either used DBCC CHECKDB or DBCC CHECKTABLE to fix the issue, and the issue was gone.

Developer Webs
  • 983
  • 9
  • 29