~ubuntu-branches/ubuntu/trusty/mysql-5.6/trusty : revision 1

drop table if exists t1, t2;

SELECT * FROM mysql.proc INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/proc.txt';

delete from mysql.proc;

create procedure syntaxerror(t int)|

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 '' at line 1

create procedure syntaxerror(t int)|

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 '' at line 1

create procedure syntaxerror(t int)|

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 '' at line 1

drop table if exists t3|

create table t3 ( x int )|

insert into t3 values (2), (3)|

create procedure bad_into(out param int)

select x from t3 into param|

call bad_into(@x)|

ERROR 42000: Result consisted of more than one row

drop procedure bad_into|

drop table t3|

create procedure proc1()

set @x = 42|

create function func1() returns int

return 42|

create procedure foo()

create procedure bar() set @x=3|

ERROR 2F003: Can't create a PROCEDURE from within another stored routine

create procedure foo()

create function bar() returns double return 2.3|

ERROR 2F003: Can't create a FUNCTION from within another stored routine

create procedure proc1()

set @x = 42|

ERROR 42000: PROCEDURE proc1 already exists

create function func1() returns int

return 42|

ERROR 42000: FUNCTION func1 already exists

drop procedure proc1|

drop function func1|

alter procedure foo|

ERROR 42000: PROCEDURE test.foo does not exist

alter function foo|

ERROR 42000: FUNCTION test.foo does not exist

drop procedure foo|

ERROR 42000: PROCEDURE test.foo does not exist

drop function foo|

ERROR 42000: FUNCTION test.foo does not exist

call foo()|

ERROR 42000: PROCEDURE test.foo does not exist

drop procedure if exists foo|

Warnings:

Note 1305 PROCEDURE test.foo does not exist

show create procedure foo|

ERROR 42000: PROCEDURE foo does not exist

show create function foo|

ERROR 42000: FUNCTION foo does not exist

create procedure foo()

foo: loop

leave bar;

end loop|

ERROR 42000: LEAVE with no matching label: bar

create procedure foo()

foo: loop

iterate bar;

end loop|

ERROR 42000: ITERATE with no matching label: bar

create procedure foo()

foo: begin

iterate foo;

end|

ERROR 42000: ITERATE with no matching label: foo

create procedure foo()

foo: loop

set @x=2;

end loop foo;

end loop foo|

ERROR 42000: Redefining label foo

create procedure foo()

foo: loop

set @x=2;

end loop bar|

ERROR 42000: End-label bar without match

create procedure foo()

return 42|

ERROR 42000: RETURN is only allowed in a FUNCTION

create procedure p(x int)

set @x = x|

create function f(x int) returns int

return x+42|

call p()|

ERROR 42000: Incorrect number of arguments for PROCEDURE test.p; expected 1, got 0

call p(1, 2)|

ERROR 42000: Incorrect number of arguments for PROCEDURE test.p; expected 1, got 2

select f()|

ERROR 42000: Incorrect number of arguments for FUNCTION test.f; expected 1, got 0

select f(1, 2)|

ERROR 42000: Incorrect number of arguments for FUNCTION test.f; expected 1, got 2

drop procedure p|

drop function f|

create procedure p(val int, out res int)

begin

100

declare x int default 0;

101

declare continue handler for foo set x = 1;

102

insert into test.t1 values (val);

103

if (x) then

104

set res = 0;

105

else

106

set res = 1;

107

end if;

108

end|

109

ERROR 42000: Undefined CONDITION: foo

110

create procedure p(val int, out res int)

111

begin

112

declare x int default 0;

113

declare foo condition for 1146;

114

declare continue handler for bar set x = 1;

115

insert into test.t1 values (val);

116

if (x) then

117

set res = 0;

118

else

119

set res = 1;

120

end if;

121

end|

122

ERROR 42000: Undefined CONDITION: bar

123

create function f(val int) returns int

124

begin

125

declare x int;

126

set x = val+3;

127

end|

128

ERROR 42000: No RETURN found in FUNCTION test.f

129

create function f(val int) returns int

130

begin

131

declare x int;

132

set x = val+3;

133

if x < 4 then

134

return x;

135

end if;

136

end|

137

select f(10)|

138

ERROR 2F005: FUNCTION f ended without RETURN

139

drop function f|

140

create procedure p()

141

begin

142

declare c cursor for insert into test.t1 values ("foo", 42);

143

open c;

144

close c;

145

end|

146

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 'insert into test.t1 values ("foo", 42);

147

open c;

148

close c;

149

end' at line 3

150

create procedure p()

151

begin

152

declare x int;

153

declare c cursor for select * into x from test.t limit 1;

154

open c;

155

close c;

156

end|

157

ERROR 42000: Cursor SELECT must not have INTO

158

create procedure p()

159

begin

160

declare c cursor for select * from test.t;

161

open cc;

162

close c;

163

end|

164

ERROR 42000: Undefined CURSOR: cc

165

drop table if exists t1|

166

create table t1 (val int)|

167

create procedure p()

168

begin

169

declare c cursor for select * from test.t1;

170

open c;

171

open c;

172

close c;

173

end|

174

call p()|

175

ERROR 24000: Cursor is already open

176

drop procedure p|

177

create procedure p()

178

begin

179

declare c cursor for select * from test.t1;

180

open c;

181

close c;

182

close c;

183

end|

184

call p()|

185

ERROR 24000: Cursor is not open

186

drop procedure p|

187

alter procedure bar3 sql security invoker|

188

ERROR 42000: PROCEDURE test.bar3 does not exist

189

drop table t1|

190

drop table if exists t1|

191

create table t1 (val int, x float)|

192

insert into t1 values (42, 3.1), (19, 1.2)|

193

create procedure p()

194

begin

195

declare x int;

196

declare c cursor for select * from t1;

197

open c;

198

fetch c into x, y;

199

close c;

200

end|

201

ERROR 42000: Undeclared variable: y

202

create procedure p()

203

begin

204

declare x int;

205

declare c cursor for select * from t1;

206

open c;

207

fetch c into x;

208

close c;

209

end|

210

call p()|

211

ERROR HY000: Incorrect number of FETCH variables

212

drop procedure p|

213

create procedure p()

214

begin

215

declare x int;

216

declare y float;

217

declare z int;

218

declare c cursor for select * from t1;

219

open c;

220

fetch c into x, y, z;

221

close c;

222

end|

223

call p()|

224

ERROR HY000: Incorrect number of FETCH variables

225

drop procedure p|

226

create procedure p(in x int, x char(10))

227

begin

228

end|

229

ERROR 42000: Duplicate parameter: x

230

create function p(x int, x char(10))

231

begin

232

end|

233

ERROR 42000: Duplicate parameter: x

234

create procedure p()

235

begin

236

declare x float;

237

declare x int;

238

end|

239

ERROR 42000: Duplicate variable: x

240

create procedure p()

241

begin

242

declare c condition for 1064;

243

declare c condition for 1065;

244

end|

245

ERROR 42000: Duplicate condition: c

246

create procedure p()

247

begin

248

declare c cursor for select * from t1;

249

declare c cursor for select field from t1;

250

end|

251

ERROR 42000: Duplicate cursor: c

252

create procedure u()

253

use sptmp|

254

ERROR 0A000: USE is not allowed in stored procedures

255

create procedure p()

256

begin

257

declare c cursor for select * from t1;

258

declare x int;

259

end|

260

ERROR 42000: Variable or condition declaration after cursor or handler declaration

261

create procedure p()

262

begin

263

declare x int;

264

declare continue handler for sqlstate '42S99' set x = 1;

265

declare foo condition for sqlstate '42S99';

266

end|

267

ERROR 42000: Variable or condition declaration after cursor or handler declaration

268

create procedure p()

269

begin

270

declare x int;

271

declare continue handler for sqlstate '42S99' set x = 1;

272

declare c cursor for select * from t1;

273

end|

274

ERROR 42000: Cursor declaration after handler declaration

275

drop procedure if exists p|

276

create procedure p(in x int, inout y int, out z int)

277

begin

278

set y = x+y;

279

set z = x+y;

280

end|

281

set @tmp_x = 42|

282

set @tmp_y = 3|

283

set @tmp_z = 0|

284

call p(@tmp_x, @tmp_y, @tmp_z)|

285

select @tmp_x, @tmp_y, @tmp_z|

286

@tmp_x @tmp_y @tmp_z

287

42 45 87

288

call p(42, 43, @tmp_z)|

289

ERROR 42000: OUT or INOUT argument 2 for routine test.p is not a variable or NEW pseudo-variable in BEFORE trigger

290

call p(42, @tmp_y, 43)|

291

ERROR 42000: OUT or INOUT argument 3 for routine test.p is not a variable or NEW pseudo-variable in BEFORE trigger

292

drop procedure p|

293

create procedure p() begin end|

294

lock table t1 read|

295

call p()|

296

unlock tables|

297

drop procedure p|

298

lock tables t1 read, mysql.proc write|

299

ERROR HY000: You can't combine write-locking of system tables with other tables or lock types

300

lock tables mysql.proc write, mysql.user write|

301

ERROR HY000: You can't combine write-locking of system tables with other tables or lock types

302

lock tables t1 read, mysql.proc read|

303

unlock tables|

304

lock tables mysql.proc write|

305

unlock tables|

306

drop function if exists f1|

307

create function f1(i int) returns int

308

begin

309

insert into t1 (val) values (i);

310

return 0;

311

end|

312

select val, f1(val) from t1|

313

ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

314

select val, f1(val) from t1 as tab|

315

ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

316

select * from t1|

317

val x

318

42 3.1

319

19 1.2

320

update t1 set val= f1(val)|

321

ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

322

select * from t1|

323

val x

324

42 3.1

325

19 1.2

326

select f1(17)|

327

f1(17)

328

329

select * from t1|

330

val x

331

42 3.1

332

19 1.2

333

17 NULL

334

delete from t1 where val= 17|

335

drop function f1|

336

create procedure bug1965()

337

begin

338

declare c cursor for select val from t1 order by valname;

339

open c;

340

close c;

341

end|

342

call bug1965()|

343

ERROR 42S22: Unknown column 'valname' in 'order clause'

344

drop procedure bug1965|

345

select 1 into a|

346

ERROR 42000: Undeclared variable: a

347

drop table if exists t3|

348

create table t3 (column_1_0 int)|

349

create procedure bug1653()

350

update t3 set column_1 = 0|

351

call bug1653()|

352

ERROR 42S22: Unknown column 'column_1' in 'field list'

353

drop table t3|

354

create table t3 (column_1 int)|

355

call bug1653()|

356

drop procedure bug1653|

357

drop table t3|

358

create procedure bug2259()

359

begin

360

declare v1 int;

361

declare c1 cursor for select s1 from t1;

362

fetch c1 into v1;

363

end|

364

call bug2259()|

365

ERROR 24000: Cursor is not open

366

drop procedure bug2259|

367

create procedure bug2272()

368

begin

369

declare v int;

370

update t1 set v = 42;

371

end|

372

insert into t1 values (666, 51.3)|

373

call bug2272()|

374

ERROR 42S22: Unknown column 'v' in 'field list'

375

truncate table t1|

376

drop procedure bug2272|

377

create procedure bug2329_1()

378

begin

379

declare v int;

380

insert into t1 (v) values (5);

381

end|

382

create procedure bug2329_2()

383

begin

384

declare v int;

385

replace t1 set v = 5;

386

end|

387

call bug2329_1()|

388

ERROR 42S22: Unknown column 'v' in 'field list'

389

call bug2329_2()|

390

ERROR 42S22: Unknown column 'v' in 'field list'

391

drop procedure bug2329_1|

392

drop procedure bug2329_2|

393

create function bug3287() returns int

394

begin

395

declare v int default null;

396

case

397

when v is not null then return 1;

398

end case;

399

return 2;

400

end|

401

select bug3287()|

402

ERROR 20000: Case not found for CASE statement

403

drop function bug3287|

404

create procedure bug3287(x int)

405

case x

406

when 0 then

407

insert into test.t1 values (x, 0.1);

408

when 1 then

409

insert into test.t1 values (x, 1.1);

410

end case|

411

call bug3287(2)|

412

ERROR 20000: Case not found for CASE statement

413

drop procedure bug3287|

414

drop table if exists t3|

415

create table t3 (s1 int, primary key (s1))|

416

insert into t3 values (5),(6)|

417

create procedure bug3279(out y int)

418

begin

419

declare x int default 0;

420

begin

421

declare exit handler for sqlexception set x = x+1;

422

insert into t3 values (5);

423

end;

424

if x < 2 then

425

set x = x+1;

426

insert into t3 values (6);

427

end if;

428

set y = x;

429

end|

430

set @x = 0|

431

call bug3279(@x)|

432

ERROR 23000: Duplicate entry '6' for key 'PRIMARY'

433

select @x|

434

435

436

