MySQL Bugs: #4112: UNION makes fields disappear

Bug #4112	UNION makes fields disappear
Submitted:	11 Jun 2004 20:01	Modified:	27 Aug 2004 13:58
Reporter:	olivier menuel	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.1-alpha	OS:	UNIX
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When I use a UNION with certain fields' names or certain table names, it does not work :
( I attached the db file so that you can test )

SELECT salesperson.contact_first_name AS jordy
FROM trans_salesperson, salesperson
WHERE salesperson.id = user_id AND trans_salesperson.salesman_id =2979 AND user_type = 'salesperson'
UNION 
SELECT 'fff' AS jordy
FROM trans_salesperson

this request does not work : I have only the results of the second select

but if I change the field in the first select, it works :
SELECT 'dddd' AS jordy
FROM trans_salesperson, salesperson
WHERE salesperson.id = user_id AND trans_salesperson.salesman_id =2979 AND user_type = 'salesperson'
UNION 
SELECT 'fff' AS jordy
FROM trans_salesperson

and if I just set any other tables in the second from, it works too :
SELECT salesperson.contact_first_name AS jordy
FROM trans_salesperson, salesperson
WHERE salesperson.id = user_id AND trans_salesperson.salesman_id =2979 AND user_type = 'salesperson'
UNION 
SELECT 'fff' AS jordy
FROM salesperson

It is really weird. And the weirdest thing is that I just have to rename the second from and it works, it really seems it does not like my table name !!
This works :
and if I just set any other tables in the second from, it works too :
SELECT salesperson.contact_first_name AS jordy
FROM trans_salesperson, salesperson
WHERE salesperson.id = user_id AND trans_salesperson.salesman_id =2979 AND user_type = 'salesperson'
UNION 
SELECT 'fff' AS jordy
FROM trans_salesperson as table_cool

How to repeat:
SELECT salesperson.contact_first_name AS jordy
FROM trans_salesperson, salesperson
WHERE salesperson.id = user_id AND trans_salesperson.salesman_id =2979 AND user_type = 'salesperson'
UNION 
SELECT 'fff' AS jordy
FROM trans_salesperson

This will just display the results of the second select!

two tables of my database, that will allow you to see the bug !

Attachment: bug_report.sql (text/plain), 8.19 KiB.

Hi Olivier,

I could not reproduce this problem in 4.1.2.

root@localhost:bug4112~> SELECT salesperson.contact_first_name AS jordy
    -> FROM trans_salesperson, salesperson
    -> WHERE salesperson.id = user_id AND trans_salesperson.salesman_id =2979 AND
    -> user_type = 'salesperson'
    -> UNION 
    -> SELECT 'fff' AS jordy
    -> FROM trans_salesperson;
+------------+
| jordy      |
+------------+
| olivier!!! |
| fff        |
+------------+
2 rows in set (0.00 sec)

Could you try your query in 4.1.2 to make sure you're not still having the same problem on your end?

Best Regards,

-matt-

No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Open". Thank you.