Why does MariaDB CHAR_LENGTH return NULL instead of 0 when a field is NULL?

Solution 1:

Surround each with COALESCE(..., 0).

mysql> SELECT CHAR_LENGTH(null), CHAR_LENGTH(''), CHAR_LENGTH('abc');
+-------------------+-----------------+--------------------+
| CHAR_LENGTH(null) | CHAR_LENGTH('') | CHAR_LENGTH('abc') |
+-------------------+-----------------+--------------------+
|              NULL |               0 |                  3 |
+-------------------+-----------------+--------------------+

mysql> SELECT COALESCE(CHAR_LENGTH(null), 0);
+--------------------------------+
| COALESCE(CHAR_LENGTH(null), 0) |
+--------------------------------+
|                              0 |
+--------------------------------+

mysql> SELECT LENGTH('😁😂😃😄') AS len, CHAR_LENGTH('😁😂😃😄') AS char_len;
+-----+----------+
| len | char_len |
+-----+----------+
|  16 |        4 |
+-----+----------+

Be aware that there is overhead in any string -- maybe a comma between them, maybe a newline, maybe something else.

Also, CHAR_LENGTH() gives a character count, not a byte count.