~vkolesnikov/pbxt/pbxt-1.5-23.06.2011

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

1 one

100

set @arg00='lion' ;

101

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

102

@arg00 b

103

lion one

104

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

105

execute stmt1 using @arg00 ;

106

? b

107

lion one

108

set @arg00=NULL ;

109

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

110

@arg00 b

111

NULL one

112

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

113

execute stmt1 using @arg00 ;

114

? b

115

NULL one

116

set @arg00=1 ;

117

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

118

b a - @arg00

119

one 0

120

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

121

execute stmt1 using @arg00 ;

122

b a - ?

123

one 0

124

set @arg00=null ;

125

select @arg00 as my_col ;

126

my_col

127

NULL

128

prepare stmt1 from ' select ? as my_col';

129

execute stmt1 using @arg00 ;

130

my_col

131

NULL

132

select @arg00 + 1 as my_col ;

133

my_col

134

NULL

135

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

136

execute stmt1 using @arg00 ;

137

my_col

138

NULL

139

select 1 + @arg00 as my_col ;

140

my_col

141

NULL

142

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

143

execute stmt1 using @arg00 ;

144

my_col

145

NULL

146

set @arg00='MySQL' ;

147

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

148

substr(@arg00,1,2)

149

150

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

151

execute stmt1 using @arg00 ;

152

substr(?,1,2)

153

154

set @arg00=3 ;

155

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

156

substr('MySQL',@arg00,5)

157

SQL

158

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

159

execute stmt1 using @arg00 ;

160

substr('MySQL',?,5)

161

SQL

162

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

163

substr('MySQL',1,@arg00)

164

MyS

165

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

166

execute stmt1 using @arg00 ;

167

substr('MySQL',1,?)

168

MyS

169

set @arg00='MySQL' ;

170

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

171

a concat(@arg00,b)

172

1 MySQLone

173

2 MySQLtwo

174

3 MySQLthree

175

4 MySQLfour

176

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

177

execute stmt1 using @arg00;

178

a concat(?,b)

179

1 MySQLone

180

2 MySQLtwo

181

3 MySQLthree

182

4 MySQLfour

183

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

184

a concat(b,@arg00)

185

1 oneMySQL

186

2 twoMySQL

187

3 threeMySQL

188

4 fourMySQL

189

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

190

execute stmt1 using @arg00;

191

a concat(b,?)

192

1 oneMySQL

193

2 twoMySQL

194

3 threeMySQL

195

4 fourMySQL

196

set @arg00='MySQL' ;

197

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

198

group by 'a' ;

199

group_concat(@arg00,b order by a)

200

MySQLone,MySQLtwo,MySQLthree,MySQLfour

201

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

202

group by ''a'' ' ;

203

execute stmt1 using @arg00;

204

group_concat(?,b order by a)

205

MySQLone,MySQLtwo,MySQLthree,MySQLfour

206

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

207

group by 'a' ;

208

group_concat(b,@arg00 order by a)

209

oneMySQL,twoMySQL,threeMySQL,fourMySQL

210

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

211

group by ''a'' ' ;

212

execute stmt1 using @arg00;

213

group_concat(b,? order by a)

214

oneMySQL,twoMySQL,threeMySQL,fourMySQL

215

set @arg00='first' ;

216

set @arg01='second' ;

217

set @arg02=NULL;

218

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

219

@arg00 @arg01

220

first second

221

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

222

execute stmt1 using @arg00, @arg01 ;

223

? ?

224

first second

225

execute stmt1 using @arg02, @arg01 ;

226

? ?

227

NULL second

228

execute stmt1 using @arg00, @arg02 ;

229

? ?

230

first NULL

231

execute stmt1 using @arg02, @arg02 ;

232

? ?

233

NULL NULL

234

drop table if exists t5 ;

235

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

236

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

237

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

238

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

239

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

240

set @arg00=1 ;

241

set @arg01='hh' ;

242

execute stmt1 using @arg00, @arg01 ;

243

id1 value1

244

1 hh

245

1 ii

246

2 hh

247

drop table t5 ;

248

drop table if exists t5 ;

249

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

250

insert into t5 values ('abc') ;

251

prepare stmt1 from ' select * from t5

252

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

253

set @arg00='abc' ;

254

execute stmt1 using @arg00 ;

255

session_id

256

set @arg00='1111' ;

257

execute stmt1 using @arg00 ;

258

session_id

259

abc

260

set @arg00='abc' ;

261

execute stmt1 using @arg00 ;

262

session_id

263

drop table t5 ;

264

set @arg00='FROM' ;

265

select a @arg00 t1 where a=1 ;

266

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

267

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

268

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

269

set @arg00='t1' ;

270

select a from @arg00 where a=1 ;

271

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

272

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

273

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

274

set @arg00='WHERE' ;

275

select a from t1 @arg00 a=1 ;

276

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

277

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

278

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

279

set @arg00=1 ;

280

select a FROM t1 where a=@arg00 ;

281

282

283

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

284

execute stmt1 using @arg00 ;

285

286

287

set @arg00=1000 ;

288

execute stmt1 using @arg00 ;

289

290

set @arg00=NULL ;

291

select a FROM t1 where a=@arg00 ;

292

293

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

294

execute stmt1 using @arg00 ;

295

296

set @arg00=4 ;

297

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

298

299

300

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

301

execute stmt1 using @arg00 ;

302

303

304

set @arg00=NULL ;

305

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

306

307

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

308

execute stmt1 using @arg00 ;

309

310

set @arg00=2 ;

311

set @arg01=3 ;

312

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

313

314

315

316

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

317

execute stmt1 using @arg00, @arg01;

318

319

320

321

set @arg00= 'one' ;

322

set @arg01= 'two' ;

323

set @arg02= 'five' ;

324

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

325

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

326

327

one

328

two

329

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

330

set @arg00='two' ;

331

execute stmt1 using @arg00 ;

332

333

two

334

set @arg00='tw%' ;

335

execute stmt1 using @arg00 ;

336

337

two

338

set @arg00='%wo' ;

339

execute stmt1 using @arg00 ;

340

341

two

342

set @arg00=null ;

343

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

344

select c5 from t9 where c5 > NULL ;

345

346

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

347

execute stmt1 using @arg00 ;

348

349

select c5 from t9 where c5 < NULL ;

350

351

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

352

execute stmt1 using @arg00 ;

353

354

select c5 from t9 where c5 = NULL ;

355

356

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

357

execute stmt1 using @arg00 ;

358

359

select c5 from t9 where c5 <=> NULL ;

360

361

NULL

362

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

363

execute stmt1 using @arg00 ;

364

365

NULL

366

delete from t9 where c1= 0 ;

367

set @arg00='>' ;

368

select a FROM t1 where a @arg00 1 ;

369

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

370

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

371

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

372

set @arg00=1 ;

373

select a,b FROM t1 where a is not NULL

374

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

375

a b

376

1 one

377

2 two

378

3 three

379

4 four

380

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

381

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

382

execute stmt1 using @arg00 ;

383

a b

384

1 one

385

2 two

386

3 three

387

4 four

388

set @arg00='two' ;

389

select a,b FROM t1 where a is not NULL

390

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

391

a b

392

1 one

393

3 three

394

4 four

395

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

396

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

397

execute stmt1 using @arg00 ;

398

a b

399

1 one

400

3 three

401

4 four

402

set @arg00=1 ;

403

select a,b FROM t1 where a is not NULL

404

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

405

a b

406

1 one

407

2 two

408

3 three

409

4 four

410

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

411

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

412

execute stmt1 using @arg00 ;

413

a b

414

1 one

415

2 two

416

3 three

417

4 four

418

set @arg00=2 ;

419

select a,b from t1 order by 2 ;

420

a b

421

4 four

422

1 one

423

3 three

424

2 two

425

prepare stmt1 from ' select a,b from t1

426

order by ? ';

427

execute stmt1 using @arg00;

428

a b

429

4 four

430

1 one

431

3 three

432

2 two

433

set @arg00=1 ;

434

execute stmt1 using @arg00;

435

a b

436

1 one

437

2 two

438

3 three

439

4 four

440

set @arg00=0 ;

441

execute stmt1 using @arg00;

442

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

443

set @arg00=1;

444

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

445

limit 1 ';

446

execute stmt1 ;

447

a b

448

1 one

449

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

450

execute stmt1 using @arg00;

451

a b

452

1 one

453

set @arg00='b' ;

454

set @arg01=0 ;

455

set @arg02=2 ;

456

set @arg03=2 ;

457

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

458

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

459

having sum(a) <> @arg03 ;

460

sum(a) @arg00

461

3 b

462

1 b

463

4 b

464

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

465

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

466

having sum(a) <> ? ';

467

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

468

sum(a) ?

469

3 b

470

1 b

471

4 b

472

test_sequence

473

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

474

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

475

from t1 first, t1 second

476

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

477

a1 a2

478

1 1

479

2 2

480

3 3

481

4 4

482

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

483

from t1 first, t1 second

484

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

485

execute stmt1 ;

486

a1 a2

487

1 1

488

2 2

489

3 3

490

4 4

491

set @arg00='ABC';

492

set @arg01='two';

493

set @arg02='one';

494

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

495

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

496

order by second.a, first.a;

497

a @arg00 a

498

1 ABC 1

499

2 ABC 1

500

3 ABC 1

501

4 ABC 1

502

2 ABC 2

503

2 ABC 3

504

3 ABC 3

505

2 ABC 4

506

4 ABC 4

507

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

508

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

509

order by second.a, first.a';

510

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

511

a ? a

512

1 ABC 1

513

2 ABC 1

514

3 ABC 1

515

4 ABC 1

516

2 ABC 2

517

2 ABC 3

518

3 ABC 3

519

2 ABC 4

520

4 ABC 4

521

drop table if exists t2 ;

522

create table t2 as select * from t1 ;

523

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

524

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

525

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

526

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

527

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

528

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

529

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

530

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

531

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

532

the join statement is:

533

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

534

prepare stmt1 from @query9 ;

535

execute stmt1 ;

536

a b b

537

1 one one

538

2 two two

539

3 three three

540

4 four four

541

execute stmt1 ;

542

a b b

543

1 one one

544

2 two two

545

3 three three

546

4 four four

547

execute stmt1 ;

548

a b b

549

1 one one

550

2 two two

551

3 three three

552

4 four four

553

the join statement is:

554

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

555

prepare stmt1 from @query8 ;

556

execute stmt1 ;

557

a b

558

1 one

559

2 two

560

3 three

561

4 four

562

execute stmt1 ;

563

a b

564

1 one

565

2 two

566

3 three

