| Bug #4937 | INSERT + SELECT + UNION ALL + DATE to VARCHAR(8) conversion problem | ||
|---|---|---|---|
| Submitted: | 7 Aug 2004 0:17 | Modified: | 18 Aug 2004 16:31 |
| Reporter: | Italo | ||
| Status: | Closed | ||
| Category: | Server | Severity: | S2 (Serious) |
| Version: | 4.0.20 | OS: | Microsoft Windows (Windows/Linux) |
| Assigned to: | Konstantin Osipov | Target Version: | |
[7 Aug 2004 5: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 6:52]
Konstantin Osipov
bk commit - 4.0 tree (konstantin:1.1963) BUG#4937
[18 Aug 2004 16: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
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 ;