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);
37
--replace_regex /ENGINE=[a-z]*/ENGINE=X/i
44
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);
46
--replace_regex /ENGINE=[a-z]*/ENGINE=X/i
51
# saving in decimal field with overflow
54
create table t1 (a decimal(4,2));
55
--error ER_WARN_DATA_OUT_OF_RANGE
56
insert into t1 value (10000), (1.1e10), ("11111"), (100000.1);
57
--error ER_WARN_DATA_OUT_OF_RANGE
58
insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
61
create table t1 (a decimal(4,2));
62
--error ER_WARN_DATA_OUT_OF_RANGE
63
insert into t1 value (10000), (1.1e10), ("11111"), (100000.1);
64
--error ER_WARN_DATA_OUT_OF_RANGE
65
insert into t1 value (-10000), (-1.1e10), ("-11111"), (-100000.1);
71
# saving in field with overflow from decimal
73
create table t1 (a bigint);
74
--error ER_WARN_DATA_OUT_OF_RANGE
75
insert into t1 values (18446744073709551615.0);
76
--error ER_WARN_DATA_OUT_OF_RANGE
77
insert into t1 values (9223372036854775808.0);
78
--error ER_WARN_DATA_OUT_OF_RANGE
79
insert into t1 values (-18446744073709551615.0);
82
create table t1 (a bigint);
83
insert into t1 values (9223372036854775807.0);
84
--error ER_WARN_DATA_OUT_OF_RANGE
85
insert into t1 values (9999999999999999999999999.000);
86
insert into t1 values (-1.0);
89
create table t1 (a int);
90
--error ER_WARN_DATA_OUT_OF_RANGE
91
insert into t1 values (18446744073709551615.0);
92
--error ER_WARN_DATA_OUT_OF_RANGE
93
insert into t1 values (9223372036854775808.0);
98
# test that functions create decimal fields
100
create table t1 select round(15.4,-1), truncate(-5678.123451,-3), abs(-1.1), -(-1.1);
101
--replace_regex /ENGINE=[a-z]*/ENGINE=X/i
102
show create table t1;
109
#create table wl1612 (col1 int, col2 decimal(38,10), col3 numeric(38,10));
110
#insert into wl1612 values(1,12345678901234567890.1234567890,12345678901234567890.1234567890);
111
#select * from wl1612;
112
#insert into wl1612 values(2,01234567890123456789.0123456789,01234567890123456789.0123456789);
113
#select * from wl1612 where col1=2;
114
#insert into wl1612 values(3,1234567890123456789012345678.0123456789,1234567890123456789012345678.0123456789);
115
#select * from wl1612 where col1=3;
117
#select col1/0 from wl1612;
118
#select col2/0 from wl1612;
119
#select col3/0 from wl1612;
121
#insert into wl1612 values(5,5000.0005,5000.0005);
122
#insert into wl1612 values(6,5000.0005,5000.0005);
123
#select sum(col2),sum(col3) from wl1612;
124
#select avg(col2),avg(col3) from wl1612;
126
#insert into wl1612 values(7,500000.000005,500000.000005);
127
#insert into wl1612 values(8,500000.000005,500000.000005);
128
#select sum(col2),sum(col3) from wl1612 where col1>4;
129
#select avg(col2),avg(col3) from wl1612 where col1>4;
131
#insert into wl1612 (col1,col2) values(9,123456789012345678901234567890);
132
#insert into wl1612 (col1,col3) values(9,123456789012345678901234567890);
134
#insert into wl1612 (col1, col2) values(9,1.01234567891);
135
#insert into wl1612 (col1, col2) values(10,1.01234567894);
136
#insert into wl1612 (col1, col2) values(11,1.01234567895);
137
#insert into wl1612 (col1, col2) values(12,1.01234567896);
138
#select col1,col2 from wl1612 where col1>8;
140
#insert into wl1612 (col1, col3) values(13,1.01234567891);
141
#insert into wl1612 (col1, col3) values(14,1.01234567894);
142
#insert into wl1612 (col1, col3) values(15,1.01234567895);
143
#insert into wl1612 (col1, col3) values(16,1.01234567896);
144
#select col1,col3 from wl1612 where col1>12;
146
#select col1 from wl1612 where col1>4 and col2=1.01234567891;
147
#-- should return 0 rows
149
#select col1 from wl1612 where col1>4 and col2=1.0123456789;
150
#-- should return col1 values 9 & 10
152
#select col1 from wl1612 where col1>4 and col2<>1.0123456789;
153
#-- should return col1 values 5,6,7,8,11,12
155
#select col1 from wl1612 where col1>4 and col2<1.0123456789;
156
#-- should return 0 rows
158
#select col1 from wl1612 where col1>4 and col2<=1.0123456789;
159
#-- should return col1 values 9 & 10
161
#select col1 from wl1612 where col1>4 and col2>1.0123456789;
162
#-- should return col1 values 5,6,7,8,11,12
164
#select col1 from wl1612 where col1>4 and col2>=1.0123456789;
165
#-- should return col1 values 5,6,7,8,910,11,12
167
#select col1, col2 from wl1612 where col1=11 or col1=12;
168
#select col1 from wl1612 where col1>4 and col2=1.012345679;
169
#-- should return col1 values 11,12
171
#select col1 from wl1612 where col1>4 and col2<>1.012345679;
172
#-- should return col1 values 5,6,7,8,9,10
174
#select col1 from wl1612 where col1>4 and col3=1.01234567891;
175
#-- should return 0 rows
177
#select col1 from wl1612 where col1>4 and col3=1.0123456789;
178
#-- should return col1 values 13,14
180
#select col1 from wl1612 where col1>4 and col3<>1.0123456789;
181
#-- should return col1 values 5,6,7,8,15,16
183
#select col1 from wl1612 where col1>4 and col3<1.0123456789;
184
#-- should return 0 rows
186
#select col1 from wl1612 where col1>4 and col3<=1.0123456789;
187
#-- should return col1 values 13,14
189
#select col1 from wl1612 where col1>4 and col3>1.0123456789;
190
#-- should return col1 values 5,6,7,8,15,16
192
#select col1 from wl1612 where col1>4 and col3>=1.0123456789;
193
#-- should return col1 values 5,6,7,8,13,14,15,16
195
#select col1 from wl1612 where col1>4 and col3=1.012345679;
196
#-- should return col1 values 15,16
198
#select col1 from wl1612 where col1>4 and col3<>1.012345679;
199
#-- should return col1 values 5,6,7,8,13,14
206
#-- should return 1 (true)
210
create table wl1612_1 (col1 int);
211
insert into wl1612_1 values(10);
213
select * from wl1612_1 where 0.8=0.7+0.1;
214
#--should return 1 row (col1=10)
216
select 0.07+0.07 from wl1612_1;
218
select 0.07-0.07 from wl1612_1;
220
select 0.07*0.07 from wl1612_1;
222
select 0.07/0.07 from wl1612_1;
226
create table wl1612_2 (col1 decimal(10,2), col2 numeric(10,2));
227
insert into wl1612_2 values(1,1);
228
insert into wl1612_2 values(+1,+1);
229
insert into wl1612_2 values(+01,+01);
230
insert into wl1612_2 values(+001,+001);
232
select col1,count(*) from wl1612_2 group by col1;
234
select col2,count(*) from wl1612_2 group by col2;
238
create table wl1612_3 (col1 decimal(10,2), col2 numeric(10,2));
239
insert into wl1612_3 values('1','1');
240
insert into wl1612_3 values('+1','+1');
242
insert into wl1612_3 values('+01','+01');
243
insert into wl1612_3 values('+001','+001');
245
select col1,count(*) from wl1612_3 group by col1;
247
select col2,count(*) from wl1612_3 group by col2;
254
select mod(234.567,10.555);
255
#-- should return 2.357
257
select mod(-234.567,10.555);
258
#-- should return -2.357
260
select mod(234.567,-10.555);
261
#-- should return 2.357
279
#-- should return -15
282
#-- should return -15
285
#-- should return -16
288
#-- should return -16
291
#-- should return -16
293
select round(15.1,1);
294
#-- should return 15.1
296
select round(15.4,1);
297
#-- should return 15.4
299
select round(15.5,1);
300
#-- should return 15.5
302
select round(15.6,1);
303
#-- should return 15.6
305
select round(15.9,1);
306
#-- should return 15.9
308
select round(-15.1,1);
309
#-- should return -15.1
311
select round(-15.4,1);
312
#-- should return -15.4
314
select round(-15.5,1);
315
#-- should return -15.5
317
select round(-15.6,1);
318
#-- should return -15.6
320
select round(-15.9,1);
321
#-- should return -15.9
323
select round(15.1,0);
326
select round(15.4,0);
329
select round(15.5,0);
332
select round(15.6,0);
335
select round(15.9,0);
338
select round(-15.1,0);
339
#-- should return -15
341
select round(-15.4,0);
342
#-- should return -15
344
select round(-15.5,0);
345
#-- should return -16
347
select round(-15.6,0);
348
#-- should return -16
350
select round(-15.9,0);
351
#-- should return -16
353
select round(15.1,-1);
356
select round(15.4,-1);
359
select round(15.5,-1);
362
select round(15.6,-1);
365
select round(15.9,-1);
368
select round(-15.1,-1);
369
#-- should return -20
371
select round(-15.4,-1);
372
#-- should return -20
374
select round(-15.5,-1);
375
#-- should return -20
377
select round(-15.6,-1);
378
#-- should return -20
380
select round(-15.91,-1);
381
#-- should return -20
383
select truncate(5678.123451,0);
384
#-- should return 5678
386
select truncate(5678.123451,1);
387
#-- should return 5678.1
389
select truncate(5678.123451,2);
390
#-- should return 5678.12
392
select truncate(5678.123451,3);
393
#-- should return 5678.123
395
select truncate(5678.123451,4);
396
#-- should return 5678.1234
398
select truncate(5678.123451,5);
399
#-- should return 5678.12345
401
select truncate(5678.123451,6);
402
#-- should return 5678.123451
404
select truncate(5678.123451,-1);
405
#-- should return 5670
407
select truncate(5678.123451,-2);
408
#-- should return 5600
410
select truncate(5678.123451,-3);
411
#-- should return 5000
413
select truncate(5678.123451,-4);
416
select truncate(-5678.123451,0);
417
#-- should return -5678
419
select truncate(-5678.123451,1);
420
#-- should return -5678.1
422
select truncate(-5678.123451,2);
423
#-- should return -5678.12
425
select truncate(-5678.123451,3);
426
#-- should return -5678.123
428
select truncate(-5678.123451,4);
429
#-- should return -5678.1234
431
select truncate(-5678.123451,5);
432
#-- should return -5678.12345
434
select truncate(-5678.123451,6);
435
#-- should return -5678.123451
437
select truncate(-5678.123451,-1);
438
#-- should return -5670
440
select truncate(-5678.123451,-2);
441
#-- should return -5600
443
select truncate(-5678.123451,-3);
444
#-- should return -5000
446
select truncate(-5678.123451,-4);
449
#drop table if exists wl1612_4;
450
create table wl1612_4 (col1 int, col2 decimal(30,25), col3 numeric(30,25));
452
insert into wl1612_4 values(1,0.0123456789012345678912345,0.0123456789012345678912345);
454
select col2/9999999999 from wl1612_4 where col1=1;
456
select col3/9999999999 from wl1612_4 where col1=1;
458
select 9999999999/col2 from wl1612_4 where col1=1;
460
select 9999999999/col3 from wl1612_4 where col1=1;
462
select col2*9999999999 from wl1612_4 where col1=1;
464
select col3*9999999999 from wl1612_4 where col1=1;
466
insert into wl1612_4 values(2,55555.0123456789012345678912345,55555.0123456789012345678912345);
468
select col2/9999999999 from wl1612_4 where col1=2;
470
select col3/9999999999 from wl1612_4 where col1=2;
472
select 9999999999/col2 from wl1612_4 where col1=2;
474
select 9999999999/col3 from wl1612_4 where col1=2;
476
select col2*9999999999 from wl1612_4 where col1=2;
478
select col3*9999999999 from wl1612_4 where col1=2;
485
#-- Additional tests for WL#1612 Precision math
487
#-- Comparisons should show that a number is
488
#-- exactly equal to its value as displayed.
490
select 23.4 + (-41.7), 23.4 - (41.7) = -18.3;
498
select 0.8 = 0.7 + 0.1;
501
#-- It should be possible to define a column
502
#-- with up to 38 digits precision either before
503
#-- or after the decimal point. Any number which
504
#-- is inserted, if it's within the range, should
505
#-- be exactly the same as the number that gets
508
drop table if exists t1;
510
create table t1 (col1 decimal(38));
512
insert into t1 values (12345678901234567890123456789012345678);
516
#+----------------------------------------+
518
#+----------------------------------------+
519
#| 12345678901234567890123456789012345678 |
520
#+----------------------------------------+
524
#create table t1 (col1 decimal(38,38));
526
#insert into t1 values (.12345678901234567890123456789012345678);
530
#+------------------------------------------+
532
#+------------------------------------------+
533
#| 0.12345678901234567890123456789012345678 |
534
#+------------------------------------------+
538
create table t1 (col1 decimal(31,30));
540
insert into t1 values (0.00000000001);
552
#-- The usual arithmetic operators / * + - should work.
554
#select 77777777777777777777777777777777777777 / 7777777777777777777777777777777777777 = 10;
555
#-- should return 0 (false).
557
select 7777777777777777777777777777777777777 * 10;
558
#-- should return 77777777777777777777777777777777777770
560
select .7777777777777777777777777777777777777 *
562
#-- should return 777777777777777777.7777777777777777777
564
select .7777777777777777777777777777777777777 - 0.1;
565
#-- should return .6777777777777777777777777777777777777
567
select .343434343434343434 + .343434343434343434;
568
#-- should return .686868686868686868
570
#-- 5. All arithmetic functions mentioned in the
571
#MySQL Reference Manual should work.
573
select abs(9999999999999999999999);
574
#-- should return 9999999999999999999999
576
select abs(-9999999999999999999999);
577
#-- should return 9999999999999999999999
579
select ceiling(999999999999999999);
580
select ceiling(99999999999999999999);
581
#-- should return 99999999999999999999
583
select ceiling(9.9999999999999999999);
586
select ceiling(-9.9999999999999999999);
589
select floor(999999999999999999);
590
select floor(9999999999999999999999);
591
#-- should return 9999999999999999999999
593
select floor(9.999999999999999999999);
596
select floor(-9.999999999999999999999);
597
#-- should return -10
599
select floor(-999999999999999999999.999);
600
select ceiling(999999999999999999999.999);
603
select 99999999999999999999999999999999999999 mod 3;
606
select round(99999999999999999.999);
607
#-- should return 100000000000000000
609
select round(-99999999999999999.999);
610
#-- should return -100000000000000000
612
select round(99999999999999999.999,3);
613
#-- should return 100000000000000000.000
615
select round(-99999999999999999.999,3);
616
#-- should return -100000000000000000.000
618
select truncate(99999999999999999999999999999999999999,31);
619
#-- should return 99999999999999999999999999999999999999.000
621
select truncate(99.999999999999999999999999999999999999,31);
622
#-- should return 99.9999999999999999999999999999999
624
select truncate(99999999999999999999999999999999999999,-31);
625
# should return 90000000000000000000000000000000
627
#-- 6. Set functions (AVG, SUM, COUNT) should work.
629
#drop table if exists t1;
633
#create procedure p1 () begin
634
# declare v1 int default 1; declare v2 decimal(0,38) default 0;
635
# create table t1 (col1 decimal(0,38));
636
# while v1 <= 10000 do
637
# insert into t1 values (-v2);
638
# set v2 = v2 + 0.00000000000000000000000000000000000001;
641
# select avg(col1),sum(col1),count(col1) from t1; end;//
645
# -- avg(col1)=0.00000000000000000000000000000000000001 added 10,000 times, then divided by 10,000
646
# -- sum(col1)=0.00000000000000000000000000000000000001 added 10,000 times
648
# -- count(col1)=10000
655
#-- When I say DECIMAL(x) I should be able to store x digits.
656
#-- If I can't, there should be an error at CREATE time.
658
#drop table if exists t1;
660
#create table t1 (col1 decimal(254));
661
#-- should return SQLSTATE 22003 numeric value out of range
663
#-- When I say DECIMAL(x,y) there should be no silent change of precision or
666
#drop table if exists t1;
668
#create table t1 (col1 decimal(0,38));
670
#--replace_regex /ENGINE=[a-z]*/ENGINE=X/i
671
#show create table t1;
673
#+-------+--------------------------------+
674
#| Table | Create Table |
675
#+-------+--------------------------------+
676
#| t9 | CREATE TABLE `t1` ( |
677
#|`s1` decimal(0,38) default NULL |
678
#| ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
679
#+-------+--------------------------------+
683
#-- From WL#1612 "The future" point 2.:
684
#-- The standard requires that we treat numbers like "0.5" as
685
#-- DECIMAL or NUMERIC, not as floating-point.
687
#drop table if exists t1;
690
create table t1 as select 0.5;
692
--replace_regex /ENGINE=[a-z]*/ENGINE=X/i
693
show create table t1;
695
#+-------+-----------------------------------+
696
#| Table | Create Table |
697
#+-------+-----------------------------------+
698
#| t7 | CREATE TABLE `t1` ( |
699
#| `0.5` decimal(3,1) NOT NULL default '0.0' |
700
#| ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
701
#+-------+-----------------------------------+
705
#-- From WL#1612, "The future", point 3.: We have to start rounding correctly.
707
select round(1.5),round(2.5);
709
#+------------+------------+
710
#| round(1.5) | round(2.5) |
711
#+------------+------------+
713
#+------------+------------+
715
#-- From WL#1612, "The future", point 4.: "select 0.07 * 0.07;" should return 0.0049, not 0.00.
716
#-- If operand#1 has scale X and operand#2 has scale Y, then result should have scale (X+Y).
719
#-- should return 0.0049
721
#-- From WL#1612, "The future", point 5.: Division by zero is an error.
723
#-- should return 1 (true).
727
#-- should return SQLSTATE 22012 division by zero.
730
#-- should return SQLSTATE 22012 division by zero.
737
#1 row in set, 1 warning (0.00 sec)
739
#-- From WL#1612 "The future" point 6.: Overflow is an error.
743
#select 1E300 * 1E300;
744
#-- should return SQLSTATE 22003 numeric value out of range
746
#select 18446744073709551615 + 1;
747
#-- should return SQLSTATE 22003 numeric value out of range
749
#-- 14. From WL#1612 "The future" point 7.:
750
#-- If s1 is INTEGER and s2 is DECIMAL, then
751
#-- "create table tk7 as select avg(s1),avg(s2) from tk;"
752
#-- should not create a table with "double(17,4)" data types.
753
#-- The result of AVG must still be exact numeric, with a
754
#-- scale the same or greater than the operand's scale.
755
#-- The result of SUM must still be exact numeric, with
756
#-- a scale the same as the operand's scale.
758
#drop table if exists t1;
759
#drop table if exists t2;
761
#create table t1 (col1 int, col2 decimal(5));
763
#create table t2 as select avg(col1),avg(col2) from t1;
766
#--replace_regex /ENGINE=[a-z]*/ENGINE=X/i
767
#show create table t2;
769
#+-------+---------------------------------+
770
#| Table | Create Table |
771
#+-------+---------------------------------+
772
#| t2 | CREATE TABLE `t2` ( |
773
#| `avg(col1)` decimal(17,4) default NULL, |
774
#| `avg(col2)` decimal(17,5) default NULL |
775
#| ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
776
#+-------+---------------------------------+
781
#-- From WL#1612 "The future" point 8.: Stop storing leading "+" signs and
784
#drop table if exists t1;
786
#create table t1 (col1 decimal(5,2),col2 decimal(5) zerofill, col3 decimal(3,1));
788
#insert into t1 values (1,1,1);
790
#select col1 from t1 union select col2 from t1 union select col3 from t1;
794
#-- From WL#1612, The future" point 9.:
795
#-- Accept the data type and precision and scale as the user
796
#-- asks, or return an error, but don't change to something else.
798
#drop table if exists t1;
800
#create table t1 (col1 numeric(4,2));
802
#--replace_regex /ENGINE=[a-z]*/ENGINE=X/i
803
#show create table t1;
807
#-- The scripts in the following bugs should work:
810
#BUG#559 Maximum precision for DECIMAL column ...
811
#BUG#1499 INSERT/UPDATE into decimal field rounding problem
812
#BUG#1845 Not correctly recognising value for decimal field
813
#BUG#2493 Round function doesn't work correctly
814
#BUG#2649 round(0.5) gives 0 (should be 1)
815
#BUG#3612 impicite rounding of VARCHARS during aritchmetic operations...
816
#BUG#3722 SELECT fails for certain values in Double(255,10) column.
817
#BUG#4485 Floating point conversions are inconsistent
819
#BUG#5931 Out-of-range values are accepted
820
#BUG#6048 Stored procedure causes operating system reboot
821
#BUG#6053 DOUBLE PRECISION literal
823
# Tests from 'traditional' mode tests
825
CREATE TABLE Sow6_2f (col1 NUMERIC(4,2));
827
INSERT INTO Sow6_2f VALUES (10.55);
828
# This INSERT statement will give an error since truncation of a
829
# decimal number in Drizzle is an error. See bug#337038 for further
832
INSERT INTO Sow6_2f VALUES (10.5555);
834
INSERT INTO Sow6_2f VALUES (-10.55);
835
# This INSERT statement will give an error since truncation of a
836
# decimal number in Drizzle is an error. See bug#337038 for further
839
INSERT INTO Sow6_2f VALUES (-10.5555);
841
INSERT INTO Sow6_2f VALUES (11);
844
INSERT INTO Sow6_2f VALUES (101.55);
845
#-- should return SQLSTATE 22003 numeric value out of range
847
#UPDATE Sow6_2f SET col1 = col1 * 50 WHERE col1 = 11;
848
#-- should return SQLSTATE 22003 numeric value out of range
850
#UPDATE Sow6_2f SET col1 = col1 / 0 WHERE col1 > 0;
851
#-- should return SQLSTATE 22012 division by zero
852
SELECT MOD(col1,0) FROM Sow6_2f;
853
#-- should return SQLSTATE 22012 division by zero
855
INSERT INTO Sow6_2f VALUES ('a59b');
856
#-- should return SQLSTATE 22018 invalid character value for cast
862
select 10.3330000000000/12.34500000;
873
--disable_ps_protocol
874
select 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 as x;
875
select 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 + 1 as x;
880
select 0.190287977636363637 + 0.040372670 * 0 - 0;
890
CREATE TABLE t1 (f1 DECIMAL (12,9), f2 DECIMAL(2,2));
891
INSERT INTO t1 VALUES (10.5, 0);
892
UPDATE t1 SET f1 = 4.5;
895
CREATE TABLE t1 (f1 DECIMAL (64,20), f2 DECIMAL(2,2));
896
INSERT INTO t1 VALUES (9999999999999999999999999999999999, 0);
901
# Bug #10599: problem with NULL
908
# Bug #9894 (negative to column)
910
create table t1( d1 decimal(18), d2 decimal(20), d3 decimal (22));
911
insert into t1 values(1,-1,-1);
913
create table t1 (col1 decimal(5,2), col2 numeric(5,2));
915
insert into t1 values (999.999,999.999);
917
insert into t1 values (-999.999,-999.999);
922
# Bug #8425 (insufficient precision of the division)
924
set @sav_dpi= @@div_precision_increment;
925
set @@div_precision_increment=15;
926
create table t1 (col1 int, col2 decimal(30,25), col3 numeric(30,25));
927
insert into t1 values (1,0.0123456789012345678912345,0.0123456789012345678912345);
928
select col2/9999999999 from t1 where col1=1;
929
select 9999999999/col2 from t1 where col1=1;
930
select 77777777/7777777;
932
set div_precision_increment= @sav_dpi;
935
# Bug #10896 (0.00 > -0.00)
937
create table t1 (a decimal(4,2));
938
insert into t1 values (0.00);
939
select * from t1 where a > -0.00;
940
select * from t1 where a = -0.00;
944
# Bug #11215: a problem with LONGLONG_MIN
947
create table t1 (col1 bigint default -9223372036854775808);
948
insert into t1 values (default);
953
# Bug #10891 (converting to decimal crashes server)
955
select cast('1.00000001335143196001808973960578441619873046875E-10' as decimal(30,15));
958
# Bug #11708 (conversion to decimal fails in decimal part)
960
select ln(14000) c1, convert(ln(14000),decimal(5,3)) c2, cast(ln(14000) as decimal(5,3)) c3;
962
select convert(ln(14000),decimal(2,3)) c1;
964
select cast(ln(14000) as decimal(2,3)) c1;
967
# Bug #8449 (Silent column changes)
970
create table t1 (sl decimal(70,30));
972
create table t1 (sl decimal(32,31));
974
create table t1 (sl decimal(0,38));
976
create table t1 (sl decimal(0,30));
977
create table t1 (sl decimal(5, 5));
978
--replace_regex /ENGINE=[a-z]*/ENGINE=X/i
979
show create table t1;
982
create table t1 (sl decimal(65, 30));
983
--replace_regex /ENGINE=[a-z]*/ENGINE=X/i
984
show create table t1;
988
# Bug 12173 (show create table fails)
991
f0 decimal (30,30) not null DEFAULT 0,
992
f1 decimal (0,0) not null default 0);
993
--replace_regex /ENGINE=[a-z]*/ENGINE=X/i
994
show create table t1;
998
# Bug #13667 (Inconsistency for decimal(m,d) specification
1001
create table t (d decimal(0,10));
1004
# Bug #14268 (bad FLOAT->DECIMAL conversion)
1010
my_varchar VARCHAR(50),
1011
my_decimal DECIMAL(65,30)
1013
--replace_regex /ENGINE=[a-z]*/ENGINE=X/i
1014
SHOW CREATE TABLE t1;
1020
eval INSERT INTO t1 SET my_float = 1.175494345e-$max_power,
1021
my_double = 1.175494345e-$max_power,
1022
my_varchar = '1.175494345e-$max_power';
1026
SELECT my_float, my_double, my_varchar FROM t1;
1028
# The following statement produces results with garbage past
1029
# the significant digits. Improving it is a part of the WL#3977.
1030
SELECT CAST(my_float AS DECIMAL(65,30)), my_float FROM t1;
1031
SELECT CAST(my_double AS DECIMAL(65,30)), my_double FROM t1;
1032
SELECT CAST(my_varchar AS DECIMAL(65,30)), my_varchar FROM t1;
1034
# We have to disable warnings here as the test in
1035
# Field_new_decimal::store(double):
1037
# fails randomly depending on compiler options
1040
# This UPDATE statement will give an error since truncation of a
1041
# decimal number in Drizzle is an error. See bug#337038 for further
1044
UPDATE t1 SET my_decimal = my_float;
1046
# Expected result 0.000000000011754943372854760000
1047
# On windows we get 0.000000000011754943372854770000
1048
# use replace_result to correct it
1049
--replace_result 0.000000000011754943372854770000 0.000000000011754943372854760000
1050
SELECT my_decimal, my_float FROM t1;
1052
# This UPDATE statement will give an error since truncation of a
1053
# decimal number in Drizzle is an error. See bug#337038 for further
1056
UPDATE t1 SET my_decimal = my_double;
1057
SELECT my_decimal, my_double FROM t1;
1059
# This UPDATE statement will give an error since truncation of a
1060
# decimal number in Drizzle is an error. See bug#337038 for further
1063
UPDATE t1 SET my_decimal = my_varchar;
1064
SELECT my_decimal, my_varchar FROM t1;
1069
# Bug #13573 (Wrong data inserted for too big values)
1072
create table t1 (c1 decimal(64));
1073
--error ER_WARN_DATA_OUT_OF_RANGE
1074
insert into t1 values(
1075
89000000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
1076
--error ER_TRUNCATED_WRONG_VALUE
1077
insert into t1 values(
1078
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 *
1079
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999);
1080
--error ER_WARN_DATA_OUT_OF_RANGE
1081
insert into t1 values(1e100);
1086
# Bug #18014: problem with 'alter table'
1089
create table t1(a decimal(7,2));
1090
insert into t1 values(123.12);
1092
alter table t1 modify a decimal(10,2);
1097
# Bug#19667 group by a decimal expression yields wrong result
1099
create table t1 (i int, j int);
1100
insert into t1 values (1,1), (1,2), (2,3), (2,4);
1101
select i, count(distinct j) from t1 group by i;
1102
select i+0.0 as i2, count(distinct j) from t1 group by i2;
1105
create table t1(f1 decimal(20,6));
1106
insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond);
1111
# Bug #24558: Increasing decimal column length causes data loss
1113
create table t1(a decimal(18));
1114
insert into t1 values(123456789012345678);
1115
alter table t1 modify column a decimal(19);
1120
# Bug #27957 cast as decimal does not check overflow, also inconsistent with group, subselect
1123
select cast(11.1234 as DECIMAL(3,2));
1124
select * from (select cast(11.1234 as DECIMAL(3,2))) t;
1126
select cast(a as DECIMAL(3,2))
1127
from (select 11.1233 as a
1128
UNION select 11.1234
1129
UNION select 12.1234
1132
select cast(a as DECIMAL(3,2)), count(*)
1133
from (select 11.1233 as a
1134
UNION select 11.1234
1135
UNION select 12.1234
1139
# Bug #28361 Buffer overflow in DECIMAL code on Windows
1142
create table t1 (s varchar(100));
1143
insert into t1 values (0.00000000010000000000000000364321973154977415791655470655996396089904010295867919921875);
1147
# Bug #27984 Long Decimal Maths produces truncated results
1150
SELECT 1.000000000000 * 99.999999999998 / 100 a,1.000000000000 * (99.999999999998 / 100) b;
1153
# Bug #29415: CAST AS DECIMAL(P,S) with too big precision/scale
1156
SELECT CAST(1 AS decimal(65,10));
1157
--error ER_TOO_BIG_PRECISION
1158
SELECT CAST(1 AS decimal(66,10));
1160
SELECT CAST(1 AS decimal(65,30));
1161
--error ER_TOO_BIG_SCALE
1162
SELECT CAST(1 AS decimal(65,31));
1164
CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL);
1165
INSERT INTO t1 VALUES (3,30), (1,10), (2,10);
1166
SELECT a+CAST(1 AS decimal(65,30)) AS aa, SUM(b) FROM t1 GROUP BY aa;
1167
--error ER_TOO_BIG_SCALE
1168
SELECT a+CAST(1 AS decimal(65,31)) AS aa, SUM(b) FROM t1 GROUP BY aa;
1173
# Bug #29417: assertion abort for a grouping query with decimal user variable
1176
CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL);
1177
INSERT INTO t1 VALUES (3,30), (1,10), (2,10);
1179
SET @a= CAST(1 AS decimal);
1180
SELECT 1 FROM t1 GROUP BY @b := @a, @b;
1185
# Bug #24907: unpredictable (display) precission, if input precission
1189
# As per 10.1.1. Overview of Numeric Types, type (new) DECIMAL has a
1190
# maxmimum precision of 30 places after the decimal point. Show that
1191
# temp field creation beyond that works and throws a truncation warning.
1192
# DECIMAL(37,36) should be adjusted to DECIMAL(31,30).
1193
# After Bug#337038, truncation throws an error and not a warning. Thus, we
1194
# will change the behavior here to expect an error. We are leaving the
1195
# original test case here so people can see how behavior changed.
1196
#CREATE TABLE t1 SELECT 0.123456789012345678901234567890123456 AS f1;
1200
# This CREATE statement will give an error since truncation of a
1201
# decimal number in Drizzle is an error. See bug#337038 for further
1204
CREATE TABLE t1 SELECT 0.123456789012345678901234567890123456 AS f1;
1206
# too many decimal places, AND too many digits altogether (90 = 45+45).
1207
# should preserve integers (65 = 45+20)
1208
--error ER_WARN_DATA_OUT_OF_RANGE
1209
CREATE TABLE t1 SELECT 123451234512345123451234512345123451234512345.678906789067890678906789067890678906789067890 AS f1;
1211
--echo End of 5.0 tests
1214
# Bug#16172 DECIMAL data type processed incorrectly
1216
select cast(143.481 as decimal(4,1));
1217
select cast(143.481 as decimal(4,0));
1218
select cast(143.481 as decimal(2,1));
1219
select cast(-3.4 as decimal(2,1));
1220
select cast(99.6 as decimal(2,0));
1221
select cast(-13.4 as decimal(2,1));
1222
select cast(98.6 as decimal(2,0));
1225
## Bug337038 Decimal truncation is warning, not error
1228
create table t1 (f1 decimal not null default 17.49);