567

4 four

568

execute stmt1 ;

569

a b

570

1 one

571

2 two

572

3 three

573

4 four

574

the join statement is:

575

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

576

prepare stmt1 from @query7 ;

577

execute stmt1 ;

578

a b a b

579

1 one 1 one

580

2 two 2 two

581

3 three 3 three

582

4 four 4 four

583

execute stmt1 ;

584

a b a b

585

1 one 1 one

586

2 two 2 two

587

3 three 3 three

588

4 four 4 four

589

execute stmt1 ;

590

a b a b

591

1 one 1 one

592

2 two 2 two

593

3 three 3 three

594

4 four 4 four

595

the join statement is:

596

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

597

prepare stmt1 from @query6 ;

598

execute stmt1 ;

599

a b b

600

1 one one

601

2 two two

602

3 three three

603

4 four four

604

execute stmt1 ;

605

a b b

606

1 one one

607

2 two two

608

3 three three

609

4 four four

610

execute stmt1 ;

611

a b b

612

1 one one

613

2 two two

614

3 three three

615

4 four four

616

the join statement is:

617

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

618

prepare stmt1 from @query5 ;

619

execute stmt1 ;

620

a b

621

1 one

622

2 two

623

3 three

624

4 four

625

execute stmt1 ;

626

a b

627

1 one

628

2 two

629

3 three

630

4 four

631

execute stmt1 ;

632

a b

633

1 one

634

2 two

635

3 three

636

4 four

637

the join statement is:

638

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

639

prepare stmt1 from @query4 ;

640

execute stmt1 ;

641

a b a b

642

1 one 1 one

643

2 two 2 two

644

3 three 3 three

645

4 four 4 four

646

execute stmt1 ;

647

a b a b

648

1 one 1 one

649

2 two 2 two

650

3 three 3 three

651

4 four 4 four

652

execute stmt1 ;

653

a b a b

654

1 one 1 one

655

2 two 2 two

656

3 three 3 three

657

4 four 4 four

658

the join statement is:

659

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

660

prepare stmt1 from @query3 ;

661

execute stmt1 ;

662

a b b

663

1 one one

664

2 two two

665

3 three three

666

4 four four

667

execute stmt1 ;

668

a b b

669

1 one one

670

2 two two

671

3 three three

672

4 four four

673

execute stmt1 ;

674

a b b

675

1 one one

676

2 two two

677

3 three three

678

4 four four

679

the join statement is:

680

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

681

prepare stmt1 from @query2 ;

682

execute stmt1 ;

683

a b

684

1 one

685

2 two

686

3 three

687

4 four

688

execute stmt1 ;

689

a b

690

1 one

691

2 two

692

3 three

693

4 four

694

execute stmt1 ;

695

a b

696

1 one

697

2 two

698

3 three

699

4 four

700

the join statement is:

701

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

702

prepare stmt1 from @query1 ;

703

execute stmt1 ;

704

a b a b

705

1 one 1 one

706

2 two 2 two

707

3 three 3 three

708

4 four 4 four

709

execute stmt1 ;

710

a b a b

711

1 one 1 one

712

2 two 2 two

713

3 three 3 three

714

4 four 4 four

715

execute stmt1 ;

716

a b a b

717

1 one 1 one

718

2 two 2 two

719

3 three 3 three

720

4 four 4 four

721

drop table t2 ;

722

test_sequence

723

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

724

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

725

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

726

execute stmt1 ;

727

a b

728

2 two

729

set @arg00='two' ;

730

select a, b FROM t1 outer_table where

731

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

732

a b

733

2 two

734

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

735

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

736

execute stmt1 using @arg00;

737

a b

738

2 two

739

set @arg00='two' ;

740

select a, b FROM t1 outer_table where

741

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

742

a b

743

2 two

744

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

745

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

746

execute stmt1 using @arg00;

747

a b

748

2 two

749

set @arg00=3 ;

750

set @arg01='three' ;

751

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

752

a b

753

3 three

754

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

755

756

757

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

758

execute stmt1 using @arg00, @arg01;

759

760

761

set @arg00=1 ;

762

set @arg01='two' ;

763

set @arg02=2 ;

764

set @arg03='two' ;

765

select a, @arg00, b FROM t1 outer_table where

766

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

767

a @arg00 b

768

2 1 two

769

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

770

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

771

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

772

a ? b

773

2 1 two

774

prepare stmt1 from 'select c4 FROM t9 where

775

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

776

execute stmt1 using @arg01, @arg02;

777

778

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

779

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

780

execute stmt1 ;

781

a b

782

1 one

783

2 two

784

3 three

785

4 four

786

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

787

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

788

execute stmt1 ;

789

ccc

790

791

deallocate prepare stmt1 ;

792

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

793

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

794

execute stmt1 ;

795

ccc

796

797

deallocate prepare stmt1 ;

798

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

799

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

800

execute stmt1 ;

801

ccc

802

803

deallocate prepare stmt1 ;

804

set @arg00='two' ;

805

select a, b FROM t1 outer_table where

806

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

807

a b

808

2 two

809

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

810

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

811

execute stmt1 using @arg00;

812

a b

813

2 two

814

set @arg00=2 ;

815

select a, b FROM t1 outer_table where

816

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

817

a b

818

2 two

819

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

820

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

821

execute stmt1 using @arg00;

822

a b

823

2 two

824

set @arg00=2 ;

825

select a, b FROM t1 outer_table where

826

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

827

a b

828

2 two

829

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

830

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

831

execute stmt1 using @arg00;

832

a b

833

2 two

834

set @arg00=1 ;

835

set @arg01='two' ;

836

set @arg02=2 ;

837

set @arg03='two' ;

838

select a, @arg00, b FROM t1 outer_table where

839

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

840

and outer_table.a=a ) ;

841

a @arg00 b

842

2 1 two

843

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

844

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

845

and outer_table.a=a ) ' ;

846

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

847

a ? b

848

2 1 two

849

set @arg00=1 ;

850

set @arg01=0 ;

851

select a, @arg00

852

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

853

where a=@arg01;

854

a @arg00

855

0 1

856

prepare stmt1 from ' select a, ?

857

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

858

where a=? ';

859

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

860

a ?

861

0 1

862

drop table if exists t2 ;

863

create table t2 as select * from t1;

864

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

865

execute stmt1 ;

866

a in (select a from t2)

867

868

869

870

871

drop table if exists t5, t6, t7 ;

872

create table t5 (a int , b int) ;

873

create table t6 like t5 ;

874

create table t7 like t5 ;

875

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

876

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

877

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

878

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

879

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

880

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

881

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

882

execute stmt1 ;

883

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

884

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

885

group by t5.a order by sum limit 1)

886

3 1

887

2 2

888

1 2

889

execute stmt1 ;

890

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

891

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

892

group by t5.a order by sum limit 1)

893

3 1

894

2 2

895

1 2

896

execute stmt1 ;

897

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

898

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

899

group by t5.a order by sum limit 1)

900

3 1

901

2 2

902

1 2

903

drop table t5, t6, t7 ;

904

drop table if exists t2 ;

905

create table t2 as select * from t9;

906

set @stmt= ' SELECT

907

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

908

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

909

GROUP BY t9.c15 LIMIT 1) as scalar_s,

910

exists (select 1.0e+0 from t2

911

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

912

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

913

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

914

FROM t9,

915

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

916

prepare stmt1 from @stmt ;

917

execute stmt1 ;

918

execute stmt1 ;

919

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

920

prepare stmt1 from @stmt ;

921

execute stmt1 ;

922

execute stmt1 ;

923

set @stmt= ' SELECT

924

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

925

GROUP BY t9.c15 LIMIT 1) as scalar_s,

926