drop procedure bug3279|

437

drop table t3|

438

create procedure nodb.bug3339() begin end|

439

ERROR 42000: Unknown database 'nodb'

440

create procedure bug2653_1(a int, out b int)

441

set b = aa|

442

create procedure bug2653_2(a int, out b int)

443

begin

444

if aa < 0 then

445

set b = - a;

446

else

447

set b = a;

448

end if;

449

end|

450

call bug2653_1(1, @b)|

451

ERROR 42S22: Unknown column 'aa' in 'field list'

452

call bug2653_2(2, @b)|

453

ERROR 42S22: Unknown column 'aa' in 'field list'

454

drop procedure bug2653_1|

455

drop procedure bug2653_2|

456

create procedure bug4344() drop procedure bug4344|

457

ERROR HY000: Can't drop or alter a PROCEDURE from within another stored routine

458

create procedure bug4344() drop function bug4344|

459

ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine

460

drop procedure if exists bug3294|

461

create procedure bug3294()

462

begin

463

declare continue handler for sqlexception drop table t5;

464

drop table t5;

465

drop table t5;

466

end|

467

create table t5 (x int)|

468

call bug3294()|

469

ERROR 42S02: Unknown table 'test.t5'

470

drop procedure bug3294|

471

drop procedure if exists bug8776_1|

472

drop procedure if exists bug8776_2|

473

drop procedure if exists bug8776_3|

474

drop procedure if exists bug8776_4|

475

create procedure bug8776_1()

476

begin

477

declare continue handler for sqlstate '42S0200test' begin end;

478

begin end;

479

end|

480

ERROR 42000: Bad SQLSTATE: '42S0200test'

481

create procedure bug8776_2()

482

begin

483

declare continue handler for sqlstate '4200' begin end;

484

begin end;

485

end|

486

ERROR 42000: Bad SQLSTATE: '4200'

487

create procedure bug8776_3()

488

begin

489

declare continue handler for sqlstate '420000' begin end;

490

begin end;

491

end|

492

ERROR 42000: Bad SQLSTATE: '420000'

493

create procedure bug8776_4()

494

begin

495

declare continue handler for sqlstate '42x00' begin end;

496

begin end;

497

end|

498

ERROR 42000: Bad SQLSTATE: '42x00'

499

create procedure bug6600()

500

check table t1|

501

ERROR 0A000: CHECK is not allowed in stored procedures

502

create procedure bug6600()

503

lock table t1 read|

504

ERROR 0A000: LOCK is not allowed in stored procedures

505

create procedure bug6600()

506

unlock table t1|

507

ERROR 0A000: UNLOCK is not allowed in stored procedures

508

drop procedure if exists bug9566|

509

create procedure bug9566()

510

begin

511

select * from t1;

512

end|

513

lock table t1 read|

514

alter procedure bug9566 comment 'Some comment'|

515

ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction

516

unlock tables|

517

drop procedure bug9566|

518

drop procedure if exists bug7299|

519

create procedure bug7299()

520

begin

521

declare v int;

522

declare c cursor for select val from t1;

523

declare exit handler for sqlexception select 'Error!';

524

open c;

525

fetch c into v;

526

end|

527

truncate table t1|

528

call bug7299()|

529

ERROR 02000: No data - zero rows fetched, selected, or processed

530

drop procedure bug7299|

531

create procedure bug9073()

532

begin

533

declare continue handler for sqlexception select 1;

534

declare continue handler for sqlexception select 2;

535

end|

536

ERROR 42000: Duplicate handler declared in the same block

537

create procedure bug9073()

538

begin

539

declare condname1 condition for 1234;

540

declare continue handler for condname1 select 1;

541

declare exit handler for condname1 select 2;

542

end|

543

ERROR 42000: Duplicate handler declared in the same block

544

create procedure bug9073()

545

begin

546

declare condname1 condition for sqlstate '42000';

547

declare condname2 condition for sqlstate '42000';

548

declare exit handler for condname1 select 1;

549

declare continue handler for condname2 select 2;

550

end|

551

ERROR 42000: Duplicate handler declared in the same block

552

create procedure bug9073()

553

begin

554

declare condname1 condition for sqlstate '42000';

555

declare exit handler for condname1 select 1;

556

declare exit handler for sqlstate '42000' select 2;

557

end|

558

ERROR 42000: Duplicate handler declared in the same block

559

drop procedure if exists bug9073|

560

create procedure bug9073()

561

begin

562

declare condname1 condition for sqlstate '42000';

563

declare continue handler for condname1 select 1;

564

begin

565

declare exit handler for sqlstate '42000' select 2;

566

begin

567

declare continue handler for sqlstate '42000' select 3;

568

end;

569

end;

570

end|

571

drop procedure bug9073|

572

create procedure bug7047()

573

alter procedure bug7047|

574

ERROR HY000: Can't drop or alter a PROCEDURE from within another stored routine

575

create function bug7047() returns int

576

begin

577

alter function bug7047;

578

return 0;

579

end|

580

ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine

581

create function bug8408() returns int

582

begin

583

select * from t1;

584

return 0;

585

end|

586

ERROR 0A000: Not allowed to return a result set from a function

587

create function bug8408() returns int

588

begin

589

show warnings;

590

return 0;

591

end|

592

ERROR 0A000: Not allowed to return a result set from a function

593

create function bug8408(a int) returns int

594

begin

595

declare b int;

596

select b;

597

return b;

598

end|

599

ERROR 0A000: Not allowed to return a result set from a function

600

drop function if exists bug8408_f|

601

drop procedure if exists bug8408_p|

602

create function bug8408_f() returns int

603

begin

604

call bug8408_p();

605

return 0;

606

end|

607

create procedure bug8408_p()

608

select * from t1|

609

call bug8408_p()|

610

val x

611

select bug8408_f()|

612

ERROR 0A000: Not allowed to return a result set from a function

613

drop procedure bug8408_p|

614

drop function bug8408_f|

615

create function bug8408() returns int

616

begin

617

declare n int default 0;

618

select count(*) into n from t1;

619

return n;

620

end|

621

insert into t1 value (2, 2.7), (3, 3.14), (7, 7.0)|

622

select *,bug8408() from t1|

623

val x bug8408()

624

2 2.7 3

625

3 3.14 3

626

7 7 3

627

drop function bug8408|

628

truncate table t1|

629

drop procedure if exists bug10537|

630

create procedure bug10537()

631

load data local infile '/tmp/somefile' into table t1|

632

ERROR 0A000: LOAD DATA is not allowed in stored procedures

633

drop function if exists bug8409|

634

create function bug8409()

635

returns int

636

begin

637

flush tables;

638

return 5;

639

end|

640

ERROR 0A000: FLUSH is not allowed in stored function or trigger

641

create function bug8409() returns int begin reset query cache;

642

return 1; end|

643

ERROR 0A000: RESET is not allowed in stored function or trigger

644

create function bug8409() returns int begin reset master;

645

return 1; end|

646

ERROR 0A000: RESET is not allowed in stored function or trigger

647

create function bug8409() returns int begin reset slave;

648

return 1; end|

649

ERROR 0A000: RESET is not allowed in stored function or trigger

650

create function bug8409() returns int begin flush hosts;

651

return 1; end|

652

ERROR 0A000: FLUSH is not allowed in stored function or trigger

653

create function bug8409() returns int begin flush privileges;

654

return 1; end|

655

ERROR 0A000: FLUSH is not allowed in stored function or trigger

656

create function bug8409() returns int begin flush tables with read lock;

657

return 1; end|

658

ERROR 0A000: FLUSH is not allowed in stored function or trigger

659

create function bug8409() returns int begin flush tables;

660

return 1; end|

661

ERROR 0A000: FLUSH is not allowed in stored function or trigger

662

create function bug8409() returns int begin flush logs;

663

return 1; end|

664

ERROR 0A000: FLUSH is not allowed in stored function or trigger

665

create function bug8409() returns int begin flush status;

666

return 1; end|

667

ERROR 0A000: FLUSH is not allowed in stored function or trigger

668

create function bug8409() returns int begin flush des_key_file;

669

return 1; end|

670

ERROR 0A000: FLUSH is not allowed in stored function or trigger

671

create function bug8409() returns int begin flush user_resources;

672

return 1; end|

673

ERROR 0A000: FLUSH is not allowed in stored function or trigger

674

create procedure bug9529_901234567890123456789012345678901234567890123456789012345()

675

begin

676

end|

677

ERROR 42000: Identifier name 'bug9529_901234567890123456789012345678901234567890123456789012345' is too long

678

drop procedure if exists bug17015_0123456789012345678901234567890123456789012345678901234|

679

create procedure bug17015_0123456789012345678901234567890123456789012345678901234()

680

begin

681

end|

682

show procedure status like 'bug17015%'|

683

Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation

684

test bug17015_0123456789012345678901234567890123456789012345678901234 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci

685

drop procedure bug17015_0123456789012345678901234567890123456789012345678901234|

686

drop procedure if exists bug10969|

687

create procedure bug10969()

688

begin

689

declare s1 int default 0;

690

select default(s1) from t30;

691

end|

692

ERROR 42000: Incorrect column name 's1'

693

create procedure bug10969()

694

begin

695

declare s1 int default 0;

696

select default(t30.s1) from t30;

697

end|

698

drop procedure bug10969|

699

drop table t1|

700

create table t1(f1 int);

701

create table t2(f1 int);

702

CREATE PROCEDURE SP001()

703

P1: BEGIN

704

DECLARE ENDTABLE INT DEFAULT 0;

705

DECLARE TEMP_NUM INT;

706

DECLARE TEMP_SUM INT;

707

DECLARE C1 CURSOR FOR SELECT F1 FROM t1;

708

DECLARE C2 CURSOR FOR SELECT F1 FROM t2;

709

DECLARE CONTINUE HANDLER FOR NOT FOUND SET ENDTABLE = 1;

710

SET ENDTABLE=0;

711

SET TEMP_SUM=0;

712

SET TEMP_NUM=0;

713

OPEN C1;

714

FETCH C1 INTO TEMP_NUM;

715

WHILE ENDTABLE = 0 DO

716

SET TEMP_SUM=TEMP_NUM+TEMP_SUM;

717

FETCH C1 INTO TEMP_NUM;

718

END WHILE;

719

SELECT TEMP_SUM;

720

CLOSE C1;

721

CLOSE C1;

722

SELECT 'end of proc';

723

END P1|

724

call SP001();

725

TEMP_SUM

726

727

ERROR 24000: Cursor is not open

728

drop procedure SP001;

729

drop table t1, t2;

730

drop function if exists bug11394|

731

drop function if exists bug11394_1|

732

drop function if exists bug11394_2|

733

drop procedure if exists bug11394|

734

create function bug11394(i int) returns int

735

begin

736

if i <= 0 then

737

return 0;

738

else

739

return (i in (100, 200, bug11394(i-1), 400));

740

end if;

741

end|

742

select bug11394(2)|

743

ERROR HY000: Recursive stored functions and triggers are not allowed.

744

drop function bug11394|

745

create function bug11394_1(i int) returns int

746

begin

747

if i <= 0 then

748

return 0;

749

else

750

return (select bug11394_1(i-1));

751

end if;

752

end|

753

select bug11394_1(2)|

754

ERROR HY000: Recursive stored functions and triggers are not allowed.

755

drop function bug11394_1|

756

create function bug11394_2(i int) returns int return i|

757

select bug11394_2(bug11394_2(10))|

758

bug11394_2(bug11394_2(10))

759

760

drop function bug11394_2|

761

create procedure bug11394(i int, j int)

762

begin

763

if i > 0 then

764

call bug11394(i - 1,(select 1));

765

end if;

766

end|

767

call bug11394(2, 1)|

768

ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine bug11394

769

set @@max_sp_recursion_depth=10|

770

call bug11394(2, 1)|

771

set @@max_sp_recursion_depth=default|

772

drop procedure bug11394|

773

CREATE PROCEDURE BUG_12490() HELP CONTENTS;

774

ERROR 0A000: HELP is not allowed in stored procedures

775

CREATE FUNCTION BUG_12490() RETURNS INT HELP CONTENTS;

776

ERROR 0A000: HELP is not allowed in stored procedures

777

CREATE TABLE t_bug_12490(a int);

778

CREATE TRIGGER BUG_12490 BEFORE UPDATE ON t_bug_12490 FOR EACH ROW HELP CONTENTS;

779

ERROR 0A000: HELP is not allowed in stored procedures

780

DROP TABLE t_bug_12490;

781

drop function if exists bug11834_1;

782

drop function if exists bug11834_2;

783

create function bug11834_1() returns int return 10;

784

create function bug11834_2() returns int return bug11834_1();

785

prepare stmt from "select bug11834_2()";

786

execute stmt;

787

bug11834_2()

788

789

execute stmt;

790

bug11834_2()

791

792

drop function bug11834_1;

