~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

128

129

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

130

131

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

132

# MyISAM when testing.

133

--disable_warnings

134

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

135

--enable_warnings

136

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

137

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

138

drop table t1;

139

140

123

# End of 4.1 tests

141

124

142

125

146

129

select round(111,-10);

147

130

# round on bigint

148

131

select round(-5000111000111000155,-1);

149

# round on unsigned bigint

132

# round on bigint

150

133

select round(15000111000111000155,-1);

151

134

# truncate on bigint

152

135

select truncate(-5000111000111000155,-1);

153

# truncate on unsigned bigint

136

# truncate on bigint

154

137

select truncate(15000111000111000155,-1);

155

138

156

139

157

140

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

158

141

159

set names utf8;

160

create table t1

142

create TEMPORARY table t1

161

143

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

144

f2 int not null,

145

f3 int not null default '0')

146

engine=myisam;

165

147

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

166

148

167

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;

149

create TEMPORARY table t2

150

(f1 int not null,

151

f2 int not null,

152

f3 int not null)

153

engine=myisam;

172

154

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

173

155

174

156

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

175

157

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

176

158

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

159

160

# Bug 24912 -- misc functions have trouble with

161

162

select -2, 18446744073709551614, -2;

163

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

164

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

165

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

166

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

167

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

168

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

169

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

170

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

171

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

191

172

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

192

173

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

193

174

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

204

185

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

205

186

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

206

187

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

188

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

189

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

190

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

210

191

211

192

212

193

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

213

194

214

195

215

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

196

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

216

197

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

217

198

SELECT a DIV 900 y FROM t1 GROUP BY y;

218

199

SELECT DISTINCT a DIV 900 y FROM t1;

227

208

SELECT (a DIV 254576881) FROM t1;

228

209

DROP TABLE t1;

229

210

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

211

--echo End of 5.0 tests

236

212

237

213

Older »