exists (select ? from t2

927

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

928

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

929

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

930

FROM t9,

931

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

932

set @arg00= 0.0 ;

933

set @arg01= 0e-3 ;

934

set @arg02= 1.0e+0 ;

935

set @arg03= 9.0000000000 ;

936

set @arg04= 4 ;

937

set @arg05= 0.3e+1 ;

938

set @arg06= 4 ;

939

set @arg07= 4 ;

940

set @arg08= 4.0 ;

941

set @arg09= 40e-1 ;

942

prepare stmt1 from @stmt ;

943

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

944

@arg07, @arg08, @arg09 ;

945

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

946

@arg07, @arg08, @arg09 ;

947

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

948

prepare stmt1 from @stmt ;

949

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

950

@arg07, @arg08, @arg09 ;

951

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

952

@arg07, @arg08, @arg09 ;

953

drop table t2 ;

954

select 1 < (select a from t1) ;

955

ERROR 21000: Subquery returns more than 1 row

956

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

957

execute stmt1 ;

958

ERROR 21000: Subquery returns more than 1 row

959

select 1 as my_col ;

960

my_col

961

962

test_sequence

963

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

964

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

965

union distinct

966

select a FROM t1 where a=1 ';

967

execute stmt1 ;

968

969

970

execute stmt1 ;

971

972

973

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

974

union all

975

select a FROM t1 where a=1 ';

976

execute stmt1 ;

977

978

979

980

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

981

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

982

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

983

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

984

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

985

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

986

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

987

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

988

set @arg00=1 ;

989

select @arg00 FROM t1 where a=1

990

union distinct

991

select 1 FROM t1 where a=1;

992

@arg00

993

994

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

995

union distinct

996

select 1 FROM t1 where a=1 ' ;

997

execute stmt1 using @arg00;

998

999

1000

set @arg00=1 ;

1001

select 1 FROM t1 where a=1

1002

union distinct

1003

select @arg00 FROM t1 where a=1;

1004

1005

1006

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

1007

union distinct

1008

select ? FROM t1 where a=1 ' ;

1009

execute stmt1 using @arg00;

1010

1011

1012

set @arg00='a' ;

1013

select @arg00 FROM t1 where a=1

1014

union distinct

1015

select @arg00 FROM t1 where a=1;

1016

@arg00

1017

1018

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

1019

union distinct

1020

select ? FROM t1 where a=1 ';

1021

execute stmt1 using @arg00, @arg00;

1022

1023

1024

prepare stmt1 from ' select ?

1025

union distinct

1026

select ? ';

1027

execute stmt1 using @arg00, @arg00;

1028

1029

1030

set @arg00='a' ;

1031

set @arg01=1 ;

1032

set @arg02='a' ;

1033

set @arg03=2 ;

1034

select @arg00 FROM t1 where a=@arg01

1035

union distinct

1036

select @arg02 FROM t1 where a=@arg03;

1037

@arg00

1038

1039

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

1040

union distinct

1041

select ? FROM t1 where a=? ' ;

1042

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

1043

1044

1045

set @arg00=1 ;

1046

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

1047

union distinct

1048

select sum(a) + 200, 1 from t1

1049

group by b ' ;

1050

execute stmt1 using @arg00;

1051

sum(a) + 200 ?

1052

210 1

1053

204 1

1054

201 1

1055

203 1

1056

202 1

1057

set @Oporto='Oporto' ;

1058

set @Lisboa='Lisboa' ;

1059

set @0=0 ;

1060

set @1=1 ;

1061

set @2=2 ;

1062

set @3=3 ;

1063

set @4=4 ;

1064

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

1065

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

1066

Oporto Lisboa 0 1 2 3 4

1067

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

1068

group by b

1069

union distinct

1070

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

1071

group by b ;

1072

the_sum the_town

1073

204 Oporto

1074

201 Oporto

1075

203 Oporto

1076

202 Oporto

1077

204 Lisboa

1078

201 Lisboa

1079

203 Lisboa

1080

202 Lisboa

1081

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

1082

group by b

1083

union distinct

1084

select sum(a) + 200, ? from t1

1085

group by b ' ;

1086

execute stmt1 using @Oporto, @Lisboa;

1087

the_sum the_town

1088

204 Oporto

1089

201 Oporto

1090

203 Oporto

1091

202 Oporto

1092

204 Lisboa

1093

201 Lisboa

1094

203 Lisboa

1095

202 Lisboa

1096

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

1097

where a > @1

1098

group by b

1099

union distinct

1100

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

1101

where a > @2

1102

group by b ;

1103

the_sum the_town

1104

204 Oporto

1105

203 Oporto

1106

202 Oporto

1107

204 Lisboa

1108

203 Lisboa

1109

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

1110

where a > ?

1111

group by b

1112

union distinct

1113

select sum(a) + 200, ? from t1

1114

where a > ?

1115

group by b ' ;

1116

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

1117

the_sum the_town

1118

204 Oporto

1119

203 Oporto

1120

202 Oporto

1121

204 Lisboa

1122

203 Lisboa

1123

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

1124

where a > @1

1125

group by b

1126

having avg(a) > @2

1127

union distinct

1128

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

1129

where a > @2

1130

group by b

1131

having avg(a) > @3;

1132

the_sum the_town

1133

204 Oporto

1134

203 Oporto

1135

204 Lisboa

1136

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

1137

where a > ?

1138

group by b

1139

having avg(a) > ?

1140

union distinct

1141

select sum(a) + 200, ? from t1

1142

where a > ?

1143

group by b

1144

having avg(a) > ? ';

1145

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

1146

the_sum the_town

1147

204 Oporto

1148

203 Oporto

1149

204 Lisboa

1150

test_sequence

1151

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

1152

prepare stmt1 from ' explain select * from t9 ' ;

1153

execute stmt1;

1154

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

1155

def id 8 3 1 N 32929 0 63

1156

def select_type 253 19 6 N 1 31 8

1157

def table 253 64 2 Y 0 31 8

1158

def type 253 10 3 Y 0 31 8

1159

def possible_keys 253 4096 0 Y 0 31 8

1160

def key 253 64 0 Y 0 31 8

1161

def key_len 253 4096 0 Y 0 31 8

1162

def ref 253 1024 0 Y 0 31 8

1163

def rows 8 10 1 Y 32928 0 63

1164

def Extra 253 255 0 N 1 31 8

1165

id select_type table type possible_keys key key_len ref rows Extra

1166

1 SIMPLE t9 ALL NULL NULL NULL NULL 2

1167

drop table if exists t2 ;

1168

create table t2 (s varchar(25), fulltext(s))

1169

ENGINE = 'MYISAM' ;

1170

insert into t2 values ('Gravedigger'), ('Greed'),('Hollow Dogs') ;

1171

commit ;

1172

prepare stmt1 from ' select s from t2 where match (s) against (?) ' ;

1173

set @arg00='Dogs' ;

1174

execute stmt1 using @arg00 ;

1175

1176

Hollow Dogs

1177

prepare stmt1 from ' SELECT s FROM t2

1178

where match (s) against (concat(?,''digger'')) ';

1179

set @arg00='Grave' ;

1180

execute stmt1 using @arg00 ;

1181

1182

Gravedigger

1183

drop table t2 ;

1184

test_sequence

1185

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

1186

delete from t1 ;

1187

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

1188

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

1189

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

1190

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

1191

commit ;

1192

delete from t9 ;

1193

insert into t9

1194

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

1195

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

1196

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

1197

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

1198

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

1199

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

1200

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

1201

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

1202

insert into t9

1203

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

1204

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

1205

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

1206

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

1207

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

1208

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

1209

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

1210

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

1211

commit ;

1212

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

1213

execute stmt1;

1214

select a,b from t1 where a=2;

1215

a b

1216

execute stmt1;

1217

insert into t1 values(0,NULL);

1218

set @arg00=NULL;

1219

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

1220

execute stmt1 using @arg00;

1221

select a,b from t1 where b is NULL ;

1222

a b

1223

0 NULL

1224

set @arg00='one';

1225

execute stmt1 using @arg00;

1226

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

1227

a b

1228

prepare stmt1 from 'truncate table t1' ;

1229

test_sequence

1230

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

1231

delete from t1 ;

1232

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

1233

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

1234

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

1235

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

1236

commit ;

1237

delete from t9 ;

1238

insert into t9

1239

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

1240

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

1241

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

1242

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

1243

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

1244

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

1245

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

1246

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

1247

insert into t9

1248

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

1249

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

1250

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

1251

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

1252

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

1253

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

1254

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

1255

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

1256

commit ;

1257

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

1258

execute stmt1;

1259

select a,b from t1 where a=2;

1260

a b

1261

2 a=two

1262

execute stmt1;

1263

select a,b from t1 where a=2;

1264

a b

1265

2 a=two

1266

set @arg00=NULL;

1267

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

1268

execute stmt1 using @arg00;

1269

select a,b from t1 where a=2;

1270

a b

1271

2 NULL

1272

set @arg00='two';

1273

execute stmt1 using @arg00;

1274

select a,b from t1 where a=2;

1275

a b

1276

2 two

1277

set @arg00=2;

1278

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

1279

execute stmt1 using @arg00;

1280

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

1281

a b

1282

2 NULL

1283

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

1284

set @arg00=2000;

1285

execute stmt1 using @arg00;

1286

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

1287

a b

1288

set @arg00=2;

1289

set @arg01=22;

1290

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

1291

execute stmt1 using @arg00, @arg00;

1292

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

1293

a b

1294

2 two

1295

execute stmt1 using @arg01, @arg00;

1296

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

1297

a b

1298

22 two

1299

execute stmt1 using @arg00, @arg01;

1300

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

1301

a b

1302

2 two

1303

set @arg00=NULL;

1304

set @arg01=2;

1305

execute stmt1 using @arg00, @arg01;

1306

Warnings:

1307

Warning 1048 Column 'a' cannot be null

1308

select a,b from t1 order by a;

1309

a b

1310

0 two

1311

1 one

1312

3 three

1313

4 four

1314

set @arg00=0;

1315

execute stmt1 using @arg01, @arg00;

1316

select a,b from t1 order by a;

1317

a b

1318

1 one

1319

2 two

1320

3 three

1321

4 four

1322

set @arg00=23;

1323

set @arg01='two';

1324

set @arg02=2;

1325

set @arg03='two';

1326

set @arg04=2;

1327

drop table if exists t2;

1328

create table t2 as select a,b from t1 ;

1329

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

1330

and a in (select ? from t2

1331

where b = ? or a = ?)';

1332

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

1333

affected rows: 1

1334

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

1335

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

1336

a b

1337

23 two

1338

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

1339

and a not in (select ? from t2

1340

where b = ? or a = ?)';

1341

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

1342

affected rows: 1

1343

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

1344

select a,b from t1 order by a ;

1345

a b

1346

1 one

1347

2 two

1348

3 three

1349

4 four

1350

drop table t2 ;

1351

create table t2

1352

(

1353

a int, b varchar(30),

1354

primary key(a)

1355

) engine = 'MYISAM' ;

1356

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

1357

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

1358

and a in (select ? from t2

1359

where b = ? or a = ?)';

1360

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

1361

affected rows: 1

1362

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

1363

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

1364

a b

1365

23 two

1366

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

1367

and a not in (select ? from t2

1368

where b = ? or a = ?)';

1369

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

1370

affected rows: 1

1371

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

1372

select a,b from t1 order by a ;

1373

a b

1374

1 one

1375

2 two

1376

3 three

1377

4 four

1378

drop table t2 ;

1379

set @arg00=1;

1380

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

1381

where a=2

1382

limit 1';

1383

execute stmt1 ;

1384

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

1385

a b

1386

2 bla

1387

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

1388

execute stmt1 using @arg00;

1389

test_sequence

1390

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

1391

delete from t1 ;

1392

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

1393

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

1394

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

1395

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

1396

commit ;

1397

delete from t9 ;

1398

insert into t9

1399

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

1400

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

1401

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

1402

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

1403

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

1404

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

1405

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

1406

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

1407

insert into t9

1408

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

1409

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

1410

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

1411

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

1412

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

1413

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

1414

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

1415

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

1416

commit ;

1417

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

1418

execute stmt1;

1419

select a,b from t1 where a = 5;

1420

a b

1421

5 five

1422

set @arg00='six' ;

1423

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

1424

execute stmt1 using @arg00;

1425

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

1426

a b

1427

6 six

1428

execute stmt1 using @arg00;

1429

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

1430

set @arg00=NULL ;

1431

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

1432

execute stmt1 using @arg00;

1433

select a,b from t1 where b is NULL;

1434

a b

1435

0 NULL

1436

set @arg00=8 ;

1437

set @arg01='eight' ;

1438

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

1439

execute stmt1 using @arg00, @arg01 ;

1440

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

1441

a b

1442

8 eight

1443

set @NULL= null ;

1444

set @arg00= 'abc' ;

1445

execute stmt1 using @NULL, @NULL ;

1446

ERROR 23000: Column 'a' cannot be null

1447

execute stmt1 using @NULL, @NULL ;

1448

ERROR 23000: Column 'a' cannot be null

1449

execute stmt1 using @NULL, @arg00 ;

1450

ERROR 23000: Column 'a' cannot be null

1451

execute stmt1 using @NULL, @arg00 ;

1452

ERROR 23000: Column 'a' cannot be null

1453

set @arg01= 10000 + 2 ;

1454

execute stmt1 using @arg01, @arg00 ;

1455

set @arg01= 10000 + 1 ;

1456

execute stmt1 using @arg01, @arg00 ;

1457

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

1458

a b

1459

10001 abc

1460

10002 abc

1461

delete from t1 where a > 10000 ;

1462

set @arg01= 10000 + 2 ;

1463

execute stmt1 using @arg01, @NULL ;

1464

set @arg01= 10000 + 1 ;

1465

execute stmt1 using @arg01, @NULL ;

1466

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

1467

a b

1468

10001 NULL

1469

10002 NULL

1470

delete from t1 where a > 10000 ;

1471

set @arg01= 10000 + 10 ;

1472

execute stmt1 using @arg01, @arg01 ;

1473

set @arg01= 10000 + 9 ;

1474

execute stmt1 using @arg01, @arg01 ;

1475

set @arg01= 10000 + 8 ;

1476

execute stmt1 using @arg01, @arg01 ;

1477

set @arg01= 10000 + 7 ;

1478

execute stmt1 using @arg01, @arg01 ;

1479

set @arg01= 10000 + 6 ;

1480

execute stmt1 using @arg01, @arg01 ;

1481

set @arg01= 10000 + 5 ;

1482

execute stmt1 using @arg01, @arg01 ;

1483

set @arg01= 10000 + 4 ;

1484

execute stmt1 using @arg01, @arg01 ;

1485

set @arg01= 10000 + 3 ;

1486

execute stmt1 using @arg01, @arg01 ;

1487

set @arg01= 10000 + 2 ;

1488

execute stmt1 using @arg01, @arg01 ;

1489

set @arg01= 10000 + 1 ;

1490

execute stmt1 using @arg01, @arg01 ;

1491

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

1492

a b

1493

10001 10001

1494

10002 10002

1495

10003 10003

1496

10004 10004

1497

10005 10005

1498

10006 10006

1499

10007 10007

1500

10008 10008

1501

10009 10009

1502

10010 10010

1503

delete from t1 where a > 10000 ;

1504

set @arg00=81 ;

1505

set @arg01='8-1' ;

1506

set @arg02=82 ;

1507

set @arg03='8-2' ;

1508

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

1509

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

1510

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

1511

a b

1512

81 8-1

1513

82 8-2

1514

set @arg00=9 ;

1515

set @arg01='nine' ;

1516

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

1517

execute stmt1 using @arg00, @arg01 ;

1518

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

1519

a b

1520

9 nine

1521

set @arg00=6 ;

1522

set @arg01=1 ;

1523

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

1524

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

1525

execute stmt1 using @arg00, @arg01;

1526

select * from t1 order by a;

1527

a b

1528

0 NULL

1529

1 one

1530

2 two

1531

3 three

1532

4 four

1533

5 five

1534

7 sixmodified

1535

8 eight

1536

9 nine

1537

81 8-1

1538

82 8-2

1539

set @arg00=81 ;

1540

set @arg01=1 ;

1541

execute stmt1 using @arg00, @arg01;

1542

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

1543

drop table if exists t2 ;

1544

create table t2 (id int auto_increment primary key)

1545

ENGINE= 'MYISAM' ;

1546

prepare stmt1 from ' select last_insert_id() ' ;

1547

insert into t2 values (NULL) ;

1548

execute stmt1 ;

1549

last_insert_id()

1550

1551

insert into t2 values (NULL) ;

1552

execute stmt1 ;

1553

last_insert_id()

1554

1555

drop table t2 ;

1556

set @1000=1000 ;

1557

set @x1000_2="x1000_2" ;

1558

set @x1000_3="x1000_3" ;

1559

set @x1000="x1000" ;

1560

set @1100=1100 ;

1561

set @x1100="x1100" ;

1562

set @100=100 ;

1563

set @updated="updated" ;

1564

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

1565

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

1566

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

1567

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

1568

a b

1569

1000 x1000_3

1570

1100 x1000_1updated

1571

delete from t1 where a >= 1000 ;

1572

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

1573

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

1574

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

1575

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

1576

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

1577

a b

1578

1000 x1000_3

1579

1100 x1000_1updated

1580

delete from t1 where a >= 1000 ;

1581

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

1582

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

1583

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

1584

a b

1585

1200 x1000_1updatedupdated

1586

delete from t1 where a >= 1000 ;

1587

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

1588

execute stmt1;

1589

execute stmt1;

1590

execute stmt1;

1591

test_sequence

1592

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

1593

delete from t1 ;

1594

delete from t9 ;

1595

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

1596

insert into t9 (c1,c21)

1597

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

1598

prepare stmt_delete from " delete t1, t9

1599

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

1600

prepare stmt_update from " update t1, t9

1601

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

1602

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

1603

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

1604

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

1605

set @arg00= 1 ;

1606

execute stmt_update using @arg00 ;

1607

execute stmt_delete ;

1608

execute stmt_select1 ;

1609

a b

1610

2 two

1611

3 three

1612

execute stmt_select2 ;

1613

c1 c21

1614

2 two

1615

3 three

1616

set @arg00= @arg00 + 1 ;

1617

execute stmt_update using @arg00 ;

1618

execute stmt_delete ;

1619

execute stmt_select1 ;

1620

a b

1621

3 three

1622

execute stmt_select2 ;

1623

c1 c21

1624

3 three

1625

set @arg00= @arg00 + 1 ;

1626

execute stmt_update using @arg00 ;

1627

execute stmt_delete ;

1628

execute stmt_select1 ;

1629

a b

1630

execute stmt_select2 ;

1631

c1 c21

1632

set @arg00= @arg00 + 1 ;

1633

delete from t1 ;

1634

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

1635

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

1636

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

1637

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

1638

commit ;

1639

delete from t9 ;

1640

insert into t9

1641

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

1642

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

1643

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

1644

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

1645

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

1646

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

1647

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

1648

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

1649

insert into t9

1650

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

1651

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

1652

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

1653

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

1654

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

1655

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

1656

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

1657

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

1658

commit ;

1659

insert into t1 values(0,NULL) ;

1660

set @duplicate='duplicate ' ;

1661

set @1000=1000 ;

1662

set @5=5 ;

1663

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

1664

a b

1665

0 NULL

1666

1 one

1667

2 two

1668

3 three

1669

4 four

1670

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

1671

where a < @5 ;

1672

affected rows: 5

1673

info: Records: 5 Duplicates: 0 Warnings: 0

1674

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

1675

a b

1676

1000 NULL

1677

1001 duplicate one

1678

1002 duplicate two

1679

1003 duplicate three

1680

1004 duplicate four

1681

delete from t1 where a >= 1000 ;

1682

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

1683

where a < ? ' ;

1684

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

1685

affected rows: 5

1686

info: Records: 5 Duplicates: 0 Warnings: 0

1687

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

1688

a b

1689

1000 NULL

1690

1001 duplicate one

1691

1002 duplicate two

1692

1003 duplicate three

1693

1004 duplicate four

1694

delete from t1 where a >= 1000 ;

1695

set @1=1 ;

1696

set @2=2 ;

1697

set @100=100 ;

1698

set @float=1.00;

1699

set @five='five' ;

1700

drop table if exists t2;

1701

create table t2 like t1 ;

1702

insert into t2 (b,a)

1703

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

1704

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

1705

and second.b <> @five

1706

group by second.b

1707

having sum(second.a) > @2

1708

union

1709

select b, a + @100 from t1

1710

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

1711

from t1);