793

execute stmt;

794

ERROR 42000: FUNCTION test.bug11834_1 does not exist

795

deallocate prepare stmt;

796

drop function bug11834_2;

797

DROP FUNCTION IF EXISTS bug12953|

798

CREATE FUNCTION bug12953() RETURNS INT

799

BEGIN

800

OPTIMIZE TABLE t1;

801

RETURN 1;

802

END|

803

ERROR 0A000: Not allowed to return a result set from a function

804

DROP FUNCTION IF EXISTS bug12995|

805

CREATE FUNCTION bug12995() RETURNS INT

806

BEGIN

807

HANDLER t1 OPEN;

808

RETURN 1;

809

END|

810

ERROR 0A000: HANDLER is not allowed in stored procedures

811

CREATE FUNCTION bug12995() RETURNS INT

812

BEGIN

813

HANDLER t1 READ FIRST;

814

RETURN 1;

815

END|

816

ERROR 0A000: HANDLER is not allowed in stored procedures

817

CREATE FUNCTION bug12995() RETURNS INT

818

BEGIN

819

HANDLER t1 CLOSE;

820

RETURN 1;

821

END|

822

ERROR 0A000: HANDLER is not allowed in stored procedures

823

SELECT bug12995()|

824

ERROR 42000: FUNCTION test.bug12995 does not exist

825

drop procedure if exists bug12712;

826

drop function if exists bug12712;

827

create procedure bug12712()

828

set session autocommit = 0;

829

select @@autocommit;

830

@@autocommit

831

832

set @au = @@autocommit;

833

call bug12712();

834

select @@autocommit;

835

@@autocommit

836

837

set session autocommit = @au;

838

create function bug12712()

839

returns int

840

begin

841

call bug12712();

842

return 0;

843

end|

844

set @x = bug12712()|

845

ERROR HY000: Not allowed to set autocommit from a stored function or trigger

846

drop procedure bug12712|

847

drop function bug12712|

848

create function bug12712()

849

returns int

850

begin

851

set session autocommit = 0;

852

return 0;

853

end|

854

ERROR HY000: Not allowed to set autocommit from a stored function or trigger

855

create function bug12712()

856

returns int

857

begin

858

set @@autocommit = 0;

859

return 0;

860

end|

861

ERROR HY000: Not allowed to set autocommit from a stored function or trigger

862

create function bug12712()

863

returns int

864

begin

865

set local autocommit = 0;

866

return 0;

867

end|

868

ERROR HY000: Not allowed to set autocommit from a stored function or trigger

869

create trigger bug12712

870

before insert on t1 for each row set session autocommit = 0;

871

ERROR HY000: Not allowed to set autocommit from a stored function or trigger

872

drop procedure if exists bug13510_1|

873

drop procedure if exists bug13510_2|

874

drop procedure if exists bug13510_3|

875

drop procedure if exists bug13510_4|

876

create procedure bug13510_1()

877

begin

878

declare password varchar(10);

879

set password = 'foo1';

880

select password;

881

end|

882

ERROR 42000: Variable 'password' must be quoted with `...`, or renamed

883

set names='foo2'|

884

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 '' at line 1

885

create procedure bug13510_2()

886

begin

887

declare names varchar(10);

888

set names = 'foo2';

889

select names;

890

end|

891

ERROR 42000: Variable 'names' must be quoted with `...`, or renamed

892

create procedure bug13510_3()

893

begin

894

declare password varchar(10);

895

set `password` = 'foo3';

896

select password;

897

end|

898

create procedure bug13510_4()

899

begin

900

declare names varchar(10);

901

set `names` = 'foo4';

902

select names;

903

end|

904

call bug13510_3()|

905

password

906

foo3

907

call bug13510_4()|

908

names

909

foo4

910

drop procedure bug13510_3|

911

drop procedure bug13510_4|

912

drop function if exists bug_13627_f|

913

CREATE TABLE t1 (a int)|

914

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DROP TRIGGER test1; END |

915

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

916

CREATE FUNCTION bug_13627_f() returns int BEGIN DROP TRIGGER test1; return 1; END |

917

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

918

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create table t2 (a int); END |

919

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

920

CREATE FUNCTION bug_13627_f() returns int BEGIN create table t2 (a int); return 1; END |

921

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

922

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create index t1_i on t1 (a); END |

923

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

924

CREATE FUNCTION bug_13627_f() returns int BEGIN create index t1_i on t1 (a); return 1; END |

925

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

926

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter table t1 add column b int; END |

927

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

928

CREATE FUNCTION bug_13627_f() returns int BEGIN alter table t1 add column b int; return 1; END |

929

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

930

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename table t1 to t2; END |

931

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

932

CREATE FUNCTION bug_13627_f() returns int BEGIN rename table t1 to t2; return 1; END |

933

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

934

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN truncate table t1; END |

935

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

936

CREATE FUNCTION bug_13627_f() returns int BEGIN truncate table t1; return 1; END |

937

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

938

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop table t1; END |

939

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

940

CREATE FUNCTION bug_13627_f() returns int BEGIN drop table t1; return 1; END |

941

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

942

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop index t1_i on t1; END |

943

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

944

CREATE FUNCTION bug_13627_f() returns int BEGIN drop index t1_i on t1; return 1; END |

945

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

946

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN unlock tables; END |

947

ERROR 0A000: UNLOCK is not allowed in stored procedures

948

CREATE FUNCTION bug_13627_f() returns int BEGIN unlock tables; return 1; END |

949

ERROR 0A000: UNLOCK is not allowed in stored procedures

950

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN LOCK TABLE t1 READ; END |

951

ERROR 0A000: LOCK is not allowed in stored procedures

952

CREATE FUNCTION bug_13627_f() returns int BEGIN LOCK TABLE t1 READ; return 1; END |

953

ERROR 0A000: LOCK is not allowed in stored procedures

954

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create database mysqltest; END |

955

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

956

CREATE FUNCTION bug_13627_f() returns int BEGIN create database mysqltest; return 1; END |

957

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

958

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop database mysqltest; END |

959

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

960

CREATE FUNCTION bug_13627_f() returns int BEGIN drop database mysqltest; return 1; END |

961

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

962

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create user 'mysqltest_1'; END |

963

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

964

CREATE FUNCTION bug_13627_f() returns int BEGIN create user 'mysqltest_1'; return 1; END |

965

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

966

CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN grant select on t1 to 'mysqltest_1'; END |

967

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

968

CREATE FUNCTION bug21975() returns int BEGIN grant select on t1 to 'mysqltest_1'; return 1; END |

969

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

970

CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke select on t1 from 'mysqltest_1'; END |

971

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

972

CREATE FUNCTION bug21975() returns int BEGIN revoke select on t1 from 'mysqltest_1'; return 1; END |

973

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

974

CREATE TRIGGER bug21975 BEFORE INSERT ON t1 FOR EACH ROW BEGIN revoke all privileges on *.* from 'mysqltest_1'; END |

975

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

976

CREATE FUNCTION bug21975() returns int BEGIN revoke all privileges on *.* from 'mysqltest_1'; return 1; END |

977

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

978

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop user 'mysqltest_1'; END |

979

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

980

CREATE FUNCTION bug_13627_f() returns int BEGIN drop user 'mysqltest_1'; return 1; END |

981

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

982

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; END |

983

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

984

CREATE FUNCTION bug_13627_f() returns int BEGIN rename user 'mysqltest_2' to 'mysqltest_1'; return 1; END |

985

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

986

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create view v1 as select 1; END |

987

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

988

CREATE FUNCTION bug_13627_f() returns int BEGIN create view v1 as select 1; return 1; END |

989

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

990

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END |

991

ERROR 0A000: ALTER VIEW is not allowed in stored procedures

992

CREATE FUNCTION bug_13627_f() returns int BEGIN alter view v1 as select 1; return 1; END |

993

ERROR 0A000: ALTER VIEW is not allowed in stored procedures

994

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop view v1; END |

995

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

996

CREATE FUNCTION bug_13627_f() returns int BEGIN drop view v1; return 1; END |

997

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

998

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create trigger tr2 before insert on t1 for each row do select 1; END |

999

ERROR 2F003: Can't create a TRIGGER from within another stored routine

1000

CREATE FUNCTION bug_13627_f() returns int BEGIN create trigger tr2 before insert on t1 for each row do select 1; return 1; END |

1001

ERROR 2F003: Can't create a TRIGGER from within another stored routine

1002

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop function bug_13627_f; END |

1003

ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine

1004

CREATE FUNCTION bug_13627_f() returns int BEGIN drop function bug_13627_f; return 1; END |

1005

ERROR HY000: Can't drop or alter a FUNCTION from within another stored routine

1006

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create function f2 () returns int return 1; END |

1007

ERROR 2F003: Can't create a FUNCTION from within another stored routine

1008

CREATE FUNCTION bug_13627_f() returns int BEGIN create function f2 () returns int return 1; return 1; END |

1009

ERROR 2F003: Can't create a FUNCTION from within another stored routine

1010

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW

1011

BEGIN

1012

CREATE TEMPORARY TABLE t2 (a int);

1013

DROP TEMPORARY TABLE t2;

1014

END |

1015

CREATE FUNCTION bug_13627_f() returns int

1016

BEGIN

1017

CREATE TEMPORARY TABLE t2 (a int);

1018

DROP TEMPORARY TABLE t2;

1019

return 1;

1020

END |

1021

drop table t1|

1022

drop function bug_13627_f|

1023

drop function if exists bug12329;

1024

Warnings:

1025

Note 1305 FUNCTION test.bug12329 does not exist

1026

create table t1 as select 1 a;

1027

create table t2 as select 1 a;

1028

create function bug12329() returns int return (select a from t1);

1029

prepare stmt1 from 'select bug12329()';

1030

execute stmt1;

1031

bug12329()

1032

1033

drop function bug12329;

1034

create function bug12329() returns int return (select a+100 from t2);

1035

select bug12329();

1036

bug12329()

1037

101

1038

execute stmt1;

1039

bug12329()

1040

101

1041

deallocate prepare stmt1;

1042

drop function bug12329;

1043

drop table t1, t2;

1044

create database mysqltest1;

1045

use mysqltest1;

1046

drop database mysqltest1;

1047

create function f1() returns int return 1;

1048

ERROR 3D000: No database selected

1049

create procedure p1(out param1 int)

1050

begin

1051

select count(*) into param1 from t3;

1052

end|

1053

ERROR 3D000: No database selected

1054

use test;

1055

DROP PROCEDURE IF EXISTS bug13037_p1;

1056

DROP PROCEDURE IF EXISTS bug13037_p2;

1057

DROP PROCEDURE IF EXISTS bug13037_p3;

1058

CREATE PROCEDURE bug13037_p1()

1059

BEGIN

1060

IF bug13037_foo THEN

1061

SELECT 1;

1062

END IF;

1063

END|

1064

CREATE PROCEDURE bug13037_p2()

1065

BEGIN

1066

SET @bug13037_foo = bug13037_bar;

1067

END|

1068

CREATE PROCEDURE bug13037_p3()

1069

BEGIN

1070

SELECT bug13037_foo;

1071

END|

1072

1073

CALL bug13037_p1();

1074

ERROR 42S22: Unknown column 'bug13037_foo' in 'field list'

1075

CALL bug13037_p2();

1076

ERROR 42S22: Unknown column 'bug13037_bar' in 'field list'

1077

CALL bug13037_p3();

1078

ERROR 42S22: Unknown column 'bug13037_foo' in 'field list'

1079

CALL bug13037_p1();

1080

ERROR 42S22: Unknown column 'bug13037_foo' in 'field list'

1081

CALL bug13037_p2();

1082

ERROR 42S22: Unknown column 'bug13037_bar' in 'field list'

1083

CALL bug13037_p3();

1084

ERROR 42S22: Unknown column 'bug13037_foo' in 'field list'

1085

DROP PROCEDURE bug13037_p1;

1086

DROP PROCEDURE bug13037_p2;

1087

DROP PROCEDURE bug13037_p3;

1088

create database mysqltest1;

1089

create database mysqltest2;

1090

use mysqltest1;

1091

drop database mysqltest1;

1092

create procedure mysqltest2.p1() select version();

1093

create procedure p2() select version();

1094

ERROR 3D000: No database selected

1095

use mysqltest2;

1096

show procedure status;

1097

Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation

1098

mysqltest2 p1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci

1099

drop database mysqltest2;

1100

use test;

1101

DROP FUNCTION IF EXISTS bug13012|

1102

CREATE FUNCTION bug13012() RETURNS INT

1103

BEGIN

1104

REPAIR TABLE t1;

1105

RETURN 1;

1106

END|

1107

ERROR 0A000: Not allowed to return a result set from a function

1108

create table t1 (a int)|

1109

CREATE PROCEDURE bug13012_1() REPAIR TABLE t1|

