1
# bug in decimal() with negative numbers by kaido@tradenet.ee
4
DROP TABLE IF EXISTS t1, t2;
8
# NOTE TESTS Commented out in this section are due to
9
# Drizzle Bug 308841 queries with German umlauts in insert statements fail
11
id int NOT NULL auto_increment,
12
datatype_id int DEFAULT '0' NOT NULL,
13
minvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL,
14
maxvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL,
15
valuename varchar(20),
19
UNIQUE datatype_id (datatype_id, minvalue, maxvalue)
22
INSERT INTO t1 VALUES ( '1', '4', '0.0000000000', '0.0000000000', 'Ei saja', '0', '16776960');
23
INSERT INTO t1 VALUES ( '2', '4', '1.0000000000', '1.0000000000', 'Sajab', '16777215', '255');
24
INSERT INTO t1 VALUES ( '3', '1', '2.0000000000', '49.0000000000', '', '0', '16777215');
25
INSERT INTO t1 VALUES ( '60', '11', '0.0000000000', '0.0000000000', 'Rikkis', '16777215', '16711680');
26
INSERT INTO t1 VALUES ( '4', '12', '1.0000000000', '1.0000000000', 'nork sadu', '65280', '14474460');
27
INSERT INTO t1 VALUES ( '5', '12', '2.0000000000', '2.0000000000', 'keskmine sadu', '255', '14474460');
28
INSERT INTO t1 VALUES ( '6', '12', '3.0000000000', '3.0000000000', 'tugev sadu', '127', '14474460');
29
INSERT INTO t1 VALUES ( '43', '39', '6.0000000000', '6.0000000000', 'lobjakas', '13107327', '16763080');
30
INSERT INTO t1 VALUES ( '40', '39', '2.0000000000', '2.0000000000', 'vihm', '8355839', '16777215');
31
INSERT INTO t1 VALUES ( '53', '1', '-35.0000000000', '-5.0000000000', '', '0', '16777215');
32
#INSERT INTO t1 VALUES ( '41', '39', '3.0000000000', '3.0000000000', 'k�lm vihm', '120', '16763080');
33
INSERT INTO t1 VALUES ( '12', '21', '21.0000000000', '21.0000000000', 'Kuiv', '13158600', '16777215');
34
#INSERT INTO t1 VALUES ( '13', '21', '13.0000000000', '13.0000000000', 'M�rg', '5263615', '16777215');
35
INSERT INTO t1 VALUES ( '14', '21', '22.0000000000', '22.0000000000', 'Niiske', '9869055', '16777215');
36
#INSERT INTO t1 VALUES ( '19', '21', '33.0000000000', '33.0000000000', 'M�rg', '5263615', '16777215');
37
#INSERT INTO t1 VALUES ( '15', '21', '23.0000000000', '23.0000000000', 'M�rg', '5263615', '16777215');
38
INSERT INTO t1 VALUES ( '16', '21', '31.0000000000', '31.0000000000', 'Kuiv', '13158600', '16777215');
39
INSERT INTO t1 VALUES ( '17', '21', '12.0000000000', '12.0000000000', 'Niiske', '9869055', '16777215');
40
INSERT INTO t1 VALUES ( '18', '21', '32.0000000000', '32.0000000000', 'Niiske', '9869055', '16777215');
41
#INSERT INTO t1 VALUES ( '20', '21', '331.0000000000', '331.0000000000', 'H�rmatise hoiatus!', '14448840', '13158600');
42
INSERT INTO t1 VALUES ( '21', '21', '11.0000000000', '11.0000000000', 'Kuiv', '13158600', '16777215');
43
INSERT INTO t1 VALUES ( '22', '33', '21.0000000000', '21.0000000000', 'Pilves, kuiv', '8355711', '12632256');
44
#INSERT INTO t1 VALUES ( '23', '33', '13.0000000000', '13.0000000000', 'Sajab, m�rg', '0', '8355839');
45
INSERT INTO t1 VALUES ( '24', '33', '22.0000000000', '22.0000000000', 'Pilves, niiske', '8355711', '12632319');
46
#INSERT INTO t1 VALUES ( '29', '33', '33.0000000000', '33.0000000000', 'Selge, m�rg', '16777215', '8355839');
47
#INSERT INTO t1 VALUES ( '25', '33', '23.0000000000', '23.0000000000', 'Pilves, m�rg', '8355711', '8355839');
48
INSERT INTO t1 VALUES ( '26', '33', '31.0000000000', '31.0000000000', 'Selge, kuiv', '16777215', '12632256');
49
INSERT INTO t1 VALUES ( '27', '33', '12.0000000000', '12.0000000000', 'Sajab, niiske', '0', '12632319');
50
INSERT INTO t1 VALUES ( '28', '33', '32.0000000000', '32.0000000000', 'Selge, niiske', '16777215', '12632319');
51
#INSERT INTO t1 VALUES ( '30', '33', '331.0000000000', '331.0000000000', 'H�rmatis! selge,kuiv', '16711680', '12632256');
52
INSERT INTO t1 VALUES ( '31', '33', '11.0000000000', '11.0000000000', 'Sajab, kuiv', '0', '12632256');
53
INSERT INTO t1 VALUES ( '32', '11', '1.0000000000', '1.0000000000', 'Korras', '16777215', '49152');
54
#INSERT INTO t1 VALUES ( '33', '21', '335.0000000000', '335.0000000000', 'H�rmatis!', '14448840', '11842740');
55
INSERT INTO t1 VALUES ( '34', '21', '134.0000000000', '134.0000000000', 'Hoiatus, M+S!', '255', '13158600');
56
#INSERT INTO t1 VALUES ( '35', '21', '133.0000000000', '133.0000000000', 'Hoiatus, m�rg!', '5263615', '13158600');
57
#INSERT INTO t1 VALUES ( '36', '21', '135.0000000000', '135.0000000000', 'H�rmatis!', '14448840', '11842740');
58
#INSERT INTO t1 VALUES ( '37', '21', '334.0000000000', '334.0000000000', 'H�rmatise hoiatus!', '14448840', '13158600');
59
INSERT INTO t1 VALUES ( '38', '21', '132.0000000000', '132.0000000000', 'Hoiatus, niiske!', '9869055', '13158600');
60
INSERT INTO t1 VALUES ( '39', '39', '1.0000000000', '1.0000000000', 'ei saja', '11206570', '16777215');
61
INSERT INTO t1 VALUES ( '44', '39', '4.0000000000', '5.0000000000', 'lumi', '16711680', '16763080');
62
INSERT INTO t1 VALUES ( '45', '12', '0.0000000000', '0.0000000000', '', '16777215', '14474460');
63
INSERT INTO t1 VALUES ( '46', '39', '8.0000000000', '8.0000000000', 'rahe', '9830400', '16763080');
64
#INSERT INTO t1 VALUES ( '47', '39', '9.0000000000', '9.0000000000', 't��p ebaselge', '12582912', '16777215');
65
INSERT INTO t1 VALUES ( '48', '39', '7.0000000000', '7.0000000000', 'lumetuisk', '7209070', '16763080');
66
INSERT INTO t1 VALUES ( '142', '15', '2.0000000000', '49.0000000000', '', '0', '16777215');
67
INSERT INTO t1 VALUES ( '52', '1', '-4.9000000000', '-0.1000000000', '', '0', '15774720');
68
INSERT INTO t1 VALUES ( '141', '15', '-4.9000000000', '-0.1000000000', '', '0', '15774720');
69
INSERT INTO t1 VALUES ( '55', '8', '0.0000000000', '0.0000000000', '', '0', '16777215');
70
INSERT INTO t1 VALUES ( '56', '8', '0.0100000000', '0.1000000000', '', '0', '16770560');
71
INSERT INTO t1 VALUES ( '57', '8', '0.1100000000', '25.0000000000', '', '0', '15774720');
72
INSERT INTO t1 VALUES ( '58', '2', '90.0000000000', '94.9000000000', '', NULL, '16770560');
73
INSERT INTO t1 VALUES ( '59', '6', '0.0000000000', '360.0000000000', '', NULL, '16777215');
74
INSERT INTO t1 VALUES ( '61', '21', '38.0000000000', '38.0000000000', 'Niiske', '9869055', '16777215');
75
INSERT INTO t1 VALUES ( '62', '38', '500.0000000000', '999.0000000000', '', '0', '16770560');
76
INSERT INTO t1 VALUES ( '63', '38', '1000.0000000000', '2000.0000000000', '', '0', '16777215');
77
INSERT INTO t1 VALUES ( '64', '17', '0.0000000000', '0.0000000000', '', NULL, '16777215');
78
INSERT INTO t1 VALUES ( '65', '17', '0.1000000000', '10.0000000000', '', NULL, '16770560');
79
INSERT INTO t1 VALUES ( '67', '21', '412.0000000000', '412.0000000000', 'Niiske', '9869055', '16777215');
80
#INSERT INTO t1 VALUES ( '68', '21', '413.0000000000', '413.0000000000', 'M�rg', '5263615', '16777215');
81
#INSERT INTO t1 VALUES ( '69', '21', '113.0000000000', '113.0000000000', 'M�rg', '5263615', '16777215');
82
INSERT INTO t1 VALUES ( '70', '21', '416.0000000000', '416.0000000000', 'Lumine!', '16711680', '11842740');
83
INSERT INTO t1 VALUES ( '71', '38', '0.0000000000', '499.0000000000', '', NULL, '16711680');
84
INSERT INTO t1 VALUES ( '72', '22', '-49.0000000000', '49.0000000000', '', NULL, '16777215');
85
INSERT INTO t1 VALUES ( '73', '13', '0.0000000000', '9.9000000000', '', NULL, '16777215');
86
INSERT INTO t1 VALUES ( '74', '13', '10.0000000000', '14.9000000000', '', NULL, '16770560');
87
INSERT INTO t1 VALUES ( '75', '7', '0.0000000000', '50.0000000000', '', NULL, '16777215');
88
INSERT INTO t1 VALUES ( '76', '18', '0.0000000000', '0.0000000000', '', NULL, '16777215');
89
INSERT INTO t1 VALUES ( '77', '18', '0.1000000000', '10.0000000000', '', NULL, '16770560');
90
INSERT INTO t1 VALUES ( '78', '19', '300.0000000000', '400.0000000000', '', NULL, '16777215');
91
INSERT INTO t1 VALUES ( '79', '19', '0.0000000000', '299.0000000000', '', NULL, '16770560');
92
INSERT INTO t1 VALUES ( '80', '23', '0.0000000000', '100.0000000000', '', NULL, '16777215');
93
INSERT INTO t1 VALUES ( '81', '24', '0.0000000000', '200.0000000000', '', NULL, '16777215');
94
INSERT INTO t1 VALUES ( '82', '26', '0.0000000000', '0.0000000000', '', NULL, '16777215');
95
INSERT INTO t1 VALUES ( '83', '26', '0.1000000000', '5.0000000000', '', NULL, '16776960');
96
INSERT INTO t1 VALUES ( '84', '21', '422.0000000000', '422.0000000000', 'Niiske', '9869055', '16777215');
97
INSERT INTO t1 VALUES ( '85', '21', '411.0000000000', '411.0000000000', 'Saju hoiat.,kuiv!', '16777215', '13158600');
98
#INSERT INTO t1 VALUES ( '86', '21', '423.0000000000', '423.0000000000', 'M�rg', '5263615', '16777215');
99
INSERT INTO t1 VALUES ( '144', '16', '-49.0000000000', '-5.0000000000', '', NULL, '16777215');
100
INSERT INTO t1 VALUES ( '88', '16', '2.0000000000', '49.0000000000', '', NULL, '16777215');
101
#INSERT INTO t1 VALUES ( '89', '21', '338.0000000000', '338.0000000000', 'H�rm.hoiatus, N+S!', '16744319', '13158600');
102
#INSERT INTO t1 VALUES ( '90', '21', '332.0000000000', '332.0000000000', 'H�rm.hoiat., niiske!', '16744319', '13158600');
103
INSERT INTO t1 VALUES ( '91', '21', '114.0000000000', '114.0000000000', 'Hoiatus, M+S!', '255', '13158600');
104
#INSERT INTO t1 VALUES ( '92', '21', '117.0000000000', '117.0000000000', 'Hoiatus, J��!', '14448840', '16711680');
105
INSERT INTO t1 VALUES ( '93', '21', '116.0000000000', '116.0000000000', 'Lumine!', '16711680', '11842740');
106
INSERT INTO t1 VALUES ( '94', '21', '414.0000000000', '414.0000000000', 'Hoiatus, M+S!', '255', '13158600');
107
#INSERT INTO t1 VALUES ( '95', '21', '325.0000000000', '325.0000000000', 'H�rmatis!', '14448840', '11842740');
108
#INSERT INTO t1 VALUES ( '96', '21', '321.0000000000', '321.0000000000', 'H�rmatise hoiatus!', '14448840', '13158600');
109
#INSERT INTO t1 VALUES ( '97', '21', '328.0000000000', '328.0000000000', 'H�rm.hoiatus, N+S!', '16744319', '13158600');
110
INSERT INTO t1 VALUES ( '98', '21', '28.0000000000', '28.0000000000', 'Niiske ja sool', '9869055', '16777215');
111
INSERT INTO t1 VALUES ( '99', '21', '118.0000000000', '118.0000000000', 'Hoiatus, N+S!', '9869055', '13158600');
112
INSERT INTO t1 VALUES ( '100', '21', '418.0000000000', '418.0000000000', 'Hoiatus, N+S!', '9869055', '13158600');
113
#INSERT INTO t1 VALUES ( '101', '21', '322.0000000000', '322.0000000000', 'H�rm.hoiat., niiske!', '16744319', '13158600');
114
INSERT INTO t1 VALUES ( '102', '21', '428.0000000000', '428.0000000000', 'Hoiatus, N+S!', '9869055', '13158600');
115
INSERT INTO t1 VALUES ( '103', '21', '432.0000000000', '432.0000000000', 'Hoiatus, niiske!', '7895240', '13158600');
116
INSERT INTO t1 VALUES ( '104', '21', '421.0000000000', '421.0000000000', 'Saju hoiat.,kuiv!', '16777215', '13158600');
117
#INSERT INTO t1 VALUES ( '105', '21', '24.0000000000', '24.0000000000', 'M�rg ja sool', '255', '16777215');
118
INSERT INTO t1 VALUES ( '106', '21', '438.0000000000', '438.0000000000', 'Hoiatus, N+S!', '9869055', '13158600');
119
INSERT INTO t1 VALUES ( '107', '21', '112.0000000000', '112.0000000000', 'Hoiatus, niiske!', '9869055', '13158600');
120
#INSERT INTO t1 VALUES ( '108', '21', '34.0000000000', '34.0000000000', 'M�rg ja sool', '255', '16777215');
121
INSERT INTO t1 VALUES ( '109', '21', '434.0000000000', '434.0000000000', 'Hoiatus, M+S!', '255', '13158600');
122
INSERT INTO t1 VALUES ( '110', '21', '124.0000000000', '124.0000000000', 'Hoiatus, M+S!', '255', '13158600');
123
INSERT INTO t1 VALUES ( '111', '21', '424.0000000000', '424.0000000000', 'Hoiatus, M+S!', '255', '13158600');
124
#INSERT INTO t1 VALUES ( '112', '21', '123.0000000000', '123.0000000000', 'Hoiatus, m�rg!', '5263615', '13158600');
125
INSERT INTO t1 VALUES ( '140', '15', '-49.0000000000', '-5.0000000000', '', '0', '16777215');
126
INSERT INTO t1 VALUES ( '114', '21', '18.0000000000', '18.0000000000', 'Niiske ja sool', '9869055', '16777215');
127
INSERT INTO t1 VALUES ( '115', '21', '122.0000000000', '122.0000000000', 'Hoiatus, niiske!', '9869055', '13158600');
128
#INSERT INTO t1 VALUES ( '116', '21', '14.0000000000', '14.0000000000', 'M�rg ja sool', '255', '16777215');
129
#INSERT INTO t1 VALUES ( '117', '21', '311.0000000000', '311.0000000000', 'H�rmatise hoiatus!', '14448840', '13158600');
130
INSERT INTO t1 VALUES ( '121', '2', '95.0000000000', '100.0000000000', '', NULL, '15774720');
131
INSERT INTO t1 VALUES ( '118', '2', '0.0000000000', '89.9000000000', '', NULL, '16777215');
132
INSERT INTO t1 VALUES ( '119', '21', '16.0000000000', '16.0000000000', 'Lumine!', '16711680', '11842740');
133
INSERT INTO t1 VALUES ( '120', '21', '26.0000000000', '26.0000000000', 'Lumine!', '16711680', '11842740');
134
INSERT INTO t1 VALUES ( '122', '13', '15.0000000000', '50.0000000000', '', NULL, '15774720');
135
INSERT INTO t1 VALUES ( '123', '5', '0.0000000000', '9.9000000000', '', NULL, '16777215');
136
INSERT INTO t1 VALUES ( '124', '5', '10.0000000000', '14.9000000000', '', NULL, '16770560');
137
INSERT INTO t1 VALUES ( '125', '5', '15.0000000000', '50.0000000000', '', NULL, '15774720');
138
INSERT INTO t1 VALUES ( '126', '21', '128.0000000000', '128.0000000000', 'Hoiatus, N+S!', '9869055', '13158600');
139
#INSERT INTO t1 VALUES ( '127', '21', '318.0000000000', '318.0000000000', 'H�rm.hoiatus, N+S!', '16744319', '13158600');
140
#INSERT INTO t1 VALUES ( '128', '21', '312.0000000000', '312.0000000000', 'H�rm.hoiat., niiske!', '16744319', '13158600');
141
INSERT INTO t1 VALUES ( '129', '21', '126.0000000000', '126.0000000000', 'Lumine!', '16711680', '11842740');
142
#INSERT INTO t1 VALUES ( '130', '21', '324.0000000000', '324.0000000000', 'H�rmatise hoiatus!', '14448840', '13158600');
143
INSERT INTO t1 VALUES ( '131', '21', '316.0000000000', '316.0000000000', 'Lumine!', '16711680', '11842740');
144
INSERT INTO t1 VALUES ( '132', '1', '0.0000000000', '1.9000000000', '', NULL, '16769024');
145
INSERT INTO t1 VALUES ( '134', '3', '-50.0000000000', '50.0000000000', '', NULL, '16777215');
146
INSERT INTO t1 VALUES ( '135', '8', '26.0000000000', '2000.0000000000', '', '9868950', '15774720');
147
INSERT INTO t1 VALUES ( '136', '21', '426.0000000000', '426.0000000000', 'Lumine!', '16711680', '11842740');
148
#INSERT INTO t1 VALUES ( '137', '21', '127.0000000000', '127.0000000000', 'Hoiatus, J��!', '14448840', '16711680');
149
INSERT INTO t1 VALUES ( '138', '21', '121.0000000000', '121.0000000000', 'Kuiv', '13158600', '16777215');
150
INSERT INTO t1 VALUES ( '139', '21', '326.0000000000', '326.0000000000', 'Lumine!', '16711680', '11842740');
151
INSERT INTO t1 VALUES ( '143', '16', '-4.9000000000', '-0.1000000000', '', NULL, '15774720');
152
INSERT INTO t1 VALUES ( '145', '15', '0.0000000000', '1.9000000000', '', '0', '16769024');
153
INSERT INTO t1 VALUES ( '146', '16', '0.0000000000', '1.9000000000', '', '0', '16769024');
154
select * from t1 where minvalue<=1 and maxvalue>=-1 and datatype_id=16;
155
select * from t1 where minvalue<=-1 and maxvalue>=-1 and datatype_id=16;
159
# Test of correct handling leading zero and +/- signs
160
# then values are passed as strings
161
# Also test overflow handling in this case
164
create table t1 (a decimal(10,2));
165
insert into t1 values ("0.0");
166
insert into t1 values ("-0.0");
167
insert into t1 values ("+0.0");
168
insert into t1 values ("01.0");
169
insert into t1 values ("+01.0");
170
insert into t1 values ("-01.0");
171
insert into t1 values ("-.1");
172
insert into t1 values ("+.1");
173
insert into t1 values (".1");
174
insert into t1 values ("00000000000001");
175
insert into t1 values ("+0000000000001");
176
insert into t1 values ("-0000000000001");
177
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
178
insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11");
179
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
180
insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11");
181
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
182
insert into t1 values ("1e+1000");
183
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
184
insert into t1 values ("1e-1000");
185
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
186
insert into t1 values ("-1e+1000");
187
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
188
insert into t1 values ("1e+4294967296");
189
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
190
insert into t1 values ("1e-4294967296");
191
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
192
insert into t1 values ("1e+18446744073709551615");
193
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
194
insert into t1 values ("1e+18446744073709551616");
195
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
196
insert into t1 values ("1e-9223372036854775807");
197
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
198
insert into t1 values ("1e-9223372036854775809");
199
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
200
insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
204
create table t1 (a decimal(10,2));
205
insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0");
206
insert into t1 values ("-.1"),("+.1"),(".1");
207
insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001");
208
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
209
insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11");
210
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
211
insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11");
212
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
213
insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000");
214
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
215
insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
219
create table t1 (a decimal(10,2));
220
insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0");
221
insert into t1 values ("-.1"),("+.1"),(".1");
222
insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001");
223
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
224
insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11");
225
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
226
insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11");
227
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
228
insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000");
229
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
230
insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
235
create table t1 (a decimal(10,2));
236
# The -0.0 needs to be quoted as not all platforms supports this
237
insert into t1 values (0.0),("-0.0"),(+0.0),(01.0),(+01.0),(-01.0);
238
insert into t1 values (-.1),(+.1),(.1);
239
insert into t1 values (00000000000001),(+0000000000001),(-0000000000001);
240
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
241
insert into t1 values (+111111111.11),(111111111.11),(-11111111.11);
242
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
243
insert into t1 values (-111111111.11),(+1111111111.11),(1111111111.11);
244
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
245
insert into t1 values (1e+100),(1e-100),(-1e+100);
246
insert into t1 values (123.4e0);
247
insert into t1 values (123.4e+2);
248
# This INSERT statement will give an error since truncation of a
249
# decimal number in Drizzle is an error. See bug#337038 for further
251
--error ER_WARN_DATA_TRUNCATED
252
insert into t1 values (123.4e-2);
253
insert into t1 values (123e1);
254
insert into t1 values (123e+0);
255
insert into t1 values (MID("987",1,2));
256
insert into t1 values ("987 ");
257
insert into t1 values ("987.6e+2 ");
262
# Test correct handling of overflowed decimal values
265
create table t1 (a decimal);
266
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
267
insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+12345678901'),(99999999999999);
270
create table t1 (a decimal);
271
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
272
insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999);
275
create table t1 (a decimal);
276
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
277
insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999);
280
create table t1 (a decimal);
281
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
282
insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999);
286
# Exponent overflow bug
287
create table t1(a decimal(10,0));
288
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
289
insert into t1 values ("1e4294967295");
292
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD incorrect decimal value
293
insert into t1 values("1e4294967297");
298
# Test of wrong decimal type
301
--error ER_PARSE_ERROR
302
CREATE TABLE t1 (a_dec DECIMAL(-1,0));
303
--error ER_PARSE_ERROR
304
CREATE TABLE t1 (a_dec DECIMAL(-2,1));
305
--error ER_PARSE_ERROR
306
CREATE TABLE t1 (a_dec DECIMAL(-1,1));
307
--error ER_M_BIGGER_THAN_D
308
CREATE TABLE t1 (a_dec DECIMAL(0,11));
311
# Zero prepend overflow bug
314
create table t1(a decimal(7,3));
315
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
316
insert into t1 values ('1'),('+1'),('-1'),('0000000001'),('+0000000001'),('-0000000001'),('10'),('+10'),('-10'),('0000000010'),('+0000000010'),('-0000000010'),('100'),('+100'),('-100'),('0000000100'),('+0000000100'),('-0000000100'),('1000'),('+1000'),('-1000'),('0000001000'),('+0000001000'),('-0000001000'),('10000'),('+10000'),('-10000'),('0000010000'),('+0000010000'),('-0000010000'),('100000'),('+100000'),('-100000'),('0000100000'),('+0000100000'),('-0000100000'),('1000000'),('+1000000'),('-1000000'),('0001000000'),('+0001000000'),('-0001000000'),('10000000'),('+10000000'),('-10000000'),('0010000000'),('+0010000000'),('-0010000000'),('100000000'),('+100000000'),('-100000000'),('0100000000'),('+0100000000'),('-0100000000'),('1000000000'),('+1000000000'),('-1000000000'),('1000000000'),('+1000000000'),('-1000000000');
319
create table t1(a decimal(7,3));
320
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
321
insert into t1 values ('1'),('+1'),('-1'),('0000000001'),('+0000000001'),('-0000000001'),('10'),('+10'),('-10'),('0000000010'),('+0000000010'),('-0000000010'),('100'),('+100'),('-100'),('0000000100'),('+0000000100'),('-0000000100'),('1000'),('+1000'),('-1000'),('0000001000'),('+0000001000'),('-0000001000'),('10000'),('+10000'),('-10000'),('0000010000'),('+0000010000'),('-0000010000'),('100000'),('+100000'),('-100000'),('0000100000'),('+0000100000'),('-0000100000'),('1000000'),('+1000000'),('-1000000'),('0001000000'),('+0001000000'),('-0001000000'),('10000000'),('+10000000'),('-10000000'),('0010000000'),('+0010000000'),('-0010000000'),('100000000'),('+100000000'),('-100000000'),('0100000000'),('+0100000000'),('-0100000000'),('1000000000'),('+1000000000'),('-1000000000'),('1000000000'),('+1000000000'),('-1000000000');
324
create table t1(a decimal(7,3));
325
--error ER_WARN_DATA_OUT_OF_RANGE out of range value
326
insert into t1 values ('1'),('+1'),('-1'),('0000000001'),('+0000000001'),('-0000000001'),('10'),('+10'),('-10'),('0000000010'),('+0000000010'),('-0000000010'),('100'),('+100'),('-100'),('0000000100'),('+0000000100'),('-0000000100'),('1000'),('+1000'),('-1000'),('0000001000'),('+0000001000'),('-0000001000'),('10000'),('+10000'),('-10000'),('0000010000'),('+0000010000'),('-0000010000'),('100000'),('+100000'),('-100000'),('0000100000'),('+0000100000'),('-0000100000'),('1000000'),('+1000000'),('-1000000'),('0001000000'),('+0001000000'),('-0001000000'),('10000000'),('+10000000'),('-10000000'),('0010000000'),('+0010000000'),('-0010000000'),('100000000'),('+100000000'),('-100000000'),('0100000000'),('+0100000000'),('-0100000000'),('1000000000'),('+1000000000'),('-1000000000'),('1000000000'),('+1000000000'),('-1000000000');
332
# Bug #7589: a problem with update from column
335
#create table t1(a decimal(10,5), b decimal(10,1));
336
#insert into t1 values(123.12345, 123.12345);
341
--echo End of 4.1 tests
344
# Test for BUG#8397: decimal type in subselects (Item_cache_decimal)
347
(EMPNUM CHAR(3) NOT NULL,
350
(EMPNUM CHAR(3) NOT NULL,
353
INSERT INTO t1 VALUES ('E1',40);
354
INSERT INTO t1 VALUES ('E8',NULL);
355
INSERT INTO t2 VALUES ('E1',40);
357
SELECT EMPNUM FROM t1 WHERE HOURS IN (SELECT HOURS FROM t2);
358
SELECT EMPNUM FROM t1 WHERE HOURS IN (SELECT HOURS FROM t1);
363
# Test limits of decimal
365
create table t1 (d decimal(64,0));
366
insert into t1 values (1);
369
create table t1 (d decimal(5));
370
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
371
show create table t1;
373
create table t1 (d decimal);
374
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
375
show create table t1;
377
--error ER_TOO_BIG_PRECISION
378
create table t1 (d decimal(66,0));
381
# Test example from manual
384
CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2));
385
INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
386
(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
387
(2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
388
(4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
389
(5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
390
(6, 0.00, 0.00), (6, -51.40, 0.00);
392
SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1 GROUP BY i HAVING a <> b;
393
SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i
398
# A test case for Bug#4956 "strange result, insert into longtext, parameter
399
# with numeric value": ensure that conversion is done identically no matter
400
# where the input data comes from.
402
create table t1 (c1 varchar(100), c2 longtext);
403
insert into t1 set c1= 'non PS, 1.0 as constant', c2=1.0;
404
insert into t1 set c1='PS, 1.0 as constant ', c2=1.0;
406
insert into t1 set c1='non PS, 1.0 in parameter', c2=@a;
411
# A test case for Bug#5673 "Rounding problem in 4.0.21 inserting decimal
412
# value into a char field": this is a regression bug in 4.0 tree caused by
413
# a fix for some other decimal conversion issue. The patch never was
414
# approved to get into 4.0 (maybe because it was considered too intrusive)
418
strippedproductid char(15) not null default '',
419
zlevelprice decimal(10,2) default null,
420
primary key (strippedproductid)
424
productid char(15) not null default '',
425
zlevelprice char(21) default null,
426
primary key (productid)
429
insert into t1 values ('002trans','49.99');
430
insert into t1 values ('003trans','39.98');
431
insert into t1 values ('004trans','31.18');
433
insert INTO t2 SELECT * FROM t1;
439
# Bug #17826 'type_decimal' fails with ps-protocol
441
create table t1 (f1 decimal(5));
442
insert into t1 values (40);
444
select f1 from t1 where f1 in (select f1 from t1);
448
# Bug#22183: Unhandled NULL caused server crash
450
--error ER_ARGUMENT_OUT_OF_RANGE # 1 is not a valid Julian Day Number for SQL date range
452
select from_days(s) as date,t
453
from (select 1 as s,'t' as t union select null, null ) as sub1;
454
# This errors out when it shouldn't!
456
select from_days(s) as date,t
457
from (select TO_DAYS("0001-01-01") as s,'t' as t union select null, null ) as sub1;
460
# Bug#28980: ROUND(<dec expr>, <int col>) returned double values
464
qty decimal(16,6) default NULL,
467
INSERT INTO t1 VALUES (1.1325,3);
469
SELECT ROUND(qty,3), dps, ROUND(qty,dps) FROM t1;
474
# Bug #24541: "Data truncated..." on decimal type columns without any good reason
477
create table t1 (c1 decimal(10,6));
478
insert into t1 (c1) values (9.99e-4);
479
insert into t1 (c1) values (9.98e-4);
480
insert into t1 (c1) values (0.000999);
481
insert into t1 (c1) values (cast(9.99e-4 as decimal(10,6)));
486
# Bug#31019: MOD() function and operator crashes MySQL when
487
# divisor is very long and < 1
490
SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS '%';
491
SELECT MOD(1, .123456789123456789123456789123456789123456789123456789123456789123456789123456789) AS 'MOD()';
493
# Bug #31227: memory overrun with decimal (6,6) and zerofill and group_concat
494
# valgrind will complain about this (the group_concat(f2)) on unpatched mysqld.
496
create table t1 (f1 decimal(6,6),f2 decimal(6,6) );
497
insert into t1 values (-0.123456,0.123456);
498
select group_concat(f1),group_concat(f2) from t1;
502
# BUG#31450 "Query causes error 1048"
505
ua_id decimal(22,0) not null,
506
ua_invited_by_id decimal(22,0) default NULL,
509
insert into t1 values (123, NULL), (456, NULL);
511
--echo this must not produce error 1048:
512
select * from t1 where ua_invited_by_id not in (select ua_id from t1);
517
# Bug #30889: filesort and order by with float/numeric crashes server
520
DROP TABLE IF EXISTS t3;
521
DROP TABLE IF EXISTS t4;
523
CREATE TABLE t1( a NUMERIC, b INT );
524
INSERT INTO t1 VALUES (123456, 40), (123456, 40);
525
SELECT TRUNCATE( a, b ) AS c FROM t1 ORDER BY c;
526
SELECT ROUND( a, b ) AS c FROM t1 ORDER BY c;
527
SELECT ROUND( a, 100 ) AS c FROM t1 ORDER BY c;
529
CREATE TABLE t2( a NUMERIC, b INT );
530
INSERT INTO t2 VALUES (123456, 100);
531
SELECT TRUNCATE( a, b ) AS c FROM t2 ORDER BY c;
532
SELECT ROUND( a, b ) AS c FROM t2 ORDER BY c;
534
CREATE TABLE t3( a DECIMAL, b INT );
535
INSERT INTO t3 VALUES (123456, 40), (123456, 40);
536
SELECT TRUNCATE( a, b ) AS c FROM t3 ORDER BY c;
537
SELECT ROUND( a, b ) AS c FROM t3 ORDER BY c;
538
SELECT ROUND( a, 100 ) AS c FROM t3 ORDER BY c;
540
CREATE TABLE t4( a DECIMAL, b INT );
541
INSERT INTO t4 VALUES (123456, 40), (123456, 40);
542
SELECT TRUNCATE( a, b ) AS c FROM t4 ORDER BY c;
543
SELECT ROUND( a, b ) AS c FROM t4 ORDER BY c;
544
SELECT ROUND( a, 100 ) AS c FROM t4 ORDER BY c;
547
INSERT INTO t1 VALUES (1234567890, 20);
548
# This INSERT statement will give an error since truncation of a
549
# decimal number in Drizzle is an error. See bug#337038 for further
551
--error ER_WARN_DATA_TRUNCATED
552
INSERT INTO t1 VALUES (999.99, 5);
553
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
554
show create table t1;
556
select round(a,b) as c from t1 order by c;
558
DROP TABLE t1, t2, t3, t4;
561
# Bug #33143: Incorrect ORDER BY for ROUND()/TRUNCATE() result
564
CREATE TABLE t1( a DECIMAL(4, 3), b INT );
565
INSERT INTO t1 VALUES ( 1, 5 ), ( 2, 4 ), ( 3, 3 ), ( 4, 2 ), ( 5, 1 );
566
SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c;
567
SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c DESC;
569
CREATE TABLE t2 ( a INT, b INT, c DECIMAL(5, 4) );
571
INSERT INTO t2 VALUES ( 0, 1, 1.2345 ), ( 1, 2, 1.2345 ),
572
( 3, 3, 1.2345 ), ( 2, 4, 1.2345 );
574
SELECT a, b, MAX(ROUND(c, a))
579
SELECT a, b, ROUND(c, a)
582
CREATE TABLE t3( a INT, b DECIMAL(6, 3) );
583
INSERT INTO t3 VALUES( 0, 1.5 );
584
SELECT ROUND( b, a ) FROM t3;
586
CREATE TABLE t4( a INT, b DECIMAL( 12, 0) );
587
INSERT INTO t4 VALUES( -9, 1.5e9 );
588
SELECT ROUND( b, a ) FROM t4;
590
CREATE TABLE t5( a INT, b DECIMAL( 13, 12 ) );
591
INSERT INTO t5 VALUES( 0, 1.5 );
592
INSERT INTO t5 VALUES( 9, 1.5e-9 );
593
SELECT ROUND( b, a ) FROM t5;
595
CREATE TABLE t6( a INT );
596
INSERT INTO t6 VALUES( 6 / 8 );
599
SELECT ROUND(20061108085411.000002);
601
DROP TABLE t1, t2, t3, t4, t5, t6;
603
--echo End of 5.0 tests