bzr commit into mysql-5.1 branch (azundris:2661) Bug#37114

[Tatiana A. Nurnberg]

#At file:///misc/mysql/forest/37114/51-37114/

 2661 Tatiana A. Nurnberg	2008-06-13
      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.
modified:
  mysql-test/r/loaddata.result
  mysql-test/t/loaddata.test
  sql/log_event.cc
  sql/sql_class.cc
  sql/sql_class.h
  sql/sql_yacc.yy

per-file messages:
  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/log_event.cc
    heed NO_BACKSLASH_ESCAPES
  sql/sql_class.cc
    sql_exchange-object gets default ESCAPED BY
    according to current SQL-mode
    (NO_BACKSLASH_ESCAPES?).
  sql/sql_class.h
    Signature of sql_exchange() constructor
    changed so we cahn pass in whether SQL-mode
    NO_BACKSLASH_ESCAPES is active.
  sql/sql_yacc.yy
    pass SQL-mode to sql_exchange() constructor
    so it can set a suitable default for
    ESCAPED BY.
=== modified file 'mysql-test/r/loaddata.result'
--- a/mysql-test/r/loaddata.result	2008-03-28 21:05:20 +0000
+++ b/mysql-test/r/loaddata.result	2008-06-13 17:12:45 +0000
@@ -364,3 +364,91 @@ SET character_set_filesystem=default;
 select @@character_set_filesystem;
 @@character_set_filesystem
 binary
+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;
+SET sql_mode = '';
+CREATE TABLE t1 (id INT PRIMARY KEY, val1 CHAR(4));
+1.1 NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY
+INSERT INTO t1 (id, val1) VALUES (4, '\\r');
+SET sql_mode = 'NO_BACKSLASH_ESCAPES';
+INSERT INTO t1 (id, val1) VALUES (3, '\tx');
+SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS TERMINATED BY ' ' FROM
t1 ORDER BY id;
+UPDATE t1 SET id=id-2;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t1 FIELDS TERMINATED BY '
';
+SELECT id,val1,HEX(val1) FROM t1 ORDER BY id;
+id	val1	HEX(val1)
+1	\tx	5C7478
+2	\r	5C72
+3	\tx	5C7478
+4	\r	5C72
+TRUNCATE t1;
+SELECT LOAD_FILE("/misc/mysql/forest/37114/51-37114/mysql-test/var/tmp/bug37114.txt");
+LOAD_FILE("/misc/mysql/forest/37114/51-37114/mysql-test/var/tmp/bug37114.txt")
+3 \tx
+4 \r
+
+1.2 NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY
+SET sql_mode = '';
+INSERT INTO t1 (id, val1) VALUES (4, '\\r');
+SET sql_mode = 'NO_BACKSLASH_ESCAPES';
+INSERT INTO t1 (id, val1) VALUES (3, '\tx');
+SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS ESCAPED BY '\'
TERMINATED BY ' ' FROM t1 ORDER BY id;
+UPDATE t1 SET id=id-2;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t1 FIELDS ESCAPED BY '\'
TERMINATED BY ' ';
+SELECT id,val1,HEX(val1) FROM t1 ORDER BY id;
+id	val1	HEX(val1)
+1	\tx	5C7478
+2	\r	5C72
+3	\tx	5C7478
+4	\r	5C72
+TRUNCATE t1;
+SELECT LOAD_FILE("/misc/mysql/forest/37114/51-37114/mysql-test/var/tmp/bug37114.txt");
+LOAD_FILE("/misc/mysql/forest/37114/51-37114/mysql-test/var/tmp/bug37114.txt")
+3 \\tx
+4 \\r
+
+2.1 !NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY
+SET sql_mode = '';
+INSERT INTO t1 (id, val1) VALUES (3, '\tx');
+INSERT INTO t1 (id, val1) VALUES (4, '\\r');
+SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS TERMINATED BY ' ' FROM
t1 ORDER BY id;
+UPDATE t1 SET id=id-2;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t1 FIELDS TERMINATED BY '
';
+SELECT id,val1,HEX(val1) FROM t1 ORDER BY id;
+id	val1	HEX(val1)
+1		x	0978
+2	\r	5C72
+3		x	0978
+4	\r	5C72
+SET sql_mode = 'NO_BACKSLASH_ESCAPES';
+TRUNCATE t1;
+SELECT LOAD_FILE("/misc/mysql/forest/37114/51-37114/mysql-test/var/tmp/bug37114.txt");
+LOAD_FILE("/misc/mysql/forest/37114/51-37114/mysql-test/var/tmp/bug37114.txt")
+3 	x
+4 \\r
+
+2.2 !NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY
+SET sql_mode = '';
+INSERT INTO t1 (id, val1) VALUES (3, '\tx');
+INSERT INTO t1 (id, val1) VALUES (4, '\\r');
+SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS ESCAPED BY '' TERMINATED
BY ' ' FROM t1 ORDER BY id;
+UPDATE t1 SET id=id-2;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t1 FIELDS ESCAPED BY ''
TERMINATED BY ' ';
+SELECT id,val1,HEX(val1) FROM t1 ORDER BY id;
+id	val1	HEX(val1)
+1		x	0978
+2	\r	5C72
+3		x	0978
+4	\r	5C72
+SET sql_mode = 'NO_BACKSLASH_ESCAPES';
+TRUNCATE t1;
+SELECT LOAD_FILE("/misc/mysql/forest/37114/51-37114/mysql-test/var/tmp/bug37114.txt");
+LOAD_FILE("/misc/mysql/forest/37114/51-37114/mysql-test/var/tmp/bug37114.txt")
+3 	x
+4 \r
+
+set session sql_mode=@OLD_SQL_MODE;
+DROP TABLE t1;

