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

select distinct t2.i,@vv1:=if(sv1.i,1,0),@vv2:=if(sv2.i,1,0),@vv3:=if(sv3.i,1,0), @vv1+@vv2+@vv3 from t2 left join t1 as sv1 on sv1.i=t2.i and sv1.v=1 left join t1 as sv2 on sv2.i=t2.i and sv2.v=2 left join t1 as sv3 on sv3.i=t2.i and sv3.v=3;

i @vv1:=if(sv1.i,1,0) @vv2:=if(sv2.i,1,0) @vv3:=if(sv3.i,1,0) @vv1+@vv2+@vv3

1 1 0 1 2

2 1 0 0 1

explain select * from t1 where i=@vv1;

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE t1 ref i i 4 const 1

select @vv1,i,v from t1 where i=@vv1;

@vv1 i v

1 1 1

1 1 3

explain select * from t1 where @vv1:=@vv1+1 and i=@vv1;

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where

explain select @vv1:=i from t1 where i=@vv1;

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE t1 index NULL i 4 NULL 3 Using where; Using index

explain select * from t1 where i=@vv1;

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE t1 ref i i 4 const 1

drop table t1,t2;

set @a=0,@b=0;

select @a:=10, @b:=1, @a > @b, @a < @b;

@a:=10 @b:=1 @a > @b @a < @b

10 1 1 0

select @a:="10", @b:="1", @a > @b, @a < @b;

@a:="10" @b:="1" @a > @b @a < @b

10 1 1 0

select @a:=10, @b:=2, @a > @b, @a < @b;

@a:=10 @b:=2 @a > @b @a < @b

10 2 0 1

select @a:="10", @b:="2", @a > @b, @a < @b;

@a:="10" @b:="2" @a > @b @a < @b

10 2 1 0

select @a:=1;

@a:=1

select @a, @a:=1;

@a @a:=1

1 1

create table t1 (id int, d double, c char(10));

insert into t1 values (1,2.0, "test");

select @c:=0;

@c:=0

update t1 SET id=(@c:=@c+1);

select @c;

select @c:=0;

@c:=0

update t1 set id=(@c:=@c+1);

select @c;

select @c:=0;

@c:=0

select @c:=@c+1;

@c:=@c+1

select @d,(@d:=id),@d from t1;

@d (@d:=id) @d

NULL 1 1

select @e,(@e:=d),@e from t1;

@e (@e:=d) @e

NULL 2 2

select @f,(@f:=c),@f from t1;

@f (@f:=c) @f

NULL test test

set @g=1;

select @g,(@g:=c),@g from t1;

@g (@g:=c) @g

1 test 0

select @c, @d, @e, @f;

@c @d @e @f

1 1 2 test

select @d:=id, @e:=id, @f:=id, @g:=@id from t1;

@d:=id @e:=id @f:=id @g:=@id

100

1 1 1 NULL

101

select @c, @d, @e, @f, @g;

102

@c @d @e @f @g

103

1 1 1 1 NULL

104

drop table t1;

105

select @a:=10, @b:=2, @a>@b, @a:="10", @b:="2", @a>@b, @a:=10, @b:=2, @a>@b, @a:="10", @b:="2", @a>@b;

106

@a:=10 @b:=2 @a>@b @a:="10" @b:="2" @a>@b @a:=10 @b:=2 @a>@b @a:="10" @b:="2" @a>@b

107

10 2 1 10 2 1 10 2 1 10 2 1

108

create table t1 (i int not null);

109

insert t1 values (1),(2),(2),(3),(3),(3);

110

select @a:=0;

111

@a:=0

112

113

select @a, @a:=@a+count(*), count(*), @a from t1 group by i;

114

@a @a:=@a+count(*) count(*) @a

115

0 1 1 0

116

0 2 2 0

117

0 3 3 0

118

select @a:=0;

119

@a:=0

120

121

select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i;

122

@a+0 @a:=@a+0+count(*) count(*) @a+0

123

0 1 1 0

124

0 2 2 0

125

