| Bug #1098 | Select into outfile with empty field separator and datetime+0 | ||
|---|---|---|---|
| Submitted: | 19 Aug 2003 8:48 | Modified: | 10 Sep 2003 0:39 |
| Reporter: | Roland Rosenfeld | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.0.14 | OS: | sun-solaris2.9-sparc |
| Assigned to: | Victor Vagin | CPU Architecture: | Any |
[6 Sep 2003 5:45]
Victor Vagin
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Actually new version of mysql uses 'fixed size' format of row to output fields when the 'FIELDS TERMINATED BY' and 'OPTIONALLY ENCLOSED BY' are empty at the same time.. We'll add this description in our documentation.. Thanks a lot for your note.. I think it's better to use SELECT ... INTO DUMPFILE for things like you describe (actually you don't have to even use '+0' ;) ).. DUMPFILE option was made especially for such tasks..
[8 Sep 2003 2:03]
Victor Vagin
Actually, manual for SELECT INTO OUTFILE contains reference to part for LOAD DATA INFILE. So this page: http://www.mysql.com/doc/en/LOAD_DATA.html explains about fixed-row output (and input) already.
[8 Sep 2003 3:07]
Roland Rosenfeld
But I still don't understand this completely. Why does foo+0 refer to the format of foo? Isn't foo+0 a new expression? Would "SELECT foo+0 AS baz" change anything on this? On the other hand the type of foo is datetime, which is 19 chars long: 2003-08-19 17:26:04 while the output in file is 23 chars long: 20030819172604 x (the "x" shows where the next column begins). So I would understand to get 20030819172604 x (19 chars long), but not the 23 chars.
[10 Sep 2003 0:39]
Sergei Golubchik
It's because DATETIME is seen as string. And when you do "string"+number, the result is a double. 23 is what MySQL uses for doubles of unknown width (e.g. when converted from string) so that it can be sure any value will fit. To export the date as 20030819172604 without any extra spaces you may use DATE_FORMAT: SELECT DATE_FORMAT(datetimecolumn,"%Y%m%d%H%i%s")
Description: When I do select datetimecolumn+0, othercolumn into outfile 'foo' fields terminated by '' this appends 9 spaces after the output of datetimecolumn+0. The problem disappears when changing the fields termator to ';' or something else. How to repeat: First create and fill the table: CREATE TABLE spacetest ( foo datetime NOT NULL default '0000-00-00 00:00:00', bar char(1) NOT NULL default 'x' ) TYPE=MyISAM; INSERT INTO `spacetest` VALUES ('2003-08-19 17:26:04', 'x'); Now try to export: select foo+0, bar into outfile 'test1.txt' fields terminated by '' from spacetest; It gives: 20030819172604 x instead of the expected 20030819172604x The problem seems only to happen in the combination of datetimecolumn+0 and fields terminated by ''. With a very old mysql-3.22.32-sun-solaris2.7-sparc (where I am migrating some scripts from to the new MySQL version) the problem wasn't present.