=== modified file 'mysql-test/t/loaddata.test'
--- a/mysql-test/t/loaddata.test	2008-03-28 20:54:14 +0000
+++ b/mysql-test/t/loaddata.test	2008-06-13 17:12:45 +0000
@@ -347,3 +347,130 @@ DROP TABLE t1;
 remove_file $MYSQLTEST_VARDIR/master-data/test/t@002d1;
 SET character_set_filesystem=default;
 select @@character_set_filesystem;
+
+
+#
+# Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with
+#            LOAD DATA INFILE
+#
+
+# - For each plain "SELECT id,...", the 1st pair ("before" SELECT...OUTFILE,
+#   LOAD...INFILE) and the 2nd pair of lines ("after") in the result should
+# look the same, otherwise we broke the dumpe/restore cycle!
+#
+# - the \r is always { '\\', 'r' } in memory, but on-disk format changes
+#
+# - the \t is { '\t' } or { '\\', 't' } in memory depending on whether \
+#    is magic (that is, NO_BACKSLASH_ESCAPES is not set) at INSERT-time.
+#    on-disk format varies.
+#
+# - while INFILE/OUTFILE behaviour changes according to NO_BACKSLASH_ESCAPES,
+#   we can override these defaults using ESCAPED BY '...'
+#   1:  NO_BACKSLASH_ESCAPES default,  \  on-disk:  \,t,x,\r
+#   2:  NO_BACKSLASH_ESCAPES override, \\ on-disk:  \,\,t,x,\,\,r
+#   3: !NO_BACKSLASH_ESCAPES default,  \\ on-disk:  tab,\,\,r
+#   3: !NO_BACKSLASH_ESCAPES override, \  on-disk:  tab,\,r
+
+--echo 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;
+SET sql_mode = '';
+
+--let $file=$MYSQLTEST_VARDIR/tmp/bug37114.txt
+
+CREATE TABLE t1 (id INT PRIMARY KEY, val1 CHAR(4));
+
+# 1. with NO_BACKSLASH_ESCAPES on
+
+--echo 1.1 NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY
+
+INSERT INTO t1 (id, val1) VALUES (4, '\\r');
+
+SET sql_mode = 'NO_BACKSLASH_ESCAPES';
+
+INSERT INTO t1 (id, val1) VALUES (3, '\tx');
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--eval SELECT * INTO OUTFILE '$file' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id
+UPDATE t1 SET id=id-2;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--eval LOAD DATA INFILE '$file' INTO TABLE t1 FIELDS TERMINATED BY ' '
+
+SELECT id,val1,HEX(val1) FROM t1 ORDER BY id;
+
+TRUNCATE t1;
+eval SELECT LOAD_FILE("$file");
+--remove_file $file
+
+--echo 1.2 NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY
+
+SET sql_mode = '';
+
+INSERT INTO t1 (id, val1) VALUES (4, '\\r');
+
+SET sql_mode = 'NO_BACKSLASH_ESCAPES';
+
+INSERT INTO t1 (id, val1) VALUES (3, '\tx');
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--eval SELECT * INTO OUTFILE '$file' FIELDS ESCAPED BY '\' TERMINATED BY ' ' FROM t1
ORDER BY id
+UPDATE t1 SET id=id-2;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--eval LOAD DATA INFILE '$file' INTO TABLE t1 FIELDS ESCAPED BY '\' TERMINATED BY ' '
+
+SELECT id,val1,HEX(val1) FROM t1 ORDER BY id;
+
+TRUNCATE t1;
+eval SELECT LOAD_FILE("$file");
+--remove_file $file
+
+# 2. with NO_BACKSLASH_ESCAPES off
+
+--echo 2.1 !NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY
+
+SET sql_mode = '';
+
+INSERT INTO t1 (id, val1) VALUES (3, '\tx');
+INSERT INTO t1 (id, val1) VALUES (4, '\\r');
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--eval SELECT * INTO OUTFILE '$file' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id
+UPDATE t1 SET id=id-2;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--eval LOAD DATA INFILE '$file' INTO TABLE t1 FIELDS TERMINATED BY ' '
+
+SELECT id,val1,HEX(val1) FROM t1 ORDER BY id;
+
+SET sql_mode = 'NO_BACKSLASH_ESCAPES';
+
+TRUNCATE t1;
+eval SELECT LOAD_FILE("$file");
+--remove_file $file
+
+--echo 2.2 !NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY
+
+SET sql_mode = '';
+
+INSERT INTO t1 (id, val1) VALUES (3, '\tx');
+INSERT INTO t1 (id, val1) VALUES (4, '\\r');
+
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--eval SELECT * INTO OUTFILE '$file' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1 ORDER
BY id
+UPDATE t1 SET id=id-2;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+--eval LOAD DATA INFILE '$file' INTO TABLE t1 FIELDS ESCAPED BY '' TERMINATED BY ' '
+
+SELECT id,val1,HEX(val1) FROM t1 ORDER BY id;
+
+SET sql_mode = 'NO_BACKSLASH_ESCAPES';
+
+TRUNCATE t1;
+eval SELECT LOAD_FILE("$file");
+--remove_file $file
+
+# clean up
+set session sql_mode=@OLD_SQL_MODE;
+DROP TABLE t1;

