MySQL Bugs: #111754: When dealing with NULL，select into out file sometimes will be wrong.

Bug #111754	When dealing with NULL，select into out file sometimes will be wrong.
Submitted:	14 Jul 2023 3:20	Modified:	15 Aug 2023 10:03
Reporter:	linkang zhang (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	select into outfile

Description:
When SQL_MODE='NO_BACKSLASH_ESCAPES'，select into out file get wrong results.

How to repeat:

mysql > CREATE database bugs;

mysql > use bugs;

mysql > SET SQL_MODE= 'NO_BACKSLASH_ESCAPES';

mysql > CREATE TABLE t1 (a VARCHAR(255));

mysql > SELECT NULL INTO OUTFILE 't1.data';

mysql > LOAD DATA INFILE 't1.data' INTO TABLE t1;

mysql > SELECT a, a is NULL FROM t1;
+------+-----------+
| a    | a is NULL |
+------+-----------+
| NULL |         0 |
+------+-----------+
1 row in set (0.00 sec)

// 'a is NULL' should be 1, however it is 0.

Hi Mr. zhang,

Thank you for your bug report.

We managed to repeat the behaviour that you reported.

This is a bug , because NO_BACKSLASH_ESCAPES should not affect this behaviour.

This is now a verified bug.

Synopsis

Hi Mr. zhang,

We agree with a change in synopsis.

Posted by developer:
 
I had a tentative fix, but my reviewer says:

=== Bug report summary ===

Bug#35605113 When SQL_MODE=NO_BACKSLASH_ESCAPES select into out file will be wrong.

Bug report: When the SQL mode NO_BACKSLASH_ESCAPES is enabled and the user
issues "SELECT NULL INTO OUTFILE..." followed by "LOAD DATA INFILE ..." to load
the data file into a table with a single varchar column, what ends up happening
is that the text string NULL is inserted into the varchar column. The bug
reporter expected an actual NULL value to be inserted into the table instead.

=== What actually happens ===

When NO_BACKSLASH_ESCAPES is enabled the NULL value is written to the text file
as the character string NULL, instead of the default \N.

This seems consistent with our documentation.

From the SELECT INTO documentation:

https://dev.mysql.com/doc/refman/8.0/en/select-into.html

"If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL
is output as NULL, not \N. It is probably not a good idea to specify an empty
escape character, particularly if field values in your data contain any of the
characters in the list just given."

=== Problems with the proposed fix ===

The proposed fix changes the text string NULL to be interpreted as an actual
NULL value if NO_BACKSLASH_ESCAPES is enabled. This can produce equally
surprising/buggy behavior in the sense that now the string 'NULL' will be
interpreted as an actual NULL value:
CREATE TABLE t1 (a VARCHAR(255), b VARCHAR(255));
SET SQL_MODE = 'NO_BACKSLASH_ESCAPES';
SELECT NULL, 'NULL' INTO OUTFILE 't1.data';
LOAD DATA INFILE '1.data' INTO TABLE t1;
SELECT a, a IS NULL, b, b IS NULL FROM t1;

-- Without patch:
-- +------+-----------+------+-----------+
-- | a    | a IS NULL | b    | b IS NULL |
-- +------+-----------+------+-----------+
-- | NULL |         0 | NULL |         0 |
-- +------+-----------+------+-----------+

-- With patch:
-- +------+-----------+------+-----------+
-- | a    | a IS NULL | b    | b IS NULL |
-- +------+-----------+------+-----------+
-- | NULL |         1 | NULL |         1 |
-- +------+-----------+------+-----------+

=== Solution ===

I think the bug report should be rejected as not a bug for the following reasons:

1) The current behavior seems consistent with our documentation. In the
NO_BACKSLASH_ESCAPES mode we effectively have no escape character and NULL
values are output as NULL and not \N as stated in our documentation.

2) If the user wants to preserve NULL values in NO_BACKSLASH_ESCAPES mode, they
can use the FIELDS ESCAPED BY option. This also allows the user to distinguish
between the string 'NULL' and actual NULL values.
CREATE TABLE t1 (a VARCHAR(255), b VARCHAR(255));
SET SQL_MODE = 'NO_BACKSLASH_ESCAPES';
SELECT NULL, 'NULL' INTO OUTFILE 't1.data' FIELDS ESCAPED BY '#';
LOAD DATA INFILE 't1.data' INTO TABLE t1 FIELDS ESCAPED BY '#';
SELECT a, a IS NULL, b, b IS NULL FROM t1;

-- Without patch:
-- +------+-----------+------+-----------+
-- | a    | a IS NULL | b    | b IS NULL |
-- +------+-----------+------+-----------+
-- | NULL |         1 | NULL |         0 |
-- +------+-----------+------+-----------+

-- With patch:
-- +------+-----------+------+-----------+
-- | a    | a IS NULL | b    | b IS NULL |
-- +------+-----------+------+-----------+
-- | NULL |         1 | NULL |         1 |
-- +------+-----------+------+-----------+

=== More details from the documentation === 

From the NO_BACKSLASH_ESCAPES documentation:

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_backslash_escapes

NO_BACKSLASH_ESCAPES

Enabling this mode disables the use of the backslash character (\) as an escape
character within strings and identifiers. With this mode enabled, backslash
becomes an ordinary character like any other, and the default escape sequence
for LIKE expressions is changed so that no escape character is used.

From the documentation on string comparison functions:

https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html

To test for literal instances of a wildcard character, precede it by the escape
character. If you do not specify the ESCAPE character, \ is assumed, unless the
NO_BACKSLASH_ESCAPES SQL mode is enabled. In that case, no escape character is
used.

Posted by developer:
 
So the conclusion is: not a bug

Thank you, very much, Tor.

We understand and fully accept your analysis and we are setting this report as "Not a bug".

Please set this bug 'feature request'.
and add some explanation in the document.

Hi Mr. zhang,

If you have read our previous comments, you would have seen that this is already documented behaviour.

Hence, we do not understand what is it that you would want us to document, since the current text is sufficient.