1712

affected rows: 3

1713

info: Records: 3 Duplicates: 0 Warnings: 0

1714

select a,b from t2 order by a ;

1715

a b

1716

3 duplicate

1717

4 duplicate

1718

103 three

1719

delete from t2 ;

1720

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

1721

select ?, sum(first.a)

1722

from t1 first, t1 second

1723

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

1724

group by second.b

1725

having sum(second.a) > ?

1726

union

1727

select b, a + ? from t1

1728

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

1729

from t1 ) ' ;

1730

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

1731

affected rows: 3

1732

info: Records: 3 Duplicates: 0 Warnings: 0

1733

select a,b from t2 order by a ;

1734

a b

1735

3 duplicate

1736

4 duplicate

1737

103 three

1738

drop table t2;

1739

drop table if exists t5 ;

1740

set @arg01= 8;

1741

set @arg02= 8.0;

1742

set @arg03= 80.00000000000e-1;

1743

set @arg04= 'abc' ;

1744

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

1745

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

1746

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

1747

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

1748

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

1749

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

1750

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

1751

set @arg12= 8 ;

1752

set @arg12= NULL ;

1753

set @arg13= 8.0 ;

1754

set @arg13= NULL ;

1755

set @arg14= 'abc';

1756

set @arg14= NULL ;

1757

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

1758

set @arg15= NULL ;

1759

create table t5 as select

1760

8 as const01, @arg01 as param01,

1761

8.0 as const02, @arg02 as param02,

1762

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

1763

'abc' as const04, @arg04 as param04,

1764

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

1765

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

1766

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

1767

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

1768

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

1769

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

1770

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

1771

NULL as const12, @arg12 as param12,

1772

@arg13 as param13,

1773

@arg14 as param14,

1774

@arg15 as param15;

1775

show create table t5 ;

1776

Table Create Table

1777

t5 CREATE TABLE `t5` (

1778

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

1779

`param01` bigint(20) DEFAULT NULL,

1780

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

1781

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

1782

`const03` double NOT NULL DEFAULT '0',

1783

`param03` double DEFAULT NULL,

1784

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

1785

`param04` longtext,

1786

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

1787

`param05` longblob,

1788

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

1789

`param06` longtext,

1790

`const07` date DEFAULT NULL,

1791

`param07` longtext,

1792

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

1793

`param08` longtext,

1794

`const09` datetime DEFAULT NULL,

1795

`param09` longtext,

1796

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

1797

`param10` bigint(20) DEFAULT NULL,

1798

`const11` int(4) DEFAULT NULL,

1799

`param11` bigint(20) DEFAULT NULL,

1800

`const12` binary(0) DEFAULT NULL,

1801

`param12` bigint(20) DEFAULT NULL,

1802

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

1803

`param14` longtext,

1804

`param15` longblob

1805

) ENGINE=PBXT DEFAULT CHARSET=latin1

1806

select * from t5 ;

1807

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

1808

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

1809

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

1810

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

1811

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

1812

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

1813

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

1814

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

1815

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

1816

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

1817

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

1818

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

1819

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

1820

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

1821

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

1822

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

1823

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

1824

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

1825

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

1826

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

1827

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

1828

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

1829

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

1830

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

1831

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

1832

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

1833

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

1834

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

1835

const01 8

1836

param01 8

1837

const02 8.0

1838