1110

CREATE FUNCTION bug13012_2() RETURNS INT

1111

BEGIN

1112

CALL bug13012_1();

1113

RETURN 1;

1114

END|

1115

SELECT bug13012_2()|

1116

ERROR 0A000: Not allowed to return a result set from a function

1117

drop table t1|

1118

drop procedure bug13012_1|

1119

drop function bug13012_2|

1120

drop function if exists bug11555_1;

1121

drop function if exists bug11555_2;

1122

drop view if exists v1, v2, v3, v4;

1123

create function bug11555_1() returns int return (select max(i) from t1);

1124

create function bug11555_2() returns int return bug11555_1();

1125

create view v1 as select bug11555_1();

1126

drop view v1;

1127

create view v2 as select bug11555_2();

1128

drop view v2;

1129

create table t1 (i int);

1130

create view v1 as select bug11555_1();

1131

create view v2 as select bug11555_2();

1132

create view v3 as select * from v1;

1133

drop table t1;

1134

select * from v1;

1135

ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

1136

select * from v2;

1137

ERROR HY000: View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

1138

select * from v3;

1139

ERROR HY000: View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

1140

create view v4 as select * from v1;

1141

drop view v1, v2, v3, v4;

1142

drop function bug11555_1;

1143

drop function bug11555_2;

1144

create table t1 (i int);

1145

create table t2 (i int);

1146

create trigger t1_ai after insert on t1 for each row insert into t2 values (new.i);

1147

create view v1 as select * from t1;

1148

drop table t2;

1149

insert into v1 values (1);

1150

ERROR 42S02: Table 'test.t2' doesn't exist

1151

drop trigger t1_ai;

1152

create function bug11555_1() returns int return (select max(i) from t2);

1153

create trigger t1_ai after insert on t1 for each row set @a:=bug11555_1();

1154

insert into v1 values (2);

1155

ERROR 42S02: Table 'test.t2' doesn't exist

1156

drop function bug11555_1;

1157

drop table t1;

1158

drop view v1;

1159

drop procedure if exists ` bug15658`;

1160

create procedure ``() select 1;

1161

ERROR 42000: Incorrect routine name ''

1162

create procedure ` `() select 1;

1163

ERROR 42000: Incorrect routine name ' '

1164

create procedure `bug15658 `() select 1;

1165

ERROR 42000: Incorrect routine name 'bug15658 '

1166

create procedure ``.bug15658() select 1;

1167

ERROR 42000: Incorrect database name ''

1168

create procedure `x `.bug15658() select 1;

1169

ERROR 42000: Incorrect database name 'x '

1170

create procedure ` bug15658`() select 1;

1171

call ` bug15658`();

1172

1173

1174

show procedure status;

1175

Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation

1176

test bug15658 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci

1177

drop procedure ` bug15658`;

1178

drop function if exists bug14270;

1179

drop table if exists t1;

1180

create table t1 (s1 int primary key);

1181

create function bug14270() returns int

1182

begin

1183

load index into cache t1;

1184

return 1;

1185

end|

1186

ERROR 0A000: Not allowed to return a result set from a function

1187

create function bug14270() returns int

1188

begin

1189

cache index t1 key (`primary`) in keycache1;

1190

return 1;

1191

end|

1192

ERROR 0A000: Not allowed to return a result set from a function

1193

drop table t1;

1194

drop procedure if exists bug15091;

1195

create procedure bug15091()

1196

begin

1197

declare selectstr varchar(6000) default ' ';

1198

declare conditionstr varchar(5000) default '';

1199

set selectstr = concat(selectstr,

1200

' and ',

1201

c.operatorid,

1202

'in (',conditionstr, ')');

1203

end|

1204

call bug15091();

1205

ERROR 42S02: Unknown table 'c' in field list

1206

drop procedure bug15091;

1207

drop function if exists bug16896;

1208

create aggregate function bug16896() returns int return 1;

1209

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 '() returns int return 1' at line 1

1210

DROP PROCEDURE IF EXISTS bug14702;

1211

CREATE IF NOT EXISTS PROCEDURE bug14702()

1212

BEGIN

1213

END;

1214

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 'IF NOT EXISTS PROCEDURE bug14702()

1215

BEGIN

1216

END' at line 1

1217

CREATE PROCEDURE IF NOT EXISTS bug14702()

1218

BEGIN

1219

END;

1220

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 'IF NOT EXISTS bug14702()

1221

BEGIN

1222

END' at line 1

1223

DROP TABLE IF EXISTS t1;

1224

CREATE TABLE t1 (i INT);

1225

CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO @a;

1226

ERROR HY000: View's SELECT contains a 'INTO' clause

1227

CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO DUMPFILE "file";

1228

ERROR HY000: View's SELECT contains a 'INTO' clause

1229

CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 INTO OUTFILE "file";

1230

ERROR HY000: View's SELECT contains a 'INTO' clause

1231

CREATE PROCEDURE bug20953()

1232

CREATE VIEW v AS SELECT i FROM t1 PROCEDURE ANALYSE();

1233

ERROR HY000: View's SELECT contains a 'PROCEDURE' clause

1234

CREATE PROCEDURE bug20953() CREATE VIEW v AS SELECT 1 FROM (SELECT 1) AS d1;

1235

ERROR HY000: View's SELECT contains a subquery in the FROM clause

1236

CREATE PROCEDURE bug20953(i INT) CREATE VIEW v AS SELECT i;

1237

ERROR HY000: View's SELECT contains a variable or parameter

1238

CREATE PROCEDURE bug20953()

1239

BEGIN

1240

DECLARE i INT;

1241

CREATE VIEW v AS SELECT i;

1242

END |

1243

ERROR HY000: View's SELECT contains a variable or parameter

1244

PREPARE stmt FROM "CREATE VIEW v AS SELECT ?";

1245

ERROR HY000: View's SELECT contains a variable or parameter

1246

DROP TABLE t1;

1247

drop tables if exists t1;

1248

drop procedure if exists bug24491;

1249

create table t1 (id int primary key auto_increment, value varchar(10));

1250

insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD');

1251

create procedure bug24491()

1252

insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as v) as y on duplicate key update v = 'DUP';

1253

call bug24491();

1254

ERROR 42S22: Unknown column 'v' in 'field list'

1255

call bug24491();

1256

ERROR 42S22: Unknown column 'v' in 'field list'

1257

drop procedure bug24491;

1258

create procedure bug24491()

1259

insert into t1 (id, value) select * from (select 4 as id, 'FOURTH' as value) as y on duplicate key update y.value = 'DUP';

1260

call bug24491();

1261

ERROR 42S22: Unknown column 'y.value' in 'field list'

1262

call bug24491();

1263

ERROR 42S22: Unknown column 'y.value' in 'field list'

1264

drop procedure bug24491;

1265

drop tables t1;

1266

DROP FUNCTION IF EXISTS bug18914_f1;

1267

DROP FUNCTION IF EXISTS bug18914_f2;

1268

DROP PROCEDURE IF EXISTS bug18914_p1;

1269

DROP PROCEDURE IF EXISTS bug18914_p2;

1270

DROP TABLE IF EXISTS t1, t2;

1271

CREATE TABLE t1 (i INT);

1272

CREATE PROCEDURE bug18914_p1() CREATE TABLE t2 (i INT);

1273

CREATE PROCEDURE bug18914_p2() DROP TABLE IF EXISTS no_such_table;

1274

CREATE FUNCTION bug18914_f1() RETURNS INT

1275

BEGIN

1276

CALL bug18914_p1();

1277

RETURN 1;

1278

END |

1279

CREATE FUNCTION bug18914_f2() RETURNS INT

1280

BEGIN

1281

CALL bug18914_p2();

1282

RETURN 1;

1283

END |

1284

CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW

1285

CALL bug18914_p1();

1286

INSERT INTO t1 VALUES (1);

1287

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

1288

SELECT bug18914_f1();

1289

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

1290

SELECT bug18914_f2();

1291

ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.

1292

SELECT * FROM t2;

1293

ERROR 42S02: Table 'test.t2' doesn't exist

1294

DROP FUNCTION bug18914_f1;

1295

DROP FUNCTION bug18914_f2;

1296

DROP PROCEDURE bug18914_p1;

1297

DROP PROCEDURE bug18914_p2;

1298

DROP TABLE t1;

1299

drop table if exists bogus_table_20713;

1300

drop function if exists func_20713_a;

1301

drop function if exists func_20713_b;

1302

create table bogus_table_20713( id int(10) not null primary key);

1303

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

1304

create function func_20713_a() returns int(11)

1305

begin

1306

declare id int;

1307

declare continue handler for sqlexception set id=null;

1308

set @in_func := 1;

1309

set id = (select id from bogus_table_20713 where id = 3);

1310

set @in_func := 2;

1311

return id;

1312

end//

1313

create function func_20713_b() returns int(11)

1314

begin

1315

declare id int;

1316

declare continue handler for sqlstate value '42S02' set id=null;

1317

set @in_func := 1;

1318

set id = (select id from bogus_table_20713 where id = 3);

1319

set @in_func := 2;

1320

return id;

1321

end//

1322

set @in_func := 0;

1323

select func_20713_a();

1324

func_20713_a()

1325

NULL

1326

select @in_func;

1327

@in_func

1328

1329

set @in_func := 0;

1330

select func_20713_b();

1331

func_20713_b()

1332

NULL

1333

select @in_func;

1334

@in_func

1335

1336

drop table bogus_table_20713;

1337

set @in_func := 0;

1338

select func_20713_a();

1339

func_20713_a()

1340

NULL

1341

select @in_func;

1342

@in_func

1343

1344

set @in_func := 0;

1345

select func_20713_b();

1346

func_20713_b()

1347

NULL

1348

select @in_func;

1349

@in_func

1350

1351

drop function if exists func_20713_a;

1352

drop function if exists func_20713_b;

1353

drop table if exists table_25345_a;

1354

drop table if exists table_25345_b;

1355

drop procedure if exists proc_25345;

1356

drop function if exists func_25345;

1357

drop function if exists func_25345_b;

1358

create table table_25345_a (a int);

1359

create table table_25345_b (b int);

1360

create procedure proc_25345()

1361

begin

1362

declare c1 cursor for select a from table_25345_a;

1363

declare c2 cursor for select b from table_25345_b;

1364

select 1 as result;

1365

end ||

1366

create function func_25345() returns int(11)

1367

begin

1368

call proc_25345();

1369

return 1;

1370

end ||

1371

create function func_25345_b() returns int(11)

1372

begin

1373

declare c1 cursor for select a from table_25345_a;

1374

declare c2 cursor for select b from table_25345_b;

1375

return 1;

1376

end ||

1377

call proc_25345();

1378

result

1379

1380

select func_25345();

1381

ERROR 0A000: Not allowed to return a result set from a function

1382

select func_25345_b();

1383

func_25345_b()

1384

1385

drop table table_25345_a;

1386

call proc_25345();

1387

result

1388

1389

select func_25345();

1390

ERROR 0A000: Not allowed to return a result set from a function

1391

select func_25345_b();

1392

func_25345_b()

1393

1394

drop table table_25345_b;

1395

drop procedure proc_25345;

1396

drop function func_25345;

1397

drop function func_25345_b;

1398

End of 5.0 tests

1399

drop function if exists bug20701;

1400

create function bug20701() returns varchar(25) binary return "test";

1401

ERROR 42000: This version of MySQL doesn't yet support 'return value collation'

1402

create function bug20701() returns varchar(25) return "test";

1403

drop function bug20701;

1404

create procedure proc_26503_error_1()

1405

begin

1406

retry:

1407

repeat

1408

begin

1409

declare continue handler for sqlexception

1410

begin

1411

iterate retry;

1412

end

1413

select "do something";

1414

end

1415

until true end repeat retry;

1416

end//

1417

ERROR 42000: ITERATE with no matching label: retry

1418

create procedure proc_26503_error_2()

1419

begin

1420

retry:

1421

repeat

1422

begin

1423

declare continue handler for sqlexception

1424

iterate retry;

1425

select "do something";

1426

end

1427

until true end repeat retry;

1428

end//

1429

ERROR 42000: ITERATE with no matching label: retry

1430

create procedure proc_26503_error_3()

1431

begin

1432

retry:

1433

repeat

1434

begin

1435

declare continue handler for sqlexception

1436

begin

1437

leave retry;

1438

end

1439

select "do something";

1440

end

1441

until true end repeat retry;

1442

end//

1443

ERROR 42000: LEAVE with no matching label: retry

1444

create procedure proc_26503_error_4()

1445

begin

1446

retry:

1447

repeat

1448

begin

1449

declare continue handler for sqlexception

1450

leave retry;

1451

select "do something";

1452

end

1453

until true end repeat retry;

1454

end//

1455

ERROR 42000: LEAVE with no matching label: retry

1456

drop procedure if exists proc_28360;

