MySQL Bugs: #618: UNION clause does not work properly.

Bug #618	UNION clause does not work properly.
Submitted:	6 Jun 2003 22:46	Modified:	3 Jul 2003 5:51
Reporter:	Asish Kumar Samanta	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.12	OS:	Windows (Windows NT)
Assigned to:		CPU Architecture:	Any

Description:
When I run the sql query :

select a.group_id as "Select", a.group_name as "Group Name", 
date_format(a.group_created_date,"%M %e, %Y %H:%i") as "Created Date",
date_format(a.last_modification_date,"%M %e, %Y %H:%i") as "Last Modified", a.learning_style as "Style" , 
count(c.group_id) as "No. of Members" from student_group a left join 
student_group_association c on a.group_id = c.group_id 
where a.group_id = 'FIN' group by c.group_id, a.group_name, a.group_id union
select a.group_id as "Select", a.group_name as "Group Name", 
date_format(a.group_created_date,"%M %e, %Y %H:%i") as "Created Date",
date_format(a.last_modification_date,"%M %e, %Y %H:%i") as "Last Modified", a.learning_style as "Style" , 
count(c.group_id) as "No. of Members" from student_group a left join student_group_association c 
on a.group_id = c.group_id 
where a.group_id <> 'FIN' group by c.group_id, a.group_name, a.group_id;

the buggy result will come:

+--------+-------------+--------------------+--------------------+----------+----------------+
| Select | Group Name  | Created Date       | Last Modified      | Style    | No. of Members |
+--------+-------------+--------------------+--------------------+----------+----------------+
| FIN    | Finance     | June 6, 2003 19:32 | June 6, 2003 19:32 | Advanced |              1 |
| DEV    | Development | June 6, 2003 19:38 | June 6, 2003 19:38 | Expert   |              0 |
+--------+-------------+--------------------+--------------------+----------+----------------+
2 rows in set (0.00 sec). Actually in the count column will be 0 but in  first row it shows 1.

If I ommit the "Union" clause, it shows the result properly:

select a.group_id as "Select", a.group_name as "Group Name", 
date_format(a.group_created_date,"%M %e, %Y %H:%i") as "Created Date",
date_format(a.last_modification_date,"%M %e, %Y %H:%i") as "Last Modified", a.learning_style as "Style" , 
count(c.group_id) as "No. of Members" from student_group a left join 
student_group_association c on a.group_id = c.group_id 
where a.group_id = 'FIN' group by c.group_id, a.group_name, a.group_id;

+--------+------------+--------------------+--------------------+----------+----------------+
| Select | Group Name | Created Date       | Last Modified      | Style    | No. of Members |
+--------+------------+--------------------+--------------------+----------+----------------+
| FIN    | Finance    | June 6, 2003 19:32 | June 6, 2003 19:32 | Advanced |              0 |
+--------+------------+--------------------+--------------------+----------+----------------+
1 row in set (0.00 sec)

and
 
+--------+-------------+--------------------+--------------------+--------+----------------+
| Select | Group Name  | Created Date       | Last Modified      | Style  | No. of Members |
+--------+-------------+--------------------+--------------------+--------+----------------+
| DEV    | Development | June 6, 2003 19:38 | June 6, 2003 19:38 | Expert |              0 |
+--------+-------------+--------------------+--------------------+--------+----------------+
1 row in set (0.00 sec)

How to repeat:
When I run the sql query :

select a.group_id as "Select", a.group_name as "Group Name", 
date_format(a.group_created_date,"%M %e, %Y %H:%i") as "Created Date",
date_format(a.last_modification_date,"%M %e, %Y %H:%i") as "Last Modified", a.learning_style as "Style" , 
count(c.group_id) as "No. of Members" from student_group a left join 
student_group_association c on a.group_id = c.group_id 
where a.group_id = 'FIN' group by c.group_id, a.group_name, a.group_id union
select a.group_id as "Select", a.group_name as "Group Name", 
date_format(a.group_created_date,"%M %e, %Y %H:%i") as "Created Date",
date_format(a.last_modification_date,"%M %e, %Y %H:%i") as "Last Modified", a.learning_style as "Style" , 
count(c.group_id) as "No. of Members" from student_group a left join student_group_association c 
on a.group_id = c.group_id 
where a.group_id <> 'FIN' group by c.group_id, a.group_name, a.group_id;

the buggy result will come:

+--------+-------------+--------------------+--------------------+----------+----------------+
| Select | Group Name  | Created Date       | Last Modified      | Style    | No. of Members |
+--------+-------------+--------------------+--------------------+----------+----------------+
| FIN    | Finance     | June 6, 2003 19:32 | June 6, 2003 19:32 | Advanced |              1 |
| DEV    | Development | June 6, 2003 19:38 | June 6, 2003 19:38 | Expert   |              0 |
+--------+-------------+--------------------+--------------------+----------+----------------+
2 rows in set (0.00 sec). Actually in the count column will be 0 but in  first row it shows 1.

