~vadim-tk/percona-server/percona-5.5.15-galera

1 1 1 1 1 1 1 1 1 1 1.0000 1.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 1 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext one monday

9 9 9 9 9 9 9 9 9 9 9.0000 9.0000 2004-02-29 2004-02-29 11:11:11 2004-02-29 11:11:11 11:11:11 2004 1 0 a 123456789a 123456789a123456789b123456789c tinyblob tinytext blob text mediumblob mediumtext longblob longtext two tuesday

set @arg00='SELECT' ;

@arg00 a from t1 where a=1;

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 '@arg00 a from t1 where a=1' at line 1

prepare stmt1 from ' ? a from t1 where a=1 ';

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 '? a from t1 where a=1' at line 1

set @arg00=1 ;

select @arg00, b from t1 where a=1 ;

@arg00 b

1 one

prepare stmt1 from ' select ?, b from t1 where a=1 ' ;

execute stmt1 using @arg00 ;

? b

100

1 one

101

set @arg00='lion' ;

102

select @arg00, b from t1 where a=1 ;

103

@arg00 b

104

lion one

105

prepare stmt1 from ' select ?, b from t1 where a=1 ' ;

106

execute stmt1 using @arg00 ;

107

? b

108

lion one

109

set @arg00=NULL ;

110

select @arg00, b from t1 where a=1 ;

111

@arg00 b

112

NULL one

113

prepare stmt1 from ' select ?, b from t1 where a=1 ' ;

114

execute stmt1 using @arg00 ;

115

? b

116

NULL one

117

set @arg00=1 ;

118

select b, a - @arg00 from t1 where a=1 ;

119

b a - @arg00

120

one 0

121

prepare stmt1 from ' select b, a - ? from t1 where a=1 ' ;

122

execute stmt1 using @arg00 ;

123

b a - ?

124

one 0

125

set @arg00=null ;

126

select @arg00 as my_col ;

127

my_col

128

NULL

129

prepare stmt1 from ' select ? as my_col';

130

execute stmt1 using @arg00 ;

131

my_col

132

NULL

133

select @arg00 + 1 as my_col ;

134

my_col

135

NULL

136

prepare stmt1 from ' select ? + 1 as my_col';

137

execute stmt1 using @arg00 ;

138

my_col

139

NULL

140

select 1 + @arg00 as my_col ;

141

my_col

142

NULL

143

prepare stmt1 from ' select 1 + ? as my_col';

144

execute stmt1 using @arg00 ;

145

my_col

146

NULL

147

set @arg00='MySQL' ;

148

select substr(@arg00,1,2) from t1 where a=1 ;

149

substr(@arg00,1,2)

150

151

prepare stmt1 from ' select substr(?,1,2) from t1 where a=1 ' ;

152

execute stmt1 using @arg00 ;

153

substr(?,1,2)

154

155

set @arg00=3 ;

156

select substr('MySQL',@arg00,5) from t1 where a=1 ;

157

substr('MySQL',@arg00,5)

158

SQL

159

prepare stmt1 from ' select substr(''MySQL'',?,5) from t1 where a=1 ' ;

160

execute stmt1 using @arg00 ;

161

substr('MySQL',?,5)

162

SQL

163

select substr('MySQL',1,@arg00) from t1 where a=1 ;

164

substr('MySQL',1,@arg00)

165

MyS

166

prepare stmt1 from ' select substr(''MySQL'',1,?) from t1 where a=1 ' ;

167

execute stmt1 using @arg00 ;

168

substr('MySQL',1,?)

169

MyS

170

set @arg00='MySQL' ;

171

select a , concat(@arg00,b) from t1 order by a;

172

a concat(@arg00,b)

173

1 MySQLone

174

2 MySQLtwo

175

3 MySQLthree

176

4 MySQLfour

177

prepare stmt1 from ' select a , concat(?,b) from t1 order by a ' ;

178

execute stmt1 using @arg00;

179

a concat(?,b)

180

1 MySQLone

181

2 MySQLtwo

182

3 MySQLthree

183

4 MySQLfour

184

select a , concat(b,@arg00) from t1 order by a ;

185

a concat(b,@arg00)

186

1 oneMySQL

187

2 twoMySQL

188

3 threeMySQL

189

4 fourMySQL

190

prepare stmt1 from ' select a , concat(b,?) from t1 order by a ' ;

191

execute stmt1 using @arg00;

192

a concat(b,?)

193

1 oneMySQL

194

2 twoMySQL

195

3 threeMySQL

196

4 fourMySQL

197

set @arg00='MySQL' ;

198

select group_concat(@arg00,b order by a) from t1

199

group by 'a' ;

200

group_concat(@arg00,b order by a)

201

MySQLone,MySQLtwo,MySQLthree,MySQLfour

202

prepare stmt1 from ' select group_concat(?,b order by a) from t1

203

group by ''a'' ' ;

204

execute stmt1 using @arg00;

205

group_concat(?,b order by a)

206

MySQLone,MySQLtwo,MySQLthree,MySQLfour

207

select group_concat(b,@arg00 order by a) from t1

208

group by 'a' ;

209

group_concat(b,@arg00 order by a)

210

oneMySQL,twoMySQL,threeMySQL,fourMySQL

211

prepare stmt1 from ' select group_concat(b,? order by a) from t1

212

group by ''a'' ' ;

213

execute stmt1 using @arg00;

214

group_concat(b,? order by a)

215

oneMySQL,twoMySQL,threeMySQL,fourMySQL

216

set @arg00='first' ;

217

set @arg01='second' ;

218

set @arg02=NULL;

219

select @arg00, @arg01 from t1 where a=1 ;

220

@arg00 @arg01

221

first second

222

prepare stmt1 from ' select ?, ? from t1 where a=1 ' ;

223

execute stmt1 using @arg00, @arg01 ;

224

? ?

225

first second

226

execute stmt1 using @arg02, @arg01 ;

227

? ?

228

NULL second

229

execute stmt1 using @arg00, @arg02 ;

230

? ?

231

first NULL

232

execute stmt1 using @arg02, @arg02 ;

233

? ?

234

NULL NULL

235

drop table if exists t5 ;

236

create table t5 (id1 int(11) not null default '0',

237

value2 varchar(100), value1 varchar(100)) ;

238

insert into t5 values (1,'hh','hh'),(2,'hh','hh'),

239

(1,'ii','ii'),(2,'ii','ii') ;

240

prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? order by id1,value1 ' ;

241

set @arg00=1 ;

242

set @arg01='hh' ;

243

execute stmt1 using @arg00, @arg01 ;

244

id1 value1

245

1 hh

246

1 ii

247

2 hh

248

drop table t5 ;

249

drop table if exists t5 ;

250

create table t5(session_id char(9) not null) ;

251

insert into t5 values ('abc') ;

252

prepare stmt1 from ' select * from t5

253

where ?=''1111'' and session_id = ''abc'' ' ;

254

set @arg00='abc' ;

255

execute stmt1 using @arg00 ;

256

session_id

257

set @arg00='1111' ;

258

execute stmt1 using @arg00 ;

259

session_id

260

abc

261

set @arg00='abc' ;

262

execute stmt1 using @arg00 ;

263

session_id

264

drop table t5 ;

265

set @arg00='FROM' ;

266

select a @arg00 t1 where a=1 ;

267

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 '@arg00 t1 where a=1' at line 1

268

prepare stmt1 from ' select a ? t1 where a=1 ' ;

269

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 '? t1 where a=1' at line 1

270

set @arg00='t1' ;

271

select a from @arg00 where a=1 ;

272

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 '@arg00 where a=1' at line 1

273

prepare stmt1 from ' select a from ? where a=1 ' ;

274

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 '? where a=1' at line 1

275

set @arg00='WHERE' ;

276

select a from t1 @arg00 a=1 ;

277

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 '@arg00 a=1' at line 1

278

prepare stmt1 from ' select a from t1 ? a=1 ' ;

279

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

280

set @arg00=1 ;

281

select a FROM t1 where a=@arg00 ;

282

283

284

prepare stmt1 from ' select a FROM t1 where a=? ' ;

285

execute stmt1 using @arg00 ;

286

287

288

set @arg00=1000 ;

289

execute stmt1 using @arg00 ;

290

291

set @arg00=NULL ;

292

select a FROM t1 where a=@arg00 ;

293

294

prepare stmt1 from ' select a FROM t1 where a=? ' ;

295

execute stmt1 using @arg00 ;

296

297

set @arg00=4 ;

298

select a FROM t1 where a=sqrt(@arg00) ;

299

300

301

prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ;

302

execute stmt1 using @arg00 ;

303

304

305

set @arg00=NULL ;

306

select a FROM t1 where a=sqrt(@arg00) ;

307

308

prepare stmt1 from ' select a FROM t1 where a=sqrt(?) ' ;

309

execute stmt1 using @arg00 ;

310

311

set @arg00=2 ;

312

set @arg01=3 ;

313

select a FROM t1 where a in (@arg00,@arg01) order by a;

314

315

316

317

prepare stmt1 from ' select a FROM t1 where a in (?,?) order by a ';

318

execute stmt1 using @arg00, @arg01;

319

320

321

322

set @arg00= 'one' ;

323

set @arg01= 'two' ;

324

set @arg02= 'five' ;

325

prepare stmt1 from ' select b FROM t1 where b in (?,?,?) order by b ' ;

326

execute stmt1 using @arg00, @arg01, @arg02 ;

327

328

one

329

two

330

prepare stmt1 from ' select b FROM t1 where b like ? ';

331

set @arg00='two' ;

332

execute stmt1 using @arg00 ;

333

334

two

335

set @arg00='tw%' ;

336

execute stmt1 using @arg00 ;

337

338

two

339

set @arg00='%wo' ;

340

execute stmt1 using @arg00 ;

341

342

two

343

set @arg00=null ;

344

insert into t9 set c1= 0, c5 = NULL ;

345

select c5 from t9 where c5 > NULL ;

346

347

prepare stmt1 from ' select c5 from t9 where c5 > ? ';

348

execute stmt1 using @arg00 ;

349

350

select c5 from t9 where c5 < NULL ;

351

352

prepare stmt1 from ' select c5 from t9 where c5 < ? ';

353

execute stmt1 using @arg00 ;

354

355

select c5 from t9 where c5 = NULL ;

356

357

prepare stmt1 from ' select c5 from t9 where c5 = ? ';

358

execute stmt1 using @arg00 ;

359

360

select c5 from t9 where c5 <=> NULL ;

361

362

NULL

363

prepare stmt1 from ' select c5 from t9 where c5 <=> ? ';

364

execute stmt1 using @arg00 ;

365

366

NULL

367

delete from t9 where c1= 0 ;

368

set @arg00='>' ;

369

select a FROM t1 where a @arg00 1 ;

370

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

371

prepare stmt1 from ' select a FROM t1 where a ? 1 ' ;

372

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

373

set @arg00=1 ;

374

select a,b FROM t1 where a is not NULL

375

AND b is not NULL group by a - @arg00 ;

376

a b

377

1 one

378

2 two

379

3 three

380

4 four

381

prepare stmt1 from ' select a,b FROM t1 where a is not NULL

382

AND b is not NULL group by a - ? ' ;

383

execute stmt1 using @arg00 ;

384

a b

385

1 one

386

2 two

387

3 three

388

4 four

389

set @arg00='two' ;

390

select a,b FROM t1 where a is not NULL

391

AND b is not NULL having b <> @arg00 order by a ;

392

a b

393

1 one

394

3 three

395

4 four

396

prepare stmt1 from ' select a,b FROM t1 where a is not NULL

397

AND b is not NULL having b <> ? order by a ' ;

398

execute stmt1 using @arg00 ;

399

a b

400

1 one

401

3 three

402

4 four

403

set @arg00=1 ;

404

select a,b FROM t1 where a is not NULL

405

AND b is not NULL order by a - @arg00 ;

406

a b

407

1 one

408

2 two

409

3 three

410

4 four

411

prepare stmt1 from ' select a,b FROM t1 where a is not NULL

412

AND b is not NULL order by a - ? ' ;

413

execute stmt1 using @arg00 ;

414

a b

415

1 one

416

2 two

417

3 three

418

4 four

419

set @arg00=2 ;

420

select a,b from t1 order by 2 ;

421

a b

422

4 four

423

1 one

424

3 three

425

2 two

426

prepare stmt1 from ' select a,b from t1

427

order by ? ';

428

execute stmt1 using @arg00;

429

a b

430

4 four

431

1 one

432

3 three

433

2 two

434

set @arg00=1 ;

435

execute stmt1 using @arg00;

436

a b

437

1 one

438

2 two

439

3 three

440

4 four

441

set @arg00=0 ;

442

execute stmt1 using @arg00;

443

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

444

set @arg00=1;

445

prepare stmt1 from ' select a,b from t1 order by a

446

limit 1 ';

447

execute stmt1 ;

448

a b

449

1 one

450

prepare stmt1 from ' select a,b from t1 order by a limit ? ';

451

execute stmt1 using @arg00;

452

a b

453

1 one

454

set @arg00='b' ;

455

set @arg01=0 ;

456

set @arg02=2 ;

457

set @arg03=2 ;

458

select sum(a), @arg00 from t1 where a > @arg01

459

and b is not null group by substr(b,@arg02)

460

having sum(a) <> @arg03 ;

461

sum(a) @arg00

462

3 b

463

1 b

464

4 b

465

prepare stmt1 from ' select sum(a), ? from t1 where a > ?

466

and b is not null group by substr(b,?)

467

having sum(a) <> ? ';

468

execute stmt1 using @arg00, @arg01, @arg02, @arg03;

469

sum(a) ?

470

3 b

471

1 b

472

4 b

473

test_sequence

474

------ join tests ------

475

select first.a as a1, second.a as a2

476

from t1 first, t1 second

477

where first.a = second.a order by a1 ;

478

a1 a2

479

1 1

480

2 2

481

3 3

482

4 4

483

prepare stmt1 from ' select first.a as a1, second.a as a2

484

from t1 first, t1 second

485

where first.a = second.a order by a1 ';

486

execute stmt1 ;

487

a1 a2

488

1 1

489

2 2

490

3 3

491

4 4

492

set @arg00='ABC';

493

set @arg01='two';

494

set @arg02='one';

495

select first.a, @arg00, second.a FROM t1 first, t1 second

496

where @arg01 = first.b or first.a = second.a or second.b = @arg02

497

order by second.a, first.a;

498

a @arg00 a

