1
DROP TABLE IF EXISTS t1, t2;
4
id int NOT NULL auto_increment,
5
datatype_id int 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 ( '12', '21', '21.0000000000', '21.0000000000', 'Kuiv', '13158600', '16777215');
25
INSERT INTO t1 VALUES ( '14', '21', '22.0000000000', '22.0000000000', 'Niiske', '9869055', '16777215');
26
INSERT INTO t1 VALUES ( '16', '21', '31.0000000000', '31.0000000000', 'Kuiv', '13158600', '16777215');
27
INSERT INTO t1 VALUES ( '17', '21', '12.0000000000', '12.0000000000', 'Niiske', '9869055', '16777215');
28
INSERT INTO t1 VALUES ( '18', '21', '32.0000000000', '32.0000000000', 'Niiske', '9869055', '16777215');
29
INSERT INTO t1 VALUES ( '21', '21', '11.0000000000', '11.0000000000', 'Kuiv', '13158600', '16777215');
30
INSERT INTO t1 VALUES ( '22', '33', '21.0000000000', '21.0000000000', 'Pilves, kuiv', '8355711', '12632256');
31
INSERT INTO t1 VALUES ( '24', '33', '22.0000000000', '22.0000000000', 'Pilves, niiske', '8355711', '12632319');
32
INSERT INTO t1 VALUES ( '26', '33', '31.0000000000', '31.0000000000', 'Selge, kuiv', '16777215', '12632256');
33
INSERT INTO t1 VALUES ( '27', '33', '12.0000000000', '12.0000000000', 'Sajab, niiske', '0', '12632319');
34
INSERT INTO t1 VALUES ( '28', '33', '32.0000000000', '32.0000000000', 'Selge, niiske', '16777215', '12632319');
35
INSERT INTO t1 VALUES ( '31', '33', '11.0000000000', '11.0000000000', 'Sajab, kuiv', '0', '12632256');
36
INSERT INTO t1 VALUES ( '32', '11', '1.0000000000', '1.0000000000', 'Korras', '16777215', '49152');
37
INSERT INTO t1 VALUES ( '34', '21', '134.0000000000', '134.0000000000', 'Hoiatus, M+S!', '255', '13158600');
38
INSERT INTO t1 VALUES ( '38', '21', '132.0000000000', '132.0000000000', 'Hoiatus, niiske!', '9869055', '13158600');
39
INSERT INTO t1 VALUES ( '39', '39', '1.0000000000', '1.0000000000', 'ei saja', '11206570', '16777215');
40
INSERT INTO t1 VALUES ( '44', '39', '4.0000000000', '5.0000000000', 'lumi', '16711680', '16763080');
41
INSERT INTO t1 VALUES ( '45', '12', '0.0000000000', '0.0000000000', '', '16777215', '14474460');
42
INSERT INTO t1 VALUES ( '46', '39', '8.0000000000', '8.0000000000', 'rahe', '9830400', '16763080');
43
INSERT INTO t1 VALUES ( '48', '39', '7.0000000000', '7.0000000000', 'lumetuisk', '7209070', '16763080');
44
INSERT INTO t1 VALUES ( '142', '15', '2.0000000000', '49.0000000000', '', '0', '16777215');
45
INSERT INTO t1 VALUES ( '52', '1', '-4.9000000000', '-0.1000000000', '', '0', '15774720');
46
INSERT INTO t1 VALUES ( '141', '15', '-4.9000000000', '-0.1000000000', '', '0', '15774720');
47
INSERT INTO t1 VALUES ( '55', '8', '0.0000000000', '0.0000000000', '', '0', '16777215');
48
INSERT INTO t1 VALUES ( '56', '8', '0.0100000000', '0.1000000000', '', '0', '16770560');
49
INSERT INTO t1 VALUES ( '57', '8', '0.1100000000', '25.0000000000', '', '0', '15774720');
50
INSERT INTO t1 VALUES ( '58', '2', '90.0000000000', '94.9000000000', '', NULL, '16770560');
51
INSERT INTO t1 VALUES ( '59', '6', '0.0000000000', '360.0000000000', '', NULL, '16777215');
52
INSERT INTO t1 VALUES ( '61', '21', '38.0000000000', '38.0000000000', 'Niiske', '9869055', '16777215');
53
INSERT INTO t1 VALUES ( '62', '38', '500.0000000000', '999.0000000000', '', '0', '16770560');
54
INSERT INTO t1 VALUES ( '63', '38', '1000.0000000000', '2000.0000000000', '', '0', '16777215');
55
INSERT INTO t1 VALUES ( '64', '17', '0.0000000000', '0.0000000000', '', NULL, '16777215');
56
INSERT INTO t1 VALUES ( '65', '17', '0.1000000000', '10.0000000000', '', NULL, '16770560');
57
INSERT INTO t1 VALUES ( '67', '21', '412.0000000000', '412.0000000000', 'Niiske', '9869055', '16777215');
58
INSERT INTO t1 VALUES ( '70', '21', '416.0000000000', '416.0000000000', 'Lumine!', '16711680', '11842740');
59
INSERT INTO t1 VALUES ( '71', '38', '0.0000000000', '499.0000000000', '', NULL, '16711680');
60
INSERT INTO t1 VALUES ( '72', '22', '-49.0000000000', '49.0000000000', '', NULL, '16777215');
61
INSERT INTO t1 VALUES ( '73', '13', '0.0000000000', '9.9000000000', '', NULL, '16777215');
62
INSERT INTO t1 VALUES ( '74', '13', '10.0000000000', '14.9000000000', '', NULL, '16770560');
63
INSERT INTO t1 VALUES ( '75', '7', '0.0000000000', '50.0000000000', '', NULL, '16777215');
64
INSERT INTO t1 VALUES ( '76', '18', '0.0000000000', '0.0000000000', '', NULL, '16777215');
65
INSERT INTO t1 VALUES ( '77', '18', '0.1000000000', '10.0000000000', '', NULL, '16770560');
66
INSERT INTO t1 VALUES ( '78', '19', '300.0000000000', '400.0000000000', '', NULL, '16777215');
67
INSERT INTO t1 VALUES ( '79', '19', '0.0000000000', '299.0000000000', '', NULL, '16770560');
68
INSERT INTO t1 VALUES ( '80', '23', '0.0000000000', '100.0000000000', '', NULL, '16777215');
69
INSERT INTO t1 VALUES ( '81', '24', '0.0000000000', '200.0000000000', '', NULL, '16777215');
70
INSERT INTO t1 VALUES ( '82', '26', '0.0000000000', '0.0000000000', '', NULL, '16777215');
71
INSERT INTO t1 VALUES ( '83', '26', '0.1000000000', '5.0000000000', '', NULL, '16776960');
72
INSERT INTO t1 VALUES ( '84', '21', '422.0000000000', '422.0000000000', 'Niiske', '9869055', '16777215');
73
INSERT INTO t1 VALUES ( '85', '21', '411.0000000000', '411.0000000000', 'Saju hoiat.,kuiv!', '16777215', '13158600');
74
INSERT INTO t1 VALUES ( '144', '16', '-49.0000000000', '-5.0000000000', '', NULL, '16777215');
75
INSERT INTO t1 VALUES ( '88', '16', '2.0000000000', '49.0000000000', '', NULL, '16777215');
76
INSERT INTO t1 VALUES ( '91', '21', '114.0000000000', '114.0000000000', 'Hoiatus, M+S!', '255', '13158600');
77
INSERT INTO t1 VALUES ( '93', '21', '116.0000000000', '116.0000000000', 'Lumine!', '16711680', '11842740');
78
INSERT INTO t1 VALUES ( '94', '21', '414.0000000000', '414.0000000000', 'Hoiatus, M+S!', '255', '13158600');
79
INSERT INTO t1 VALUES ( '98', '21', '28.0000000000', '28.0000000000', 'Niiske ja sool', '9869055', '16777215');
80
INSERT INTO t1 VALUES ( '99', '21', '118.0000000000', '118.0000000000', 'Hoiatus, N+S!', '9869055', '13158600');
81
INSERT INTO t1 VALUES ( '100', '21', '418.0000000000', '418.0000000000', 'Hoiatus, N+S!', '9869055', '13158600');
82
INSERT INTO t1 VALUES ( '102', '21', '428.0000000000', '428.0000000000', 'Hoiatus, N+S!', '9869055', '13158600');
83
INSERT INTO t1 VALUES ( '103', '21', '432.0000000000', '432.0000000000', 'Hoiatus, niiske!', '7895240', '13158600');
84
INSERT INTO t1 VALUES ( '104', '21', '421.0000000000', '421.0000000000', 'Saju hoiat.,kuiv!', '16777215', '13158600');
85
INSERT INTO t1 VALUES ( '106', '21', '438.0000000000', '438.0000000000', 'Hoiatus, N+S!', '9869055', '13158600');
86
INSERT INTO t1 VALUES ( '107', '21', '112.0000000000', '112.0000000000', 'Hoiatus, niiske!', '9869055', '13158600');
87
INSERT INTO t1 VALUES ( '109', '21', '434.0000000000', '434.0000000000', 'Hoiatus, M+S!', '255', '13158600');
88
INSERT INTO t1 VALUES ( '110', '21', '124.0000000000', '124.0000000000', 'Hoiatus, M+S!', '255', '13158600');
89
INSERT INTO t1 VALUES ( '111', '21', '424.0000000000', '424.0000000000', 'Hoiatus, M+S!', '255', '13158600');
90
INSERT INTO t1 VALUES ( '140', '15', '-49.0000000000', '-5.0000000000', '', '0', '16777215');
91
INSERT INTO t1 VALUES ( '114', '21', '18.0000000000', '18.0000000000', 'Niiske ja sool', '9869055', '16777215');
92
INSERT INTO t1 VALUES ( '115', '21', '122.0000000000', '122.0000000000', 'Hoiatus, niiske!', '9869055', '13158600');
93
INSERT INTO t1 VALUES ( '121', '2', '95.0000000000', '100.0000000000', '', NULL, '15774720');
94
INSERT INTO t1 VALUES ( '118', '2', '0.0000000000', '89.9000000000', '', NULL, '16777215');
95
INSERT INTO t1 VALUES ( '119', '21', '16.0000000000', '16.0000000000', 'Lumine!', '16711680', '11842740');
96
INSERT INTO t1 VALUES ( '120', '21', '26.0000000000', '26.0000000000', 'Lumine!', '16711680', '11842740');
97
INSERT INTO t1 VALUES ( '122', '13', '15.0000000000', '50.0000000000', '', NULL, '15774720');
98
INSERT INTO t1 VALUES ( '123', '5', '0.0000000000', '9.9000000000', '', NULL, '16777215');
99
INSERT INTO t1 VALUES ( '124', '5', '10.0000000000', '14.9000000000', '', NULL, '16770560');
100
INSERT INTO t1 VALUES ( '125', '5', '15.0000000000', '50.0000000000', '', NULL, '15774720');
101
INSERT INTO t1 VALUES ( '126', '21', '128.0000000000', '128.0000000000', 'Hoiatus, N+S!', '9869055', '13158600');
102
INSERT INTO t1 VALUES ( '129', '21', '126.0000000000', '126.0000000000', 'Lumine!', '16711680', '11842740');
103
INSERT INTO t1 VALUES ( '131', '21', '316.0000000000', '316.0000000000', 'Lumine!', '16711680', '11842740');
104
INSERT INTO t1 VALUES ( '132', '1', '0.0000000000', '1.9000000000', '', NULL, '16769024');
105
INSERT INTO t1 VALUES ( '134', '3', '-50.0000000000', '50.0000000000', '', NULL, '16777215');
106
INSERT INTO t1 VALUES ( '135', '8', '26.0000000000', '2000.0000000000', '', '9868950', '15774720');
107
INSERT INTO t1 VALUES ( '136', '21', '426.0000000000', '426.0000000000', 'Lumine!', '16711680', '11842740');
108
INSERT INTO t1 VALUES ( '138', '21', '121.0000000000', '121.0000000000', 'Kuiv', '13158600', '16777215');
109
INSERT INTO t1 VALUES ( '139', '21', '326.0000000000', '326.0000000000', 'Lumine!', '16711680', '11842740');
110
INSERT INTO t1 VALUES ( '143', '16', '-4.9000000000', '-0.1000000000', '', NULL, '15774720');
111
INSERT INTO t1 VALUES ( '145', '15', '0.0000000000', '1.9000000000', '', '0', '16769024');
112
INSERT INTO t1 VALUES ( '146', '16', '0.0000000000', '1.9000000000', '', '0', '16769024');
113
select * from t1 where minvalue<=1 and maxvalue>=-1 and datatype_id=16;
114
id datatype_id minvalue maxvalue valuename forecolor backcolor
115
143 16 -4.9000000000 -0.1000000000 NULL 15774720
116
146 16 0.0000000000 1.9000000000 0 16769024
117
select * from t1 where minvalue<=-1 and maxvalue>=-1 and datatype_id=16;
118
id datatype_id minvalue maxvalue valuename forecolor backcolor
119
143 16 -4.9000000000 -0.1000000000 NULL 15774720
121
create table t1 (a decimal(10,2));
122
insert into t1 values ("0.0");
123
insert into t1 values ("-0.0");
124
insert into t1 values ("+0.0");
125
insert into t1 values ("01.0");
126
insert into t1 values ("+01.0");
127
insert into t1 values ("-01.0");
128
insert into t1 values ("-.1");
129
insert into t1 values ("+.1");
130
insert into t1 values (".1");
131
insert into t1 values ("00000000000001");
132
insert into t1 values ("+0000000000001");
133
insert into t1 values ("-0000000000001");
134
insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11");
135
ERROR 22003: Out of range value for column 'a' at row 1
136
insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11");
137
ERROR 22003: Out of range value for column 'a' at row 1
138
insert into t1 values ("1e+1000");
139
ERROR HY000: Incorrect decimal value: '1e+1000' for column 'a' at row 1
140
insert into t1 values ("1e-1000");
141
ERROR HY000: Incorrect decimal value: '1e-1000' for column 'a' at row 1
142
insert into t1 values ("-1e+1000");
143
ERROR HY000: Incorrect decimal value: '-1e+1000' for column 'a' at row 1
144
insert into t1 values ("1e+4294967296");
145
ERROR HY000: Incorrect decimal value: '1e+4294967296' for column 'a' at row 1
146
insert into t1 values ("1e-4294967296");
147
ERROR HY000: Incorrect decimal value: '1e-4294967296' for column 'a' at row 1
148
insert into t1 values ("1e+18446744073709551615");
149
ERROR HY000: Incorrect decimal value: '1e+18446744073709551615' for column 'a' at row 1
150
insert into t1 values ("1e+18446744073709551616");
151
ERROR HY000: Incorrect decimal value: '1e+18446744073709551616' for column 'a' at row 1
152
insert into t1 values ("1e-9223372036854775807");
153
ERROR HY000: Incorrect decimal value: '1e-9223372036854775807' for column 'a' at row 1
154
insert into t1 values ("1e-9223372036854775809");
155
ERROR HY000: Incorrect decimal value: '1e-9223372036854775809' for column 'a' at row 1
156
insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
157
ERROR HY000: Incorrect decimal value: '123.4e' for column 'a' at row 1
173
create table t1 (a decimal(10,2));
174
insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0");
175
insert into t1 values ("-.1"),("+.1"),(".1");
176
insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001");
177
insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11");
178
ERROR 22003: Out of range value for column 'a' at row 1
179
insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11");
180
ERROR 22003: Out of range value for column 'a' at row 1
181
insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000");
182
ERROR HY000: Incorrect decimal value: '1e+1000' for column 'a' at row 1
183
insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
184
ERROR HY000: Incorrect decimal value: '123.4e' for column 'a' at row 1
200
create table t1 (a decimal(10,2));
201
insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0");
202
insert into t1 values ("-.1"),("+.1"),(".1");
203
insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001");
204
insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11");
205
ERROR 22003: Out of range value for column 'a' at row 1
206
insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11");
207
ERROR 22003: Out of range value for column 'a' at row 1
208
insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000");
209
ERROR HY000: Incorrect decimal value: '1e+1000' for column 'a' at row 1
210
insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0");
211
ERROR HY000: Incorrect decimal value: '123.4e' for column 'a' at row 1
227
create table t1 (a decimal(10,2));
228
insert into t1 values (0.0),("-0.0"),(+0.0),(01.0),(+01.0),(-01.0);
229
insert into t1 values (-.1),(+.1),(.1);
230
insert into t1 values (00000000000001),(+0000000000001),(-0000000000001);
231
insert into t1 values (+111111111.11),(111111111.11),(-11111111.11);
232
ERROR 22003: Out of range value for column 'a' at row 1
233
insert into t1 values (-111111111.11),(+1111111111.11),(1111111111.11);
234
ERROR 22003: Out of range value for column 'a' at row 1
235
insert into t1 values (1e+100),(1e-100),(-1e+100);
236
ERROR 22003: Out of range value for column 'a' at row 1
237
insert into t1 values (123.4e0);
238
insert into t1 values (123.4e+2);
239
insert into t1 values (123.4e-2);
240
ERROR 01000: Data truncated for column 'a' at row 1
241
insert into t1 values (123e1);
242
insert into t1 values (123e+0);
243
insert into t1 values (MID("987",1,2));
244
insert into t1 values ("987 ");
245
insert into t1 values ("987.6e+2 ");
268
create table t1 (a decimal);
269
insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+12345678901'),(99999999999999);
270
ERROR 22003: Out of range value for column 'a' at row 1
274
create table t1 (a decimal);
275
insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999);
276
ERROR 22003: Out of range value for column 'a' at row 1
280
create table t1 (a decimal);
281
insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999);
282
ERROR 22003: Out of range value for column 'a' at row 1
286
create table t1 (a decimal);
287
insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+1234567890'),(99999999999999);
288
ERROR 22003: Out of range value for column 'a' at row 1
292
create table t1(a decimal(10,0));
293
insert into t1 values ("1e4294967295");
294
ERROR HY000: Incorrect decimal value: '1e4294967295' for column 'a' at row 1
298
insert into t1 values("1e4294967297");
299
ERROR HY000: Incorrect decimal value: '1e4294967297' for column 'a' at row 1
303
CREATE TABLE t1 (a_dec DECIMAL(-1,0));
304
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '-1,0))' at line 1
305
CREATE TABLE t1 (a_dec DECIMAL(-2,1));
306
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '-2,1))' at line 1
307
CREATE TABLE t1 (a_dec DECIMAL(-1,1));
308
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '-1,1))' at line 1
309
CREATE TABLE t1 (a_dec DECIMAL(0,11));
310
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'a_dec').
311
create table t1(a decimal(7,3));
312
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');
313
ERROR 22003: Out of range value for column 'a' at row 25
317
create table t1(a decimal(7,3));
318
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
ERROR 22003: Out of range value for column 'a' at row 25
323
create table t1(a decimal(7,3));
324
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');
325
ERROR 22003: Out of range value for column 'a' at row 25
331
(EMPNUM CHAR(3) NOT NULL,
334
(EMPNUM CHAR(3) NOT NULL,
336
INSERT INTO t1 VALUES ('E1',40);
337
INSERT INTO t1 VALUES ('E8',NULL);
338
INSERT INTO t2 VALUES ('E1',40);
339
SELECT EMPNUM FROM t1 WHERE HOURS IN (SELECT HOURS FROM t2);
342
SELECT EMPNUM FROM t1 WHERE HOURS IN (SELECT HOURS FROM t1);
346
create table t1 (d decimal(64,0));
347
insert into t1 values (1);
352
create table t1 (d decimal(5));
353
show create table t1;
355
t1 CREATE TABLE `t1` (
356
`d` DECIMAL(5,0) DEFAULT NULL
357
) ENGINE=DEFAULT COLLATE = utf8_general_ci
359
create table t1 (d decimal);
360
show create table t1;
362
t1 CREATE TABLE `t1` (
363
`d` DECIMAL(10,0) DEFAULT NULL
364
) ENGINE=DEFAULT COLLATE = utf8_general_ci
366
create table t1 (d decimal(66,0));
367
ERROR 42000: Too big precision 66 specified for column 'd'. Maximum is 65.
368
CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2));
369
INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
370
(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
371
(2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
372
(4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
373
(5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
374
(6, 0.00, 0.00), (6, -51.40, 0.00);
375
SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1 GROUP BY i HAVING a <> b;
378
SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i
383
create table t1 (c1 varchar(100), c2 longtext);
384
insert into t1 set c1= 'non PS, 1.0 as constant', c2=1.0;
385
insert into t1 set c1='PS, 1.0 as constant ', c2=1.0;
387
insert into t1 set c1='non PS, 1.0 in parameter', c2=@a;
390
non PS, 1.0 as constant 1.0
391
PS, 1.0 as constant 1.0
392
non PS, 1.0 in parameter 1.0
395
strippedproductid char(15) not null default '',
396
zlevelprice decimal(10,2) default null,
397
primary key (strippedproductid)
400
productid char(15) not null default '',
401
zlevelprice char(21) default null,
402
primary key (productid)
404
insert into t1 values ('002trans','49.99');
405
insert into t1 values ('003trans','39.98');
406
insert into t1 values ('004trans','31.18');
407
insert INTO t2 SELECT * FROM t1;
409
productid zlevelprice
414
create table t1 (f1 decimal(5));
415
insert into t1 values (40);
417
select f1 from t1 where f1 in (select f1 from t1);
422
select from_days(s) as date,t
423
from (select 1 as s,'t' as t union select null, null ) as sub1;
424
ERROR HY000: Received an out-of-range argument '1' for function 'from_days'.
426
select from_days(s) as date,t
427
from (select TO_DAYS("0001-01-01") as s,'t' as t union select null, null ) as sub1;
430
qty decimal(16,6) default NULL,
433
INSERT INTO t1 VALUES (1.1325,3);
434
SELECT ROUND(qty,3), dps, ROUND(qty,dps) FROM t1;
435
ROUND(qty,3) dps ROUND(qty,dps)
438
create table t1 (c1 decimal(10,6));
439
insert into t1 (c1) values (9.99e-4);
440
insert into t1 (c1) values (9.98e-4);
441
insert into t1 (c1) values (0.000999);
442
insert into t1 (c1) values (cast(9.99e-4 as decimal(10,6)));
450
SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS '%';
452
0.012345687012345687012345687012345687012345687012345687012345687012345687000000000
453
SELECT MOD(1, .123456789123456789123456789123456789123456789123456789123456789123456789123456789) AS 'MOD()';
455
0.012345687012345687012345687012345687012345687012345687012345687012345687000000000
456
create table t1 (f1 decimal(6,6),f2 decimal(6,6) );
457
insert into t1 values (-0.123456,0.123456);
458
select group_concat(f1),group_concat(f2) from t1;
459
group_concat(f1) group_concat(f2)
463
ua_id decimal(22,0) not null,
464
ua_invited_by_id decimal(22,0) default NULL,
467
insert into t1 values (123, NULL), (456, NULL);
468
this must not produce error 1048:
469
select * from t1 where ua_invited_by_id not in (select ua_id from t1);
470
ua_id ua_invited_by_id
472
DROP TABLE IF EXISTS t3;
473
DROP TABLE IF EXISTS t4;
474
CREATE TABLE t1( a NUMERIC, b INT );
475
INSERT INTO t1 VALUES (123456, 40), (123456, 40);
476
SELECT TRUNCATE( a, b ) AS c FROM t1 ORDER BY c;
480
SELECT ROUND( a, b ) AS c FROM t1 ORDER BY c;
484
SELECT ROUND( a, 100 ) AS c FROM t1 ORDER BY c;
486
123456.000000000000000000000000000000
487
123456.000000000000000000000000000000
488
CREATE TABLE t2( a NUMERIC, b INT );
489
INSERT INTO t2 VALUES (123456, 100);
490
SELECT TRUNCATE( a, b ) AS c FROM t2 ORDER BY c;
493
SELECT ROUND( a, b ) AS c FROM t2 ORDER BY c;
496
CREATE TABLE t3( a DECIMAL, b INT );
497
INSERT INTO t3 VALUES (123456, 40), (123456, 40);
498
SELECT TRUNCATE( a, b ) AS c FROM t3 ORDER BY c;
502
SELECT ROUND( a, b ) AS c FROM t3 ORDER BY c;
506
SELECT ROUND( a, 100 ) AS c FROM t3 ORDER BY c;
508
123456.000000000000000000000000000000
509
123456.000000000000000000000000000000
510
CREATE TABLE t4( a DECIMAL, b INT );
511
INSERT INTO t4 VALUES (123456, 40), (123456, 40);
512
SELECT TRUNCATE( a, b ) AS c FROM t4 ORDER BY c;
516
SELECT ROUND( a, b ) AS c FROM t4 ORDER BY c;
520
SELECT ROUND( a, 100 ) AS c FROM t4 ORDER BY c;
522
123456.000000000000000000000000000000
523
123456.000000000000000000000000000000
525
INSERT INTO t1 VALUES (1234567890, 20);
526
INSERT INTO t1 VALUES (999.99, 5);
527
ERROR 01000: Data truncated for column 'a' at row 1
528
show create table t1;
530
t1 CREATE TABLE `t1` (
531
`a` DECIMAL(10,0) DEFAULT NULL,
533
) ENGINE=DEFAULT COLLATE = utf8_general_ci
534
select round(a,b) as c from t1 order by c;
537
DROP TABLE t1, t2, t3, t4;
538
CREATE TABLE t1( a DECIMAL(4, 3), b INT );
539
INSERT INTO t1 VALUES ( 1, 5 ), ( 2, 4 ), ( 3, 3 ), ( 4, 2 ), ( 5, 1 );
540
SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c;
547
SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c DESC;
554
CREATE TABLE t2 ( a INT, b INT, c DECIMAL(5, 4) );
555
INSERT INTO t2 VALUES ( 0, 1, 1.2345 ), ( 1, 2, 1.2345 ),
556
( 3, 3, 1.2345 ), ( 2, 4, 1.2345 );
557
SELECT a, b, MAX(ROUND(c, a))
566
SELECT a, b, ROUND(c, a)
573
CREATE TABLE t3( a INT, b DECIMAL(6, 3) );
574
INSERT INTO t3 VALUES( 0, 1.5 );
575
SELECT ROUND( b, a ) FROM t3;
578
CREATE TABLE t4( a INT, b DECIMAL( 12, 0) );
579
INSERT INTO t4 VALUES( -9, 1.5e9 );
580
SELECT ROUND( b, a ) FROM t4;
583
CREATE TABLE t5( a INT, b DECIMAL( 13, 12 ) );
584
INSERT INTO t5 VALUES( 0, 1.5 );
585
INSERT INTO t5 VALUES( 9, 1.5e-9 );
586
SELECT ROUND( b, a ) FROM t5;
590
CREATE TABLE t6( a INT );
591
INSERT INTO t6 VALUES( 6 / 8 );
595
SELECT ROUND(20061108085411.000002);
596
ROUND(20061108085411.000002)
598
DROP TABLE t1, t2, t3, t4, t5, t6;