From: Date: September 18 2008 3:32pm Subject: bzr commit into mysql-5.1-bugteam tree (Matthias.Leich:2682) Bug#37114 List-Archive: http://lists.mysql.com/commits/54301 X-Bug: 37114 Message-Id: <20080918133217.70A0AA41E1@five.local.lan> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0527728550785242367==" --===============0527728550785242367== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///work2/5.0/mysql-5.0-bugteam-push/ ------------------------------------------------------------ revno: 2682 revision-id: matthias.leich@stripped parent: matthias.leich@stripped parent: azundris@stripped committer: Matthias Leich branch nick: mysql-5.0-bugteam-push timestamp: Do 2008-09-18 15:32:06 +0200 message: Merge actual tree -> local tree no conflicts modified: mysql-test/r/loaddata.result sp1f-loaddata.result-20030505115238-y53yqooyomv7c43r2heznierboaww576 mysql-test/t/loaddata.test sp1f-loaddata.test-20030505115238-2kz3yrlkdadbbcxdc3cncvt7ofzo2ln5 sql/sql_class.cc sp1f-sql_class.cc-19700101030959-rpotnweaff2pikkozh3butrf7mv3oero sql/sql_class.h sp1f-sql_class.h-19700101030959-jnqnbrjyqsvgncsibnumsmg3lyi7pa5s sql/sql_load.cc sp1f-sql_load.cc-19700101030959-hoqlay5we4yslrw23xqedulkejw6a3o5 ------------------------------------------------------------ revno: 2679.1.3 revision-id: azundris@stripped parent: azundris@stripped committer: Tatiana A. Nurnberg branch nick: 50-37114 timestamp: Do 2008-09-18 11:24:50 +0200 message: Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA INFILE tweaked test to make embedded server happy modified: mysql-test/r/loaddata.result sp1f-loaddata.result-20030505115238-y53yqooyomv7c43r2heznierboaww576 mysql-test/t/loaddata.test sp1f-loaddata.test-20030505115238-2kz3yrlkdadbbcxdc3cncvt7ofzo2ln5 ------------------------------------------------------------ revno: 2679.1.2 revision-id: azundris@stripped parent: azundris@stripped parent: vvaintroub@stripped committer: Tatiana A. Nurnberg branch nick: 50-37114 timestamp: Mi 2008-09-17 17:44:15 +0200 message: auto-merge modified: sql/stacktrace.c sp1f-stacktrace.c-20010513221240-wwmyzt4dneecpsyuor3g7w3zacc6u4mq ------------------------------------------------------------ revno: 2679.1.1 revision-id: azundris@stripped parent: patrick.crews@stripped committer: Tatiana A. Nurnberg branch nick: 50-37114 timestamp: Mi 2008-09-17 08:34:00 +0200 message: 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 sp1f-loaddata.result-20030505115238-y53yqooyomv7c43r2heznierboaww576 mysql-test/t/loaddata.test sp1f-loaddata.test-20030505115238-2kz3yrlkdadbbcxdc3cncvt7ofzo2ln5 sql/sql_class.cc sp1f-sql_class.cc-19700101030959-rpotnweaff2pikkozh3butrf7mv3oero sql/sql_class.h sp1f-sql_class.h-19700101030959-jnqnbrjyqsvgncsibnumsmg3lyi7pa5s sql/sql_load.cc sp1f-sql_load.cc-19700101030959-hoqlay5we4yslrw23xqedulkejw6a3o5 --===============0527728550785242367== MIME-Version: 1.0 Content-Type: text/text/x-diff; charset="us-ascii"; name="patch-2682.diff" Content-Transfer-Encoding: 7bit Content-Disposition: inline === modified file 'mysql-test/r/loaddata.result' --- a/mysql-test/r/loaddata.result 2008-03-28 20:39:47 +0000 +++ b/mysql-test/r/loaddata.result 2008-09-18 09:24:50 +0000 @@ -328,3 +328,123 @@ 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 +SET sql_mode = ''; +SELECT '1 \\aa\n' INTO DUMPFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt'; +CREATE TABLE t1 (id INT, val1 CHAR(3)) ENGINE=MyISAM; +SET sql_mode = 'NO_BACKSLASH_ESCAPES'; +LOAD DATA 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 === modified file 'mysql-test/t/loaddata.test' --- a/mysql-test/t/loaddata.test 2008-03-28 20:39:47 +0000 +++ b/mysql-test/t/loaddata.test 2008-09-18 09:24:50 +0000 @@ -318,4 +318,184 @@ ########################################################################### -# End of 5.0 tests + +# +# 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; + +# 0. test LOAD DATA INFILE first; if that works, all issues in +# SELECT INTO OUTFILE / LOAD DATA INFILE cycles below are +# arguably in the saving. + +--echo test LOAD DATA INFILE + +--let $file=$MYSQLTEST_VARDIR/tmp/bug37114.txt +--let $file2=$MYSQLTEST_VARDIR/tmp/bug37114_out.txt + +SET sql_mode = ''; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT '1 \\\\aa\n' INTO DUMPFILE '$file' + +CREATE TABLE t1 (id INT, val1 CHAR(3)) ENGINE=MyISAM; + +SET sql_mode = 'NO_BACKSLASH_ESCAPES'; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' REPLACE INTO TABLE t1 FIELDS TERMINATED BY ' ' +SELECT * FROM t1; + +# show we can write this with OUTFILE, forcing the parameters for now +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file2' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1 +--diff_files $file $file2 +--remove_file $file2 + +# now show the OUTFILE defaults are correct with NO_BACKSLASH_ESCAPES +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file2' FIELDS TERMINATED BY ' ' FROM t1 +--diff_files $file $file2 +--remove_file $file2 + +INSERT INTO t1 (id, val1) VALUES (1, '\aa'); +SELECT * FROM t1; + +SET sql_mode=''; +INSERT INTO t1 (id, val1) VALUES (1, '\aa'); +SELECT * FROM t1; + +DROP TABLE t1; + +--remove_file $file + + + +--echo test SELECT INTO OUTFILE + +CREATE TABLE t1 (id INT PRIMARY KEY, val1 CHAR(4)); +CREATE TABLE t2 LIKE t1; + +# 1. with NO_BACKSLASH_ESCAPES on + +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'); + +--echo 1.1 NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' 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; + +TRUNCATE t2; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval SELECT LOAD_FILE("$file"); +--remove_file $file + + + +--echo 1.2 NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file' FIELDS ESCAPED BY '\' TERMINATED BY ' ' FROM t1 ORDER BY id + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' 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; + +TRUNCATE t2; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval SELECT LOAD_FILE("$file"); +--remove_file $file + + + +# 2. with NO_BACKSLASH_ESCAPES off + +SET sql_mode = ''; + +--echo 2.1 !NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' 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; + +TRUNCATE t2; + +SET sql_mode = 'NO_BACKSLASH_ESCAPES'; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval SELECT LOAD_FILE("$file"); +--remove_file $file + +SET sql_mode = ''; + + + +--echo 2.2 !NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1 ORDER BY id + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' 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; + +TRUNCATE t2; + +SET sql_mode = 'NO_BACKSLASH_ESCAPES'; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval SELECT LOAD_FILE("$file"); +--remove_file $file + +# clean up +set session sql_mode=@OLD_SQL_MODE; +DROP TABLE t1,t2; + + + +--echo End of 5.0 tests === modified file 'sql/sql_class.cc' --- a/sql/sql_class.cc 2008-07-14 21:41:30 +0000 +++ b/sql/sql_class.cc 2008-09-17 06:34:00 +0000 @@ -974,6 +974,12 @@ cs= NULL; } +bool sql_exchange::escaped_given(void) +{ + return escaped != &default_escaped; +} + + bool select_send::send_fields(List &list, uint flags) { bool res; @@ -1234,8 +1240,11 @@ exchange->line_term=exchange->field_term; // Use this if it exists field_sep_char= (exchange->enclosed->length() ? (int) (uchar) (*exchange->enclosed)[0] : field_term_char); - escape_char= (exchange->escaped->length() ? - (int) (uchar) (*exchange->escaped)[0] : -1); + if (exchange->escaped->length() && (exchange->escaped_given() || + !(thd->variables.sql_mode & MODE_NO_BACKSLASH_ESCAPES))) + escape_char= (int) (uchar) (*exchange->escaped)[0]; + else + escape_char= -1; is_ambiguous_field_sep= test(strchr(ESCAPE_CHARS, field_sep_char)); is_unsafe_field_sep= test(strchr(NUMERIC_CHARS, field_sep_char)); line_sep_char= (exchange->line_term->length() ? === modified file 'sql/sql_class.h' --- a/sql/sql_class.h 2008-07-14 21:41:30 +0000 +++ b/sql/sql_class.h 2008-09-17 06:34:00 +0000 @@ -1896,6 +1896,7 @@ ulong skip_lines; CHARSET_INFO *cs; sql_exchange(char *name,bool dumpfile_flag); + bool escaped_given(void); }; #include "log_event.h" === modified file 'sql/sql_load.cc' --- a/sql/sql_load.cc 2008-03-28 15:59:13 +0000 +++ b/sql/sql_load.cc 2008-09-17 06:34:00 +0000 @@ -325,7 +325,9 @@ bzero((char*) &info,sizeof(info)); info.ignore= ignore; info.handle_duplicates=handle_duplicates; - info.escape_char=escaped->length() ? (*escaped)[0] : INT_MAX; + info.escape_char= (escaped->length() && (ex->escaped_given() || + !(thd->variables.sql_mode & MODE_NO_BACKSLASH_ESCAPES))) + ? (*escaped)[0] : INT_MAX; READ_INFO read_info(file,tot_length, ex->cs ? ex->cs : thd->variables.collation_database, --===============0527728550785242367==--