499

1 ABC 1

500

2 ABC 1

501

3 ABC 1

502

4 ABC 1

503

2 ABC 2

504

2 ABC 3

505

3 ABC 3

506

2 ABC 4

507

4 ABC 4

508

prepare stmt1 from ' select first.a, ?, second.a FROM t1 first, t1 second

509

where ? = first.b or first.a = second.a or second.b = ?

510

order by second.a, first.a';

511

execute stmt1 using @arg00, @arg01, @arg02;

512

a ? a

513

1 ABC 1

514

2 ABC 1

515

3 ABC 1

516

4 ABC 1

517

2 ABC 2

518

2 ABC 3

519

3 ABC 3

520

2 ABC 4

521

4 ABC 4

522

drop table if exists t2 ;

523

create table t2 as select * from t1 ;

524

set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;

525

set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;

526

set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;

527

set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;

528

set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;

529

set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;

530

set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;

531

set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;

532

set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;

533

the join statement is:

534

SELECT * FROM t2 right join t1 using(a) order by t2.a

535

prepare stmt1 from @query9 ;

536

execute stmt1 ;

537

a b b

538

1 one one

539

2 two two

540

3 three three

541

4 four four

542

execute stmt1 ;

543

a b b

544

1 one one

545

2 two two

546

3 three three

547

4 four four

548

execute stmt1 ;

549

a b b

550

1 one one

551

2 two two

552

3 three three

553

4 four four

554

the join statement is:

555

SELECT * FROM t2 natural right join t1 order by t2.a

556

prepare stmt1 from @query8 ;

557

execute stmt1 ;

558

a b

559

1 one

560

2 two

561

3 three

562

4 four

563

execute stmt1 ;

564

a b

565

1 one

566

2 two

567

3 three

568

4 four

569

execute stmt1 ;

570

a b

571

1 one

572

2 two

573

3 three

574

4 four

575

the join statement is:

576

SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a

577

prepare stmt1 from @query7 ;

578

execute stmt1 ;

579

a b a b

580

1 one 1 one

581

2 two 2 two

582

3 three 3 three

583

4 four 4 four

584

execute stmt1 ;

585

a b a b

586

1 one 1 one

587

2 two 2 two

588

3 three 3 three

589

4 four 4 four

590

execute stmt1 ;

591

a b a b

592

1 one 1 one

593

2 two 2 two

594

3 three 3 three

595

4 four 4 four

596

the join statement is:

597

SELECT * FROM t2 left join t1 using(a) order by t2.a

598

prepare stmt1 from @query6 ;

599

execute stmt1 ;

600

a b b

601

1 one one

602

2 two two

603

3 three three

604

4 four four

605

execute stmt1 ;

606

a b b

607

1 one one

608

2 two two

609

3 three three

610

4 four four

611

execute stmt1 ;

612

a b b

613

1 one one

614

2 two two

615

3 three three

616

4 four four

617

the join statement is:

618

SELECT * FROM t2 natural left join t1 order by t2.a

619

prepare stmt1 from @query5 ;

620

execute stmt1 ;

621

a b

622

1 one

623

2 two

624

3 three

625

4 four

626

execute stmt1 ;

627

a b

628

1 one

629

2 two

630

3 three

631

4 four

632

execute stmt1 ;

633

a b

634

1 one

635

2 two

636

3 three

637

4 four

638

the join statement is:

639

SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a

640

prepare stmt1 from @query4 ;

641

execute stmt1 ;

642

a b a b

643

1 one 1 one

644

2 two 2 two

645

3 three 3 three

646

4 four 4 four

647

execute stmt1 ;

648

a b a b

649

1 one 1 one

650

2 two 2 two

651

3 three 3 three

652

4 four 4 four

653

execute stmt1 ;

654

a b a b

655

1 one 1 one

656

2 two 2 two

657

3 three 3 three

658

4 four 4 four

659

the join statement is:

660

SELECT * FROM t2 join t1 using(a) order by t2.a

661

prepare stmt1 from @query3 ;

662

execute stmt1 ;

663

a b b

664

1 one one

665

2 two two

666

3 three three

667

4 four four

668

execute stmt1 ;

669

a b b

670

1 one one

671

2 two two

672

3 three three

673

4 four four

674

execute stmt1 ;

675

a b b

676

1 one one

677

2 two two

678

3 three three

679

4 four four

680

the join statement is:

681

SELECT * FROM t2 natural join t1 order by t2.a

682

prepare stmt1 from @query2 ;

683

execute stmt1 ;

684

a b

685

1 one

686

2 two

687

3 three

688

4 four

689

execute stmt1 ;

690

a b

691

1 one

692

2 two

693

3 three

694

4 four

695

execute stmt1 ;

696

a b

697

1 one

698

2 two

699

3 three

700

4 four

701

the join statement is:

702

SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a

703

prepare stmt1 from @query1 ;

704

execute stmt1 ;

705

a b a b

706

1 one 1 one

707

2 two 2 two

708

3 three 3 three

709

4 four 4 four

710

execute stmt1 ;

711

a b a b

712

1 one 1 one

713

2 two 2 two

714

3 three 3 three

715

4 four 4 four

716

execute stmt1 ;

717

a b a b

718

1 one 1 one

719

2 two 2 two

720

3 three 3 three

721

4 four 4 four

722

drop table t2 ;

723

test_sequence

724

------ subquery tests ------

725

prepare stmt1 from ' select a, b FROM t1 outer_table where

726

a = (select a from t1 where b = ''two'') ';

727

execute stmt1 ;

728

a b

729

2 two

730

set @arg00='two' ;

731

select a, b FROM t1 outer_table where

732

a = (select a from t1 where b = 'two' ) and b=@arg00 ;

733

a b

734

2 two

735

prepare stmt1 from ' select a, b FROM t1 outer_table where

736

a = (select a from t1 where b = ''two'') and b=? ';

737

execute stmt1 using @arg00;

738

a b

739

2 two

740

set @arg00='two' ;

741

select a, b FROM t1 outer_table where

742

a = (select a from t1 where b = @arg00 ) and b='two' ;

743

a b

744

2 two

745

prepare stmt1 from ' select a, b FROM t1 outer_table where

746

a = (select a from t1 where b = ? ) and b=''two'' ' ;

747

execute stmt1 using @arg00;

748

a b

749

2 two

750

set @arg00=3 ;

751

set @arg01='three' ;

752

select a,b FROM t1 where (a,b) in (select 3, 'three');

753

a b

754

3 three

755

select a FROM t1 where (a,b) in (select @arg00,@arg01);

756

757

758

prepare stmt1 from ' select a FROM t1 where (a,b) in (select ?, ?) ';

759

execute stmt1 using @arg00, @arg01;

760

761

762

set @arg00=1 ;

763

set @arg01='two' ;

764

set @arg02=2 ;

765

set @arg03='two' ;

766

select a, @arg00, b FROM t1 outer_table where

767

b=@arg01 and a = (select @arg02 from t1 where b = @arg03 ) ;

768

a @arg00 b

769

2 1 two

770

prepare stmt1 from ' select a, ?, b FROM t1 outer_table where

771

b=? and a = (select ? from t1 where b = ? ) ' ;

772

execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;

773

a ? b

774

2 1 two

775

prepare stmt1 from 'select c4 FROM t9 where

776

c13 = (select MAX(b) from t1 where a = ?) and c22 = ? ' ;

777

execute stmt1 using @arg01, @arg02;

778

779

prepare stmt1 from ' select a, b FROM t1 outer_table where

780

a = (select a from t1 where b = outer_table.b ) order by a ';

781

execute stmt1 ;

782

a b

783

1 one

784

2 two

785

3 three

786

4 four

787

prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=

788

(SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';

789

execute stmt1 ;

790

ccc

791

792

deallocate prepare stmt1 ;

793

prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=

794

(SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';

795

execute stmt1 ;

796

ccc

797

798

deallocate prepare stmt1 ;

799

prepare stmt1 from ' SELECT a as ccc from t1 outr where a+1=

800

(SELECT 1+outr.a from t1 where outr.a+1=a+1 and a=1) ';

801

execute stmt1 ;

802

ccc

803

804

deallocate prepare stmt1 ;

805

set @arg00='two' ;

806

select a, b FROM t1 outer_table where

807

a = (select a from t1 where b = outer_table.b ) and b=@arg00 ;

808

a b

809

2 two

810

prepare stmt1 from ' select a, b FROM t1 outer_table where

811

a = (select a from t1 where b = outer_table.b) and b=? ';

812

execute stmt1 using @arg00;

813

a b

814

2 two

815

set @arg00=2 ;

816

select a, b FROM t1 outer_table where

817

a = (select a from t1 where a = @arg00 and b = outer_table.b) and b='two' ;

818

a b

819

2 two

820

prepare stmt1 from ' select a, b FROM t1 outer_table where

821

a = (select a from t1 where a = ? and b = outer_table.b) and b=''two'' ' ;

822

execute stmt1 using @arg00;

823

a b

824

2 two

825

set @arg00=2 ;

826

select a, b FROM t1 outer_table where

827

a = (select a from t1 where outer_table.a = @arg00 and a=2) and b='two' ;

828

a b

829

2 two

830

prepare stmt1 from ' select a, b FROM t1 outer_table where

831

a = (select a from t1 where outer_table.a = ? and a=2) and b=''two'' ' ;

832

execute stmt1 using @arg00;

833

a b

834

2 two

835

set @arg00=1 ;

836

set @arg01='two' ;

837

set @arg02=2 ;

838

set @arg03='two' ;

839

select a, @arg00, b FROM t1 outer_table where

840

b=@arg01 and a = (select @arg02 from t1 where outer_table.b = @arg03

841

and outer_table.a=a ) ;

842

a @arg00 b

843

2 1 two

844

prepare stmt1 from ' select a, ?, b FROM t1 outer_table where

845

b=? and a = (select ? from t1 where outer_table.b = ?

846

and outer_table.a=a ) ' ;

847

execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;

848

a ? b

849

2 1 two

850

set @arg00=1 ;

851

set @arg01=0 ;

852

select a, @arg00

853

from ( select a - @arg00 as a from t1 where a=@arg00 ) as t2

854

where a=@arg01;

855

a @arg00

856

0 1

857

prepare stmt1 from ' select a, ?

858

from ( select a - ? as a from t1 where a=? ) as t2

859

where a=? ';

860

execute stmt1 using @arg00, @arg00, @arg00, @arg01 ;

861

a ?

862

0 1

863

drop table if exists t2 ;

864

create table t2 as select * from t1;

865

prepare stmt1 from ' select a in (select a from t2) from t1 ' ;

866

execute stmt1 ;

867

a in (select a from t2)

868

869

870

871

872

drop table if exists t5, t6, t7 ;

873

create table t5 (a int , b int) ;

874

create table t6 like t5 ;

875

create table t7 like t5 ;

876

insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7),

877

(2, -1), (3, 10) ;

878

insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1) ;

879

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

880

prepare stmt1 from ' select a, (select count(distinct t5.b) as sum from t5, t6

881

where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b

882

group by t5.a order by sum limit 1) from t7 ' ;

883

execute stmt1 ;

884

a (select count(distinct t5.b) as sum from t5, t6

885

where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b

886

group by t5.a order by sum limit 1)

887

3 1

888

2 2

889

1 2

890

execute stmt1 ;

891

a (select count(distinct t5.b) as sum from t5, t6

892

where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b

893

group by t5.a order by sum limit 1)

894

3 1

895

2 2

896

1 2

897

execute stmt1 ;

898

a (select count(distinct t5.b) as sum from t5, t6

899

where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b

900

group by t5.a order by sum limit 1)

901

3 1

902

2 2

903

1 2

904

drop table t5, t6, t7 ;

905

drop table if exists t2 ;

906

create table t2 as select * from t9;

907

set @stmt= ' SELECT

908

(SELECT SUM(c1 + c12 + 0.0) FROM t2

909

where (t9.c2 - 0e-3) = t2.c2

910

GROUP BY t9.c15 LIMIT 1) as scalar_s,

911

exists (select 1.0e+0 from t2

912

where t2.c3 * 9.0000000000 = t9.c4) as exists_s,

913

c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s,

914

