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
# The following statement produces results with garbage past
1058
# the significant digits. Improving it is a part of the WL#3977.
1059
SELECT CAST(my_float AS DECIMAL(65,30)), my_float FROM t1;
1060
SELECT CAST(my_double AS DECIMAL(65,30)), my_double FROM t1;
1061
SELECT CAST(my_varchar AS DECIMAL(65,30)), my_varchar FROM t1;
1063
# We have to disable warnings here as the test in
1064
# Field_new_decimal::store(double):
1066
# fails randomly depending on compiler options
1069
UPDATE t1 SET my_decimal = my_float;
1071
# Expected result 0.000000000011754943372854760000
1072
# On windows we get 0.000000000011754943372854770000
1073
# use replace_result to correct it
1074
--replace_result 0.000000000011754943372854770000 0.000000000011754943372854760000
1075
SELECT my_decimal, my_float FROM t1;
1077
UPDATE t1 SET my_decimal = my_double;
1078
SELECT my_decimal, my_double FROM t1;
1080
UPDATE t1 SET my_decimal = my_varchar;
1081
SELECT my_decimal, my_varchar FROM t1;
1086
# Bug #13573 (Wrong data inserted for too big values)
1089
create table t1 (c1 decimal(64));
1090
--disable_ps_protocol
1091
insert into t1 values(
1092
89000000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
1093
insert into t1 values(
1094
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 *
1095
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999);
1096
--enable_ps_protocol
1097
insert into t1 values(1e100);
1102
# Bug #18014: problem with 'alter table'
1105
create table t1(a decimal(7,2));
1106
insert into t1 values(123.12);
1108
alter table t1 modify a decimal(10,2);
1113
# Bug#19667 group by a decimal expression yields wrong result
1115
create table t1 (i int, j int);
1116
insert into t1 values (1,1), (1,2), (2,3), (2,4);
1117
select i, count(distinct j) from t1 group by i;
1118
select i+0.0 as i2, count(distinct j) from t1 group by i2;
1121
create table t1(f1 decimal(20,6));
1122
insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond);
1123
insert into t1 values (CAST('10:11:12' AS time));
1128
# Bug #8663 (cant use bigint as input to CAST)
1130
select cast(19999999999999999999 as unsigned);
1133
# Bug #24558: Increasing decimal column length causes data loss
1135
create table t1(a decimal(18));
1136
insert into t1 values(123456789012345678);
1137
alter table t1 modify column a decimal(19);
1142
# Bug #27957 cast as decimal does not check overflow, also inconsistent with group, subselect
1145
select cast(11.1234 as DECIMAL(3,2));
1146
select * from (select cast(11.1234 as DECIMAL(3,2))) t;
1148
select cast(a as DECIMAL(3,2))
1149
from (select 11.1233 as a
1150
UNION select 11.1234
1151
UNION select 12.1234
1154
select cast(a as DECIMAL(3,2)), count(*)
1155
from (select 11.1233 as a
1156
UNION select 11.1234
1157
UNION select 12.1234
1161
# Bug #28361 Buffer overflow in DECIMAL code on Windows
1164
create table t1 (s varchar(100));
1165
insert into t1 values (0.00000000010000000000000000364321973154977415791655470655996396089904010295867919921875);
1169
# Bug #27984 Long Decimal Maths produces truncated results
1172
SELECT 1.000000000000 * 99.999999999998 / 100 a,1.000000000000 * (99.999999999998 / 100) b;
1175
# Bug #29415: CAST AS DECIMAL(P,S) with too big precision/scale
1178
SELECT CAST(1 AS decimal(65,10));
1179
--error ER_TOO_BIG_PRECISION
1180
SELECT CAST(1 AS decimal(66,10));
1182
SELECT CAST(1 AS decimal(65,30));
1183
--error ER_TOO_BIG_SCALE
1184
SELECT CAST(1 AS decimal(65,31));
1186
CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL);
1187
INSERT INTO t1 VALUES (3,30), (1,10), (2,10);
1188
SELECT a+CAST(1 AS decimal(65,30)) AS aa, SUM(b) FROM t1 GROUP BY aa;
1189
--error ER_TOO_BIG_SCALE
1190
SELECT a+CAST(1 AS decimal(65,31)) AS aa, SUM(b) FROM t1 GROUP BY aa;
1195
# Bug #29417: assertion abort for a grouping query with decimal user variable
1198
CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL);
1199
INSERT INTO t1 VALUES (3,30), (1,10), (2,10);
1201
SET @a= CAST(1 AS decimal);
1202
SELECT 1 FROM t1 GROUP BY @b := @a, @b;
1207
# Bug #24907: unpredictable (display) precission, if input precission
1211
# As per 10.1.1. Overview of Numeric Types, type (new) DECIMAL has a
1212
# maxmimum precision of 30 places after the decimal point. Show that
1213
# temp field creation beyond that works and throws a truncation warning.
1214
# DECIMAL(37,36) should be adjusted to DECIMAL(31,30).
1215
CREATE TABLE t1 SELECT 0.123456789012345678901234567890123456 AS f1;
1220
# too many decimal places, AND too many digits altogether (90 = 45+45).
1221
# should preserve integers (65 = 45+20)
1222
CREATE TABLE t1 SELECT 123451234512345123451234512345123451234512345.678906789067890678906789067890678906789067890 AS f1;
1227
--echo End of 5.0 tests
1230
# Bug#16172 DECIMAL data type processed incorrectly
1232
select cast(143.481 as decimal(4,1));
1233
select cast(143.481 as decimal(4,0));
1234
select cast(143.481 as decimal(2,1));
1235
select cast(-3.4 as decimal(2,1));
1236
select cast(99.6 as decimal(2,0));
1237
select cast(-13.4 as decimal(2,1));
1238
select cast(98.6 as decimal(2,0));