char_length not working as thought

Question

Below is what I have

+++++++++++++++
+ id + myData +
+++++++++++++++
+ 1  + ABCD   +
+ 2  + ABCDD  +
+++++++++++++++

When I execute

select char_length((select myData from mytable where id = 1)) as MyLength;

I get result as

++++++++++++
+ MyLength +
++++++++++++
+  4       +
++++++++++++

But when I execute query,

select char_length((select myData from mytable) as MyLength;

I get error as

DDL and DML statements are not allowed in the query panel for MySQL; only SELECT statements are allowed.

Demo

Answer 1

You are wrapping the entire query output in CHAR_LENGTH(). When applying the WHERE id = 1 to the subquery, it returns a single row, and since you've selected a single column, you get one single string value back which CHAR_LENGTH() will accept as its argument.

If the subquery select mySata from mytable returns multiple rows, char_length() cannot accept it as an input. Instead, merely apply CHAR_LENGTH() to myData in a regular SELECT statement to see the lengths of multiple rows.

SELECT CHAR_LENGTH(myData) AS myLength FROM mytable