(c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s

915

FROM t9,

916

(select c25 x, c32 y from t2) tt WHERE x = c25 ' ;

917

prepare stmt1 from @stmt ;

918

execute stmt1 ;

919

execute stmt1 ;

920

set @stmt= concat('explain ',@stmt);

921

prepare stmt1 from @stmt ;

922

execute stmt1 ;

923

execute stmt1 ;

924

set @stmt= ' SELECT

925

(SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2

926

GROUP BY t9.c15 LIMIT 1) as scalar_s,

927

exists (select ? from t2

928

where t2.c3*?=t9.c4) as exists_s,

929

c5*? in (select c6+? from t2) as in_s,

930

(c7-?, c8-?) in (select c9+?, c10+? from t2) as in_row_s

931

FROM t9,

932

(select c25 x, c32 y from t2) tt WHERE x =c25 ' ;

933

set @arg00= 0.0 ;

934

set @arg01= 0e-3 ;

935

set @arg02= 1.0e+0 ;

936

set @arg03= 9.0000000000 ;

937

set @arg04= 4 ;

938

set @arg05= 0.3e+1 ;

939

set @arg06= 4 ;

940

set @arg07= 4 ;

941

set @arg08= 4.0 ;

942

set @arg09= 40e-1 ;

943

prepare stmt1 from @stmt ;

944

execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,

945

@arg07, @arg08, @arg09 ;

946

execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,

947

@arg07, @arg08, @arg09 ;

948

set @stmt= concat('explain ',@stmt);

949

prepare stmt1 from @stmt ;

950

execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,

951

@arg07, @arg08, @arg09 ;

952

execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,

953

@arg07, @arg08, @arg09 ;

954

drop table t2 ;

955

select 1 < (select a from t1) ;

956

ERROR 21000: Subquery returns more than 1 row

957

prepare stmt1 from ' select 1 < (select a from t1) ' ;

958

execute stmt1 ;

959

ERROR 21000: Subquery returns more than 1 row

960

select 1 as my_col ;

961

my_col

962

963

test_sequence

964

------ union tests ------

965

prepare stmt1 from ' select a FROM t1 where a=1

966

union distinct

967

select a FROM t1 where a=1 ';

968

execute stmt1 ;

969

970

971

execute stmt1 ;

972

973

974

prepare stmt1 from ' select a FROM t1 where a=1

975

union all

976

select a FROM t1 where a=1 ';

977

execute stmt1 ;

978

979

980

981

prepare stmt1 from ' SELECT 1, 2 union SELECT 1 ' ;

982

ERROR 21000: The used SELECT statements have a different number of columns

983

prepare stmt1 from ' SELECT 1 union SELECT 1, 2 ' ;

984

ERROR 21000: The used SELECT statements have a different number of columns

985

prepare stmt1 from ' SELECT * from t1 union SELECT 1 ' ;

986

ERROR 21000: The used SELECT statements have a different number of columns

987

prepare stmt1 from ' SELECT 1 union SELECT * from t1 ' ;

988

ERROR 21000: The used SELECT statements have a different number of columns

989

set @arg00=1 ;

990

select @arg00 FROM t1 where a=1

991

union distinct

992

select 1 FROM t1 where a=1;

993

@arg00

994

995

prepare stmt1 from ' select ? FROM t1 where a=1

996

union distinct

997

select 1 FROM t1 where a=1 ' ;

998

execute stmt1 using @arg00;

999

1000

1001

set @arg00=1 ;

1002

select 1 FROM t1 where a=1

1003

union distinct

1004

select @arg00 FROM t1 where a=1;

1005

1006

1007

prepare stmt1 from ' select 1 FROM t1 where a=1

1008

union distinct

1009

select ? FROM t1 where a=1 ' ;

1010

execute stmt1 using @arg00;

1011

1012

1013

set @arg00='a' ;

1014

select @arg00 FROM t1 where a=1

1015

union distinct

1016

select @arg00 FROM t1 where a=1;

1017

@arg00

1018

1019

prepare stmt1 from ' select ? FROM t1 where a=1

1020

union distinct

1021

select ? FROM t1 where a=1 ';

1022

execute stmt1 using @arg00, @arg00;

1023

1024

1025

prepare stmt1 from ' select ?

1026

union distinct

1027

select ? ';

1028

execute stmt1 using @arg00, @arg00;

1029

1030

1031

set @arg00='a' ;

1032

set @arg01=1 ;

1033

set @arg02='a' ;

1034

set @arg03=2 ;

1035

select @arg00 FROM t1 where a=@arg01

1036

union distinct

1037

select @arg02 FROM t1 where a=@arg03;

1038

@arg00

1039

1040

prepare stmt1 from ' select ? FROM t1 where a=?

1041

union distinct

1042

select ? FROM t1 where a=? ' ;

1043

execute stmt1 using @arg00, @arg01, @arg02, @arg03;

1044

1045

1046

set @arg00=1 ;

1047

prepare stmt1 from ' select sum(a) + 200, ? from t1

1048

union distinct

1049

select sum(a) + 200, 1 from t1

1050

group by b ' ;

1051

execute stmt1 using @arg00;

1052

sum(a) + 200 ?

1053

210 1

1054

204 1

1055

201 1

1056

203 1

1057

202 1

1058

set @Oporto='Oporto' ;

1059

set @Lisboa='Lisboa' ;

1060

set @0=0 ;

1061

set @1=1 ;

1062

set @2=2 ;

1063

set @3=3 ;

1064

set @4=4 ;

1065

select @Oporto,@Lisboa,@0,@1,@2,@3,@4 ;

1066

@Oporto @Lisboa @0 @1 @2 @3 @4

1067

Oporto Lisboa 0 1 2 3 4

1068

select sum(a) + 200 as the_sum, @Oporto as the_town from t1

1069

group by b

1070

union distinct

1071

select sum(a) + 200, @Lisboa from t1

1072

group by b ;

1073

the_sum the_town

1074

204 Oporto

1075

201 Oporto

1076

203 Oporto

1077

202 Oporto

1078

204 Lisboa

1079

201 Lisboa

1080

203 Lisboa

1081

202 Lisboa

1082

prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1

1083

group by b

1084

union distinct

1085

select sum(a) + 200, ? from t1

1086

group by b ' ;

1087

execute stmt1 using @Oporto, @Lisboa;

1088

the_sum the_town

1089

204 Oporto

1090

201 Oporto

1091

203 Oporto

1092

202 Oporto

1093

204 Lisboa

1094

201 Lisboa

1095

203 Lisboa

1096

202 Lisboa

1097

select sum(a) + 200 as the_sum, @Oporto as the_town from t1

1098

where a > @1

1099

group by b

1100

union distinct

1101

select sum(a) + 200, @Lisboa from t1

1102

where a > @2

1103

group by b ;

1104

the_sum the_town

1105

204 Oporto

1106

203 Oporto

1107

202 Oporto

1108

204 Lisboa

1109

203 Lisboa

1110

prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1

1111

where a > ?

1112

group by b

1113

union distinct

1114

select sum(a) + 200, ? from t1

1115

where a > ?

1116

group by b ' ;

1117

execute stmt1 using @Oporto, @1, @Lisboa, @2;

1118

the_sum the_town

1119

204 Oporto

1120

203 Oporto

1121

202 Oporto

1122

204 Lisboa

1123

203 Lisboa

1124

select sum(a) + 200 as the_sum, @Oporto as the_town from t1

1125

where a > @1

1126

group by b

1127

having avg(a) > @2

1128

union distinct

1129

select sum(a) + 200, @Lisboa from t1

1130

where a > @2

1131

group by b

1132

having avg(a) > @3;

1133

the_sum the_town

1134

204 Oporto

1135

203 Oporto

1136

204 Lisboa

1137

prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1

1138

where a > ?

1139

group by b

1140

having avg(a) > ?

1141

union distinct

1142

select sum(a) + 200, ? from t1

1143

where a > ?

1144

group by b

1145

having avg(a) > ? ';

1146

execute stmt1 using @Oporto, @1, @2, @Lisboa, @2, @3;

1147

the_sum the_town

1148

204 Oporto

1149

203 Oporto

1150

204 Lisboa

1151

test_sequence

1152

------ explain select tests ------

1153

prepare stmt1 from ' explain select * from t9 ' ;

1154

execute stmt1;

1155

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

1156

def id 8 3 1 N 32929 0 63

1157

def select_type 253 19 6 N 1 31 8

1158

def table 253 64 2 Y 0 31 8

1159

def type 253 10 3 Y 0 31 8

1160

def possible_keys 253 4096 0 Y 0 31 8

1161

def key 253 64 0 Y 0 31 8

1162

def key_len 253 4096 0 Y 0 31 8

1163

def ref 253 1024 0 Y 0 31 8

1164

def rows 8 10 1 Y 32928 0 63

1165

def Extra 253 255 0 N 1 31 8

1166

id select_type table type possible_keys key key_len ref rows Extra

1167

1 SIMPLE t9 ALL NULL NULL NULL NULL 2

1168

test_sequence

1169

------ delete tests ------

1170

delete from t1 ;

1171

insert into t1 values (1,'one');

1172

insert into t1 values (2,'two');

1173

insert into t1 values (3,'three');

1174

insert into t1 values (4,'four');

1175

commit ;

1176

delete from t9 ;

1177

insert into t9

1178

set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,

1179

c10= 1, c11= 1, c12 = 1,

1180

c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',

1181

c16= '11:11:11', c17= '2004',

1182

c18= 1, c19=true, c20= 'a', c21= '123456789a',

1183

c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',

1184

c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',

1185

c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';

1186

insert into t9

1187

set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,

1188

c10= 9, c11= 9, c12 = 9,

1189

c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',

1190

c16= '11:11:11', c17= '2004',

1191

c18= 1, c19=false, c20= 'a', c21= '123456789a',

1192

c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',

1193

c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',

1194

c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';

1195

commit ;

1196

prepare stmt1 from 'delete from t1 where a=2' ;

1197

execute stmt1;

1198

select a,b from t1 where a=2;

1199

a b

1200

execute stmt1;

1201

insert into t1 values(0,NULL);

1202

set @arg00=NULL;

1203

prepare stmt1 from 'delete from t1 where b=?' ;

1204

execute stmt1 using @arg00;

1205

select a,b from t1 where b is NULL ;

1206

a b

1207

0 NULL

1208

set @arg00='one';

1209

execute stmt1 using @arg00;

1210

select a,b from t1 where b=@arg00;

1211

a b

1212

prepare stmt1 from 'truncate table t1' ;

1213

test_sequence

1214

------ update tests ------

1215

delete from t1 ;

1216

insert into t1 values (1,'one');

1217

insert into t1 values (2,'two');

1218

insert into t1 values (3,'three');

1219

insert into t1 values (4,'four');

1220

commit ;

1221

delete from t9 ;

1222

insert into t9

1223

set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,

1224

c10= 1, c11= 1, c12 = 1,

1225

c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',

1226

c16= '11:11:11', c17= '2004',

1227

c18= 1, c19=true, c20= 'a', c21= '123456789a',

1228

c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',

1229

c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',

1230

c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';

1231

insert into t9

1232

set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,

1233

c10= 9, c11= 9, c12 = 9,

1234

c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',

1235

c16= '11:11:11', c17= '2004',

1236

c18= 1, c19=false, c20= 'a', c21= '123456789a',

1237

c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',

1238

c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',

1239

c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';

1240

commit ;

1241

prepare stmt1 from 'update t1 set b=''a=two'' where a=2' ;

1242

execute stmt1;

1243

select a,b from t1 where a=2;

1244

a b

1245

2 a=two

1246

execute stmt1;

1247

select a,b from t1 where a=2;

1248

a b

1249

2 a=two

1250

set @arg00=NULL;

1251

prepare stmt1 from 'update t1 set b=? where a=2' ;

1252

execute stmt1 using @arg00;

1253

select a,b from t1 where a=2;

1254

a b

1255

2 NULL

1256

set @arg00='two';

1257

execute stmt1 using @arg00;

1258

select a,b from t1 where a=2;

1259

a b

1260

2 two

1261

set @arg00=2;

1262

prepare stmt1 from 'update t1 set b=NULL where a=?' ;

1263

execute stmt1 using @arg00;

1264

select a,b from t1 where a=@arg00;

1265

a b

1266

2 NULL

1267

update t1 set b='two' where a=@arg00;

1268

set @arg00=2000;

1269

execute stmt1 using @arg00;

1270

select a,b from t1 where a=@arg00;

1271

a b

1272

set @arg00=2;

1273

set @arg01=22;

1274

prepare stmt1 from 'update t1 set a=? where a=?' ;

1275

execute stmt1 using @arg00, @arg00;

1276

select a,b from t1 where a=@arg00;

1277

a b

1278

2 two

1279

execute stmt1 using @arg01, @arg00;

1280

select a,b from t1 where a=@arg01;

1281

a b

1282

22 two

1283

execute stmt1 using @arg00, @arg01;

1284

select a,b from t1 where a=@arg00;

1285

a b

1286

2 two

1287

set @arg00=NULL;

1288

set @arg01=2;

1289

execute stmt1 using @arg00, @arg01;

1290

Warnings:

1291

Warning 1048 Column 'a' cannot be null

1292

select a,b from t1 order by a;

1293

a b

1294

0 two

1295

1 one

1296

3 three

1297

4 four

1298

set @arg00=0;

1299

execute stmt1 using @arg01, @arg00;

1300

select a,b from t1 order by a;

1301

a b

1302

1 one

1303

2 two

1304

3 three

1305

4 four

1306

set @arg00=23;

1307

set @arg01='two';

1308

set @arg02=2;

1309

set @arg03='two';

1310

set @arg04=2;

1311

drop table if exists t2;

1312

create table t2 as select a,b from t1 ;

1313

prepare stmt1 from 'update t1 set a=? where b=?

1314

and a in (select ? from t2

1315

where b = ? or a = ?)';

1316

execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;

1317

affected rows: 1

1318

info: Rows matched: 1 Changed: 1 Warnings: 0

1319

select a,b from t1 where a = @arg00 ;

1320

a b

1321

23 two

1322

prepare stmt1 from 'update t1 set a=? where b=?

1323

and a not in (select ? from t2

1324

where b = ? or a = ?)';

1325

execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;

1326

affected rows: 1

1327

info: Rows matched: 1 Changed: 1 Warnings: 0

1328

select a,b from t1 order by a ;

1329

a b

1330

1 one

1331

2 two

1332

3 three

1333

4 four

1334

drop table t2 ;

1335

create table t2

1336

(

1337

a int, b varchar(30),

1338

primary key(a)

1339

) engine = 'HEAP' ;

1340

insert into t2(a,b) select a, b from t1 ;

1341

prepare stmt1 from 'update t1 set a=? where b=?

1342

and a in (select ? from t2

1343

where b = ? or a = ?)';

1344

execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04 ;

1345

affected rows: 1

1346

info: Rows matched: 1 Changed: 1 Warnings: 0

1347

select a,b from t1 where a = @arg00 ;

1348

a b

1349

23 two

1350

prepare stmt1 from 'update t1 set a=? where b=?

1351

and a not in (select ? from t2

1352

where b = ? or a = ?)';

1353

execute stmt1 using @arg04, @arg01, @arg02, @arg03, @arg00 ;

1354

affected rows: 1

1355

info: Rows matched: 1 Changed: 1 Warnings: 0

1356

select a,b from t1 order by a ;

1357

a b

1358

1 one

1359

2 two

1360

3 three

1361

4 four

1362

drop table t2 ;

1363

set @arg00=1;

1364

prepare stmt1 from 'update t1 set b=''bla''

1365

where a=2

1366

limit 1';

1367

execute stmt1 ;

1368

select a,b from t1 where b = 'bla' ;

1369

a b

1370

2 bla

1371

prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';

1372

execute stmt1 using @arg00;

1373

test_sequence

1374

------ insert tests ------

1375

delete from t1 ;

1376

insert into t1 values (1,'one');

1377

insert into t1 values (2,'two');

1378

insert into t1 values (3,'three');

1379

insert into t1 values (4,'four');

1380

commit ;

1381

delete from t9 ;

1382

insert into t9

1383

set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,

1384

c10= 1, c11= 1, c12 = 1,

1385

c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',

1386

c16= '11:11:11', c17= '2004',

1387

c18= 1, c19=true, c20= 'a', c21= '123456789a',

1388

c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',

1389

c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',

1390

c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';

1391

insert into t9

1392

set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,

1393

c10= 9, c11= 9, c12 = 9,

1394

c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',

1395

c16= '11:11:11', c17= '2004',

1396

c18= 1, c19=false, c20= 'a', c21= '123456789a',

1397

c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',

1398

c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',

1399

c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';

1400

commit ;

1401

prepare stmt1 from 'insert into t1 values(5, ''five'' )';

1402

execute stmt1;

1403

select a,b from t1 where a = 5;

1404

a b

1405

5 five

1406

set @arg00='six' ;

1407

prepare stmt1 from 'insert into t1 values(6, ? )';

1408

execute stmt1 using @arg00;

1409

select a,b from t1 where b = @arg00;

1410

a b

1411

6 six

1412

execute stmt1 using @arg00;

1413

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

1414

set @arg00=NULL ;

1415

prepare stmt1 from 'insert into t1 values(0, ? )';

1416

execute stmt1 using @arg00;

1417

select a,b from t1 where b is NULL;

1418

a b

1419

0 NULL

1420

set @arg00=8 ;

1421

set @arg01='eight' ;

1422

prepare stmt1 from 'insert into t1 values(?, ? )';

1423

execute stmt1 using @arg00, @arg01 ;

1424

select a,b from t1 where b = @arg01;

1425

a b

1426

8 eight

1427

set @NULL= null ;

1428

set @arg00= 'abc' ;

1429

execute stmt1 using @NULL, @NULL ;

1430

ERROR 23000: Column 'a' cannot be null

1431

execute stmt1 using @NULL, @NULL ;

1432

ERROR 23000: Column 'a' cannot be null

1433

execute stmt1 using @NULL, @arg00 ;

1434

ERROR 23000: Column 'a' cannot be null

1435

execute stmt1 using @NULL, @arg00 ;

1436

ERROR 23000: Column 'a' cannot be null

1437

set @arg01= 10000 + 2 ;

1438

execute stmt1 using @arg01, @arg00 ;

1439

set @arg01= 10000 + 1 ;

1440

execute stmt1 using @arg01, @arg00 ;

1441

select * from t1 where a > 10000 order by a ;

1442

a b

1443

10001 abc

1444

10002 abc

1445

delete from t1 where a > 10000 ;

1446

set @arg01= 10000 + 2 ;

1447

execute stmt1 using @arg01, @NULL ;

1448

set @arg01= 10000 + 1 ;

1449

execute stmt1 using @arg01, @NULL ;

1450

select * from t1 where a > 10000 order by a ;

1451

a b

1452

10001 NULL

1453

10002 NULL

1454

delete from t1 where a > 10000 ;

1455

set @arg01= 10000 + 10 ;

1456

execute stmt1 using @arg01, @arg01 ;

1457

set @arg01= 10000 + 9 ;

1458

execute stmt1 using @arg01, @arg01 ;

1459

set @arg01= 10000 + 8 ;

1460

execute stmt1 using @arg01, @arg01 ;

1461

set @arg01= 10000 + 7 ;

1462

execute stmt1 using @arg01, @arg01 ;

1463

set @arg01= 10000 + 6 ;

1464

execute stmt1 using @arg01, @arg01 ;

1465

set @arg01= 10000 + 5 ;

1466

execute stmt1 using @arg01, @arg01 ;

1467

set @arg01= 10000 + 4 ;

1468

execute stmt1 using @arg01, @arg01 ;

1469

set @arg01= 10000 + 3 ;

1470

execute stmt1 using @arg01, @arg01 ;

1471

set @arg01= 10000 + 2 ;

1472

execute stmt1 using @arg01, @arg01 ;

1473

set @arg01= 10000 + 1 ;

1474

execute stmt1 using @arg01, @arg01 ;

1475

select * from t1 where a > 10000 order by a ;

1476

a b

1477

10001 10001

1478

10002 10002

1479

10003 10003

1480

10004 10004

1481

10005 10005

1482

10006 10006

1483

10007 10007

1484

10008 10008

1485

10009 10009

1486

10010 10010

1487

delete from t1 where a > 10000 ;

1488

set @arg00=81 ;

1489

set @arg01='8-1' ;

1490

set @arg02=82 ;

1491

set @arg03='8-2' ;

1492

prepare stmt1 from 'insert into t1 values(?,?),(?,?)';

1493

execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;

1494

select a,b from t1 where a in (@arg00,@arg02) ;

1495

a b

1496

81 8-1

1497

82 8-2

1498

set @arg00=9 ;

1499

set @arg01='nine' ;

1500

prepare stmt1 from 'insert into t1 set a=?, b=? ';

1501

execute stmt1 using @arg00, @arg01 ;

1502

select a,b from t1 where a = @arg00 ;

1503

a b

1504

9 nine

1505

set @arg00=6 ;

1506

set @arg01=1 ;

1507

prepare stmt1 from 'insert into t1 set a=?, b=''sechs''

1508

on duplicate key update a=a + ?, b=concat(b,''modified'') ';

1509

execute stmt1 using @arg00, @arg01;

1510

select * from t1 order by a;

1511

a b

1512

0 NULL

1513

1 one

1514

2 two

1515

3 three

1516

4 four

1517

5 five

1518

7 sixmodified

1519

8 eight

1520

9 nine

1521

81 8-1

1522

82 8-2

1523

set @arg00=81 ;

1524

set @arg01=1 ;

1525

execute stmt1 using @arg00, @arg01;

1526

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

1527

drop table if exists t2 ;

1528

create table t2 (id int auto_increment primary key)

1529

ENGINE= 'HEAP' ;

1530

prepare stmt1 from ' select last_insert_id() ' ;

1531

insert into t2 values (NULL) ;

1532

execute stmt1 ;

1533

last_insert_id()

1534

1535

insert into t2 values (NULL) ;

1536

execute stmt1 ;

1537

last_insert_id()

1538

1539

drop table t2 ;

1540

set @1000=1000 ;

1541

set @x1000_2="x1000_2" ;

1542

set @x1000_3="x1000_3" ;

1543

set @x1000="x1000" ;

1544

set @1100=1100 ;

1545

set @x1100="x1100" ;

1546

set @100=100 ;

1547

set @updated="updated" ;

1548

insert into t1 values(1000,'x1000_1') ;

1549

insert into t1 values(@1000,@x1000_2),(@1000,@x1000_3)

1550

on duplicate key update a = a + @100, b = concat(b,@updated) ;

1551

select a,b from t1 where a >= 1000 order by a ;

1552

a b

1553

1000 x1000_3

1554

1100 x1000_1updated

1555

delete from t1 where a >= 1000 ;

1556

insert into t1 values(1000,'x1000_1') ;

1557

prepare stmt1 from ' insert into t1 values(?,?),(?,?)

1558

on duplicate key update a = a + ?, b = concat(b,?) ';

1559

execute stmt1 using @1000, @x1000_2, @1000, @x1000_3, @100, @updated ;

1560

select a,b from t1 where a >= 1000 order by a ;

1561

a b

1562

1000 x1000_3

1563

1100 x1000_1updated

1564

delete from t1 where a >= 1000 ;

1565

insert into t1 values(1000,'x1000_1') ;

1566

execute stmt1 using @1000, @x1000_2, @1100, @x1000_3, @100, @updated ;

1567

select a,b from t1 where a >= 1000 order by a ;

1568

a b

1569

1200 x1000_1updatedupdated

1570

delete from t1 where a >= 1000 ;

1571

prepare stmt1 from ' replace into t1 (a,b) select 100, ''hundred'' ';

1572

execute stmt1;

1573

execute stmt1;

1574

execute stmt1;

1575

test_sequence

1576

------ multi table tests ------

1577

delete from t1 ;

1578

delete from t9 ;

1579

insert into t1(a,b) values (1, 'one'), (2, 'two'), (3, 'three') ;

1580

insert into t9 (c1,c21)

1581

values (1, 'one'), (2, 'two'), (3, 'three') ;

1582

prepare stmt_delete from " delete t1, t9

1583

from t1, t9 where t1.a=t9.c1 and t1.b='updated' ";

1584

prepare stmt_update from " update t1, t9

1585

set t1.b='updated', t9.c21='updated'

1586

where t1.a=t9.c1 and t1.a=? ";

1587

prepare stmt_select1 from " select a, b from t1 order by a" ;

1588

prepare stmt_select2 from " select c1, c21 from t9 order by c1" ;

1589

set @arg00= 1 ;

1590

execute stmt_update using @arg00 ;

1591

execute stmt_delete ;

1592

execute stmt_select1 ;

1593

a b

1594

2 two

1595

3 three

1596

execute stmt_select2 ;

1597

c1 c21

1598

2 two

1599

3 three

1600

set @arg00= @arg00 + 1 ;

1601

execute stmt_update using @arg00 ;

1602

execute stmt_delete ;

1603

execute stmt_select1 ;

1604

a b

1605

3 three

1606

execute stmt_select2 ;

1607

c1 c21

1608

3 three

1609

set @arg00= @arg00 + 1 ;

1610

execute stmt_update using @arg00 ;

1611

execute stmt_delete ;

1612

execute stmt_select1 ;

1613

a b

1614

execute stmt_select2 ;

1615

c1 c21

1616

set @arg00= @arg00 + 1 ;

1617

delete from t1 ;

1618

insert into t1 values (1,'one');

1619

insert into t1 values (2,'two');

1620

insert into t1 values (3,'three');

1621

insert into t1 values (4,'four');

1622

commit ;

1623

delete from t9 ;

1624

insert into t9

1625

set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,

1626

c10= 1, c11= 1, c12 = 1,

1627

c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',

1628

c16= '11:11:11', c17= '2004',

1629

c18= 1, c19=true, c20= 'a', c21= '123456789a',

1630

c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',

1631

c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',

1632

c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';

1633

insert into t9

1634

set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,

1635

c10= 9, c11= 9, c12 = 9,

1636

c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',

1637

c16= '11:11:11', c17= '2004',

1638

c18= 1, c19=false, c20= 'a', c21= '123456789a',

1639

c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',

1640

c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',

1641

c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';

1642

commit ;

1643

insert into t1 values(0,NULL) ;

1644

set @duplicate='duplicate ' ;

1645

set @1000=1000 ;

1646

set @5=5 ;

1647

select a,b from t1 where a < 5 order by a ;

1648

a b

1649

0 NULL

1650

1 one

1651

2 two

1652

3 three

1653

4 four

1654

insert into t1 select a + @1000, concat(@duplicate,b) from t1

1655

where a < @5 ;

1656

affected rows: 5

1657

info: Records: 5 Duplicates: 0 Warnings: 0

1658

select a,b from t1 where a >= 1000 order by a ;

1659

a b

1660

1000 NULL

1661

1001 duplicate one

1662

1002 duplicate two

1663

1003 duplicate three

1664

1004 duplicate four

1665

delete from t1 where a >= 1000 ;

1666

prepare stmt1 from ' insert into t1 select a + ?, concat(?,b) from t1

1667

where a < ? ' ;

1668

execute stmt1 using @1000, @duplicate, @5;

1669

affected rows: 5

1670

info: Records: 5 Duplicates: 0 Warnings: 0

1671

select a,b from t1 where a >= 1000 order by a ;

1672

a b

1673

1000 NULL

1674

1001 duplicate one

1675

1002 duplicate two

1676

1003 duplicate three

1677

1004 duplicate four

1678

delete from t1 where a >= 1000 ;

1679

set @1=1 ;

1680

set @2=2 ;

1681

set @100=100 ;

1682

set @float=1.00;

1683

set @five='five' ;

1684

drop table if exists t2;

1685

create table t2 like t1 ;

1686

insert into t2 (b,a)

1687

select @duplicate, sum(first.a) from t1 first, t1 second

1688

where first.a <> @5 and second.b = first.b

1689

and second.b <> @five

1690

group by second.b

1691

having sum(second.a) > @2

1692

union

1693

select b, a + @100 from t1

1694

where (a,b) in ( select sqrt(a+@1)+CAST(@float AS signed),b

1695

from t1);

1696

affected rows: 3

1697

info: Records: 3 Duplicates: 0 Warnings: 0

1698

select a,b from t2 order by a ;

1699

a b

1700

3 duplicate

1701

4 duplicate

1702

103 three

1703

delete from t2 ;

1704

prepare stmt1 from ' insert into t2 (b,a)

1705

select ?, sum(first.a)

1706

from t1 first, t1 second

1707

where first.a <> ? and second.b = first.b and second.b <> ?

1708

group by second.b

1709

having sum(second.a) > ?

1710

union

1711

select b, a + ? from t1

1712

where (a,b) in ( select sqrt(a+?)+CAST(? AS signed),b

1713

from t1 ) ' ;

1714

execute stmt1 using @duplicate, @5, @five, @2, @100, @1, @float ;

1715

affected rows: 3

1716

info: Records: 3 Duplicates: 0 Warnings: 0

1717

select a,b from t2 order by a ;

1718

a b

1719

3 duplicate

1720

4 duplicate

1721

103 three

1722

drop table t2;

1723

drop table if exists t5 ;

1724

set @arg01= 8;

1725

set @arg02= 8.0;

1726

set @arg03= 80.00000000000e-1;

1727

set @arg04= 'abc' ;

1728

set @arg05= CAST('abc' as binary) ;

1729

set @arg06= '1991-08-05' ;

1730

set @arg07= CAST('1991-08-05' as date);

1731

set @arg08= '1991-08-05 01:01:01' ;

1732

set @arg09= CAST('1991-08-05 01:01:01' as datetime) ;

1733

set @arg10= unix_timestamp('1991-01-01 01:01:01');

1734

set @arg11= YEAR('1991-01-01 01:01:01');

1735

set @arg12= 8 ;

1736

set @arg12= NULL ;

1737

set @arg13= 8.0 ;

1738

set @arg13= NULL ;

1739

set @arg14= 'abc';

1740

set @arg14= NULL ;

1741

set @arg15= CAST('abc' as binary) ;

1742

set @arg15= NULL ;

1743

create table t5 as select

1744

8 as const01, @arg01 as param01,

1745

8.0 as const02, @arg02 as param02,

1746

80.00000000000e-1 as const03, @arg03 as param03,

1747

'abc' as const04, @arg04 as param04,

1748

CAST('abc' as binary) as const05, @arg05 as param05,

1749

'1991-08-05' as const06, @arg06 as param06,

1750

CAST('1991-08-05' as date) as const07, @arg07 as param07,

1751

'1991-08-05 01:01:01' as const08, @arg08 as param08,

1752

CAST('1991-08-05 01:01:01' as datetime) as const09, @arg09 as param09,

1753

unix_timestamp('1991-01-01 01:01:01') as const10, @arg10 as param10,

1754

YEAR('1991-01-01 01:01:01') as const11, @arg11 as param11,

1755

NULL as const12, @arg12 as param12,

1756

@arg13 as param13,

1757

@arg14 as param14,

1758

@arg15 as param15;

1759

show create table t5 ;

1760

Table Create Table

1761

t5 CREATE TABLE `t5` (

1762

`const01` int(1) NOT NULL DEFAULT '0',

1763

`param01` bigint(20) DEFAULT NULL,

1764

`const02` decimal(2,1) NOT NULL DEFAULT '0.0',

1765

`param02` decimal(65,30) DEFAULT NULL,

1766

`const03` double NOT NULL DEFAULT '0',

1767

`param03` double DEFAULT NULL,

1768

`const04` varchar(3) NOT NULL DEFAULT '',

1769

`param04` longtext,

1770

`const05` varbinary(3) NOT NULL DEFAULT '',

1771

`param05` longblob,

1772

`const06` varchar(10) NOT NULL DEFAULT '',

1773

`param06` longtext,

1774

`const07` date DEFAULT NULL,

1775

`param07` longtext,

1776

`const08` varchar(19) NOT NULL DEFAULT '',

1777

`param08` longtext,

1778

`const09` datetime DEFAULT NULL,

1779

`param09` longtext,

1780

`const10` int(10) NOT NULL DEFAULT '0',

1781

`param10` bigint(20) DEFAULT NULL,

1782

`const11` int(4) DEFAULT NULL,

1783

`param11` bigint(20) DEFAULT NULL,

1784

`const12` binary(0) DEFAULT NULL,

1785

`param12` bigint(20) DEFAULT NULL,

1786

`param13` decimal(65,30) DEFAULT NULL,

1787

`param14` longtext,

1788

`param15` longblob

1789

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1790

select * from t5 ;

1791

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

1792

def test t5 t5 const01 const01 3 1 1 N 32769 0 63

1793

def test t5 t5 param01 param01 8 20 1 Y 32768 0 63

1794

def test t5 t5 const02 const02 246 4 3 N 32769 1 63

1795

def test t5 t5 param02 param02 246 67 32 Y 32768 30 63

1796

def test t5 t5 const03 const03 5 17 1 N 32769 31 63

1797

def test t5 t5 param03 param03 5 23 1 Y 32768 31 63

1798

def test t5 t5 const04 const04 253 3 3 N 1 0 8

1799

def test t5 t5 param04 param04 252 4294967295 3 Y 16 0 8

1800

def test t5 t5 const05 const05 253 3 3 N 129 0 63

1801

def test t5 t5 param05 param05 252 4294967295 3 Y 144 0 63

1802

def test t5 t5 const06 const06 253 10 10 N 1 0 8

1803

def test t5 t5 param06 param06 252 4294967295 10 Y 16 0 8

1804

def test t5 t5 const07 const07 10 10 10 Y 128 0 63

1805

def test t5 t5 param07 param07 252 4294967295 10 Y 16 0 8

1806

def test t5 t5 const08 const08 253 19 19 N 1 0 8

1807

def test t5 t5 param08 param08 252 4294967295 19 Y 16 0 8

1808

def test t5 t5 const09 const09 12 19 19 Y 128 0 63

1809

def test t5 t5 param09 param09 252 4294967295 19 Y 16 0 8

1810

def test t5 t5 const10 const10 3 10 9 N 32769 0 63

1811

def test t5 t5 param10 param10 8 20 9 Y 32768 0 63

1812

def test t5 t5 const11 const11 3 4 4 Y 32768 0 63

1813

def test t5 t5 param11 param11 8 20 4 Y 32768 0 63

1814

def test t5 t5 const12 const12 254 0 0 Y 128 0 63

1815

def test t5 t5 param12 param12 8 20 0 Y 32768 0 63

1816

def test t5 t5 param13 param13 246 67 0 Y 32768 30 63

1817

def test t5 t5 param14 param14 252 4294967295 0 Y 16 0 8

1818

def test t5 t5 param15 param15 252 4294967295 0 Y 144 0 63

1819

const01 8

1820

param01 8

1821

const02 8.0

1822

param02 8.000000000000000000000000000000

1823

const03 8

1824

param03 8

1825

const04 abc

1826

param04 abc

1827

const05 abc

1828

param05 abc

1829

const06 1991-08-05

1830

param06 1991-08-05

1831

const07 1991-08-05

1832

param07 1991-08-05

1833

const08 1991-08-05 01:01:01

1834

param08 1991-08-05 01:01:01

1835

const09 1991-08-05 01:01:01

1836

param09 1991-08-05 01:01:01

1837

const10 662680861

1838

param10 662680861

1839

const11 1991

1840

param11 1991

1841

const12 NULL

1842

param12 NULL

1843

param13 NULL

1844

param14 NULL

1845

param15 NULL

1846

drop table t5 ;

1847

test_sequence

1848

------ data type conversion tests ------

1849

delete from t1 ;

1850

insert into t1 values (1,'one');

1851

insert into t1 values (2,'two');

1852

insert into t1 values (3,'three');

1853

insert into t1 values (4,'four');

1854

commit ;

1855

delete from t9 ;

1856

insert into t9

1857

set c1= 1, c2= 1, c3= 1, c4= 1, c5= 1, c6= 1, c7= 1, c8= 1, c9= 1,

1858

c10= 1, c11= 1, c12 = 1,

1859

c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',

1860

c16= '11:11:11', c17= '2004',

1861

c18= 1, c19=true, c20= 'a', c21= '123456789a',

1862

c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',

1863

c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',

1864

c29= 'longblob', c30= 'longtext', c31='one', c32= 'monday';

1865

insert into t9

1866

set c1= 9, c2= 9, c3= 9, c4= 9, c5= 9, c6= 9, c7= 9, c8= 9, c9= 9,

1867

c10= 9, c11= 9, c12 = 9,

1868

c13= '2004-02-29', c14= '2004-02-29 11:11:11', c15= '2004-02-29 11:11:11',

1869

c16= '11:11:11', c17= '2004',

1870

c18= 1, c19=false, c20= 'a', c21= '123456789a',

1871

c22= '123456789a123456789b123456789c', c23= 'tinyblob', c24= 'tinytext',

1872

c25= 'blob', c26= 'text', c27= 'mediumblob', c28= 'mediumtext',

1873

c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';

1874

commit ;

1875

insert into t9 set c1= 0, c15= '1991-01-01 01:01:01' ;

1876

select * from t9 order by c1 ;

1877

c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32

1878

0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

1879

1880

1881

test_sequence

1882

------ select @parameter:= column ------

1883

prepare full_info from "select @arg01, @arg02, @arg03, @arg04,

1884

@arg05, @arg06, @arg07, @arg08,

1885

@arg09, @arg10, @arg11, @arg12,

1886

@arg13, @arg14, @arg15, @arg16,

1887

@arg17, @arg18, @arg19, @arg20,

1888

@arg21, @arg22, @arg23, @arg24,

1889

@arg25, @arg26, @arg27, @arg28,

1890

@arg29, @arg30, @arg31, @arg32" ;

1891

select @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,

1892

@arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,

1893

@arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,

1894

@arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,

1895

@arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,

1896

@arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,

1897

@arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,

1898

@arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32

1899

from t9 where c1= 1 ;

1900

@arg01:= c1 @arg02:= c2 @arg03:= c3 @arg04:= c4 @arg05:= c5 @arg06:= c6 @arg07:= c7 @arg08:= c8 @arg09:= c9 @arg10:= c10 @arg11:= c11 @arg12:= c12 @arg13:= c13 @arg14:= c14 @arg15:= c15 @arg16:= c16 @arg17:= c17 @arg18:= c18 @arg19:= c19 @arg20:= c20 @arg21:= c21 @arg22:= c22 @arg23:= c23 @arg24:= c24 @arg25:= c25 @arg26:= c26 @arg27:= c27 @arg28:= c28 @arg29:= c29 @arg30:= c30 @arg31:= c31 @arg32:= c32

1901

1902

execute full_info ;

1903

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

1904

def @arg01 8 20 1 Y 32896 0 63

1905

def @arg02 8 20 1 Y 32896 0 63

1906

def @arg03 8 20 1 Y 32896 0 63

1907

def @arg04 8 20 1 Y 32896 0 63

1908

def @arg05 8 20 1 Y 32896 0 63

1909

def @arg06 8 20 1 Y 32896 0 63

1910

def @arg07 5 23 1 Y 32896 31 63

1911

def @arg08 5 23 1 Y 32896 31 63

1912

def @arg09 5 23 1 Y 32896 31 63

1913

def @arg10 5 23 1 Y 32896 31 63

1914

def @arg11 246 83 6 Y 32896 30 63

1915

def @arg12 246 83 6 Y 32896 30 63

1916

def @arg13 250 16777215 10 Y 0 31 8

1917

def @arg14 250 16777215 19 Y 0 31 8

1918

def @arg15 250 16777215 19 Y 0 31 8

1919

def @arg16 250 16777215 8 Y 0 31 8

1920

def @arg17 8 20 4 Y 32928 0 63

1921

def @arg18 8 20 1 Y 32896 0 63

1922

def @arg19 8 20 1 Y 32896 0 63

1923

def @arg20 250 16777215 1 Y 0 31 8

1924

def @arg21 250 16777215 10 Y 0 31 8

1925

def @arg22 250 16777215 30 Y 0 31 8

1926

def @arg23 250 16777215 8 Y 0 31 8

1927

def @arg24 250 16777215 8 Y 0 31 8

1928

def @arg25 250 16777215 4 Y 0 31 8

1929

def @arg26 250 16777215 4 Y 0 31 8

1930

def @arg27 250 16777215 10 Y 0 31 8

1931

def @arg28 250 16777215 10 Y 0 31 8

1932

def @arg29 250 16777215 8 Y 0 31 8

1933

def @arg30 250 16777215 8 Y 0 31 8

1934

def @arg31 250 16777215 3 Y 0 31 8

1935

def @arg32 250 16777215 6 Y 0 31 8

1936

@arg01 @arg02 @arg03 @arg04 @arg05 @arg06 @arg07 @arg08 @arg09 @arg10 @arg11 @arg12 @arg13 @arg14 @arg15 @arg16 @arg17 @arg18 @arg19 @arg20 @arg21 @arg22 @arg23 @arg24 @arg25 @arg26 @arg27 @arg28 @arg29 @arg30 @arg31 @arg32

1937

1938

select @arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,

1939

@arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,

1940

@arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,

1941

@arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,

1942

@arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,

1943

@arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,

1944

@arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,

1945

@arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32

1946

from t9 where c1= 0 ;

1947

1948

0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

1949

execute full_info ;

1950

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

1951

def @arg01 8 20 1 Y 32896 0 63

1952

def @arg02 8 20 0 Y 32896 0 63

1953

def @arg03 8 20 0 Y 32896 0 63

1954

def @arg04 8 20 0 Y 32896 0 63

1955

def @arg05 8 20 0 Y 32896 0 63

1956

def @arg06 8 20 0 Y 32896 0 63

1957

def @arg07 5 23 0 Y 32896 31 63

1958

def @arg08 5 23 0 Y 32896 31 63

1959

def @arg09 5 23 0 Y 32896 31 63

1960

def @arg10 5 23 0 Y 32896 31 63

1961

def @arg11 246 83 0 Y 32896 30 63

1962

def @arg12 246 83 0 Y 32896 30 63

1963

def @arg13 250 16777215 0 Y 0 31 8

1964

def @arg14 250 16777215 0 Y 0 31 8

1965

def @arg15 250 16777215 19 Y 0 31 8

1966

def @arg16 250 16777215 0 Y 0 31 8

1967

def @arg17 8 20 0 Y 32928 0 63

1968

def @arg18 8 20 0 Y 32896 0 63

1969

def @arg19 8 20 0 Y 32896 0 63

1970

def @arg20 250 16777215 0 Y 0 31 8

1971

def @arg21 250 16777215 0 Y 0 31 8

1972

def @arg22 250 16777215 0 Y 0 31 8

1973

def @arg23 250 16777215 0 Y 0 31 8

1974

def @arg24 250 16777215 0 Y 0 31 8

1975

def @arg25 250 16777215 0 Y 0 31 8

1976

def @arg26 250 16777215 0 Y 0 31 8

1977

def @arg27 250 16777215 0 Y 0 31 8

1978

def @arg28 250 16777215 0 Y 0 31 8

1979

def @arg29 250 16777215 0 Y 0 31 8

1980

def @arg30 250 16777215 0 Y 0 31 8

1981

def @arg31 250 16777215 0 Y 0 31 8

1982

def @arg32 250 16777215 0 Y 0 31 8

1983

1984

0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

1985

prepare stmt1 from "select

1986

@arg01:= c1, @arg02:= c2, @arg03:= c3, @arg04:= c4,

1987

@arg05:= c5, @arg06:= c6, @arg07:= c7, @arg08:= c8,

1988

@arg09:= c9, @arg10:= c10, @arg11:= c11, @arg12:= c12,

1989

@arg13:= c13, @arg14:= c14, @arg15:= c15, @arg16:= c16,

1990

@arg17:= c17, @arg18:= c18, @arg19:= c19, @arg20:= c20,

1991

@arg21:= c21, @arg22:= c22, @arg23:= c23, @arg24:= c24,

1992

@arg25:= c25, @arg26:= c26, @arg27:= c27, @arg28:= c28,

1993

@arg29:= c29, @arg30:= c30, @arg31:= c31, @arg32:= c32

1994

from t9 where c1= ?" ;

1995

set @my_key= 1 ;

1996

execute stmt1 using @my_key ;

1997

1998

1999

execute full_info ;

2000

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

2001

def @arg01 8 20 1 Y 32896 0 63

2002

def @arg02 8 20 1 Y 32896 0 63

2003

def @arg03 8 20 1 Y 32896 0 63

2004

def @arg04 8 20 1 Y 32896 0 63

2005

def @arg05 8 20 1 Y 32896 0 63

2006

def @arg06 8 20 1 Y 32896 0 63

2007

def @arg07 5 23 1 Y 32896 31 63

2008

def @arg08 5 23 1 Y 32896 31 63

2009

def @arg09 5 23 1 Y 32896 31 63

2010

def @arg10 5 23 1 Y 32896 31 63

2011

def @arg11 246 83 6 Y 32896 30 63

2012

def @arg12 246 83 6 Y 32896 30 63

2013

def @arg13 250 16777215 10 Y 0 31 8

2014

def @arg14 250 16777215 19 Y 0 31 8

2015

def @arg15 250 16777215 19 Y 0 31 8

2016

def @arg16 250 16777215 8 Y 0 31 8

2017

def @arg17 8 20 4 Y 32928 0 63

2018

def @arg18 8 20 1 Y 32896 0 63

2019

def @arg19 8 20 1 Y 32896 0 63

2020

def @arg20 250 16777215 1 Y 0 31 8

2021

def @arg21 250 16777215 10 Y 0 31 8

2022

def @arg22 250 16777215 30 Y 0 31 8

2023

def @arg23 250 16777215 8 Y 0 31 8

2024

def @arg24 250 16777215 8 Y 0 31 8

2025

def @arg25 250 16777215 4 Y 0 31 8

2026

def @arg26 250 16777215 4 Y 0 31 8

2027

def @arg27 250 16777215 10 Y 0 31 8

2028

def @arg28 250 16777215 10 Y 0 31 8

2029

def @arg29 250 16777215 8 Y 0 31 8

2030

def @arg30 250 16777215 8 Y 0 31 8

2031

def @arg31 250 16777215 3 Y 0 31 8

2032

def @arg32 250 16777215 6 Y 0 31 8

2033

2034

2035

set @my_key= 0 ;

2036

execute stmt1 using @my_key ;

2037

2038

0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2039

execute full_info ;

2040

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

2041

def @arg01 8 20 1 Y 32896 0 63

2042

def @arg02 8 20 0 Y 32896 0 63

2043

def @arg03 8 20 0 Y 32896 0 63

2044

def @arg04 8 20 0 Y 32896 0 63

2045

def @arg05 8 20 0 Y 32896 0 63

2046

def @arg06 8 20 0 Y 32896 0 63

2047

def @arg07 5 23 0 Y 32896 31 63

2048

def @arg08 5 23 0 Y 32896 31 63

2049

def @arg09 5 23 0 Y 32896 31 63

2050

def @arg10 5 23 0 Y 32896 31 63

2051

def @arg11 246 83 0 Y 32896 30 63

2052

def @arg12 246 83 0 Y 32896 30 63

2053

def @arg13 250 16777215 0 Y 0 31 8

2054

def @arg14 250 16777215 0 Y 0 31 8

2055

def @arg15 250 16777215 19 Y 0 31 8

2056

def @arg16 250 16777215 0 Y 0 31 8

2057

def @arg17 8 20 0 Y 32928 0 63

2058

def @arg18 8 20 0 Y 32896 0 63

2059

def @arg19 8 20 0 Y 32896 0 63

2060

def @arg20 250 16777215 0 Y 0 31 8

2061

def @arg21 250 16777215 0 Y 0 31 8

2062

def @arg22 250 16777215 0 Y 0 31 8

2063

def @arg23 250 16777215 0 Y 0 31 8

2064

def @arg24 250 16777215 0 Y 0 31 8

2065

def @arg25 250 16777215 0 Y 0 31 8

2066

def @arg26 250 16777215 0 Y 0 31 8

2067

def @arg27 250 16777215 0 Y 0 31 8

2068

def @arg28 250 16777215 0 Y 0 31 8

2069

def @arg29 250 16777215 0 Y 0 31 8

2070

def @arg30 250 16777215 0 Y 0 31 8

2071

def @arg31 250 16777215 0 Y 0 31 8

2072

def @arg32 250 16777215 0 Y 0 31 8

2073

2074

0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2075

prepare stmt1 from "select ? := c1 from t9 where c1= 1" ;

2076

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 ':= c1 from t9 where c1= 1' at line 1

2077

test_sequence

2078

------ select column, .. into @parm,.. ------

2079

select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,

2080

c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,

2081

c25, c26, c27, c28, c29, c30, c31, c32

2082

into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,

2083

@arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,

2084

@arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,

2085

@arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32

2086

from t9 where c1= 1 ;

2087

execute full_info ;

2088

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

2089

def @arg01 8 20 1 Y 32896 0 63

2090

def @arg02 8 20 1 Y 32896 0 63

2091

def @arg03 8 20 1 Y 32896 0 63

2092

def @arg04 8 20 1 Y 32896 0 63

2093

def @arg05 8 20 1 Y 32896 0 63

2094

def @arg06 8 20 1 Y 32896 0 63

2095

def @arg07 5 23 1 Y 32896 31 63

2096

def @arg08 5 23 1 Y 32896 31 63

2097

def @arg09 5 23 1 Y 32896 31 63

2098

def @arg10 5 23 1 Y 32896 31 63

2099

def @arg11 246 83 6 Y 32896 30 63

2100

def @arg12 246 83 6 Y 32896 30 63

2101

def @arg13 250 16777215 10 Y 0 31 8

2102

def @arg14 250 16777215 19 Y 0 31 8

2103

def @arg15 250 16777215 19 Y 0 31 8

2104

def @arg16 250 16777215 8 Y 0 31 8

2105

def @arg17 8 20 4 Y 32928 0 63

2106

def @arg18 8 20 1 Y 32896 0 63

2107

def @arg19 8 20 1 Y 32896 0 63

2108

def @arg20 250 16777215 1 Y 0 31 8

2109

def @arg21 250 16777215 10 Y 0 31 8

2110

def @arg22 250 16777215 30 Y 0 31 8

2111

def @arg23 250 16777215 8 Y 0 31 8

2112

def @arg24 250 16777215 8 Y 0 31 8

2113

def @arg25 250 16777215 4 Y 0 31 8

2114

def @arg26 250 16777215 4 Y 0 31 8

2115

def @arg27 250 16777215 10 Y 0 31 8

2116

def @arg28 250 16777215 10 Y 0 31 8

2117

def @arg29 250 16777215 8 Y 0 31 8

2118

def @arg30 250 16777215 8 Y 0 31 8

2119

def @arg31 250 16777215 3 Y 0 31 8

2120

def @arg32 250 16777215 6 Y 0 31 8

2121

2122

2123

select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,

2124

c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,

2125

c25, c26, c27, c28, c29, c30, c31, c32

2126

into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,

2127

@arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,

2128

@arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,

2129

@arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32

2130

from t9 where c1= 0 ;

2131

execute full_info ;

2132

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

2133

def @arg01 8 20 1 Y 32896 0 63

2134

def @arg02 8 20 0 Y 32896 0 63

2135

def @arg03 8 20 0 Y 32896 0 63

2136

def @arg04 8 20 0 Y 32896 0 63

2137

def @arg05 8 20 0 Y 32896 0 63

2138

def @arg06 8 20 0 Y 32896 0 63

2139

def @arg07 5 23 0 Y 32896 31 63

2140

def @arg08 5 23 0 Y 32896 31 63

2141

def @arg09 5 23 0 Y 32896 31 63

2142

def @arg10 5 23 0 Y 32896 31 63

2143

def @arg11 246 83 0 Y 32896 30 63

2144

def @arg12 246 83 0 Y 32896 30 63

2145

def @arg13 250 16777215 0 Y 0 31 8

2146

def @arg14 250 16777215 0 Y 0 31 8

2147

def @arg15 250 16777215 19 Y 0 31 8

2148

def @arg16 250 16777215 0 Y 0 31 8

2149

def @arg17 8 20 0 Y 32928 0 63

2150

def @arg18 8 20 0 Y 32896 0 63

2151

def @arg19 8 20 0 Y 32896 0 63

2152

def @arg20 250 16777215 0 Y 0 31 8

2153

def @arg21 250 16777215 0 Y 0 31 8

2154

def @arg22 250 16777215 0 Y 0 31 8

2155

def @arg23 250 16777215 0 Y 0 31 8

2156

def @arg24 250 16777215 0 Y 0 31 8

2157

def @arg25 250 16777215 0 Y 0 31 8

2158

def @arg26 250 16777215 0 Y 0 31 8

2159

def @arg27 250 16777215 0 Y 0 31 8

2160

def @arg28 250 16777215 0 Y 0 31 8

2161

def @arg29 250 16777215 0 Y 0 31 8

2162

def @arg30 250 16777215 0 Y 0 31 8

2163

def @arg31 250 16777215 0 Y 0 31 8

2164

def @arg32 250 16777215 0 Y 0 31 8

2165

2166

0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2167

prepare stmt1 from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,

2168

c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24,

2169

c25, c26, c27, c28, c29, c30, c31, c32

2170

into @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08,

2171

@arg09, @arg10, @arg11, @arg12, @arg13, @arg14, @arg15, @arg16,

2172

@arg17, @arg18, @arg19, @arg20, @arg21, @arg22, @arg23, @arg24,

2173

@arg25, @arg26, @arg27, @arg28, @arg29, @arg30, @arg31, @arg32

2174

from t9 where c1= ?" ;

2175

set @my_key= 1 ;

2176

execute stmt1 using @my_key ;

2177

execute full_info ;

2178

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

2179

def @arg01 8 20 1 Y 32896 0 63

2180

def @arg02 8 20 1 Y 32896 0 63

2181

def @arg03 8 20 1 Y 32896 0 63

2182

def @arg04 8 20 1 Y 32896 0 63

2183

def @arg05 8 20 1 Y 32896 0 63

2184

def @arg06 8 20 1 Y 32896 0 63

2185

def @arg07 5 23 1 Y 32896 31 63

2186

def @arg08 5 23 1 Y 32896 31 63

2187

def @arg09 5 23 1 Y 32896 31 63

2188

def @arg10 5 23 1 Y 32896 31 63

2189

def @arg11 246 83 6 Y 32896 30 63

2190

def @arg12 246 83 6 Y 32896 30 63

2191

def @arg13 250 16777215 10 Y 0 31 8

2192

def @arg14 250 16777215 19 Y 0 31 8

2193

def @arg15 250 16777215 19 Y 0 31 8

2194

def @arg16 250 16777215 8 Y 0 31 8

2195

def @arg17 8 20 4 Y 32928 0 63

2196

def @arg18 8 20 1 Y 32896 0 63

2197

def @arg19 8 20 1 Y 32896 0 63

2198

def @arg20 250 16777215 1 Y 0 31 8

2199

def @arg21 250 16777215 10 Y 0 31 8

2200

def @arg22 250 16777215 30 Y 0 31 8

2201

def @arg23 250 16777215 8 Y 0 31 8

2202

def @arg24 250 16777215 8 Y 0 31 8

2203

def @arg25 250 16777215 4 Y 0 31 8

2204

def @arg26 250 16777215 4 Y 0 31 8

2205

def @arg27 250 16777215 10 Y 0 31 8

2206

def @arg28 250 16777215 10 Y 0 31 8

2207

def @arg29 250 16777215 8 Y 0 31 8

2208

def @arg30 250 16777215 8 Y 0 31 8

2209

def @arg31 250 16777215 3 Y 0 31 8

2210

def @arg32 250 16777215 6 Y 0 31 8

2211

2212

2213

set @my_key= 0 ;

2214

execute stmt1 using @my_key ;

2215

execute full_info ;

2216

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

2217

def @arg01 8 20 1 Y 32896 0 63

2218

def @arg02 8 20 0 Y 32896 0 63

2219

def @arg03 8 20 0 Y 32896 0 63

2220

def @arg04 8 20 0 Y 32896 0 63

2221

def @arg05 8 20 0 Y 32896 0 63

2222

def @arg06 8 20 0 Y 32896 0 63

2223

def @arg07 5 23 0 Y 32896 31 63

2224

def @arg08 5 23 0 Y 32896 31 63

2225

def @arg09 5 23 0 Y 32896 31 63

2226

def @arg10 5 23 0 Y 32896 31 63

2227

def @arg11 246 83 0 Y 32896 30 63

2228

def @arg12 246 83 0 Y 32896 30 63

2229

def @arg13 250 16777215 0 Y 0 31 8

2230

def @arg14 250 16777215 0 Y 0 31 8

2231

def @arg15 250 16777215 19 Y 0 31 8

2232

def @arg16 250 16777215 0 Y 0 31 8

2233

def @arg17 8 20 0 Y 32928 0 63

2234

def @arg18 8 20 0 Y 32896 0 63

2235

def @arg19 8 20 0 Y 32896 0 63

2236

def @arg20 250 16777215 0 Y 0 31 8

2237

def @arg21 250 16777215 0 Y 0 31 8

2238

def @arg22 250 16777215 0 Y 0 31 8

2239

def @arg23 250 16777215 0 Y 0 31 8

2240

def @arg24 250 16777215 0 Y 0 31 8

2241

def @arg25 250 16777215 0 Y 0 31 8

2242

def @arg26 250 16777215 0 Y 0 31 8

2243

def @arg27 250 16777215 0 Y 0 31 8

2244

def @arg28 250 16777215 0 Y 0 31 8

2245

def @arg29 250 16777215 0 Y 0 31 8

2246

def @arg30 250 16777215 0 Y 0 31 8

2247

def @arg31 250 16777215 0 Y 0 31 8

2248

def @arg32 250 16777215 0 Y 0 31 8

2249

2250

0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1991-01-01 01:01:01 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2251

prepare stmt1 from "select c1 into ? from t9 where c1= 1" ;

2252

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 '? from t9 where c1= 1' at line 1

2253

test_sequence

2254

-- insert into numeric columns --

2255

insert into t9

2256

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2257

values

2258

( 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20 ) ;

2259

set @arg00= 21 ;

2260

insert into t9

2261

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2262

values

2263

( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2264

@arg00, @arg00, @arg00, @arg00, @arg00 ) ;

2265

prepare stmt1 from "insert into t9

2266

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2267

values

2268

( 22, 22, 22, 22, 22, 22, 22, 22, 22, 22, 22 )" ;

2269

execute stmt1 ;

2270

set @arg00= 23;

2271

prepare stmt2 from "insert into t9

2272

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2273

values

2274

( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;

2275

execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2276

@arg00, @arg00, @arg00, @arg00 ;

2277

insert into t9

2278

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2279

values

2280

( 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0, 30.0,

2281

30.0, 30.0, 30.0 ) ;

2282

set @arg00= 31.0 ;

2283

insert into t9

2284

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2285

values

2286

( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2287

@arg00, @arg00, @arg00, @arg00, @arg00 ) ;

2288

prepare stmt1 from "insert into t9

2289

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2290

values

2291

( 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0, 32.0,

2292

32.0, 32.0, 32.0 )" ;

2293

execute stmt1 ;

2294

set @arg00= 33.0;

2295

prepare stmt2 from "insert into t9

2296

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2297

values

2298

( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;

2299

execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2300

@arg00, @arg00, @arg00, @arg00 ;

2301

insert into t9

2302

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2303

values

2304

( '40', '40', '40', '40', '40', '40', '40', '40',

2305

'40', '40', '40' ) ;

2306

set @arg00= '41' ;

2307

insert into t9

2308

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2309

values

2310

( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2311

@arg00, @arg00, @arg00, @arg00, @arg00 ) ;

2312

prepare stmt1 from "insert into t9

2313

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2314

values

2315

( '42', '42', '42', '42', '42', '42', '42', '42',

2316

'42', '42', '42' )" ;

2317

execute stmt1 ;

2318

set @arg00= '43';

2319

prepare stmt2 from "insert into t9

2320

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2321

values

2322

( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;

2323

execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2324

@arg00, @arg00, @arg00, @arg00 ;

2325

insert into t9

2326

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2327

values

2328

( CAST('50' as binary), CAST('50' as binary),

2329

CAST('50' as binary), CAST('50' as binary), CAST('50' as binary),

2330

CAST('50' as binary), CAST('50' as binary), CAST('50' as binary),

2331

CAST('50' as binary), CAST('50' as binary), CAST('50' as binary) ) ;

2332

set @arg00= CAST('51' as binary) ;

2333

insert into t9

2334

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2335

values

2336

( @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2337

@arg00, @arg00, @arg00, @arg00, @arg00 ) ;

2338

prepare stmt1 from "insert into t9

2339

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2340

values

2341

( CAST('52' as binary), CAST('52' as binary),

2342

CAST('52' as binary), CAST('52' as binary), CAST('52' as binary),

2343

CAST('52' as binary), CAST('52' as binary), CAST('52' as binary),

2344

CAST('52' as binary), CAST('52' as binary), CAST('52' as binary) )" ;

2345

execute stmt1 ;

2346

set @arg00= CAST('53' as binary) ;

2347

prepare stmt2 from "insert into t9

2348

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2349

values

2350

( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;

2351

execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2352

@arg00, @arg00, @arg00, @arg00 ;

2353

set @arg00= 2 ;

2354

set @arg00= NULL ;

2355

insert into t9

2356

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2357

values

2358

( 60, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

2359

NULL, NULL, NULL ) ;

2360

insert into t9

2361

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2362

values

2363

( 61, @arg00, @arg00, @arg00, @arg00, @arg00,

2364

@arg00, @arg00, @arg00, @arg00, @arg00 ) ;

2365

prepare stmt1 from "insert into t9

2366

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2367

values

2368

( 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

2369

NULL, NULL, NULL )" ;

2370

execute stmt1 ;

2371

prepare stmt2 from "insert into t9

2372

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2373

values

2374

( 63, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;

2375

execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2376

@arg00, @arg00, @arg00, @arg00 ;

2377

set @arg00= 8.0 ;

2378

set @arg00= NULL ;

2379

insert into t9

2380

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2381

values

2382

( 71, @arg00, @arg00, @arg00, @arg00, @arg00,

2383

@arg00, @arg00, @arg00, @arg00, @arg00 ) ;

2384

prepare stmt2 from "insert into t9

2385

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2386

values

2387

( 73, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;

2388

execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2389

@arg00, @arg00, @arg00, @arg00 ;

2390

set @arg00= 'abc' ;

2391

set @arg00= NULL ;

2392

insert into t9

2393

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2394

values

2395

( 81, @arg00, @arg00, @arg00, @arg00, @arg00,

2396

@arg00, @arg00, @arg00, @arg00, @arg00 ) ;

2397

prepare stmt2 from "insert into t9

2398

( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2399

values

2400

( 83, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;

2401

execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2402

@arg00, @arg00, @arg00, @arg00 ;

2403

select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12

2404

from t9 where c1 >= 20

2405

order by c1 ;

2406

c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c12

2407

20 20 20 20 20 20 20 20 20 20 20.0000

2408

21 21 21 21 21 21 21 21 21 21 21.0000

2409

22 22 22 22 22 22 22 22 22 22 22.0000

2410

23 23 23 23 23 23 23 23 23 23 23.0000

2411

30 30 30 30 30 30 30 30 30 30 30.0000

2412

31 31 31 31 31 31 31 31 31 31 31.0000

2413

32 32 32 32 32 32 32 32 32 32 32.0000

2414

33 33 33 33 33 33 33 33 33 33 33.0000

2415

40 40 40 40 40 40 40 40 40 40 40.0000

2416

41 41 41 41 41 41 41 41 41 41 41.0000

2417

42 42 42 42 42 42 42 42 42 42 42.0000

2418

43 43 43 43 43 43 43 43 43 43 43.0000

2419

50 50 50 50 50 50 50 50 50 50 50.0000

2420

51 51 51 51 51 51 51 51 51 51 51.0000

2421

52 52 52 52 52 52 52 52 52 52 52.0000

2422

53 53 53 53 53 53 53 53 53 53 53.0000

2423

60 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2424

61 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2425

62 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2426

63 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2427

71 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2428

73 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2429

81 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2430

83 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2431

test_sequence

2432

-- select .. where numeric column = .. --

2433

set @arg00= 20;

2434

select 'true' as found from t9

2435

where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20

2436

and c8= 20 and c9= 20 and c10= 20 and c12= 20;

2437

found

2438

true

2439

select 'true' as found from t9

2440

where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00

2441

and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00

2442

and c12= @arg00;

2443

found

2444

true

2445

prepare stmt1 from "select 'true' as found from t9

2446

where c1= 20 and c2= 20 and c3= 20 and c4= 20 and c5= 20 and c6= 20 and c7= 20

2447

and c8= 20 and c9= 20 and c10= 20 and c12= 20 ";

2448

execute stmt1 ;

2449

found

2450

true

2451

prepare stmt1 from "select 'true' as found from t9

2452

where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?

2453

and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?

2454

and c12= ? ";

2455

execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2456

@arg00, @arg00, @arg00, @arg00 ;

2457

found

2458

true

2459

set @arg00= 20.0;

2460

select 'true' as found from t9

2461

where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0

2462

and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0;

2463

found

2464

true

2465

select 'true' as found from t9

2466

where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00

2467

and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00

2468

and c12= @arg00;

2469

found

2470

true

2471

prepare stmt1 from "select 'true' as found from t9

2472

where c1= 20.0 and c2= 20.0 and c3= 20.0 and c4= 20.0 and c5= 20.0 and c6= 20.0

2473

and c7= 20.0 and c8= 20.0 and c9= 20.0 and c10= 20.0 and c12= 20.0 ";

2474

execute stmt1 ;

2475

found

2476

true

2477

prepare stmt1 from "select 'true' as found from t9

2478

where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?

2479

and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?

2480

and c12= ? ";

2481

execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2482

@arg00, @arg00, @arg00, @arg00 ;

2483

found

2484

true

2485

select 'true' as found from t9

2486

where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'

2487

and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20';

2488

found

2489

true

2490

prepare stmt1 from "select 'true' as found from t9

2491

where c1= '20' and c2= '20' and c3= '20' and c4= '20' and c5= '20' and c6= '20'

2492

and c7= '20' and c8= '20' and c9= '20' and c10= '20' and c12= '20' ";

2493

execute stmt1 ;

2494

found

2495

true

2496

set @arg00= '20';

2497

select 'true' as found from t9

2498

where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00

2499

and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00

2500

and c12= @arg00;

2501

found

2502

true

2503

prepare stmt1 from "select 'true' as found from t9

2504

where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?

2505

and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?

2506

and c12= ? ";

2507

execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2508

@arg00, @arg00, @arg00, @arg00 ;

2509

found

2510

true

2511

select 'true' as found from t9

2512

where c1= CAST('20' as binary) and c2= CAST('20' as binary) and

2513

c3= CAST('20' as binary) and c4= CAST('20' as binary) and

2514

c5= CAST('20' as binary) and c6= CAST('20' as binary) and

2515

c7= CAST('20' as binary) and c8= CAST('20' as binary) and

2516

c9= CAST('20' as binary) and c10= CAST('20' as binary) and

2517

c12= CAST('20' as binary);

2518

found

2519

true

2520

prepare stmt1 from "select 'true' as found from t9

2521

where c1= CAST('20' as binary) and c2= CAST('20' as binary) and

2522

c3= CAST('20' as binary) and c4= CAST('20' as binary) and

2523

c5= CAST('20' as binary) and c6= CAST('20' as binary) and

2524

c7= CAST('20' as binary) and c8= CAST('20' as binary) and

2525

c9= CAST('20' as binary) and c10= CAST('20' as binary) and

2526

c12= CAST('20' as binary) ";

2527

execute stmt1 ;

2528

found

2529

true

2530

set @arg00= CAST('20' as binary) ;

2531

select 'true' as found from t9

2532

where c1= @arg00 and c2= @arg00 and c3= @arg00 and c4= @arg00 and c5= @arg00

2533

and c6= @arg00 and c7= @arg00 and c8= @arg00 and c9= @arg00 and c10= @arg00

2534

and c12= @arg00;

2535

found

2536

true

2537

prepare stmt1 from "select 'true' as found from t9

2538

where c1= ? and c2= ? and c3= ? and c4= ? and c5= ?

2539

and c6= ? and c7= ? and c8= ? and c9= ? and c10= ?

2540

and c12= ? ";

2541

execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2542

@arg00, @arg00, @arg00, @arg00 ;

2543

found

2544

true

2545

delete from t9 ;

2546

test_sequence

2547

-- some numeric overflow experiments --

2548

prepare my_insert from "insert into t9

2549

( c21, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )

2550

values

2551

( 'O', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;

2552

prepare my_select from "select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12

2553

from t9 where c21 = 'O' ";

2554

prepare my_delete from "delete from t9 where c21 = 'O' ";

2555

set @arg00= 9223372036854775807 ;

2556

execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2557

@arg00, @arg00, @arg00, @arg00, @arg00 ;

2558

Warnings:

2559

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

2560

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

2561

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

2562

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

2563

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

2564

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

2565

execute my_select ;

2566

c1 127

2567

c2 32767

2568

c3 8388607

2569

c4 2147483647

2570

c5 2147483647

2571

c6 9223372036854775807

2572

c7 9.22337e18

2573

c8 9.223372036854776e18

2574

c9 9.223372036854776e18

2575

c10 9.223372036854776e18

2576

c12 9999.9999

2577

execute my_delete ;

2578

set @arg00= '9223372036854775807' ;

2579

execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2580

@arg00, @arg00, @arg00, @arg00, @arg00 ;

2581

Warnings:

2582

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

2583

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

2584

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

2585

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

2586

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

2587

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

2588

execute my_select ;

2589

c1 127

2590

c2 32767

2591

c3 8388607

2592

c4 2147483647

2593

c5 2147483647

2594

c6 9223372036854775807

2595

c7 9.22337e18

2596

c8 9.223372036854776e18

2597

c9 9.223372036854776e18

2598

c10 9.223372036854776e18

2599

c12 9999.9999

2600

execute my_delete ;

2601

set @arg00= -9223372036854775808 ;

2602

execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2603

@arg00, @arg00, @arg00, @arg00, @arg00 ;

2604

Warnings:

2605

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

2606

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

2607

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

2608

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

2609

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

2610

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

2611

execute my_select ;

2612

c1 -128

2613

c2 -32768

2614

c3 -8388608

2615

c4 -2147483648

2616

c5 -2147483648

2617

c6 -9223372036854775808

2618

c7 -9.22337e18

2619

c8 -9.223372036854776e18

2620

c9 -9.223372036854776e18

2621

c10 -9.223372036854776e18

2622

c12 -9999.9999

2623

execute my_delete ;

2624

set @arg00= '-9223372036854775808' ;

2625

execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2626

@arg00, @arg00, @arg00, @arg00, @arg00 ;

2627

Warnings:

2628

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

2629

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

2630

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

2631

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

2632

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

2633

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

2634

execute my_select ;

2635

c1 -128

2636

c2 -32768

2637

c3 -8388608

2638

c4 -2147483648

2639

c5 -2147483648

2640

c6 -9223372036854775808

2641

c7 -9.22337e18

2642

c8 -9.223372036854776e18

2643

c9 -9.223372036854776e18

2644

c10 -9.223372036854776e18

2645

c12 -9999.9999

2646

execute my_delete ;

2647

set @arg00= 1.11111111111111111111e+50 ;

2648

execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2649

@arg00, @arg00, @arg00, @arg00, @arg00 ;

2650

Warnings:

2651

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

2652

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

2653

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

2654

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

2655

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

2656

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

2657

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

2658

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

2659

execute my_select ;

2660

c1 127

2661

c2 32767

2662

c3 8388607

2663

c4 2147483647

2664

c5 2147483647

2665

c6 9223372036854775807

2666

c7 3.40282e38

2667

c8 1.111111111111111e50

2668

c9 1.111111111111111e50

2669

c10 1.111111111111111e50

2670

c12 9999.9999

2671

execute my_delete ;

2672

set @arg00= '1.11111111111111111111e+50' ;

2673

execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2674

@arg00, @arg00, @arg00, @arg00, @arg00 ;

2675

Warnings:

2676

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

2677

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

2678

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

2679

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

2680

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

2681

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

2682

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

2683

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

2684

execute my_select ;

2685

c1 127

2686

c2 32767

2687

c3 8388607

2688

c4 2147483647

2689

c5 2147483647

2690

c6 9223372036854775807

2691

c7 3.40282e38

2692

c8 1.111111111111111e50

2693

c9 1.111111111111111e50

2694

c10 1.111111111111111e50

2695

c12 9999.9999

2696

execute my_delete ;

2697

set @arg00= -1.11111111111111111111e+50 ;

2698

execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2699

@arg00, @arg00, @arg00, @arg00, @arg00 ;

2700

Warnings:

2701

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

2702

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

2703

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

2704

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

2705

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

2706

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

2707

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

2708

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

2709

execute my_select ;

2710

c1 -128

2711

c2 -32768

2712

c3 -8388608

2713

c4 -2147483648

2714

c5 -2147483648

2715

c6 -9223372036854775808

2716

c7 -3.40282e38

2717

c8 -1.111111111111111e50

2718

c9 -1.111111111111111e50

2719

c10 -1.111111111111111e50

2720

c12 -9999.9999

2721

execute my_delete ;

2722

set @arg00= '-1.11111111111111111111e+50' ;

2723

execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2724

@arg00, @arg00, @arg00, @arg00, @arg00 ;

2725

Warnings:

2726

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

2727

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

2728

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

2729

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

2730

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

2731

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

2732

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

2733

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

2734

execute my_select ;

2735

c1 -128

2736

c2 -32768

2737

c3 -8388608

2738

c4 -2147483648

2739

c5 -2147483648

2740

c6 -9223372036854775808

2741

c7 -3.40282e38

2742

c8 -1.111111111111111e50

2743

c9 -1.111111111111111e50

2744

c10 -1.111111111111111e50

2745

c12 -9999.9999

2746

execute my_delete ;

2747

test_sequence

2748

-- insert into string columns --

2749

Warnings:

2750

Warning 1265 Data truncated for column 'c20' at row 1

2751

Warnings:

2752

Warning 1265 Data truncated for column 'c20' at row 1

2753

Warnings:

2754

Warning 1265 Data truncated for column 'c20' at row 1

2755

Warnings:

2756

Warning 1265 Data truncated for column 'c20' at row 1

2757

Warnings:

2758

Warning 1265 Data truncated for column 'c20' at row 1

2759

Warnings:

2760

Warning 1265 Data truncated for column 'c20' at row 1

2761

Warnings:

2762

Warning 1265 Data truncated for column 'c20' at row 1

2763

Warnings:

2764

Warning 1265 Data truncated for column 'c20' at row 1

2765

Warnings:

2766

Warning 1265 Data truncated for column 'c20' at row 1

2767

Warnings:

2768

Warning 1265 Data truncated for column 'c20' at row 1

2769

Warnings:

2770

Warning 1265 Data truncated for column 'c20' at row 1

2771

Warnings:

2772

Warning 1265 Data truncated for column 'c20' at row 1

2773

Warnings:

2774

Warning 1265 Data truncated for column 'c20' at row 1

2775

Warnings:

2776

Warning 1265 Data truncated for column 'c20' at row 1

2777

Warnings:

2778

Warning 1265 Data truncated for column 'c20' at row 1

2779

Warnings:

2780

Warning 1265 Data truncated for column 'c20' at row 1

2781

Warnings:

2782

Warning 1265 Data truncated for column 'c20' at row 1

2783

Warnings:

2784

Warning 1265 Data truncated for column 'c20' at row 1

2785

Warnings:

2786

Warning 1265 Data truncated for column 'c20' at row 1

2787

Warnings:

2788

Warning 1265 Data truncated for column 'c20' at row 1

2789

select c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30

2790

from t9 where c1 >= 20

2791

order by c1 ;

2792

c1 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30

2793

20 2 20 20 20 20 20 20 20 20 20 20

2794

21 2 21 21 21 21 21 21 21 21 21 21

2795

22 2 22 22 22 22 22 22 22 22 22 22

2796

23 2 23 23 23 23 23 23 23 23 23 23

2797

30 3 30 30 30 30 30 30 30 30 30 30

2798

31 3 31 31 31 31 31 31 31 31 31 31

2799

32 3 32 32 32 32 32 32 32 32 32 32

2800

33 3 33 33 33 33 33 33 33 33 33 33

2801

40 4 40 40 40 40 40 40 40 40 40 40

2802

41 4 41 41 41 41 41 41 41 41 41 41

2803

42 4 42 42 42 42 42 42 42 42 42 42

2804

43 4 43 43 43 43 43 43 43 43 43 43

2805

50 5 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0 50.0

2806

51 5 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0 51.0

2807

52 5 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0 52.0

2808

53 5 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0 53.0

2809

54 5 54 54 54 54 54 54 54 54 54 54

2810

55 6 55 55 55 55 55 55 55 55 55 55

2811

56 6 56 56 56 56 56 56 56 56 56 56

2812

57 6 57 57 57 57 57 57 57 57 57 57

2813

60 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2814

61 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2815

62 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2816

63 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2817

71 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2818

73 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2819

81 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2820

83 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2821

test_sequence

2822

-- select .. where string column = .. --

2823

set @arg00= '20';

2824

select 'true' as found from t9

2825

where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and

2826

c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and

2827

c27= '20' and c28= '20' and c29= '20' and c30= '20' ;

2828

found

2829

true

2830

select 'true' as found from t9

2831

where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and

2832

c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and

2833

c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;

2834

found

2835

true

2836

prepare stmt1 from "select 'true' as found from t9

2837

where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and

2838

c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and

2839

c27= '20' and c28= '20' and c29= '20' and c30= '20'" ;

2840

execute stmt1 ;

2841

found

2842

true

2843

prepare stmt1 from "select 'true' as found from t9

2844

where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and

2845

c21= ? and c22= ? and c23= ? and c25= ? and

2846

c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;

2847

execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2848

@arg00, @arg00, @arg00, @arg00, @arg00 ;

2849

found

2850

true

2851

set @arg00= CAST('20' as binary);

2852

select 'true' as found from t9

2853

where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))

2854

= CAST('20' as binary) and c21= CAST('20' as binary)

2855

and c22= CAST('20' as binary) and c23= CAST('20' as binary) and

2856

c24= CAST('20' as binary) and c25= CAST('20' as binary) and

2857

c26= CAST('20' as binary) and c27= CAST('20' as binary) and

2858

c28= CAST('20' as binary) and c29= CAST('20' as binary) and

2859

c30= CAST('20' as binary) ;

2860

found

2861

true

2862

select 'true' as found from t9

2863

where c1= 20 and concat(c20,substr(@arg00,1+length(c20))) = @arg00 and

2864

c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and

2865

c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and

2866

c30= @arg00;

2867

found

2868

true

2869

prepare stmt1 from "select 'true' as found from t9

2870

where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))

2871

= CAST('20' as binary) and c21= CAST('20' as binary)

2872

and c22= CAST('20' as binary) and c23= CAST('20' as binary) and

2873

c24= CAST('20' as binary) and c25= CAST('20' as binary) and

2874

c26= CAST('20' as binary) and c27= CAST('20' as binary) and

2875

c28= CAST('20' as binary) and c29= CAST('20' as binary) and

2876

c30= CAST('20' as binary)" ;

2877

execute stmt1 ;

2878

found

2879

true

2880

prepare stmt1 from "select 'true' as found from t9

2881

where c1= 20 and concat(c20,substr(?,1+length(c20))) = ? and c21= ? and

2882

c22= ? and c23= ? and c25= ? and c26= ? and c27= ? and c28= ? and

2883

c29= ? and c30= ?";

2884

execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2885

@arg00, @arg00, @arg00, @arg00, @arg00 ;

2886

found

2887

true

2888

set @arg00= 20;

2889

select 'true' as found from t9

2890

where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and

2891

c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and

2892

c27= 20 and c28= 20 and c29= 20 and c30= 20 ;

2893

found

2894

true

2895

select 'true' as found from t9

2896

where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and

2897

c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and

2898

c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;

2899

found

2900

true

2901

prepare stmt1 from "select 'true' as found from t9

2902

where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and

2903

c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and

2904

c27= 20 and c28= 20 and c29= 20 and c30= 20" ;

2905

execute stmt1 ;

2906

found

2907

true

2908

prepare stmt1 from "select 'true' as found from t9

2909

where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and

2910

c21= ? and c22= ? and c23= ? and c25= ? and

2911

c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;

2912

execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2913

@arg00, @arg00, @arg00, @arg00, @arg00 ;

2914

found

2915

true

2916

set @arg00= 20.0;

2917

select 'true' as found from t9

2918

where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and

2919

c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and

2920

c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0 ;

2921

found

2922

true

2923

select 'true' as found from t9

2924

where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and

2925

c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and

2926

c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;

2927

found

2928

true

2929

prepare stmt1 from "select 'true' as found from t9

2930

where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and

2931

c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and

2932

c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0" ;

2933

execute stmt1 ;

2934

found

2935

true

2936

prepare stmt1 from "select 'true' as found from t9

2937

where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and

2938

c21= ? and c22= ? and c23= ? and c25= ? and

2939

c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;

2940

execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,

2941

@arg00, @arg00, @arg00, @arg00, @arg00 ;

2942

found

2943

true

2944

delete from t9 ;

2945

test_sequence

2946

-- insert into date/time columns --

2947

Warnings:

2948

Note 1265 Data truncated for column 'c13' at row 1

2949

Warning 1265 Data truncated for column 'c17' at row 1

2950

Warnings:

2951

Note 1265 Data truncated for column 'c13' at row 1

2952

Warning 1265 Data truncated for column 'c17' at row 1

2953

Warnings:

2954

Note 1265 Data truncated for column 'c13' at row 1

2955

Warning 1265 Data truncated for column 'c17' at row 1

2956

Warnings:

2957

Note 1265 Data truncated for column 'c13' at row 1

2958

Warning 1265 Data truncated for column 'c17' at row 1

2959

Warnings:

2960

Note 1265 Data truncated for column 'c13' at row 1

2961

Warning 1265 Data truncated for column 'c17' at row 1

2962

Warnings:

2963

Note 1265 Data truncated for column 'c13' at row 1

2964

Warning 1265 Data truncated for column 'c17' at row 1

2965

Warnings:

2966

Note 1265 Data truncated for column 'c13' at row 1

2967

Warning 1265 Data truncated for column 'c17' at row 1

2968

Warnings:

2969

Note 1265 Data truncated for column 'c13' at row 1

2970

Warning 1265 Data truncated for column 'c17' at row 1

2971

Warnings:

2972

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

2973

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

2974

Warning 1265 Data truncated for column 'c15' at row 1

2975

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

2976

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

2977

Warnings:

2978

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

2979

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

2980

Warning 1265 Data truncated for column 'c15' at row 1

2981

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

2982

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

2983

Warnings:

2984

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

2985

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

2986

Warning 1265 Data truncated for column 'c15' at row 1

2987

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

2988

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

2989

Warnings:

2990

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

2991

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

2992

Warning 1265 Data truncated for column 'c15' at row 1

2993

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

2994

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

2995

Warnings:

2996

Warning 1265 Data truncated for column 'c15' at row 1

2997

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

2998

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

2999

Warnings:

3000

Warning 1265 Data truncated for column 'c15' at row 1

3001

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

3002

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

3003

Warnings:

3004

Warning 1265 Data truncated for column 'c15' at row 1

3005

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

3006

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

3007

Warnings:

3008

Warning 1265 Data truncated for column 'c15' at row 1

3009

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

3010

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

3011

select c1, c13, c14, c15, c16, c17 from t9 order by c1 ;

3012

c1 c13 c14 c15 c16 c17

3013

20 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3014

21 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3015

22 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3016

23 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3017

30 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3018

31 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3019

32 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3020

33 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3021

40 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3022

41 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3023

42 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3024

43 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3025

50 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3026

51 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3027

52 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3028

53 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3029

60 NULL NULL 1991-01-01 01:01:01 NULL NULL

3030

61 NULL NULL 1991-01-01 01:01:01 NULL NULL

3031

62 NULL NULL 1991-01-01 01:01:01 NULL NULL

3032

63 NULL NULL 1991-01-01 01:01:01 NULL NULL

3033

71 NULL NULL 1991-01-01 01:01:01 NULL NULL

3034

73 NULL NULL 1991-01-01 01:01:01 NULL NULL

3035

81 NULL NULL 1991-01-01 01:01:01 NULL NULL

3036

83 NULL NULL 1991-01-01 01:01:01 NULL NULL

3037

test_sequence

3038

-- select .. where date/time column = .. --

3039

set @arg00= '1991-01-01 01:01:01' ;

3040

select 'true' as found from t9

3041

where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and

3042

c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and

3043

c17= '1991-01-01 01:01:01' ;

3044

found

3045

true

3046

select 'true' as found from t9

3047

where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00

3048

and c17= @arg00 ;

3049

found

3050

true

3051

prepare stmt1 from "select 'true' as found from t9

3052

where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and

3053

c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and

3054

c17= '1991-01-01 01:01:01'" ;

3055

execute stmt1 ;

3056

found

3057

true

3058

prepare stmt1 from "select 'true' as found from t9

3059

where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;

3060

execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;

3061

found

3062

true

3063

set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;

3064

select 'true' as found from t9

3065

where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and

3066

c14= CAST('1991-01-01 01:01:01' as datetime) and

3067

c15= CAST('1991-01-01 01:01:01' as datetime) and

3068

c16= CAST('1991-01-01 01:01:01' as datetime) and

3069

c17= CAST('1991-01-01 01:01:01' as datetime) ;

3070

found

3071

true

3072

select 'true' as found from t9

3073

where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00

3074

and c17= @arg00 ;

3075

found

3076

true

3077

prepare stmt1 from "select 'true' as found from t9

3078

where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and

3079

c14= CAST('1991-01-01 01:01:01' as datetime) and

3080

c15= CAST('1991-01-01 01:01:01' as datetime) and

3081

c16= CAST('1991-01-01 01:01:01' as datetime) and

3082

c17= CAST('1991-01-01 01:01:01' as datetime)" ;

3083

execute stmt1 ;

3084

found

3085

true

3086

prepare stmt1 from "select 'true' as found from t9

3087

where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;

3088

execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;

3089

found

3090

true

3091

set @arg00= 1991 ;

3092

select 'true' as found from t9

3093

where c1= 20 and c17= 1991 ;

3094

found

3095

true

3096

select 'true' as found from t9

3097

where c1= 20 and c17= @arg00 ;

3098

found

3099

true

3100

prepare stmt1 from "select 'true' as found from t9

3101

where c1= 20 and c17= 1991" ;

3102

execute stmt1 ;

3103

found

3104

true

3105

prepare stmt1 from "select 'true' as found from t9

3106

where c1= 20 and c17= ?" ;

3107

execute stmt1 using @arg00 ;

3108

found

3109

true

3110

set @arg00= 1.991e+3 ;

3111

select 'true' as found from t9

3112

where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;

3113

found

3114

true

3115

select 'true' as found from t9

3116

where c1= 20 and abs(c17 - @arg00) < 0.01 ;

3117

found

3118

true

3119

prepare stmt1 from "select 'true' as found from t9

3120

where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;

3121

execute stmt1 ;

3122

found

3123

true

3124

prepare stmt1 from "select 'true' as found from t9

3125

where c1= 20 and abs(c17 - ?) < 0.01" ;

3126

execute stmt1 using @arg00 ;

3127

found

3128

true

3129

drop table t1, t9;