Score:1

Server

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

Tim

11/9/22, 6:22 AM

I am trying to calculate to total size of stored metadata in a table using CHAR_LENGTH to add up the size of the relevant fields in each row to get a rough idea of the storage size of a result set.

I don't want the size of all the data in the table, only the size of a set of results.

However, some fields in the table have NULL values and as soon as CHAR_LENGTH hits one of those the whole calculation fails because CHAR_LENGTH returns NULL and not 0.

For example:

SELECT SUM(row_size) as `total` FROM ( SELECT CHAR_LENGTH(`field1`)+CHAR_LENGTH(`field2`)+CHAR_LENGTH(`field3`)+CHAR_LENGTH(`field4`) as `row_size` FROM `meta_data` WHERE `id` = 1 AND `id2` = 2 ) as tbl1;

For some queries this works and I get the size in bytes but for any that have a NULL value on any field in any row of the results I get nothing.

Why would CHAR_LENGTH not return 0 for a NULL value?

I suppose the next best thing is to get the results back and iterate through them in code to add up the size. I was hoping to avoid doing that but it might be the only way?

2 + 5

mysql

mariadb

Dom

11/9/22, 6:56 AM

https://mariadb.com/kb/en/char_length/ : "If the argument is NULL, it returns NULL.". So, it is not a bug, it is a feature ;-). I try to solve the problem with SUM or CONCAT, but no luck

Dom

11/9/22, 7:00 AM

SELECT CHAR_LENGTH(IFNULL(null, "")); works and return 0 !

Dom

11/9/22, 7:01 AM

SELECT SUM(row_size) as `total` FROM ( SELECT CHAR_LENGTH(IFNULL(`field1`, ''))+CHAR_LENGTH(IFNULL(`field2`, '')+CHAR_LENGTH(IFNULL(`field3`, ''))+CHAR_LENGTH(IFNULL(`field4`, '')) as `row_size` FROM `meta_data` WHERE `id` = 1 AND `id2` = 2 ) as tbl1;

djdomi

11/9/22, 7:03 AM

is that realted to serverfault? no is it Stackfault yes?! :-)

Michael Hampton

11/9/22, 10:32 AM

No, this belongs on [dba.se]

Score:1

Server

Rick James

11/9/22, 7:04 AM

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.

0 + 1

Tim

11/9/22, 7:25 AM

Great answer. Thank you.

Score:0

Server

Phill W.

11/9/22, 9:05 AM

Why would CHAR_LENGTH not return 0 for a NULL value?

Because any function (except the likes of NVL(), obviously) will return NULL if passed a NULL argument. Formally:

f(NULL) == NULL

More importantly:

NULL != ''

An empty string is .. well .. an empty string.
A NULL marks the absence of data.

Not the same thing.
(Unless, of course, you're trying to use varchar data in an Oracle Database, which still can't tell the difference, even in the 2020's).

0 + 0

Elon Musk

I sit in a Tesla and translated this thread with Ai:

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

TH: เหตุใด MariaDB CHAR_LENGTH จึงส่งคืน NULL แทนที่จะเป็น 0 เมื่อฟิลด์เป็น NULL

RO: De ce MariaDB CHAR_LENGTH returnează NULL în loc de 0 când un câmp este NULL?

RU: Почему MariaDB CHAR_LENGTH возвращает NULL вместо 0, когда поле имеет значение NULL?

VI: Tại sao MariaDB CHAR_LENGTH trả về NULL thay vì 0 khi một trường là NULL?

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.