Bug #40096 update fail with Null value because of bad comparaison syntax
Submitted: 17 Oct 2008 1:07 Modified: 18 Nov 2008 13:01
Reporter: Cyrille Giquello Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:5.2.2 OS:Windows
Assigned to: CPU Architecture:Any

[17 Oct 2008 1:07] Cyrille Giquello
Description:
Hello,

The update command is generated with a bad syntax. If previous value was null, the where clause of the update command is
 WHERE ... PreviousValue = NULL
but it should be :
 WHERE ... PreviousValue IS NULL

more details :
to manage concurency, the generated update command contains a where clause with all fields with their previous values, values that was fetched at the previous select time.
when one of those previous values was NULL, the where clause of the generated update command has a bad syntax, because it use the equal sign to test the NULL, but it should be the operator IS NULL instead.

How to repeat:
create some data with some null value like :
fields : id,value
row 1 : 1,foo
row 2 : 2,NULL
then make 2 select to see the difference:
1) select from table where `value` = NULL
2) select from table where `value` IS NULL

the first select return zero row, the second return one row.
[17 Oct 2008 6:32] Tonci Grgin
Hi Cyrille and thanks for your report.

I don't quite follow you. Is this happening in some wizard or in code or are you typing the commands? Please attach test case if possible.
[18 Nov 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[18 Nov 2008 9:26] Cyrille Giquello
Hello,

The problem occure in the code generated by the dataset designer, in the updateCommand.
The updateCommand use the where clause syntax "UDPATE ... WHERE .... AND theField = NULL" and the right syntax is "UDPATE ... WHERE ... AND theField IS NULL".
[18 Nov 2008 12:07] Cyrille Giquello
Argh... This morning while I was writing a demonstration case for this bug, the designer generated a update query with the right syntax :
UPDATE `test`.`mytable` SET `value` = @value WHERE ((`id` = @Original_id) AND ((@IsNull_value = 1 AND `value` IS NULL) OR (`value` = @Original_value)))

So, we can close the bug...
and if something like this is coming back, I'll see.
[18 Nov 2008 13:01] Cyrille Giquello
Argh... This morning while I was writing a demonstration case for this bug, the designer generated a update query with the right syntax :
UPDATE `test`.`mytable` SET `value` = @value WHERE ((`id` = @Original_id) AND ((@IsNull_value = 1 AND `value` IS NULL) OR (`value` = @Original_value)))

So, we can close the bug...
and if something like this is coming back, I'll see.