Bug #3753 NULL string handling of LOAD DATA INFILE input is case sensitive; should not be

• Submitted: 14 May 2004 1:33
• Modified: 21 Feb 2007 14:23
• Reporter: Bernard Treine
• Status: Can't repeat
• Category: MySQL Server: MyISAM storage engine
• Severity: S4 (Feature request)
• Version: 3.23.56
• OS: Linux (Linux)
• CPU Architecture: Any

[14 May 2004 1:33] Bernard Treine

Description:
Reference:

http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

Quote start

If FIELDS ENCLOSED BY is not empty, a field containing the literal word NULL
as its value is read as a NULL value. This differs from the word NULL enclosed
within FIELDS ENCLOSED BY characters, which is read as the string 'NULL'.

Quote end

The string NULL is case sensitive, upper case.

This case sensitivity is inconvenient in the Java language.
Upper case is consistent with the manual text, however it is not consistent
with SQL in general where keywords such as "NULL" are case insensitive.

Suppose you want to export a Java Integer value by using a StringBuffer for
a single record:

CREATE TABLE MY_TABLE(myIntegerField INT);

Statement stmt = ...
ResultSet rs = stmt.executeQuery("SELECT * FROM MY_TABLE");
rs.next();
StringBuffer recBuf = new StringBuffer();
recBuf.append(rs.getObject("myIntegerField"));
recBuf.append(',');
...
Other fields
...
String recordString = new String(recBuf);

If the Integer in the myIntegerField is null then recordString is:

"null,..,..,.."

This lower case null results in the default value 0 to be inserted into the table,
and NOT the expected null value.

Therefore, it would be highly desirable that MySQL is not case sensitive here.

Application bugs in this area may be extremely difficult to find, depending
on how frequently null values occur in import files.

How to repeat:
Please refer to the above.

Suggested fix:
Make NULL case insensitive.

[19 May 2004 20:24] MySQL Verification Team

This is not a bug.

This is a feature request.

[21 Feb 2007 14:23] Sveta Smirnova

test case

Attachment: bug3753.java (text/java), 1.92 KiB.

[21 Feb 2007 14:23] Sveta Smirnova

Thank you for the report.

This feature is already implemented. See also attached file.