Re: Interesting query outcomes

On Wednesday, August 19, 2020 at 5:23:21 PM UTC-5, J.O. Aho wrote:
> 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

I did:
mysql> select distinct (select concat('"',TNAM'"')) from ITEMS order by 1;
and here is output:
+-----------+
| NULL      |  
| ""        |  
+-----------+
among other values.  So 'group' treats NULL and "nothing" ('') as separate values - perhaps that's where the mystery is.
I already translated them both to "default"; now all I need to do is to combine the two "default"s.

Thanks all!
joe