1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
|
#
# Test of math functions
#
--disable_warnings
drop table if exists t1;
--enable_warnings
select floor(5.5),floor(-5.5);
explain extended select floor(5.5),floor(-5.5);
select ceiling(5.5),ceiling(-5.5);
explain extended select ceiling(5.5),ceiling(-5.5);
select truncate(52.64,1),truncate(52.64,2),truncate(52.64,-1),truncate(52.64,-2), truncate(-52.64,1),truncate(-52.64,-1);
explain extended select truncate(52.64,1),truncate(52.64,2),truncate(52.64,-1),truncate(52.64,-2), truncate(-52.64,1),truncate(-52.64,-1);
select round(5.5),round(-5.5);
explain extended select round(5.5),round(-5.5);
select round(5.64,1),round(5.64,2),round(5.64,-1),round(5.64,-2);
select abs(-10), sign(-5), sign(5), sign(0);
explain extended select abs(-10), sign(-5), sign(5), sign(0);
select log(exp(10)),exp(log(sqrt(10))*2),log(-1),log(NULL),log(1,1),log(3,9),log(-1,2),log(NULL,2);
explain extended select log(exp(10)),exp(log(sqrt(10))*2),log(-1),log(NULL),log(1,1),log(3,9),log(-1,2),log(NULL,2);
select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL);
explain extended select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL);
select log2(8),log2(15),log2(-2),log2(0),log2(NULL);
explain extended select log2(8),log2(15),log2(-2),log2(0),log2(NULL);
select log10(100),log10(18),log10(-4),log10(0),log10(NULL);
explain extended select log10(100),log10(18),log10(-4),log10(0),log10(NULL);
select pow(10,log10(10)),power(2,4);
explain extended select pow(10,log10(10)),power(2,4);
set @@rand_seed1=10000000,@@rand_seed2=1000000;
select rand(999999),rand();
explain extended select rand(999999),rand();
select pi(),format(sin(pi()/2),6),format(cos(pi()/2),6),format(abs(tan(pi())),6),format(cot(1),6),format(asin(1),6),format(acos(0),6),format(atan(1),6);
explain extended select pi(),format(sin(pi()/2),6),format(cos(pi()/2),6),format(abs(tan(pi())),6),format(cot(1),6),format(asin(1),6),format(acos(0),6),format(atan(1),6);
select degrees(pi()),radians(360);
select format(atan(-2, 2), 6);
select format(atan(pi(), 0), 6);
select format(atan2(-2, 2), 6);
select format(atan2(pi(), 0), 6);
#
# Bug #2338 Trignometric arithmatic problems
#
SELECT ACOS(1.0);
SELECT ASIN(1.0);
SELECT ACOS(0.2*5.0);
SELECT ACOS(0.5*2.0);
SELECT ASIN(0.8+0.2);
SELECT ASIN(1.2-0.2);
#
# Bug #3051 FLOOR returns invalid
#
# This can't be tested as it's not portable
#select floor(log(4)/log(2));
#select floor(log(8)/log(2));
#select floor(log(16)/log(2));
#
# Bug #9060 (format returns incorrect result)
#
select format(4.55, 1), format(4.551, 1);
explain extended select degrees(pi()),radians(360);
#
# Bug #7281: problem with rand()
#
--error 1054
select rand(rand);
# End of 4.1 tests
#
# Bug #8459 (FORMAT returns incorrect result)
#
create table t1 (col1 int, col2 decimal(60,30));
insert into t1 values(1,1234567890.12345);
select format(col2,7) from t1;
select format(col2,8) from t1;
insert into t1 values(7,1234567890123456.12345);
select format(col2,6) from t1 where col1=7;
drop table t1;
#
# Bug @10632 (Ceiling function returns wrong answer)
#
select ceil(0.09);
select ceil(0.000000000000000009);
#
# Bug #9837: problem with round()
#
create table t1 select round(1, 6);
show create table t1;
select * from t1;
drop table t1;
#
# Bug #11402: abs() forces rest of calculation to unsigned
#
select abs(-2) * -2;
#
# Bug #6172 RAND(a) should only accept constant values as arguments
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(1),(1),(2);
SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED)
FROM t1;
SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED)
FROM t1 WHERE a = 1;
INSERT INTO t1 VALUES (3);
SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED)
FROM t1;
SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED)
FROM t1 WHERE a = 1;
DROP TABLE t1;
#
# Bug #14009: use of abs() on null value causes problems with filesort
#
# InnoDB is required to reproduce the fault, but it is okay if we default to
# MyISAM when testing.
--disable_warnings
create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8;
--enable_warnings
insert into t1 values ('http://www.foo.com/', now());
select a from t1 where a='http://www.foo.com/' order by abs(timediff(ts, 0));
drop table t1;
# End of 4.1 tests
#
# Bug #8461 truncate() and round() return false results 2nd argument negative.
#
# round(a,-b) log_10(b) > a
select round(111,-10);
# round on bigint
select round(-5000111000111000155,-1);
# round on unsigned bigint
select round(15000111000111000155,-1);
# truncate on bigint
select truncate(-5000111000111000155,-1);
# truncate on unsigned bigint
select truncate(15000111000111000155,-1);
#
# Bug#16678 FORMAT gives wrong result if client run with default-character-set=utf8
#
set names utf8;
create table t1
(f1 varchar(32) not null,
f2 smallint(5) unsigned not null,
f3 int(10) unsigned not null default '0')
engine=myisam default charset=utf8;
insert into t1 values ('zombie',0,0),('gold',1,10000),('silver',2,10000);
create table t2
(f1 int(10) unsigned not null,
f2 int(10) unsigned not null,
f3 smallint(5) unsigned not null)
engine=myisam default charset=utf8;
insert into t2 values (16777216,16787215,1),(33554432,33564431,2);
select format(t2.f2-t2.f1+1,0) from t1,t2
where t1.f2 = t2.f3 order by t1.f1;
drop table t1, t2;
set names default;
# Bug 24912 -- misc functions have trouble with unsigned
select cast(-2 as unsigned), 18446744073709551614, -2;
select abs(cast(-2 as unsigned)), abs(18446744073709551614), abs(-2);
select ceiling(cast(-2 as unsigned)), ceiling(18446744073709551614), ceiling(-2);
select floor(cast(-2 as unsigned)), floor(18446744073709551614), floor(-2);
select format(cast(-2 as unsigned), 2), format(18446744073709551614, 2), format(-2, 2);
select sqrt(cast(-2 as unsigned)), sqrt(18446744073709551614), sqrt(-2);
select round(cast(-2 as unsigned), 1), round(18446744073709551614, 1), round(-2, 1);
select round(4, cast(-2 as unsigned)), round(4, 18446744073709551614), round(4, -2);
select truncate(cast(-2 as unsigned), 1), truncate(18446744073709551614, 1), truncate(-2, 1);
select truncate(4, cast(-2 as unsigned)), truncate(4, 18446744073709551614), truncate(4, -2);
select round(10000000000000000000, -19), truncate(10000000000000000000, -19);
select round(1e0, -309), truncate(1e0, -309);
select round(1e1,308), truncate(1e1, 308);
select round(1e1, 2147483648), truncate(1e1, 2147483648);
select round(1.1e1, 4294967295), truncate(1.1e1, 4294967295);
select round(1.12e1, 4294967296), truncate(1.12e1, 4294967296);
select round(1.5, 2147483640), truncate(1.5, 2147483640);
select round(1.5, -2147483649), round(1.5, 2147483648);
select truncate(1.5, -2147483649), truncate(1.5, 2147483648);
select round(1.5, -4294967296), round(1.5, 4294967296);
select truncate(1.5, -4294967296), truncate(1.5, 4294967296);
select round(1.5, -9223372036854775808), round(1.5, 9223372036854775808);
select truncate(1.5, -9223372036854775808), truncate(1.5, 9223372036854775808);
select round(1.5, 18446744073709551615), truncate(1.5, 18446744073709551615);
select round(18446744073709551614, -1), truncate(18446744073709551614, -1);
select round(4, -4294967200), truncate(4, -4294967200);
select mod(cast(-2 as unsigned), 3), mod(18446744073709551614, 3), mod(-2, 3);
select mod(5, cast(-2 as unsigned)), mod(5, 18446744073709551614), mod(5, -2);
select pow(cast(-2 as unsigned), 5), pow(18446744073709551614, 5), pow(-2, 5);
#
# Bug #30587: mysql crashes when trying to group by TIME div NUMBER
#
CREATE TABLE t1 (a timestamp, b varchar(20), c bit(1));
INSERT INTO t1 VALUES('1998-09-23', 'str1', 1), ('2003-03-25', 'str2', 0);
SELECT a DIV 900 y FROM t1 GROUP BY y;
SELECT DISTINCT a DIV 900 y FROM t1;
SELECT b DIV 900 y FROM t1 GROUP BY y;
SELECT c DIV 900 y FROM t1 GROUP BY y;
DROP TABLE t1;
CREATE TABLE t1(a LONGBLOB);
INSERT INTO t1 VALUES('1'),('2'),('3');
SELECT DISTINCT (a DIV 254576881) FROM t1;
SELECT (a DIV 254576881) FROM t1 UNION ALL
SELECT (a DIV 254576881) FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a SET('a','b','c'));
INSERT INTO t1 VALUES ('a');
SELECT a DIV 2 FROM t1 UNION SELECT a DIV 2 FROM t1;
DROP TABLE t1;
--echo End of 5.0 tests
#
# Bug #31236: Inconsistent division by zero behavior for floating point numbers
#
SELECT 1e308 + 1e308;
SELECT -1e308 - 1e308;
SELECT 1e300 * 1e300;
SELECT 1e300 / 1e-300;
SELECT EXP(750);
SELECT POW(10, 309);
--echo End of 5.1 tests
|