param02 8.000000000000000000000000000000

1839

const03 8

1840

param03 8

1841

const04 abc

1842

param04 abc

1843

const05 abc

1844

param05 abc

1845

const06 1991-08-05

1846

param06 1991-08-05

1847

const07 1991-08-05

1848

param07 1991-08-05

1849

const08 1991-08-05 01:01:01

1850

param08 1991-08-05 01:01:01

1851

const09 1991-08-05 01:01:01

1852

param09 1991-08-05 01:01:01

1853

const10 662680861

1854

param10 662680861

1855

const11 1991

1856

param11 1991

1857

const12 NULL

1858

param12 NULL

1859

param13 NULL

1860

param14 NULL

1861

param15 NULL

1862

drop table t5 ;

1863

test_sequence

1864

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

1865

delete from t1 ;

1866

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

1867

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

1868

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

1869

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

1870

commit ;

1871

delete from t9 ;

1872

insert into t9

1873

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

1874

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

1875

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

1876

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

1877

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

1878

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

1879

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

1880

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

1881

insert into t9

1882

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

1883

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

1884

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

1885

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

1886

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

1887

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

1888

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

1889

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

1890

commit ;

1891

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

1892

select * from t9 order by c1 ;

1893

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

1894

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

1895

1896

1897

test_sequence

1898

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

1899

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

1900

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

1901

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

1902

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

1903

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

1904

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

1905

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

1906

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

1907

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

1908

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

1909

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

1910

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

1911

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

1912

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

1913

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

1914

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

1915

from t9 where c1= 1 ;

1916

@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

1917

1918

execute full_info ;

1919

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

1920

def @arg01 8 20 1 Y 32896 0 63

1921

def @arg02 8 20 1 Y 32896 0 63

1922

def @arg03 8 20 1 Y 32896 0 63

1923

def @arg04 8 20 1 Y 32896 0 63

1924

def @arg05 8 20 1 Y 32896 0 63

1925

def @arg06 8 20 1 Y 32896 0 63

1926

def @arg07 5 23 1 Y 32896 31 63

1927

def @arg08 5 23 1 Y 32896 31 63

1928

def @arg09 5 23 1 Y 32896 31 63

1929

def @arg10 5 23 1 Y 32896 31 63

1930

def @arg11 246 83 6 Y 32896 30 63

1931

def @arg12 246 83 6 Y 32896 30 63

1932

def @arg13 250 16777215 10 Y 0 31 8

1933

def @arg14 250 16777215 19 Y 0 31 8

1934

def @arg15 250 16777215 19 Y 0 31 8

1935

def @arg16 250 16777215 8 Y 0 31 8

1936

def @arg17 8 20 4 Y 32928 0 63

1937

def @arg18 8 20 1 Y 32896 0 63

1938

def @arg19 8 20 1 Y 32896 0 63

1939

def @arg20 250 16777215 1 Y 0 31 8

1940

def @arg21 250 16777215 10 Y 0 31 8

1941

def @arg22 250 16777215 30 Y 0 31 8

1942

def @arg23 250 16777215 8 Y 128 31 63

1943

def @arg24 250 16777215 8 Y 0 31 8

1944

def @arg25 250 16777215 4 Y 128 31 63

1945

def @arg26 250 16777215 4 Y 0 31 8

1946

def @arg27 250 16777215 10 Y 128 31 63

1947

def @arg28 250 16777215 10 Y 0 31 8

1948

def @arg29 250 16777215 8 Y 128 31 63

1949

def @arg30 250 16777215 8 Y 0 31 8

1950

def @arg31 250 16777215 3 Y 0 31 8

1951

def @arg32 250 16777215 6 Y 0 31 8

1952

@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

1953

1954

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

1955

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

1956

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

1957

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

1958

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

1959

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

1960

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

1961

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

1962

from t9 where c1= 0 ;

1963

1964

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

1965

execute full_info ;

1966

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

1967

def @arg01 8 20 1 Y 32896 0 63

1968

def @arg02 8 20 0 Y 32896 0 63

1969

def @arg03 8 20 0 Y 32896 0 63

1970

def @arg04 8 20 0 Y 32896 0 63

1971

def @arg05 8 20 0 Y 32896 0 63

1972

def @arg06 8 20 0 Y 32896 0 63

1973

def @arg07 5 23 0 Y 32896 31 63

1974

def @arg08 5 23 0 Y 32896 31 63

1975

def @arg09 5 23 0 Y 32896 31 63

1976

def @arg10 5 23 0 Y 32896 31 63

1977

def @arg11 246 83 0 Y 32896 30 63

1978

def @arg12 246 83 0 Y 32896 30 63

1979

def @arg13 250 16777215 0 Y 0 31 8

1980

def @arg14 250 16777215 0 Y 0 31 8

1981

def @arg15 250 16777215 19 Y 0 31 8

1982

def @arg16 250 16777215 0 Y 0 31 8

1983

def @arg17 8 20 0 Y 32928 0 63

1984

def @arg18 8 20 0 Y 32896 0 63

1985

def @arg19 8 20 0 Y 32896 0 63

1986

def @arg20 250 16777215 0 Y 0 31 8

1987

def @arg21 250 16777215 0 Y 0 31 8

1988

def @arg22 250 16777215 0 Y 0 31 8

1989

def @arg23 250 16777215 0 Y 128 31 63

1990

def @arg24 250 16777215 0 Y 0 31 8

1991

def @arg25 250 16777215 0 Y 128 31 63

1992

def @arg26 250 16777215 0 Y 0 31 8

1993

def @arg27 250 16777215 0 Y 128 31 63

1994

def @arg28 250 16777215 0 Y 0 31 8

1995

def @arg29 250 16777215 0 Y 128 31 63

1996

def @arg30 250 16777215 0 Y 0 31 8

1997

def @arg31 250 16777215 0 Y 0 31 8

1998

def @arg32 250 16777215 0 Y 0 31 8

1999

2000

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

2001

prepare stmt1 from "select

2002

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

2003

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

2004

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

2005

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

2006

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

2007

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

2008

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

2009

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

2010

from t9 where c1= ?" ;

2011

set @my_key= 1 ;

2012

execute stmt1 using @my_key ;

2013

2014

2015

execute full_info ;

2016

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

2017

def @arg01 8 20 1 Y 32896 0 63

2018

def @arg02 8 20 1 Y 32896 0 63

2019

def @arg03 8 20 1 Y 32896 0 63

2020

def @arg04 8 20 1 Y 32896 0 63

2021

def @arg05 8 20 1 Y 32896 0 63

2022

def @arg06 8 20 1 Y 32896 0 63

2023

def @arg07 5 23 1 Y 32896 31 63

2024

def @arg08 5 23 1 Y 32896 31 63

2025

def @arg09 5 23 1 Y 32896 31 63

2026

def @arg10 5 23 1 Y 32896 31 63

2027

def @arg11 246 83 6 Y 32896 30 63

2028

def @arg12 246 83 6 Y 32896 30 63

2029

def @arg13 250 16777215 10 Y 0 31 8

2030

def @arg14 250 16777215 19 Y 0 31 8

2031

def @arg15 250 16777215 19 Y 0 31 8

2032

def @arg16 250 16777215 8 Y 0 31 8

2033

def @arg17 8 20 4 Y 32928 0 63

2034

def @arg18 8 20 1 Y 32896 0 63

2035

def @arg19 8 20 1 Y 32896 0 63

2036

def @arg20 250 16777215 1 Y 0 31 8

2037

def @arg21 250 16777215 10 Y 0 31 8

2038

def @arg22 250 16777215 30 Y 0 31 8

2039

def @arg23 250 16777215 8 Y 128 31 63

2040

def @arg24 250 16777215 8 Y 0 31 8

2041

def @arg25 250 16777215 4 Y 128 31 63

2042

def @arg26 250 16777215 4 Y 0 31 8

2043

def @arg27 250 16777215 10 Y 128 31 63

2044

def @arg28 250 16777215 10 Y 0 31 8

2045

def @arg29 250 16777215 8 Y 128 31 63

2046

def @arg30 250 16777215 8 Y 0 31 8

2047

def @arg31 250 16777215 3 Y 0 31 8

2048

def @arg32 250 16777215 6 Y 0 31 8

2049

2050

2051

set @my_key= 0 ;

2052

execute stmt1 using @my_key ;

2053

2054

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

2055

execute full_info ;

2056

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

2057

def @arg01 8 20 1 Y 32896 0 63

2058

def @arg02 8 20 0 Y 32896 0 63

2059

def @arg03 8 20 0 Y 32896 0 63

2060

def @arg04 8 20 0 Y 32896 0 63

2061

def @arg05 8 20 0 Y 32896 0 63

2062

def @arg06 8 20 0 Y 32896 0 63

2063

def @arg07 5 23 0 Y 32896 31 63

2064

def @arg08 5 23 0 Y 32896 31 63

2065

def @arg09 5 23 0 Y 32896 31 63

2066

def @arg10 5 23 0 Y 32896 31 63

2067

def @arg11 246 83 0 Y 32896 30 63

2068

def @arg12 246 83 0 Y 32896 30 63

2069

def @arg13 250 16777215 0 Y 0 31 8

2070

def @arg14 250 16777215 0 Y 0 31 8

2071

def @arg15 250 16777215 19 Y 0 31 8

2072

def @arg16 250 16777215 0 Y 0 31 8

2073

def @arg17 8 20 0 Y 32928 0 63

2074

def @arg18 8 20 0 Y 32896 0 63

2075

def @arg19 8 20 0 Y 32896 0 63

2076

def @arg20 250 16777215 0 Y 0 31 8

2077

def @arg21 250 16777215 0 Y 0 31 8

2078

def @arg22 250 16777215 0 Y 0 31 8

2079

def @arg23 250 16777215 0 Y 128 31 63

2080

def @arg24 250 16777215 0 Y 0 31 8

2081

def @arg25 250 16777215 0 Y 128 31 63

2082

def @arg26 250 16777215 0 Y 0 31 8

2083

def @arg27 250 16777215 0 Y 128 31 63

2084

def @arg28 250 16777215 0 Y 0 31 8

2085

def @arg29 250 16777215 0 Y 128 31 63

2086

def @arg30 250 16777215 0 Y 0 31 8

2087

def @arg31 250 16777215 0 Y 0 31 8

2088

def @arg32 250 16777215 0 Y 0 31 8

2089

2090

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

2091

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

2092

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

2093

test_sequence

2094

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

2095

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

2096

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

2097

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

2098

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

2099

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

2100

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

2101

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

2102

from t9 where c1= 1 ;

2103

execute full_info ;

