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?

Post by 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
select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;
| 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

[Joe]

Post by 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?

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?

Post by Joe

Post by 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?

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]

Post by Lyle H. Gray
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]

Post by Lyle H. Gray

Post by Lyle H. Gray
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...

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

[Jerry Stuckle]

Post by Joe
select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;
| 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

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

[J.O. Aho]

Post by Joe
select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;
| 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).

[Joe]

Post by J.O. Aho

Post by Joe
select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;
| 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

[J.O. Aho]

Post by Joe

Post by J.O. Aho

Post by Joe
select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;
| 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).

mysql> select distinct (select concat('"',TNAM'"')) from ITEMS order by 1;
+-----------+
| 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.

[Tony Mountifield]

Post by Joe
I have a MySQL table field sometimes with NULL or empty values when there is no data. When I wrote the following to
select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;
| 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???

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

[Axel Schwenke]

Post by Tony Mountifield

Post by Joe
I have a MySQL table field sometimes with NULL or empty values when there is no data. When I wrote the following to
select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;
| 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]

Post by Axel Schwenke

Post by Tony Mountifield

Post by Joe
I have a MySQL table field sometimes with NULL or empty values when there is no data. When I wrote the following to
select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;
| 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)

[Tony Mountifield]

Post by Luuk

Post by Axel Schwenke

Post by Tony Mountifield

Post by Joe
I have a MySQL table field sometimes with NULL or empty values when there is no data. When I wrote the following to
select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;
| 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

[Joe]

Post by Tony Mountifield

I have a MySQL table field sometimes with NULL or empty values when there is no data. When I wrote the following to
select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;
| 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.

Post by Tony Mountifield
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]

Post by Joe
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'
ELSE TNAM END, count(1)
from ITEMS group by 1;

[Luuk]

Post by Johann Klammer

Post by Joe
select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

select CASE WHEN (TNAM is NULL || TNAM=''|| TNAM='default') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

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 |
+---------+------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[Joe]

Post by Johann Klammer

Post by Joe
select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

select CASE WHEN (TNAM is NULL || TNAM=''|| TNAM='default') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

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