MySQL: Stored function not accepting null from view field

59 Views Asked by At

I have the following query that call stored function:

select count(*) 
  from vw_estatus_empleados vw
 where nivel_color_id('Cumplimiento', vw.estatus_actual) = -1;

The nivel_color_id function returns -1 when second parameter is null.

Actually, the vw.estatus_actual field have null in all rows.

When I run the query above the results is:

+----------+
| count(*) |
+----------+
|        0 |
+----------+

By other hand, when I run the same query with null instead of vw.estatus_actual:

select count(*) 
  from vw_estatus_empleados vw
 where nivel_color_id('Cumplimiento', null) = -1;   

The result is:

+----------+
| count(*) |
+----------+
|       19 |
+----------+

Here is the function header:

CREATE DEFINER=... 
FUNCTION `nivel_color_id`(`escala` VARCHAR(16), `valor` FLOAT) 
RETURNS int(11)

Why this could happed?

Edit:

A view row sample:

SELECT * FROM vw_estatus_empleados 
 WHERE estatus_actual IS NULL;

+-------------+--------------+----------------+
| id_empleado | id_proveedor | estatus_actual |
+-------------+--------------+----------------+
|          11 |           16 |           NULL |
|           8 |           11 |           NULL |
|           4 |            2 |           NULL |
|          19 |           23 |           NULL |
|          13 |           18 |           NULL |
+-------------+--------------+----------------+
1

There are 1 best solutions below

0
On

Are you sure your vw.estatus_actual is actually set to NULL and not just an empty space ' '?

Do a SELECT * FROM vw_estatus_empleados WHERE estatus_actual IS NULL and see what is returned.