Cannot delete rows using [image] IS NULL - grantagu

Hello,

I've been having a problem recently with deleting rows from a table. The
table contains an image column for which I want to delete all rows that have
a NULL value for this column. This works fine until I have a foreign key
referencing the table, at which point any rows that have had their image
column UPDATED to be NULL fail to be deleted.

To illsutrate this, I've created a simple script (see below). This problem
only occurs on SQL Server 2005 (both Express and Developer editions), not on
SQL Server 2000.


CREATE TABLE [dbo].[TableA] ([Identity] [int] NOT NULL IDENTITY(1, 1)
PRIMARY KEY, [TableB_Identity] [int] NULL)
CREATE TABLE [dbo].[TableB] ([Identity] [int] NOT NULL IDENTITY(1, 1)
PRIMARY KEY, [Data] image NULL)

-- removing this foreign key will cause it to work successfully
ALTER TABLE [TableA] ADD CONSTRAINT [FK_TableA_TableB] FOREIGN KEY
([TableB_Identity]) REFERENCES [TableB] ([Identity])

INSERT INTO [TableB] ([Data]) values (NULL)
INSERT INTO [TableB] ([Data]) values (NULL)
INSERT INTO [TableB] ([Data]) values (NULL)

-- this delete works successfully
DELETE FROM [TableB] WHERE [Data] IS NULL
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]

INSERT INTO [TableB] ([Data]) values (NULL)
INSERT INTO [TableB] ([Data]) values (NULL)
INSERT INTO [TableB] ([Data]) values (NULL)

-- removing this update query will cause it to work successfully
UPDATE [TableB] SET [Data] = NULL WHERE [Data] IS NULL

-- this delete doesn't work
DELETE FROM [TableB] WHERE [Data] IS NULL
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]

-- this delete doesn't work
DELETE FROM [TableB] WHERE ISNULL([Data], NULL) IS NULL
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]

-- this delete works successfully
DELETE FROM [TableB] WHERE EXISTS (SELECT * FROM [TableB] AS TB WHERE [Data]
IS NULL AND TB.[Identity] = [TableB].[Identity])
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]




The output of this script will be four result sets with the count of how
many rows are in the table at each point. All of them should be 0 (as is the
case on SQL Server 2000), but they are actually 0, 3, 3 and 0. The simple
delete query

DELETE FROM [TableB] WHERE [Data] IS NULL

does not delete any rows after the values for the Data column have been
updated to NULL, even though a similar select query

SELECT * FROM [TableB] WHERE [Data] IS NULL

will return rows.


Notably, if either the foreign key is removed, or the

UPDATE [TableB] SET [Data] = NULL WHERE [Data] IS NULL

query is not performed, the script behaves as expected.


Why is this the case? Am I missing something simple? BOL (in Managing ntext,
text and image Data) does say that you cannot directly reference an image
column in a where clause, but it does stipulate that it can be included in an
IS NULL expression, so it should work.

Incidentally, using text or ntext instead of image does not work as well,
but using varchar(max), nvarchar(max) or varbinary(max) does work. However,
these are only supported on SQL Server 2005 and I need this to work in SQL
Server 2000.

Cannot delete rows using [image] IS NULL - Erland Sommarskog

Granta Guy (grantaguy@newsgroup.nospam) writes:

Amazing! Absolutely amazing! That is certainly one of those bugs that
makes you think: how do they do it?

And, yes, a bug it is. All I can really say is that you can file a bug on
http://connect.microsoft.com/SqlServer/Feedback. But if you need a hotfix
for this, you would need to open a case with Microsoft and convince them
that there is no good workarounds.


Alas, this makes me suspect that Microsoft will decline to fix it, since
image & co are deprecated in SQL 2005.


Can't you use image on SQL 2000 and varbinary(MAX) on SQL 2005? Dis-
regarding this bug, varbinary(MAX) is still so much easier to work with.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Cannot delete rows using [image] IS NULL - weil

Hello Grant,

I have noticed that you have send the feedback to the product team via
http://connect.microsoft.com/sql

Currently, our product team is working on this. If you have any questions
or it is a urgent situation, please submit a Support Incedent to the
Microsoft CSS. Hope this helps.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Cannot delete rows using [image] IS NULL - Kalle Olavi Niemitalo

Erland Sommarskog <esquel@sommarskog.se> writes:


The distinction between NULL values stored by INSERT and UPDATE
is not entirely unprecedented.  WRITETEXT in SQL Server 2005:

by not initializing text columns when explicit or implicit
null values are added in text columns with INSERT, and no text
pointer can be obtained for such nulls. To initialize text
columns to NULL, use the UPDATE statement. If the table has in
row text, you do not have to initialize the text column for
nulls and you can always get a text pointer."

Which makes me suspect that tweaking the "text in row" option
might possibly work around the bug.

For reference, this is where the bug was registered:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=321074

Cannot delete rows using [image] IS NULL - Erland Sommarskog

Kalle Olavi Niemitalo (kon@iki.fi) writes:

Ah, that casts some light on the mystery! Thanks Kalle!


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Cannot delete rows using [image] IS NULL - grantagu

Aha! That does indeed work around the problem. Running

sp_tableoption N'TableB', 'text in row', 'ON'

after creating the tables results in the script working as expected.

Thanks.