[asterisk-bugs] [Asterisk 0015045]: [patch] Fetching a NULL value from database returns "NULL" string

[Asterisk Bug Tracker]

A NOTE has been added to this issue. 
====================================================================== 
http://bugs.digium.com/view.php?id=15045 
====================================================================== 
Reported By:                chris-mac
Assigned To:                
====================================================================== 
Project:                    Asterisk
Issue ID:                   15045
Category:                   Addons/app_addon_sql_mysql
Reproducibility:            always
Severity:                   major
Priority:                   normal
Status:                     new
Asterisk Version:           SVN 
Regression:                 No 
SVN Branch (only for SVN checkouts, not tarball releases): 1.6.1 
SVN Revision (number only!): 889 
Request Review:              
====================================================================== 
Date Submitted:             2009-05-06 15:25 CDT
Last Modified:              2009-05-07 10:22 CDT
====================================================================== 
Summary:                    [patch] Fetching a NULL value from database returns
"NULL" string
Description: 
How to reproduce (asumming SQL query "SELECT some_null_col
FROM table WHERE where id = 1" returns NULL):

extensions.conf:
exten => 601,1,MYSQL(Connect connid1 ${DBHOST} ${DBUSER} ${DBPASS}
${DBNAME})
exten => 601,n,MYSQL(Query resultid1 ${connid1} SELECT some_null_col
FROM table WHERE where id = 1)
exten => 601,n,MYSQL(Fetch fetchid1 ${resultid1} X-DB-null)
exten => 601,n,MYSQL(Clear ${resultid1})
exten => 601,n,MYSQL(Disconnect ${connid1})
exten => 601,n,NoOp(X-DB-null: ${X-DB-null})
exten => 601,n,NoOp(ISNULL = ${ISNULL(${X-DB-null})})

console:
   -- Executing [601 at test:1] MYSQL("SIP/OpenSER-082cf100", "Connect
connid1 mysql-ro asterisk pass1 test") in new stack
   -- Executing [601 at test:2] MYSQL("SIP/OpenSER-082cf100", "Query
resultid1 1 SELECT some_null_col FROM table WHERE where id = 1") in
new stack
   -- Executing [601 at test:3] MYSQL("SIP/OpenSER-082cf100", "Fetch
fetchid1 2 X-DB-null") in new stack
   -- Executing [601 at test:4] MYSQL("SIP/OpenSER-082cf100", "Clear 2")
in new stack
   -- Executing [601 at test:5] MYSQL("SIP/OpenSER-082cf100",
"Disconnect 1") in new stack
   -- Executing [601 at test:6] NoOp("SIP/OpenSER-082cf100", "X-DB-null:
NULL") in new stack
   -- Executing [601 at test:7] NoOp("SIP/OpenSER-082cf100", "ISNULL =
0") in new stack

So looks like NULL value fetched from a MySQL table is converted into a
'NULL' string and ISNULL function returns 0.
====================================================================== 

---------------------------------------------------------------------- 
 (0104360) atis (reporter) - 2009-05-07 10:22
 http://bugs.digium.com/view.php?id=15045#c104360 
---------------------------------------------------------------------- 
The problem is that there is no distinction within Asterisk variables
between empty and NULL. So, Your patch would just return empty string
which could be perfectly valid string response (not NULL). I believe
that it's more uncommon to have actual data in MySQL to contain string
"NULL" than empty string, so it seems a good choice. Changes in this
could break some dialplans, which would lead to unsatisfied users.

The total solution of course would be to have distinction in Asterisk
between string not set and empty string. 

Issue History 
Date Modified    Username       Field                    Change               
====================================================================== 
2009-05-07 10:22 atis           Note Added: 0104360                          
======================================================================