0 3 3 0

126

set @a=0;

127

select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i;

128

@a @a:="hello" @a @a:=3 @a @a:="hello again"

129

0 hello 0 3 0 hello again

130

0 hello 0 3 0 hello again

131

0 hello 0 3 0 hello again

132

select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i;

133

@a @a:="hello" @a @a:=3 @a @a:="hello again"

134

hello again hello hello again 3 hello again hello again

135

hello again hello hello again 3 hello again hello again

136

hello again hello hello again 3 hello again hello again

137

drop table t1;

138

set @a=_latin2'test';

139

select charset(@a),collation(@a),coercibility(@a);

140

charset(@a) collation(@a) coercibility(@a)

141

latin2 latin2_general_ci 2

142

select @a=_latin2'TEST';

143

@a=_latin2'TEST'

144

145

select @a=_latin2'TEST' collate latin2_bin;

146

@a=_latin2'TEST' collate latin2_bin

147

148

set @a=_latin2'test' collate latin2_general_ci;

149

select charset(@a),collation(@a),coercibility(@a);

150

charset(@a) collation(@a) coercibility(@a)

151

latin2 latin2_general_ci 2

152

select @a=_latin2'TEST';

153

@a=_latin2'TEST'

154

155

select @a=_latin2'TEST' collate latin2_bin;

156

@a=_latin2'TEST' collate latin2_bin

157

158

select charset(@a:=_latin2'test');

159

charset(@a:=_latin2'test')

160

latin2

161

select collation(@a:=_latin2'test');

162

collation(@a:=_latin2'test')

163

latin2_general_ci

164

select coercibility(@a:=_latin2'test');

165

coercibility(@a:=_latin2'test')

166

167

select collation(@a:=_latin2'test' collate latin2_bin);

168

collation(@a:=_latin2'test' collate latin2_bin)

169

latin2_bin

170

select coercibility(@a:=_latin2'test' collate latin2_bin);

171

coercibility(@a:=_latin2'test' collate latin2_bin)

172

173

select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST';

174

(@a:=_latin2'test' collate latin2_bin) = _latin2'TEST'

175

176

select charset(@a),collation(@a),coercibility(@a);

177

charset(@a) collation(@a) coercibility(@a)

178

latin2 latin2_bin 2

179

select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci;

180

