2
drop table if exists t1;
5
# constant IN function test
7
select 1.1 IN (1.0, 1.2);
8
select 1.1 IN (1.0, 1.2, 1.1, 1.4, 0.5);
9
select 1.1 IN (1.0, 1.2, NULL, 1.4, 0.5);
10
select 0.5 IN (1.0, 1.2, NULL, 1.4, 0.5);
11
select 1 IN (1.11, 1.2, 1.1, 1.4, 1, 0.5);
12
select 1 IN (1.11, 1.2, 1.1, 1.4, NULL, 0.5);
17
select case 1.0 when 0.1 then "a" when 1.0 then "b" else "c" END;
18
select case 0.1 when 0.1 then "a" when 1.0 then "b" else "c" END;
19
select case 1 when 0.1 then "a" when 1.0 then "b" else "c" END;
20
select case 1.0 when 0.1 then "a" when 1 then "b" else "c" END;
21
select case 1.001 when 0.1 then "a" when 1 then "b" else "c" END;
24
# non constant IN test
26
create table t1 (a decimal(6,3));
27
insert into t1 values (1.0), (NULL), (0.1);
29
select 0.1 in (1.0, 1.2, 1.1, a, 1.4, 0.5) from t1;
35
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);
43
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);
49
# saving in decimal field with overflow
52
create table t1 (a decimal(4,2));
53
insert into t1 value (10000), (1.1e10), ("11111"), (100000.1);
54
insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
57
create table t1 (a decimal(4,2) unsigned);
58
insert into t1 value (10000), (1.1e10), ("11111"), (100000.1);
59
insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
65
# saving in field with overflow from decimal
67
create table t1 (a bigint);
68
insert into t1 values (18446744073709551615.0);
69
insert into t1 values (9223372036854775808.0);
70
insert into t1 values (-18446744073709551615.0);
73
create table t1 (a bigint unsigned);
74
insert into t1 values (18446744073709551615.0);
75
insert into t1 values (9223372036854775808.0);
76
insert into t1 values (9999999999999999999999999.000);
77
insert into t1 values (-1.0);
80
create table t1 (a tinyint);
81
insert into t1 values (18446744073709551615.0);
82
insert into t1 values (9223372036854775808.0);
87
# test that functions create decimal fields
89
create table t1 select round(15.4,-1), truncate(-5678.123451,-3), abs(-1.1), -(-1.1);
96
set session sql_mode='traditional';
98
create table wl1612 (col1 int, col2 decimal(38,10), col3 numeric(38,10));
99
insert into wl1612 values(1,12345678901234567890.1234567890,12345678901234567890.1234567890);
100
select * from wl1612;
101
insert into wl1612 values(2,01234567890123456789.0123456789,01234567890123456789.0123456789);
102
select * from wl1612 where col1=2;
103
insert into wl1612 values(3,1234567890123456789012345678.0123456789,1234567890123456789012345678.0123456789);
104
select * from wl1612 where col1=3;
106
select col1/0 from wl1612;
107
select col2/0 from wl1612;
108
select col3/0 from wl1612;
110
insert into wl1612 values(5,5000.0005,5000.0005);
111
insert into wl1612 values(6,5000.0005,5000.0005);
112
select sum(col2),sum(col3) from wl1612;
113
#select avg(col2),avg(col3) from wl1612;
115
insert into wl1612 values(7,500000.000005,500000.000005);
116
insert into wl1612 values(8,500000.000005,500000.000005);
117
select sum(col2),sum(col3) from wl1612 where col1>4;
118
#select avg(col2),avg(col3) from wl1612 where col1>4;
120
#insert into wl1612 (col1,col2) values(9,123456789012345678901234567890);
121
#insert into wl1612 (col1,col3) values(9,123456789012345678901234567890);
123
insert into wl1612 (col1, col2) values(9,1.01234567891);
124
insert into wl1612 (col1, col2) values(10,1.01234567894);
125
insert into wl1612 (col1, col2) values(11,1.01234567895);
126
insert into wl1612 (col1, col2) values(12,1.01234567896);
127
select col1,col2 from wl1612 where col1>8;
129
insert into wl1612 (col1, col3) values(13,1.01234567891);
130
insert into wl1612 (col1, col3) values(14,1.01234567894);
131
insert into wl1612 (col1, col3) values(15,1.01234567895);
132
insert into wl1612 (col1, col3) values(16,1.01234567896);
133
select col1,col3 from wl1612 where col1>12;
135
select col1 from wl1612 where col1>4 and col2=1.01234567891;
136
#-- should return 0 rows
138
select col1 from wl1612 where col1>4 and col2=1.0123456789;
139
#-- should return col1 values 9 & 10
141
select col1 from wl1612 where col1>4 and col2<>1.0123456789;
142
#-- should return col1 values 5,6,7,8,11,12
144
select col1 from wl1612 where col1>4 and col2<1.0123456789;
145
#-- should return 0 rows
147
select col1 from wl1612 where col1>4 and col2<=1.0123456789;
148
#-- should return col1 values 9 & 10
150
select col1 from wl1612 where col1>4 and col2>1.0123456789;
151
#-- should return col1 values 5,6,7,8,11,12
153
select col1 from wl1612 where col1>4 and col2>=1.0123456789;
154
#-- should return col1 values 5,6,7,8,910,11,12
156
#select col1, col2 from wl1612 where col1=11 or col1=12;
157
select col1 from wl1612 where col1>4 and col2=1.012345679;
158
#-- should return col1 values 11,12
160
select col1 from wl1612 where col1>4 and col2<>1.012345679;
161
#-- should return col1 values 5,6,7,8,9,10
163
select col1 from wl1612 where col1>4 and col3=1.01234567891;
164
#-- should return 0 rows
166
select col1 from wl1612 where col1>4 and col3=1.0123456789;
167
#-- should return col1 values 13,14
169
select col1 from wl1612 where col1>4 and col3<>1.0123456789;
170
#-- should return col1 values 5,6,7,8,15,16
172
select col1 from wl1612 where col1>4 and col3<1.0123456789;
173
#-- should return 0 rows
175
select col1 from wl1612 where col1>4 and col3<=1.0123456789;
176
#-- should return col1 values 13,14
178
select col1 from wl1612 where col1>4 and col3>1.0123456789;
179
#-- should return col1 values 5,6,7,8,15,16
181
select col1 from wl1612 where col1>4 and col3>=1.0123456789;
182
#-- should return col1 values 5,6,7,8,13,14,15,16
184
select col1 from wl1612 where col1>4 and col3=1.012345679;
185
#-- should return col1 values 15,16
187
select col1 from wl1612 where col1>4 and col3<>1.012345679;
188
#-- should return col1 values 5,6,7,8,13,14
195
#-- should return 1 (true)
199
create table wl1612_1 (col1 int);
200
insert into wl1612_1 values(10);
202
select * from wl1612_1 where 0.8=0.7+0.1;
203
#--should return 1 row (col1=10)
205
select 0.07+0.07 from wl1612_1;
207
select 0.07-0.07 from wl1612_1;
209
select 0.07*0.07 from wl1612_1;
211
select 0.07/0.07 from wl1612_1;
215
create table wl1612_2 (col1 decimal(10,2), col2 numeric(10,2));
216
insert into wl1612_2 values(1,1);
217
insert into wl1612_2 values(+1,+1);
218
insert into wl1612_2 values(+01,+01);
219
insert into wl1612_2 values(+001,+001);
221
select col1,count(*) from wl1612_2 group by col1;
223
select col2,count(*) from wl1612_2 group by col2;
227
create table wl1612_3 (col1 decimal(10,2), col2 numeric(10,2));
228
insert into wl1612_3 values('1','1');
229
insert into wl1612_3 values('+1','+1');
231
insert into wl1612_3 values('+01','+01');
232
insert into wl1612_3 values('+001','+001');
234
select col1,count(*) from wl1612_3 group by col1;
236
select col2,count(*) from wl1612_3 group by col2;
243
select mod(234.567,10.555);
244
#-- should return 2.357
246
select mod(-234.567,10.555);
247
#-- should return -2.357
249
select mod(234.567,-10.555);
250
#-- should return 2.357
268
#-- should return -15
271
#-- should return -15
274
#-- should return -16
277
#-- should return -16
280
#-- should return -16
282
select round(15.1,1);
283
#-- should return 15.1
285
select round(15.4,1);
286
#-- should return 15.4
288
select round(15.5,1);
289
#-- should return 15.5
291
select round(15.6,1);
292
#-- should return 15.6
294
select round(15.9,1);
295
#-- should return 15.9
297
select round(-15.1,1);
298
#-- should return -15.1
300
select round(-15.4,1);
301
#-- should return -15.4
303
select round(-15.5,1);
304
#-- should return -15.5
306
select round(-15.6,1);
307
#-- should return -15.6
309
select round(-15.9,1);
310
#-- should return -15.9
312
select round(15.1,0);
315
select round(15.4,0);
318
select round(15.5,0);
321
select round(15.6,0);
324
select round(15.9,0);
327
select round(-15.1,0);
328
#-- should return -15
330
select round(-15.4,0);
331
#-- should return -15
333
select round(-15.5,0);
334
#-- should return -16
336
select round(-15.6,0);
337
#-- should return -16
339
select round(-15.9,0);
340
#-- should return -16
342
select round(15.1,-1);
345
select round(15.4,-1);
348
select round(15.5,-1);
351
select round(15.6,-1);
354
select round(15.9,-1);
357
select round(-15.1,-1);
358
#-- should return -20
360
select round(-15.4,-1);
361
#-- should return -20
363
select round(-15.5,-1);
364
#-- should return -20
366
select round(-15.6,-1);
367
#-- should return -20
369
select round(-15.91,-1);
370
#-- should return -20
372
select truncate(5678.123451,0);
373
#-- should return 5678
375
select truncate(5678.123451,1);
376
#-- should return 5678.1
378
select truncate(5678.123451,2);
379
#-- should return 5678.12
381
select truncate(5678.123451,3);
382
#-- should return 5678.123
384
select truncate(5678.123451,4);
385
#-- should return 5678.1234
387
select truncate(5678.123451,5);
388
#-- should return 5678.12345
390
select truncate(5678.123451,6);
391
#-- should return 5678.123451
393
select truncate(5678.123451,-1);
394
#-- should return 5670
396
select truncate(5678.123451,-2);
397
#-- should return 5600
399
select truncate(5678.123451,-3);
400
#-- should return 5000
402
select truncate(5678.123451,-4);
405
select truncate(-5678.123451,0);
406
#-- should return -5678
408
select truncate(-5678.123451,1);
409
#-- should return -5678.1
411
select truncate(-5678.123451,2);
412
#-- should return -5678.12
414
select truncate(-5678.123451,3);
415
#-- should return -5678.123
417
select truncate(-5678.123451,4);
418
#-- should return -5678.1234
420
select truncate(-5678.123451,5);
421
#-- should return -5678.12345
423
select truncate(-5678.123451,6);
424
#-- should return -5678.123451
426
select truncate(-5678.123451,-1);
427
#-- should return -5670
429
select truncate(-5678.123451,-2);
430
#-- should return -5600
432
select truncate(-5678.123451,-3);
433
#-- should return -5000
435
select truncate(-5678.123451,-4);
438
#drop table if exists wl1612_4;
439
create table wl1612_4 (col1 int, col2 decimal(30,25), col3 numeric(30,25));
441
insert into wl1612_4 values(1,0.0123456789012345678912345,0.0123456789012345678912345);
443
select col2/9999999999 from wl1612_4 where col1=1;
445
select col3/9999999999 from wl1612_4 where col1=1;
447
select 9999999999/col2 from wl1612_4 where col1=1;
449
select 9999999999/col3 from wl1612_4 where col1=1;
451
select col2*9999999999 from wl1612_4 where col1=1;
453
select col3*9999999999 from wl1612_4 where col1=1;
455
insert into wl1612_4 values(2,55555.0123456789012345678912345,55555.0123456789012345678912345);
457
select col2/9999999999 from wl1612_4 where col1=2;
459
select col3/9999999999 from wl1612_4 where col1=2;
461
select 9999999999/col2 from wl1612_4 where col1=2;
463
select 9999999999/col3 from wl1612_4 where col1=2;
465
select col2*9999999999 from wl1612_4 where col1=2;
467
select col3*9999999999 from wl1612_4 where col1=2;
474
#-- Additional tests for WL#1612 Precision math
476
#-- Comparisons should show that a number is
477
#-- exactly equal to its value as displayed.
481
select 23.4 + (-41.7), 23.4 - (41.7) = -18.3;
489
select 0.8 = 0.7 + 0.1;
492
#-- It should be possible to define a column
493
#-- with up to 38 digits precision either before
494
#-- or after the decimal point. Any number which
495
#-- is inserted, if it's within the range, should
496
#-- be exactly the same as the number that gets
499
drop table if exists t1;
501
create table t1 (col1 decimal(38));
503
insert into t1 values (12345678901234567890123456789012345678);
507
#+----------------------------------------+
509
#+----------------------------------------+
510
#| 12345678901234567890123456789012345678 |
511
#+----------------------------------------+
515
#create table t1 (col1 decimal(38,38));
517
#insert into t1 values (.12345678901234567890123456789012345678);
521
#+------------------------------------------+
523
#+------------------------------------------+
524
#| 0.12345678901234567890123456789012345678 |
525
#+------------------------------------------+
529
create table t1 (col1 decimal(31,30));
531
insert into t1 values (0.00000000001);
543
#-- The usual arithmetic operators / * + - should work.
545
#select 77777777777777777777777777777777777777 / 7777777777777777777777777777777777777 = 10;
546
#-- should return 0 (false).
548
select 7777777777777777777777777777777777777 * 10;
549
#-- should return 77777777777777777777777777777777777770
551
select .7777777777777777777777777777777777777 *
553
#-- should return 777777777777777777.7777777777777777777
555
select .7777777777777777777777777777777777777 - 0.1;
556
#-- should return .6777777777777777777777777777777777777
558
select .343434343434343434 + .343434343434343434;
559
#-- should return .686868686868686868
561
#-- 5. All arithmetic functions mentioned in the
562
#MySQL Reference Manual should work.
564
select abs(9999999999999999999999);
565
#-- should return 9999999999999999999999
567
select abs(-9999999999999999999999);
568
#-- should return 9999999999999999999999
570
select ceiling(999999999999999999);
571
select ceiling(99999999999999999999);
572
#-- should return 99999999999999999999
574
select ceiling(9.9999999999999999999);
577
select ceiling(-9.9999999999999999999);
580
select floor(999999999999999999);
581
select floor(9999999999999999999999);
582
#-- should return 9999999999999999999999
584
select floor(9.999999999999999999999);
587
select floor(-9.999999999999999999999);
588
#-- should return -10
590
select floor(-999999999999999999999.999);
591
select ceiling(999999999999999999999.999);
594
select 99999999999999999999999999999999999999 mod 3;
597
select round(99999999999999999.999);
598
#-- should return 100000000000000000
600
select round(-99999999999999999.999);
601
#-- should return -100000000000000000
603
select round(99999999999999999.999,3);
604
#-- should return 100000000000000000.000
606
select round(-99999999999999999.999,3);
607
#-- should return -100000000000000000.000
609
select truncate(99999999999999999999999999999999999999,31);
610
#-- should return 99999999999999999999999999999999999999.000
612
select truncate(99.999999999999999999999999999999999999,31);
613
#-- should return 99.9999999999999999999999999999999
615
select truncate(99999999999999999999999999999999999999,-31);
616
# should return 90000000000000000000000000000000
618
#-- 6. Set functions (AVG, SUM, COUNT) should work.
620
#drop table if exists t1;
624
#create procedure p1 () begin
625
# declare v1 int default 1; declare v2 decimal(0,38) default 0;
626
# create table t1 (col1 decimal(0,38));
627
# while v1 <= 10000 do
628
# insert into t1 values (-v2);
629
# set v2 = v2 + 0.00000000000000000000000000000000000001;
632
# select avg(col1),sum(col1),count(col1) from t1; end;//
636
# -- avg(col1)=0.00000000000000000000000000000000000001 added 10,000 times, then divided by 10,000
637
# -- sum(col1)=0.00000000000000000000000000000000000001 added 10,000 times
639
# -- count(col1)=10000
646
#-- When I say DECIMAL(x) I should be able to store x digits.
647
#-- If I can't, there should be an error at CREATE time.
649
#drop table if exists t1;
651
#create table t1 (col1 decimal(254));
652
#-- should return SQLSTATE 22003 numeric value out of range
654
#-- When I say DECIMAL(x,y) there should be no silent change of precision or
657
#drop table if exists t1;
659
#create table t1 (col1 decimal(0,38));
661
#show create table t1;
663
#+-------+--------------------------------+
664
#| Table | Create Table |
665
#+-------+--------------------------------+
666
#| t9 | CREATE TABLE `t1` ( |
667
#|`s1` decimal(0,38) default NULL |
668
#| ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
669
#+-------+--------------------------------+
673
#-- From WL#1612 "The future" point 2.:
674
#-- The standard requires that we treat numbers like "0.5" as
675
#-- DECIMAL or NUMERIC, not as floating-point.
677
#drop table if exists t1;
680
create table t1 as select 0.5;
682
show create table t1;
684
#+-------+-----------------------------------+
685
#| Table | Create Table |
686
#+-------+-----------------------------------+
687
#| t7 | CREATE TABLE `t1` ( |
688
#| `0.5` decimal(3,1) NOT NULL default '0.0' |
689
#| ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
690
#+-------+-----------------------------------+
694
#-- From WL#1612, "The future", point 3.: We have to start rounding correctly.
696
select round(1.5),round(2.5);
698
#+------------+------------+
699
#| round(1.5) | round(2.5) |
700
#+------------+------------+
702
#+------------+------------+
704
#-- From WL#1612, "The future", point 4.: "select 0.07 * 0.07;" should return 0.0049, not 0.00.
705
#-- If operand#1 has scale X and operand#2 has scale Y, then result should have scale (X+Y).
708
#-- should return 0.0049
710
#-- From WL#1612, "The future", point 5.: Division by zero is an error.
712
set sql_mode='traditional';
715
#-- should return 1 (true).
719
#-- should return SQLSTATE 22012 division by zero.
722
#-- should return SQLSTATE 22012 division by zero.
729
#1 row in set, 1 warning (0.00 sec)
731
#-- From WL#1612 "The future" point 6.: Overflow is an error.
735
#select 1E300 * 1E300;
736
#-- should return SQLSTATE 22003 numeric value out of range
738
#select 18446744073709551615 + 1;
739
#-- should return SQLSTATE 22003 numeric value out of range
741
#-- 14. From WL#1612 "The future" point 7.:
742
#-- If s1 is INTEGER and s2 is DECIMAL, then
743
#-- "create table tk7 as select avg(s1),avg(s2) from tk;"
744
#-- should not create a table with "double(17,4)" data types.
745
#-- The result of AVG must still be exact numeric, with a
746
#-- scale the same or greater than the operand's scale.
747
#-- The result of SUM must still be exact numeric, with
748
#-- a scale the same as the operand's scale.
750
#drop table if exists t1;
751
#drop table if exists t2;
753
#create table t1 (col1 int, col2 decimal(5));
755
#create table t2 as select avg(col1),avg(col2) from t1;
758
#show create table t2;
760
#+-------+---------------------------------+
761
#| Table | Create Table |
762
#+-------+---------------------------------+
763
#| t2 | CREATE TABLE `t2` ( |
764
#| `avg(col1)` decimal(17,4) default NULL, |
765
#| `avg(col2)` decimal(17,5) default NULL |
766
#| ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
767
#+-------+---------------------------------+
772
#-- From WL#1612 "The future" point 8.: Stop storing leading "+" signs and
775
#drop table if exists t1;
777
#create table t1 (col1 decimal(5,2),col2 decimal(5) zerofill, col3 decimal(3,1));
779
#insert into t1 values (1,1,1);
781
#select col1 from t1 union select col2 from t1 union select col3 from t1;
785
#-- From WL#1612, The future" point 9.:
786
#-- Accept the data type and precision and scale as the user
787
#-- asks, or return an error, but don't change to something else.
789
#drop table if exists t1;
791
#create table t1 (col1 numeric(4,2));
793
#show create table t1;
797
#-- The scripts in the following bugs should work:
800
#BUG#559 Maximum precision for DECIMAL column ...
801
#BUG#1499 INSERT/UPDATE into decimal field rounding problem
802
#BUG#1845 Not correctly recognising value for decimal field
803
#BUG#2493 Round function doesn't work correctly
804
#BUG#2649 round(0.5) gives 0 (should be 1)
805
#BUG#3612 impicite rounding of VARCHARS during aritchmetic operations...
806
#BUG#3722 SELECT fails for certain values in Double(255,10) column.
807
#BUG#4485 Floating point conversions are inconsistent
809
#BUG#5931 Out-of-range values are accepted
810
#BUG#6048 Stored procedure causes operating system reboot
811
#BUG#6053 DOUBLE PRECISION literal
813
# Tests from 'traditional' mode tests
815
set sql_mode='ansi,traditional';
817
CREATE TABLE Sow6_2f (col1 NUMERIC(4,2));
819
INSERT INTO Sow6_2f VALUES (10.55);
821
INSERT INTO Sow6_2f VALUES (10.5555);
823
INSERT INTO Sow6_2f VALUES (-10.55);
825
INSERT INTO Sow6_2f VALUES (-10.5555);
827
INSERT INTO Sow6_2f VALUES (11);
830
INSERT INTO Sow6_2f VALUES (101.55);
831
#-- should return SQLSTATE 22003 numeric value out of range
833
UPDATE Sow6_2f SET col1 = col1 * 50 WHERE col1 = 11;
834
#-- should return SQLSTATE 22003 numeric value out of range
836
UPDATE Sow6_2f SET col1 = col1 / 0 WHERE col1 > 0;
837
#-- should return SQLSTATE 22012 division by zero
838
SELECT MOD(col1,0) FROM Sow6_2f;
839
#-- should return SQLSTATE 22012 division by zero
841
INSERT INTO Sow6_2f VALUES ('a59b');
842
#-- should return SQLSTATE 22018 invalid character value for cast
848
select 10.3330000000000/12.34500000;
860
--disable_ps_protocol
861
select 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 as x;
862
select 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 + 1 as x;
867
select 0.190287977636363637 + 0.040372670 * 0 - 0;
877
CREATE TABLE t1 (f1 DECIMAL (12,9), f2 DECIMAL(2,2));
878
INSERT INTO t1 VALUES (10.5, 0);
879
UPDATE t1 SET f1 = 4.5;
882
CREATE TABLE t1 (f1 DECIMAL (64,20), f2 DECIMAL(2,2));
883
INSERT INTO t1 VALUES (9999999999999999999999999999999999, 0);
888
# Bug #10599: problem with NULL
895
# Bug #9894 (negative to unsigned column)
897
set @@sql_mode='traditional';
898
create table t1( d1 decimal(18) unsigned, d2 decimal(20) unsigned, d3 decimal (22) unsigned);
900
insert into t1 values(1,-1,-1);
902
create table t1 (col1 decimal(5,2), col2 numeric(5,2));
904
insert into t1 values (999.999,999.999);
906
insert into t1 values (-999.999,-999.999);
912
# Bug #8425 (insufficient precision of the division)
914
set @sav_dpi= @@div_precision_increment;
915
set @@div_precision_increment=15;
916
create table t1 (col1 int, col2 decimal(30,25), col3 numeric(30,25));
917
insert into t1 values (1,0.0123456789012345678912345,0.0123456789012345678912345);
918
select col2/9999999999 from t1 where col1=1;
919
select 9999999999/col2 from t1 where col1=1;
920
select 77777777/7777777;
922
set div_precision_increment= @sav_dpi;
925
# Bug #10896 (0.00 > -0.00)
927
create table t1 (a decimal(4,2));
928
insert into t1 values (0.00);
929
select * from t1 where a > -0.00;
930
select * from t1 where a = -0.00;
934
# Bug #11215: a problem with LONGLONG_MIN
937
create table t1 (col1 bigint default -9223372036854775808);
938
insert into t1 values (default);
943
# Bug #10891 (converting to decimal crashes server)
945
select cast('1.00000001335143196001808973960578441619873046875E-10' as decimal(30,15));
948
# Bug #11708 (conversion to decimal fails in decimal part)
950
select ln(14000) c1, convert(ln(14000),decimal(5,3)) c2, cast(ln(14000) as decimal(5,3)) c3;
952
select convert(ln(14000),decimal(2,3)) c1;
954
select cast(ln(14000) as decimal(2,3)) c1;
957
# Bug #8449 (Silent column changes)
960
create table t1 (sl decimal(70,30));
962
create table t1 (sl decimal(32,31));
964
create table t1 (sl decimal(0,38));
966
create table t1 (sl decimal(0,30));
967
create table t1 (sl decimal(5, 5));
968
show create table t1;
971
create table t1 (sl decimal(65, 30));
972
show create table t1;
976
# Bug 11557 (DEFAULT values rounded improperly
979
f1 decimal unsigned not null default 17.49,
980
f2 decimal unsigned not null default 17.68,
981
f3 decimal unsigned not null default 99.2,
982
f4 decimal unsigned not null default 99.7,
983
f5 decimal unsigned not null default 104.49,
984
f6 decimal unsigned not null default 199.91,
985
f7 decimal unsigned not null default 999.9,
986
f8 decimal unsigned not null default 9999.99);
987
insert into t1 (f1) values (1);
992
# Bug 12173 (show create table fails)
995
f0 decimal (30,30) zerofill not null DEFAULT 0,
996
f1 decimal (0,0) zerofill not null default 0);
997
show create table t1;
1001
# Bug 12938 (arithmetic loop's zero)
1004
drop procedure if exists wg2;
1007
create procedure wg2()
1009
declare v int default 1;
1010
declare tdec decimal(5) default 0;
1011
while v <= 9 do set tdec =tdec * 10;
1023
# Bug #12979 Stored procedures: crash if inout decimal parameter
1024
# (not a SP bug in fact)
1027
select cast(@non_existing_user_var/2 as DECIMAL);
1030
# Bug #13667 (Inconsistency for decimal(m,d) specification
1033
create table t (d decimal(0,10));
1036
# Bug #14268 (bad FLOAT->DECIMAL conversion)
1042
my_varchar VARCHAR(50),
1043
my_decimal DECIMAL(65,30)
1045
SHOW CREATE TABLE t1;
1050
eval INSERT INTO t1 SET my_float = 1.175494345e-$max_power,
1051
my_double = 1.175494345e-$max_power,
1052
my_varchar = '1.175494345e-$max_power';
1055
SELECT my_float, my_double, my_varchar FROM t1;
1057
# Expected result 0.000000000011754943372854760000
1058
# On windows we get 0.000000000011754943372854770000
1059
# use replace_result to correct it
1060
--replace_result 0.000000000011754943372854770000 0.000000000011754943372854760000
1061
SELECT CAST(my_float AS DECIMAL(65,30)), my_float FROM t1;
1062
SELECT CAST(my_double AS DECIMAL(65,30)), my_double FROM t1;
1063
SELECT CAST(my_varchar AS DECIMAL(65,30)), my_varchar FROM t1;
1065
# We have to disable warnings here as the test in
1066
# Field_new_decimal::store(double):
1068
# fails randomly depending on compiler options
1071
UPDATE t1 SET my_decimal = my_float;
1073
# Expected result 0.000000000011754943372854760000
1074
# On windows we get 0.000000000011754943372854770000
1075
# use replace_result to correct it
1076
--replace_result 0.000000000011754943372854770000 0.000000000011754943372854760000
1077
SELECT my_decimal, my_float FROM t1;
1079
UPDATE t1 SET my_decimal = my_double;
1080
SELECT my_decimal, my_double FROM t1;
1082
UPDATE t1 SET my_decimal = my_varchar;
1083
SELECT my_decimal, my_varchar FROM t1;
1088
# Bug #13573 (Wrong data inserted for too big values)
1091
create table t1 (c1 decimal(64));
1092
--disable_ps_protocol
1093
insert into t1 values(
1094
89000000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
1095
insert into t1 values(
1096
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 *
1097
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999);
1098
--enable_ps_protocol
1099
insert into t1 values(1e100);
1104
# Bug #18014: problem with 'alter table'
1107
create table t1(a decimal(7,2));
1108
insert into t1 values(123.12);
1110
alter table t1 modify a decimal(10,2);
1115
# Bug#19667 group by a decimal expression yields wrong result
1117
create table t1 (i int, j int);
1118
insert into t1 values (1,1), (1,2), (2,3), (2,4);
1119
select i, count(distinct j) from t1 group by i;
1120
select i+0.0 as i2, count(distinct j) from t1 group by i2;
1123
create table t1(f1 decimal(20,6));
1124
insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond);
1125
insert into t1 values (CAST('10:11:12' AS time));
1130
# Bug #8663 (cant use bigint as input to CAST)
1132
select cast(19999999999999999999 as unsigned);
1135
# Bug #24558: Increasing decimal column length causes data loss
1137
create table t1(a decimal(18));
1138
insert into t1 values(123456789012345678);
1139
alter table t1 modify column a decimal(19);
1144
# Bug #27957 cast as decimal does not check overflow, also inconsistent with group, subselect
1147
select cast(11.1234 as DECIMAL(3,2));
1148
select * from (select cast(11.1234 as DECIMAL(3,2))) t;
1150
select cast(a as DECIMAL(3,2))
1151
from (select 11.1233 as a
1152
UNION select 11.1234
1153
UNION select 12.1234
1156
select cast(a as DECIMAL(3,2)), count(*)
1157
from (select 11.1233 as a
1158
UNION select 11.1234
1159
UNION select 12.1234
1163
# Bug #28361 Buffer overflow in DECIMAL code on Windows
1166
create table t1 (s varchar(100));
1167
insert into t1 values (0.00000000010000000000000000364321973154977415791655470655996396089904010295867919921875);
1171
# Bug #27984 Long Decimal Maths produces truncated results
1174
SELECT 1.000000000000 * 99.999999999998 / 100 a,1.000000000000 * (99.999999999998 / 100) b;
1177
# Bug #29415: CAST AS DECIMAL(P,S) with too big precision/scale
1180
SELECT CAST(1 AS decimal(65,10));
1181
--error ER_TOO_BIG_PRECISION
1182
SELECT CAST(1 AS decimal(66,10));
1184
SELECT CAST(1 AS decimal(65,30));
1185
--error ER_TOO_BIG_SCALE
1186
SELECT CAST(1 AS decimal(65,31));
1188
CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL);
1189
INSERT INTO t1 VALUES (3,30), (1,10), (2,10);
1190
SELECT a+CAST(1 AS decimal(65,30)) AS aa, SUM(b) FROM t1 GROUP BY aa;
1191
--error ER_TOO_BIG_SCALE
1192
SELECT a+CAST(1 AS decimal(65,31)) AS aa, SUM(b) FROM t1 GROUP BY aa;
1197
# Bug #29417: assertion abort for a grouping query with decimal user variable
1200
CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL);
1201
INSERT INTO t1 VALUES (3,30), (1,10), (2,10);
1203
SET @a= CAST(1 AS decimal);
1204
SELECT 1 FROM t1 GROUP BY @b := @a, @b;
1209
# Bug #24907: unpredictable (display) precission, if input precission
1213
# As per 10.1.1. Overview of Numeric Types, type (new) DECIMAL has a
1214
# maxmimum precision of 30 places after the decimal point. Show that
1215
# temp field creation beyond that works and throws a truncation warning.
1216
# DECIMAL(37,36) should be adjusted to DECIMAL(31,30).
1217
CREATE TABLE t1 SELECT 0.123456789012345678901234567890123456 AS f1;
1222
# too many decimal places, AND too many digits altogether (90 = 45+45).
1223
# should preserve integers (65 = 45+20)
1224
CREATE TABLE t1 SELECT 123451234512345123451234512345123451234512345.678906789067890678906789067890678906789067890 AS f1;
1230
# Bug #36270: incorrect calculation result - works in 4.1 but not in 5.0 or 5.1
1233
# show that if we need to truncate the scale of an operand, we pick the
1234
# right one (that is, we discard the least significant decimal places)
1235
select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 *
1236
1.01500000 * 1.01500000 * 0.99500000);
1239
# Bug #31616 div_precision_increment description looks wrong
1242
create table t1 as select 5.05 / 0.014;
1244
show create table t1;
1248
--echo End of 5.0 tests
1251
# Bug#16172 DECIMAL data type processed incorrectly
1253
select cast(143.481 as decimal(4,1));
1254
select cast(143.481 as decimal(4,0));
1255
select cast(143.481 as decimal(2,1));
1256
select cast(-3.4 as decimal(2,1));
1257
select cast(99.6 as decimal(2,0));
1258
select cast(-13.4 as decimal(2,1));
1259
select cast(98.6 as decimal(2,0));
1262
--echo # Bug #45262: Bad effects with CREATE TABLE and DECIMAL
1265
CREATE TABLE t1 SELECT .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col;
1267
SELECT my_col FROM t1;
1270
CREATE TABLE t1 SELECT 1 + .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col;
1272
SELECT my_col FROM t1;
1275
CREATE TABLE t1 SELECT 1 * .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col;
1277
SELECT my_col FROM t1;
1280
CREATE TABLE t1 SELECT 1 / .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col;
1282
SELECT my_col FROM t1;
1285
CREATE TABLE t1 SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col;
1287
SELECT my_col FROM t1;
1291
--echo # Bug#45261: Crash, stored procedure + decimal
1295
DROP TABLE IF EXISTS t1;
1298
CREATE TABLE t1 SELECT
1299
/* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001
1305
CREATE TABLE t1 SELECT
1306
/* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001.
1312
CREATE TABLE t1 SELECT
1313
/* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001.1 /* 1 */
1319
CREATE TABLE t1 SELECT
1320
/* 82 */ 1000000000000000000000000000000000000000000000000000000000000000000000000000000001
1326
CREATE TABLE t1 SELECT
1327
/* 40 */ 1000000000000000000000000000000000000001.1000000000000000000000000000000000000001 /* 40 */
1333
CREATE TABLE t1 SELECT
1334
/* 1 */ 1.10000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 80 */
1340
CREATE TABLE t1 SELECT
1341
/* 1 */ 1.100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81 */
1347
CREATE TABLE t1 SELECT
1348
.100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81 */
1354
CREATE TABLE t1 SELECT
1355
/* 45 */ 123456789012345678901234567890123456789012345.123456789012345678901234567890123456789012345 /* 45 */
1361
CREATE TABLE t1 SELECT
1362
/* 65 */ 12345678901234567890123456789012345678901234567890123456789012345.1 /* 1 */
1368
CREATE TABLE t1 SELECT
1369
/* 66 */ 123456789012345678901234567890123456789012345678901234567890123456.1 /* 1 */
1375
CREATE TABLE t1 SELECT
1376
.123456789012345678901234567890123456789012345678901234567890123456 /* 66 */
1382
CREATE TABLE t1 AS SELECT 123.1234567890123456789012345678901 /* 31 */ AS c1;
1387
CREATE TABLE t1 SELECT 1.1 + CAST(1 AS DECIMAL(65,30)) AS c1;
1393
--echo # Test that the integer and decimal parts are properly calculated.
1396
CREATE TABLE t1 (a DECIMAL(30,30));
1397
INSERT INTO t1 VALUES (0.1),(0.2),(0.3);
1398
CREATE TABLE t2 SELECT MIN(a + 0.0000000000000000000000000000001) AS c1 FROM t1;
1402
CREATE TABLE t1 (a DECIMAL(30,30));
1403
INSERT INTO t1 VALUES (0.1),(0.2),(0.3);
1404
CREATE TABLE t2 SELECT IFNULL(a + 0.0000000000000000000000000000001, NULL) AS c1 FROM t1;
1408
CREATE TABLE t1 (a DECIMAL(30,30));
1409
INSERT INTO t1 VALUES (0.1),(0.2),(0.3);
1410
CREATE TABLE t2 SELECT CASE a WHEN 0.1 THEN 0.0000000000000000000000000000000000000000000000000000000000000000001 END AS c1 FROM t1;
1415
--echo # Test that variables get maximum precision.
1419
CREATE TABLE t1 SELECT @decimal AS c1;
1425
--echo # Bug #45261 : Crash, stored procedure + decimal
1426
--echo # Original test by the reporter.
1429
--echo # should not crash
1431
SELECT .123456789012345678901234567890123456789012345678901234567890123456 AS a;
1435
CREATE PROCEDURE test_proc()
1437
# The las non critical CUSER definition is:
1438
# DECLARE mycursor CURSOR FOR SELECT 1 %
1439
# .12345678912345678912345678912345678912345678912345678912345678912 AS my_col;
1440
DECLARE mycursor CURSOR FOR
1442
.123456789123456789123456789123456789123456789123456789123456789123456789123456789
1449
--echo # should not crash
1451
DROP PROCEDURE test_proc;
1454
--echo # Bug #48370 Absolutely wrong calculations with GROUP BY and
1455
--echo # decimal fields when using IF
1458
CREATE TABLE currencies (id int, rate decimal(16,4),
1459
PRIMARY KEY (id), KEY (rate));
1461
INSERT INTO currencies VALUES (11,0.7028);
1462
INSERT INTO currencies VALUES (1,1);
1464
CREATE TABLE payments (
1471
KEY currency_id (currency_id),
1472
KEY supplier_id (supplier_id)
1475
INSERT INTO payments (id,status,vat,supplier_id,currency_id) VALUES
1476
(3001,2,0.0000,344,11), (1,2,0.0000,1,1);
1478
CREATE TABLE sub_tasks (
1481
price decimal(16,4),
1482
discount decimal(10,4),
1485
KEY currency_id (currency_id),
1486
KEY payment_id (payment_id)
1489
INSERT INTO sub_tasks (id, price, discount, payment_id, currency_id) VALUES
1490
(52, 12.60, 0, 3001, 11), (56, 14.58, 0, 3001, 11);
1492
--echo # should return 1 and the same values in col 2 and 3
1493
select STRAIGHT_JOIN
1494
(1 + PAY.vat) AS mult,
1495
SUM(ROUND((SUB.price - ROUND(ROUND(SUB.price, 2) * SUB.discount, 2)) *
1496
CUR.rate / CUR.rate, 2)
1497
) v_net_with_discount,
1499
SUM(ROUND((SUB.price - ROUND(ROUND(SUB.price, 2) * SUB.discount, 1)) *
1500
CUR.rate / CUR.rate , 2)
1504
currencies CUR, payments PAY, sub_tasks SUB
1506
SUB.payment_id = PAY.id and
1507
PAY.currency_id = CUR.id and
1509
group by PAY.id + 1;
1511
DROP TABLE currencies, payments, sub_tasks;
1514
--echo End of 5.1 tests