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: Italo asdf Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.20 OS:Microsoft Windows (Windows/Linux)
Assigned to: Konstantin Osipov CPU Architecture:Any

[6 Aug 2004 22:17] Italo asdf
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
;
[7 Aug 2004 3:32] Matthew Lord
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 |
+----------+
[17 Aug 2004 4:52] Konstantin Osipov
bk commit - 4.0 tree (konstantin:1.1963) BUG#4937
[18 Aug 2004 14:31] Sergei Golubchik
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