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