Re: Interesting query outcomes

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 <[email protected]> wrote in
news:[email protected]: 

> 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