1
drop table if exists t1;
2
select 1.1 IN (1.0, 1.2);
5
select 1.1 IN (1.0, 1.2, 1.1, 1.4, 0.5);
6
1.1 IN (1.0, 1.2, 1.1, 1.4, 0.5)
8
select 1.1 IN (1.0, 1.2, NULL, 1.4, 0.5);
9
1.1 IN (1.0, 1.2, NULL, 1.4, 0.5)
11
select 0.5 IN (1.0, 1.2, NULL, 1.4, 0.5);
12
0.5 IN (1.0, 1.2, NULL, 1.4, 0.5)
14
select 1 IN (1.11, 1.2, 1.1, 1.4, 1, 0.5);
15
1 IN (1.11, 1.2, 1.1, 1.4, 1, 0.5)
17
select 1 IN (1.11, 1.2, 1.1, 1.4, NULL, 0.5);
18
1 IN (1.11, 1.2, 1.1, 1.4, NULL, 0.5)
20
select case 1.0 when 0.1 then "a" when 1.0 then "b" else "c" END;
21
case 1.0 when 0.1 then "a" when 1.0 then "b" else "c" END
23
select case 0.1 when 0.1 then "a" when 1.0 then "b" else "c" END;
24
case 0.1 when 0.1 then "a" when 1.0 then "b" else "c" END
26
select case 1 when 0.1 then "a" when 1.0 then "b" else "c" END;
27
case 1 when 0.1 then "a" when 1.0 then "b" else "c" END
29
select case 1.0 when 0.1 then "a" when 1 then "b" else "c" END;
30
case 1.0 when 0.1 then "a" when 1 then "b" else "c" END
32
select case 1.001 when 0.1 then "a" when 1 then "b" else "c" END;
33
case 1.001 when 0.1 then "a" when 1 then "b" else "c" END
35
create table t1 (a decimal(6,3));
36
insert into t1 values (1.0), (NULL), (0.1);
42
select 0.1 in (1.0, 1.2, 1.1, a, 1.4, 0.5) from t1;
43
0.1 in (1.0, 1.2, 1.1, a, 1.4, 0.5)
48
create table t1 select if(1, 1.1, 1.2), if(0, 1.1, 1.2), if(0.1, 1.1, 1.2), if(0, 1, 1.1), if(0, NULL, 1.2), if(1, 0.22e1, 1.1), if(1E0, 1.1, 1.2);
50
if(1, 1.1, 1.2) if(0, 1.1, 1.2) if(0.1, 1.1, 1.2) if(0, 1, 1.1) if(0, NULL, 1.2) if(1, 0.22e1, 1.1) if(1E0, 1.1, 1.2)
51
1.1 1.2 1.1 1.1 1.2 2.2 1.1
54
t1 CREATE TABLE `t1` (
55
`if(1, 1.1, 1.2)` decimal(2,1) NOT NULL,
56
`if(0, 1.1, 1.2)` decimal(2,1) NOT NULL,
57
`if(0.1, 1.1, 1.2)` decimal(2,1) NOT NULL,
58
`if(0, 1, 1.1)` decimal(2,1) NOT NULL,
59
`if(0, NULL, 1.2)` decimal(2,1) DEFAULT NULL,
60
`if(1, 0.22e1, 1.1)` double NOT NULL,
61
`if(1E0, 1.1, 1.2)` decimal(2,1) NOT NULL
64
create table t1 select nullif(1.1, 1.1), nullif(1.1, 1.2), nullif(1.1, 0.11e1), nullif(1.0, 1), nullif(1, 1.0), nullif(1, 1.1);
66
nullif(1.1, 1.1) nullif(1.1, 1.2) nullif(1.1, 0.11e1) nullif(1.0, 1) nullif(1, 1.0) nullif(1, 1.1)
67
NULL 1.1 NULL NULL NULL 1
70
t1 CREATE TABLE `t1` (
71
`nullif(1.1, 1.1)` decimal(2,1) DEFAULT NULL,
72
`nullif(1.1, 1.2)` decimal(2,1) DEFAULT NULL,
73
`nullif(1.1, 0.11e1)` decimal(2,1) DEFAULT NULL,
74
`nullif(1.0, 1)` decimal(2,1) DEFAULT NULL,
75
`nullif(1, 1.0)` int DEFAULT NULL,
76
`nullif(1, 1.1)` int DEFAULT NULL
79
create table t1 (a decimal(4,2));
80
insert into t1 value (10000), (1.1e10), ("11111"), (100000.1);
81
ERROR 22003: Out of range value for column 'a' at row 1
82
insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
83
ERROR 22003: Out of range value for column 'a' at row 1
87
create table t1 (a decimal(4,2));
88
insert into t1 value (10000), (1.1e10), ("11111"), (100000.1);
89
ERROR 22003: Out of range value for column 'a' at row 1
90
insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
91
ERROR 22003: Out of range value for column 'a' at row 1
95
create table t1 (a bigint);
96
insert into t1 values (18446744073709551615.0);
97
ERROR 22003: Out of range value for column 'a' at row 1
98
insert into t1 values (9223372036854775808.0);
99
ERROR 22003: Out of range value for column 'a' at row 1
100
insert into t1 values (-18446744073709551615.0);
101
ERROR 22003: Out of range value for column 'a' at row 1
105
create table t1 (a bigint);
106
insert into t1 values (9223372036854775807.0);
107
insert into t1 values (9999999999999999999999999.000);
108
ERROR 22003: Out of range value for column 'a' at row 1
109
insert into t1 values (-1.0);
115
create table t1 (a int);
116
insert into t1 values (18446744073709551615.0);
117
ERROR 22003: Out of range value for column 'a' at row 1
118
insert into t1 values (9223372036854775808.0);
119
ERROR 22003: Out of range value for column 'a' at row 1
123
create table t1 select round(15.4,-1), truncate(-5678.123451,-3), abs(-1.1), -(-1.1);
124
show create table t1;
126
t1 CREATE TABLE `t1` (
127
`round(15.4,-1)` decimal(3,0) NOT NULL,
128
`truncate(-5678.123451,-3)` decimal(4,0) NOT NULL,
129
`abs(-1.1)` decimal(3,1) NOT NULL,
130
`-(-1.1)` decimal(2,1) NOT NULL
137
Error 1365 Division by 0
147
create table wl1612_1 (col1 int);
148
insert into wl1612_1 values(10);
149
select * from wl1612_1 where 0.8=0.7+0.1;
152
select 0.07+0.07 from wl1612_1;
155
select 0.07-0.07 from wl1612_1;
158
select 0.07*0.07 from wl1612_1;
161
select 0.07/0.07 from wl1612_1;
165
create table wl1612_2 (col1 decimal(10,2), col2 numeric(10,2));
166
insert into wl1612_2 values(1,1);
167
insert into wl1612_2 values(+1,+1);
168
insert into wl1612_2 values(+01,+01);
169
insert into wl1612_2 values(+001,+001);
170
select col1,count(*) from wl1612_2 group by col1;
173
select col2,count(*) from wl1612_2 group by col2;
177
create table wl1612_3 (col1 decimal(10,2), col2 numeric(10,2));
178
insert into wl1612_3 values('1','1');
179
insert into wl1612_3 values('+1','+1');
180
insert into wl1612_3 values('+01','+01');
181
insert into wl1612_3 values('+001','+001');
182
select col1,count(*) from wl1612_3 group by col1;
185
select col2,count(*) from wl1612_3 group by col2;
192
select mod(234.567,10.555);
195
select mod(-234.567,10.555);
198
select mod(234.567,-10.555);
231
select round(15.1,1);
234
select round(15.4,1);
237
select round(15.5,1);
240
select round(15.6,1);
243
select round(15.9,1);
246
select round(-15.1,1);
249
select round(-15.4,1);
252
select round(-15.5,1);
255
select round(-15.6,1);
258
select round(-15.9,1);
261
select round(15.1,0);
264
select round(15.4,0);
267
select round(15.5,0);
270
select round(15.6,0);
273
select round(15.9,0);
276
select round(-15.1,0);
279
select round(-15.4,0);
282
select round(-15.5,0);
285
select round(-15.6,0);
288
select round(-15.9,0);
291
select round(15.1,-1);
294
select round(15.4,-1);
297
select round(15.5,-1);
300
select round(15.6,-1);
303
select round(15.9,-1);
306
select round(-15.1,-1);
309
select round(-15.4,-1);
312
select round(-15.5,-1);
315
select round(-15.6,-1);
318
select round(-15.91,-1);
321
select truncate(5678.123451,0);
322
truncate(5678.123451,0)
324
select truncate(5678.123451,1);
325
truncate(5678.123451,1)
327
select truncate(5678.123451,2);
328
truncate(5678.123451,2)
330
select truncate(5678.123451,3);
331
truncate(5678.123451,3)
333
select truncate(5678.123451,4);
334
truncate(5678.123451,4)
336
select truncate(5678.123451,5);
337
truncate(5678.123451,5)
339
select truncate(5678.123451,6);
340
truncate(5678.123451,6)
342
select truncate(5678.123451,-1);
343
truncate(5678.123451,-1)
345
select truncate(5678.123451,-2);
346
truncate(5678.123451,-2)
348
select truncate(5678.123451,-3);
349
truncate(5678.123451,-3)
351
select truncate(5678.123451,-4);
352
truncate(5678.123451,-4)
354
select truncate(-5678.123451,0);
355
truncate(-5678.123451,0)
357
select truncate(-5678.123451,1);
358
truncate(-5678.123451,1)
360
select truncate(-5678.123451,2);
361
truncate(-5678.123451,2)
363
select truncate(-5678.123451,3);
364
truncate(-5678.123451,3)
366
select truncate(-5678.123451,4);
367
truncate(-5678.123451,4)
369
select truncate(-5678.123451,5);
370
truncate(-5678.123451,5)
372
select truncate(-5678.123451,6);
373
truncate(-5678.123451,6)
375
select truncate(-5678.123451,-1);
376
truncate(-5678.123451,-1)
378
select truncate(-5678.123451,-2);
379
truncate(-5678.123451,-2)
381
select truncate(-5678.123451,-3);
382
truncate(-5678.123451,-3)
384
select truncate(-5678.123451,-4);
385
truncate(-5678.123451,-4)
387
create table wl1612_4 (col1 int, col2 decimal(30,25), col3 numeric(30,25));
388
insert into wl1612_4 values(1,0.0123456789012345678912345,0.0123456789012345678912345);
389
select col2/9999999999 from wl1612_4 where col1=1;
391
0.00000000000123456789024691358
392
select col3/9999999999 from wl1612_4 where col1=1;
394
0.00000000000123456789024691358
395
select 9999999999/col2 from wl1612_4 where col1=1;
398
select 9999999999/col3 from wl1612_4 where col1=1;
401
select col2*9999999999 from wl1612_4 where col1=1;
403
123456789.0000000000111104321087655
404
select col3*9999999999 from wl1612_4 where col1=1;
406
123456789.0000000000111104321087655
407
insert into wl1612_4 values(2,55555.0123456789012345678912345,55555.0123456789012345678912345);
408
select col2/9999999999 from wl1612_4 where col1=2;
410
0.00000555550123512344024696913
411
select col3/9999999999 from wl1612_4 where col1=2;
413
0.00000555550123512344024696913
414
select 9999999999/col2 from wl1612_4 where col1=2;
417
select 9999999999/col3 from wl1612_4 where col1=2;
420
select col2*9999999999 from wl1612_4 where col1=2;
422
555550123401234.0000000000111104321087655
423
select col3*9999999999 from wl1612_4 where col1=2;
425
555550123401234.0000000000111104321087655
427
select 23.4 + (-41.7), 23.4 - (41.7) = -18.3;
428
23.4 + (-41.7) 23.4 - (41.7) = -18.3
439
select 0.8 = 0.7 + 0.1;
442
drop table if exists t1;
444
Note 1051 Unknown table 't1'
445
create table t1 (col1 decimal(38));
446
insert into t1 values (12345678901234567890123456789012345678);
449
12345678901234567890123456789012345678
451
create table t1 (col1 decimal(31,30));
452
insert into t1 values (0.00000000001);
455
0.000000000010000000000000000000
457
select 7777777777777777777777777777777777777 * 10;
458
7777777777777777777777777777777777777 * 10
459
77777777777777777777777777777777777770
460
select .7777777777777777777777777777777777777 *
462
.7777777777777777777777777777777777777 *
464
777777777777777777.777777777777777777700000000000
465
select .7777777777777777777777777777777777777 - 0.1;
466
.7777777777777777777777777777777777777 - 0.1
467
0.6777777777777777777777777777777777777
468
select .343434343434343434 + .343434343434343434;
469
.343434343434343434 + .343434343434343434
471
select abs(9999999999999999999999);
472
abs(9999999999999999999999)
473
9999999999999999999999
474
select abs(-9999999999999999999999);
475
abs(-9999999999999999999999)
476
9999999999999999999999
477
select ceiling(999999999999999999);
478
ceiling(999999999999999999)
480
select ceiling(99999999999999999999);
481
ceiling(99999999999999999999)
483
select ceiling(9.9999999999999999999);
484
ceiling(9.9999999999999999999)
486
select ceiling(-9.9999999999999999999);
487
ceiling(-9.9999999999999999999)
489
select floor(999999999999999999);
490
floor(999999999999999999)
492
select floor(9999999999999999999999);
493
floor(9999999999999999999999)
494
9999999999999999999999
495
select floor(9.999999999999999999999);
496
floor(9.999999999999999999999)
498
select floor(-9.999999999999999999999);
499
floor(-9.999999999999999999999)
501
select floor(-999999999999999999999.999);
502
floor(-999999999999999999999.999)
503
-1000000000000000000000
504
select ceiling(999999999999999999999.999);
505
ceiling(999999999999999999999.999)
506
1000000000000000000000
507
select 99999999999999999999999999999999999999 mod 3;
508
99999999999999999999999999999999999999 mod 3
510
select round(99999999999999999.999);
511
round(99999999999999999.999)
513
select round(-99999999999999999.999);
514
round(-99999999999999999.999)
516
select round(99999999999999999.999,3);
517
round(99999999999999999.999,3)
518
99999999999999999.999
519
select round(-99999999999999999.999,3);
520
round(-99999999999999999.999,3)
521
-99999999999999999.999
522
select truncate(99999999999999999999999999999999999999,31);
523
truncate(99999999999999999999999999999999999999,31)
524
99999999999999999999999999999999999999.000000000000000000000000000000
525
select truncate(99.999999999999999999999999999999999999,31);
526
truncate(99.999999999999999999999999999999999999,31)
527
99.999999999999999999999999999999
528
select truncate(99999999999999999999999999999999999999,-31);
529
truncate(99999999999999999999999999999999999999,-31)
530
99999990000000000000000000000000000000
531
create table t1 as select 0.5;
532
show create table t1;
534
t1 CREATE TABLE `t1` (
535
`0.5` decimal(2,1) NOT NULL
538
select round(1.5),round(2.5);
539
round(1.5) round(2.5)
551
Error 1365 Division by 0
556
Error 1365 Division by 0
557
CREATE TABLE Sow6_2f (col1 NUMERIC(4,2));
558
INSERT INTO Sow6_2f VALUES (10.55);
559
INSERT INTO Sow6_2f VALUES (10.5555);
560
ERROR 01000: Data truncated for column 'col1' at row 1
561
INSERT INTO Sow6_2f VALUES (-10.55);
562
INSERT INTO Sow6_2f VALUES (-10.5555);
563
ERROR 01000: Data truncated for column 'col1' at row 1
564
INSERT INTO Sow6_2f VALUES (11);
565
INSERT INTO Sow6_2f VALUES (101.55);
566
ERROR 22003: Out of range value for column 'col1' at row 1
567
SELECT MOD(col1,0) FROM Sow6_2f;
573
Error 1365 Division by 0
574
Error 1365 Division by 0
575
Error 1365 Division by 0
576
INSERT INTO Sow6_2f VALUES ('a59b');
577
ERROR HY000: Incorrect decimal value: 'a59b' for column 'col1' at row 1
579
select 10.3330000000000/12.34500000;
580
10.3330000000000/12.34500000
586
Error 1365 Division by 0
587
select 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 as x;
589
99999999999999999999999999999999999999999999999999999999999999999
591
Error 1292 Truncated incorrect DECIMAL value: ''
592
select 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 + 1 as x;
594
100000000000000000000000000000000000000000000000000000000000000000
596
Error 1292 Truncated incorrect DECIMAL value: ''
597
select 0.190287977636363637 + 0.040372670 * 0 - 0;
598
0.190287977636363637 + 0.040372670 * 0 - 0
603
CREATE TABLE t1 (f1 DECIMAL (12,9), f2 DECIMAL(2,2));
604
INSERT INTO t1 VALUES (10.5, 0);
605
UPDATE t1 SET f1 = 4.5;
610
CREATE TABLE t1 (f1 DECIMAL (64,20), f2 DECIMAL(2,2));
611
INSERT INTO t1 VALUES (9999999999999999999999999999999999, 0);
614
9999999999999999999999999999999999.00000000000000000000 0.00
620
Error 1365 Division by 0
624
create table t1( d1 decimal(18), d2 decimal(20), d3 decimal (22));
625
insert into t1 values(1,-1,-1);
627
create table t1 (col1 decimal(5,2), col2 numeric(5,2));
628
insert into t1 values (999.999,999.999);
629
ERROR 22003: Out of range value for column 'col1' at row 1
630
insert into t1 values (-999.999,-999.999);
631
ERROR 22003: Out of range value for column 'col1' at row 1
635
set @sav_dpi= @@div_precision_increment;
636
set @@div_precision_increment=15;
637
create table t1 (col1 int, col2 decimal(30,25), col3 numeric(30,25));
638
insert into t1 values (1,0.0123456789012345678912345,0.0123456789012345678912345);
639
select col2/9999999999 from t1 where col1=1;
641
0.000000000001234567890246913578
642
select 9999999999/col2 from t1 where col1=1;
644
810000007209.000065537105051
645
select 77777777/7777777;
649
set div_precision_increment= @sav_dpi;
650
create table t1 (a decimal(4,2));
651
insert into t1 values (0.00);
652
select * from t1 where a > -0.00;
654
select * from t1 where a = -0.00;
658
create table t1 (col1 bigint default -9223372036854775808);
659
insert into t1 values (default);
664
select cast('1.00000001335143196001808973960578441619873046875E-10' as decimal(30,15));
665
cast('1.00000001335143196001808973960578441619873046875E-10' as decimal(30,15))
667
select ln(14000) c1, convert(ln(14000),decimal(5,3)) c2, cast(ln(14000) as decimal(5,3)) c3;
669
9.5468126085974 9.547 9.547
670
select convert(ln(14000),decimal(2,3)) c1;
671
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column '').
672
select cast(ln(14000) as decimal(2,3)) c1;
673
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column '').
674
create table t1 (sl decimal(70,30));
675
ERROR 42000: Too big precision 70 specified for column 'sl'. Maximum is 65.
676
create table t1 (sl decimal(32,31));
677
ERROR 42000: Too big scale 31 specified for column 'sl'. Maximum is 30.
678
create table t1 (sl decimal(0,38));
679
ERROR 42000: Too big scale 38 specified for column 'sl'. Maximum is 30.
680
create table t1 (sl decimal(0,30));
681
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'sl').
682
create table t1 (sl decimal(5, 5));
683
show create table t1;
685
t1 CREATE TABLE `t1` (
686
`sl` decimal(5,5) DEFAULT NULL
689
create table t1 (sl decimal(65, 30));
690
show create table t1;
692
t1 CREATE TABLE `t1` (
693
`sl` decimal(65,30) DEFAULT NULL
697
f0 decimal (30,30) not null DEFAULT 0,
698
f1 decimal (0,0) not null default 0);
699
show create table t1;
701
t1 CREATE TABLE `t1` (
702
`f0` decimal(30,30) NOT NULL DEFAULT '0.000000000000000000000000000000',
703
`f1` decimal(10,0) NOT NULL DEFAULT '0'
706
create table t (d decimal(0,10));
707
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'd').
711
my_varchar VARCHAR(50),
712
my_decimal DECIMAL(65,30)
714
SHOW CREATE TABLE t1;
716
t1 CREATE TABLE `t1` (
717
`my_float` double DEFAULT NULL,
718
`my_double` double DEFAULT NULL,
719
`my_varchar` varchar(50) DEFAULT NULL,
720
`my_decimal` decimal(65,30) DEFAULT NULL
723
INSERT INTO t1 SET my_float = 1.175494345e-32,
724
my_double = 1.175494345e-32,
725
my_varchar = '1.175494345e-32';
726
INSERT INTO t1 SET my_float = 1.175494345e-31,
727
my_double = 1.175494345e-31,
728
my_varchar = '1.175494345e-31';
729
INSERT INTO t1 SET my_float = 1.175494345e-30,
730
my_double = 1.175494345e-30,
731
my_varchar = '1.175494345e-30';
732
INSERT INTO t1 SET my_float = 1.175494345e-29,
733
my_double = 1.175494345e-29,
734
my_varchar = '1.175494345e-29';
735
INSERT INTO t1 SET my_float = 1.175494345e-28,
736
my_double = 1.175494345e-28,
737
my_varchar = '1.175494345e-28';
738
INSERT INTO t1 SET my_float = 1.175494345e-27,
739
my_double = 1.175494345e-27,
740
my_varchar = '1.175494345e-27';
741
INSERT INTO t1 SET my_float = 1.175494345e-26,
742
my_double = 1.175494345e-26,
743
my_varchar = '1.175494345e-26';
744
INSERT INTO t1 SET my_float = 1.175494345e-25,
745
my_double = 1.175494345e-25,
746
my_varchar = '1.175494345e-25';
747
INSERT INTO t1 SET my_float = 1.175494345e-24,
748
my_double = 1.175494345e-24,
749
my_varchar = '1.175494345e-24';
750
INSERT INTO t1 SET my_float = 1.175494345e-23,
751
my_double = 1.175494345e-23,
752
my_varchar = '1.175494345e-23';
753
INSERT INTO t1 SET my_float = 1.175494345e-22,
754
my_double = 1.175494345e-22,
755
my_varchar = '1.175494345e-22';
756
INSERT INTO t1 SET my_float = 1.175494345e-21,
757
my_double = 1.175494345e-21,
758
my_varchar = '1.175494345e-21';
759
INSERT INTO t1 SET my_float = 1.175494345e-20,
760
my_double = 1.175494345e-20,
761
my_varchar = '1.175494345e-20';
762
INSERT INTO t1 SET my_float = 1.175494345e-19,
763
my_double = 1.175494345e-19,
764
my_varchar = '1.175494345e-19';
765
INSERT INTO t1 SET my_float = 1.175494345e-18,
766
my_double = 1.175494345e-18,
767
my_varchar = '1.175494345e-18';
768
INSERT INTO t1 SET my_float = 1.175494345e-17,
769
my_double = 1.175494345e-17,
770
my_varchar = '1.175494345e-17';
771
INSERT INTO t1 SET my_float = 1.175494345e-16,
772
my_double = 1.175494345e-16,
773
my_varchar = '1.175494345e-16';
774
INSERT INTO t1 SET my_float = 1.175494345e-15,
775
my_double = 1.175494345e-15,
776
my_varchar = '1.175494345e-15';
777
INSERT INTO t1 SET my_float = 1.175494345e-14,
778
my_double = 1.175494345e-14,
779
my_varchar = '1.175494345e-14';
780
INSERT INTO t1 SET my_float = 1.175494345e-13,
781
my_double = 1.175494345e-13,
782
my_varchar = '1.175494345e-13';
783
INSERT INTO t1 SET my_float = 1.175494345e-12,
784
my_double = 1.175494345e-12,
785
my_varchar = '1.175494345e-12';
786
INSERT INTO t1 SET my_float = 1.175494345e-11,
787
my_double = 1.175494345e-11,
788
my_varchar = '1.175494345e-11';
789
INSERT INTO t1 SET my_float = 1.175494345e-10,
790
my_double = 1.175494345e-10,
791
my_varchar = '1.175494345e-10';
792
INSERT INTO t1 SET my_float = 1.175494345e-9,
793
my_double = 1.175494345e-9,
794
my_varchar = '1.175494345e-9';
795
INSERT INTO t1 SET my_float = 1.175494345e-8,
796
my_double = 1.175494345e-8,
797
my_varchar = '1.175494345e-8';
798
INSERT INTO t1 SET my_float = 1.175494345e-7,
799
my_double = 1.175494345e-7,
800
my_varchar = '1.175494345e-7';
801
INSERT INTO t1 SET my_float = 1.175494345e-6,
802
my_double = 1.175494345e-6,
803
my_varchar = '1.175494345e-6';
804
INSERT INTO t1 SET my_float = 1.175494345e-5,
805
my_double = 1.175494345e-5,
806
my_varchar = '1.175494345e-5';
807
INSERT INTO t1 SET my_float = 1.175494345e-4,
808
my_double = 1.175494345e-4,
809
my_varchar = '1.175494345e-4';
810
INSERT INTO t1 SET my_float = 1.175494345e-3,
811
my_double = 1.175494345e-3,
812
my_varchar = '1.175494345e-3';
813
INSERT INTO t1 SET my_float = 1.175494345e-2,
814
my_double = 1.175494345e-2,
815
my_varchar = '1.175494345e-2';
816
INSERT INTO t1 SET my_float = 1.175494345e-1,
817
my_double = 1.175494345e-1,
818
my_varchar = '1.175494345e-1';
820
SELECT my_float, my_double, my_varchar FROM t1;
821
my_float my_double my_varchar
822
1.175494345e-32 1.175494345e-32 1.175494345e-32
823
1.175494345e-31 1.175494345e-31 1.175494345e-31
824
1.175494345e-30 1.175494345e-30 1.175494345e-30
825
1.175494345e-29 1.175494345e-29 1.175494345e-29
826
1.175494345e-28 1.175494345e-28 1.175494345e-28
827
1.175494345e-27 1.175494345e-27 1.175494345e-27
828
1.175494345e-26 1.175494345e-26 1.175494345e-26
829
1.175494345e-25 1.175494345e-25 1.175494345e-25
830
1.175494345e-24 1.175494345e-24 1.175494345e-24
831
1.175494345e-23 1.175494345e-23 1.175494345e-23
832
1.175494345e-22 1.175494345e-22 1.175494345e-22
833
1.175494345e-21 1.175494345e-21 1.175494345e-21
834
1.175494345e-20 1.175494345e-20 1.175494345e-20
835
1.175494345e-19 1.175494345e-19 1.175494345e-19
836
1.175494345e-18 1.175494345e-18 1.175494345e-18
837
1.175494345e-17 1.175494345e-17 1.175494345e-17
838
1.175494345e-16 1.175494345e-16 1.175494345e-16
839
0.000000000000001175494345 0.000000000000001175494345 1.175494345e-15
840
0.00000000000001175494345 0.00000000000001175494345 1.175494345e-14
841
0.0000000000001175494345 0.0000000000001175494345 1.175494345e-13
842
0.000000000001175494345 0.000000000001175494345 1.175494345e-12
843
0.00000000001175494345 0.00000000001175494345 1.175494345e-11
844
0.0000000001175494345 0.0000000001175494345 1.175494345e-10
845
0.000000001175494345 0.000000001175494345 1.175494345e-9
846
0.00000001175494345 0.00000001175494345 1.175494345e-8
847
0.0000001175494345 0.0000001175494345 1.175494345e-7
848
0.000001175494345 0.000001175494345 1.175494345e-6
849
0.00001175494345 0.00001175494345 1.175494345e-5
850
0.0001175494345 0.0001175494345 1.175494345e-4
851
0.001175494345 0.001175494345 1.175494345e-3
852
0.01175494345 0.01175494345 1.175494345e-2
853
0.1175494345 0.1175494345 1.175494345e-1
854
SELECT CAST(my_float AS DECIMAL(65,30)), my_float FROM t1;
855
CAST(my_float AS DECIMAL(65,30)) my_float
856
0.000000000000000000000000000000 1.175494345e-32
857
0.000000000000000000000000000000 1.175494345e-31
858
0.000000000000000000000000000001 1.175494345e-30
859
0.000000000000000000000000000012 1.175494345e-29
860
0.000000000000000000000000000118 1.175494345e-28
861
0.000000000000000000000000001175 1.175494345e-27
862
0.000000000000000000000000011755 1.175494345e-26
863
0.000000000000000000000000117549 1.175494345e-25
864
0.000000000000000000000001175494 1.175494345e-24
865
0.000000000000000000000011754943 1.175494345e-23
866
0.000000000000000000000117549435 1.175494345e-22
867
0.000000000000000000001175494345 1.175494345e-21
868
0.000000000000000000011754943450 1.175494345e-20
869
0.000000000000000000117549434500 1.175494345e-19
870
0.000000000000000001175494345000 1.175494345e-18
871
0.000000000000000011754943450000 1.175494345e-17
872
0.000000000000000117549434500000 1.175494345e-16
873
0.000000000000001175494345000000 0.000000000000001175494345
874
0.000000000000011754943450000000 0.00000000000001175494345
875
0.000000000000117549434500000000 0.0000000000001175494345
876
0.000000000001175494345000000000 0.000000000001175494345
877
0.000000000011754943450000000000 0.00000000001175494345
878
0.000000000117549434500000000000 0.0000000001175494345
879
0.000000001175494345000000000000 0.000000001175494345
880
0.000000011754943450000000000000 0.00000001175494345
881
0.000000117549434500000000000000 0.0000001175494345
882
0.000001175494345000000000000000 0.000001175494345
883
0.000011754943450000000000000000 0.00001175494345
884
0.000117549434500000000000000000 0.0001175494345
885
0.001175494345000000000000000000 0.001175494345
886
0.011754943450000000000000000000 0.01175494345
887
0.117549434500000000000000000000 0.1175494345
888
SELECT CAST(my_double AS DECIMAL(65,30)), my_double FROM t1;
889
CAST(my_double AS DECIMAL(65,30)) my_double
890
0.000000000000000000000000000000 1.175494345e-32
891
0.000000000000000000000000000000 1.175494345e-31
892
0.000000000000000000000000000001 1.175494345e-30
893
0.000000000000000000000000000012 1.175494345e-29
894
0.000000000000000000000000000118 1.175494345e-28
895
0.000000000000000000000000001175 1.175494345e-27
896
0.000000000000000000000000011755 1.175494345e-26
897
0.000000000000000000000000117549 1.175494345e-25
898
0.000000000000000000000001175494 1.175494345e-24
899
0.000000000000000000000011754943 1.175494345e-23
900
0.000000000000000000000117549435 1.175494345e-22
901
0.000000000000000000001175494345 1.175494345e-21
902
0.000000000000000000011754943450 1.175494345e-20
903
0.000000000000000000117549434500 1.175494345e-19
904
0.000000000000000001175494345000 1.175494345e-18
905
0.000000000000000011754943450000 1.175494345e-17
906
0.000000000000000117549434500000 1.175494345e-16
907
0.000000000000001175494345000000 0.000000000000001175494345
908
0.000000000000011754943450000000 0.00000000000001175494345
909
0.000000000000117549434500000000 0.0000000000001175494345
910
0.000000000001175494345000000000 0.000000000001175494345
911
0.000000000011754943450000000000 0.00000000001175494345
912
0.000000000117549434500000000000 0.0000000001175494345
913
0.000000001175494345000000000000 0.000000001175494345
914
0.000000011754943450000000000000 0.00000001175494345
915
0.000000117549434500000000000000 0.0000001175494345
916
0.000001175494345000000000000000 0.000001175494345
917
0.000011754943450000000000000000 0.00001175494345
918
0.000117549434500000000000000000 0.0001175494345
919
0.001175494345000000000000000000 0.001175494345
920
0.011754943450000000000000000000 0.01175494345
921
0.117549434500000000000000000000 0.1175494345
922
SELECT CAST(my_varchar AS DECIMAL(65,30)), my_varchar FROM t1;
923
CAST(my_varchar AS DECIMAL(65,30)) my_varchar
924
0.000000000000000000000000000000 1.175494345e-32
925
0.000000000000000000000000000000 1.175494345e-31
926
0.000000000000000000000000000001 1.175494345e-30
927
0.000000000000000000000000000012 1.175494345e-29
928
0.000000000000000000000000000118 1.175494345e-28
929
0.000000000000000000000000001175 1.175494345e-27
930
0.000000000000000000000000011755 1.175494345e-26
931
0.000000000000000000000000117549 1.175494345e-25
932
0.000000000000000000000001175494 1.175494345e-24
933
0.000000000000000000000011754943 1.175494345e-23
934
0.000000000000000000000117549435 1.175494345e-22
935
0.000000000000000000001175494345 1.175494345e-21
936
0.000000000000000000011754943450 1.175494345e-20
937
0.000000000000000000117549434500 1.175494345e-19
938
0.000000000000000001175494345000 1.175494345e-18
939
0.000000000000000011754943450000 1.175494345e-17
940
0.000000000000000117549434500000 1.175494345e-16
941
0.000000000000001175494345000000 1.175494345e-15
942
0.000000000000011754943450000000 1.175494345e-14
943
0.000000000000117549434500000000 1.175494345e-13
944
0.000000000001175494345000000000 1.175494345e-12
945
0.000000000011754943450000000000 1.175494345e-11
946
0.000000000117549434500000000000 1.175494345e-10
947
0.000000001175494345000000000000 1.175494345e-9
948
0.000000011754943450000000000000 1.175494345e-8
949
0.000000117549434500000000000000 1.175494345e-7
950
0.000001175494345000000000000000 1.175494345e-6
951
0.000011754943450000000000000000 1.175494345e-5
952
0.000117549434500000000000000000 1.175494345e-4
953
0.001175494345000000000000000000 1.175494345e-3
954
0.011754943450000000000000000000 1.175494345e-2
955
0.117549434500000000000000000000 1.175494345e-1
956
UPDATE t1 SET my_decimal = my_float;
957
ERROR 01000: Data truncated for column 'my_decimal' at row 1
958
SELECT my_decimal, my_float FROM t1;
977
NULL 0.000000000000001175494345
978
NULL 0.00000000000001175494345
979
NULL 0.0000000000001175494345
980
NULL 0.000000000001175494345
981
NULL 0.00000000001175494345
982
NULL 0.0000000001175494345
983
NULL 0.000000001175494345
984
NULL 0.00000001175494345
985
NULL 0.0000001175494345
986
NULL 0.000001175494345
987
NULL 0.00001175494345
992
UPDATE t1 SET my_decimal = my_double;
993
ERROR 01000: Data truncated for column 'my_decimal' at row 1
994
SELECT my_decimal, my_double FROM t1;
1000
NULL 1.175494345e-28
1001
NULL 1.175494345e-27
1002
NULL 1.175494345e-26
1003
NULL 1.175494345e-25
1004
NULL 1.175494345e-24
1005
NULL 1.175494345e-23
1006
NULL 1.175494345e-22
1007
NULL 1.175494345e-21
1008
NULL 1.175494345e-20
1009
NULL 1.175494345e-19
1010
NULL 1.175494345e-18
1011
NULL 1.175494345e-17
1012
NULL 1.175494345e-16
1013
NULL 0.000000000000001175494345
1014
NULL 0.00000000000001175494345
1015
NULL 0.0000000000001175494345
1016
NULL 0.000000000001175494345
1017
NULL 0.00000000001175494345
1018
NULL 0.0000000001175494345
1019
NULL 0.000000001175494345
1020
NULL 0.00000001175494345
1021
NULL 0.0000001175494345
1022
NULL 0.000001175494345
1023
NULL 0.00001175494345
1024
NULL 0.0001175494345
1028
UPDATE t1 SET my_decimal = my_varchar;
1029
ERROR 01000: Data truncated for column 'my_decimal' at row 1
1030
SELECT my_decimal, my_varchar FROM t1;
1031
my_decimal my_varchar
1032
NULL 1.175494345e-32
1033
NULL 1.175494345e-31
1034
NULL 1.175494345e-30
1035
NULL 1.175494345e-29
1036
NULL 1.175494345e-28
1037
NULL 1.175494345e-27
1038
NULL 1.175494345e-26
1039
NULL 1.175494345e-25
1040
NULL 1.175494345e-24
1041
NULL 1.175494345e-23
1042
NULL 1.175494345e-22
1043
NULL 1.175494345e-21
1044
NULL 1.175494345e-20
1045
NULL 1.175494345e-19
1046
NULL 1.175494345e-18
1047
NULL 1.175494345e-17
1048
NULL 1.175494345e-16
1049
NULL 1.175494345e-15
1050
NULL 1.175494345e-14
1051
NULL 1.175494345e-13
1052
NULL 1.175494345e-12
1053
NULL 1.175494345e-11
1054
NULL 1.175494345e-10
1065
create table t1 (c1 decimal(64));
1066
insert into t1 values(
1067
89000000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
1068
ERROR 22003: Out of range value for column 'c1' at row 1
1069
insert into t1 values(
1070
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 *
1071
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999);
1072
ERROR 22007: Truncated incorrect DECIMAL value: ''
1073
insert into t1 values(1e100);
1074
ERROR 22003: Out of range value for column 'c1' at row 1
1078
create table t1(a decimal(7,2));
1079
insert into t1 values(123.12);
1083
alter table t1 modify a decimal(10,2);
1088
create table t1 (i int, j int);
1089
insert into t1 values (1,1), (1,2), (2,3), (2,4);
1090
select i, count(distinct j) from t1 group by i;
1094
select i+0.0 as i2, count(distinct j) from t1 group by i2;
1095
i2 count(distinct j)
1099
create table t1(f1 decimal(20,6));
1100
insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond);
1103
20101112000000.000014
1105
create table t1(a decimal(18));
1106
insert into t1 values(123456789012345678);
1107
alter table t1 modify column a decimal(19);
1112
select cast(11.1234 as DECIMAL(3,2));
1113
cast(11.1234 as DECIMAL(3,2))
1116
Error 1264 Out of range value for column 'cast(11.1234 as DECIMAL(3,2))' at row 1
1117
select * from (select cast(11.1234 as DECIMAL(3,2))) t;
1118
cast(11.1234 as DECIMAL(3,2))
1121
Error 1264 Out of range value for column 'cast(11.1234 as DECIMAL(3,2))' at row 1
1122
select cast(a as DECIMAL(3,2))
1123
from (select 11.1233 as a
1124
UNION select 11.1234
1125
UNION select 12.1234
1127
cast(a as DECIMAL(3,2))
1132
Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
1133
Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
1134
Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
1135
select cast(a as DECIMAL(3,2)), count(*)
1136
from (select 11.1233 as a
1137
UNION select 11.1234
1138
UNION select 12.1234
1140
cast(a as DECIMAL(3,2)) count(*)
1143
Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
1144
Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
1145
Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
1146
Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
1147
create table t1 (s varchar(100));
1148
insert into t1 values (0.00000000010000000000000000364321973154977415791655470655996396089904010295867919921875);
1150
SELECT 1.000000000000 * 99.999999999998 / 100 a,1.000000000000 * (99.999999999998 / 100) b;
1152
0.9999999999999800000000000000 0.9999999999999800000000000000
1153
SELECT CAST(1 AS decimal(65,10));
1154
CAST(1 AS decimal(65,10))
1156
SELECT CAST(1 AS decimal(66,10));
1157
ERROR 42000: Too big precision 66 specified for column '1'. Maximum is 65.
1158
SELECT CAST(1 AS decimal(65,30));
1159
CAST(1 AS decimal(65,30))
1160
1.000000000000000000000000000000
1161
SELECT CAST(1 AS decimal(65,31));
1162
ERROR 42000: Too big scale 31 specified for column '1'. Maximum is 30.
1163
CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL);
1164
INSERT INTO t1 VALUES (3,30), (1,10), (2,10);
1165
SELECT a+CAST(1 AS decimal(65,30)) AS aa, SUM(b) FROM t1 GROUP BY aa;
1167
2.000000000000000000000000000000 10
1168
3.000000000000000000000000000000 10
1169
4.000000000000000000000000000000 30
1170
SELECT a+CAST(1 AS decimal(65,31)) AS aa, SUM(b) FROM t1 GROUP BY aa;
1171
ERROR 42000: Too big scale 31 specified for column '1'. Maximum is 30.
1173
CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL);
1174
INSERT INTO t1 VALUES (3,30), (1,10), (2,10);
1175
SET @a= CAST(1 AS decimal);
1176
SELECT 1 FROM t1 GROUP BY @b := @a, @b;
1181
CREATE TABLE t1 SELECT 0.123456789012345678901234567890123456 AS f1;
1182
ERROR 01000: Data truncated for column 'f1' at row 1
1183
CREATE TABLE t1 SELECT 123451234512345123451234512345123451234512345.678906789067890678906789067890678906789067890 AS f1;
1184
ERROR 22003: Out of range value for column 'f1' at row 1
1186
select cast(143.481 as decimal(4,1));
1187
cast(143.481 as decimal(4,1))
1189
select cast(143.481 as decimal(4,0));
1190
cast(143.481 as decimal(4,0))
1192
select cast(143.481 as decimal(2,1));
1193
cast(143.481 as decimal(2,1))
1196
Error 1264 Out of range value for column 'cast(143.481 as decimal(2,1))' at row 1
1197
select cast(-3.4 as decimal(2,1));
1198
cast(-3.4 as decimal(2,1))
1200
select cast(99.6 as decimal(2,0));
1201
cast(99.6 as decimal(2,0))
1204
Error 1264 Out of range value for column 'cast(99.6 as decimal(2,0))' at row 1
1205
select cast(-13.4 as decimal(2,1));
1206
cast(-13.4 as decimal(2,1))
1209
Error 1264 Out of range value for column 'cast(-13.4 as decimal(2,1))' at row 1
1210
select cast(98.6 as decimal(2,0));
1211
cast(98.6 as decimal(2,0))
1213
create table t1 (f1 decimal not null default 17.49);
1214
ERROR 42000: Invalid default value for 'f1'