diff options
Diffstat (limited to 'mysql-test/suite/pbxt/t/sql_mode.test')
| -rw-r--r-- | mysql-test/suite/pbxt/t/sql_mode.test | 273 |
1 files changed, 273 insertions, 0 deletions
diff --git a/mysql-test/suite/pbxt/t/sql_mode.test b/mysql-test/suite/pbxt/t/sql_mode.test new file mode 100644 index 00000000000..9f5183ce3cb --- /dev/null +++ b/mysql-test/suite/pbxt/t/sql_mode.test @@ -0,0 +1,273 @@ +--disable_warnings +drop table if exists t1,t2,v1,v2; +drop view if exists t1,t2,v1,v2; +--enable_warnings + +CREATE TABLE `t1` ( + a int not null auto_increment, + `pseudo` varchar(35) character set latin2 NOT NULL default '', + `email` varchar(60) character set latin2 NOT NULL default '', + PRIMARY KEY (a), + UNIQUE KEY `email` USING BTREE (`email`) +) ENGINE=HEAP CHARSET=latin1 ROW_FORMAT DYNAMIC; +set @@sql_mode=""; +show variables like 'sql_mode'; +show create table t1; +set @@sql_mode="ansi_quotes"; +show variables like 'sql_mode'; +show create table t1; +set @@sql_mode="no_table_options"; +show variables like 'sql_mode'; +show create table t1; +set @@sql_mode="no_key_options"; +show variables like 'sql_mode'; +show create table t1; +set @@sql_mode="no_field_options,mysql323,mysql40"; +show variables like 'sql_mode'; +show create table t1; +set sql_mode="postgresql,oracle,mssql,db2,maxdb"; +select @@sql_mode; +show create table t1; +drop table t1; + +# +# Check that a binary collation adds 'binary' +# suffix into a char() column definition in +# mysql40 and mysql2323 modes. This allows +# not to lose the column's case sensitivity +# when loading the dump in pre-4.1 servers. +# +# Thus, in 4.0 and 3.23 modes we dump: +# +# 'char(10) collate xxx_bin' as 'char(10) binary' +# 'binary(10)' as 'binary(10)' +# +# In mysql-4.1 these types are different, and they will +# be recreated differently. +# +# In mysqld-4.0 the the above two types were the same, +# so it will create a 'char(10) binary' column for both definitions. +# +CREATE TABLE t1 ( + a char(10), + b char(10) collate latin1_bin, + c binary(10) +) character set latin1; +set @@sql_mode=""; +show create table t1; +set @@sql_mode="mysql323"; +show create table t1; +set @@sql_mode="mysql40"; +show create table t1; +drop table t1; + +# +# BUG#5318 - failure: 'IGNORE_SPACE' affects numeric values after DEFAULT +# +# Force the usage of the default +set session sql_mode = ''; +# statement for comparison, value starts with '.' +create table t1 ( min_num dec(6,6) default .000001); +show create table t1; +drop table t1 ; +# +set session sql_mode = 'IGNORE_SPACE'; +# statement for comparison, value starts with '0' +create table t1 ( min_num dec(6,6) default 0.000001); +show create table t1; +drop table t1 ; +# This statement fails, value starts with '.' +create table t1 ( min_num dec(6,6) default .000001); +show create table t1; +drop table t1 ; + +# +# Bug #10732: Set SQL_MODE to NULL gives garbled error message +# +--error 1231 +set @@SQL_MODE=NULL; + +# +# Bug #797: in sql_mode=ANSI, show create table ignores auto_increment +# +set session sql_mode=ansi; +create table t1 +(f1 integer auto_increment primary key, + f2 timestamp default current_timestamp on update current_timestamp); +show create table t1; +set session sql_mode=no_field_options; +show create table t1; +drop table t1; + +# End of 4.1 tests + +# +# 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; + +# Bug #6368: Make sure backslashes mixed with doubled quotes are handled +# correctly in NO_BACKSLASH_ESCAPES mode +SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; +SELECT 'a\\b', 'a\\\"b', 'a''\\b', 'a''\\\"b'; +SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b"; + +SET @@SQL_MODE=''; +SELECT 'a\\b', 'a\\\"b', 'a''\\b', 'a''\\\"b'; +SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b"; + +# +# Bug#6877: MySQL should give an error if the requested table type +# is not available +# + +#set session sql_mode = 'NO_ENGINE_SUBSTITUTION'; +#--error 1289 +#create table t1 (a int) engine=isam; +#--error 1146 +#show create table t1; +#drop table if exists t1; +# +## for comparison, lets see the warnings... +#set session sql_mode = ''; +#create table t1 (a int) engine=isam; +#show create table t1; +#drop table t1; + +# +# Bug #6903: ANSI_QUOTES does not come into play with SHOW CREATE FUNCTION +# or PROCEDURE because it displays the SQL_MODE used to create the routine. +# +SET @@SQL_MODE=''; +create function `foo` () returns int return 5; +show create function `foo`; +SET @@SQL_MODE='ANSI_QUOTES'; +show create function `foo`; +drop function `foo`; + +create function `foo` () returns int return 5; +show create function `foo`; +SET @@SQL_MODE=''; +show create function `foo`; +drop function `foo`; + +# +# Bug #6903: ANSI_QUOTES should have effect for SHOW CREATE VIEW (Bug #6903) +# +SET @@SQL_MODE=''; +create table t1 (a int); +create table t2 (a int); +create view v1 as select a from t1; +show create view v1; +SET @@SQL_MODE='ANSI_QUOTES'; +show create view v1; +# Test a view with a subselect, which will get shown incorrectly without +# thd->lex->view_prepare_mode set properly. +create view v2 as select a from t2 where a in (select a from v1); +drop view v2, v1; +drop table t1, t2; + +select @@sql_mode; +set sql_mode=2097152; +select @@sql_mode; +# BUG#14675 +set sql_mode=4194304; +select @@sql_mode; +set sql_mode=16384+(65536*4); +select @@sql_mode; +set sql_mode=2147483648; # that mode does not exist +select @@sql_mode; + +SET @@SQL_MODE=@OLD_SQL_MODE; + +--disable_query_log +drop database pbxt; +--enable_query_log |