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 DEFAULT '0.0',
56
`if(0, 1.1, 1.2)` decimal(2,1) NOT NULL DEFAULT '0.0',
57
`if(0.1, 1.1, 1.2)` decimal(2,1) NOT NULL DEFAULT '0.0',
58
`if(0, 1, 1.1)` decimal(2,1) NOT NULL DEFAULT '0.0',
59
`if(0, NULL, 1.2)` decimal(2,1) DEFAULT NULL,
60
`if(1, 0.22e1, 1.1)` double NOT NULL DEFAULT '0',
61
`if(1E0, 1.1, 1.2)` decimal(2,1) NOT NULL DEFAULT '0.0'
62
) ENGINE=MyISAM DEFAULT CHARSET=latin1
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(1) DEFAULT NULL,
76
`nullif(1, 1.1)` int(1) DEFAULT NULL
77
) ENGINE=MyISAM DEFAULT CHARSET=latin1
79
create table t1 (a decimal(4,2));
80
insert into t1 value (10000), (1.1e10), ("11111"), (100000.1);
82
Warning 1264 Out of range value for column 'a' at row 1
83
Warning 1264 Out of range value for column 'a' at row 2
84
Warning 1264 Out of range value for column 'a' at row 3
85
Warning 1264 Out of range value for column 'a' at row 4
86
insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
88
Warning 1264 Out of range value for column 'a' at row 1
89
Warning 1264 Out of range value for column 'a' at row 2
90
Warning 1264 Out of range value for column 'a' at row 3
91
Warning 1264 Out of range value for column 'a' at row 4
103
create table t1 (a decimal(4,2) unsigned);
104
insert into t1 value (10000), (1.1e10), ("11111"), (100000.1);
106
Warning 1264 Out of range value for column 'a' at row 1
107
Warning 1264 Out of range value for column 'a' at row 2
108
Warning 1264 Out of range value for column 'a' at row 3
109
Warning 1264 Out of range value for column 'a' at row 4
110
insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
112
Warning 1264 Out of range value for column 'a' at row 1
113
Warning 1264 Out of range value for column 'a' at row 2
114
Warning 1264 Out of range value for column 'a' at row 3
115
Warning 1264 Out of range value for column 'a' at row 4
127
create table t1 (a bigint);
128
insert into t1 values (18446744073709551615.0);
130
Warning 1264 Out of range value for column 'a' at row 1
131
insert into t1 values (9223372036854775808.0);
133
Warning 1264 Out of range value for column 'a' at row 1
134
insert into t1 values (-18446744073709551615.0);
136
Warning 1264 Out of range value for column 'a' at row 1
143
create table t1 (a bigint unsigned);
144
insert into t1 values (18446744073709551615.0);
145
insert into t1 values (9223372036854775808.0);
146
insert into t1 values (9999999999999999999999999.000);
148
Warning 1264 Out of range value for column 'a' at row 1
149
insert into t1 values (-1.0);
151
Warning 1264 Out of range value for column 'a' at row 1
159
create table t1 (a tinyint);
160
insert into t1 values (18446744073709551615.0);
162
Warning 1264 Out of range value for column 'a' at row 1
163
Warning 1264 Out of range value for column 'a' at row 1
164
insert into t1 values (9223372036854775808.0);
166
Warning 1264 Out of range value for column 'a' at row 1
167
Warning 1264 Out of range value for column 'a' at row 1
173
create table t1 select round(15.4,-1), truncate(-5678.123451,-3), abs(-1.1), -(-1.1);
174
show create table t1;
176
t1 CREATE TABLE `t1` (
177
`round(15.4,-1)` decimal(3,0) NOT NULL DEFAULT '0',
178
`truncate(-5678.123451,-3)` decimal(4,0) NOT NULL DEFAULT '0',
179
`abs(-1.1)` decimal(3,1) NOT NULL DEFAULT '0.0',
180
`-(-1.1)` decimal(2,1) NOT NULL DEFAULT '0.0'
181
) ENGINE=MyISAM DEFAULT CHARSET=latin1
183
set session sql_mode='traditional';
188
Error 1365 Division by 0
189
create table wl1612 (col1 int, col2 decimal(38,10), col3 numeric(38,10));
190
insert into wl1612 values(1,12345678901234567890.1234567890,12345678901234567890.1234567890);
191
select * from wl1612;
193
1 12345678901234567890.1234567890 12345678901234567890.1234567890
194
insert into wl1612 values(2,01234567890123456789.0123456789,01234567890123456789.0123456789);
195
select * from wl1612 where col1=2;
197
2 1234567890123456789.0123456789 1234567890123456789.0123456789
198
insert into wl1612 values(3,1234567890123456789012345678.0123456789,1234567890123456789012345678.0123456789);
199
select * from wl1612 where col1=3;
201
3 1234567890123456789012345678.0123456789 1234567890123456789012345678.0123456789
202
select col1/0 from wl1612;
208
Error 1365 Division by 0
209
Error 1365 Division by 0
210
Error 1365 Division by 0
211
select col2/0 from wl1612;
217
Error 1365 Division by 0
218
Error 1365 Division by 0
219
Error 1365 Division by 0
220
select col3/0 from wl1612;
226
Error 1365 Division by 0
227
Error 1365 Division by 0
228
Error 1365 Division by 0
229
insert into wl1612 values(5,5000.0005,5000.0005);
230
insert into wl1612 values(6,5000.0005,5000.0005);
231
select sum(col2),sum(col3) from wl1612;
233
1234567903703703580370380357.1491481468 1234567903703703580370380357.1491481468
234
insert into wl1612 values(7,500000.000005,500000.000005);
235
insert into wl1612 values(8,500000.000005,500000.000005);
236
select sum(col2),sum(col3) from wl1612 where col1>4;
238
1010000.0010100000 1010000.0010100000
239
insert into wl1612 (col1, col2) values(9,1.01234567891);
241
Note 1265 Data truncated for column 'col2' at row 1
242
insert into wl1612 (col1, col2) values(10,1.01234567894);
244
Note 1265 Data truncated for column 'col2' at row 1
245
insert into wl1612 (col1, col2) values(11,1.01234567895);
247
Note 1265 Data truncated for column 'col2' at row 1
248
insert into wl1612 (col1, col2) values(12,1.01234567896);
250
Note 1265 Data truncated for column 'col2' at row 1
251
select col1,col2 from wl1612 where col1>8;
257
insert into wl1612 (col1, col3) values(13,1.01234567891);
259
Note 1265 Data truncated for column 'col3' at row 1
260
insert into wl1612 (col1, col3) values(14,1.01234567894);
262
Note 1265 Data truncated for column 'col3' at row 1
263
insert into wl1612 (col1, col3) values(15,1.01234567895);
265
Note 1265 Data truncated for column 'col3' at row 1
266
insert into wl1612 (col1, col3) values(16,1.01234567896);
268
Note 1265 Data truncated for column 'col3' at row 1
269
select col1,col3 from wl1612 where col1>12;
275
select col1 from wl1612 where col1>4 and col2=1.01234567891;
277
select col1 from wl1612 where col1>4 and col2=1.0123456789;
281
select col1 from wl1612 where col1>4 and col2<>1.0123456789;
289
select col1 from wl1612 where col1>4 and col2<1.0123456789;
291
select col1 from wl1612 where col1>4 and col2<=1.0123456789;
295
select col1 from wl1612 where col1>4 and col2>1.0123456789;
303
select col1 from wl1612 where col1>4 and col2>=1.0123456789;
313
select col1 from wl1612 where col1>4 and col2=1.012345679;
317
select col1 from wl1612 where col1>4 and col2<>1.012345679;
325
select col1 from wl1612 where col1>4 and col3=1.01234567891;
327
select col1 from wl1612 where col1>4 and col3=1.0123456789;
331
select col1 from wl1612 where col1>4 and col3<>1.0123456789;
339
select col1 from wl1612 where col1>4 and col3<1.0123456789;
341
select col1 from wl1612 where col1>4 and col3<=1.0123456789;
345
select col1 from wl1612 where col1>4 and col3>1.0123456789;
353
select col1 from wl1612 where col1>4 and col3>=1.0123456789;
363
select col1 from wl1612 where col1>4 and col3=1.012345679;
367
select col1 from wl1612 where col1>4 and col3<>1.012345679;
385
create table wl1612_1 (col1 int);
386
insert into wl1612_1 values(10);
387
select * from wl1612_1 where 0.8=0.7+0.1;
390
select 0.07+0.07 from wl1612_1;
393
select 0.07-0.07 from wl1612_1;
396
select 0.07*0.07 from wl1612_1;
399
select 0.07/0.07 from wl1612_1;
403
create table wl1612_2 (col1 decimal(10,2), col2 numeric(10,2));
404
insert into wl1612_2 values(1,1);
405
insert into wl1612_2 values(+1,+1);
406
insert into wl1612_2 values(+01,+01);
407
insert into wl1612_2 values(+001,+001);
408
select col1,count(*) from wl1612_2 group by col1;
411
select col2,count(*) from wl1612_2 group by col2;
415
create table wl1612_3 (col1 decimal(10,2), col2 numeric(10,2));
416
insert into wl1612_3 values('1','1');
417
insert into wl1612_3 values('+1','+1');
418
insert into wl1612_3 values('+01','+01');
419
insert into wl1612_3 values('+001','+001');
420
select col1,count(*) from wl1612_3 group by col1;
423
select col2,count(*) from wl1612_3 group by col2;
430
select mod(234.567,10.555);
433
select mod(-234.567,10.555);
436
select mod(234.567,-10.555);
469
select round(15.1,1);
472
select round(15.4,1);
475
select round(15.5,1);
478
select round(15.6,1);
481
select round(15.9,1);
484
select round(-15.1,1);
487
select round(-15.4,1);
490
select round(-15.5,1);
493
select round(-15.6,1);
496
select round(-15.9,1);
499
select round(15.1,0);
502
select round(15.4,0);
505
select round(15.5,0);
508
select round(15.6,0);
511
select round(15.9,0);
514
select round(-15.1,0);
517
select round(-15.4,0);
520
select round(-15.5,0);
523
select round(-15.6,0);
526
select round(-15.9,0);
529
select round(15.1,-1);
532
select round(15.4,-1);
535
select round(15.5,-1);
538
select round(15.6,-1);
541
select round(15.9,-1);
544
select round(-15.1,-1);
547
select round(-15.4,-1);
550
select round(-15.5,-1);
553
select round(-15.6,-1);
556
select round(-15.91,-1);
559
select truncate(5678.123451,0);
560
truncate(5678.123451,0)
562
select truncate(5678.123451,1);
563
truncate(5678.123451,1)
565
select truncate(5678.123451,2);
566
truncate(5678.123451,2)
568
select truncate(5678.123451,3);
569
truncate(5678.123451,3)
571
select truncate(5678.123451,4);
572
truncate(5678.123451,4)
574
select truncate(5678.123451,5);
575
truncate(5678.123451,5)
577
select truncate(5678.123451,6);
578
truncate(5678.123451,6)
580
select truncate(5678.123451,-1);
581
truncate(5678.123451,-1)
583
select truncate(5678.123451,-2);
584
truncate(5678.123451,-2)
586
select truncate(5678.123451,-3);
587
truncate(5678.123451,-3)
589
select truncate(5678.123451,-4);
590
truncate(5678.123451,-4)
592
select truncate(-5678.123451,0);
593
truncate(-5678.123451,0)
595
select truncate(-5678.123451,1);
596
truncate(-5678.123451,1)
598
select truncate(-5678.123451,2);
599
truncate(-5678.123451,2)
601
select truncate(-5678.123451,3);
602
truncate(-5678.123451,3)
604
select truncate(-5678.123451,4);
605
truncate(-5678.123451,4)
607
select truncate(-5678.123451,5);
608
truncate(-5678.123451,5)
610
select truncate(-5678.123451,6);
611
truncate(-5678.123451,6)
613
select truncate(-5678.123451,-1);
614
truncate(-5678.123451,-1)
616
select truncate(-5678.123451,-2);
617
truncate(-5678.123451,-2)
619
select truncate(-5678.123451,-3);
620
truncate(-5678.123451,-3)
622
select truncate(-5678.123451,-4);
623
truncate(-5678.123451,-4)
625
create table wl1612_4 (col1 int, col2 decimal(30,25), col3 numeric(30,25));
626
insert into wl1612_4 values(1,0.0123456789012345678912345,0.0123456789012345678912345);
627
select col2/9999999999 from wl1612_4 where col1=1;
629
0.00000000000123456789024691358
630
select col3/9999999999 from wl1612_4 where col1=1;
632
0.00000000000123456789024691358
633
select 9999999999/col2 from wl1612_4 where col1=1;
636
select 9999999999/col3 from wl1612_4 where col1=1;
639
select col2*9999999999 from wl1612_4 where col1=1;
641
123456789.0000000000111104321087655
642
select col3*9999999999 from wl1612_4 where col1=1;
644
123456789.0000000000111104321087655
645
insert into wl1612_4 values(2,55555.0123456789012345678912345,55555.0123456789012345678912345);
646
select col2/9999999999 from wl1612_4 where col1=2;
648
0.00000555550123512344024696913
649
select col3/9999999999 from wl1612_4 where col1=2;
651
0.00000555550123512344024696913
652
select 9999999999/col2 from wl1612_4 where col1=2;
655
select 9999999999/col3 from wl1612_4 where col1=2;
658
select col2*9999999999 from wl1612_4 where col1=2;
660
555550123401234.0000000000111104321087655
661
select col3*9999999999 from wl1612_4 where col1=2;
663
555550123401234.0000000000111104321087655
666
select 23.4 + (-41.7), 23.4 - (41.7) = -18.3;
667
23.4 + (-41.7) 23.4 - (41.7) = -18.3
678
select 0.8 = 0.7 + 0.1;
681
drop table if exists t1;
683
Note 1051 Unknown table 't1'
684
create table t1 (col1 decimal(38));
685
insert into t1 values (12345678901234567890123456789012345678);
688
12345678901234567890123456789012345678
690
create table t1 (col1 decimal(31,30));
691
insert into t1 values (0.00000000001);
694
0.000000000010000000000000000000
696
select 7777777777777777777777777777777777777 * 10;
697
7777777777777777777777777777777777777 * 10
698
77777777777777777777777777777777777770
699
select .7777777777777777777777777777777777777 *
701
.7777777777777777777777777777777777777 *
703
777777777777777777.777777777777777777700000000000
704
select .7777777777777777777777777777777777777 - 0.1;
705
.7777777777777777777777777777777777777 - 0.1
706
0.6777777777777777777777777777777777777
707
select .343434343434343434 + .343434343434343434;
708
.343434343434343434 + .343434343434343434
710
select abs(9999999999999999999999);
711
abs(9999999999999999999999)
712
9999999999999999999999
713
select abs(-9999999999999999999999);
714
abs(-9999999999999999999999)
715
9999999999999999999999
716
select ceiling(999999999999999999);
717
ceiling(999999999999999999)
719
select ceiling(99999999999999999999);
720
ceiling(99999999999999999999)
722
select ceiling(9.9999999999999999999);
723
ceiling(9.9999999999999999999)
725
select ceiling(-9.9999999999999999999);
726
ceiling(-9.9999999999999999999)
728
select floor(999999999999999999);
729
floor(999999999999999999)
731
select floor(9999999999999999999999);
732
floor(9999999999999999999999)
733
9999999999999999999999
734
select floor(9.999999999999999999999);
735
floor(9.999999999999999999999)
737
select floor(-9.999999999999999999999);
738
floor(-9.999999999999999999999)
740
select floor(-999999999999999999999.999);
741
floor(-999999999999999999999.999)
742
-1000000000000000000000
743
select ceiling(999999999999999999999.999);
744
ceiling(999999999999999999999.999)
745
1000000000000000000000
746
select 99999999999999999999999999999999999999 mod 3;
747
99999999999999999999999999999999999999 mod 3
749
select round(99999999999999999.999);
750
round(99999999999999999.999)
752
select round(-99999999999999999.999);
753
round(-99999999999999999.999)
755
select round(99999999999999999.999,3);
756
round(99999999999999999.999,3)
757
99999999999999999.999
758
select round(-99999999999999999.999,3);
759
round(-99999999999999999.999,3)
760
-99999999999999999.999
761
select truncate(99999999999999999999999999999999999999,31);
762
truncate(99999999999999999999999999999999999999,31)
763
99999999999999999999999999999999999999.000000000000000000000000000000
764
select truncate(99.999999999999999999999999999999999999,31);
765
truncate(99.999999999999999999999999999999999999,31)
766
99.999999999999999999999999999999
767
select truncate(99999999999999999999999999999999999999,-31);
768
truncate(99999999999999999999999999999999999999,-31)
769
99999990000000000000000000000000000000
770
create table t1 as select 0.5;
771
show create table t1;
773
t1 CREATE TABLE `t1` (
774
`0.5` decimal(2,1) NOT NULL DEFAULT '0.0'
775
) ENGINE=MyISAM DEFAULT CHARSET=latin1
777
select round(1.5),round(2.5);
778
round(1.5) round(2.5)
783
set sql_mode='traditional';
791
Error 1365 Division by 0
796
Error 1365 Division by 0
797
set sql_mode='ansi,traditional';
798
CREATE TABLE Sow6_2f (col1 NUMERIC(4,2));
799
INSERT INTO Sow6_2f VALUES (10.55);
800
INSERT INTO Sow6_2f VALUES (10.5555);
802
Note 1265 Data truncated for column 'col1' at row 1
803
INSERT INTO Sow6_2f VALUES (-10.55);
804
INSERT INTO Sow6_2f VALUES (-10.5555);
806
Note 1265 Data truncated for column 'col1' at row 1
807
INSERT INTO Sow6_2f VALUES (11);
808
INSERT INTO Sow6_2f VALUES (101.55);
809
ERROR 22003: Out of range value for column 'col1' at row 1
810
UPDATE Sow6_2f SET col1 = col1 * 50 WHERE col1 = 11;
811
ERROR 22003: Out of range value for column 'col1' at row 5
812
UPDATE Sow6_2f SET col1 = col1 / 0 WHERE col1 > 0;
813
ERROR 22012: Division by 0
814
SELECT MOD(col1,0) FROM Sow6_2f;
822
Error 1365 Division by 0
823
Error 1365 Division by 0
824
Error 1365 Division by 0
825
Error 1365 Division by 0
826
Error 1365 Division by 0
827
INSERT INTO Sow6_2f VALUES ('a59b');
828
ERROR HY000: Incorrect decimal value: 'a59b' for column 'col1' at row 1
830
select 10.3330000000000/12.34500000;
831
10.3330000000000/12.34500000
837
select 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 as x;
839
99999999999999999999999999999999999999999999999999999999999999999
841
Error 1292 Truncated incorrect DECIMAL value: ''
842
select 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 + 1 as x;
844
100000000000000000000000000000000000000000000000000000000000000000
846
Error 1292 Truncated incorrect DECIMAL value: ''
847
select 0.190287977636363637 + 0.040372670 * 0 - 0;
848
0.190287977636363637 + 0.040372670 * 0 - 0
853
CREATE TABLE t1 (f1 DECIMAL (12,9), f2 DECIMAL(2,2));
854
INSERT INTO t1 VALUES (10.5, 0);
855
UPDATE t1 SET f1 = 4.5;
860
CREATE TABLE t1 (f1 DECIMAL (64,20), f2 DECIMAL(2,2));
861
INSERT INTO t1 VALUES (9999999999999999999999999999999999, 0);
864
9999999999999999999999999999999999.00000000000000000000 0.00
872
set @@sql_mode='traditional';
873
create table t1( d1 decimal(18) unsigned, d2 decimal(20) unsigned, d3 decimal (22) unsigned);
874
insert into t1 values(1,-1,-1);
875
ERROR 22003: Out of range value for column 'd2' at row 1
877
create table t1 (col1 decimal(5,2), col2 numeric(5,2));
878
insert into t1 values (999.999,999.999);
879
ERROR 22003: Out of range value for column 'col1' at row 1
880
insert into t1 values (-999.999,-999.999);
881
ERROR 22003: Out of range value for column 'col1' at row 1
886
set @sav_dpi= @@div_precision_increment;
887
set @@div_precision_increment=15;
888
create table t1 (col1 int, col2 decimal(30,25), col3 numeric(30,25));
889
insert into t1 values (1,0.0123456789012345678912345,0.0123456789012345678912345);
890
select col2/9999999999 from t1 where col1=1;
892
0.000000000001234567890246913578
893
select 9999999999/col2 from t1 where col1=1;
895
810000007209.000065537105051
896
select 77777777/7777777;
900
set div_precision_increment= @sav_dpi;
901
create table t1 (a decimal(4,2));
902
insert into t1 values (0.00);
903
select * from t1 where a > -0.00;
905
select * from t1 where a = -0.00;
909
create table t1 (col1 bigint default -9223372036854775808);
910
insert into t1 values (default);
915
select cast('1.00000001335143196001808973960578441619873046875E-10' as decimal(30,15));
916
cast('1.00000001335143196001808973960578441619873046875E-10' as decimal(30,15))
918
select ln(14000) c1, convert(ln(14000),decimal(5,3)) c2, cast(ln(14000) as decimal(5,3)) c3;
920
9.5468126085974 9.547 9.547
921
select convert(ln(14000),decimal(2,3)) c1;
922
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column '').
923
select cast(ln(14000) as decimal(2,3)) c1;
924
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column '').
925
create table t1 (sl decimal(70,30));
926
ERROR 42000: Too big precision 70 specified for column 'sl'. Maximum is 65.
927
create table t1 (sl decimal(32,31));
928
ERROR 42000: Too big scale 31 specified for column 'sl'. Maximum is 30.
929
create table t1 (sl decimal(0,38));
930
ERROR 42000: Too big scale 38 specified for column 'sl'. Maximum is 30.
931
create table t1 (sl decimal(0,30));
932
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'sl').
933
create table t1 (sl decimal(5, 5));
934
show create table t1;
936
t1 CREATE TABLE `t1` (
937
`sl` decimal(5,5) DEFAULT NULL
938
) ENGINE=MyISAM DEFAULT CHARSET=latin1
940
create table t1 (sl decimal(65, 30));
941
show create table t1;
943
t1 CREATE TABLE `t1` (
944
`sl` decimal(65,30) DEFAULT NULL
945
) ENGINE=MyISAM DEFAULT CHARSET=latin1
948
f1 decimal unsigned not null default 17.49,
949
f2 decimal unsigned not null default 17.68,
950
f3 decimal unsigned not null default 99.2,
951
f4 decimal unsigned not null default 99.7,
952
f5 decimal unsigned not null default 104.49,
953
f6 decimal unsigned not null default 199.91,
954
f7 decimal unsigned not null default 999.9,
955
f8 decimal unsigned not null default 9999.99);
957
Note 1265 Data truncated for column 'f1' at row 1
958
Note 1265 Data truncated for column 'f2' at row 1
959
Note 1265 Data truncated for column 'f3' at row 1
960
Note 1265 Data truncated for column 'f4' at row 1
961
Note 1265 Data truncated for column 'f5' at row 1
962
Note 1265 Data truncated for column 'f6' at row 1
963
Note 1265 Data truncated for column 'f7' at row 1
964
Note 1265 Data truncated for column 'f8' at row 1
965
insert into t1 (f1) values (1);
967
f1 f2 f3 f4 f5 f6 f7 f8
968
1 18 99 100 104 200 1000 10000
971
f0 decimal (30,30) zerofill not null DEFAULT 0,
972
f1 decimal (0,0) zerofill not null default 0);
973
show create table t1;
975
t1 CREATE TABLE `t1` (
976
`f0` decimal(30,30) unsigned zerofill NOT NULL DEFAULT '0.000000000000000000000000000000',
977
`f1` decimal(10,0) unsigned zerofill NOT NULL DEFAULT '0000000000'
978
) ENGINE=MyISAM DEFAULT CHARSET=latin1
980
drop procedure if exists wg2;
981
create procedure wg2()
983
declare v int default 1;
984
declare tdec decimal(5) default 0;
985
while v <= 9 do set tdec =tdec * 10;
1010
select cast(@non_existing_user_var/2 as DECIMAL);
1011
cast(@non_existing_user_var/2 as DECIMAL)
1013
create table t (d decimal(0,10));
1014
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'd').
1018
my_varchar VARCHAR(50),
1019
my_decimal DECIMAL(65,30)
1021
SHOW CREATE TABLE t1;
1023
t1 CREATE TABLE `t1` (
1024
`my_float` float DEFAULT NULL,
1025
`my_double` double DEFAULT NULL,
1026
`my_varchar` varchar(50) DEFAULT NULL,
1027
`my_decimal` decimal(65,30) DEFAULT NULL
1028
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1029
INSERT INTO t1 SET my_float = 1.175494345e-32,
1030
my_double = 1.175494345e-32,
1031
my_varchar = '1.175494345e-32';
1032
INSERT INTO t1 SET my_float = 1.175494345e-31,
1033
my_double = 1.175494345e-31,
1034
my_varchar = '1.175494345e-31';
1035
INSERT INTO t1 SET my_float = 1.175494345e-30,
1036
my_double = 1.175494345e-30,
1037
my_varchar = '1.175494345e-30';
1038
INSERT INTO t1 SET my_float = 1.175494345e-29,
1039
my_double = 1.175494345e-29,
1040
my_varchar = '1.175494345e-29';
1041
INSERT INTO t1 SET my_float = 1.175494345e-28,
1042
my_double = 1.175494345e-28,
1043
my_varchar = '1.175494345e-28';
1044
INSERT INTO t1 SET my_float = 1.175494345e-27,
1045
my_double = 1.175494345e-27,
1046
my_varchar = '1.175494345e-27';
1047
INSERT INTO t1 SET my_float = 1.175494345e-26,
1048
my_double = 1.175494345e-26,
1049
my_varchar = '1.175494345e-26';
1050
INSERT INTO t1 SET my_float = 1.175494345e-25,
1051
my_double = 1.175494345e-25,
1052
my_varchar = '1.175494345e-25';
1053
INSERT INTO t1 SET my_float = 1.175494345e-24,
1054
my_double = 1.175494345e-24,
1055
my_varchar = '1.175494345e-24';
1056
INSERT INTO t1 SET my_float = 1.175494345e-23,
1057
my_double = 1.175494345e-23,
1058
my_varchar = '1.175494345e-23';
1059
INSERT INTO t1 SET my_float = 1.175494345e-22,
1060
my_double = 1.175494345e-22,
1061
my_varchar = '1.175494345e-22';
1062
INSERT INTO t1 SET my_float = 1.175494345e-21,
1063
my_double = 1.175494345e-21,
1064
my_varchar = '1.175494345e-21';
1065
INSERT INTO t1 SET my_float = 1.175494345e-20,
1066
my_double = 1.175494345e-20,
1067
my_varchar = '1.175494345e-20';
1068
INSERT INTO t1 SET my_float = 1.175494345e-19,
1069
my_double = 1.175494345e-19,
1070
my_varchar = '1.175494345e-19';
1071
INSERT INTO t1 SET my_float = 1.175494345e-18,
1072
my_double = 1.175494345e-18,
1073
my_varchar = '1.175494345e-18';
1074
INSERT INTO t1 SET my_float = 1.175494345e-17,
1075
my_double = 1.175494345e-17,
1076
my_varchar = '1.175494345e-17';
1077
INSERT INTO t1 SET my_float = 1.175494345e-16,
1078
my_double = 1.175494345e-16,
1079
my_varchar = '1.175494345e-16';
1080
INSERT INTO t1 SET my_float = 1.175494345e-15,
1081
my_double = 1.175494345e-15,
1082
my_varchar = '1.175494345e-15';
1083
INSERT INTO t1 SET my_float = 1.175494345e-14,
1084
my_double = 1.175494345e-14,
1085
my_varchar = '1.175494345e-14';
1086
INSERT INTO t1 SET my_float = 1.175494345e-13,
1087
my_double = 1.175494345e-13,
1088
my_varchar = '1.175494345e-13';
1089
INSERT INTO t1 SET my_float = 1.175494345e-12,
1090
my_double = 1.175494345e-12,
1091
my_varchar = '1.175494345e-12';
1092
INSERT INTO t1 SET my_float = 1.175494345e-11,
1093
my_double = 1.175494345e-11,
1094
my_varchar = '1.175494345e-11';
1095
INSERT INTO t1 SET my_float = 1.175494345e-10,
1096
my_double = 1.175494345e-10,
1097
my_varchar = '1.175494345e-10';
1098
INSERT INTO t1 SET my_float = 1.175494345e-9,
1099
my_double = 1.175494345e-9,
1100
my_varchar = '1.175494345e-9';
1101
INSERT INTO t1 SET my_float = 1.175494345e-8,
1102
my_double = 1.175494345e-8,
1103
my_varchar = '1.175494345e-8';
1104
INSERT INTO t1 SET my_float = 1.175494345e-7,
1105
my_double = 1.175494345e-7,
1106
my_varchar = '1.175494345e-7';
1107
INSERT INTO t1 SET my_float = 1.175494345e-6,
1108
my_double = 1.175494345e-6,
1109
my_varchar = '1.175494345e-6';
1110
INSERT INTO t1 SET my_float = 1.175494345e-5,
1111
my_double = 1.175494345e-5,
1112
my_varchar = '1.175494345e-5';
1113
INSERT INTO t1 SET my_float = 1.175494345e-4,
1114
my_double = 1.175494345e-4,
1115
my_varchar = '1.175494345e-4';
1116
INSERT INTO t1 SET my_float = 1.175494345e-3,
1117
my_double = 1.175494345e-3,
1118
my_varchar = '1.175494345e-3';
1119
INSERT INTO t1 SET my_float = 1.175494345e-2,
1120
my_double = 1.175494345e-2,
1121
my_varchar = '1.175494345e-2';
1122
INSERT INTO t1 SET my_float = 1.175494345e-1,
1123
my_double = 1.175494345e-1,
1124
my_varchar = '1.175494345e-1';
1125
SELECT my_float, my_double, my_varchar FROM t1;
1126
my_float my_double my_varchar
1127
1.17549e-32 1.175494345e-32 1.175494345e-32
1128
1.17549e-31 1.175494345e-31 1.175494345e-31
1129
1.17549e-30 1.175494345e-30 1.175494345e-30
1130
1.17549e-29 1.175494345e-29 1.175494345e-29
1131
1.17549e-28 1.175494345e-28 1.175494345e-28
1132
1.17549e-27 1.175494345e-27 1.175494345e-27
1133
1.17549e-26 1.175494345e-26 1.175494345e-26
1134
1.17549e-25 1.175494345e-25 1.175494345e-25
1135
1.17549e-24 1.175494345e-24 1.175494345e-24
1136
1.17549e-23 1.175494345e-23 1.175494345e-23
1137
1.17549e-22 1.175494345e-22 1.175494345e-22
1138
1.17549e-21 1.175494345e-21 1.175494345e-21
1139
1.17549e-20 1.175494345e-20 1.175494345e-20
1140
1.17549e-19 1.175494345e-19 1.175494345e-19
1141
1.17549e-18 1.175494345e-18 1.175494345e-18
1142
1.17549e-17 1.175494345e-17 1.175494345e-17
1143
1.17549e-16 1.175494345e-16 1.175494345e-16
1144
1.17549e-15 1.175494345e-15 1.175494345e-15
1145
1.17549e-14 1.175494345e-14 1.175494345e-14
1146
1.17549e-13 1.175494345e-13 1.175494345e-13
1147
1.17549e-12 1.175494345e-12 1.175494345e-12
1148
1.17549e-11 1.175494345e-11 1.175494345e-11
1149
1.17549e-10 1.175494345e-10 1.175494345e-10
1150
1.17549e-09 1.175494345e-09 1.175494345e-9
1151
1.17549e-08 1.175494345e-08 1.175494345e-8
1152
1.17549e-07 1.175494345e-07 1.175494345e-7
1153
1.17549e-06 1.175494345e-06 1.175494345e-6
1154
1.17549e-05 1.175494345e-05 1.175494345e-5
1155
0.000117549 0.0001175494345 1.175494345e-4
1156
0.00117549 0.001175494345 1.175494345e-3
1157
0.0117549 0.01175494345 1.175494345e-2
1158
0.117549 0.1175494345 1.175494345e-1
1159
SELECT CAST(my_float AS DECIMAL(65,30)), my_float FROM t1;
1160
CAST(my_float AS DECIMAL(65,30)) my_float
1161
0.000000000000000000000000000000 1.17549e-32
1162
0.000000000000000000000000000000 1.17549e-31
1163
0.000000000000000000000000000001 1.17549e-30
1164
0.000000000000000000000000000012 1.17549e-29
1165
0.000000000000000000000000000118 1.17549e-28
1166
0.000000000000000000000000001175 1.17549e-27
1167
0.000000000000000000000000011755 1.17549e-26
1168
0.000000000000000000000000117549 1.17549e-25
1169
0.000000000000000000000001175494 1.17549e-24
1170
0.000000000000000000000011754943 1.17549e-23
1171
0.000000000000000000000117549438 1.17549e-22
1172
0.000000000000000000001175494332 1.17549e-21
1173
0.000000000000000000011754943324 1.17549e-20
1174
0.000000000000000000117549434853 1.17549e-19
1175
0.000000000000000001175494374380 1.17549e-18
1176
0.000000000000000011754943743802 1.17549e-17
1177
0.000000000000000117549432474939 1.17549e-16
1178
0.000000000000001175494324749389 1.17549e-15
1179
0.000000000000011754943671010360 1.17549e-14
1180
0.000000000000117549429933840000 1.17549e-13
1181
0.000000000001175494380653563000 1.17549e-12
1182
0.000000000011754943372854760000 1.17549e-11
1183
0.000000000117549428524377200000 1.17549e-10
1184
0.000000001175494368510499000000 1.17549e-09
1185
0.000000011754943685104990000000 1.17549e-08
1186
0.000000117549433298336200000000 1.17549e-07
1187
0.000001175494389826781000000000 1.17549e-06
1188
0.000011754943443520460000000000 1.17549e-05
1189
0.000117549432616215200000000000 0.000117549
1190
0.001175494398921728000000000000 0.00117549
1191
0.011754943057894710000000000000 0.0117549
1192
0.117549434304237400000000000000 0.117549
1193
SELECT CAST(my_double AS DECIMAL(65,30)), my_double FROM t1;
1194
CAST(my_double AS DECIMAL(65,30)) my_double
1195
0.000000000000000000000000000000 1.175494345e-32
1196
0.000000000000000000000000000000 1.175494345e-31
1197
0.000000000000000000000000000001 1.175494345e-30
1198
0.000000000000000000000000000012 1.175494345e-29
1199
0.000000000000000000000000000118 1.175494345e-28
1200
0.000000000000000000000000001175 1.175494345e-27
1201
0.000000000000000000000000011755 1.175494345e-26
1202
0.000000000000000000000000117549 1.175494345e-25
1203
0.000000000000000000000001175494 1.175494345e-24
1204
0.000000000000000000000011754943 1.175494345e-23
1205
0.000000000000000000000117549435 1.175494345e-22
1206
0.000000000000000000001175494345 1.175494345e-21
1207
0.000000000000000000011754943450 1.175494345e-20
1208
0.000000000000000000117549434500 1.175494345e-19
1209
0.000000000000000001175494345000 1.175494345e-18
1210
0.000000000000000011754943450000 1.175494345e-17
1211
0.000000000000000117549434500000 1.175494345e-16
1212
0.000000000000001175494345000000 1.175494345e-15
1213
0.000000000000011754943450000000 1.175494345e-14
1214
0.000000000000117549434500000000 1.175494345e-13
1215
0.000000000001175494345000000000 1.175494345e-12
1216
0.000000000011754943450000000000 1.175494345e-11
1217
0.000000000117549434500000000000 1.175494345e-10
1218
0.000000001175494345000000000000 1.175494345e-09
1219
0.000000011754943450000000000000 1.175494345e-08
1220
0.000000117549434500000000000000 1.175494345e-07
1221
0.000001175494345000000000000000 1.175494345e-06
1222
0.000011754943450000000000000000 1.175494345e-05
1223
0.000117549434500000000000000000 0.0001175494345
1224
0.001175494345000000000000000000 0.001175494345
1225
0.011754943450000000000000000000 0.01175494345
1226
0.117549434500000000000000000000 0.1175494345
1227
SELECT CAST(my_varchar AS DECIMAL(65,30)), my_varchar FROM t1;
1228
CAST(my_varchar AS DECIMAL(65,30)) my_varchar
1229
0.000000000000000000000000000000 1.175494345e-32
1230
0.000000000000000000000000000000 1.175494345e-31
1231
0.000000000000000000000000000001 1.175494345e-30
1232
0.000000000000000000000000000012 1.175494345e-29
1233
0.000000000000000000000000000118 1.175494345e-28
1234
0.000000000000000000000000001175 1.175494345e-27
1235
0.000000000000000000000000011755 1.175494345e-26
1236
0.000000000000000000000000117549 1.175494345e-25
1237
0.000000000000000000000001175494 1.175494345e-24
1238
0.000000000000000000000011754943 1.175494345e-23
1239
0.000000000000000000000117549435 1.175494345e-22
1240
0.000000000000000000001175494345 1.175494345e-21
1241
0.000000000000000000011754943450 1.175494345e-20
1242
0.000000000000000000117549434500 1.175494345e-19
1243
0.000000000000000001175494345000 1.175494345e-18
1244
0.000000000000000011754943450000 1.175494345e-17
1245
0.000000000000000117549434500000 1.175494345e-16
1246
0.000000000000001175494345000000 1.175494345e-15
1247
0.000000000000011754943450000000 1.175494345e-14
1248
0.000000000000117549434500000000 1.175494345e-13
1249
0.000000000001175494345000000000 1.175494345e-12
1250
0.000000000011754943450000000000 1.175494345e-11
1251
0.000000000117549434500000000000 1.175494345e-10
1252
0.000000001175494345000000000000 1.175494345e-9
1253
0.000000011754943450000000000000 1.175494345e-8
1254
0.000000117549434500000000000000 1.175494345e-7
1255
0.000001175494345000000000000000 1.175494345e-6
1256
0.000011754943450000000000000000 1.175494345e-5
1257
0.000117549434500000000000000000 1.175494345e-4
1258
0.001175494345000000000000000000 1.175494345e-3
1259
0.011754943450000000000000000000 1.175494345e-2
1260
0.117549434500000000000000000000 1.175494345e-1
1261
UPDATE t1 SET my_decimal = my_float;
1262
SELECT my_decimal, my_float FROM t1;
1264
0.000000000000000000000000000000 1.17549e-32
1265
0.000000000000000000000000000000 1.17549e-31
1266
0.000000000000000000000000000001 1.17549e-30
1267
0.000000000000000000000000000012 1.17549e-29
1268
0.000000000000000000000000000118 1.17549e-28
1269
0.000000000000000000000000001175 1.17549e-27
1270
0.000000000000000000000000011755 1.17549e-26
1271
0.000000000000000000000000117549 1.17549e-25
1272
0.000000000000000000000001175494 1.17549e-24
1273
0.000000000000000000000011754943 1.17549e-23
1274
0.000000000000000000000117549438 1.17549e-22
1275
0.000000000000000000001175494332 1.17549e-21
1276
0.000000000000000000011754943324 1.17549e-20
1277
0.000000000000000000117549434853 1.17549e-19
1278
0.000000000000000001175494374380 1.17549e-18
1279
0.000000000000000011754943743802 1.17549e-17
1280
0.000000000000000117549432474939 1.17549e-16
1281
0.000000000000001175494324749389 1.17549e-15
1282
0.000000000000011754943671010360 1.17549e-14
1283
0.000000000000117549429933840000 1.17549e-13
1284
0.000000000001175494380653563000 1.17549e-12
1285
0.000000000011754943372854760000 1.17549e-11
1286
0.000000000117549428524377200000 1.17549e-10
1287
0.000000001175494368510499000000 1.17549e-09
1288
0.000000011754943685104990000000 1.17549e-08
1289
0.000000117549433298336200000000 1.17549e-07
1290
0.000001175494389826781000000000 1.17549e-06
1291
0.000011754943443520460000000000 1.17549e-05
1292
0.000117549432616215200000000000 0.000117549
1293
0.001175494398921728000000000000 0.00117549
1294
0.011754943057894710000000000000 0.0117549
1295
0.117549434304237400000000000000 0.117549
1296
UPDATE t1 SET my_decimal = my_double;
1297
SELECT my_decimal, my_double FROM t1;
1298
my_decimal my_double
1299
0.000000000000000000000000000000 1.175494345e-32
1300
0.000000000000000000000000000000 1.175494345e-31
1301
0.000000000000000000000000000001 1.175494345e-30
1302
0.000000000000000000000000000012 1.175494345e-29
1303
0.000000000000000000000000000118 1.175494345e-28
1304
0.000000000000000000000000001175 1.175494345e-27
1305
0.000000000000000000000000011755 1.175494345e-26
1306
0.000000000000000000000000117549 1.175494345e-25
1307
0.000000000000000000000001175494 1.175494345e-24
1308
0.000000000000000000000011754943 1.175494345e-23
1309
0.000000000000000000000117549435 1.175494345e-22
1310
0.000000000000000000001175494345 1.175494345e-21
1311
0.000000000000000000011754943450 1.175494345e-20
1312
0.000000000000000000117549434500 1.175494345e-19
1313
0.000000000000000001175494345000 1.175494345e-18
1314
0.000000000000000011754943450000 1.175494345e-17
1315
0.000000000000000117549434500000 1.175494345e-16
1316
0.000000000000001175494345000000 1.175494345e-15
1317
0.000000000000011754943450000000 1.175494345e-14
1318
0.000000000000117549434500000000 1.175494345e-13
1319
0.000000000001175494345000000000 1.175494345e-12
1320
0.000000000011754943450000000000 1.175494345e-11
1321
0.000000000117549434500000000000 1.175494345e-10
1322
0.000000001175494345000000000000 1.175494345e-09
1323
0.000000011754943450000000000000 1.175494345e-08
1324
0.000000117549434500000000000000 1.175494345e-07
1325
0.000001175494345000000000000000 1.175494345e-06
1326
0.000011754943450000000000000000 1.175494345e-05
1327
0.000117549434500000000000000000 0.0001175494345
1328
0.001175494345000000000000000000 0.001175494345
1329
0.011754943450000000000000000000 0.01175494345
1330
0.117549434500000000000000000000 0.1175494345
1331
UPDATE t1 SET my_decimal = my_varchar;
1333
Note 1265 Data truncated for column 'my_decimal' at row 1
1334
Note 1265 Data truncated for column 'my_decimal' at row 2
1335
Note 1265 Data truncated for column 'my_decimal' at row 3
1336
Note 1265 Data truncated for column 'my_decimal' at row 4
1337
Note 1265 Data truncated for column 'my_decimal' at row 5
1338
Note 1265 Data truncated for column 'my_decimal' at row 6
1339
Note 1265 Data truncated for column 'my_decimal' at row 7
1340
Note 1265 Data truncated for column 'my_decimal' at row 8
1341
Note 1265 Data truncated for column 'my_decimal' at row 9
1342
Note 1265 Data truncated for column 'my_decimal' at row 10
1343
Note 1265 Data truncated for column 'my_decimal' at row 11
1344
SELECT my_decimal, my_varchar FROM t1;
1345
my_decimal my_varchar
1346
0.000000000000000000000000000000 1.175494345e-32
1347
0.000000000000000000000000000000 1.175494345e-31
1348
0.000000000000000000000000000001 1.175494345e-30
1349
0.000000000000000000000000000012 1.175494345e-29
1350
0.000000000000000000000000000118 1.175494345e-28
1351
0.000000000000000000000000001175 1.175494345e-27
1352
0.000000000000000000000000011755 1.175494345e-26
1353
0.000000000000000000000000117549 1.175494345e-25
1354
0.000000000000000000000001175494 1.175494345e-24
1355
0.000000000000000000000011754943 1.175494345e-23
1356
0.000000000000000000000117549435 1.175494345e-22
1357
0.000000000000000000001175494345 1.175494345e-21
1358
0.000000000000000000011754943450 1.175494345e-20
1359
0.000000000000000000117549434500 1.175494345e-19
1360
0.000000000000000001175494345000 1.175494345e-18
1361
0.000000000000000011754943450000 1.175494345e-17
1362
0.000000000000000117549434500000 1.175494345e-16
1363
0.000000000000001175494345000000 1.175494345e-15
1364
0.000000000000011754943450000000 1.175494345e-14
1365
0.000000000000117549434500000000 1.175494345e-13
1366
0.000000000001175494345000000000 1.175494345e-12
1367
0.000000000011754943450000000000 1.175494345e-11
1368
0.000000000117549434500000000000 1.175494345e-10
1369
0.000000001175494345000000000000 1.175494345e-9
1370
0.000000011754943450000000000000 1.175494345e-8
1371
0.000000117549434500000000000000 1.175494345e-7
1372
0.000001175494345000000000000000 1.175494345e-6
1373
0.000011754943450000000000000000 1.175494345e-5
1374
0.000117549434500000000000000000 1.175494345e-4
1375
0.001175494345000000000000000000 1.175494345e-3
1376
0.011754943450000000000000000000 1.175494345e-2
1377
0.117549434500000000000000000000 1.175494345e-1
1379
create table t1 (c1 decimal(64));
1380
insert into t1 values(
1381
89000000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
1383
Error 1292 Truncated incorrect DECIMAL value: ''
1384
Warning 1264 Out of range value for column 'c1' at row 1
1385
insert into t1 values(
1386
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 *
1387
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999);
1389
Error 1292 Truncated incorrect DECIMAL value: ''
1390
Error 1292 Truncated incorrect DECIMAL value: ''
1391
Error 1292 Truncated incorrect DECIMAL value: ''
1392
Warning 1264 Out of range value for column 'c1' at row 1
1393
insert into t1 values(1e100);
1395
Warning 1264 Out of range value for column 'c1' at row 1
1398
9999999999999999999999999999999999999999999999999999999999999999
1399
9999999999999999999999999999999999999999999999999999999999999999
1400
9999999999999999999999999999999999999999999999999999999999999999
1402
create table t1(a decimal(7,2));
1403
insert into t1 values(123.12);
1407
alter table t1 modify a decimal(10,2);
1412
create table t1 (i int, j int);
1413
insert into t1 values (1,1), (1,2), (2,3), (2,4);
1414
select i, count(distinct j) from t1 group by i;
1418
select i+0.0 as i2, count(distinct j) from t1 group by i2;
1419
i2 count(distinct j)
1423
create table t1(f1 decimal(20,6));
1424
insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond);
1425
insert into t1 values (CAST('10:11:12' AS time));
1428
20101112000000.000014
1431
select cast(19999999999999999999 as unsigned);
1432
cast(19999999999999999999 as unsigned)
1433
18446744073709551615
1435
Error 1292 Truncated incorrect DECIMAL value: ''
1436
create table t1(a decimal(18));
1437
insert into t1 values(123456789012345678);
1438
alter table t1 modify column a decimal(19);
1443
select cast(11.1234 as DECIMAL(3,2));
1444
cast(11.1234 as DECIMAL(3,2))
1447
Error 1264 Out of range value for column 'cast(11.1234 as DECIMAL(3,2))' at row 1
1448
select * from (select cast(11.1234 as DECIMAL(3,2))) t;
1449
cast(11.1234 as DECIMAL(3,2))
1452
Error 1264 Out of range value for column 'cast(11.1234 as DECIMAL(3,2))' at row 1
1453
select cast(a as DECIMAL(3,2))
1454
from (select 11.1233 as a
1455
UNION select 11.1234
1456
UNION select 12.1234
1458
cast(a as DECIMAL(3,2))
1463
Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
1464
Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
1465
Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
1466
select cast(a as DECIMAL(3,2)), count(*)
1467
from (select 11.1233 as a
1468
UNION select 11.1234
1469
UNION select 12.1234
1471
cast(a as DECIMAL(3,2)) count(*)
1474
Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
1475
Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
1476
Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
1477
Error 1264 Out of range value for column 'cast(a as DECIMAL(3,2))' at row 1
1478
create table t1 (s varchar(100));
1479
insert into t1 values (0.00000000010000000000000000364321973154977415791655470655996396089904010295867919921875);
1481
SELECT 1.000000000000 * 99.999999999998 / 100 a,1.000000000000 * (99.999999999998 / 100) b;
1483
0.9999999999999800000000000000 0.9999999999999800000000000000
1484
SELECT CAST(1 AS decimal(65,10));
1485
CAST(1 AS decimal(65,10))
1487
SELECT CAST(1 AS decimal(66,10));
1488
ERROR 42000: Too big precision 66 specified for column '1'. Maximum is 65.
1489
SELECT CAST(1 AS decimal(65,30));
1490
CAST(1 AS decimal(65,30))
1491
1.000000000000000000000000000000
1492
SELECT CAST(1 AS decimal(65,31));
1493
ERROR 42000: Too big scale 31 specified for column '1'. Maximum is 30.
1494
CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL);
1495
INSERT INTO t1 VALUES (3,30), (1,10), (2,10);
1496
SELECT a+CAST(1 AS decimal(65,30)) AS aa, SUM(b) FROM t1 GROUP BY aa;
1498
2.000000000000000000000000000000 10
1499
3.000000000000000000000000000000 10
1500
4.000000000000000000000000000000 30
1501
SELECT a+CAST(1 AS decimal(65,31)) AS aa, SUM(b) FROM t1 GROUP BY aa;
1502
ERROR 42000: Too big scale 31 specified for column '1'. Maximum is 30.
1504
CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL);
1505
INSERT INTO t1 VALUES (3,30), (1,10), (2,10);
1506
SET @a= CAST(1 AS decimal);
1507
SELECT 1 FROM t1 GROUP BY @b := @a, @b;
1512
CREATE TABLE t1 SELECT 0.123456789012345678901234567890123456 AS f1;
1514
Note 1265 Data truncated for column 'f1' at row 1
1516
Field Type Null Key Default Extra
1517
f1 decimal(31,30) NO 0.000000000000000000000000000000
1520
0.123456789012345678901234567890
1522
CREATE TABLE t1 SELECT 123451234512345123451234512345123451234512345.678906789067890678906789067890678906789067890 AS f1;
1524
Warning 1264 Out of range value for column 'f1' at row 1
1526
Field Type Null Key Default Extra
1527
f1 decimal(65,30) NO 0.000000000000000000000000000000
1530
99999999999999999999999999999999999.999999999999999999999999999999
1532
select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 *
1533
1.01500000 * 1.01500000 * 0.99500000);
1534
(1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 *
1535
1.01500000 * 1.01500000 * 0.99500000)
1536
0.812988073953673124592306939480
1537
create table t1 as select 5.05 / 0.014;
1539
Note 1265 Data truncated for column '5.05 / 0.014' at row 1
1542
Note 1265 Data truncated for column '5.05 / 0.014' at row 1
1543
show create table t1;
1545
t1 CREATE TABLE `t1` (
1546
`5.05 / 0.014` decimal(10,6) DEFAULT NULL
1547
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1553
select cast(143.481 as decimal(4,1));
1554
cast(143.481 as decimal(4,1))
1556
select cast(143.481 as decimal(4,0));
1557
cast(143.481 as decimal(4,0))
1559
select cast(143.481 as decimal(2,1));
1560
cast(143.481 as decimal(2,1))
1563
Error 1264 Out of range value for column 'cast(143.481 as decimal(2,1))' at row 1
1564
select cast(-3.4 as decimal(2,1));
1565
cast(-3.4 as decimal(2,1))
1567
select cast(99.6 as decimal(2,0));
1568
cast(99.6 as decimal(2,0))
1571
Error 1264 Out of range value for column 'cast(99.6 as decimal(2,0))' at row 1
1572
select cast(-13.4 as decimal(2,1));
1573
cast(-13.4 as decimal(2,1))
1576
Error 1264 Out of range value for column 'cast(-13.4 as decimal(2,1))' at row 1
1577
select cast(98.6 as decimal(2,0));
1578
cast(98.6 as decimal(2,0))
1581
# Bug #45262: Bad effects with CREATE TABLE and DECIMAL
1583
CREATE TABLE t1 SELECT .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col;
1585
Note 1265 Data truncated for column 'my_col' at row 1
1587
Field Type Null Key Default Extra
1588
my_col decimal(30,30) NO 0.000000000000000000000000000000
1589
SELECT my_col FROM t1;
1591
0.123456789123456789123456789123
1593
CREATE TABLE t1 SELECT 1 + .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col;
1595
Note 1265 Data truncated for column 'my_col' at row 1
1597
Field Type Null Key Default Extra
1598
my_col decimal(65,30) NO 0.000000000000000000000000000000
1599
SELECT my_col FROM t1;
1601
1.123456789123456789123456789123
1603
CREATE TABLE t1 SELECT 1 * .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col;
1605
Note 1265 Data truncated for column 'my_col' at row 1
1607
Field Type Null Key Default Extra
1608
my_col decimal(65,30) NO 0.000000000000000000000000000000
1609
SELECT my_col FROM t1;
1611
0.123456789123456789123456789123
1613
CREATE TABLE t1 SELECT 1 / .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col;
1615
Note 1265 Data truncated for column 'my_col' at row 1
1617
Field Type Null Key Default Extra
1618
my_col decimal(65,4) YES NULL
1619
SELECT my_col FROM t1;
1623
CREATE TABLE t1 SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col;
1625
Note 1265 Data truncated for column 'my_col' at row 1
1627
Field Type Null Key Default Extra
1628
my_col decimal(65,30) YES NULL
1629
SELECT my_col FROM t1;
1631
0.012345687012345687012345687012
1634
# Bug#45261: Crash, stored procedure + decimal
1636
DROP TABLE IF EXISTS t1;
1637
CREATE TABLE t1 SELECT
1638
/* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001
1641
Warning 1264 Out of range value for column 'c1' at row 1
1643
Field Type Null Key Default Extra
1644
c1 decimal(65,0) NO 0
1647
99999999999999999999999999999999999999999999999999999999999999999
1649
CREATE TABLE t1 SELECT
1650
/* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001.
1653
Warning 1264 Out of range value for column 'c1' at row 1
1655
Field Type Null Key Default Extra
1656
c1 decimal(65,0) NO 0
1659
99999999999999999999999999999999999999999999999999999999999999999
1661
CREATE TABLE t1 SELECT
1662
/* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001.1 /* 1 */
1665
Warning 1264 Out of range value for column 'c1' at row 1
1667
Field Type Null Key Default Extra
1668
c1 decimal(65,0) NO 0
1671
99999999999999999999999999999999999999999999999999999999999999999
1673
CREATE TABLE t1 SELECT
1674
/* 82 */ 1000000000000000000000000000000000000000000000000000000000000000000000000000000001
1677
Error 1292 Truncated incorrect DECIMAL value: ''
1679
Field Type Null Key Default Extra
1680
c1 decimal(65,0) NO 0
1683
99999999999999999999999999999999999999999999999999999999999999999
1685
CREATE TABLE t1 SELECT
1686
/* 40 */ 1000000000000000000000000000000000000001.1000000000000000000000000000000000000001 /* 40 */
1689
Warning 1264 Out of range value for column 'c1' at row 1
1691
Field Type Null Key Default Extra
1692
c1 decimal(65,30) NO 0.000000000000000000000000000000
1695
99999999999999999999999999999999999.999999999999999999999999999999
1697
CREATE TABLE t1 SELECT
1698
/* 1 */ 1.10000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 80 */
1701
Field Type Null Key Default Extra
1702
c1 decimal(31,30) NO 0.000000000000000000000000000000
1705
1.100000000000000000000000000000
1707
CREATE TABLE t1 SELECT
1708
/* 1 */ 1.100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81 */
1711
Field Type Null Key Default Extra
1712
c1 decimal(31,30) NO 0.000000000000000000000000000000
1715
1.100000000000000000000000000000
1717
CREATE TABLE t1 SELECT
1718
.100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81 */
1721
Note 1265 Data truncated for column 'c1' at row 1
1723
Field Type Null Key Default Extra
1724
c1 decimal(30,30) NO 0.000000000000000000000000000000
1727
0.100000000000000000000000000000
1729
CREATE TABLE t1 SELECT
1730
/* 45 */ 123456789012345678901234567890123456789012345.123456789012345678901234567890123456789012345 /* 45 */
1733
Warning 1264 Out of range value for column 'c1' at row 1
1735
Field Type Null Key Default Extra
1736
c1 decimal(65,30) NO 0.000000000000000000000000000000
1739
99999999999999999999999999999999999.999999999999999999999999999999
1741
CREATE TABLE t1 SELECT
1742
/* 65 */ 12345678901234567890123456789012345678901234567890123456789012345.1 /* 1 */
1745
Warning 1264 Out of range value for column 'c1' at row 1
1747
Field Type Null Key Default Extra
1748
c1 decimal(65,1) NO 0.0
1751
9999999999999999999999999999999999999999999999999999999999999999.9
1753
CREATE TABLE t1 SELECT
1754
/* 66 */ 123456789012345678901234567890123456789012345678901234567890123456.1 /* 1 */
1757
Warning 1264 Out of range value for column 'c1' at row 1
1759
Field Type Null Key Default Extra
1760
c1 decimal(65,1) NO 0.0
1763
9999999999999999999999999999999999999999999999999999999999999999.9
1765
CREATE TABLE t1 SELECT
1766
.123456789012345678901234567890123456789012345678901234567890123456 /* 66 */
1769
Note 1265 Data truncated for column 'c1' at row 1
1771
Field Type Null Key Default Extra
1772
c1 decimal(30,30) NO 0.000000000000000000000000000000
1775
0.123456789012345678901234567890
1777
CREATE TABLE t1 AS SELECT 123.1234567890123456789012345678901 /* 31 */ AS c1;
1779
Note 1265 Data truncated for column 'c1' at row 1
1781
Field Type Null Key Default Extra
1782
c1 decimal(33,30) NO 0.000000000000000000000000000000
1785
123.123456789012345678901234567890
1787
CREATE TABLE t1 SELECT 1.1 + CAST(1 AS DECIMAL(65,30)) AS c1;
1789
Field Type Null Key Default Extra
1790
c1 decimal(65,30) NO 0.000000000000000000000000000000
1793
2.100000000000000000000000000000
1796
# Test that the integer and decimal parts are properly calculated.
1798
CREATE TABLE t1 (a DECIMAL(30,30));
1799
INSERT INTO t1 VALUES (0.1),(0.2),(0.3);
1800
CREATE TABLE t2 SELECT MIN(a + 0.0000000000000000000000000000001) AS c1 FROM t1;
1802
Note 1265 Data truncated for column 'c1' at row 3
1804
Field Type Null Key Default Extra
1805
c1 decimal(32,30) YES NULL
1807
CREATE TABLE t1 (a DECIMAL(30,30));
1808
INSERT INTO t1 VALUES (0.1),(0.2),(0.3);
1809
CREATE TABLE t2 SELECT IFNULL(a + 0.0000000000000000000000000000001, NULL) AS c1 FROM t1;
1811
Note 1265 Data truncated for column 'c1' at row 1
1812
Note 1265 Data truncated for column 'c1' at row 2
1813
Note 1265 Data truncated for column 'c1' at row 3
1815
Field Type Null Key Default Extra
1816
c1 decimal(34,0) YES NULL
1818
CREATE TABLE t1 (a DECIMAL(30,30));
1819
INSERT INTO t1 VALUES (0.1),(0.2),(0.3);
1820
CREATE TABLE t2 SELECT CASE a WHEN 0.1 THEN 0.0000000000000000000000000000000000000000000000000000000000000000001 END AS c1 FROM t1;
1822
Note 1265 Data truncated for column 'c1' at row 1
1824
Field Type Null Key Default Extra
1825
c1 decimal(65,30) YES NULL
1828
# Test that variables get maximum precision.
1831
CREATE TABLE t1 SELECT @decimal AS c1;
1833
Field Type Null Key Default Extra
1834
c1 decimal(65,30) YES NULL
1837
1.100000000000000000000000000000
1840
# Bug #45261 : Crash, stored procedure + decimal
1841
# Original test by the reporter.
1845
SELECT .123456789012345678901234567890123456789012345678901234567890123456 AS a;
1847
Note 1265 Data truncated for column 'a' at row 1
1849
CREATE PROCEDURE test_proc()
1851
# The las non critical CUSER definition is:
1852
# DECLARE mycursor CURSOR FOR SELECT 1 %
1853
# .12345678912345678912345678912345678912345678912345678912345678912 AS my_col;
1854
DECLARE mycursor CURSOR FOR
1856
.123456789123456789123456789123456789123456789123456789123456789123456789123456789
1863
DROP PROCEDURE test_proc;
1865
# Bug #48370 Absolutely wrong calculations with GROUP BY and
1866
# decimal fields when using IF
1868
CREATE TABLE currencies (id int, rate decimal(16,4),
1869
PRIMARY KEY (id), KEY (rate));
1870
INSERT INTO currencies VALUES (11,0.7028);
1871
INSERT INTO currencies VALUES (1,1);
1872
CREATE TABLE payments (
1879
KEY currency_id (currency_id),
1880
KEY supplier_id (supplier_id)
1882
INSERT INTO payments (id,status,vat,supplier_id,currency_id) VALUES
1883
(3001,2,0.0000,344,11), (1,2,0.0000,1,1);
1884
CREATE TABLE sub_tasks (
1887
price decimal(16,4),
1888
discount decimal(10,4),
1891
KEY currency_id (currency_id),
1892
KEY payment_id (payment_id)
1894
INSERT INTO sub_tasks (id, price, discount, payment_id, currency_id) VALUES
1895
(52, 12.60, 0, 3001, 11), (56, 14.58, 0, 3001, 11);
1896
# should return 1 and the same values in col 2 and 3
1897
select STRAIGHT_JOIN
1898
(1 + PAY.vat) AS mult,
1899
SUM(ROUND((SUB.price - ROUND(ROUND(SUB.price, 2) * SUB.discount, 2)) *
1900
CUR.rate / CUR.rate, 2)
1901
) v_net_with_discount,
1902
SUM(ROUND((SUB.price - ROUND(ROUND(SUB.price, 2) * SUB.discount, 1)) *
1903
CUR.rate / CUR.rate , 2)
1907
currencies CUR, payments PAY, sub_tasks SUB
1909
SUB.payment_id = PAY.id and
1910
PAY.currency_id = CUR.id and
1912
group by PAY.id + 1;
1913
mult v_net_with_discount v_total
1914
1.0000 27.18 27.180000
1915
DROP TABLE currencies, payments, sub_tasks;
added
removed
mysql-test/r/type_newdecimal.result