1457

drop function if exists func_28360;

1458

CREATE PROCEDURE proc_28360()

1459

BEGIN

1460

ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME;

1461

END//

1462

ERROR HY000: Can't drop or alter a DATABASE from within another stored routine

1463

CREATE FUNCTION func_28360() RETURNS int

1464

BEGIN

1465

ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME;

1466

RETURN 0;

1467

END//

1468

ERROR HY000: Can't drop or alter a DATABASE from within another stored routine

1469

DROP PROCEDURE IF EXISTS p1;

1470

CREATE PROCEDURE p1()

1471

BEGIN

1472

DECLARE c char(100);

1473

DECLARE cur1 CURSOR FOR SHOW TABLES;

1474

OPEN cur1;

1475

FETCH cur1 INTO c;

1476

select c;

1477

CLOSE cur1;

1478

END|

1479

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 'SHOW TABLES;

1480

OPEN cur1;

1481

FETCH cur1 INTO c;

1482

select c;

1483

CLOSE cur1;

1484

END' at line 4

1485

DROP DATABASE IF EXISTS mysqltest;

1486

CREATE DATABASE mysqltest;

1487

USE mysqltest;

1488

DROP DATABASE mysqltest;

1489

SELECT inexistent(), 1 + ,;

1490

ERROR 42000: FUNCTION inexistent does not exist

1491

SELECT inexistent();

1492

ERROR 42000: FUNCTION inexistent does not exist

1493

SELECT .inexistent();

1494

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 '()' at line 1

1495

SELECT ..inexistent();

1496

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 '.inexistent()' at line 1

1497

USE test;

1498

create function f1() returns int

1499

begin

1500

set @test = 1, password = password('foo');

1501

return 1;

1502

end|

1503

ERROR HY000: Not allowed to set autocommit from a stored function or trigger

1504

create trigger t1

1505

before insert on t2 for each row set password = password('foo');|

1506

ERROR HY000: Not allowed to set autocommit from a stored function or trigger

1507

drop function if exists f1;

1508

drop function if exists f2;

1509

drop table if exists t1, t2;

1510

create function f1() returns int

1511

begin

1512

drop temporary table t1;

1513

return 1;

1514

end|

1515

create temporary table t1 as select f1();

1516

ERROR HY000: Can't reopen table: 't1'

1517

create function f2() returns int

1518

begin

1519

create temporary table t2 as select f1();

1520

return 1;

1521

end|

1522

create temporary table t1 as select f2();

1523

ERROR HY000: Can't reopen table: 't1'

1524

drop function f1;

1525

drop function f2;

1526

create function f1() returns int

1527

begin

1528

drop temporary table t2,t1;

1529

return 1;

1530

end|

1531

create function f2() returns int

1532

begin

1533

create temporary table t2 as select f1();

1534

return 1;

1535

end|

1536

create temporary table t1 as select f2();

1537

ERROR HY000: Can't reopen table: 't2'

1538

drop function f1;

1539

drop function f2;

1540

create temporary table t2(a int);

1541

select * from t2;

1542

1543

create function f2() returns int

1544

begin

1545

drop temporary table t2;

1546

return 1;

1547

end|

1548

select f2();

1549

f2()

1550

1551

drop function f2;

1552

drop table t2;

1553

ERROR 42S02: Unknown table 'test.t2'

1554

End of 5.1 tests

1555

drop procedure if exists proc_33983_a;

1556

drop procedure if exists proc_33983_b;

1557

drop procedure if exists proc_33983_c;

1558

drop procedure if exists proc_33983_d;

1559

create procedure proc_33983_a()

1560

begin

1561

label1:

1562

begin

1563

label2:

1564

begin

1565

select 1;

1566

end label1;

1567

end;

1568

end|

1569

ERROR 42000: End-label label1 without match

1570

create procedure proc_33983_b()

1571

begin

1572

label1:

1573

repeat

1574

label2:

1575

repeat

1576

select 1;

1577

until FALSE end repeat label1;

1578

until FALSE end repeat;

1579

end|

1580

ERROR 42000: End-label label1 without match

1581

create procedure proc_33983_c()

1582

begin

1583

label1:

1584

while TRUE do

1585

label2:

1586

while TRUE do

1587

select 1;

1588

end while label1;

1589

end while;

1590

end|

1591

ERROR 42000: End-label label1 without match

1592

create procedure proc_33983_d()

1593

begin

1594

label1:

1595

loop

1596

label2:

1597

loop

1598

select 1;

1599

end loop label1;

1600

end loop;

1601

end|

1602

ERROR 42000: End-label label1 without match

1603

CREATE TABLE t1 (a INT)|

1604

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

1605

CREATE PROCEDURE p1(a INT) BEGIN END|

1606

CALL p1((SELECT * FROM t1))|

1607

ERROR 21000: Subquery returns more than 1 row

1608

DROP PROCEDURE IF EXISTS p1|

1609

DROP TABLE t1|

1610

drop procedure if exists p1;

1611

create procedure p1()

1612

begin

1613

create table t1 (a int) engine=MyISAM;

1614

drop table t1;

1615

end|

1616

call p1();

1617

call p1();

1618

drop procedure p1;

1619

drop procedure if exists proc_8759;

1620

create procedure proc_8759()

1621

begin

1622

declare should_be_illegal condition for sqlstate '00000';

1623

declare continue handler for should_be_illegal set @x=0;

1624

end$$

1625

ERROR 42000: Bad SQLSTATE: '00000'

1626

create procedure proc_8759()

1627

begin

1628

declare continue handler for sqlstate '00000' set @x=0;

1629

end$$

1630

ERROR 42000: Bad SQLSTATE: '00000'

1631

drop procedure if exists proc_36510;

1632

create procedure proc_36510()

1633

begin

1634

declare should_be_illegal condition for sqlstate '00123';

1635

declare continue handler for should_be_illegal set @x=0;

1636

end$$

1637

ERROR 42000: Bad SQLSTATE: '00123'

1638

create procedure proc_36510()

1639

begin

1640

declare continue handler for sqlstate '00123' set @x=0;

1641

end$$

1642

ERROR 42000: Bad SQLSTATE: '00123'

1643

create procedure proc_36510()

1644

begin

1645

declare should_be_illegal condition for 0;

1646

declare continue handler for should_be_illegal set @x=0;

1647

end$$

1648

ERROR HY000: Incorrect CONDITION value: '0'

1649

create procedure proc_36510()

1650

begin

1651

declare continue handler for 0 set @x=0;

1652

end$$

1653

ERROR HY000: Incorrect CONDITION value: '0'

1654

drop procedure if exists p1;

1655

set @old_recursion_depth = @@max_sp_recursion_depth;

1656

set @@max_sp_recursion_depth = 255;

1657

create procedure p1(a int)

1658

begin

1659

declare continue handler for 1436 -- ER_STACK_OVERRUN_NEED_MORE

1660

select 'exception';

1661

call p1(a+1);

1662

end|

1663

call p1(1);

1664

set @@max_sp_recursion_depth = @old_recursion_depth;

1665

drop procedure p1;

1666

LOAD DATA INFILE '../../tmp/proc.txt' INTO TABLE mysql.proc;

1667

CREATE TABLE t1 (a INT, b INT);

1668

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

1669

SELECT MAX (a) FROM t1 WHERE b = 999999;

1670

ERROR 42000: FUNCTION test.MAX does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

1671

SELECT AVG (a) FROM t1 WHERE b = 999999;

1672

AVG (a)

1673

NULL

1674

SELECT non_existent (a) FROM t1 WHERE b = 999999;

1675

ERROR 42000: FUNCTION test.non_existent does not exist

1676

DROP TABLE t1;

1677

CREATE TABLE t1 ( f2 INTEGER, f3 INTEGER );

1678

INSERT INTO t1 VALUES ( 1, 1 );

1679

CREATE FUNCTION func_1 () RETURNS INTEGER

1680

BEGIN

1681

INSERT INTO t1 SELECT * FROM t1 ;

1682

RETURN 1 ;

1683

END|

1684

INSERT INTO t1 SELECT * FROM (SELECT 2 AS f1, 2 AS f2) AS A WHERE func_1() = 5;

1685

ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

1686

DROP FUNCTION func_1;

1687

DROP TABLE t1;

1688

1689

# Bug #47788: Crash in TABLE_LIST::hide_view_error on UPDATE + VIEW +

1690

# SP + MERGE + ALTER

1691

1692

CREATE TABLE t1 (pk INT, b INT, KEY (b));

1693

CREATE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT * FROM t1;

1694

CREATE PROCEDURE p1 (a int) UPDATE IGNORE v1 SET b = a;

1695

CALL p1(5);

1696

ERROR HY000: The target table v1 of the UPDATE is not updatable

1697

ALTER TABLE t1 CHANGE COLUMN b b2 INT;

1698

CALL p1(7);

1699

ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

1700

DROP PROCEDURE p1;

1701

DROP VIEW v1;

1702

DROP TABLE t1;

1703

1704

# Bug#12428824 - PARSER STACK OVERFLOW AND CRASH IN SP_ADD_USED_ROUTINE

1705

# WITH OBSCURE QUERY

1706

1707

SELECT very_long_fn_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999();

1708

ERROR 42000: Identifier name 'very_long_fn_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222' is too long

1709

CALL very_long_pr_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999();

1710

ERROR 42000: Identifier name 'very_long_pr_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222' is too long

1711

SELECT very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999.simple_func();

1712

ERROR 42000: Incorrect database name 'very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222'

1713

CALL very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999.simple_proc();

1714

ERROR 42000: Incorrect database name 'very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222'

1715

SELECT db_name.very_long_fn_name_111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222999999999999999999999();

1716

ERROR 42000: Identifier name 'very_long_fn_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222' is too long

1717

CALL db_name.very_long_pr_name_111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222999999999999999999999();

1718

ERROR 42000: Identifier name 'very_long_pr_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222' is too long

1719

End of 5.1 tests

1720

1721

# Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP

1722

1723

1724

# - Case 1

1725

1726

DROP PROCEDURE IF EXISTS p1;

1727

DROP PROCEDURE IF EXISTS p2;

1728

DROP PROCEDURE IF EXISTS p3;

1729

DROP PROCEDURE IF EXISTS p4;

1730

DROP PROCEDURE IF EXISTS p5;

1731

DROP PROCEDURE IF EXISTS p6;

1732

CREATE PROCEDURE p1()

1733

BEGIN

1734

SELECT CAST('10 ' as unsigned integer);

1735

SELECT 1;

1736

CALL p2();

1737

END|

1738

CREATE PROCEDURE p2()

1739

BEGIN

1740

SELECT CAST('10 ' as unsigned integer);

1741

END|

1742

CALL p1();

1743

CAST('10 ' as unsigned integer)

1744

1745

1746

1747

CAST('10 ' as unsigned integer)

1748

1749

Warnings:

1750

Warning 1292 Truncated incorrect INTEGER value: '10 '

1751

DROP PROCEDURE p1;

1752

DROP PROCEDURE p2;

1753

1754

# - Case 2

1755

1756

DROP TABLE IF EXISTS t1;

1757

CREATE TABLE t1(a INT);

1758

CREATE PROCEDURE p1()

1759

BEGIN

1760

DECLARE c INT DEFAULT 0;

1761

DECLARE CONTINUE HANDLER FOR SQLWARNING

1762

BEGIN

1763

SET c = c + 1;

1764

SELECT 'Warning caught!' AS Msg;

1765

END;

1766

CALL p2(); # 1 warning

1767

CALL p3(); # 1 warning

1768

CALL p4(); # No warnings

1769

CALL p5(); # 1 warning

1770

SELECT c;

1771

SELECT @@warning_count;

1772

SHOW WARNINGS;

1773

END|

1774

CREATE PROCEDURE p2()

1775

BEGIN

1776

SELECT CAST('2 ' as unsigned integer);

1777

END|

1778

CREATE PROCEDURE p3()

1779

BEGIN

1780

SELECT CAST('3 ' as unsigned integer);

1781

SELECT 1; # does not clear the warning

1782

END|

1783

CREATE PROCEDURE p4()

1784

BEGIN

1785

SELECT CAST('4 ' as unsigned integer);

1786

INSERT INTO t1 VALUES(1); # Clears the warning

1787

END|

1788

CREATE PROCEDURE p5()

1789

BEGIN

1790

SELECT CAST('5 ' as unsigned integer);

1791

CALL p2();

1792

END|

1793

CREATE PROCEDURE p6()

1794

BEGIN

1795

SELECT CAST('6 ' as unsigned integer);

1796

SHOW WARNINGS;

1797

END|

1798

CREATE PROCEDURE p7()

1799

BEGIN

1800

DECLARE c INT DEFAULT 0;

1801

DECLARE CONTINUE HANDLER FOR SQLWARNING

1802

BEGIN

1803

SET c = c + 1;

1804

