Re: Interesting query outcomes

On 20.08.2020 11:28, Tony Mountifield wrote:
> In article <[email protected]>,
> Joe  <[email protected]> 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?
> 
> What does "GROUP BY 1" even mean???

It's a MySQL extension. In ORDER BY and GROUP BY you can refer to the n-th
field in the SELECT list by saying just n. A clean formulation of the query
would have been to give an alias to the calculated first field and then use
that alias name in the GROUP BY clause.

But I doubt that this is the reason for the observed behavior. For me this
looks like an ordinary bug. This is confirmed by the fact that others cannot
reproduce it and that it is only observed in versions from bronze age (5.6)
or even stone age (5.0). 5.6 will become EOL next February.