[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. 
====================================================================== 
https://issues.asterisk.org/view.php?id=15045 
====================================================================== 
Reported By:                chris-mac
Assigned To:                tilghman
====================================================================== 
Project:                    Asterisk
Issue ID:                   15045
Category:                   Addons/app_addon_sql_mysql
Reproducibility:            always
Severity:                   feature
Priority:                   normal
Status:                     acknowledged
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-20 13:30 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.
======================================================================
Relationships       ID      Summary
----------------------------------------------------------------------
related to          0014757 [patch] 'h' extension not being execute...
====================================================================== 

---------------------------------------------------------------------- 
 (0105135) chris-mac (reporter) - 2009-05-20 13:30
 https://issues.asterisk.org/view.php?id=15045#c105135 
---------------------------------------------------------------------- 
I agree "nullasstring" is maybe not the best name. But in my opinion
whatever name of this option will be it should be boolean true/false (for
current broken incorrect behaviour, and when NULL is NULL and '' is '').

I suggest it is *much* better to return the actual value from DB, rather
than temper with it again.

So it should be (as it is in the latest patch):

if (somenicename) {
  /* Current broken behaviour - return NULL and '' as 'NULL' */
  pbx_builtin_setvar_helper(chan, s5, s6 ? s6 : "NULL");
} else {
  /* New behaviour - return whatever is in DB, so if it is NULL return
NULL, if it is '' return '' */
  pbx_builtin_setvar_helper(chan, s5, s6);
}

And it should *NOT* be (as suggested by dismas):

if (nullvalue == 'null') {
  /* Current broken behaviour - return NULL and '' as 'NULL' */
  pbx_builtin_setvar_helper(chan, s5, s6 ? s6 : "NULL");
} else if (nullvalue == 'empty') {
  /* New broken behaviour!!! - return NULL and '' as '' */
  pbx_builtin_setvar_helper(chan, s5, s6 ? s6 : "");
} else {
  /* New behaviour - return whatever is in DB, so if it is NULL return
NULL, if it is '' return '' */
  pbx_builtin_setvar_helper(chan, s5, s6);
} 

Issue History 
Date Modified    Username       Field                    Change               
====================================================================== 
2009-05-20 13:30 chris-mac      Note Added: 0105135                          
======================================================================