SELECT 'Warning caught!' AS Msg;

1805

END;

1806

CALL p6();

1807

SELECT c;

1808

END|

1809

1810

CALL p1();

1811

CAST('2 ' as unsigned integer)

1812

1813

Msg

1814

Warning caught!

1815

CAST('3 ' as unsigned integer)

1816

1817

1818

1819

Msg

1820

Warning caught!

1821

CAST('4 ' as unsigned integer)

1822

1823

CAST('5 ' as unsigned integer)

1824

1825

CAST('2 ' as unsigned integer)

1826

1827

Msg

1828

Warning caught!

1829

1830

1831

@@warning_count

1832

1833

Level Code Message

1834

1835

CALL p7();

1836

CAST('6 ' as unsigned integer)

1837

1838

Level Code Message

1839

Warning 1292 Truncated incorrect INTEGER value: '6 '

1840

Msg

1841

Warning caught!

1842

1843

1844

1845

DROP PROCEDURE p1;

1846

DROP PROCEDURE p2;

1847

DROP PROCEDURE p3;

1848

DROP PROCEDURE p4;

1849

DROP PROCEDURE p5;

1850

DROP PROCEDURE p6;

1851

DROP PROCEDURE p7;

1852

DROP TABLE t1;

1853

1854

# - Case 3: check that "Exception trumps No Data".

1855

1856

DROP TABLE IF EXISTS t1;

1857

CREATE TABLE t1(a INT);

1858

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

1859

CREATE PROCEDURE p1()

1860

BEGIN

1861

DECLARE c CURSOR FOR SELECT a FROM t1;

1862

OPEN c;

1863

BEGIN

1864

DECLARE v1 INT;

1865

DECLARE v2 INT;

1866

DECLARE EXIT HANDLER FOR SQLEXCEPTION

1867

SELECT "Error caught (expected)";

1868

DECLARE EXIT HANDLER FOR NOT FOUND

1869

SELECT "End of Result Set found!";

1870

WHILE TRUE DO

1871

FETCH c INTO v1, v2;

1872

END WHILE;

1873

END;

1874

CLOSE c;

1875

SELECT a INTO @foo FROM t1 LIMIT 1; # Clear warning stack

1876

END|

1877

CALL p1();

1878

Error caught (expected)

1879

Error caught (expected)

1880

DROP PROCEDURE p1;

1881

DROP TABLE t1;

1882

1883

# Bug#36185: Incorrect precedence for warning and exception handlers

1884

1885

DROP TABLE IF EXISTS t1;

1886

DROP PROCEDURE IF EXISTS p1;

1887

CREATE TABLE t1 (a INT, b INT NOT NULL);

1888

CREATE PROCEDURE p1()

1889

BEGIN

1890

DECLARE CONTINUE HANDLER FOR SQLWARNING SELECT 'warning';

1891

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'exception';

1892

INSERT INTO t1 VALUES (CAST('10 ' AS SIGNED), NULL);

1893

END|

1894

CALL p1();

1895

exception

1896

exception

1897

DROP TABLE t1;

1898

DROP PROCEDURE p1;

1899

1900

# Bug#5889: Exit handler for a warning doesn't hide the warning in trigger

1901

1902

CREATE TABLE t1(a INT, b INT);

1903

INSERT INTO t1 VALUES (1, 2);

1904

CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW

1905

BEGIN

1906

DECLARE EXIT HANDLER FOR SQLWARNING

1907

SET NEW.a = 10;

1908

SET NEW.a = 99999999999;

1909

END|

1910

UPDATE t1 SET b = 20;

1911

SHOW WARNINGS;

1912

Level Code Message

1913

SELECT * FROM t1;

1914

a b

1915

10 20

1916

DROP TRIGGER t1_bu;

1917

DROP TABLE t1;

1918

1919

# Bug#9857: Stored procedures: handler for sqlwarning ignored

1920

1921

SET @sql_mode_saved = @@sql_mode;

1922

SET sql_mode = traditional;

1923

CREATE PROCEDURE p1()

1924

BEGIN

1925

DECLARE CONTINUE HANDLER FOR SQLWARNING

1926

SELECT 'warning caught (expected)';

1927

SELECT 5 / 0;

1928

END|

1929

CREATE PROCEDURE p2()

1930

BEGIN

1931

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

1932

SELECT 'error caught (unexpected)';

1933

SELECT 5 / 0;

1934

END|

1935

CALL p1();

1936

5 / 0

1937

NULL

1938

warning caught (expected)

1939

warning caught (expected)

1940

SHOW WARNINGS;

1941

Level Code Message

1942

CALL p2();

1943

5 / 0

1944

NULL

1945

Warnings:

1946

Warning 1365 Division by 0

1947

SHOW WARNINGS;

1948

Level Code Message

1949

Warning 1365 Division by 0

1950

DROP PROCEDURE p1;

1951

DROP PROCEDURE p2;

1952

SET sql_mode = @sql_mode_saved;

1953

1954

# Bug#55850: Trigger warnings not cleared.

1955

1956

DROP TABLE IF EXISTS t1;

1957

DROP TABLE IF EXISTS t2;

1958

DROP PROCEDURE IF EXISTS p1;

1959

CREATE TABLE t1(x SMALLINT, y SMALLINT, z SMALLINT);

1960

CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT,

1961

d SMALLINT, e SMALLINT, f SMALLINT);

1962

CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW

1963

INSERT INTO t2(a, b, c) VALUES(99999, 99999, 99999);

1964

CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW

1965

INSERT INTO t2(d, e, f) VALUES(99999, 99999, 99999);

1966

CREATE PROCEDURE p1()

1967

INSERT INTO t1 VALUES(99999, 99999, 99999);

1968

1969

CALL p1();

1970

Warnings:

1971

Warning 1264 Out of range value for column 'x' at row 1

1972

Warning 1264 Out of range value for column 'y' at row 1

1973

Warning 1264 Out of range value for column 'z' at row 1

1974

1975

SHOW WARNINGS;

1976

Level Code Message

1977

Warning 1264 Out of range value for column 'x' at row 1

1978

Warning 1264 Out of range value for column 'y' at row 1

1979

Warning 1264 Out of range value for column 'z' at row 1

1980

1981

DROP TABLE t1;

1982

DROP TABLE t2;

1983

DROP PROCEDURE p1;

1984

# ----------------------------------------------------------------------

1985

CREATE TABLE t1(x SMALLINT, y SMALLINT, z SMALLINT);

1986

CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT NOT NULL);

1987

CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW

1988

BEGIN

1989

INSERT INTO t2 VALUES(

1990

CAST('111111 ' AS SIGNED),

1991

CAST('222222 ' AS SIGNED),

1992

NULL);

1993

END|

1994

CREATE PROCEDURE p1()

1995

INSERT INTO t1 VALUES(99999, 99999, 99999);

1996

1997

CALL p1();

1998

ERROR 23000: Column 'c' cannot be null

1999

2000

SHOW WARNINGS;

2001

Level Code Message

2002

Warning 1264 Out of range value for column 'x' at row 1

2003

Warning 1264 Out of range value for column 'y' at row 1

2004

Warning 1264 Out of range value for column 'z' at row 1

2005

Warning 1292 Truncated incorrect INTEGER value: '111111 '

2006

Warning 1264 Out of range value for column 'a' at row 1

2007

Warning 1292 Truncated incorrect INTEGER value: '222222 '

2008

Warning 1264 Out of range value for column 'b' at row 1

2009

Error 1048 Column 'c' cannot be null

2010

2011

DROP TABLE t1;

2012

DROP TABLE t2;

2013

DROP PROCEDURE p1;

2014

2015

###################################################################

2016

# Tests for the following bugs:

2017

# - Bug#11763171: 55852 - Possibly inappropriate handler activation.

2018

# - Bug#11749343: 38806 - Wrong scope for SQL HANDLERS in SP.

2019

###################################################################

2020

2021

2022

# -- Check that SQL-conditions thrown by Statement-blocks are

2023

# -- handled by Handler-decl blocks properly.

2024

2025

CREATE PROCEDURE p1()

2026

BEGIN

2027

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2028

SELECT 'H1' AS HandlerId;

2029

DECLARE CONTINUE HANDLER FOR SQLWARNING

2030

SELECT 'H2' AS HandlerId;

2031

SIGNAL SQLSTATE '01000'; # Should be handled by H2.

2032

END|

2033

2034

CALL p1()|

2035

HandlerId

2036

2037

2038

# -- Check that SQL-conditions thrown by Statement-blocks are

2039

# -- handled by Handler-decl blocks properly in case of nested

2040

# -- SQL-blocks.

2041

2042

CREATE PROCEDURE p2()

2043

BEGIN

2044

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2045

SELECT 'H1' AS HandlerId;

2046

DECLARE CONTINUE HANDLER FOR SQLWARNING

2047

SELECT 'H2' AS HandlerId;

2048

BEGIN

2049

SELECT 'B1' AS BlockId;

2050

BEGIN

2051

SELECT 'B2' AS BlockId;

2052

BEGIN

2053

SELECT 'B3' AS BlockId;

2054

SIGNAL SQLSTATE '01000'; # Should be handled by H2.

2055

END;

2056

END;

2057

END;

2058

END|

2059

2060

CALL p2()|

2061

BlockId

2062

2063

BlockId

2064

2065

BlockId

2066

2067

HandlerId

2068

2069

2070

# -- Check SQL-handler resolution rules.

2071

2072

CREATE PROCEDURE p3()

2073

BEGIN

2074

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2075

SELECT 'H1' AS HandlerId;

2076

DECLARE CONTINUE HANDLER FOR SQLWARNING

2077

SELECT 'H2' AS HandlerId;

2078

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2079

SELECT 'H3' AS HandlerId;

2080

SIGNAL SQLSTATE '01000'; # Should be handled by H3.

2081

END|

2082

2083

CALL p3()|

2084

HandlerId

2085

2086

2087

CREATE PROCEDURE p4()

2088

BEGIN

2089

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2090

SELECT 'H1' AS HandlerId;

2091

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2092

SELECT 'H2' AS HandlerId;

2093

DECLARE CONTINUE HANDLER FOR SQLWARNING

2094

SELECT 'H3' AS HandlerId;

2095

SIGNAL SQLSTATE '01000'; # Should be handled by H2.

2096

END|

2097

2098

CALL p4()|

2099

HandlerId

2100

2101

2102

CREATE PROCEDURE p5()

2103

BEGIN

2104

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2105

SELECT 'H1' AS HandlerId;

2106

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2107

SELECT 'H2' AS HandlerId;

2108

BEGIN

2109

DECLARE CONTINUE HANDLER FOR SQLWARNING

2110

SELECT 'H3' AS HandlerId;

2111

SIGNAL SQLSTATE '01000'; # Should be handled by H3.

2112

END;

2113

END|

2114

2115

CALL p5()|

2116

HandlerId

2117

2118

2119

# -- Check that handlers don't handle its own exceptions.

2120

2121

CREATE PROCEDURE p6()

2122

BEGIN

2123

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2124

BEGIN

2125

SELECT 'H1' AS HandlerId;

2126

SIGNAL SQLSTATE 'HY000'; # Should *not* be handled by H1.

2127

END;

2128

SELECT 'S1' AS SignalId;

2129

SIGNAL SQLSTATE 'HY000'; # Should be handled by H1.

2130

END|

2131

2132

CALL p6()|

2133

SignalId

2134

2135

HandlerId

2136

2137

ERROR HY000: Unhandled user-defined exception condition

2138

2139

# -- Check that handlers don't handle its own warnings.

2140

2141

CREATE PROCEDURE p7()

2142

BEGIN

2143

DECLARE CONTINUE HANDLER FOR SQLWARNING

2144

BEGIN

2145

SELECT 'H1' AS HandlerId;

2146

SIGNAL SQLSTATE '01000'; # Should *not* be handled by H1.

2147

END;

2148

SELECT 'S1' AS SignalId;

2149

SIGNAL SQLSTATE '01000'; # Should be handled by H1.

2150

END|

2151

2152

CALL p7()|

2153

SignalId

2154

2155

HandlerId

2156

2157

Warnings:

2158

Warning 1642 Unhandled user-defined warning condition

2159

2160

# -- Check that conditions for handlers are not handled by the handlers

2161

# -- from the same block.

2162

2163

CREATE PROCEDURE p8()

2164

BEGIN

2165

DECLARE CONTINUE HANDLER FOR SQLWARNING

2166

SELECT 'H1' AS HandlerId;

2167

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2168

BEGIN

2169

SELECT 'H2' AS HandlerId;

2170

SIGNAL SQLSTATE '01000'; # Should *not* be handled by H1.

2171

END;

2172

SELECT 'S1' AS SignalId;

2173

SIGNAL SQLSTATE 'HY000'; # Should be handled by H2.

2174

END|

2175

2176

CALL p8()|

2177