2104

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

2105

def @arg01 8 20 1 Y 32896 0 63

2106

def @arg02 8 20 1 Y 32896 0 63

2107

def @arg03 8 20 1 Y 32896 0 63

2108

def @arg04 8 20 1 Y 32896 0 63

2109

def @arg05 8 20 1 Y 32896 0 63

2110

def @arg06 8 20 1 Y 32896 0 63

2111

def @arg07 5 23 1 Y 32896 31 63

2112

def @arg08 5 23 1 Y 32896 31 63

2113

def @arg09 5 23 1 Y 32896 31 63

2114

def @arg10 5 23 1 Y 32896 31 63

2115

def @arg11 246 83 6 Y 32896 30 63

2116

def @arg12 246 83 6 Y 32896 30 63

2117

def @arg13 250 16777215 10 Y 0 31 8

2118

def @arg14 250 16777215 19 Y 0 31 8

2119

def @arg15 250 16777215 19 Y 0 31 8

2120

def @arg16 250 16777215 8 Y 0 31 8

2121

def @arg17 8 20 4 Y 32928 0 63

2122

def @arg18 8 20 1 Y 32896 0 63

2123

def @arg19 8 20 1 Y 32896 0 63

2124

def @arg20 250 16777215 1 Y 0 31 8

2125

def @arg21 250 16777215 10 Y 0 31 8

2126

def @arg22 250 16777215 30 Y 0 31 8

2127

def @arg23 250 16777215 8 Y 128 31 63

2128

def @arg24 250 16777215 8 Y 0 31 8

2129

def @arg25 250 16777215 4 Y 128 31 63

2130

def @arg26 250 16777215 4 Y 0 31 8

2131

def @arg27 250 16777215 10 Y 128 31 63

2132

def @arg28 250 16777215 10 Y 0 31 8

2133

def @arg29 250 16777215 8 Y 128 31 63

2134

def @arg30 250 16777215 8 Y 0 31 8

2135

def @arg31 250 16777215 3 Y 0 31 8

2136

def @arg32 250 16777215 6 Y 0 31 8

2137

2138

2139

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

2140

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

2141

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

2142

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

2143

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

2144

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

2145

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

2146

from t9 where c1= 0 ;

2147

execute full_info ;

2148

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

2149

def @arg01 8 20 1 Y 32896 0 63

2150

def @arg02 8 20 0 Y 32896 0 63

2151

def @arg03 8 20 0 Y 32896 0 63

2152

def @arg04 8 20 0 Y 32896 0 63

2153

def @arg05 8 20 0 Y 32896 0 63

2154

def @arg06 8 20 0 Y 32896 0 63

2155

def @arg07 5 23 0 Y 32896 31 63

2156

def @arg08 5 23 0 Y 32896 31 63

2157

def @arg09 5 23 0 Y 32896 31 63

2158

def @arg10 5 23 0 Y 32896 31 63

2159

def @arg11 246 83 0 Y 32896 30 63

2160

def @arg12 246 83 0 Y 32896 30 63

2161

def @arg13 250 16777215 0 Y 0 31 8

2162

def @arg14 250 16777215 0 Y 0 31 8

2163

def @arg15 250 16777215 19 Y 0 31 8

2164

def @arg16 250 16777215 0 Y 0 31 8

2165

def @arg17 8 20 0 Y 32928 0 63

2166

def @arg18 8 20 0 Y 32896 0 63

2167

def @arg19 8 20 0 Y 32896 0 63

2168

def @arg20 250 16777215 0 Y 0 31 8

2169

def @arg21 250 16777215 0 Y 0 31 8

2170

def @arg22 250 16777215 0 Y 0 31 8

2171

def @arg23 250 16777215 0 Y 128 31 63

2172

def @arg24 250 16777215 0 Y 0 31 8

2173

def @arg25 250 16777215 0 Y 128 31 63

2174

def @arg26 250 16777215 0 Y 0 31 8

2175

def @arg27 250 16777215 0 Y 128 31 63

2176

def @arg28 250 16777215 0 Y 0 31 8

2177

def @arg29 250 16777215 0 Y 128 31 63

2178

def @arg30 250 16777215 0 Y 0 31 8

2179

def @arg31 250 16777215 0 Y 0 31 8

2180

def @arg32 250 16777215 0 Y 0 31 8

2181

2182

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

2183

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

2184

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

2185

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

2186

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

2187

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

2188

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

2189

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

2190

from t9 where c1= ?" ;

2191

set @my_key= 1 ;

2192

execute stmt1 using @my_key ;

2193

execute full_info ;

2194

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

2195

def @arg01 8 20 1 Y 32896 0 63

2196

def @arg02 8 20 1 Y 32896 0 63

2197

def @arg03 8 20 1 Y 32896 0 63

2198

def @arg04 8 20 1 Y 32896 0 63

2199

def @arg05 8 20 1 Y 32896 0 63

2200

def @arg06 8 20 1 Y 32896 0 63

2201

def @arg07 5 23 1 Y 32896 31 63

2202

def @arg08 5 23 1 Y 32896 31 63

2203

def @arg09 5 23 1 Y 32896 31 63

2204

def @arg10 5 23 1 Y 32896 31 63

2205

def @arg11 246 83 6 Y 32896 30 63

2206

def @arg12 246 83 6 Y 32896 30 63

2207

def @arg13 250 16777215 10 Y 0 31 8

2208

def @arg14 250 16777215 19 Y 0 31 8

2209

def @arg15 250 16777215 19 Y 0 31 8

2210

def @arg16 250 16777215 8 Y 0 31 8

2211

def @arg17 8 20 4 Y 32928 0 63

2212

def @arg18 8 20 1 Y 32896 0 63

2213

def @arg19 8 20 1 Y 32896 0 63

2214

def @arg20 250 16777215 1 Y 0 31 8

2215

def @arg21 250 16777215 10 Y 0 31 8

2216

def @arg22 250 16777215 30 Y 0 31 8

2217

def @arg23 250 16777215 8 Y 128 31 63

2218

def @arg24 250 16777215 8 Y 0 31 8

2219

def @arg25 250 16777215 4 Y 128 31 63

2220

def @arg26 250 16777215 4 Y 0 31 8

2221

def @arg27 250 16777215 10 Y 128 31 63

2222

def @arg28 250 16777215 10 Y 0 31 8

2223

def @arg29 250 16777215 8 Y 128 31 63

2224

def @arg30 250 16777215 8 Y 0 31 8

2225

def @arg31 250 16777215 3 Y 0 31 8

2226

def @arg32 250 16777215 6 Y 0 31 8

2227

2228

2229

set @my_key= 0 ;

2230

execute stmt1 using @my_key ;

2231

execute full_info ;

2232

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

2233

def @arg01 8 20 1 Y 32896 0 63

2234

def @arg02 8 20 0 Y 32896 0 63

2235

def @arg03 8 20 0 Y 32896 0 63

2236

def @arg04 8 20 0 Y 32896 0 63

2237

def @arg05 8 20 0 Y 32896 0 63

2238

def @arg06 8 20 0 Y 32896 0 63

2239

def @arg07 5 23 0 Y 32896 31 63

2240

def @arg08 5 23 0 Y 32896 31 63

2241

def @arg09 5 23 0 Y 32896 31 63

2242

def @arg10 5 23 0 Y 32896 31 63

2243

def @arg11 246 83 0 Y 32896 30 63

2244

def @arg12 246 83 0 Y 32896 30 63

2245

def @arg13 250 16777215 0 Y 0 31 8

2246

def @arg14 250 16777215 0 Y 0 31 8

2247

def @arg15 250 16777215 19 Y 0 31 8

2248

def @arg16 250 16777215 0 Y 0 31 8

2249

def @arg17 8 20 0 Y 32928 0 63

2250

def @arg18 8 20 0 Y 32896 0 63

2251

def @arg19 8 20 0 Y 32896 0 63

2252

def @arg20 250 16777215 0 Y 0 31 8

2253

def @arg21 250 16777215 0 Y 0 31 8

2254

def @arg22 250 16777215 0 Y 0 31 8

2255

def @arg23 250 16777215 0 Y 128 31 63

2256

def @arg24 250 16777215 0 Y 0 31 8

2257

def @arg25 250 16777215 0 Y 128 31 63

2258

def @arg26 250 16777215 0 Y 0 31 8

2259

def @arg27 250 16777215 0 Y 128 31 63

2260

def @arg28 250 16777215 0 Y 0 31 8

2261

def @arg29 250 16777215 0 Y 128 31 63

2262

def @arg30 250 16777215 0 Y 0 31 8

2263

def @arg31 250 16777215 0 Y 0 31 8

2264

def @arg32 250 16777215 0 Y 0 31 8

2265

2266

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

2267

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

2268

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

2269

test_sequence

2270

-- insert into numeric columns --

2271

insert into t9

2272

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

2273

values

2274

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

2275

set @arg00= 21 ;

2276

insert into t9

2277

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

2278

values

2279

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

2280

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

2281

prepare stmt1 from "insert into t9

2282

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

2283

values

2284

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

2285

execute stmt1 ;

2286

set @arg00= 23;

2287

prepare stmt2 from "insert into t9

2288

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

2289

values

2290

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

2291

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

2292

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

2293

insert into t9

2294

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

2295

values

2296

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

2297

30.0, 30.0, 30.0 ) ;

2298

set @arg00= 31.0 ;

2299

insert into t9

2300

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

2301

values

2302

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

2303

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

2304

prepare stmt1 from "insert into t9

2305

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

2306

values

2307

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

2308

32.0, 32.0, 32.0 )" ;

2309

execute stmt1 ;

2310

set @arg00= 33.0;

2311

prepare stmt2 from "insert into t9

2312

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

2313

values

2314

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

2315

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

2316

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

2317

insert into t9

2318

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

2319

values

2320

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

2321

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

2322

set @arg00= '41' ;

2323

insert into t9

2324

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

2325

values

2326

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

2327

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

2328

prepare stmt1 from "insert into t9

2329

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

2330

values

2331

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

2332

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

2333

execute stmt1 ;

2334

set @arg00= '43';

2335

prepare stmt2 from "insert into t9

2336

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

2337

values

2338

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

2339

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

2340

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

2341

insert into t9

2342

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

2343

values

2344

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

2345

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

2346

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

2347

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

2348

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

2349

insert into t9

2350

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

2351

values

2352

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

2353

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

2354

prepare stmt1 from "insert into t9

2355

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

2356

values

2357

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

2358

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

2359

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

2360

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

2361

execute stmt1 ;

2362

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

2363

prepare stmt2 from "insert into t9

2364

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

