MySQL Bugs: #4937: INSERT + SELECT + UNION ALL + DATE to VARCHAR(8) conversion problem

Bug #4937	INSERT + SELECT + UNION ALL + DATE to VARCHAR(8) conversion problem
Submitted:	6 Aug 2004 22:17	Modified:	18 Aug 2004 14:31
Reporter:	Bicho Verde	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.20	OS:	Windows (Windows/Linux)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
I have two tables. One has a DATE field and the other has a VARCHAR(8) field. I want to INSERT the contents of the first table INTO the second, converting the type.

First I CREATE the tables.

Then I INSERT a date filed INTO the first.

After that I do a INSERT SELECT FROM the first INTO the second. Works fine.

I try SELECT FORM the first UNION ALL SELECT FROM the first. Also works fine.

But if I do a INSERT SELECT FORM the first UNION ALL SELECT FROM the first INTO the second, the numbers are displayed in scientific notation: "1.98e+00", instead of "19781126". And the number is not even right! It should be "1.98e+07" (on linux it is "1.98e+07").

If I don't use UNION ALL, it works. If I don't INSERT SELECT, it works. But the two together give me the scientific notation, and the notation is wrong on windows.

How to repeat:
DROP TABLE IF EXISTS table1;

CREATE TABLE table1 (
  field1 date NOT NULL default '0000-00-00'
) TYPE=MyISAM;

DROP TABLE IF EXISTS table2;

CREATE TABLE table2 (
  field2 varchar(8) NOT NULL default ''
) TYPE=MyISAM;

INSERT INTO table1 (field1) VALUES ('1978-11-26');

INSERT INTO table2 (field2)
  SELECT table1.field1 + 0 AS field2
    FROM table1
;

INSERT INTO table2 (field2)

  SELECT table1.field1 + 0 AS field2
    FROM table1

  UNION ALL

  SELECT table1.field1 + 0 AS field2
    FROM table1
;

I was able to verify this using 4.0.20 and 4.1.3 on windows 2k and linux.

I get this on linux with both versions:
+----------+
| 19781126 |
| 1.98e+07 |
| 1.98e+07 |
+----------+

I end up with this on windows using both versions:
+----------+
| 19781126 |
| 1.98e+00 |
| 1.98e+00 |
+----------+

bk commit - 4.0 tree (konstantin:1.1963) BUG#4937

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

fixed in 4.0.21