SignalId

2178

2179

HandlerId

2180

2181

Warnings:

2182

Warning 1642 Unhandled user-defined warning condition

2183

2184

# -- Check that conditions for handlers are not handled by the handlers

2185

# -- from the same block even if they are thrown deep down the stack.

2186

2187

CREATE PROCEDURE p9()

2188

BEGIN

2189

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2190

SELECT 'Wrong:H1:1' AS HandlerId;

2191

DECLARE CONTINUE HANDLER FOR SQLWARNING

2192

SELECT 'Wrong:H1:2' AS HandlerId;

2193

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2194

BEGIN

2195

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2196

SELECT 'Wrong:H2:1' AS HandlerId;

2197

DECLARE CONTINUE HANDLER FOR SQLWARNING

2198

SELECT 'Wrong:H2:2' AS HandlerId;

2199

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2200

BEGIN

2201

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2202

SELECT 'Wrong:H3:1' AS HandlerId;

2203

DECLARE CONTINUE HANDLER FOR SQLWARNING

2204

SELECT 'Wrong:H3:2' AS HandlerId;

2205

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2206

BEGIN

2207

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2208

SELECT 'Wrong:H4:1' AS HandlerId;

2209

DECLARE CONTINUE HANDLER FOR SQLWARNING

2210

SELECT 'Wrong:H4:2' AS HandlerId;

2211

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2212

BEGIN

2213

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2214

SELECT 'Wrong:H5:1' AS HandlerId;

2215

DECLARE CONTINUE HANDLER FOR SQLWARNING

2216

SELECT 'Wrong:H5:2' AS HandlerId;

2217

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2218

BEGIN

2219

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2220

SELECT 'Wrong:H6:1' AS HandlerId;

2221

DECLARE CONTINUE HANDLER FOR SQLWARNING

2222

SELECT 'Wrong:H6:2' AS HandlerId;

2223

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2224

BEGIN

2225

SELECT 'H2' AS HandlerId;

2226

SIGNAL SQLSTATE '01000'; # Should *not* be handled by H1.

2227

END;

2228

SELECT 'S6' AS SignalId;

2229

SIGNAL SQLSTATE 'HY000';

2230

END;

2231

SELECT 'S5' AS SignalId;

2232

SIGNAL SQLSTATE 'HY000';

2233

END;

2234

SELECT 'S4' AS SignalId;

2235

SIGNAL SQLSTATE 'HY000';

2236

END;

2237

SELECT 'S3' AS SignalId;

2238

SIGNAL SQLSTATE 'HY000';

2239

END;

2240

SELECT 'S2' AS SignalId;

2241

SIGNAL SQLSTATE 'HY000';

2242

END;

2243

SELECT 'S1' AS SignalId;

2244

SIGNAL SQLSTATE 'HY000'; # Should be handled by H2.

2245

END|

2246

2247

CALL p9()|

2248

SignalId

2249

2250

SignalId

2251

2252

SignalId

2253

2254

SignalId

2255

2256

SignalId

2257

2258

SignalId

2259

2260

HandlerId

2261

2262

Warnings:

2263

Warning 1642 Unhandled user-defined warning condition

2264

2265

# -- Check that handlers are choosen properly in case of deep stack and

2266

# -- nested SQL-blocks.

2267

2268

CREATE PROCEDURE p10()

2269

BEGIN

2270

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2271

SELECT 'H1' AS HandlerId;

2272

DECLARE CONTINUE HANDLER FOR SQLWARNING

2273

SELECT 'H2' AS HandlerId;

2274

BEGIN

2275

BEGIN

2276

BEGIN

2277

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2278

SELECT 'Wrong:H1:1' AS HandlerId;

2279

DECLARE CONTINUE HANDLER FOR SQLWARNING

2280

SELECT 'Wrong:H1:2' AS HandlerId;

2281

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2282

BEGIN

2283

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2284

SELECT 'Wrong:H2:1' AS HandlerId;

2285

DECLARE CONTINUE HANDLER FOR SQLWARNING

2286

SELECT 'Wrong:H2:2' AS HandlerId;

2287

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2288

BEGIN

2289

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2290

SELECT 'Wrong:H3:1' AS HandlerId;

2291

DECLARE CONTINUE HANDLER FOR SQLWARNING

2292

SELECT 'Wrong:H3:2' AS HandlerId;

2293

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2294

BEGIN

2295

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2296

SELECT 'Wrong:H4:1' AS HandlerId;

2297

DECLARE CONTINUE HANDLER FOR SQLWARNING

2298

SELECT 'Wrong:H4:2' AS HandlerId;

2299

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2300

BEGIN

2301

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2302

SELECT 'Wrong:H5:1' AS HandlerId;

2303

DECLARE CONTINUE HANDLER FOR SQLWARNING

2304

SELECT 'Wrong:H5:2' AS HandlerId;

2305

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2306

BEGIN

2307

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000'

2308

SELECT 'Wrong:H6:1' AS HandlerId;

2309

DECLARE CONTINUE HANDLER FOR SQLWARNING

2310

SELECT 'Wrong:H6:2' AS HandlerId;

2311

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2312

BEGIN

2313

SELECT 'H2' AS HandlerId;

2314

SIGNAL SQLSTATE '01000'; # Should be handled by H1.

2315

END;

2316

SELECT 'S6' AS SignalId;

2317

SIGNAL SQLSTATE 'HY000';

2318

END;

2319

SELECT 'S5' AS SignalId;

2320

SIGNAL SQLSTATE 'HY000';

2321

END;

2322

SELECT 'S4' AS SignalId;

2323

SIGNAL SQLSTATE 'HY000';

2324

END;

2325

SELECT 'S3' AS SignalId;

2326

SIGNAL SQLSTATE 'HY000';

2327

END;

2328

SELECT 'S2' AS SignalId;

2329

SIGNAL SQLSTATE 'HY000';

2330

END;

2331

SELECT 'S1' AS SignalId;

2332

SIGNAL SQLSTATE 'HY000'; # Should be handled by H2.

2333

END;

2334

END;

2335

END;

2336

END|

2337

2338

CALL p10()|

2339

SignalId

2340

2341

SignalId

2342

2343

SignalId

2344

2345

SignalId

2346

2347

SignalId

2348

2349

SignalId

2350

2351

HandlerId

2352

2353

HandlerId

2354

2355

2356

# -- Test stored procedure from Peter's mail.

2357

2358

CREATE PROCEDURE p11()

2359

BEGIN

2360

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2361

SELECT 'H1' AS HandlerId;

2362

DECLARE CONTINUE HANDLER FOR SQLWARNING

2363

SELECT 'H2' AS HandlerId;

2364

BEGIN

2365

DECLARE CONTINUE HANDLER FOR SQLSTATE '01000', 1249

2366

BEGIN

2367

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

2368

SELECT 'H3' AS HandlerId;

2369

DECLARE CONTINUE HANDLER FOR SQLWARNING

2370

SELECT 'H4' AS HandlerId;

2371

BEGIN

2372

SELECT 'H5' AS HandlerId;

2373

SELECT 'S3' AS SignalId;

2374

SIGNAL SQLSTATE 'HY000'; # H3

2375

SELECT 'S4' AS SignalId;

2376

SIGNAL SQLSTATE '22003'; # H3

2377

SELECT 'S5' AS SignalId;

2378

SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1249; # H4

2379

END;

2380

END;

2381

SELECT 'S6' AS SignalId;

2382

SIGNAL SQLSTATE 'HY000'; # H1

2383

SELECT 'S7' AS SignalId;

2384

SIGNAL SQLSTATE '22003'; # H1

2385

SELECT 'S8' AS SignalId;

2386

SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1249; # H5

2387

END;

2388

SELECT 'S1' AS SignalId;

2389

SIGNAL SQLSTATE 'HY000'; # H1

2390

SELECT 'S2' AS SignalId;

2391

SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1249; # H2

2392

END|

2393

2394

CALL p11()|

2395

SignalId

2396

2397

HandlerId

2398

2399

SignalId

2400

2401

HandlerId

2402

2403

SignalId

2404

2405

HandlerId

2406

2407

SignalId

2408

2409

HandlerId

2410

2411

SignalId

2412

2413

HandlerId

2414

2415

SignalId

2416

2417

HandlerId

2418

2419

SignalId

2420

2421

HandlerId

2422

2423

SignalId

2424

2425

HandlerId

2426

2427

2428

# -- Check that runtime stack-trace can be deeper than parsing-time one.

2429

2430

CREATE PROCEDURE p12()

2431

BEGIN

2432

DECLARE CONTINUE HANDLER FOR SQLSTATE '01001'

2433

BEGIN

2434

DECLARE CONTINUE HANDLER FOR SQLSTATE '01001'

2435

BEGIN

2436

DECLARE CONTINUE HANDLER FOR SQLSTATE '01001'

2437

BEGIN

2438

DECLARE CONTINUE HANDLER FOR SQLSTATE '01001'

2439

BEGIN

2440

DECLARE CONTINUE HANDLER FOR SQLSTATE '01001'

2441

BEGIN

2442

SELECT 'H1:5' AS HandlerId;

2443

SIGNAL SQLSTATE '01002';

2444

END;

2445

SELECT 'H1:4' AS HandlerId;

2446

SIGNAL SQLSTATE '01001';

2447

END;

2448

SELECT 'H1:3' AS HandlerId;

2449

SIGNAL SQLSTATE '01001';

2450

END;

2451

SELECT 'H1:2' AS HandlerId;

2452

SIGNAL SQLSTATE '01001';

2453

END;

2454

SELECT 'H1:1' AS HandlerId;

2455

SIGNAL SQLSTATE '01001';

2456

END;

2457

#########################################################

2458

DECLARE CONTINUE HANDLER FOR SQLSTATE '01002'

2459

SELECT 'OK' AS Msg;

2460

#########################################################

2461

BEGIN

2462

DECLARE CONTINUE HANDLER FOR SQLWARNING

2463

BEGIN

2464

DECLARE CONTINUE HANDLER FOR SQLWARNING

2465

BEGIN

2466

DECLARE CONTINUE HANDLER FOR SQLWARNING

2467

BEGIN

2468

DECLARE CONTINUE HANDLER FOR SQLWARNING

2469

BEGIN

2470

DECLARE CONTINUE HANDLER FOR SQLWARNING

2471

BEGIN

2472

SELECT 'H2:5' AS HandlerId;

2473

SIGNAL SQLSTATE '01001';

2474

END;

2475

SELECT 'H2:4' AS HandlerId;

2476

SIGNAL SQLSTATE '01000';

2477

END;

2478

SELECT 'H2:3' AS HandlerId;

2479

SIGNAL SQLSTATE '01000';

2480

END;

2481

SELECT 'H2:2' AS HandlerId;

2482

SIGNAL SQLSTATE '01000';

2483

END;

2484

SELECT 'H2:1' AS HandlerId;

2485

SIGNAL SQLSTATE '01000';

2486

END;

2487

#######################################################

2488

SELECT 'Throw 01000' AS Msg;

2489

SIGNAL SQLSTATE '01000';

2490

END;

2491

END|

2492

2493

CALL p12()|

2494

Msg

2495

Throw 01000

2496

HandlerId

2497

H2:1

2498

HandlerId

2499

H2:2

2500

HandlerId

2501

H2:3

2502

HandlerId

2503

H2:4

2504

HandlerId

2505

H2:5

2506

HandlerId

2507

H1:1

2508

HandlerId

2509

H1:2

2510

HandlerId

2511

H1:3

2512

HandlerId

2513

H1:4

2514

HandlerId

2515

H1:5

2516

Warnings:

2517

Warning 1642 Unhandled user-defined warning condition

2518

2519

# -- Check that handler-call-frames are removed properly for EXIT

2520

# -- handlers.

2521

2522

CREATE PROCEDURE p13()

2523

BEGIN

2524

DECLARE CONTINUE HANDLER FOR SQLWARNING

2525

BEGIN

2526

DECLARE CONTINUE HANDLER FOR SQLWARNING

2527

BEGIN

2528

DECLARE EXIT HANDLER FOR SQLWARNING

2529

BEGIN

2530

SELECT 'EXIT handler 3' AS Msg;

2531

END;

2532

SELECT 'CONTINUE handler 2: 1' AS Msg;

2533

SIGNAL SQLSTATE '01000';

2534

SELECT 'CONTINUE handler 2: 2' AS Msg;

2535

END;

2536

SELECT 'CONTINUE handler 1: 1' AS Msg;

2537

SIGNAL SQLSTATE '01000';

2538

SELECT 'CONTINUE handler 1: 2' AS Msg;

2539

END;

2540

SELECT 'Throw 01000' AS Msg;

2541

SIGNAL SQLSTATE '01000';

2542

END|

2543

2544

CALL p13()|

2545

Msg

2546

Throw 01000

2547

Msg

2548

