MySQL Bugs: #3610: UNION works incorrect?

Bug #3610	UNION works incorrect?
Submitted:	30 Apr 2004 13:25	Modified:	14 May 2004 13:48
Reporter:	George Ivanov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0.18-standard	OS:	Linux (RedHat 7.3)
Assigned to:		CPU Architecture:	Any

Description:
Guess you have several tables of similar structure and you want to SELECT from all of them. You would also like to know from which table you receive every line of the result. So your query will be:

(SELECT 'Tabname1' AS 'Table', field1, field2, field3 FROM Tabname1 WHERE ... )
UNION
(SELECT 'Tabname2' AS 'Table', field1, field2, field3 FROM Tabname2 WHERE ... );

If Tabname1 and Tabname2 are !quite! different - everything goes ok and you receive the names of the tables in the Table column of the result.

But in case of slightly similar tablenames (in my case they were 'chronology' and 'chronology_other') one receives only the name of the first mentioned in the query table name (i.e. Tabname1) in all rows of the result.

Is it a correct behaviour?

How to repeat:
please see description

Can you please provide some examples of results you consider wrong?

To reproduce:

Lets make a couple of tables with one record each:
CREATE TABLE `test` (
`id` INT NOT NULL ,
`text` TEXT NOT NULL 
);
CREATE TABLE `test1` (
`id1` INT NOT NULL ,
`text1` TEXT NOT NULL 
);
INSERT INTO `test` ( `id` , `text` ) 
VALUES (
'1', 'abcd'
);
INSERT INTO `test1` ( `id1` , `text1` ) 
VALUES (
'1', 'bcdef'
);

Now run a select:
(SELECT 'test' AS 'Table', id, text FROM test WHERE text REGEXP ('bc'))
UNION
(SELECT 'test1' AS 'Table', id1, text1 FROM test1 WHERE text1 REGEXP ('bc'))

as a result you get:
Table  id  text  
test   1   abcd 
test   1   bcdef 

But for the case I assumed to get 'test' as Table field in the 1-st row and 'test1' as Table field in the second row.
And if you change the names of tables into quite diferent values (for example, like 'Names' and 'Companies') you'll get the expected result.

I guess that I am just doing or understand something wrong or this is not quite correct?
Thank you again.

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

This is deviation from standard in MySQL 4.0 which is changed in 4.1, but is not going to be changed in 4.0

See bug #3363 http://bugs.mysql.com/bug.php?id=3363

This UNION behaviour is also described in manual at

http://dev.mysql.com/doc/mysql/en/UNION.html