=== modified file 'sql/log_event.cc'
--- a/sql/log_event.cc	2008-05-12 17:50:53 +0000
+++ b/sql/log_event.cc	2008-06-13 17:12:45 +0000
@@ -3856,7 +3856,7 @@ int Load_log_event::do_apply_event(NET*
       thd->lex->sql_command= SQLCOM_LOAD;
       thd->lex->duplicates= handle_dup;
 
-      sql_exchange ex((char*)fname, sql_ex.opt_flags & DUMPFILE_FLAG);
+      sql_exchange ex((char*)fname, sql_ex.opt_flags & DUMPFILE_FLAG,
(thd->variables.sql_mode & MODE_NO_BACKSLASH_ESCAPES));
       String field_term(sql_ex.field_term,sql_ex.field_term_len,log_cs);
       String enclosed(sql_ex.enclosed,sql_ex.enclosed_len,log_cs);
       String line_term(sql_ex.line_term,sql_ex.line_term_len,log_cs);

=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc	2008-05-20 07:38:17 +0000
+++ b/sql/sql_class.cc	2008-06-13 17:12:45 +0000
@@ -1466,15 +1466,16 @@ bool select_result::check_simple_select(
 
 static String default_line_term("\n",default_charset_info);
 static String default_escaped("\\",default_charset_info);
+static String default_not_escaped("",default_charset_info);
 static String default_field_term("\t",default_charset_info);
 
-sql_exchange::sql_exchange(char *name,bool flag)
-  :file_name(name), opt_enclosed(0), dumpfile(flag), skip_lines(0)
+sql_exchange::sql_exchange(char *name,bool dumpfile_flag,bool sql_mode_nbe)
+  :file_name(name), opt_enclosed(0), dumpfile(dumpfile_flag), skip_lines(0)
 {
   field_term= &default_field_term;
   enclosed=   line_start= &my_empty_string;
   line_term=  &default_line_term;
-  escaped=    &default_escaped;
+  escaped=    sql_mode_nbe ? &default_not_escaped : &default_escaped;
   cs= NULL;
 }
 

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2008-05-20 07:38:17 +0000
+++ b/sql/sql_class.h	2008-06-13 17:12:45 +0000
@@ -2240,7 +2240,7 @@ public:
   bool dumpfile;
   ulong skip_lines;
   CHARSET_INFO *cs;
-  sql_exchange(char *name,bool dumpfile_flag);
+  sql_exchange(char *name,bool dumpfile_flag,bool sql_mode_nbe);
 };
 
 #include "log_event.h"

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2008-05-20 07:38:17 +0000
+++ b/sql/sql_yacc.yy	2008-06-13 17:12:45 +0000
@@ -8463,8 +8463,9 @@ into_destination:
           OUTFILE TEXT_STRING_filesystem
           {
             LEX *lex= Lex;
+            THD *thd= YYTHD;
             lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
-            if (!(lex->exchange= new sql_exchange($2.str, 0)) ||
+            if (!(lex->exchange= new sql_exchange($2.str, 0,
(thd->variables.sql_mode & MODE_NO_BACKSLASH_ESCAPES))) ||
                 !(lex->result= new select_export(lex->exchange,
lex->nest_level)))
               MYSQL_YYABORT;
           }
@@ -8472,10 +8473,11 @@ into_destination:
         | DUMPFILE TEXT_STRING_filesystem
           {
             LEX *lex=Lex;
+            THD *thd= YYTHD;
             if (!lex->describe)
             {
               lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
-              if (!(lex->exchange= new sql_exchange($2.str,1)))
+              if (!(lex->exchange= new sql_exchange($2.str,1,
(thd->variables.sql_mode & MODE_NO_BACKSLASH_ESCAPES))))
                 MYSQL_YYABORT;
               if (!(lex->result= new select_dump(lex->exchange,
lex->nest_level)))
                 MYSQL_YYABORT;
@@ -9686,12 +9688,13 @@ load_data:
           load_data_lock opt_local INFILE TEXT_STRING_filesystem
           {
             LEX *lex=Lex;
+            THD *thd= YYTHD;
             lex->sql_command= SQLCOM_LOAD;
             lex->lock_option= $1;
             lex->local_file=  $2;
             lex->duplicates= DUP_ERROR;
             lex->ignore= 0;
-            if (!(lex->exchange= new sql_exchange($4.str, 0)))
+            if (!(lex->exchange= new sql_exchange($4.str, 0,
(thd->variables.sql_mode & MODE_NO_BACKSLASH_ESCAPES))))
               MYSQL_YYABORT;
           }
           opt_duplicate INTO