diff options
| -rw-r--r-- | mysql-test/r/sql_mode.result | 249 | ||||
| -rw-r--r-- | mysql-test/t/sql_mode.test | 95 | ||||
| -rw-r--r-- | sql/mysql_priv.h | 1 | ||||
| -rw-r--r-- | sql/mysqld.cc | 2 | ||||
| -rw-r--r-- | sql/sql_lex.cc | 3 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 6 |
6 files changed, 353 insertions, 3 deletions
diff --git a/mysql-test/r/sql_mode.result b/mysql-test/r/sql_mode.result index 77fe5d06bb0..c4731b93d2b 100644 --- a/mysql-test/r/sql_mode.result +++ b/mysql-test/r/sql_mode.result @@ -85,3 +85,252 @@ t1 CREATE TABLE "t1" ( UNIQUE KEY "email" ("email") ) drop table t1; +SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=''; +show local variables like 'SQL_MODE'; +Variable_name Value +sql_mode +CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p)); +INSERT t1 (a) VALUES +('\\'), +('\n'), +('\b'), +('\r'), +('\t'), +('\x'), +('\a'), +('\aa'), +('\\a'), +('\\aa'), +('_'), +('\_'), +('\\_'), +('\\\_'), +('\\\\_'), +('%'), +('\%'), +('\\%'), +('\\\%'), +('\\\\%') +; +SELECT p, hex(a) FROM t1; +p hex(a) +1 5C +2 0A +3 08 +4 0D +5 09 +6 78 +7 61 +8 6161 +9 5C61 +10 5C6161 +11 5F +12 5C5F +13 5C5F +14 5C5C5F +15 5C5C5F +16 25 +17 5C25 +18 5C25 +19 5C5C25 +20 5C5C25 +delete from t1 where a in ('\n','\r','\t', '\b'); +select +masks.p, +masks.a as mask, +examples.a as example +from +t1 as masks +left join t1 as examples on examples.a LIKE masks.a +order by masks.p, example; +p mask example +1 \ \ +6 x x +7 a a +8 aa aa +9 \a a +10 \aa aa +11 _ % +11 _ a +11 _ x +11 _ \ +11 _ _ +12 \_ _ +13 \_ _ +14 \\_ \% +14 \\_ \% +14 \\_ \a +14 \\_ \_ +14 \\_ \_ +15 \\_ \% +15 \\_ \% +15 \\_ \a +15 \\_ \_ +15 \\_ \_ +16 % % +16 % a +16 % aa +16 % x +16 % \ +16 % \% +16 % \% +16 % \a +16 % \aa +16 % \\% +16 % \\% +16 % \\_ +16 % \\_ +16 % \_ +16 % \_ +16 % _ +17 \% % +18 \% % +19 \\% \ +19 \\% \% +19 \\% \% +19 \\% \a +19 \\% \aa +19 \\% \\% +19 \\% \\% +19 \\% \\_ +19 \\% \\_ +19 \\% \_ +19 \\% \_ +20 \\% \ +20 \\% \% +20 \\% \% +20 \\% \a +20 \\% \aa +20 \\% \\% +20 \\% \\% +20 \\% \\_ +20 \\% \\_ +20 \\% \_ +20 \\% \_ +DROP TABLE t1; +SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; +show local variables like 'SQL_MODE'; +Variable_name Value +sql_mode NO_BACKSLASH_ESCAPES +CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p)); +INSERT t1 (a) VALUES +('\\'), +('\n'), +('\b'), +('\r'), +('\t'), +('\x'), +('\a'), +('\aa'), +('\\a'), +('\\aa'), +('_'), +('\_'), +('\\_'), +('\\\_'), +('\\\\_'), +('%'), +('\%'), +('\\%'), +('\\\%'), +('\\\\%') +; +SELECT p, hex(a) FROM t1; +p hex(a) +1 5C5C +2 5C6E +3 5C62 +4 5C72 +5 5C74 +6 5C78 +7 5C61 +8 5C6161 +9 5C5C61 +10 5C5C6161 +11 5F +12 5C5F +13 5C5C5F +14 5C5C5C5F +15 5C5C5C5C5F +16 25 +17 5C25 +18 5C5C25 +19 5C5C5C25 +20 5C5C5C5C25 +delete from t1 where a in ('\n','\r','\t', '\b'); +select +masks.p, +masks.a as mask, +examples.a as example +from +t1 as masks +left join t1 as examples on examples.a LIKE masks.a +order by masks.p, example; +p mask example +1 \\ \\ +6 \x \x +7 \a \a +8 \aa \aa +9 \\a \\a +10 \\aa \\aa +11 _ % +11 _ _ +12 \_ \% +12 \_ \a +12 \_ \x +12 \_ \\ +12 \_ \_ +13 \\_ \\% +13 \\_ \\a +13 \\_ \\_ +14 \\\_ \\\% +14 \\\_ \\\_ +15 \\\\_ \\\\% +15 \\\\_ \\\\_ +16 % % +16 % \% +16 % \a +16 % \aa +16 % \x +16 % \\ +16 % \\% +16 % \\a +16 % \\aa +16 % \\\% +16 % \\\\% +16 % \\\\_ +16 % \\\_ +16 % \\_ +16 % \_ +16 % _ +17 \% \% +17 \% \a +17 \% \aa +17 \% \x +17 \% \\ +17 \% \\% +17 \% \\a +17 \% \\aa +17 \% \\\% +17 \% \\\\% +17 \% \\\\_ +17 \% \\\_ +17 \% \\_ +17 \% \_ +18 \\% \\ +18 \\% \\% +18 \\% \\a +18 \\% \\aa +18 \\% \\\% +18 \\% \\\\% +18 \\% \\\\_ +18 \\% \\\_ +18 \\% \\_ +19 \\\% \\\% +19 \\\% \\\\% +19 \\\% \\\\_ +19 \\\% \\\_ +20 \\\\% \\\\% +20 \\\\% \\\\_ +DROP TABLE t1; +SET @@SQL_MODE=@OLD_SQL_MODE; diff --git a/mysql-test/t/sql_mode.test b/mysql-test/t/sql_mode.test index 95e83b4b9e2..c7b6510db9d 100644 --- a/mysql-test/t/sql_mode.test +++ b/mysql-test/t/sql_mode.test @@ -28,3 +28,98 @@ set sql_mode="postgresql,oracle,mssql,db2,maxdb"; select @@sql_mode; show create table t1; drop table t1; + +# +# test for +# WL 1941 "NO_C_ESCAPES sql_mode" +# +# an sql_mode to disable \n, \r, \b, etc escapes in string literals. actually, to +# disable special meaning of backslash completely. It's not in the SQL standard +# and it causes some R/3 tests to fail. +# + +SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=''; +show local variables like 'SQL_MODE'; + +CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p)); +INSERT t1 (a) VALUES +('\\'), +('\n'), +('\b'), +('\r'), +('\t'), +('\x'), +('\a'), +('\aa'), +('\\a'), +('\\aa'), +('_'), +('\_'), +('\\_'), +('\\\_'), +('\\\\_'), +('%'), +('\%'), +('\\%'), +('\\\%'), +('\\\\%') +; + +SELECT p, hex(a) FROM t1; + +delete from t1 where a in ('\n','\r','\t', '\b'); + +select + masks.p, + masks.a as mask, + examples.a as example +from + t1 as masks + left join t1 as examples on examples.a LIKE masks.a +order by masks.p, example; + +DROP TABLE t1; + +SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; +show local variables like 'SQL_MODE'; + +CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p)); +INSERT t1 (a) VALUES +('\\'), +('\n'), +('\b'), +('\r'), +('\t'), +('\x'), +('\a'), +('\aa'), +('\\a'), +('\\aa'), +('_'), +('\_'), +('\\_'), +('\\\_'), +('\\\\_'), +('%'), +('\%'), +('\\%'), +('\\\%'), +('\\\\%') +; + +SELECT p, hex(a) FROM t1; + +delete from t1 where a in ('\n','\r','\t', '\b'); + +select + masks.p, + masks.a as mask, + examples.a as example +from + t1 as masks + left join t1 as examples on examples.a LIKE masks.a +order by masks.p, example; + +DROP TABLE t1; + +SET @@SQL_MODE=@OLD_SQL_MODE; diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index c8b57d9fb55..4953286e4f7 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -272,6 +272,7 @@ extern CHARSET_INFO *national_charset_info, *table_alias_charset; #define MODE_MYSQL40 (MODE_MYSQL323*2) #define MODE_ANSI (MODE_MYSQL40*2) #define MODE_NO_AUTO_VALUE_ON_ZERO (MODE_ANSI*2) +#define MODE_NO_BACKSLASH_ESCAPES (MODE_NO_AUTO_VALUE_ON_ZERO*2) #define RAID_BLOCK_SIZE 1024 diff --git a/sql/mysqld.cc b/sql/mysqld.cc index e4302a4a9ad..f1406ac2e5e 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -223,7 +223,7 @@ const char *sql_mode_names[] = "NO_DIR_IN_CREATE", "POSTGRESQL", "ORACLE", "MSSQL", "DB2", "MAXDB", "NO_KEY_OPTIONS", "NO_TABLE_OPTIONS", "NO_FIELD_OPTIONS", "MYSQL323", "MYSQL40", "ANSI", - "NO_AUTO_VALUE_ON_ZERO", NullS + "NO_AUTO_VALUE_ON_ZERO", "NO_BACKSLASH_ESCAPES", NullS }; TYPELIB sql_mode_typelib= { array_elements(sql_mode_names)-1,"", sql_mode_names }; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 2c970e4c7ca..2a4f29083e8 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -250,7 +250,8 @@ static char *get_text(LEX *lex) continue; } #endif - if (c == '\\') + if (c == '\\' && + !(lex->thd->variables.sql_mode & MODE_NO_BACKSLASH_ESCAPES)) { // Escaped character found_escape=1; if (lex->ptr == lex->end_of_query) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index bad4a2c7190..ad199bd8b20 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -4725,7 +4725,11 @@ having_clause: opt_escape: ESCAPE_SYM TEXT_STRING_literal { $$= $2.str; } - | /* empty */ { $$= (char*) "\\"; }; + | /* empty */ + { + $$= (char*) ((YYTHD->variables.sql_mode & MODE_NO_BACKSLASH_ESCAPES) + ? "" : "\\"); + }; /* |