2365

values

2366

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

2367

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

2368

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

2369

set @arg00= 2 ;

2370

set @arg00= NULL ;

2371

insert into t9

2372

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

2373

values

2374

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

2375

NULL, NULL, NULL ) ;

2376

insert into t9

2377

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

2378

values

2379

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

2380

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

2381

prepare stmt1 from "insert into t9

2382

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

2383

values

2384

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

2385

NULL, NULL, NULL )" ;

2386

execute stmt1 ;

2387

prepare stmt2 from "insert into t9

2388

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

2389

values

2390

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

2391

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

2392

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

2393

set @arg00= 8.0 ;

2394

set @arg00= NULL ;

2395

insert into t9

2396

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

2397

values

2398

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

2399

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

2400

prepare stmt2 from "insert into t9

2401

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

2402

values

2403

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

2404

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

2405

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

2406

set @arg00= 'abc' ;

2407

set @arg00= NULL ;

2408

insert into t9

2409

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

2410

values

2411

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

2412

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

2413

prepare stmt2 from "insert into t9

2414

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

2415

values

2416

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

2417

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

2418

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

2419

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

2420

from t9 where c1 >= 20

2421

order by c1 ;

2422

c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c12

2423

20 20 20 20 20 20 20 20 20 20 20.0000

2424

21 21 21 21 21 21 21 21 21 21 21.0000

2425

22 22 22 22 22 22 22 22 22 22 22.0000

2426

23 23 23 23 23 23 23 23 23 23 23.0000

2427

30 30 30 30 30 30 30 30 30 30 30.0000

2428

31 31 31 31 31 31 31 31 31 31 31.0000

2429

32 32 32 32 32 32 32 32 32 32 32.0000

2430

33 33 33 33 33 33 33 33 33 33 33.0000

2431

40 40 40 40 40 40 40 40 40 40 40.0000

2432

41 41 41 41 41 41 41 41 41 41 41.0000

2433

42 42 42 42 42 42 42 42 42 42 42.0000

2434

43 43 43 43 43 43 43 43 43 43 43.0000

2435

50 50 50 50 50 50 50 50 50 50 50.0000

2436

51 51 51 51 51 51 51 51 51 51 51.0000

2437

52 52 52 52 52 52 52 52 52 52 52.0000

2438

53 53 53 53 53 53 53 53 53 53 53.0000

2439

60 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2440

61 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2441

62 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2442

63 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2443

71 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2444

73 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2445

81 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2446

83 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

2447

test_sequence

2448

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

2449

set @arg00= 20;

2450

select 'true' as found from t9

2451

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

2452

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

2453

found

2454

true

2455

select 'true' as found from t9

2456

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

2457

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

2458

and c12= @arg00;

2459

found

2460

true

2461

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

2462

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

2463

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

2464

execute stmt1 ;

2465

found

2466

true

2467

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

2468

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

2469

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

2470

and c12= ? ";

2471

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

2472

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

2473

found

2474

true

2475

set @arg00= 20.0;

2476

select 'true' as found from t9

2477

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

2478

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

2479

found

2480

true

2481

select 'true' as found from t9

2482

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

2483

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

2484

and c12= @arg00;

2485

found

2486

true

2487

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

2488

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

2489

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

2490

execute stmt1 ;

2491

found

2492

true

2493

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

2494

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

2495

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

2496

and c12= ? ";

2497

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

2498

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

2499

found

2500

true

2501

select 'true' as found from t9

2502

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

2503

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

2504

found

2505

true

2506

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

2507

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

2508

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

2509

execute stmt1 ;

2510

found

2511

true

2512

set @arg00= '20';

2513

select 'true' as found from t9

2514

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

2515

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

2516

and c12= @arg00;

2517

found

2518

true

2519

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

2520

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

2521

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

2522

and c12= ? ";

2523

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

2524

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

2525

found

2526

true

2527

select 'true' as found from t9

2528

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

2529

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

2530

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

2531

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

2532

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

2533

c12= CAST('20' as binary);

2534

found

2535

true

2536

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

2537

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

2538

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

2539

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

2540

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

2541

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

2542

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

2543

execute stmt1 ;

2544

found

2545

true

2546

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

2547

select 'true' as found from t9

2548

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

2549

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

2550

and c12= @arg00;

2551

found

2552

true

2553

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

2554

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

2555

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

2556

and c12= ? ";

2557

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

2558

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

2559

found

2560

true

2561

delete from t9 ;

2562

test_sequence

2563

-- some numeric overflow experiments --

2564

prepare my_insert from "insert into t9

2565

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

2566

values

2567

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

2568

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

2569

from t9 where c21 = 'O' ";

2570

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

2571

set @arg00= 9223372036854775807 ;

2572

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

2573

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

2574

Warnings:

2575

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

2576

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

2577

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

2578

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

2579

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

2580

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

2581

execute my_select ;

2582

c1 127

2583

c2 32767

2584

c3 8388607

2585

c4 2147483647

2586

c5 2147483647

2587

c6 9223372036854775807

2588

c7 9.22337e18

2589

c8 9.223372036854776e18

2590

c9 9.223372036854776e18

2591

c10 9.223372036854776e18

2592

c12 9999.9999

2593

execute my_delete ;

2594

set @arg00= '9223372036854775807' ;

2595

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

2596

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

2597

Warnings:

2598

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

2599

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

2600

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

2601

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

2602

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

2603

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

2604

execute my_select ;

2605

c1 127

2606

c2 32767

2607

c3 8388607

2608

c4 2147483647

2609

c5 2147483647

2610

c6 9223372036854775807

2611

c7 9.22337e18

2612

c8 9.223372036854776e18

2613

c9 9.223372036854776e18

2614

c10 9.223372036854776e18

2615

c12 9999.9999

2616

execute my_delete ;

2617

set @arg00= -9223372036854775808 ;

2618

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

2619

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

2620

Warnings:

2621

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

2622

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

2623

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

2624

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

2625

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

2626

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

2627

execute my_select ;

2628

c1 -128

2629

c2 -32768

2630

c3 -8388608

2631

c4 -2147483648

2632

c5 -2147483648

2633

c6 -9223372036854775808

2634

c7 -9.22337e18

2635

c8 -9.223372036854776e18

2636

c9 -9.223372036854776e18

2637

c10 -9.223372036854776e18

2638

c12 -9999.9999

2639

execute my_delete ;

2640

set @arg00= '-9223372036854775808' ;

2641

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

2642

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

2643

Warnings:

2644

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

2645

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

2646

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

2647

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

2648

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

2649

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

2650

execute my_select ;

2651

c1 -128

2652

c2 -32768

2653

c3 -8388608

2654

c4 -2147483648

2655

c5 -2147483648

2656

c6 -9223372036854775808

2657

c7 -9.22337e18

2658

c8 -9.223372036854776e18

2659

c9 -9.223372036854776e18

2660

c10 -9.223372036854776e18

2661

c12 -9999.9999

2662

execute my_delete ;

2663

set @arg00= 1.11111111111111111111e+50 ;

2664

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

2665

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

2666

Warnings:

2667

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

2668

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

2669

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

2670

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

2671

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

2672

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

2673

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

2674

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

2675

execute my_select ;

2676

c1 127

2677

c2 32767

2678

c3 8388607

2679

c4 2147483647

2680

c5 2147483647

2681

c6 9223372036854775807

2682

c7 3.40282e38

2683

c8 1.111111111111111e50

2684

c9 1.111111111111111e50

2685

c10 1.111111111111111e50

2686

c12 9999.9999

2687

execute my_delete ;

2688

set @arg00= '1.11111111111111111111e+50' ;

2689

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

2690

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

2691

Warnings:

2692

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

2693

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

2694

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

2695

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

2696

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

2697

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

2698

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

2699

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

2700

execute my_select ;

2701

c1 127

2702

c2 32767

2703

c3 8388607

2704

c4 2147483647

2705

c5 2147483647

2706

c6 9223372036854775807

2707

c7 3.40282e38

2708

c8 1.111111111111111e50

2709

c9 1.111111111111111e50

2710

c10 1.111111111111111e50

2711

c12 9999.9999

2712

execute my_delete ;

2713

set @arg00= -1.11111111111111111111e+50 ;

2714

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

2715

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

2716

Warnings:

2717

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

2718

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

2719

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

2720

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

2721

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

2722

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

2723

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

2724

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

2725

execute my_select ;

2726

c1 -128

2727

c2 -32768

2728

c3 -8388608

2729

c4 -2147483648

2730

c5 -2147483648

2731

c6 -9223372036854775808

2732

c7 -3.40282e38

2733

c8 -1.111111111111111e50

2734

c9 -1.111111111111111e50

2735

c10 -1.111111111111111e50

2736

c12 -9999.9999

2737

execute my_delete ;

2738

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

2739

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

2740

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

2741

Warnings:

2742

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

2743

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

2744

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

2745

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

2746

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

2747

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

2748

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

2749

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

2750

execute my_select ;

2751

c1 -128

2752

c2 -32768

2753

c3 -8388608

2754

c4 -2147483648

2755

c5 -2147483648

2756

c6 -9223372036854775808

2757

c7 -3.40282e38

2758

c8 -1.111111111111111e50

2759

c9 -1.111111111111111e50

2760

c10 -1.111111111111111e50

2761

c12 -9999.9999

2762

execute my_delete ;

2763

test_sequence

2764

-- insert into string columns --

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

Warnings:

2790

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

2791

Warnings:

2792

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

2793

Warnings:

2794

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

2795

Warnings:

2796

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

2797

Warnings:

2798

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

2799

Warnings:

2800

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

2801

Warnings:

2802

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

2803

Warnings:

2804

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

2805

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

2806

from t9 where c1 >= 20

2807

order by c1 ;

2808

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

2809

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

2810

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

2811

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

2812

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

2813

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

2814

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

2815

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

2816

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

2817

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

2818

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

2819

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

2820

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

2821

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

2822

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

2823

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

2824

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

2825

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

2826

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

2827

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

2828

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

2829

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

2830

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

2831

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

2832

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

2833

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

2834

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

2835

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

2836

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

2837

test_sequence

2838

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

2839

set @arg00= '20';

2840

select 'true' as found from t9

2841

where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and

2842

c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and

2843

c27= '20' and c28= '20' and c29= '20' and c30= '20' ;

2844

found

2845

true

2846

select 'true' as found from t9

2847

where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and

2848

c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and

2849

c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;

2850

found

2851

true

2852

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

2853

where c1= 20 and concat(c20,substr('20',1+length(c20)))= '20' and c21= '20' and

2854

