~ubuntu-branches/ubuntu/precise/mysql-5.1/precise

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

100

create table t1 select round(1, 6);

101

show create table t1;

102

select * from t1;

103

drop table t1;

104

105

106

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

107

108

select abs(-2) * -2;

109

110

111

# Bug #6172 RAND(a) should only accept constant values as arguments

112

113

CREATE TABLE t1 (a INT);

114

115

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

116

SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED)

117

FROM t1;

118

SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED)

119

FROM t1 WHERE a = 1;

120

INSERT INTO t1 VALUES (3);

121

SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED)

122

FROM t1;

123

SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED)

124

FROM t1 WHERE a = 1;

125

PREPARE stmt FROM

126

"SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(?) * 1000 AS UNSIGNED)

127

FROM t1 WHERE a = 1";

128

set @var=2;

129

EXECUTE stmt USING @var;

130

131

DROP TABLE t1;

132

133

134

# Bug #14009: use of abs() on null value causes problems with filesort

135

136

# InnoDB is required to reproduce the fault, but it is okay if we default to

137

# MyISAM when testing.

138

--disable_warnings

139

create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8;

140

--enable_warnings

141

insert into t1 values ('http://www.foo.com/', now());

142

select a from t1 where a='http://www.foo.com/' order by abs(timediff(ts, 0));

143

drop table t1;

144

145

# End of 4.1 tests

146

147

148

# Bug #13820 (No warning on log(negative)

149

150

set sql_mode='traditional';

151

select ln(-1);

152

select log10(-1);

153

select log2(-1);

154

select log(2,-1);

155

select log(-2,1);

156

set sql_mode='';

157

158

159

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

160

161

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

162

select round(111,-10);

163

# round on bigint

164

select round(-5000111000111000155,-1);

165

# round on unsigned bigint

166

select round(15000111000111000155,-1);

167

# truncate on bigint

168

select truncate(-5000111000111000155,-1);

169

# truncate on unsigned bigint

170

select truncate(15000111000111000155,-1);

171

172

173

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

174

175

set names utf8;

176

create table t1

177

(f1 varchar(32) not null,

178

f2 smallint(5) unsigned not null,

179

f3 int(10) unsigned not null default '0')

180

engine=myisam default charset=utf8;

181

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

182

183

create table t2

184

(f1 int(10) unsigned not null,

185

f2 int(10) unsigned not null,

186

f3 smallint(5) unsigned not null)

187

engine=myisam default charset=utf8;

188

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

189

190

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

191

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

192

drop table t1, t2;

193

set names default;

194

195

# Bug 24912 -- misc functions have trouble with unsigned

196

197

select cast(-2 as unsigned), 18446744073709551614, -2;

198

select abs(cast(-2 as unsigned)), abs(18446744073709551614), abs(-2);

199

select ceiling(cast(-2 as unsigned)), ceiling(18446744073709551614), ceiling(-2);

200

select floor(cast(-2 as unsigned)), floor(18446744073709551614), floor(-2);

201

select format(cast(-2 as unsigned), 2), format(18446744073709551614, 2), format(-2, 2);

202

select sqrt(cast(-2 as unsigned)), sqrt(18446744073709551614), sqrt(-2);

203

select round(cast(-2 as unsigned), 1), round(18446744073709551614, 1), round(-2, 1);

204

select round(4, cast(-2 as unsigned)), round(4, 18446744073709551614), round(4, -2);

205

select truncate(cast(-2 as unsigned), 1), truncate(18446744073709551614, 1), truncate(-2, 1);

206

select truncate(4, cast(-2 as unsigned)), truncate(4, 18446744073709551614), truncate(4, -2);

207

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

208

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

209

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

210

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

211

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

212

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

213

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

214

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

215

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

216

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

217

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

218

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

219

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

220

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

221

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

222

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

223

select mod(cast(-2 as unsigned), 3), mod(18446744073709551614, 3), mod(-2, 3);

224

select mod(5, cast(-2 as unsigned)), mod(5, 18446744073709551614), mod(5, -2);

225

select pow(cast(-2 as unsigned), 5), pow(18446744073709551614, 5), pow(-2, 5);

226

227

228

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

229

230

231

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

232

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

233

SELECT a DIV 900 y FROM t1 GROUP BY y;

234

SELECT DISTINCT a DIV 900 y FROM t1;

235

SELECT b DIV 900 y FROM t1 GROUP BY y;

236

SELECT c DIV 900 y FROM t1 GROUP BY y;

237

DROP TABLE t1;

238

239

CREATE TABLE t1(a LONGBLOB);

240

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

241

SELECT DISTINCT (a DIV 254576881) FROM t1;

242

SELECT (a DIV 254576881) FROM t1 UNION ALL

243

SELECT (a DIV 254576881) FROM t1;

244

DROP TABLE t1;

245

246

CREATE TABLE t1(a SET('a','b','c'));

247

INSERT INTO t1 VALUES ('a');

248

SELECT a DIV 2 FROM t1 UNION SELECT a DIV 2 FROM t1;

249

DROP TABLE t1;

250

251

252

# Bug #15936: "round" differs on Windows to Unix

253

254

255

CREATE TABLE t1 (a DOUBLE);

256

257

INSERT INTO t1 VALUES (-1.1), (1.1),

258

(-1.5), (1.5),

259

(-1.9), (1.9),

260

(-2.1), (2.1),

261

(-2.5), (2.5),

262

(-2.9), (2.9),

263

# Check numbers with absolute values > 2^53 - 1

264

# (see comments for MAX_EXACT_INTEGER)

265

(-1e16 - 0.5), (1e16 + 0.5),

266

(-1e16 - 1.5), (1e16 + 1.5);

267

268

SELECT a, ROUND(a) FROM t1;

269

270

DROP TABLE t1;

271

272

273

# Bug#45152 crash with round() function on longtext column in a derived table

274

275

CREATE TABLE t1(f1 LONGTEXT) engine=myisam;

276

INSERT INTO t1 VALUES ('a');

277

SELECT 1 FROM (SELECT ROUND(f1) AS a FROM t1) AS s WHERE a LIKE 'a';

278

SELECT 1 FROM (SELECT ROUND(f1, f1) AS a FROM t1) AS s WHERE a LIKE 'a';

279

DROP TABLE t1;

280

281

--echo End of 5.0 tests

282

283

284

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

285

286

287

SELECT 1e308 + 1e308;

288

SELECT -1e308 - 1e308;

289

SELECT 1e300 * 1e300;

290

SELECT 1e300 / 1e-300;

291

SELECT EXP(750);

292

SELECT POW(10, 309);

293

294

--echo #

295

--echo # Bug #44768: SIGFPE crash when selecting rand from a view

296

--echo # containing null

297

--echo #

298

299

CREATE OR REPLACE VIEW v1 AS SELECT NULL AS a;

300

SELECT RAND(a) FROM v1;

301

DROP VIEW v1;

302

303

SELECT RAND(a) FROM (SELECT NULL AS a) b;

304

305

CREATE TABLE t1 (i INT);

306

INSERT INTO t1 VALUES (NULL);

307

SELECT RAND(i) FROM t1;

308

DROP TABLE t1;

309

310

--echo #

311

312

--echo End of 5.1 tests

Older »