CONTINUE handler 1: 1

2549

Msg

2550

CONTINUE handler 2: 1

2551

Msg

2552

EXIT handler 3

2553

Msg

2554

CONTINUE handler 1: 2

2555

2556

# That's it. Cleanup.

2557

2558

DROP PROCEDURE p1;

2559

DROP PROCEDURE p2;

2560

DROP PROCEDURE p3;

2561

DROP PROCEDURE p4;

2562

DROP PROCEDURE p5;

2563

DROP PROCEDURE p6;

2564

DROP PROCEDURE p7;

2565

DROP PROCEDURE p8;

2566

DROP PROCEDURE p9;

2567

DROP PROCEDURE p10;

2568

DROP PROCEDURE p11;

2569

DROP PROCEDURE p12;

2570

DROP PROCEDURE p13;

2571

2572

# Bug#12731619: NESTED SP HANDLERS CAN TRIGGER ASSERTION

2573

2574

DROP FUNCTION IF EXISTS f1;

2575

DROP TABLE IF EXISTS t1;

2576

CREATE TABLE t1(msg VARCHAR(255));

2577

CREATE FUNCTION f1() RETURNS INT

2578

BEGIN

2579

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION # handler 1

2580

BEGIN

2581

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION # handler 2

2582

BEGIN

2583

INSERT INTO t1 VALUE('WRONG: Inside H2');

2584

RETURN 2;

2585

END;

2586

INSERT INTO t1 VALUE('CORRECT: Inside H1');

2587

RETURN 1;

2588

END;

2589

BEGIN

2590

DECLARE CONTINUE HANDLER FOR SQLWARNING # handler 3

2591

BEGIN

2592

INSERT INTO t1 VALUE('WRONG: Inside H3');

2593

RETURN 3;

2594

END;

2595

INSERT INTO t1 VALUE('CORRECT: Calling f1()');

2596

RETURN f1(); # -- exception here

2597

END;

2598

INSERT INTO t1 VALUE('WRONG: Returning 10');

2599

RETURN 10;

2600

END|

2601

2602

SELECT f1();

2603

f1()

2604

2605

2606

SELECT * FROM t1;

2607

msg

2608

CORRECT: Calling f1()

2609

CORRECT: Inside H1

2610

2611

DROP FUNCTION f1;

2612

DROP TABLE t1;

2613

2614

# Check that handled SQL-conditions are properly cleared from DA.

2615

2616

DROP TABLE IF EXISTS t1;

2617

DROP TABLE IF EXISTS t2;

2618

DROP PROCEDURE IF EXISTS p1;

2619

DROP PROCEDURE IF EXISTS p2;

2620

DROP PROCEDURE IF EXISTS p3;

2621

DROP PROCEDURE IF EXISTS p4;

2622

DROP PROCEDURE IF EXISTS p5;

2623

CREATE TABLE t1(a CHAR, b CHAR, c CHAR);

2624

CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT);

2625

2626

# Check that SQL-conditions for which SQL-handler has been invoked,

2627

# are cleared from the Diagnostics Area. Note, there might be several

2628

# SQL-conditions, but SQL-handler must be invoked only once.

2629

2630

CREATE PROCEDURE p1()

2631

BEGIN

2632

DECLARE EXIT HANDLER FOR SQLWARNING

2633

SELECT 'Warning caught' AS msg;

2634

# The INSERT below raises 3 SQL-conditions (warnings). The EXIT HANDLER

2635

# above must be invoked once (for one condition), but all three conditions

2636

# must be cleared from the Diagnostics Area.

2637

INSERT INTO t1 VALUES('qqqq', 'ww', 'eee');

2638

# The following INSERT will not be executed, because of the EXIT HANDLER.

2639

INSERT INTO t1 VALUES('zzz', 'xx', 'yyyy');

2640

END|

2641

2642

CALL p1()|

2643

msg

2644

Warning caught

2645

2646

SELECT * FROM t1|

2647

a b c

2648

q w e

2649

2650

# Check that SQL-conditions for which SQL-handler has *not* been

2651

# invoked, are *still* cleared from the Diagnostics Area.

2652

2653

CREATE PROCEDURE p2()

2654

BEGIN

2655

DECLARE CONTINUE HANDLER FOR 1292

2656

SELECT 'Warning 1292 caught' AS msg;

2657

# The following INSERT raises 6 SQL-warnings with code 1292,

2658

# and 3 SQL-warnings with code 1264. The CONTINUE HANDLER above must be

2659

# invoked once, and all nine SQL-warnings must be cleared from

2660

# the Diagnostics Area.

2661

INSERT INTO t2

2662

SELECT

2663

CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER),

2664

CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER),

2665

CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER);

2666

END|

2667

2668

CALL p2()|

2669

msg

2670

Warning 1292 caught

2671

2672

# Check that if there are two equally ranked SQL-handlers to handle

2673

# SQL-conditions from SQL-statement, only one of them will be invoked.

2674

2675

CREATE PROCEDURE p3()

2676

BEGIN

2677

DECLARE CONTINUE HANDLER FOR 1292

2678

SELECT 'Warning 1292 caught' AS msg;

2679

DECLARE CONTINUE HANDLER FOR 1264

2680

SELECT 'Warning 1264 caught' AS msg;

2681

# The following INSERT raises 6 SQL-warnings with code 1292,

2682

# and 3 SQL-warnings with code 1264. Only one of the CONTINUE HANDLERs above

2683

# must be called, and only once. The SQL Standard does not define, which one

2684

# should be invoked.

2685

INSERT INTO t2

2686

SELECT

2687

CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER),

2688

CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER),

2689

CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER);

2690

END|

2691

2692

CALL p3()|

2693

msg

2694

Warning 1264 caught

2695

2696

# The same as p3, but 1264 comes first.

2697

2698

CREATE PROCEDURE p4()

2699

BEGIN

2700

DECLARE CONTINUE HANDLER FOR 1292

2701

SELECT 'Warning 1292 caught' AS msg;

2702

DECLARE CONTINUE HANDLER FOR 1264

2703

SELECT 'Warning 1264 caught' AS msg;

2704

# The following INSERT raises 4 SQL-warnings with code 1292,

2705

# and 3 SQL-warnings with code 1264. Only one of the CONTINUE HANDLERs above

2706

# must be called, and only once. The SQL Standard does not define, which one

2707

# should be invoked.

2708

INSERT INTO t2

2709

SELECT

2710

CAST(999999 AS SIGNED INTEGER),

2711

CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER),

2712

CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER);

2713

END|

2714

2715

CALL p4()|

2716

msg

2717

Warning 1264 caught

2718

2719

# Check that if a SQL-handler raised its own SQL-conditions, there are

2720

# preserved after handler exit.

2721

2722

CREATE PROCEDURE p5()

2723

BEGIN

2724

DECLARE EXIT HANDLER FOR 1292

2725

BEGIN

2726

SELECT 'Handler for 1292 (1)' AS Msg;

2727

SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1234;

2728

SHOW WARNINGS;

2729

SELECT 'Handler for 1292 (2)' AS Msg;

2730

END;

2731

INSERT INTO t2

2732

SELECT

2733

CAST(999999 AS SIGNED INTEGER),

2734

CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER),

2735

CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER);

2736

END|

2737

2738

CALL p5()|

2739

Msg

2740

Handler for 1292 (1)

2741

Level Code Message

2742

Warning 1234 Unhandled user-defined warning condition

2743

Msg

2744

Handler for 1292 (2)

2745

Warnings:

2746

Warning 1234 Unhandled user-defined warning condition

2747

2748

# Check that SQL-conditions are available inside the handler, but

2749

# cleared after the handler exits.

2750

2751

CREATE PROCEDURE p6()

2752

BEGIN

2753

DECLARE CONTINUE HANDLER FOR 1292

2754

BEGIN

2755

SHOW WARNINGS;

2756

SELECT 'Handler for 1292' Msg;

2757

END;

2758

INSERT INTO t2

2759

SELECT

2760

CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER),

2761

CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER),

2762

CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER);

2763

END|

2764

2765

CALL p6()|

2766

Level Code Message

2767

Warning 1292 Truncated incorrect INTEGER value: '1 '

2768

Warning 1292 Truncated incorrect INTEGER value: '1999999 '

2769

Warning 1264 Out of range value for column 'a' at row 1

2770

Warning 1292 Truncated incorrect INTEGER value: '2 '

2771

Warning 1292 Truncated incorrect INTEGER value: '2999999 '

2772

Warning 1264 Out of range value for column 'b' at row 1

2773

Warning 1292 Truncated incorrect INTEGER value: '3 '

2774

Warning 1292 Truncated incorrect INTEGER value: '3999999 '

2775

Warning 1264 Out of range value for column 'c' at row 1

2776

Msg

2777

Handler for 1292

2778

2779

DROP PROCEDURE p1;

2780

DROP PROCEDURE p2;

2781

DROP PROCEDURE p3;

2782

DROP PROCEDURE p4;

2783

DROP PROCEDURE p5;

2784

DROP PROCEDURE p6;

2785

DROP TABLE t1;

2786

DROP TABLE t2;

2787

2788

# Bug#13059316: ASSERTION FAILURE IN SP_RCONTEXT.CC

2789

# Check DECLARE statements that raise conditions before handlers

2790

# are declared.

2791

2792

DROP PROCEDURE IF EXISTS p1;

2793

DROP PROCEDURE IF EXISTS p2;

2794

CREATE PROCEDURE p1()

2795

BEGIN

2796

DECLARE var1 INTEGER DEFAULT 'string';

2797

DECLARE EXIT HANDLER FOR SQLWARNING SELECT 'H1';

2798

END|

2799

2800

CALL p1()|

2801

Warnings:

2802

Warning 1366 Incorrect integer value: 'string' for column 'var1' at row 1

2803

2804

CREATE PROCEDURE p2()

2805

BEGIN

2806

DECLARE EXIT HANDLER FOR SQLWARNING SELECT 'H2';

2807

CALL p1();

2808

END|

2809

2810

CALL p2()|

2811

2812

2813

2814

DROP PROCEDURE p1;

2815

DROP PROCEDURE p2;

2816

2817

# Bug#13113222 RQG_SIGNAL_RESIGNAL FAILED WITH ASSERTION.

2818

2819

DROP PROCEDURE IF EXISTS p1;

2820

DROP PROCEDURE IF EXISTS p2;

2821

CREATE PROCEDURE p1()

2822

BEGIN

2823

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'triggered p1';

2824

# This will trigger an error.

2825

SIGNAL SQLSTATE 'HY000';

2826

END|

2827

CREATE PROCEDURE p2()

2828

BEGIN

2829

DECLARE CONTINUE HANDLER FOR SQLWARNING SELECT 'triggered p2';

2830

# This will trigger a warning.

2831

SIGNAL SQLSTATE '01000';

2832

END|

2833

SET @old_max_error_count= @@session.max_error_count;

2834

SET SESSION max_error_count= 0;

2835

CALL p1();

2836

triggered p1

2837

triggered p1

2838

CALL p2();

2839

SET SESSION max_error_count= @old_max_error_count;

2840

DROP PROCEDURE p1;

2841

DROP PROCEDURE p2;

2842

2843

# Bug#12652873: 61392: Continue handler for NOT FOUND being triggered

2844

# from internal stored function.

2845

2846

DROP FUNCTION IF EXISTS f1;

2847

DROP FUNCTION IF EXISTS f2;

2848

DROP TABLE IF EXISTS t1;

2849

2850

CREATE TABLE t1 (a INT, b INT);

2851

INSERT INTO t1 VALUES (1, 2);

2852

2853

# f1() raises NOT_FOUND condition.

2854

# Raising NOT_FOUND can not be simulated by SIGNAL,

2855

# because SIGNAL would raise SQL-error in that case.

2856

2857

CREATE FUNCTION f1() RETURNS INTEGER

2858

BEGIN

2859

DECLARE v VARCHAR(5) DEFAULT -1;

2860

SELECT b FROM t1 WHERE a = 2 INTO v;

2861

RETURN v;

2862

END|

2863

2864

# Here we check that the NOT_FOUND condition raised in f1()

2865

# is not visible in the outer function (f2), i.e. the continue

2866

# handler in f2() will not be called.

2867

2868

CREATE FUNCTION f2() RETURNS INTEGER

2869

BEGIN

2870

DECLARE v INTEGER;

2871

DECLARE CONTINUE HANDLER FOR NOT FOUND

2872

SET @msg = 'Handler activated.';

2873

SELECT f1() INTO v;

2874

RETURN v;

2875

END|

2876

SET @msg = '';

2877

2878

SELECT f2();

2879

f2()

2880

-1

2881

2882

SELECT @msg;

2883

@msg

2884

2885

2886

DROP FUNCTION f1;

2887

DROP FUNCTION f2;

2888

DROP TABLE t1;

Older »