Bug #25089 NULLIF() not working

• Submitted: 15 Dec 2006 9:34
• Modified: 27 Jan 2007 14:43
• Reporter: Aftab Khan
• Status: No Feedback
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: Server version: 5.1.12-beta-community-nt
• OS: Windows (windows)
• 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".