~drizzle-trunk/drizzle/development

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);

# 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);

--replace_regex /ENGINE=[a-z]*/ENGINE=X/i

show create table t1;

select * from t1;

drop table t1;

100

101

# Bug #11402: abs() forces rest of calculation to

102

103

select abs(-2) * -2;

104

105

# End of 4.1 tests

106

107

108

# Bug #8461 truncate() and round() return false results 2nd argument negative.

109

110

# round(a,-b) log_10(b) > a

111

select round(111,-10);

112

# round on bigint

113

select round(-5000111000111000155,-1);

114

# round on bigint

115

select round(15000111000111000155,-1);

116

# truncate on bigint

117

select truncate(-5000111000111000155,-1);

118

# truncate on bigint

119

select truncate(15000111000111000155,-1);

120

121

122

# Bug#16678 FORMAT gives wrong result if client run with default-character-set=utf8

123

124

create TEMPORARY table t1

125

(f1 varchar(32) not null,

126

f2 int not null,

127

f3 int not null default '0')

128

engine=myisam;

129

insert into t1 values ('zombie',0,0),('gold',1,10000),('silver',2,10000);

130

131

create TEMPORARY table t2

132

(f1 int not null,

133

f2 int not null,

134

f3 int not null)

135

engine=myisam;

136

insert into t2 values (16777216,16787215,1),(33554432,33564431,2);

137

138

select format(t2.f2-t2.f1+1,0) from t1,t2

139

where t1.f2 = t2.f3 order by t1.f1;

140

drop table t1, t2;

141

142

# Bug 24912 -- misc functions have trouble with

143

144

select -2, 18446744073709551614, -2;

145

select abs(-2), abs(18446744073709551614), abs(-2);

146

select ceiling(-2), ceiling(18446744073709551614), ceiling(-2);

147

select floor(-2), floor(18446744073709551614), floor(-2);

148

select format(-2, 2), format(18446744073709551614, 2), format(-2, 2);

149

select sqrt(-2), sqrt(18446744073709551614), sqrt(-2);

150

select round(-2, 1), round(18446744073709551614, 1), round(-2, 1);

151

select round(4, -2), round(4, 18446744073709551614), round(4, -2);

152

select truncate(-2, 1), truncate(18446744073709551614, 1), truncate(-2, 1);

153

select truncate(4, -2), truncate(4, 18446744073709551614), truncate(4, -2);

154

select round(10000000000000000000, -19), truncate(10000000000000000000, -19);

155

select round(1e0, -309), truncate(1e0, -309);

156

select round(1e1,308), truncate(1e1, 308);

157

select round(1e1, 2147483648), truncate(1e1, 2147483648);

158

select round(1.1e1, 4294967295), truncate(1.1e1, 4294967295);

159

select round(1.12e1, 4294967296), truncate(1.12e1, 4294967296);

160

select round(1.5, 2147483640), truncate(1.5, 2147483640);

161

select round(1.5, -2147483649), round(1.5, 2147483648);

162

select truncate(1.5, -2147483649), truncate(1.5, 2147483648);

163

select round(1.5, -4294967296), round(1.5, 4294967296);

164

select truncate(1.5, -4294967296), truncate(1.5, 4294967296);

165

select round(1.5, -9223372036854775808), round(1.5, 9223372036854775808);

166

select truncate(1.5, -9223372036854775808), truncate(1.5, 9223372036854775808);

167

select round(1.5, 18446744073709551615), truncate(1.5, 18446744073709551615);

168

select round(18446744073709551614, -1), truncate(18446744073709551614, -1);

169

select round(4, -4294967200), truncate(4, -4294967200);

170

select mod(-2, 3), mod(18446744073709551614, 3), mod(-2, 3);

171

select mod(5, -2), mod(5, 18446744073709551614), mod(5, -2);

172

select pow(-2, 5), pow(18446744073709551614, 5), pow(-2, 5);

173

174

175

# Bug #30587: mysql crashes when trying to group by TIME div NUMBER

176

177

178

CREATE TABLE t1 (a timestamp, b varchar(20), c int);

179

INSERT INTO t1 VALUES('1998-09-23', 'str1', 1), ('2003-03-25', 'str2', 0);

180

SELECT a DIV 900 y FROM t1 GROUP BY y;

181

SELECT DISTINCT a DIV 900 y FROM t1;

182

SELECT b DIV 900 y FROM t1 GROUP BY y;

183

SELECT c DIV 900 y FROM t1 GROUP BY y;

184

DROP TABLE t1;

185

186

CREATE TABLE t1(a LONGBLOB);

187

INSERT INTO t1 VALUES('1'),('2'),('3');

188

SELECT DISTINCT (a DIV 254576881) FROM t1;

189

SELECT (a DIV 254576881) FROM t1 UNION ALL

190

SELECT (a DIV 254576881) FROM t1;

191

DROP TABLE t1;

192

193

--echo End of 5.0 tests

194

195

196

# Bug #31236: Inconsistent division by zero behavior for floating point numbers

197

198

199

SELECT 1e308 + 1e308;

200

SELECT -1e308 - 1e308;

201

SELECT 1e300 * 1e300;

202

SELECT 1e300 / 1e-300;

203

SELECT EXP(750);

204

SELECT POW(10, 309);

205

206

--echo End of 5.1 tests

Older »