c22= '20' and c23= '20' and c24= '20' and c25= '20' and c26= '20' and

2855

c27= '20' and c28= '20' and c29= '20' and c30= '20'" ;

2856

execute stmt1 ;

2857

found

2858

true

2859

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

2860

where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and

2861

c21= ? and c22= ? and c23= ? and c25= ? and

2862

c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;

2863

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

2864

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

2865

found

2866

true

2867

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

2868

select 'true' as found from t9

2869

where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))

2870

= CAST('20' as binary) and c21= CAST('20' as binary)

2871

and c22= CAST('20' as binary) and c23= CAST('20' as binary) and

2872

c24= CAST('20' as binary) and c25= CAST('20' as binary) and

2873

c26= CAST('20' as binary) and c27= CAST('20' as binary) and

2874

c28= CAST('20' as binary) and c29= CAST('20' as binary) and

2875

c30= CAST('20' as binary) ;

2876

found

2877

true

2878

select 'true' as found from t9

2879

where c1= 20 and concat(c20,substr(@arg00,1+length(c20))) = @arg00 and

2880

c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and

2881

c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and

2882

c30= @arg00;

2883

found

2884

true

2885

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

2886

where c1= 20 and concat(c20,substr(CAST('20' as binary),1+length(c20)))

2887

= CAST('20' as binary) and c21= CAST('20' as binary)

2888

and c22= CAST('20' as binary) and c23= CAST('20' as binary) and

2889

c24= CAST('20' as binary) and c25= CAST('20' as binary) and

2890

c26= CAST('20' as binary) and c27= CAST('20' as binary) and

2891

c28= CAST('20' as binary) and c29= CAST('20' as binary) and

2892

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

2893

execute stmt1 ;

2894

found

2895

true

2896

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

2897

where c1= 20 and concat(c20,substr(?,1+length(c20))) = ? and c21= ? and

2898

c22= ? and c23= ? and c25= ? and c26= ? and c27= ? and c28= ? and

2899

c29= ? and c30= ?";

2900

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

2901

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

2902

found

2903

true

2904

set @arg00= 20;

2905

select 'true' as found from t9

2906

where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and

2907

c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and

2908

c27= 20 and c28= 20 and c29= 20 and c30= 20 ;

2909

found

2910

true

2911

select 'true' as found from t9

2912

where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and

2913

c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and

2914

c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;

2915

found

2916

true

2917

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

2918

where c1= 20 and concat(c20,substr(20,1+length(c20)))= 20 and c21= 20 and

2919

c22= 20 and c23= 20 and c24= 20 and c25= 20 and c26= 20 and

2920

c27= 20 and c28= 20 and c29= 20 and c30= 20" ;

2921

execute stmt1 ;

2922

found

2923

true

2924

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

2925

where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and

2926

c21= ? and c22= ? and c23= ? and c25= ? and

2927

c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;

2928

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

2929

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

2930

found

2931

true

2932

set @arg00= 20.0;

2933

select 'true' as found from t9

2934

where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and

2935

c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and

2936

c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0 ;

2937

found

2938

true

2939

select 'true' as found from t9

2940

where c1= 20 and concat(c20,substr(@arg00,1+length(c20)))= @arg00 and

2941

c21= @arg00 and c22= @arg00 and c23= @arg00 and c25= @arg00 and

2942

c26= @arg00 and c27= @arg00 and c28= @arg00 and c29= @arg00 and c30= @arg00;

2943

found

2944

true

2945

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

2946

where c1= 20 and concat(c20,substr(20.0,1+length(c20)))= 20.0 and c21= 20.0 and

2947

c22= 20.0 and c23= 20.0 and c24= 20.0 and c25= 20.0 and c26= 20.0 and

2948

c27= 20.0 and c28= 20.0 and c29= 20.0 and c30= 20.0" ;

2949

execute stmt1 ;

2950

found

2951

true

2952

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

2953

where c1= 20 and concat(c20,substr(?,1+length(c20)))= ? and

2954

c21= ? and c22= ? and c23= ? and c25= ? and

2955

c26= ? and c27= ? and c28= ? and c29= ? and c30= ?" ;

2956

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

2957

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

2958

found

2959

true

2960

delete from t9 ;

2961

test_sequence

2962

-- insert into date/time columns --

2963

Warnings:

2964

Note 1265 Data truncated for column 'c13' at row 1

2965

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

2966

Warnings:

2967

Note 1265 Data truncated for column 'c13' at row 1

2968

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

2969

Warnings:

2970

Note 1265 Data truncated for column 'c13' at row 1

2971

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

2972

Warnings:

2973

Note 1265 Data truncated for column 'c13' at row 1

2974

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

2975

Warnings:

2976

Note 1265 Data truncated for column 'c13' at row 1

2977

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

2978

Warnings:

2979

Note 1265 Data truncated for column 'c13' at row 1

2980

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

2981

Warnings:

2982

Note 1265 Data truncated for column 'c13' at row 1

2983

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

2984

Warnings:

2985

Note 1265 Data truncated for column 'c13' at row 1

2986

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

2987

Warnings:

2988

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

2989

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

2990

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

2991

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

2992

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

2993

Warnings:

2994

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

2995

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

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 1264 Out of range value for column 'c13' at row 1

3001

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

3002

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

3003

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

3004

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

3005

Warnings:

3006

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

3007

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

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

Warnings:

3012

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

3013

Warning 1264 Out of range value for column 'c16' at row 1

3014

Warning 1264 Out of range value for column 'c17' at row 1

3015

Warnings:

3016

Warning 1265 Data truncated for column 'c15' at row 1

3017

Warning 1264 Out of range value for column 'c16' at row 1

3018

Warning 1264 Out of range value for column 'c17' at row 1

3019

Warnings:

3020

Warning 1265 Data truncated for column 'c15' at row 1

3021

Warning 1264 Out of range value for column 'c16' at row 1

3022

Warning 1264 Out of range value for column 'c17' at row 1

3023

Warnings:

3024

Warning 1265 Data truncated for column 'c15' at row 1

3025

Warning 1264 Out of range value for column 'c16' at row 1

3026

Warning 1264 Out of range value for column 'c17' at row 1

3027

select c1, c13, c14, c15, c16, c17 from t9 order by c1 ;

3028

c1 c13 c14 c15 c16 c17

3029

20 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3030

21 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3031

22 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3032

23 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3033

30 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3034

31 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3035

32 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3036

33 1991-01-01 1991-01-01 01:01:01 1991-01-01 01:01:01 01:01:01 1991

3037

40 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3038

41 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3039

42 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3040

43 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3041

50 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3042

51 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3043

52 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3044

53 2001-00-00 2001-00-00 00:00:00 0000-00-00 00:00:00 838:59:59 0000

3045

60 NULL NULL 1991-01-01 01:01:01 NULL NULL

3046

61 NULL NULL 1991-01-01 01:01:01 NULL NULL

3047

62 NULL NULL 1991-01-01 01:01:01 NULL NULL

3048

63 NULL NULL 1991-01-01 01:01:01 NULL NULL

3049

71 NULL NULL 1991-01-01 01:01:01 NULL NULL

3050

73 NULL NULL 1991-01-01 01:01:01 NULL NULL

3051

81 NULL NULL 1991-01-01 01:01:01 NULL NULL

3052

83 NULL NULL 1991-01-01 01:01:01 NULL NULL

3053

test_sequence

3054

-- select .. where date/time column = .. --

3055

set @arg00= '1991-01-01 01:01:01' ;

3056

select 'true' as found from t9

3057

where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and

3058

c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and

3059

c17= '1991-01-01 01:01:01' ;

3060

found

3061

true

3062

select 'true' as found from t9

3063

where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00

3064

and c17= @arg00 ;

3065

found

3066

true

3067

prepare stmt1 from "select 'true' as found from t9

3068

where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and

3069

c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and

3070

c17= '1991-01-01 01:01:01'" ;

3071

execute stmt1 ;

3072

found

3073

true

3074

prepare stmt1 from "select 'true' as found from t9

3075

where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;

3076

execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;

3077

found

3078

true

3079

set @arg00= CAST('1991-01-01 01:01:01' as datetime) ;

3080

select 'true' as found from t9

3081

where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and

3082

c14= CAST('1991-01-01 01:01:01' as datetime) and

3083

c15= CAST('1991-01-01 01:01:01' as datetime) and

3084

c16= CAST('1991-01-01 01:01:01' as datetime) and

3085

c17= CAST('1991-01-01 01:01:01' as datetime) ;

3086

found

3087

true

3088

select 'true' as found from t9

3089

where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00

3090

and c17= @arg00 ;

3091

found

3092

true

3093

prepare stmt1 from "select 'true' as found from t9

3094

where c1= 20 and c13= CAST('1991-01-01 00:00:00' as datetime) and

3095

c14= CAST('1991-01-01 01:01:01' as datetime) and

3096

c15= CAST('1991-01-01 01:01:01' as datetime) and

3097

c16= CAST('1991-01-01 01:01:01' as datetime) and

3098

c17= CAST('1991-01-01 01:01:01' as datetime)" ;

3099

execute stmt1 ;

3100

found

3101

true

3102

prepare stmt1 from "select 'true' as found from t9

3103

where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;

3104

execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;

3105

found

3106

true

3107

set @arg00= 1991 ;

3108

select 'true' as found from t9

3109

where c1= 20 and c17= 1991 ;

3110

found

3111

true

3112

select 'true' as found from t9

3113

where c1= 20 and c17= @arg00 ;

3114

found

3115

true

3116

prepare stmt1 from "select 'true' as found from t9

3117

where c1= 20 and c17= 1991" ;

3118

execute stmt1 ;

3119

found

3120

true

3121

prepare stmt1 from "select 'true' as found from t9

3122

where c1= 20 and c17= ?" ;

3123

execute stmt1 using @arg00 ;

3124

found

3125

true

3126

set @arg00= 1.991e+3 ;

3127

select 'true' as found from t9

3128

where c1= 20 and abs(c17 - 1.991e+3) < 0.01 ;

3129

found

3130

true

3131

select 'true' as found from t9

3132

where c1= 20 and abs(c17 - @arg00) < 0.01 ;

3133

found

3134

true

3135

prepare stmt1 from "select 'true' as found from t9

3136

where c1= 20 and abs(c17 - 1.991e+3) < 0.01" ;

3137

execute stmt1 ;

3138

found

3139

true

3140

prepare stmt1 from "select 'true' as found from t9

3141

where c1= 20 and abs(c17 - ?) < 0.01" ;

3142

execute stmt1 using @arg00 ;

3143

found

3144

true

3145

drop table t1, t9;

Older »