Bug #12469 Group by did not return the correct result when there is a NULL value

• Submitted: 9 Aug 2005 17:21
• Modified: 10 Aug 2005 15:23
• Reporter: Dorn Bhechsonggram
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.11-standard
• OS: Linux (Fedora Core 1)
• CPU Architecture: Any

[9 Aug 2005 17:21] Dorn Bhechsonggram

Description:
Please see how to repeat.

How to repeat:
create table tab_a (col_a1 varchar(10));
create table tab_b (col_b1 varchar(10), col_b2 varchar(10));

insert into tab_a values ('A1');
insert into tab_a values ('A3');
insert into tab_a values ('A2');
insert into tab_a values ('A1');
insert into tab_a values ('A2');
insert into tab_a values ('A1');

insert into tab_b values ('A1','X');
insert into tab_b values ('A2','Y');

mysql> select col_b2, col_a1 from tab_a left join tab_b on col_a1 = col_b1 group by 1,2;
+--------+--------+
| col_b2 | col_a1 |
+--------+--------+
| NULL   | A3     |
| X      | A1     |
| Y      | A2     |
+--------+--------+
3 rows in set (0.00 sec)

mysql> select col_b2, col_a1 from tab_a left join tab_b on col_a1 = col_b1 where col_b2 !='Y' group by 1,2;
+--------+--------+
| col_b2 | col_a1 |
+--------+--------+
| X      | A1     |
+--------+--------+
1 row in set (0.00 sec)

I believe the result should contain the NULL in col_b2 as follows:
+--------+--------+
| col_b2 | col_a1 |
+--------+--------+
| NULL   | A3         |
| X        | A1         |
+--------+--------+

[10 Aug 2005 15:23] Aleksey Kishkin

Hi! in your example you assumed that NULL != 'Y'

But as the matter of fact, according to SQL standarts the only operation 'is NULL' valid for NULL comparision. All other operation with NULL values return NULL (not boolean value)

So in your example I believe you should use 
... where col_b2 !='Y' or col_b2 is NULL ..