1# 2# Float types 3# 4 5# $extra_col_opts is set by a calling test 6# $extra_col_opts_ddl_err is set by a calling test 7# $extra_col_opts_dml_err is set by a calling test 8 9if ($extra_col_opts_ddl_err == 0) 10{ 11 eval CREATE TABLE t1 ( 12 f FLOAT $extra_col_opts, 13 f0 FLOAT(0) $extra_col_opts, 14 r1_1 REAL(1,1) $extra_col_opts, 15 f23_0 FLOAT(23) $extra_col_opts, 16 f20_3 FLOAT(20,3) $extra_col_opts, 17 d DOUBLE $extra_col_opts, 18 d1_0 DOUBLE(1,0) $extra_col_opts, 19 d10_10 DOUBLE PRECISION (10,10) $extra_col_opts, 20 d53 DOUBLE(53,0) $extra_col_opts, 21 d53_10 DOUBLE(53,10) $extra_col_opts, 22 pk DOUBLE $extra_col_opts PRIMARY KEY 23 ) ENGINE=rocksdb; 24} 25if ($extra_col_opts_ddl_err == 1) 26{ 27 # 5.7 uses strict mode by default, so this will now return an error 28 --error ER_PRIMARY_CANT_HAVE_NULL 29 eval CREATE TABLE t1 ( 30 f FLOAT $extra_col_opts, 31 f0 FLOAT(0) $extra_col_opts, 32 r1_1 REAL(1,1) $extra_col_opts, 33 f23_0 FLOAT(23) $extra_col_opts, 34 f20_3 FLOAT(20,3) $extra_col_opts, 35 d DOUBLE $extra_col_opts, 36 d1_0 DOUBLE(1,0) $extra_col_opts, 37 d10_10 DOUBLE PRECISION (10,10) $extra_col_opts, 38 d53 DOUBLE(53,0) $extra_col_opts, 39 d53_10 DOUBLE(53,10) $extra_col_opts, 40 pk DOUBLE $extra_col_opts PRIMARY KEY 41 ) ENGINE=rocksdb; 42 43 # 5.7 _requires_ a not NULL PK and can't mimick 5.6 behavior, so we are 44 # adding in a fake key to satisfy and allow the test to resume 45 eval CREATE TABLE t1 ( 46 f FLOAT $extra_col_opts, 47 f0 FLOAT(0) $extra_col_opts, 48 r1_1 REAL(1,1) $extra_col_opts, 49 f23_0 FLOAT(23) $extra_col_opts, 50 f20_3 FLOAT(20,3) $extra_col_opts, 51 d DOUBLE $extra_col_opts, 52 d1_0 DOUBLE(1,0) $extra_col_opts, 53 d10_10 DOUBLE PRECISION (10,10) $extra_col_opts, 54 d53 DOUBLE(53,0) $extra_col_opts, 55 d53_10 DOUBLE(53,10) $extra_col_opts, 56 pk DOUBLE $extra_col_opts, 57 fake_key INT NOT NULL AUTO_INCREMENT, 58 PRIMARY KEY (fake_key) 59 ) ENGINE=rocksdb; 60} 61 62SHOW COLUMNS IN t1; 63 64# Always valid values 65 66INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES (12345.12345,12345.12345,0.9,123456789.123,56789.987,11111111.111,8.0,0.0123456789,1234566789123456789,99999999999999999.99999999,1); 67 68--sorted_result 69--query_vertical SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1 70 71INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES (0,0,0,0,0,0,0,0,0,0,2); 72 73# 5.7 uses strict mode by default, so this will now return an error 74--error ER_WARN_DATA_OUT_OF_RANGE 75INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES ( 76 99999999999999999999999999999999999999, 77 99999999999999999999999999999999999999.9999999999999999, 78 0.9, 79 99999999999999999999999999999999999999.9, 80 99999999999999999.999, 81 999999999999999999999999999999999999999999999999999999999999999999999999999999999, 82 9, 83 0.9999999999, 84 1999999999999999999999999999999999999999999999999999999, 85 19999999999999999999999999999999999999999999.9999999999, 86 3 87); 88 89# now test with the 5.6 behavior just to be sure 90set @orig_sql_mode=@@session.sql_mode; 91set session sql_mode=""; 92INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES ( 93 99999999999999999999999999999999999999, 94 99999999999999999999999999999999999999.9999999999999999, 95 0.9, 96 99999999999999999999999999999999999999.9, 97 99999999999999999.999, 98 999999999999999999999999999999999999999999999999999999999999999999999999999999999, 99 9, 100 0.9999999999, 101 1999999999999999999999999999999999999999999999999999999, 102 19999999999999999999999999999999999999999999.9999999999, 103 3 104); 105set session sql_mode=@orig_sql_mode; 106 107--sorted_result 108--query_vertical SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1 109 110# Values which can be valid or not, 111# depending on whether columns are SIGNED or UNSIGNED 112# (if not valid should produce warnings) 113 114if ($extra_col_opts_dml_err == 0) 115{ 116 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES (-999999999999999999999999,-99999999999.999999999999,-0.9,-999.99999999999999999999,-99999999999999999.999,-999999999999999999999999999999999999999999999999999999999999-0.999,-9,-.9999999999,-999999999999999999999999999999.99999999999999999999999,-9999999999999999999999999999999999999999999.9999999999,4); 117} 118if ($extra_col_opts_dml_err == 1) 119{ 120 # 5.7 uses strict mode by default, so this will now return an error 121 --error ER_WARN_DATA_OUT_OF_RANGE 122 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES (-999999999999999999999999,-99999999999.999999999999,-0.9,-999.99999999999999999999,-99999999999999999.999,-999999999999999999999999999999999999999999999999999999999999-0.999,-9,-.9999999999,-999999999999999999999999999999.99999999999999999999999,-9999999999999999999999999999999999999999999.9999999999,4); 123 124 # now test with the 5.6 behavior just to be sure 125 set @orig_sql_mode=@@session.sql_mode; 126 set session sql_mode=""; 127 INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES (-999999999999999999999999,-99999999999.999999999999,-0.9,-999.99999999999999999999,-99999999999999999.999,-999999999999999999999999999999999999999999999999999999999999-0.999,-9,-.9999999999,-999999999999999999999999999999.99999999999999999999999,-9999999999999999999999999999999999999999999.9999999999,4); 128 set session sql_mode=@orig_sql_mode; 129} 130 131--sorted_result 132--query_vertical SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1 133 134--sorted_result 135query_vertical 136SELECT 137 CONCAT('', MAX(f)), 138 CONCAT('', MAX(f0)), 139 CONCAT('', MAX(r1_1)), 140 CONCAT('', MAX(f23_0)), 141 CONCAT('', MAX(f20_3)), 142 CONCAT('', MAX(d)), 143 CONCAT('', MAX(d1_0)), 144 CONCAT('', MAX(d10_10)), 145 CONCAT('', MAX(d53)), 146 CONCAT('', MAX(d53_10)) FROM t1; 147 148# Invalid values 149 150# 5.7 uses strict mode by default, so this will now return an error 151--error ER_WARN_DATA_OUT_OF_RANGE 152INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES ( 153 9999999999999999999999999999999999999999999999999999999999999.9999, 154 9999999999999999999999999999999999999999999999999999999999999.9999, 155 9999999999999999999999999999999999999999999999999999999999999.9999, 156 9999999999999999999999999999999999999999999999999999999999999.9999, 157 9999999999999999999999999999999999999999999999999999999999999.9999, 158 9999999999999999999999999999999999999999999999999999999999999.9999, 159 9999999999999999999999999999999999999999999999999999999999999.9999, 160 9999999999999999999999999999999999999999999999999999999999999.9999, 161 9999999999999999999999999999999999999999999999999999999999999.9999, 162 9999999999999999999999999999999999999999999999999999999999999.9999, 163 5 164); 165 166# now test with the 5.6 behavior just to be sure 167set @orig_sql_mode=@@session.sql_mode; 168set session sql_mode=""; 169INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES ( 170 9999999999999999999999999999999999999999999999999999999999999.9999, 171 9999999999999999999999999999999999999999999999999999999999999.9999, 172 9999999999999999999999999999999999999999999999999999999999999.9999, 173 9999999999999999999999999999999999999999999999999999999999999.9999, 174 9999999999999999999999999999999999999999999999999999999999999.9999, 175 9999999999999999999999999999999999999999999999999999999999999.9999, 176 9999999999999999999999999999999999999999999999999999999999999.9999, 177 9999999999999999999999999999999999999999999999999999999999999.9999, 178 9999999999999999999999999999999999999999999999999999999999999.9999, 179 9999999999999999999999999999999999999999999999999999999999999.9999, 180 5 181); 182set session sql_mode=@orig_sql_mode; 183 184--sorted_result 185--query_vertical SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1 186 187# 5.7 uses strict mode by default, so this will now return an error 188--error ER_WARN_DATA_OUT_OF_RANGE 189INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES ( 190 999999999999999999999999999999999999999, 191 999999999999999999999999999999999999999.9999999999999999, 192 1.9, 193 999999999999999999999999999999999999999.9, 194 999999999999999999.999, 195 9999999999999999999999999999999999999999999999999999999999999999999999999999999999, 196 99, 197 1.9999999999, 198 1999999999999999999999999999999999999999999999999999999, 199 19999999999999999999999999999999999999999999.9999999999, 200 6 201); 202 203# now test with the 5.6 behavior just to be sure 204set @orig_sql_mode=@@session.sql_mode; 205set session sql_mode=""; 206INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10,pk) VALUES ( 207 999999999999999999999999999999999999999, 208 999999999999999999999999999999999999999.9999999999999999, 209 1.9, 210 999999999999999999999999999999999999999.9, 211 999999999999999999.999, 212 9999999999999999999999999999999999999999999999999999999999999999999999999999999999, 213 99, 214 1.9999999999, 215 1999999999999999999999999999999999999999999999999999999, 216 19999999999999999999999999999999999999999999.9999999999, 217 6 218); 219set session sql_mode=@orig_sql_mode; 220 221--sorted_result 222--query_vertical SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1 223 224--error ER_TOO_BIG_DISPLAYWIDTH 225eval ALTER TABLE t1 ADD COLUMN d0_0 DOUBLE(0,0) $extra_col_opts; 226 227--error ER_TOO_BIG_PRECISION 228eval ALTER TABLE t1 ADD COLUMN n66_6 DECIMAL(256,1) $extra_col_opts; 229 230--error ER_TOO_BIG_SCALE 231eval ALTER TABLE t1 ADD COLUMN n66_66 DECIMAL(40,35) $extra_col_opts; 232 233DROP TABLE t1; 234