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

100

create table t1 select round(1, 6);

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

101

100

show create table t1;

102

101

select * from t1;

103

102

drop table t1;

104

103

105

104

106

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

105

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

107

106

108

107

select abs(-2) * -2;

109

108

113

112

CREATE TABLE t1 (a INT);

114

113

115

114

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;

115

SELECT RAND(2) * 1000, RAND(a) * 1000 FROM t1;

116

SELECT RAND(2) * 1000, RAND(a) * 1000 FROM t1 WHERE a = 1;

120

117

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;

118

SELECT RAND(2) * 1000, RAND(a) * 1000 FROM t1;

119

SELECT RAND(2) * 1000, RAND(a) * 1000 FROM t1 WHERE a = 1;

125

120

126

121

DROP TABLE t1;

127

122

131

126

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

132

127

# MyISAM when testing.

133

128

--disable_warnings

134

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

129

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

135

130

--enable_warnings

136

131

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

137

132

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

146

141

select round(111,-10);

147

142

# round on bigint

148

143

select round(-5000111000111000155,-1);

149

# round on unsigned bigint

144

# round on bigint

150

145

select round(15000111000111000155,-1);

151

146

# truncate on bigint

152

147

select truncate(-5000111000111000155,-1);

153

# truncate on unsigned bigint

148

# truncate on bigint

154

149

select truncate(15000111000111000155,-1);

155

150

156

151

157

152

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

158

153

159

set names utf8;

160

154

create table t1

161

155

(f1 varchar(32) not null,

162

f2 smallint(5) unsigned not null,

163

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

164

engine=myisam default charset=utf8;

156

f2 int not null,

157

f3 int not null default '0')

158

engine=myisam;

165

159

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

166

160

167

161

create table t2

168

(f1 int(10) unsigned not null,

169

f2 int(10) unsigned not null,

170

f3 smallint(5) unsigned not null)

171

engine=myisam default charset=utf8;

162

(f1 int not null,

163

f2 int not null,

164

f3 int not null)

165

engine=myisam;

172

166

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

173

167

174

168

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

175

169

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

176

170

drop table t1, t2;

177

set names default;

178

179

# Bug 24912 -- misc functions have trouble with unsigned

180

181

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

182

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

183

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

184

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

185

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

186

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

187

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

188

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

189

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

190

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

171

172

# Bug 24912 -- misc functions have trouble with

173

174

select -2, 18446744073709551614, -2;

175

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

176

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

177

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

178

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

179

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

180

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

181

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

182

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

183

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

191

184

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

192

185

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

193

186

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

204

197

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

205

198

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

206

199

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

207

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

208

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

209

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

200

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

201

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

202

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

210

203

211

204

212

205

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

213

206

214

207

215

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

208

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

216

209

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

217

210

SELECT a DIV 900 y FROM t1 GROUP BY y;

218

211

SELECT DISTINCT a DIV 900 y FROM t1;

227

220

SELECT (a DIV 254576881) FROM t1;

228

221

DROP TABLE t1;

229

222

230

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

231

INSERT INTO t1 VALUES ('a');

232

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

233

DROP TABLE t1;

234

235

223

--echo End of 5.0 tests

236

224

237

225

Older »