From: Date: June 13 2008 7:12pm Subject: bzr commit into mysql-5.1 branch (azundris:2661) Bug#37114 List-Archive: http://lists.mysql.com/commits/47852 X-Bug: 37114 Message-Id: <20080613171251.EB55342E40F8@noir.koehntopp.de> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit #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