MySQL Bugs: #18318: NULL in the output file is not being represented as Empty String

Bug #18318	NULL in the output file is not being represented as Empty String
Submitted:	17 Mar 2006 20:39	Modified:	20 Mar 2006 16:37
Reporter:	Sum Reyetta	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4+	OS:	Windows (Windows)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
this statement is false as following this results in the NULL  value is still not being presented as an Empty String:
"With fixed-row format (which happens when FIELDS TERMINATED BY and FIELDS ENCLOSED BY are both empty), NULL is written as an empty string. Note that this causes both NULL values and empty strings in the table to be indistinguishable when written to the file because they are both written as empty strings. If you need to be able to tell the two apart when reading the file back in, you should not use fixed-row format."
here is the statement with FIELDS TERMINATED BY and FIELDS ENCLOSED BY are empty
SELECT *
FROM your_table
INTO OUTFILE '\path\to\your_file.csv'
FIELDS
ESCAPED BY ''
LINES
TERMINATED BY '\r\n';

The null values are still being represented by either \N or Null.  Following your statements in the Manual, I expect the Null to be represented as empty string =''.
-Sam

How to repeat:
SELECT *
FROM your_table
INTO OUTFILE '\path\to\your_file.csv'
FIELDS
ESCAPED BY ''
LINES
TERMINATED BY '\r\n';

Make sure you have NULL in some of the fields
1.Once the file is created, open it and look at the fields that are supposed to be NULLs

Thank you for the bug report.
Could you please point me the exactly place in our Manual that NULL
values are represented as empty string=''. Then should be a Documentation
bug.

http://dev.mysql.com/doc/refman/5.0/en/problems-with-null.html

A.5.3. Problems with NULL Values

The concept of the NULL value is a common source of confusion for newcomers to SQL, who often think that NULL is the same thing as an empty string ''. This is not the case. For example, the following statements are completely different: 

Thanks in advance.

Miguel, I got your email. Here is the article:
http://dev.mysql.com/doc/refman/4.1/en/load-data.html
1/2 down the page when it talks about NULLS, it states 
"With fixed-row format (which happens when FIELDS TERMINATED BY and FIELDS ENCLOSED BY are both empty), NULL is written as an empty string.
Note that this causes both NULL values and empty strings in the table to be indistinguishable when written to the file because they are both written as empty strings. If you need to be able to tell the two apart when reading the file back in, you should not use fixed-row format."

Which is what I'm trying to do. I have Nulls in the fields of my table and I want them to be outputed in a CSV file as '' or emptystring.
-Sam

Thank you for the feedback.

Fixed-row format requires that the fields TERMINATED BY and
ENCLOSED BY values both be empty ('').  The statement shown
in the bug report specified neither of those values, so the
defaults are used.  The default ENCLOSED BY is empty, but the
default TERMINATED BY is '\t', so the output will not be written
with fixed-row format.

*************************** 1. row ***************************
           Name: tb78
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 7036874417766399
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2006-03-17 17:56:10
    Update_time: 2006-03-20 13:08:27
     Check_time: 2006-03-18 10:23:39
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> insert into tb78 (id) values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT *
    -> FROM tb78
    -> INTO OUTFILE 'c:/temp/tb78.csv'
    -> FIELDS TERMINATED BY ''
    -> ENCLOSED BY ''
    -> LINES
    -> TERMINATED BY '\r\n';
Query OK, 2 rows affected (0.00 sec)

mysql> select * from tb78;
+----+------+
| id | name |
+----+------+
| 1  |      |
| 2  |      |
+----+------+
2 rows in set (0.00 sec)

mysql> desc tb78;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   |     |         |       |
| name  | char(20) | YES  |     |         |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.05 sec)

mysql> exit
Bye

c:\mysql\bin>type c:\temp\tb78.csv
1
2

Miguel,
I've copied your SQL statement and I'm getting \N instead of '' as a NULL representation.

select * 
from table1 limit 100
into outfile 'D:/table1.csv'
fields terminated by ''
enclosed by ''
lines terminated by '\r\n'