| 1 |
############# mysql-test\t\sql_mode_func.test ###################################### |
| 2 |
# # |
| 3 |
# Variable Name: sql_mode # |
| 4 |
# Scope: GLOBAL & SESSION # |
| 5 |
# Access Type: Dynamic # |
| 6 |
# Data Type: enumeration # |
| 7 |
# Values: ALLOW_INVALID_DATES, ANSI_QUOTES, ERROR_FOR_DIVISION_BY_ZERO, # |
| 8 |
# HIGH_NOT_PRECEDENCE, IGNORE_SPACE, # |
| 9 |
# NO_AUTO_VALUE_ON_ZERO, NO_BACKSLASH_ESCAPES, NO_DIR_IN_CREATE, # |
| 10 |
# NO_ENGINE_SUBSTITUTION, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, # |
| 11 |
# NO_TABLE_OPTIONS, NO_UNSIGNED_SUBTRACTION, NO_ZERO_DATE, # |
| 12 |
# NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY, PIPES_AS_CONCAT, REAL_AS_FLOAT# |
| 13 |
# STRICT_ALL_TABLES, STRICT_TRANS_TABLES, OFF # |
| 14 |
# # |
| 15 |
# # |
| 16 |
# Creation Date: 2008-02-22 # |
| 17 |
# Author: Sharique Abdullah # |
| 18 |
# # |
| 19 |
# Description: Test Cases of Dynamic System Variable "sql_mode" # |
| 20 |
# that checks behavior of this variable in the following ways # |
| 21 |
# * Functionality based on different values # |
| 22 |
# # |
| 23 |
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # |
| 24 |
# server-system-variables.html#option_mysqld_sql_mode # |
| 25 |
# # |
| 26 |
#################################################################################### |
| 27 |
|
| 28 |
--echo ** Setup ** |
| 29 |
--echo |
| 30 |
# |
| 31 |
# Setup |
| 32 |
# |
| 33 |
|
| 34 |
# |
| 35 |
# Save initial value |
| 36 |
# |
| 37 |
|
| 38 |
SET @sql_mode_session = @@SESSION.sql_mode; |
| 39 |
SET @sql_mode_global = @@GLOBAL.sql_mode; |
| 40 |
|
| 41 |
# |
| 42 |
# Create tables |
| 43 |
# |
| 44 |
|
| 45 |
CREATE TEMPORARY TABLE t1(a varchar(20) not null, b varchar(20)); |
| 46 |
CREATE TEMPORARY TABLE t2(a varchar(20) null, b varchar(20)); |
| 47 |
|
| 48 |
--echo '#--------------------FN_DYNVARS_153_01-------------------------#' |
| 49 |
# |
| 50 |
#ANSI mode |
| 51 |
# |
| 52 |
--echo ANSI mode |
| 53 |
|
| 54 |
|
| 55 |
SET SESSION sql_mode = ANSI; |
| 56 |
|
| 57 |
SELECT @@SESSION.sql_mode; |
| 58 |
|
| 59 |
INSERT INTO t1 VALUES('t1a1','t1b1'); |
| 60 |
INSERT INTO t1 VALUES('t1a2','t1b2'); |
| 61 |
INSERT INTO t1 VALUES('t1a3','t1b3'); |
| 62 |
INSERT INTO t1 VALUES('t1a4','t1b4'); |
| 63 |
|
| 64 |
INSERT INTO t2 VALUES('t2a1','t2b1'); |
| 65 |
INSERT INTO t2 VALUES('t2a2','t2b2'); |
| 66 |
INSERT INTO t2 VALUES('t2a3','t2b3'); |
| 67 |
INSERT INTO t2 VALUES('t2a4','t2b4'); |
| 68 |
|
| 69 |
--error ER_INVALID_GROUP_FUNC_USE |
| 70 |
SELECT * FROM t1 WHERE t1.a IN (SELECT MAX(t1.b) FROM t2); |
| 71 |
--echo Expected error for invalid group function |
| 72 |
|
| 73 |
# |
| 74 |
# Reset |
| 75 |
# |
| 76 |
|
| 77 |
SET SESSION sql_mode = @sql_mode_session; |
| 78 |
SET GLOBAL sql_mode = @sql_mode_global; |
| 79 |
|
| 80 |
--echo '#--------------------FN_DYNVARS_153_02-------------------------#' |
| 81 |
# |
| 82 |
#STRICT_TRANS_TABLES mode |
| 83 |
# |
| 84 |
--echo STRICT_TRANS_TABLES mode |
| 85 |
|
| 86 |
DELETE FROM t1; |
| 87 |
DELETE FROM t2; |
| 88 |
|
| 89 |
SET SESSION sql_mode = STRICT_TRANS_TABLES; |
| 90 |
|
| 91 |
SELECT @@SESSION.sql_mode; |
| 92 |
|
| 93 |
INSERT INTO t1 VALUES('t1a1','t1b1'); |
| 94 |
INSERT INTO t1 VALUES('t1a2','t1b2'); |
| 95 |
INSERT INTO t1 VALUES('t1a3','t1b3'); |
| 96 |
INSERT INTO t1 VALUES('t1a4','t1b4'); |
| 97 |
|
| 98 |
INSERT INTO t2 VALUES(NULL,'t2b1'); |
| 99 |
INSERT INTO t2 VALUES('t2a2','t2b2'); |
| 100 |
INSERT INTO t2 VALUES('t2a3','t2b3'); |
| 101 |
INSERT INTO t2 VALUES('t2a4','t2b4'); |
| 102 |
|
| 103 |
--error ER_BAD_NULL_ERROR |
| 104 |
INSERT INTO t1 SELECT * FROM t2; |
| 105 |
--echo Expected error Bad NULL value |
| 106 |
|
| 107 |
SELECT * FROM t1 WHERE a = 't2a2'; |
| 108 |
--echo Should return no rows in columns a,b; |
| 109 |
|
| 110 |
# |
| 111 |
# Reset |
| 112 |
# |
| 113 |
|
| 114 |
SET SESSION sql_mode = @sql_mode_session; |
| 115 |
SET GLOBAL sql_mode = @sql_mode_global; |
| 116 |
|
| 117 |
--echo '#--------------------FN_DYNVARS_153_03-------------------------#' |
| 118 |
# |
| 119 |
#TRADITIONAL mode |
| 120 |
# |
| 121 |
--echo TRADITIONAL mode |
| 122 |
|
| 123 |
SET SESSION sql_mode = TRADITIONAL; |
| 124 |
|
| 125 |
SELECT @@SESSION.sql_mode; |
| 126 |
|
| 127 |
INSERT INTO t1 VALUES('t1a1','t1b1'); |
| 128 |
INSERT INTO t1 VALUES('t1a2','t1b2'); |
| 129 |
INSERT INTO t1 VALUES('t1a3','t1b3'); |
| 130 |
INSERT INTO t1 VALUES('t1a4','t1b4'); |
| 131 |
|
| 132 |
INSERT INTO t2 VALUES(NULL,'t2b1'); |
| 133 |
INSERT INTO t2 VALUES('t2a2','t2b2'); |
| 134 |
INSERT INTO t2 VALUES('t2a3','t2b3'); |
| 135 |
INSERT INTO t2 VALUES('t2a4','t2b4'); |
| 136 |
|
| 137 |
--error ER_BAD_NULL_ERROR |
| 138 |
INSERT INTO t1 SELECT * FROM t2; |
| 139 |
--echo Expected error Bad NULL value |
| 140 |
|
| 141 |
SELECT * FROM t1 WHERE a = 't2a2'; |
| 142 |
--echo Should return no rows in columns a,b; |
| 143 |
|
| 144 |
|
| 145 |
--echo '#-------------------FN_DYNVARS_153_04-------------------------#' |
| 146 |
# |
| 147 |
# Session data integrity check & GLOBAL Value check |
| 148 |
# |
| 149 |
|
| 150 |
SET GLOBAL sql_mode = ''; |
| 151 |
--echo ** Connecting con_int1 using root ** |
| 152 |
connect (con_int1,localhost,root,,); |
| 153 |
|
| 154 |
--echo ** Connection con_int1 ** |
| 155 |
connection con_int1; |
| 156 |
SELECT @@SESSION.sql_mode; |
| 157 |
--echo '' Expected |
| 158 |
|
| 159 |
SET SESSION sql_mode = 'TRADITIONAL'; |
| 160 |
|
| 161 |
--echo ** Connecting con_int2 using root ** |
| 162 |
connect (con_int2,localhost,root,,); |
| 163 |
|
| 164 |
--echo ** Connection con_int2 ** |
| 165 |
connection con_int2; |
| 166 |
SELECT @@SESSION.sql_mode; |
| 167 |
--echo '' Expected |
| 168 |
|
| 169 |
SET SESSION sql_mode = 'ANSI'; |
| 170 |
|
| 171 |
--echo ** Connection con_int2 ** |
| 172 |
connection con_int2; |
| 173 |
SELECT @@SESSION.sql_mode; |
| 174 |
--echo 'REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ANSI' Expected |
| 175 |
|
| 176 |
--echo ** Connection con_int1 ** |
| 177 |
connection con_int1; |
| 178 |
SELECT @@SESSION.sql_mode; |
| 179 |
--echo 'STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL' Expected |
| 180 |
|
| 181 |
|
| 182 |
SELECT @@GLOBAL.sql_mode; |
| 183 |
--echo '' Expected |
| 184 |
|
| 185 |
--echo ** Connection default ** |
| 186 |
connection default; |
| 187 |
|
| 188 |
--echo Disconnecting Connections con_int1, con_int2 |
| 189 |
disconnect con_int1; |
| 190 |
disconnect con_int2; |
| 191 |
|
| 192 |
# |
| 193 |
# Cleanup |
| 194 |
# |
| 195 |
|
| 196 |
SET SESSION sql_mode = @sql_mode_session; |
| 197 |
SET GLOBAL sql_mode = @sql_mode_global; |
| 198 |
|
| 199 |
DROP TABLE t1; |
| 200 |
DROP TABLE t2; |