Interesting query outcomes

[Joe]

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?

Joe

[Lyle H. Gray]

I am unable to duplicate this effect.

What is the full structure of your table, and what version of MySQL are you
using?


Joe <zhili...@gmail.com> wrote in
news:6266a024-14d7-43a9...@googlegroups.com:

[Joe]

On Tuesday, August 18, 2020 at 5:34:12 PM UTC-5, Lyle H. Gray wrote:
> I am unable to duplicate this effect.
>
> What is the full structure of your table, and what version of MySQL are you
> using?

UNIX> mysql --version
Ver 14.14 Distrib 5.6.45, for Linux (x86_64) using EditLine wrapper

MYSQL> desc jcrosstbl;
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-----------------------------+
| gn_ID | varchar(12) | NO | PRI | | |
| TNAM | varchar(96) | YES | | NULL | |
| Descrptn | varchar(512) | YES | | NULL | |
| created | datetime | YES | | NULL | |
| updated | datetime | YES | | NULL | on update CURRENT_TIMESTAMP |
| status | int(1) | YES | | NULL |

It also duplicates when I port the table to an older server where MySQL is
Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1.

If I do
mysql> update jcrosstbl set TNAM=NULL where TNAM='';
the duplicates get combined.

joe

[Lyle H. Gray]

I'm using 5.7.28.

I don't know why this is happening on your server, but you can obviously
verify whether or not the counts represent the different source values by
simply using

SELECT TNAM, COUNT(1)
FROM jcrosstbl GROUP BY 1 ;

If the counts match, then I would guess that the GROUP BY is working on
the original column values, rather than the results of the CASE WHEN
(which does not make sense to me).

Have you tried using either the IFNULL() or COALESCE() functions instead
of the CASE WHEN block?


Joe <zhili...@gmail.com> wrote in
news:29c28bb7-2da7-4793...@googlegroups.com:

[Lyle H. Gray]

"Lyle H. Gray" <lyle...@no.spam.gmail.com.invalid> wrote in
news:XnsAC1E8D2852FE4gr...@216.166.97.131:

> Have you tried using either the IFNULL() or COALESCE() functions
> instead of the CASE WHEN block?

Of course, that would only work if chose to convert a NULL to a blank to
match the other blanks, rather than 'default', so that might not be an
option for you...

[Lyle H. Gray]

"Lyle H. Gray" <lyle...@no.spam.gmail.com.invalid> wrote in

news:XnsAC1E8DD9CD6FCgr...@216.166.97.131:

You might also try using IF(expr1,expr2,expr3) to see if it changes your
results.

[Jerry Stuckle]

My quest would be the GROUP BY clause is being applied before the CASE
WHEN clause.


--
==================
Remove the "x" from my email address
Jerry Stuckle
jstu...@attglobal.net
==================

[J.O. Aho]

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

[Joe]

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

[J.O. Aho]

On 20/08/2020 02.10, Joe wrote:
> 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).
>

> 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.

You can have a outer select with grouping, this should fix your issue

select a.names, sum(a.counts) as amount from (

select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'

ELSE TNAM END as names, count(1) as counts
from ITEMS group by names;
) as a group by a.names

I haven't tested this on mysql, seldom I do write SQL for mysql
nowadays, but should at least work on a t-sql engine like sybase.

--

//Aho

[Tony Mountifield]

In article <6266a024-14d7-43a9...@googlegroups.com>,

What does "GROUP BY 1" even mean???

When doing a GROUP BY, the field list in SELECT should only contain either
columns listed in the GROUP BY, or aggregate functions. This is enforced
by other SQL engines, but not by MySQL, although it is in the SQL standard.

So your query should be:

SELECT CASE WHEN TNAM IS NULL OR TNAM = '' THEN 'default' ELSE TNAM END AS tnam2, COUNT(*)
FROM ITEMS
GROUP BY tnam2;

Or you could say:

SELECT IFNULL(NULLIF(TNAM, ''), 'default') AS tnam2, COUNT(*)
FROM ITEMS
GROUP BY tnam2;

(not tested)

Cheers
Tony
--
Tony Mountifield
Work: to...@softins.co.uk - http://www.softins.co.uk
Play: to...@mountifield.org - http://tony.mountifield.org

[Axel Schwenke]

On 20.08.2020 11:28, Tony Mountifield wrote:
> In article <6266a024-14d7-43a9...@googlegroups.com>,
> Joe <zhili...@gmail.com> 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.

[Luuk]

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)

[Tony Mountifield]

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.

[Joe]

On Thursday, August 20, 2020 at 4:28:59 AM UTC-5, Tony Mountifield wrote:
> In article <6266a024-14d7-43a9...@googlegroups.com>,
> Joe <zhili...@gmail.com> 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???

I did more experiments and found "group by 1" and "group by TNAM" did gave different outcomes in terms of separate or combines "default" counts. Probably I should stop here as Luuk said my MySQLs are too old. Will yet to to see how it works on the current version.

> Or you could say:
>
> SELECT IFNULL(NULLIF(TNAM, ''), 'default') AS tnam2, COUNT(*)
> FROM ITEMS
> GROUP BY tnam2;

I like this IFNULL(NULLIF(TNAM, '') construct. That's neat!

joe

[Johann Klammer]

On 08/18/2020 10:33 PM, Joe wrote:
> select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
> ELSE TNAM END, count(1)
> from ITEMS group by 1;

What happens if you do:

select CASE WHEN (TNAM is NULL || TNAM=''|| TNAM='default') THEN 'default'

[Luuk]

a warning will be shown.


mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------+
| Level | Code | Message
|
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '|| as a synonym for OR' is deprecated and will be removed
in a future release. Please use OR instead |
+---------+------+-------------------------------------------------------------------------------------------------------+

[Joe]

Interesting thoughts but it didn't help (tried) because there is no 'default' value in the table.