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
create table t1 (c1 decimal(10,6));
800
insert into t1 (c1) values (9.99e-4);
801
insert into t1 (c1) values (9.98e-4);
802
insert into t1 (c1) values (0.000999);
803
insert into t1 (c1) values (cast(9.99e-4 as decimal(10,6)));
811
SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS '%';
813
0.012345687012345687012345687012345687012345687012345687012345687012345687000000000
814
SELECT MOD(1, .123456789123456789123456789123456789123456789123456789123456789123456789123456789) AS 'MOD()';
816
0.012345687012345687012345687012345687012345687012345687012345687012345687000000000
817
create table t1 (f1 decimal(6,6),f2 decimal(6,6) zerofill);
818
insert into t1 values (-0.123456,0.123456);
819
select group_concat(f1),group_concat(f2) from t1;
820
group_concat(f1) group_concat(f2)
824
ua_id decimal(22,0) not null,
825
ua_invited_by_id decimal(22,0) default NULL,
828
insert into t1 values (123, NULL), (456, NULL);
829
this must not produce error 1048:
830
select * from t1 where ua_invited_by_id not in (select ua_id from t1);
831
ua_id ua_invited_by_id
833
DROP TABLE IF EXISTS t3;
834
DROP TABLE IF EXISTS t4;
835
CREATE TABLE t1( a NUMERIC, b INT );
836
INSERT INTO t1 VALUES (123456, 40), (123456, 40);
837
SELECT TRUNCATE( a, b ) AS c FROM t1 ORDER BY c;
841
SELECT ROUND( a, b ) AS c FROM t1 ORDER BY c;
845
SELECT ROUND( a, 100 ) AS c FROM t1 ORDER BY c;
847
123456.000000000000000000000000000000
848
123456.000000000000000000000000000000
849
CREATE TABLE t2( a NUMERIC, b INT );
850
INSERT INTO t2 VALUES (123456, 100);
851
SELECT TRUNCATE( a, b ) AS c FROM t2 ORDER BY c;
854
SELECT ROUND( a, b ) AS c FROM t2 ORDER BY c;
857
CREATE TABLE t3( a DECIMAL, b INT );
858
INSERT INTO t3 VALUES (123456, 40), (123456, 40);
859
SELECT TRUNCATE( a, b ) AS c FROM t3 ORDER BY c;
863
SELECT ROUND( a, b ) AS c FROM t3 ORDER BY c;
867
SELECT ROUND( a, 100 ) AS c FROM t3 ORDER BY c;
869
123456.000000000000000000000000000000
870
123456.000000000000000000000000000000
871
CREATE TABLE t4( a DECIMAL, b INT );
872
INSERT INTO t4 VALUES (123456, 40), (123456, 40);
873
SELECT TRUNCATE( a, b ) AS c FROM t4 ORDER BY c;
877
SELECT ROUND( a, b ) AS c FROM t4 ORDER BY c;
881
SELECT ROUND( a, 100 ) AS c FROM t4 ORDER BY c;
883
123456.000000000000000000000000000000
884
123456.000000000000000000000000000000
886
INSERT INTO t1 VALUES (1234567890, 20), (999.99, 5);
888
Note 1265 Data truncated for column 'a' at row 2
889
show create table t1;
891
t1 CREATE TABLE `t1` (
892
`a` decimal(10,0) DEFAULT NULL,
893
`b` int(11) DEFAULT NULL
894
) ENGINE=MyISAM DEFAULT CHARSET=latin1
895
select round(a,b) as c from t1 order by c;
899
DROP TABLE t1, t2, t3, t4;
900
CREATE TABLE t1( a DECIMAL(4, 3), b INT );
901
INSERT INTO t1 VALUES ( 1, 5 ), ( 2, 4 ), ( 3, 3 ), ( 4, 2 ), ( 5, 1 );
902
SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c;
909
SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c DESC;
916
CREATE TABLE t2 ( a INT, b INT, c DECIMAL(5, 4) );
917
INSERT INTO t2 VALUES ( 0, 1, 1.2345 ), ( 1, 2, 1.2345 ),
918
( 3, 3, 1.2345 ), ( 2, 4, 1.2345 );
919
SELECT a, b, MAX(ROUND(c, a))
928
SELECT a, b, ROUND(c, a)
935
CREATE TABLE t3( a INT, b DECIMAL(6, 3) );
936
INSERT INTO t3 VALUES( 0, 1.5 );
937
SELECT ROUND( b, a ) FROM t3;
940
CREATE TABLE t4( a INT, b DECIMAL( 12, 0) );
941
INSERT INTO t4 VALUES( -9, 1.5e9 );
942
SELECT ROUND( b, a ) FROM t4;
945
CREATE TABLE t5( a INT, b DECIMAL( 13, 12 ) );
946
INSERT INTO t5 VALUES( 0, 1.5 );
947
INSERT INTO t5 VALUES( 9, 1.5e-9 );
948
SELECT ROUND( b, a ) FROM t5;
952
CREATE TABLE t6( a INT );
953
INSERT INTO t6 VALUES( 6 / 8 );
957
SELECT ROUND(20061108085411.000002);
958
ROUND(20061108085411.000002)
960
DROP TABLE t1, t2, t3, t4, t5, t6;