Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA INFILE

NO_BACKSLASH_ESCAPES was not heeded in LOAD DATA INFILE
and SELECT INTO OUTFILE.  It is now.

mysql-test/r/loaddata.result:
  Show that SQL-mode NO_BACKSLASH_ESCAPES is heeded in
  INFILE/OUTFILE, and that dump/restore cycles work!
mysql-test/t/loaddata.test:
  Show that SQL-mode NO_BACKSLASH_ESCAPES is heeded in
  INFILE/OUTFILE, and that dump/restore cycles work!
sql/sql_class.cc:
  Add function to enquire whether ESCAPED BY was given.
  When doing SELECT...OUTFILE, use ESCAPED BY if specifically
  given; otherwise use sensible default value depending on
  SQL-mode features NO_BACKSLASH_ESCAPES.
sql/sql_class.h:
  Add function to enquire whether ESCAPED BY was given.
sql/sql_load.cc:
  When doing LOAD DATA INFILE, use ESCAPED BY if specifically
  given; otherwise use sensible default value depending on
  SQL-mode features NO_BACKSLASH_ESCAPES.

1 files changed, 118 insertions, 0 deletions

diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result
index 18b0d84a296..2aef2ade625 100644
--- a/mysql-test/r/loaddata.result
+++ b/mysql-test/r/loaddata.result
@@ -328,3 +328,121 @@ DROP VIEW v2;
 DROP VIEW v3;
 
 # -- End of Bug#35469.
+Bug#37114
+SET SESSION character_set_client=latin1;
+SET SESSION character_set_server=latin1;
+SET SESSION character_set_connection=latin1;
+SET @OLD_SQL_MODE=@@SESSION.SQL_MODE;
+test LOAD DATA INFILE
+CREATE TABLE t1 (id INT, val1 CHAR(3)) ENGINE=MyISAM;
+SET sql_mode = 'NO_BACKSLASH_ESCAPES';
+LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' REPLACE INTO TABLE t1 FIELDS TERMINATED BY ' ';
+SELECT * FROM t1;
+id	val1
+1	\aa
+SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114_out.txt' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1;
+SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114_out.txt' FIELDS               TERMINATED BY ' ' FROM t1;
+INSERT INTO t1 (id, val1) VALUES (1, '\aa');
+SELECT * FROM t1;
+id	val1
+1	\aa
+1	\aa
+SET sql_mode='';
+INSERT INTO t1 (id, val1) VALUES (1, '\aa');
+SELECT * FROM t1;
+id	val1
+1	\aa
+1	\aa
+1	aa
+DROP TABLE t1;
+test SELECT INTO OUTFILE
+CREATE TABLE t1 (id INT PRIMARY KEY, val1 CHAR(4));
+CREATE TABLE t2 LIKE t1;
+SET sql_mode = '';
+INSERT INTO t1 (id, val1) VALUES (5, '\ttab');
+INSERT INTO t1 (id, val1) VALUES (4, '\\r');
+SET sql_mode = 'NO_BACKSLASH_ESCAPES';
+INSERT INTO t1 (id, val1) VALUES (3, '\tx');
+1.1 NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY
+SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS TERMINATED BY ' ';
+SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION
+SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC;
+t	id	val1	hex(val1)
+before	3	\tx	5C7478
+after	3	\tx	5C7478
+before	4	\r	5C72
+after	4	\r	5C72
+before	5		tab	09746162
+after	5		tab	09746162
+TRUNCATE t2;
+SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt");
+LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt")
+3 \tx
+4 \r
+5 	tab
+
+1.2 NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY
+SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS ESCAPED BY '\' TERMINATED BY ' ' FROM t1 ORDER BY id;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS ESCAPED BY '\' TERMINATED BY ' ';
+SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION
+SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC;
+t	id	val1	hex(val1)
+before	3	\tx	5C7478
+after	3	\tx	5C7478
+before	4	\r	5C72
+after	4	\r	5C72
+before	5		tab	09746162
+after	5		tab	09746162
+TRUNCATE t2;
+SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt");
+LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt")
+3 \\tx
+4 \\r
+5 	tab
+
+SET sql_mode = '';
+2.1 !NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY
+SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS TERMINATED BY ' ';
+SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION
+SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC;
+t	id	val1	hex(val1)
+before	3	\tx	5C7478
+after	3	\tx	5C7478
+before	4	\r	5C72
+after	4	\r	5C72
+before	5		tab	09746162
+after	5		tab	09746162
+TRUNCATE t2;
+SET sql_mode = 'NO_BACKSLASH_ESCAPES';
+SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt");
+LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt")
+3 \\tx
+4 \\r
+5 	tab
+
+SET sql_mode = '';
+2.2 !NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY
+SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1 ORDER BY id;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS ESCAPED BY '' TERMINATED BY ' ';
+SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION
+SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC;
+t	id	val1	hex(val1)
+before	3	\tx	5C7478
+after	3	\tx	5C7478
+before	4	\r	5C72
+after	4	\r	5C72
+before	5		tab	09746162
+after	5		tab	09746162
+TRUNCATE t2;
+SET sql_mode = 'NO_BACKSLASH_ESCAPES';
+SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt");
+LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt")
+3 \tx
+4 \r
+5 	tab
+
+set session sql_mode=@OLD_SQL_MODE;
+DROP TABLE t1,t2;
+End of 5.0 tests