(@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci

181

182

set @var= NULL ;

183

select FIELD( @var,'1it','Hit') as my_column;

184

my_column

185

186

select @v, coercibility(@v);

187

@v coercibility(@v)

188

NULL 2

189

set @v1=null, @v2=1, @v3=1.1, @v4=now();

190

select coercibility(@v1),coercibility(@v2),coercibility(@v3),coercibility(@v4);

191

coercibility(@v1) coercibility(@v2) coercibility(@v3) coercibility(@v4)

192

2 2 2 2

193

set session @honk=99;

194

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@honk=99' at line 1

195

set one_shot @honk=99;

196

ERROR HY000: The 'SET ONE_SHOT' syntax is reserved for purposes internal to the MySQL server

197

select @@local.max_allowed_packet;

198

@@local.max_allowed_packet

199

200

select @@session.max_allowed_packet;

201

@@session.max_allowed_packet

202

203

select @@global.max_allowed_packet;

204

@@global.max_allowed_packet

205

206

select @@max_allowed_packet;

207

@@max_allowed_packet

208

209

select @@Max_Allowed_Packet;

210

@@Max_Allowed_Packet

211

212

select @@version;

213

@@version

214

215

select @@global.version;

216

@@global.version

217

218

End of 4.1 tests

219

set @first_var= NULL;

220

create table t1 select @first_var;

221

show create table t1;

222

Table Create Table

223

t1 CREATE TABLE `t1` (

224

`@first_var` longblob

225

) ENGINE=MyISAM DEFAULT CHARSET=latin1

226

drop table t1;

227

set @first_var= cast(NULL as signed integer);

228

create table t1 select @first_var;

229

show create table t1;

230

Table Create Table

231

t1 CREATE TABLE `t1` (

232

`@first_var` bigint(20) DEFAULT NULL

233

) ENGINE=MyISAM DEFAULT CHARSET=latin1

234

drop table t1;

235

set @first_var= NULL;

236

create table t1 select @first_var;

237

show create table t1;

238

Table Create Table

239

t1 CREATE TABLE `t1` (

240

`@first_var` bigint(20) DEFAULT NULL

241

) ENGINE=MyISAM DEFAULT CHARSET=latin1

242

drop table t1;

243

set @first_var= concat(NULL);

244

create table t1 select @first_var;

245

show create table t1;

246

Table Create Table

247

t1 CREATE TABLE `t1` (

248

`@first_var` longblob

249

) ENGINE=MyISAM DEFAULT CHARSET=latin1

250

drop table t1;

251

set @first_var=1;

252

set @first_var= cast(NULL as CHAR);

253

create table t1 select @first_var;

254

show create table t1;

255

Table Create Table

256

t1 CREATE TABLE `t1` (

257

`@first_var` longtext

258

) ENGINE=MyISAM DEFAULT CHARSET=latin1

259

drop table t1;

260

set @a=18446744071710965857;

261

select @a;

262

263

18446744071710965857

264

CREATE TABLE `bigfailure` (

265

`afield` BIGINT UNSIGNED NOT NULL

266

);

267

INSERT INTO `bigfailure` VALUES (18446744071710965857);

268

SELECT * FROM bigfailure;

269

afield

270

18446744071710965857

271

select * from (SELECT afield FROM bigfailure) as b;

272

afield

273

18446744071710965857

274

select * from bigfailure where afield = (SELECT afield FROM bigfailure);

275

afield

276

18446744071710965857

277

select * from bigfailure where afield = 18446744071710965857;

278

afield

279

18446744071710965857

280

select * from bigfailure where afield = 18446744071710965856+1;

281

afield

282

18446744071710965857

283

SET @a := (SELECT afield FROM bigfailure);

284

SELECT @a;

285

286

18446744071710965857

287

SET @a := (select afield from (SELECT afield FROM bigfailure) as b);

288

SELECT @a;

289

290

18446744071710965857

291

SET @a := (select * from bigfailure where afield = (SELECT afield FROM bigfailure));

292

SELECT @a;

293

294

18446744071710965857

295

drop table bigfailure;

296

create table t1(f1 int, f2 int);

297

insert into t1 values (1,2),(2,3),(3,1);

298

select @var:=f2 from t1 group by f1 order by f2 desc limit 1;

299

@var:=f2

300

301

select @var;

302

@var

303

304

create table t2 as select @var:=f2 from t1 group by f1 order by f2 desc limit 1;

305

select * from t2;

306

@var:=f2

307

308

select @var;

309

@var

310

311

drop table t1,t2;

312

insert into city 'blah';

313

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''blah'' at line 1

314

SHOW COUNT(*) WARNINGS;

315

@@session.warning_count

316

317

SHOW COUNT(*) ERRORS;

318

@@session.error_count

319

320

create table t1(f1 int, f2 varchar(2), f3 float, f4 decimal(2,1));

321

insert into t1 values

322

(1, "a", 1.5, 1.6), (1, "a", 1.5, 1.6), (2, "b", 2.5, 2.6),

323

(3, "c", 3.5, 3.6), (4, "d", 4.5, 4.6), (1, "a", 1.5, 1.6),

324

(3, "c", 3.5, 3.6), (1, "a", 1.5, 1.6);

325

select @a:=f1, count(f1) from t1 group by 1 desc;

326

@a:=f1 count(f1)

327

4 1

328

3 2

329

2 1

330

1 4

331

select @a:=f1, count(f1) from t1 group by 1 asc;

332

@a:=f1 count(f1)

333

1 4

334

2 1

335

3 2

336

4 1

337

select @a:=f2, count(f2) from t1 group by 1 desc;

338

@a:=f2 count(f2)

339

d 1

340

c 2

341

b 1

342

a 4

343

select @a:=f3, count(f3) from t1 group by 1 desc;

344

@a:=f3 count(f3)

345

4.5 1

346

3.5 2

347

2.5 1

348

1.5 4

349

select @a:=f4, count(f4) from t1 group by 1 desc;

350

@a:=f4 count(f4)

351

4.6 1

352

3.6 2

353

2.6 1

354

1.6 4

355

drop table t1;

356

create table t1 (f1 int);

357

insert into t1 values (2), (1);

358

select @i := f1 as j from t1 order by 1;

359

360

361

362

drop table t1;

363

create table t1(a int);

364

insert into t1 values(5),(4),(4),(3),(2),(2),(2),(1);

365

set @rownum := 0;

366

set @rank := 0;

367

set @prev_score := NULL;

368

select @rownum := @rownum + 1 as row,

369

@rank := IF(@prev_score!=a, @rownum, @rank) as rank,

370

@prev_score := a as score

371

from t1 order by score desc;

372

drop table t1;

373

create table t1(b bigint);

374

insert into t1 (b) values (10), (30), (10);

375

set @var := 0;

376

select if(b=@var, 999, b) , @var := b from t1 order by b;

377

if(b=@var, 999, b) @var := b

378

10 10

379

999 10

380

30 30

381

drop table t1;

382

create temporary table t1 (id int);

383

insert into t1 values (2), (3), (3), (4);

384

set @lastid=-1;

385

select @lastid != id, @lastid, @lastid := id from t1;

386

@lastid != id @lastid @lastid := id

387

1 -1 2

388

1 2 3

389

0 3 3

390

1 3 4

391

drop table t1;

392

create temporary table t1 (id bigint);

393

insert into t1 values (2), (3), (3), (4);

394

set @lastid=-1;

395

select @lastid != id, @lastid, @lastid := id from t1;

396

@lastid != id @lastid @lastid := id

397

1 -1 2

398

1 2 3

399

0 3 3

400

1 3 4

401

drop table t1;

402

CREATE TABLE t1(a INT, b INT);

403

INSERT INTO t1 VALUES (0, 0), (2, 1), (2, 3), (1, 1), (30, 20);

404

SELECT a, b INTO @a, @b FROM t1 WHERE a=2 AND b=3 GROUP BY a, b;

405

SELECT @a, @b;

406

@a @b

407

2 3

408

SELECT a, b FROM t1 WHERE a=2 AND b=3 GROUP BY a, b;

409

a b

410

2 3

411

DROP TABLE t1;

412

CREATE TABLE t1 (f1 int(11) default NULL, f2 int(11) default NULL);

413

CREATE TABLE t2 (f1 int(11) default NULL, f2 int(11) default NULL, foo int(11));

414

CREATE TABLE t3 (f1 int(11) default NULL, f2 int(11) default NULL);

415

INSERT INTO t1 VALUES(10, 10);

416

INSERT INTO t1 VALUES(10, 10);

417

INSERT INTO t2 VALUES(10, 10, 10);

418

INSERT INTO t2 VALUES(10, 10, 10);

419

INSERT INTO t3 VALUES(10, 10);

420

INSERT INTO t3 VALUES(10, 10);

421

SELECT MIN(t2.f1),

422

@bar:= (SELECT MIN(t3.f2) FROM t3 WHERE t3.f2 > foo)

423

FROM t1,t2 WHERE t1.f1 = t2.f1 ORDER BY t2.f1;

424

MIN(t2.f1) @bar:= (SELECT MIN(t3.f2) FROM t3 WHERE t3.f2 > foo)

425

10 NULL

426

DROP TABLE t1, t2, t3;

427

End of 5.0 tests

428

CREATE TABLE t1 (i INT);

429

CREATE TRIGGER t_after_insert AFTER INSERT ON t1 FOR EACH ROW SET @bug42188 = 10;

430

INSERT INTO t1 VALUES (1);

431

INSERT INTO t1 VALUES (1);

432

DROP TABLE t1;

433

End of 5.1 tests

Older »