Re: Interesting query outcomes

On 20-8-2020 12:47, Axel Schwenke wrote:
> 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.
> 

3 options to get the correct result, followed by the 4th way...

mysql> select case when a='a' then "A" else "a" end, count(*) from (select 'a' 
as a union all select null) a;
+---------------------------------------+----------+
| case when a='a' then "A" else "a" end | count(*) |
+---------------------------------------+----------+
| A                                     |        2 |
+---------------------------------------+----------+
1 row in set (0.00 sec)

mysql> select case when a='a' then "A" else "a" end, count(*) from (select 'a' 
as a union all select null) a group by case when a='a' then "A" else "a" end;
+---------------------------------------+----------+
| case when a='a' then "A" else "a" end | count(*) |
+---------------------------------------+----------+
| A                                     |        2 |
+---------------------------------------+----------+
1 row in set (0.00 sec)

mysql> select case when a='a' then "A" else "a" end x, count(*) from (select 
'a' as a union all select null) a group by x;
+---+----------+
| x | count(*) |
+---+----------+
| A |        2 |
+---+----------+
1 row in set (0.00 sec)

mysql> select case when a='a' then "A" else "a" end, count(*) from (select 'a' 
as a union all select null) a group by a;
+---------------------------------------+----------+
| case when a='a' then "A" else "a" end | count(*) |
+---------------------------------------+----------+
| A                                     |        1 |
| a                                     |        1 |
+---------------------------------------+----------+
2 rows in set (0.00 sec)

mysql>

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.21    |
+-----------+
1 row in set (0.00 sec)