Re: Interesting query outcomes

On 18/08/2020 22.33, Joe wrote:
> I have a MySQL table field sometimes with NULL or empty values when there is no data.  When I wrote the following to count data in this field:
> 
> select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
>                         ELSE TNAM END,  count(1)
>   from ITEMS group by 1;
> 
> I got:
> 
> | default          |     2929 |
> | default          |      139 |
> | item A           |      347 |
> | item B           |      831 |
> ....
> -- why "default" repeats? I humble one is from "TNAM is NULL" and the other from "TNAM=''", and also thought they should have been combined with "group" function... Thoughts without actually update table to turn "empty" to NULL or vise versa?

Maybe you should look at what values you have, try a "select distinct 
select concat('"',TNAM,'"') from ITEMS", it could show that you have a 
TNAM which begins with default which could hold an extra space 
(depending on mysql version, the extra space could be treated differently).

-- 

  //Aho