If I ommit the "Union" clause, it shows the result properly:

select a.group_id as "Select", a.group_name as "Group Name", 
date_format(a.group_created_date,"%M %e, %Y %H:%i") as "Created Date",
date_format(a.last_modification_date,"%M %e, %Y %H:%i") as "Last Modified", a.learning_style as "Style" , 
count(c.group_id) as "No. of Members" from student_group a left join 
student_group_association c on a.group_id = c.group_id 
where a.group_id = 'FIN' group by c.group_id, a.group_name, a.group_id;

+--------+------------+--------------------+--------------------+----------+----------------+
| Select | Group Name | Created Date       | Last Modified      | Style    | No. of Members |
+--------+------------+--------------------+--------------------+----------+----------------+
| FIN    | Finance    | June 6, 2003 19:32 | June 6, 2003 19:32 | Advanced |              0 |
+--------+------------+--------------------+--------------------+----------+----------------+
1 row in set (0.00 sec)

and
 
+--------+-------------+--------------------+--------------------+--------+----------------+
| Select | Group Name  | Created Date       | Last Modified      | Style  | No. of Members |
+--------+-------------+--------------------+--------------------+--------+----------------+
| DEV    | Development | June 6, 2003 19:38 | June 6, 2003 19:38 | Expert |              0 |
+--------+-------------+--------------------+--------------------+--------+----------------+
1 row in set (0.00 sec)

Suggested fix:
I think, there is a problem in Union clause. I also run the above sql query in SQL Server and Oracle, it works properly.

We are very interested in fixing this bug.

In order to be able to do that, we need tables involved.

Upload them to :

ftp://support.mysql.com:/pub/mysql/secret

and let us know a name of the file.

Hi, 

I am unable to upload the file. I paste it here. Pls copy it. Run this query, it creates two tables with the name "student_group" and "student_group_association". Also it inserts two rows in the table "student_group" and there is no row in the table "student_group_association".

SQL:

/********************** student_group ***************************/

drop table if exists student_group ;

create table student_group
(
  group_id 					varchar(25) not null,
  group_name 				varchar(25) not null,
  group_created_date 			datetime,
  group_created_by 			varchar(25) not null,
  last_modification_date 		datetime,
  learning_style  	varchar(25),
  primary key(group_id)
) TYPE=INNODB;

/*******************************************************************/

insert into table student_group values ( 'DEV','Development','2003-06-06 19:38:02','superadmin','2003-06-06 19:38:02','Expert');
insert into table student_group values ( 'FIN','Finance','2003-06-06 19:32:18','superadmin','2003-06-06 19:32:18','Advanced');

/****************** student_group_association **********************/

drop table if exists student_group_association ;

create table student_group_association
(
  group_id 					varchar(25) not null,
  student_id 				varchar(25) not null,
   
  primary key(group_id,student_id)
) TYPE=INNODB;

/*******************************************************************/

Is there any progress?

I have tested your query with the last release 4.0.13 and showed the
expected result, please upgrade and verify the query result:

c:\mysql\bin>mysql test -E
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.13-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select a.group_id as "Select", a.group_name as "Group Name",
    -> date_format(a.group_created_date,"%M %e, %Y %H:%i") as "Created Date"
    -> date_format(a.last_modification_date,"%M %e, %Y %H:%i") as "Last
    "> Modified", a.learning_style as "Style" ,
    -> count(c.group_id) as "No. of Members" from student_group a left join
    -> student_group_association c on a.group_id = c.group_id
    -> where a.group_id = 'FIN' group by c.group_id, a.group_name, a.group_i
    -> union
    -> select a.group_id as "Select", a.group_name as "Group Name",
    -> date_format(a.group_created_date,"%M %e, %Y %H:%i") as "Created Date"
    -> date_format(a.last_modification_date,"%M %e, %Y %H:%i") as "Last
    "> Modified", a.learning_style as "Style" ,
    -> count(c.group_id) as "No. of Members" from student_group a left join
    -> student_group_association c
    -> on a.group_id = c.group_id
    -> where a.group_id <> 'FIN' group by c.group_id, a.group_name,
    -> a.group_id;
*************************** 1. row ***************************
        Select: FIN
    Group Name: Finance
  Created Date: June 6, 2003 19:32
 Last
Modified: June 6, 2003 19:32
         Style: Advanced
No. of Members: 0
*************************** 2. row ***************************
        Select: DEV
    Group Name: Development
  Created Date: June 6, 2003 19:38
 Last
Modified: June 6, 2003 19:38
         Style: Expert
No. of Members: 0
2 rows in set (0.01 sec)

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/