1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
|
#
# Fixed point types
#
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
eval CREATE TABLE t1 (
d DECIMAL $extra_col_opts,
d0 DECIMAL(0) $extra_col_opts,
d1_1 DECIMAL(1,1) $extra_col_opts,
d10_2 DECIMAL(10,2) $extra_col_opts,
d60_10 DECIMAL(60,10) $extra_col_opts,
n NUMERIC $extra_col_opts,
n0_0 NUMERIC(0,0) $extra_col_opts,
n1 NUMERIC(1) $extra_col_opts,
n20_4 NUMERIC(20,4) $extra_col_opts,
n65_4 NUMERIC(65,4) $extra_col_opts,
pk NUMERIC $extra_col_opts PRIMARY KEY
) ENGINE=rocksdb;
SHOW COLUMNS IN t1;
# Always valid values
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (100,123456,0.3,40000.25,123456789123456789.10001,1024,7000.0,8.0,999999.9,9223372036854775807,1);
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (0,0,0,0,0,0,0,0,0,0,2);
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (9999999999.0,9999999999.0,0.9,99999999.99,99999999999999999999999999999999999999999999999999.9999999999,9999999999.0,9999999999.0,9.0,9999999999999999.9999,9999999999999999999999999999999999999999999999999999999999999.9999,3);
--sorted_result
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
# Values which can be valid or not,
# depending on whether columns are SIGNED or UNSIGNED
# (if not valid should produce warnings)
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (-100,-123456,-0.3,-40000.25,-123456789123456789.10001,-1024,-7000.0,-8.0,-999999.9,-9223372036854775807,4);
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (-9999999999.0,-9999999999.0,-0.9,-99999999.99,-99999999999999999999999999999999999999999999999999.9999999999,-9999999999.0,-9999999999.0,-9.0,-9999999999999999.9999,-9999999999999999999999999999999999999999999999999999999999999.9999,5);
--sorted_result
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
--sorted_result
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1 WHERE n20_4 = 9999999999999999.9999 OR d < 100;
# Invalid values
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (
9999999999999999999999999999999999999999999999999999999999999.9999,
9999999999999999999999999999999999999999999999999999999999999.9999,
9999999999999999999999999999999999999999999999999999999999999.9999,
9999999999999999999999999999999999999999999999999999999999999.9999,
9999999999999999999999999999999999999999999999999999999999999.9999,
9999999999999999999999999999999999999999999999999999999999999.9999,
9999999999999999999999999999999999999999999999999999999999999.9999,
9999999999999999999999999999999999999999999999999999999999999.9999,
9999999999999999999999999999999999999999999999999999999999999.9999,
9999999999999999999999999999999999999999999999999999999999999.9999,
6
);
--sorted_result
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (10000000000.0,10000000000.0,1.1,100000000.99,100000000000000000000000000000000000000000000000000.0,10000000000.0,10000000000.0,10.0,10000000000000000.9999,10000000000000000000000000000000000000000000000000000000000000.9999,7);
--sorted_result
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (9999999999.1,9999999999.1,1.9,99999999.001,99999999999999999999999999999999999999999999999999.99999999991,9999999999.1,9999999999.1,9.1,9999999999999999.00001,9999999999999999999999999999999999999999999999999999999999999.11111,8);
--sorted_result
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
--error ER_TOO_BIG_PRECISION
eval ALTER TABLE t1 ADD COLUMN n66 NUMERIC(66) $extra_col_opts;
--error ER_TOO_BIG_PRECISION
eval ALTER TABLE t1 ADD COLUMN n66_6 DECIMAL(66,6) $extra_col_opts;
--error ER_TOO_BIG_SCALE
eval ALTER TABLE t1 ADD COLUMN n66_66 DECIMAL(66,66) $extra_col_opts;
DROP TABLE t1;
|
