Bug #25089 NULLIF() not working
Submitted: 15 Dec 2006 9:34 Modified: 27 Jan 2007 14:43
Reporter: Aftab Khan Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:Server version: 5.1.12-beta-community-nt OS:Microsoft Windows (windows)
Assigned to: CPU Architecture:Any
Tags: NULL Values Not Recogized using IFNULL()

[15 Dec 2006 9:34] Aftab Khan
Description:

I have tried many times NULLIF funtion for testing NULL values, but it's failed to catch NULL values.

As you can see in the following code, I have one trigger which activate after each row insert, 

There is a  variable declaration statement and the variable default value is NEW.CallDestRegid, this field could be null, in order to replace null value, I used NULLIF() function, but amazing this fuction do not catch/recognize null values; in order to double check I am writing the 'temp'(variable) to a dumy table; but it contains NULL values; which it shouldn't.

CREATE TRIGGER ProcessCDR before INSERT ON nextone
FOR EACH ROW
BEGIN

DECLARE temp VARCHAR(200) DEFAULT IFNULL(NEW.CallDestRegid, 'n/a') ;
insert into dumy values(temp);

end

How to repeat:
CREATE TRIGGER ProcessCDR before INSERT ON nextone
FOR EACH ROW
BEGIN

DECLARE temp VARCHAR(200) DEFAULT IFNULL(NEW.CallDestRegid, 'n/a') ;
insert into dumy values(temp);

end
[15 Dec 2006 20:30] Valeriy Kravchuk
Thank you for a problem report. Please, specify the exact (5.1.x) version of MySQL server used, and send CREATE TABLE and test data for the nextone table.
[18 Dec 2006 11:29] Aftab Khan
structure of ' nextone' table

Attachment: Nextone (Table Structure).htm (text/html), 1.86 KiB.

[18 Dec 2006 11:32] Aftab Khan
I am using Server version: 5.1.12-beta-community-nt; plz find the attachment of 'Nextone' table structure, and CSV file, which contains test values;
[18 Dec 2006 11:37] Aftab Khan
testing values for Nextone table

Attachment: TestValues.doc (application/msword, text), 25.50 KiB.

[18 Dec 2006 11:52] Aftab Khan
Complete Trigger, which activate after each row insert into Nextone table

Attachment: temp.php (application/octet-stream, text), 1.66 KiB.

[18 Dec 2006 11:57] Aftab Khan
Create table statement for creating Nextone table

Attachment: CREATE TABLE Nextone.doc (application/msword, text), 24.00 KiB.

[18 Dec 2006 11:59] Aftab Khan
plz do not use following file Nextone (Table Structure).htm, try another file 'CREATE TABLE Nextone.doc'
thnx
[27 Dec 2006 14:43] Valeriy Kravchuk
Please, try to repeat with a newer version, 5.1.14 beta, and inform about the results.
[28 Jan 2007 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".