Re: Interesting query outcomes

In article <[email protected]>, Luuk  <[email protected]> wrote:
> 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...

I gave two simpler options to get the correct result in my posting further up
this thread, but Axel snipped those examples and just replied to my initial
query about "GROUP BY 1".

Although at the time I said "untested", I subsequently tried them both out and
verified they operated correctly. No need for nested selects, unions, etc.

Cheers
Tony
-- 
Tony Mountifield
Work: [email protected] - http://www.softins.co.uk
Play: [email protected] - http://tony.mountifield.org