1
DROP TABLE IF EXISTS t1, t2;
4
id int(11) NOT NULL auto_increment,
5
datatype_id int(11) DEFAULT '0' NOT NULL,
6
minvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL,
7
maxvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL,
12
UNIQUE datatype_id (datatype_id, minvalue, maxvalue)
14
INSERT INTO t1 VALUES ( '1', '4', '0.0000000000', '0.0000000000', 'Ei saja', '0', '16776960');
15
INSERT INTO t1 VALUES ( '2', '4', '1.0000000000', '1.0000000000', 'Sajab', '16777215', '255');
16
INSERT INTO t1 VALUES ( '3', '1', '2.0000000000', '49.0000000000', '', '0', '16777215');
17
INSERT INTO t1 VALUES ( '60', '11', '0.0000000000', '0.0000000000', 'Rikkis', '16777215', '16711680');
18
INSERT INTO t1 VALUES ( '4', '12', '1.0000000000', '1.0000000000', 'nork sadu', '65280', '14474460');
19
INSERT INTO t1 VALUES ( '5', '12', '2.0000000000', '2.0000000000', 'keskmine sadu', '255', '14474460');
20
INSERT INTO t1 VALUES ( '6', '12', '3.0000000000', '3.0000000000', 'tugev sadu', '127', '14474460');
21
INSERT INTO t1 VALUES ( '43', '39', '6.0000000000', '6.0000000000', 'lobjakas', '13107327', '16763080');
22
INSERT INTO t1 VALUES ( '40', '39', '2.0000000000', '2.0000000000', 'vihm', '8355839', '16777215');
23
INSERT INTO t1 VALUES ( '53', '1', '-35.0000000000', '-5.0000000000', '', '0', '16777215');
24
INSERT INTO t1 VALUES ( '41', '39', '3.0000000000', '3.0000000000', 'k�lm vihm', '120', '16763080');
25
INSERT INTO t1 VALUES ( '12', '21', '21.0000000000', '21.0000000000', 'Kuiv', '13158600', '16777215');
26
INSERT INTO t1 VALUES ( '13', '21', '13.0000000000', '13.0000000000', 'M�rg', '5263615', '16777215');
27
INSERT INTO t1 VALUES ( '14', '21', '22.0000000000', '22.0000000000', 'Niiske', '9869055', '16777215');
28
INSERT INTO t1 VALUES ( '19', '21', '33.0000000000', '33.0000000000', 'M�rg', '5263615', '16777215');
29
INSERT INTO t1 VALUES ( '15', '21', '23.0000000000', '23.0000000000', 'M�rg', '5263615', '16777215');
30
INSERT INTO t1 VALUES ( '16', '21', '31.0000000000', '31.0000000000', 'Kuiv', '13158600', '16777215');
31
INSERT INTO t1 VALUES ( '17', '21', '12.0000000000', '12.0000000000', 'Niiske', '9869055', '16777215');
32
INSERT INTO t1 VALUES ( '18', '21', '32.0000000000', '32.0000000000', 'Niiske', '9869055', '16777215');
33
INSERT INTO t1 VALUES ( '20', '21', '331.0000000000', '331.0000000000', 'H�rmatise hoiatus!', '14448840', '13158600');
34
INSERT INTO t1 VALUES ( '21', '21', '11.0000000000', '11.0000000000', 'Kuiv', '13158600', '16777215');
35
INSERT INTO t1 VALUES ( '22', '33', '21.0000000000', '21.0000000000', 'Pilves, kuiv', '8355711', '12632256');
36
INSERT INTO t1 VALUES ( '23', '33', '13.0000000000', '13.0000000000', 'Sajab, m�rg', '0', '8355839');
37
INSERT INTO t1 VALUES ( '24', '33', '22.0000000000', '22.0000000000', 'Pilves, niiske', '8355711', '12632319');
38
INSERT INTO t1 VALUES ( '29', '33', '33.0000000000', '33.0000000000', 'Selge, m�rg', '16777215', '8355839');
39
INSERT INTO t1 VALUES ( '25', '33', '23.0000000000', '23.0000000000', 'Pilves, m�rg', '8355711', '8355839');
40
INSERT INTO t1 VALUES ( '26', '33', '31.0000000000', '31.0000000000', 'Selge, kuiv', '16777215', '12632256');
41
INSERT INTO t1 VALUES ( '27', '33', '12.0000000000', '12.0000000000', 'Sajab, niiske', '0', '12632319');
42
INSERT INTO t1 VALUES ( '28', '33', '32.0000000000', '32.0000000000', 'Selge, niiske', '16777215', '12632319');
43
INSERT INTO t1 VALUES ( '30', '33', '331.0000000000', '331.0000000000', 'H�rmatis! selge,kuiv', '16711680', '12632256');
44
INSERT INTO t1 VALUES ( '31', '33', '11.0000000000', '11.0000000000', 'Sajab, kuiv', '0', '12632256');
45
INSERT INTO t1 VALUES ( '32', '11', '1.0000000000', '1.0000000000', 'Korras', '16777215', '49152');
46
INSERT INTO t1 VALUES ( '33', '21', '335.0000000000', '335.0000000000', 'H�rmatis!', '14448840', '11842740');
47
INSERT INTO t1 VALUES ( '34', '21', '134.0000000000', '134.0000000000', 'Hoiatus, M+S!', '255', '13158600');
48
INSERT INTO t1 VALUES ( '35', '21', '133.0000000000', '133.0000000000', 'Hoiatus, m�rg!', '5263615', '13158600');
49
INSERT INTO t1 VALUES ( '36', '21', '135.0000000000', '135.0000000000', 'H�rmatis!', '14448840', '11842740');
50
INSERT INTO t1 VALUES ( '37', '21', '334.0000000000', '334.0000000000', 'H�rmatise hoiatus!', '14448840', '13158600');
51
INSERT INTO t1 VALUES ( '38', '21', '132.0000000000', '132.0000000000', 'Hoiatus, niiske!', '9869055', '13158600');
52
INSERT INTO t1 VALUES ( '39', '39', '1.0000000000', '1.0000000000', 'ei saja', '11206570', '16777215');
53
INSERT INTO t1 VALUES ( '44', '39', '4.0000000000', '5.0000000000', 'lumi', '16711680', '16763080');
54
INSERT INTO t1 VALUES ( '45', '12', '0.0000000000', '0.0000000000', '', '16777215', '14474460');
55
INSERT INTO t1 VALUES ( '46', '39', '8.0000000000', '8.0000000000', 'rahe', '9830400', '16763080');
56
INSERT INTO t1 VALUES ( '47', '39', '9.0000000000', '9.0000000000', 't��p ebaselge', '12582912', '16777215');
57
INSERT INTO t1 VALUES ( '48', '39', '7.0000000000', '7.0000000000', 'lumetuisk', '7209070', '16763080');
58
INSERT INTO t1 VALUES ( '142', '15', '2.0000000000', '49.0000000000', '', '0', '16777215');
59
INSERT INTO t1 VALUES ( '52', '1', '-4.9000000000', '-0.1000000000', '', '0', '15774720');
60
INSERT INTO t1 VALUES ( '141', '15', '-4.9000000000', '-0.1000000000', '', '0', '15774720');
61
INSERT INTO t1 VALUES ( '55', '8', '0.0000000000', '0.0000000000', '', '0', '16777215');
62
INSERT INTO t1 VALUES ( '56', '8', '0.0100000000', '0.1000000000', '', '0', '16770560');
63
INSERT INTO t1 VALUES ( '57', '8', '0.1100000000', '25.0000000000', '', '0', '15774720');
64
INSERT INTO t1 VALUES ( '58', '2', '90.0000000000', '94.9000000000', '', NULL, '16770560');
65
INSERT INTO t1 VALUES ( '59', '6', '0.0000000000', '360.0000000000', '', NULL, '16777215');
66
INSERT INTO t1 VALUES ( '61', '21', '38.0000000000', '38.0000000000', 'Niiske', '9869055', '16777215');
67
INSERT INTO t1 VALUES ( '62', '38', '500.0000000000', '999.0000000000', '', '0', '16770560');
68
INSERT INTO t1 VALUES ( '63', '38', '1000.0000000000', '2000.0000000000', '', '0', '16777215');
69
INSERT INTO t1 VALUES ( '64', '17', '0.0000000000', '0.0000000000', '', NULL, '16777215');
70
INSERT INTO t1 VALUES ( '65', '17', '0.1000000000', '10.0000000000', '', NULL, '16770560');
71
INSERT INTO t1 VALUES ( '67', '21', '412.0000000000', '412.0000000000', 'Niiske', '9869055', '16777215');
72
INSERT INTO t1 VALUES ( '68', '21', '413.0000000000', '413.0000000000', 'M�rg', '5263615', '16777215');
73
INSERT INTO t1 VALUES ( '69', '21', '113.0000000000', '113.0000000000', 'M�rg', '5263615', '16777215');
74
INSERT INTO t1 VALUES ( '70', '21', '416.0000000000', '416.0000000000', 'Lumine!', '16711680', '11842740');
75
INSERT INTO t1 VALUES ( '71', '38', '0.0000000000', '499.0000000000', '', NULL, '16711680');
76
INSERT INTO t1 VALUES ( '72', '22', '-49.0000000000', '49.0000000000', '', NULL, '16777215');
77
INSERT INTO t1 VALUES ( '73', '13', '0.0000000000', '9.9000000000', '', NULL, '16777215');
78
INSERT INTO t1 VALUES ( '74', '13', '10.0000000000', '14.9000000000', '', NULL, '16770560');
79
INSERT INTO t1 VALUES ( '75', '7', '0.0000000000', '50.0000000000', '', NULL, '16777215');
80
INSERT INTO t1 VALUES ( '76', '18', '0.0000000000', '0.0000000000', '', NULL, '16777215');
81
INSERT INTO t1 VALUES ( '77', '18', '0.1000000000', '10.0000000000', '', NULL, '16770560');
82
INSERT INTO t1 VALUES ( '78', '19', '300.0000000000', '400.0000000000', '', NULL, '16777215');
83
INSERT INTO t1 VALUES ( '79', '19', '0.0000000000', '299.0000000000', '', NULL, '16770560');
84
INSERT INTO t1 VALUES ( '80', '23', '0.0000000000', '100.0000000000', '', NULL, '16777215');
85
INSERT INTO t1 VALUES ( '81', '24', '0.0000000000', '200.0000000000', '', NULL, '16777215');
86
INSERT INTO t1 VALUES ( '82', '26', '0.0000000000', '0.0000000000', '', NULL, '16777215');
87
INSERT INTO t1 VALUES ( '83', '26', '0.1000000000', '5.0000000000', '', NULL, '16776960');
88
INSERT INTO t1 VALUES ( '84', '21', '422.0000000000', '422.0000000000', 'Niiske', '9869055', '16777215');
89
INSERT INTO t1 VALUES ( '85', '21', '411.0000000000', '411.0000000000', 'Saju hoiat.,kuiv!', '16777215', '13158600');
90
INSERT INTO t1 VALUES ( '86', '21', '423.0000000000', '423.0000000000', 'M�rg', '5263615', '16777215');
91
INSERT INTO t1 VALUES ( '144', '16', '-49.0000000000', '-5.0000000000', '', NULL, '16777215');
92
INSERT INTO t1 VALUES ( '88', '16', '2.0000000000', '49.0000000000', '', NULL, '16777215');
93
INSERT INTO t1 VALUES ( '89', '21', '338.0000000000', '338.0000000000', 'H�rm.hoiatus, N+S!', '16744319', '13158600');
94
INSERT INTO t1 VALUES ( '90', '21', '332.0000000000', '332.0000000000', 'H�rm.hoiat., niiske!', '16744319', '13158600');
95
INSERT INTO t1 VALUES ( '91', '21', '114.0000000000', '114.0000000000', 'Hoiatus, M+S!', '255', '13158600');
96
INSERT INTO t1 VALUES ( '92', '21', '117.0000000000', '117.0000000000', 'Hoiatus, J��!', '14448840', '16711680');
97
INSERT INTO t1 VALUES ( '93', '21', '116.0000000000', '116.0000000000', 'Lumine!', '16711680', '11842740');
98
INSERT INTO t1 VALUES ( '94', '21', '414.0000000000', '414.0000000000', 'Hoiatus, M+S!', '255', '13158600');
99
INSERT INTO t1 VALUES ( '95', '21', '325.0000000000', '325.0000000000', 'H�rmatis!', '14448840', '11842740');
100
INSERT INTO t1 VALUES ( '96', '21', '321.0000000000', '321.0000000000', 'H�rmatise hoiatus!', '14448840', '13158600');
101
INSERT INTO t1 VALUES ( '97', '21', '328.0000000000', '328.0000000000', 'H�rm.hoiatus, N+S!', '16744319', '13158600');
102
INSERT INTO t1 VALUES ( '98', '21', '28.0000000000', '28.0000000000', 'Niiske ja sool', '9869055', '16777215');
103
INSERT INTO t1 VALUES ( '99', '21', '118.0000000000', '118.0000000000', 'Hoiatus, N+S!', '9869055', '13158600');
104
INSERT INTO t1 VALUES ( '100', '21', '418.0000000000', '418.0000000000', 'Hoiatus, N+S!', '9869055', '13158600');
105
INSERT INTO t1 VALUES ( '101', '21', '322.0000000000', '322.0000000000', 'H�rm.hoiat., niiske!', '16744319', '13158600');
106
INSERT INTO t1 VALUES ( '102', '21', '428.0000000000', '428.0000000000', 'Hoiatus, N+S!', '9869055', '13158600');
107
INSERT INTO t1 VALUES ( '103', '21', '432.0000000000', '432.0000000000', 'Hoiatus, niiske!', '7895240', '13158600');
108
INSERT INTO t1 VALUES ( '104', '21', '421.0000000000', '421.0000000000', 'Saju hoiat.,kuiv!', '16777215', '13158600');
109
INSERT INTO t1 VALUES ( '105', '21', '24.0000000000', '24.0000000000', 'M�rg ja sool', '255', '16777215');
110
INSERT INTO t1 VALUES ( '106', '21', '438.0000000000', '438.0000000000', 'Hoiatus, N+S!', '9869055', '13158600');
111
INSERT INTO t1 VALUES ( '107', '21', '112.0000000000', '112.0000000000', 'Hoiatus, niiske!', '9869055', '13158600');
112
INSERT INTO t1 VALUES ( '108', '21', '34.0000000000', '34.0000000000', 'M�rg ja sool', '255', '16777215');
113
INSERT INTO t1 VALUES ( '109', '21', '434.0000000000', '434.0000000000', 'Hoiatus, M+S!', '255', '13158600');
114
INSERT INTO t1 VALUES ( '110', '21', '124.0000000000', '124.0000000000', 'Hoiatus, M+S!', '255', '13158600');
115
INSERT INTO t1 VALUES ( '111', '21', '424.0000000000', '424.0000000000', 'Hoiatus, M+S!', '255', '13158600');
116
INSERT INTO t1 VALUES ( '112', '21', '123.0000000000', '123.0000000000', 'Hoiatus, m�rg!', '5263615', '13158600');
117
INSERT INTO t1 VALUES ( '140', '15', '-49.0000000000', '-5.0000000000', '', '0', '16777215');
118
INSERT INTO t1 VALUES ( '114', '21', '18.0000000000', '18.0000000000', 'Niiske ja sool', '9869055', '16777215');
119
INSERT INTO t1 VALUES ( '115', '21', '122.0000000000', '122.0000000000', 'Hoiatus, niiske!', '9869055', '13158600');
120
INSERT INTO t1 VALUES ( '116', '21', '14.0000000000', '14.0000000000', 'M�rg ja sool', '255', '16777215');
121
INSERT INTO t1 VALUES ( '117', '21', '311.0000000000', '311.0000000000', 'H�rmatise hoiatus!', '14448840', '13158600');
122
INSERT INTO t1 VALUES ( '121', '2', '95.0000000000', '100.0000000000', '', NULL, '15774720');
123
INSERT INTO t1 VALUES ( '118', '2', '0.0000000000', '89.9000000000', '', NULL, '16777215');
124
INSERT INTO t1 VALUES ( '119', '21', '16.0000000000', '16.0000000000', 'Lumine!', '16711680', '11842740');
125
INSERT INTO t1 VALUES ( '120', '21', '26.0000000000', '26.0000000000', 'Lumine!', '16711680', '11842740');
126
INSERT INTO t1 VALUES ( '122', '13', '15.0000000000', '50.0000000000', '', NULL, '15774720');
127
INSERT INTO t1 VALUES ( '123', '5', '0.0000000000', '9.9000000000', '', NULL, '16777215');
128
INSERT INTO t1 VALUES ( '124', '5', '10.0000000000', '14.9000000000', '', NULL, '16770560');
129
INSERT INTO t1 VALUES ( '125', '5', '15.0000000000', '50.0000000000', '', NULL, '15774720');
130
INSERT INTO t1 VALUES ( '126', '21', '128.0000000000', '128.0000000000', 'Hoiatus, N+S!', '9869055', '13158600');
131
INSERT INTO t1 VALUES ( '127', '21', '318.0000000000', '318.0000000000', 'H�rm.hoiatus, N+S!', '16744319', '13158600');
132
INSERT INTO t1 VALUES ( '128', '21', '312.0000000000', '312.0000000000', 'H�rm.hoiat., niiske!', '16744319', '13158600');
133
INSERT INTO t1 VALUES ( '129', '21', '126.0000000000', '126.0000000000', 'Lumine!', '16711680', '11842740');
134
INSERT INTO t1 VALUES ( '130', '21', '324.0000000000', '324.0000000000', 'H�rmatise hoiatus!', '14448840', '13158600');
135
INSERT INTO t1 VALUES ( '131', '21', '316.0000000000', '316.0000000000', 'Lumine!', '16711680', '11842740');
136
INSERT INTO t1 VALUES ( '132', '1', '0.0000000000', '1.9000000000', '', NULL, '16769024');
137
INSERT INTO t1 VALUES ( '134', '3', '-50.0000000000', '50.0000000000', '', NULL, '16777215');
138
INSERT INTO t1 VALUES ( '135', '8', '26.0000000000', '2000.0000000000', '', '9868950', '15774720');
139
INSERT INTO t1 VALUES ( '136', '21', '426.0000000000', '426.0000000000', 'Lumine!', '16711680', '11842740');
140
INSERT INTO t1 VALUES ( '137', '21', '127.0000000000', '127.0000000000', 'Hoiatus, J��!', '14448840', '16711680');
141
INSERT INTO t1 VALUES ( '138', '21', '121.0000000000', '121.0000000000', 'Kuiv', '13158600', '16777215');
142
INSERT INTO t1 VALUES ( '139', '21', '326.0000000000', '326.0000000000', 'Lumine!', '16711680', '11842740');
143
INSERT INTO t1 VALUES ( '143', '16', '-4.9000000000', '-0.1000000000', '', NULL, '15774720');
144
INSERT INTO t1 VALUES ( '145', '15', '0.0000000000', '1.9000000000', '', '0', '16769024');
145
INSERT INTO t1 VALUES ( '146', '16', '0.0000000000', '1.9000000000', '', '0', '16769024');
146
select * from t1 where minvalue<=1 and maxvalue>=-1 and datatype_id=16;
147
id datatype_id minvalue maxvalue valuename forecolor backcolor
148
143 16 -4.9000000000 -0.1000000000 NULL 15774720
149
146 16 0.0000000000 1.9000000000 0 16769024
150
select * from t1 where minvalue<=-1 and maxvalue>=-1 and datatype_id=16;
151
id datatype_id minvalue maxvalue valuename forecolor backcolor
152
143 16 -4.9000000000 -0.1000000000 NULL 15774720
154
create table t1 (a decimal(10,2));
155
insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0");
156
insert into t1 values ("-.1"),("+.1"),(".1");
157
insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001");
158
insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11");
160
Warning 1264 Out of range value for column 'a' at row 1
161
Warning 1264 Out of range value for column 'a' at row 2
162
insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11");
164
Warning 1264 Out of range value for column 'a' at row 1
165
Warning 1264 Out of range value for column 'a' at row 2
166
Warning 1264 Out of range value for column 'a' at row 3
167
insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000");
169
Warning 1264 Out of range value for column 'a' at row 1
170
Note 1265 Data truncated for column 'a' at row 2
171
Warning 1264 Out of range value for column 'a' at row 3
172
insert into t1 values ("1e+4294967296"),("1e-4294967296");
174
Warning 1264 Out of range value for column 'a' at row 1
175
Note 1265 Data truncated for column 'a' at row 2
176
insert into t1 values ("1e+18446744073709551615"),("1e+18446744073709551616"),("1e-9223372036854775807"),("1e-9223372036854775809");
178
Warning 1264 Out of range value for column 'a' at row 1
179
Warning 1366 Incorrect decimal value: '1e+18446744073709551616' for column 'a' at row 2
180
Note 1265 Data truncated for column 'a' at row 3
181
Warning 1366 Incorrect decimal value: '1e-9223372036854775809' for column 'a' at row 4
182
insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
184
Note 1265 Data truncated for column 'a' at row 1
185
Note 1265 Data truncated for column 'a' at row 3
221
create table t1 (a decimal(10,2) unsigned);
222
insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0");
224
Warning 1264 Out of range value for column 'a' at row 2
225
Warning 1264 Out of range value for column 'a' at row 6
226
insert into t1 values ("-.1"),("+.1"),(".1");
228
Warning 1264 Out of range value for column 'a' at row 1
229
insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001");
231
Warning 1264 Out of range value for column 'a' at row 3
232
insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11");
234
Warning 1264 Out of range value for column 'a' at row 1
235
Warning 1264 Out of range value for column 'a' at row 2
236
Warning 1264 Out of range value for column 'a' at row 3
237
insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11");
239
Warning 1264 Out of range value for column 'a' at row 1
240
Warning 1264 Out of range value for column 'a' at row 2
241
Warning 1264 Out of range value for column 'a' at row 3
242
insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000");
244
Warning 1264 Out of range value for column 'a' at row 1
245
Note 1265 Data truncated for column 'a' at row 2
246
Warning 1264 Out of range value for column 'a' at row 3
247
insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
249
Note 1265 Data truncated for column 'a' at row 1
250
Note 1265 Data truncated for column 'a' at row 3
280
create table t1 (a decimal(10,2) zerofill);
281
insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0");
283
Warning 1264 Out of range value for column 'a' at row 2
284
Warning 1264 Out of range value for column 'a' at row 6
285
insert into t1 values ("-.1"),("+.1"),(".1");
287
Warning 1264 Out of range value for column 'a' at row 1
288
insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001");
290
Warning 1264 Out of range value for column 'a' at row 3
291
insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11");
293
Warning 1264 Out of range value for column 'a' at row 1
294
Warning 1264 Out of range value for column 'a' at row 2
295
Warning 1264 Out of range value for column 'a' at row 3
296
insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11");
298
Warning 1264 Out of range value for column 'a' at row 1
299
Warning 1264 Out of range value for column 'a' at row 2
300
Warning 1264 Out of range value for column 'a' at row 3
301
insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000");
303
Warning 1264 Out of range value for column 'a' at row 1
304
Note 1265 Data truncated for column 'a' at row 2
305
Warning 1264 Out of range value for column 'a' at row 3
306
insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
308
Note 1265 Data truncated for column 'a' at row 1
309
Note 1265 Data truncated for column 'a' at row 3
339
create table t1 (a decimal(10,2));
340
insert into t1 values (0.0),("-0.0"),(+0.0),(01.0),(+01.0),(-01.0);
341
insert into t1 values (-.1),(+.1),(.1);
342
insert into t1 values (00000000000001),(+0000000000001),(-0000000000001);
343
insert into t1 values (+111111111.11),(111111111.11),(-11111111.11);
345
Warning 1264 Out of range value for column 'a' at row 1
346
Warning 1264 Out of range value for column 'a' at row 2
347
insert into t1 values (-111111111.11),(+1111111111.11),(1111111111.11);
349
Warning 1264 Out of range value for column 'a' at row 1
350
Warning 1264 Out of range value for column 'a' at row 2
351
Warning 1264 Out of range value for column 'a' at row 3
352
insert into t1 values (1e+100),(1e-100),(-1e+100);
354
Warning 1264 Out of range value for column 'a' at row 1
355
Note 1265 Data truncated for column 'a' at row 2
356
Warning 1264 Out of range value for column 'a' at row 3
357
insert into t1 values (123.4e0),(123.4e+2),(123.4e-2),(123e1),(123e+0);
359
Note 1265 Data truncated for column 'a' at row 3
360
insert into t1 values (MID("987",1,2)),("987 "),("987.6e+2 ");
393
create table t1 (a decimal);
394
insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+12345678901'),(99999999999999);
396
Warning 1264 Out of range value for column 'a' at row 1
397
Warning 1264 Out of range value for column 'a' at row 6
398
Warning 1264 Out of range value for column 'a' at row 7
409
create table t1 (a decimal unsigned);
410
insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999);
412
Warning 1264 Out of range value for column 'a' at row 1
413
Warning 1264 Out of range value for column 'a' at row 2
414
Warning 1264 Out of range value for column 'a' at row 7
425
create table t1 (a decimal zerofill);
426
insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999);
428
Warning 1264 Out of range value for column 'a' at row 1
429
Warning 1264 Out of range value for column 'a' at row 2
430
Warning 1264 Out of range value for column 'a' at row 7
441
create table t1 (a decimal unsigned zerofill);
442
insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999);
444
Warning 1264 Out of range value for column 'a' at row 1
445
Warning 1264 Out of range value for column 'a' at row 2
446
Warning 1264 Out of range value for column 'a' at row 7
457
create table t1(a decimal(10,0));
458
insert into t1 values ("1e4294967295");
460
Warning 1264 Out of range value for column 'a' at row 1
465
insert into t1 values("1e4294967297");
467
Warning 1264 Out of range value for column 'a' at row 1
472
CREATE TABLE t1 (a_dec DECIMAL(-1,0));
473
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1,0))' at line 1
474
CREATE TABLE t1 (a_dec DECIMAL(-2,1));
475
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2,1))' at line 1
476
CREATE TABLE t1 (a_dec DECIMAL(-1,1));
477
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1,1))' at line 1
478
CREATE TABLE t1 (a_dec DECIMAL(0,11));
479
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'a_dec').
480
create table t1(a decimal(7,3));
481
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');
545
create table t1(a decimal(7,3) unsigned);
546
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');
610
create table t1(a decimal(7,3) zerofill);
611
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');
675
create table t1(a decimal(10,5), b decimal(10,1));
676
insert into t1 values(123.12345, 123.12345);
678
Note 1265 Data truncated for column 'b' at row 1
681
Note 1265 Data truncated for column 'b' at row 1
688
(EMPNUM CHAR(3) NOT NULL,
691
(EMPNUM CHAR(3) NOT NULL,
693
INSERT INTO t1 VALUES ('E1',40);
694
INSERT INTO t1 VALUES ('E8',NULL);
695
INSERT INTO t2 VALUES ('E1',40);
696
SELECT EMPNUM FROM t1 WHERE HOURS IN (SELECT HOURS FROM t2);
699
SELECT EMPNUM FROM t1 WHERE HOURS IN (SELECT HOURS FROM t1);
703
create table t1 (d decimal(64,0));
704
insert into t1 values (1);
709
create table t1 (d decimal(5));
710
show create table t1;
712
t1 CREATE TABLE `t1` (
713
`d` decimal(5,0) DEFAULT NULL
714
) ENGINE=MyISAM DEFAULT CHARSET=latin1
716
create table t1 (d decimal);
717
show create table t1;
719
t1 CREATE TABLE `t1` (
720
`d` decimal(10,0) DEFAULT NULL
721
) ENGINE=MyISAM DEFAULT CHARSET=latin1
723
create table t1 (d decimal(66,0));
724
ERROR 42000: Too big precision 66 specified for column 'd'. Maximum is 65.
725
CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2));
726
INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
727
(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
728
(2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
729
(4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
730
(5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
731
(6, 0.00, 0.00), (6, -51.40, 0.00);
732
SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1 GROUP BY i HAVING a <> b;
735
SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i
740
create table t1 (c1 varchar(100), c2 longtext);
741
insert into t1 set c1= 'non PS, 1.0 as constant', c2=1.0;
742
prepare stmt from "insert into t1 set c1='PS, 1.0 as constant ', c2=1.0";
745
insert into t1 set c1='non PS, 1.0 in parameter', c2=@a;
746
prepare stmt from "insert into t1 set c1='PS, 1.0 in parameter ', c2=?";
747
execute stmt using @a;
750
non PS, 1.0 as constant 1.0
751
PS, 1.0 as constant 1.0
752
non PS, 1.0 in parameter 1.0
753
PS, 1.0 in parameter 1.0
754
deallocate prepare stmt;
757
strippedproductid char(15) not null default '',
758
zlevelprice decimal(10,2) default null,
759
primary key (strippedproductid)
762
productid char(15) not null default '',
763
zlevelprice char(21) default null,
764
primary key (productid)
766
insert into t1 values ('002trans','49.99');
767
insert into t1 values ('003trans','39.98');
768
insert into t1 values ('004trans','31.18');
769
insert INTO t2 SELECT * FROM t1;
771
productid zlevelprice
776
create table t1 (f1 decimal(5));
777
insert into t1 values (40);
779
select f1 from t1 where f1 in (select f1 from t1);
784
select from_days(s) as date,t
785
from (select 1 as s,'t' as t union select null, null ) as sub1;
786
select group_concat(t) from t1 group by week(date)/10;
791
qty decimal(16,6) default NULL,
792
dps tinyint(3) unsigned default NULL
794
INSERT INTO t1 VALUES (1.1325,3);
795
SELECT ROUND(qty,3), dps, ROUND(qty,dps) FROM t1;
796
ROUND(qty,3) dps ROUND(qty,dps)
799
SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS '%';
801
0.012345687012345687012345687012345687012345687012345687012345687012345687000000000
802
SELECT MOD(1, .123456789123456789123456789123456789123456789123456789123456789123456789123456789) AS 'MOD()';
804
0.012345687012345687012345687012345687012345687012345687012345687012345687000000000
805
create table t1 (f1 decimal(6,6),f2 decimal(6,6) zerofill);
806
insert into t1 values (-0.123456,0.123456);
807
select group_concat(f1),group_concat(f2) from t1;
808
group_concat(f1) group_concat(f2)
812
ua_id decimal(22,0) not null,
813
ua_invited_by_id decimal(22,0) default NULL,
816
insert into t1 values (123, NULL), (456, NULL);
817
this must not produce error 1048:
818
select * from t1 where ua_invited_by_id not in (select ua_id from t1);
819
ua_id ua_invited_by_id
821
DROP TABLE IF EXISTS t3;
822
DROP TABLE IF EXISTS t4;
823
CREATE TABLE t1( a NUMERIC, b INT );
824
INSERT INTO t1 VALUES (123456, 40), (123456, 40);
825
SELECT TRUNCATE( a, b ) AS c FROM t1 ORDER BY c;
829
SELECT ROUND( a, b ) AS c FROM t1 ORDER BY c;
833
SELECT ROUND( a, 100 ) AS c FROM t1 ORDER BY c;
835
123456.000000000000000000000000000000
836
123456.000000000000000000000000000000
837
CREATE TABLE t2( a NUMERIC, b INT );
838
INSERT INTO t2 VALUES (123456, 100);
839
SELECT TRUNCATE( a, b ) AS c FROM t2 ORDER BY c;
842
SELECT ROUND( a, b ) AS c FROM t2 ORDER BY c;
845
CREATE TABLE t3( a DECIMAL, b INT );
846
INSERT INTO t3 VALUES (123456, 40), (123456, 40);
847
SELECT TRUNCATE( a, b ) AS c FROM t3 ORDER BY c;
851
SELECT ROUND( a, b ) AS c FROM t3 ORDER BY c;
855
SELECT ROUND( a, 100 ) AS c FROM t3 ORDER BY c;
857
123456.000000000000000000000000000000
858
123456.000000000000000000000000000000
859
CREATE TABLE t4( a DECIMAL, b INT );
860
INSERT INTO t4 VALUES (123456, 40), (123456, 40);
861
SELECT TRUNCATE( a, b ) AS c FROM t4 ORDER BY c;
865
SELECT ROUND( a, b ) AS c FROM t4 ORDER BY c;
869
SELECT ROUND( a, 100 ) AS c FROM t4 ORDER BY c;
871
123456.000000000000000000000000000000
872
123456.000000000000000000000000000000
874
INSERT INTO t1 VALUES (1234567890, 20), (999.99, 5);
876
Note 1265 Data truncated for column 'a' at row 2
877
show create table t1;
879
t1 CREATE TABLE `t1` (
880
`a` decimal(10,0) DEFAULT NULL,
881
`b` int(11) DEFAULT NULL
882
) ENGINE=MyISAM DEFAULT CHARSET=latin1
883
select round(a,b) as c from t1 order by c;
887
DROP TABLE t1, t2, t3, t4;
888
CREATE TABLE t1( a DECIMAL(4, 3), b INT );
889
INSERT INTO t1 VALUES ( 1, 5 ), ( 2, 4 ), ( 3, 3 ), ( 4, 2 ), ( 5, 1 );
890
SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c;
897
SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c DESC;
904
CREATE TABLE t2 ( a INT, b INT, c DECIMAL(5, 4) );
905
INSERT INTO t2 VALUES ( 0, 1, 1.2345 ), ( 1, 2, 1.2345 ),
906
( 3, 3, 1.2345 ), ( 2, 4, 1.2345 );
907
SELECT a, b, MAX(ROUND(c, a))
916
SELECT a, b, ROUND(c, a)
923
CREATE TABLE t3( a INT, b DECIMAL(6, 3) );
924
INSERT INTO t3 VALUES( 0, 1.5 );
925
SELECT ROUND( b, a ) FROM t3;
928
CREATE TABLE t4( a INT, b DECIMAL( 12, 0) );
929
INSERT INTO t4 VALUES( -9, 1.5e9 );
930
SELECT ROUND( b, a ) FROM t4;
933
CREATE TABLE t5( a INT, b DECIMAL( 13, 12 ) );
934
INSERT INTO t5 VALUES( 0, 1.5 );
935
INSERT INTO t5 VALUES( 9, 1.5e-9 );
936
SELECT ROUND( b, a ) FROM t5;
940
CREATE TABLE t6( a INT );
941
INSERT INTO t6 VALUES( 6 / 8 );
945
SELECT ROUND(20061108085411.000002);
946
ROUND(20061108085411.000002)
948
DROP TABLE t1, t2, t3, t4, t5, t6;
949
create table t1(`c` decimal(9,2));
950
insert into t1 values (300),(201.11);
951
select max(case 1 when 1 then c else null end) from t1 group by c;
952
max(case 1 when 1 then c else null end)
added
removed
mysql-test/r/type_decimal.result
