Mysql – Condition evaluation with NULL or ”

MySQLmysql-5.5

I am having this strange effect from evaluating truthiness of a field

IF ( a IS NOT NULL && ((b IS NOT NULL) || (NOT b = '')) && c IS NOT NULL ) THEN
   set NEW.results = "Passed"
ELSE
   set NEW.results = "Failed" 
END IF;

Basically, if a,b,c are not null (or empty) then pass otherwise fail.

When there is no data for b, the field is set to NULL and the condition returns "Failed"

BUT

When no data is an empty string ('') the condition returns "Passed"

What am I missing here?

UPDATE:
@akina pointed out what my logic was delivering, which was not desired.

I adjusted the condition logic to the proper condition:

( NOT ((v_sectionnumber IS NULL) || (v_sectionnumber = '')))

which gave the desired results.

Best Answer

As per W3Resource documentation MySQL OR operator MySQL OR operator compares two expressions and returns TRUE if either of the expressions is TRUE.

Syntax:

OR, ||

When more than one logical operator is used in a statement, OR operators perform after AND operator. The order of evaluation can be changed by using parentheses.

The operator returns 1 when both operands are a non-NULL and one of them is nonzero and returns 0 when both operands are non-NULL and one of them is zero and returns NULL when one operand is NULL and other is zero and return 1 also when one is NULL and another operand is nonzero and NULL also when both operands and NULL.

Example

The following MySQL statement satisfies the condition - "both operands are a non-NULL and one of them is nonzero", so it returns 1.

SELECT 5 || 5;


MySQL> SELECT 5 || 5;
+--------+
| 5 || 5 |
+--------+
|      1 | 
+--------+
1 row in set (0.00 sec)

Example of MySQL OR operator with at least one (zero) 0

The following MySQL statement satisfies the condition - "both operands are non-NULL and one of them is zero", so it returns 1.

SELECT 5 || 0;


MySQL> SELECT 5 || 0;
+--------+
| 5 || 0 |
+--------+
|      1 | 
+--------+
1 row in set (0.00 sec)

Example of MySQL OR operator with at least one NULL operand

The following MySQL statement satisfies the condition - "one operand is NULL and other is zero", so it returns NULL.

SELECT 0 || NULL;

MySQL> SELECT 0 || NULL;
+-----------+
| 0 || NULL |
+-----------+
|      NULL | 
+-----------+
1 row in set (0.01 sec)

Example of MySQL OR operator with NULL and non-zero operand

The following MySQL statement satisfies the condition - "one operand is NULL and other is non-zero", so it returns 1.

SELECT 5 || NULL;

MySQL> SELECT 5 || NULL;
+-----------+
| 5 || NULL |
+-----------+
|         1 | 
+-----------+
1 row in set (0.00 sec)
Related Question