~drizzle-trunk/drizzle/development

Note 1003 (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` limit 2) union all (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`a` limit 1) order by `b` desc

(select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2;

a b

1 a

2 b

select found_rows();

found_rows()

select sql_calc_found_rows a,b from t1 union all select a,b from t2 limit 2;

100

a b

101

1 a

102

2 b

103

select found_rows();

104

found_rows()

105

106

explain select a,b from t1 union all select a,b from t2;

107

id select_type table type possible_keys key key_len ref rows Extra

108

1 PRIMARY t1 ALL NULL NULL NULL NULL #

109

2 UNION t2 ALL NULL NULL NULL NULL #

110

NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL #

111

explain select xx from t1 union select 1;

112

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

113

explain select a,b from t1 union select 1;

114

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

115

explain select 1 union select a,b from t1 union select 1;

116

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

117

explain select a,b from t1 union select 1 limit 0;

118

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

119

select a,b from t1 into outfile 'skr' union select a,b from t2;

120

ERROR HY000: Incorrect usage of UNION and INTO

121

select a,b from t1 order by a union select a,b from t2;

122

ERROR HY000: Incorrect usage of UNION and order_st BY

123

insert into t3 select a from t1 order by a union select a from t2;

124

ERROR HY000: Incorrect usage of UNION and order_st BY

125

create table t3 select a,b from t1 union select a from t2;

126

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

127

select a,b from t1 union select a from t2;

128

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

129

select * from t1 union select a from t2;

130

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

131

select a from t1 union select * from t2;

132

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

133

select * from t1 union select SQL_BUFFER_RESULT * from t2;

134

ERROR 42000: Incorrect usage/placement of 'SQL_BUFFER_RESULT'

135

create table t3 select a,b from t1 union all select a,b from t2;

136

insert into t3 select a,b from t1 union all select a,b from t2;

137

replace into t3 select a,b as c from t1 union all select a,b from t2;

138

drop table t1,t2,t3;

139

select * union select 1;

140

ERROR HY000: No tables used

141

select 1 as a,(select a union select a);

142

a (select a union select a)

143

1 1

144

(select 1) union (select 2) order by 0;

145

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

146

SELECT @a:=1 UNION SELECT @a:=@a+1;

147

@a:=1

148

149

150

(SELECT 1) UNION (SELECT 2) ORDER BY (SELECT a);

151

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

152

(SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2);

153

1 3

154

1 3

155

2 1

156

CREATE TABLE t1 (

157

`pseudo` char(35) NOT NULL default '',

158

`pseudo1` char(35) NOT NULL default '',

159

`same` int NOT NULL default '1',

160

PRIMARY KEY (`pseudo1`),

161

KEY `pseudo` (`pseudo`)

162

);

163

INSERT INTO t1 (pseudo,pseudo1,same) VALUES ('joce', 'testtt', 1),('joce', 'tsestset', 1),('dekad', 'joce', 1);

164

SELECT pseudo FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo FROM t1 WHERE pseudo='joce';

165

pseudo

166

dekad

167

joce

168

SELECT pseudo1 FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo1 FROM t1 WHERE pseudo='joce';

169

pseudo1

170

joce

171

testtt

172

tsestset

173

SELECT * FROM t1 WHERE pseudo1='joce' UNION SELECT * FROM t1 WHERE pseudo='joce' order by pseudo desc,pseudo1 desc;

174

pseudo pseudo1 same

175

joce tsestset 1

176

joce testtt 1

177

dekad joce 1

178

SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT pseudo FROM t1 WHERE pseudo1='joce';

179

pseudo1

180

testtt

181

tsestset

182

dekad

183

SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION ALL SELECT pseudo FROM t1 WHERE pseudo1='joce';

184

pseudo1

185

testtt

186

tsestset

187

dekad

188

SELECT pseudo1 FROM t1 WHERE pseudo='joce' UNION SELECT 1;

189

pseudo1

190

testtt

191

tsestset

192

193

drop table t1;

194

create table t1 (a int);

195

create table t2 (a int);

196

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

197

insert into t2 values (11),(12),(13),(14),(15);

198

(select * from t1 limit 2) union (select * from t2 limit 3) limit 4;

199

200

201

202

203

204

(select * from t1 limit 2) union (select * from t2 limit 3);

205

206

207

208

209

210

211

(select * from t1 limit 2) union (select * from t2 limit 20,3);

212

213

214

215

set SQL_SELECT_LIMIT=2;

216

(select * from t1 limit 1) union (select * from t2 limit 3);

217

218

219

220

set SQL_SELECT_LIMIT=DEFAULT;

221

drop table t1,t2;

222

CREATE TABLE t1 (

223

cid int NOT NULL default '0',

224

cv varchar(190) NOT NULL default '',

225

PRIMARY KEY (cid),

226

UNIQUE KEY cv (cv)

227

) ;

228

INSERT INTO t1 VALUES (8,'dummy');

229

CREATE TABLE t2 (

230

cid int NOT NULL auto_increment,

231

cap varchar(255) NOT NULL default '',

232

PRIMARY KEY (cid),

233

KEY cap (cap)

234

) ;

235

CREATE TABLE t3 (

236

gid int NOT NULL auto_increment,

237

gn varchar(255) NOT NULL default '',

238

must int default NULL,

239

PRIMARY KEY (gid),

240

KEY gn (gn)

241

) ;

242

INSERT INTO t3 VALUES (1,'V1',NULL);

243

CREATE TABLE t4 (

244

uid bigint NOT NULL default '0',

245

gid bigint default NULL,

246

rid bigint default NULL,

247

cid bigint default NULL,

248

UNIQUE KEY m (uid,gid,rid,cid),

249

KEY uid (uid),

250

KEY rid (rid),

251

KEY cid (cid),

252

KEY container (gid,rid,cid)

253

) ;

254

INSERT INTO t4 VALUES (1,1,NULL,NULL);

255

CREATE TABLE t5 (

256

rid bigint NOT NULL auto_increment,

257

rl varchar(255) NOT NULL default '',

258

PRIMARY KEY (rid),

259

KEY rl (rl)

260

) ;

261

CREATE TABLE t6 (

262

uid bigint NOT NULL auto_increment,

263

un varchar(190) NOT NULL default '',

264

uc int NOT NULL default '0',

265

PRIMARY KEY (uid),

266

UNIQUE KEY nc (un,uc),

267

KEY un (un)

268

) ;

269

INSERT INTO t6 VALUES (1,'test',8);

270

SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test";

271

uid rl g1 cid gg

272

1 NULL V1 NULL 1

273

SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test";

274

uid rl g1 cid gg

275

(SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test") UNION (SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test");

276

uid rl g1 cid gg

277

1 NULL V1 NULL 1

278

drop table t1,t2,t3,t4,t5,t6;

279

CREATE TABLE t1 (a int not null, b char (10) not null);

280

insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');

281

CREATE TABLE t2 (a int not null, b char (10) not null);

282

insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');

283

create table t3 select a,b from t1 union select a,b from t2;

284

create table t4 (select a,b from t1) union (select a,b from t2) limit 2;

285

insert into t4 select a,b from t1 union select a,b from t2;

286

insert into t3 (select a,b from t1) union (select a,b from t2) limit 2;

287

select * from t3;

288

a b

289

1 a

290

2 b

291

3 c

292

4 d

293

5 f

294

6 e

295

1 a

296

2 b

297

select * from t4;

298

a b

299

1 a

300

2 b

301

1 a

302

2 b

303

3 c

304

4 d

305

5 f

306

6 e

307

drop table t1,t2,t3,t4;

308

create table t1 (a int);

309

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

310

create table t2 (a int);

311

insert into t2 values (3),(4),(5);

312

(SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2) LIMIT 1;

313

314

315

select found_rows();

316

found_rows()

317

318

(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2) LIMIT 2;

319

320

321

322

select found_rows();

323

found_rows()

324

325

(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION all (SELECT * FROM t2);

326

327

328

329

330

331

select found_rows();

332

found_rows()

333

334

(SELECT SQL_CALC_FOUND_ROWS * FROM t1) UNION all (SELECT * FROM t2 LIMIT 1);

335

336

337

338

339

340

select found_rows();

341

found_rows()

342

343

(SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1) UNION SELECT * FROM t2 LIMIT 1;

344

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

345

SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION all SELECT * FROM t2 LIMIT 2;

346

347

348

349

select found_rows();

350

found_rows()

351

352

SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION all SELECT * FROM t2 LIMIT 2;

353

354

355

356

select found_rows();

357

found_rows()

358

359

SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2;

360

361

362

363

select found_rows();

364

found_rows()

365

366

SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 100;

367

368

369

370

371

372

373

select found_rows();

374

found_rows()

375

376

SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 100 UNION SELECT * FROM t2;

377

378

379

380

381

382

383

select found_rows();

384

found_rows()

385

386

SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2;

387

388

389

390

391

392

select found_rows();

393

found_rows()

394

395

SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 1 UNION SELECT * FROM t2 LIMIT 2;

396

397

398

399

select found_rows();

400

found_rows()

401

402

SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 2,2;

403

404

405

406

select found_rows();

407

found_rows()

408

409

SELECT SQL_CALC_FOUND_ROWS * FROM t1 limit 2,2 UNION SELECT * FROM t2;

410

411

412

413

414

select found_rows();

415

found_rows()

416

417

SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a desc LIMIT 1;

418

419

420

(SELECT * FROM t1 ORDER by a) UNION ALL (SELECT * FROM t2 ORDER BY a) ORDER BY A desc LIMIT 4;

421

422

423

424

425

426

(SELECT * FROM t1) UNION all (SELECT SQL_CALC_FOUND_ROWS * FROM t2) LIMIT 1;

427

ERROR 42000: Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS'

428

create temporary table t1 select a from t1 union select a from t2;

429

drop temporary table t1;

430

create table t1 select a from t1 union select a from t2;

431

ERROR HY000: You can't specify target table 't1' for update in FROM clause

432

select a from t1 union select a from t2 order by t2.a;

433

ERROR 42S22: Unknown column 't2.a' in 'order clause'

434

drop table t1,t2;

435

select length(version()) > 1 as `*` UNION select 2;

436

437

438

439

create table t1 (a int);

440

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

441

explain (select * from t1) union (select * from t1) order by a;

442

id select_type table type possible_keys key key_len ref rows Extra

443

1 PRIMARY t1 ALL NULL NULL NULL NULL 4

444

2 UNION t1 ALL NULL NULL NULL NULL 4

445

NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using filesort

446

drop table t1;

447

CREATE TEMPORARY TABLE t1 ( id int default '0') ENGINE=MyISAM;

448

INSERT INTO t1 (id) VALUES("1");

449

CREATE TEMPORARY TABLE t2 ( id int default '0', id_master int default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL) ENGINE=MyISAM;

450

INSERT INTO t2 (id, id_master, text1, text2) VALUES("1", "1",

451

"foo1", "bar1");

452

INSERT INTO t2 (id, id_master, text1, text2) VALUES("2", "1",

453

"foo2", "bar2");

454

INSERT INTO t2 (id, id_master, text1, text2) VALUES("3", "1", NULL,

455

"bar3");

456

INSERT INTO t2 (id, id_master, text1, text2) VALUES("4", "1",

457

"foo4", "bar4");

458

SELECT 1 AS id_master, 1 AS id, NULL AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master;

459

id_master id text1 text2

460

1 1 NULL ABCDE

461

1 1 foo1 bar1

462

1 2 foo2 bar2

463

1 3 NULL bar3

464

1 4 foo4 bar4

465

SELECT 1 AS id_master, 1 AS id, 'ABCDE' AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master;

466

id_master id text1 text2

467

1 1 ABCDE ABCDE

468

1 1 foo1 bar1

469

1 2 foo2 bar2

470

1 3 NULL bar3

471

1 4 foo4 bar4

472

drop table if exists t1,t2;

473

create table t1 (a int not null primary key auto_increment, b int, key(b));

474

create table t2 (a int not null primary key auto_increment, b int);

475

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

476

insert into t2 (b) values (10),(11),(12),(13);

477

explain extended (select * from t1 where a=1) union (select * from t2 where a=1);

478

id select_type table type possible_keys key key_len ref rows filtered Extra

479

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 100.00

480

2 UNION t2 const PRIMARY PRIMARY 4 const 1 100.00

481

NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL

482

Warnings:

483

Note 1003 (select '1' AS `a`,'1' AS `b` from `test`.`t1` where ('1' = 1)) union (select '1' AS `a`,'10' AS `b` from `test`.`t2` where ('1' = 1))

484

(select * from t1 where a=5) union (select * from t2 where a=1);

485

a b

486

1 10

487

(select * from t1 where a=5 and a=6) union (select * from t2 where a=1);

488

a b

489

1 10

490

(select t1.a,t1.b from t1,t2 where t1.a=5) union (select * from t2 where a=1);

491

a b

492

1 10

493

(select * from t1 where a=1) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a);

494

a b

495

1 1

496

2 2

497

3 3

498

4 4

499

explain (select * from t1 where a=1 and b=10) union (select straight_join t1.a,t2.a from t1,t2 where t1.a=t2.a);

500

id select_type table type possible_keys key key_len ref rows Extra

501

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables

502

2 UNION t1 index PRIMARY PRIMARY 4 NULL 4 Using index

503

2 UNION t2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index; Using join buffer

504

NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL

505

explain (select * from t1 where a=1) union (select * from t1 where b=1);

506

id select_type table type possible_keys key key_len ref rows Extra

507

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1

508

2 UNION t1 ref b b 5 const 1

509

NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL

510

drop table t1,t2;

511

create table t1 ( id int not null auto_increment, primary key (id) ,user_name text );

512

create table t2 ( id int not null auto_increment, primary key (id) ,group_name text );

513

create table t3 ( id int not null auto_increment, primary key (id) ,user_id int ,index user_idx (user_id) ,foreign key (user_id) references t1(id) ,group_id int ,index group_idx (group_id) ,foreign key (group_id) references t2(id) );

514

insert into t1 (user_name) values ('Tester');

515

insert into t2 (group_name) values ('Group A');

516

insert into t2 (group_name) values ('Group B');

517

insert into t3 (user_id, group_id) values (1,1);

518

select 1 'is_in_group', a.user_name, c.group_name, b.id from t1 a, t3 b, t2 c where a.id = b.user_id and b.group_id = c.id UNION select 0 'is_in_group', a.user_name, c.group_name, null from t1 a, t2 c;

519

is_in_group user_name group_name id

520

1 Tester Group A 1

521

0 Tester Group A NULL

522

0 Tester Group B NULL

523

drop table t3, t1, t2;

524

create table t1 (mat_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test INT NULL);

525

create table t2 (mat_id INT NOT NULL, pla_id INT NOT NULL);

526

insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), (NULL, 'i', 9);

527

insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);

528

SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id union SELECT 0, 0;

529

pla_id matintnum

530

100 a

531

101 a

532

102 a

533

103 b

534

104 b

535

105 c

536

0 0

537

drop table t1, t2;

538

create table t1 SELECT "a" as a UNION select "aa" as a;

539

select * from t1;

540

541

542

show create table t1;

543

Table Create Table

544

t1 CREATE TABLE `t1` (

545

`a` varchar(1) NOT NULL DEFAULT ''

546

) ENGINE=DEFAULT

547

drop table t1;

548

create table t1 SELECT 12 as a UNION select "aa" as a;

549

select * from t1;

550

551

552

553

show create table t1;

554

Table Create Table

555

t1 CREATE TABLE `t1` (

556

`a` varbinary(4) NOT NULL DEFAULT ''

557

) ENGINE=DEFAULT

558

drop table t1;

559

create table t1 SELECT 12 as a UNION select 12.2 as a;

560

select * from t1;

561

562

12.0

563

12.2

564

show create table t1;

565

Table Create Table

566

t1 CREATE TABLE `t1` (

567

`a` decimal(3,1) NOT NULL DEFAULT '0.0'

568

) ENGINE=DEFAULT

569

drop table t1;

570

create table t2 (it1 int, it2 int not null, i int not null, ib int, f float, d double, da date, dt datetime, sc char(10), sv varchar(10), b blob, tx text);

571

insert into t2 values (NULL, 1, 3, 4, 1.5, 2.5, '1972-10-22', '1972-10-22 11:50:00', 'testc', 'testv', 'tetetetetest', 'teeeeeeeeeeeest');

572

create table t1 SELECT it2 from t2 UNION select it1 from t2;

573

select * from t1;

574

it2

575

576

NULL

577

show create table t1;

578

Table Create Table

579

t1 CREATE TABLE `t1` (

580

`it2` int DEFAULT NULL

581

) ENGINE=DEFAULT

582

drop table t1;

583

create table t1 SELECT it2 from t2 UNION select i from t2;

584

select * from t1;

585

it2

586

587

588

show create table t1;

589

Table Create Table

590

t1 CREATE TABLE `t1` (

591

`it2` int NOT NULL DEFAULT '0'

592

) ENGINE=DEFAULT

593

drop table t1;

594

create table t1 SELECT i from t2 UNION select f from t2;

595

select * from t1;

596

597

598

1.5

599

show create table t1;

600

Table Create Table

601

t1 CREATE TABLE `t1` (

602

`i` double DEFAULT NULL

603

) ENGINE=DEFAULT

604

drop table t1;

605

create table t1 SELECT f from t2 UNION select d from t2;

606

select * from t1;

607

608

1.5

609

2.5

610

show create table t1;

611

Table Create Table

612

t1 CREATE TABLE `t1` (

613

`f` double DEFAULT NULL

614

) ENGINE=DEFAULT

615

drop table t1;

616

create table t1 SELECT ib from t2 UNION select f from t2;

617

select * from t1;

618

619

620

1.5

621

show create table t1;

622

Table Create Table

623

t1 CREATE TABLE `t1` (

624

`ib` double DEFAULT NULL

625

) ENGINE=DEFAULT

626

drop table t1;

627

create table t1 SELECT ib from t2 UNION select d from t2;

628

select * from t1;

629

630

631

2.5

632

show create table t1;

633

Table Create Table

634

t1 CREATE TABLE `t1` (

635

`ib` double DEFAULT NULL

636

) ENGINE=DEFAULT

637

drop table t1;

638

create table t1 SELECT f from t2 UNION select da from t2;

639

select * from t1;

640

641

1.5

642

1972-10-22

643

show create table t1;

644

Table Create Table

645

t1 CREATE TABLE `t1` (

646

`f` varbinary(22) DEFAULT NULL

647

) ENGINE=DEFAULT

648

drop table t1;

649

create table t1 SELECT da from t2 UNION select dt from t2;

650

select * from t1;

651

652

1972-10-22 00:00:00

653

1972-10-22 11:50:00

654

show create table t1;

655

Table Create Table

656

t1 CREATE TABLE `t1` (

657

`da` datetime DEFAULT NULL

658

) ENGINE=DEFAULT

659

drop table t1;

660

create table t1 SELECT dt from t2 UNION select trim(sc) from t2;

661

select trim(dt) from t1;

662

trim(dt)

663

1972-10-22 11:50:00

664

testc

665

show create table t1;

666

Table Create Table

667

t1 CREATE TABLE `t1` (

668

`dt` varbinary(19) DEFAULT NULL

669

) ENGINE=DEFAULT

670

drop table t1;

671

create table t1 SELECT dt from t2 UNION select sv from t2;

672

select * from t1;

673

674

1972-10-22 11:50:00

675

testv

676

show create table t1;

677

Table Create Table

678

t1 CREATE TABLE `t1` (

679

`dt` varbinary(40) DEFAULT NULL

680

) ENGINE=DEFAULT

681

drop table t1;

682

create table t1 SELECT sc from t2 UNION select sv from t2;

683

select * from t1;

684

685

testc

686

testv

687

show create table t1;

688

Table Create Table

689

t1 CREATE TABLE `t1` (

690

`sc` varchar(10) DEFAULT NULL

691

) ENGINE=DEFAULT

692

drop table t1;

693

create table t1 SELECT dt from t2 UNION select b from t2;

694

select * from t1;

695

696

1972-10-22 11:50:00

697

tetetetetest

698

show create table t1;

699

Table Create Table

700

t1 CREATE TABLE `t1` (

701

`dt` blob

702

) ENGINE=DEFAULT

703

drop table t1;

704

create table t1 SELECT sv from t2 UNION select b from t2;

705

select * from t1;

706

707

testv

708

tetetetetest

709

show create table t1;

710

Table Create Table

711

t1 CREATE TABLE `t1` (

712

`sv` blob

713

) ENGINE=DEFAULT

714

drop table t1;

715

create table t1 SELECT i from t2 UNION select d from t2 UNION select b from t2;

716

select * from t1;

717

718

719

2.5

720

tetetetetest

721

show create table t1;

722

Table Create Table

723

t1 CREATE TABLE `t1` (

724

`i` blob

725

) ENGINE=DEFAULT

726

drop table t1;

727

create table t1 SELECT sv from t2 UNION select tx from t2;

728

select * from t1;

729

730

testv

731

teeeeeeeeeeeest

732

show create table t1;

733

Table Create Table

734

t1 CREATE TABLE `t1` (

735

`sv` text

736

) ENGINE=DEFAULT

737

drop table t1;

738

create table t1 SELECT b from t2 UNION select tx from t2;

739

select * from t1;

740

741

tetetetetest

742

teeeeeeeeeeeest

743

show create table t1;

744

Table Create Table

745

t1 CREATE TABLE `t1` (

746

`b` blob

747

) ENGINE=DEFAULT

748

drop table t1,t2;

749

create table t1 select 1 union select -1;

750

select * from t1;

751

752

753

-1

754

show create table t1;

755

Table Create Table

756

t1 CREATE TABLE `t1` (

757

`1` bigint NOT NULL DEFAULT '0'

758

) ENGINE=DEFAULT

759

drop table t1;

760

create table t1 select _latin1"test" union select _latin1"testt" ;

761

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

762

create table t1 select _utf8"test" union select _utf8"testt" ;

763

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

764

create table t1 select "test" union select "testt" ;

765

show create table t1;

766

Table Create Table

767

t1 CREATE TABLE `t1` (

768

`test` varchar(4) NOT NULL DEFAULT ''

769

) ENGINE=DEFAULT

770

drop table t1;

771

create table t1 (s char(200));

772

insert into t1 values (repeat("1",200));

773

create table t2 select * from t1;

774

insert into t2 select * from t1;

775

insert into t1 select * from t2;

776

insert into t2 select * from t1;

777

insert into t1 select * from t2;

778

insert into t2 select * from t1;

779

set local tmp_table_size=1024;

780

select count(*) from (select * from t1 union all select * from t2 order by 1) b;

781

count(*)

782

783

select count(*) from t1;

784

count(*)

785

786

select count(*) from t2;

787

count(*)

788

789

drop table t1,t2;

790

set local tmp_table_size=default;

791

create table t1 (a int, index (a), b int);

792

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

793

insert t1 select a+1, a+b from t1;

794

insert t1 select a+1, a+b from t1;

795

insert t1 select a+1, a+b from t1;

796

insert t1 select a+1, a+b from t1;

797

insert t1 select a+1, a+b from t1;

798

FLUSH STATUS;

799

show status like 'Slow_queries';

800

Variable_name Value

801

Slow_queries 0

802

select count(*) from t1 where a=7;

803

count(*)

804

805

show status like 'Slow_queries';

806

Variable_name Value

807

Slow_queries 0

808

select count(*) from t1 where b=13;

809

count(*)

810

811

show status like 'Slow_queries';

812

Variable_name Value

813

Slow_queries 0

814

select count(*) from t1 where b=13 union select count(*) from t1 where a=7;

815

count(*)

816

817

818

show status like 'Slow_queries';

819

Variable_name Value

820

Slow_queries 0

821

select count(*) from t1 where a=7 union select count(*) from t1 where b=13;

822

count(*)

823

824

825

show status like 'Slow_queries';

826

Variable_name Value

827

Slow_queries 0

828

flush status;

829

select a from t1 where b not in (1,2,3) union select a from t1 where b not in (4,5,6);

830

831

832

833

834

835

836

837

838

839

840

841

show status like 'Slow_queries';

842

Variable_name Value

843

Slow_queries 0

844

drop table t1;

845

create table t1 ( RID int not null default '0', IID int not null default '0', nada varchar(50) not null,NAME varchar(50) not null,PHONE varchar(50) not null);

846

insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777');

847

select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 A left join t1 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null) union select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 B left join t1 A on B.NAME = A.NAME and A.IID = 1 where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null);

848

NAME PHONE NAME PHONE

849

a 111 NULL NULL

850

b 222 NULL NULL

851

d 444 d 454

852

NULL NULL f 666

853

NULL NULL g 777

854

drop table t1;

855

create table t1 (col1 int, col2 int);

856

insert into t1 values (1,2),(3,4),(5,6),(7,8),(9,10);

857

select col1 n from t1 union select col2 n from t1 order by n;

858

859

860

861

862

863

864

865

866

867

868

869

alter table t1 add index myindex (col2);

870

select col1 n from t1 union select col2 n from t1 order by n;

871

872

873

874

875

876

877

878

879

880

881

882

drop table t1;

883

create table t1 (i int);

884

insert into t1 values (1);

885

select * from t1 UNION select * from t1;

886

887

888

select * from t1 UNION ALL select * from t1;

889

890

891

892

select * from t1 UNION select * from t1 UNION ALL select * from t1;

893

894

895

896

drop table t1;

897

select 1 as a union all select 1 union all select 2 union select 1 union all select 2;

898

899

900

901

902

set sql_select_limit=1;

903

select 1 union select 2;

904

905

906

(select 1) union (select 2);

907

908

909

(select 1) union (select 2) union (select 3) limit 2;

910

911

912

913

set sql_select_limit=default;

914

create table t1 (a int);

915

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

916

create table t2 (a int);

917

insert into t2 values (100);

918

select a from t1 union select a from t2 order by a;

919

920

921

100

922

SET SQL_SELECT_LIMIT=1;

923

select a from t1 union select a from t2 order by a;

924

925

926

drop table t1, t2;

927

set sql_select_limit=default;

928

CREATE TABLE t1 (i int default NULL,c char(1) default NULL,KEY i (i));

929

CREATE TABLE t2 (i int default NULL,c char(1) default NULL,KEY i (i));

930

explain (select * from t1) union (select * from t2) order by not_existing_column;

931

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

932

drop table t1, t2;

933

CREATE TABLE t1 (uid int);

934

INSERT INTO t1 SELECT 150;

935

SELECT 'a' UNION SELECT uid FROM t1;

936

937

938

150

939

drop table t1;

940

CREATE TABLE t1 ( ID1 int NOT NULL DEFAULT '0' , ID2 datetime, DATA1 varchar(10) , DATA2 double(5,4) , DATA3 datetime , PRIMARY KEY (ID1,ID2));

941

CREATE TABLE t2 ( ID int NOT NULL DEFAULT '0' , DATA1 timestamp NULL, PRIMARY KEY (ID));

942

(SELECT * FROM t1 AS PARTITIONED, t2 AS

943

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

944

(SELECT * FROM t1 AS PARTITIONED, t2 AS

945

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

946

(SELECT * FROM t1 AS PARTITIONED, t2 AS

947

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

948

(SELECT * FROM t1 AS PARTITIONED, t2 AS

949

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

950

(SELECT * FROM t1 AS PARTITIONED, t2 AS

951

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

952

(SELECT * FROM t1 AS PARTITIONED, t2 AS

953

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

954

(SELECT * FROM t1 AS PARTITIONED, t2 AS

955

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

956

(SELECT * FROM t1 AS PARTITIONED, t2 AS

957

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

958

(SELECT * FROM t1 AS PARTITIONED, t2 AS

959

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

960

(SELECT * FROM t1 AS PARTITIONED, t2 AS

961

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

962

(SELECT * FROM t1 AS PARTITIONED, t2 AS

963

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1) UNION

964

(SELECT * FROM t1 AS PARTITIONED, t2 AS

965

PARTITIONED_B WHERE PARTITIONED_B.ID=PARTITIONED.ID1);

966

ID1 ID2 DATA1 DATA2 DATA3 ID DATA1

967

drop table t1,t2;

968

create table t1 (a ENUM('Yes', 'No') NOT NULL);

969

create table t2 (a ENUM('aaa', 'bbb') NOT NULL);

970

insert into t1 values ('No');

971

insert into t2 values ('bbb');

972

create table t3 (a ENUM('Yes', 'No') NOT NULL);

973

create table t4 (a ENUM('aaa', 'bbb') NOT NULL);

974

insert into t3 values (1);

975

insert into t4 values (3);

976

ERROR HY000: Received an invalid enum value '3'.

977

select "1" as a union select a from t1;

978

979

980

981

select a as a from t1 union select "1";

982

983

984

985

select a as a from t2 union select a from t1;

986

987

bbb

988

989

select "1" as a union select a from t3;

990

991

992

Yes

993

select a as a from t3 union select "1";

994

995

Yes

996

997

select a as a from t4 union select a from t3;

998

999

Yes

1000

select a as a from t1 union select a from t4;

1001

1002

1003

drop table t1,t2,t3,t4;

1004

create table t1 as

1005

(select 'test') union

1006

(select 'TEST') union

1007

(select 'TeST');

1008

show create table t1;

1009

Table Create Table

1010

t1 CREATE TABLE `t1` (

1011

`test` varchar(4) NOT NULL DEFAULT ''

1012

) ENGINE=DEFAULT

1013

select count(*) from t1;

1014

count(*)

1015

1016

drop table t1;

1017

create table t1 as

1018

(select 'test' collate utf8_bin) union

1019

(select 'TEST') union

1020

(select 'TeST');

1021

show create table t1;

1022

Table Create Table

1023

t1 CREATE TABLE `t1` (

1024

`'test' collate utf8_bin` varchar(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL

1025

) ENGINE=DEFAULT

1026

select count(*) from t1;

1027

count(*)

1028

1029

drop table t1;

1030

create table t1 as

1031

(select 'test') union

1032

(select 'TEST' collate utf8_bin) union

1033

(select 'TeST');

1034

show create table t1;

1035

Table Create Table

1036

t1 CREATE TABLE `t1` (

1037

`test` varchar(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL

1038

) ENGINE=DEFAULT

1039

select count(*) from t1;

1040

count(*)

1041

1042

drop table t1;

1043

create table t1 as

1044

(select 'test') union

1045

(select 'TEST') union

1046

(select 'TeST' collate utf8_bin);

1047

show create table t1;

1048

Table Create Table

1049

t1 CREATE TABLE `t1` (

1050

`test` varchar(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL

1051

) ENGINE=DEFAULT

1052

select count(*) from t1;

1053

count(*)

1054

1055

drop table t1;

1056

create table t2 (

1057

a char character set utf8 collate utf8_swedish_ci,

1058

b char character set utf8 collate utf8_spanish_ci);

1059

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'character set utf8 collate utf8_swedish_ci,

1060

b char character set utf8 collate ut' at line 2

1061

create table t2 (

1062

a char collate utf8_swedish_ci,

1063

b char collate utf8_spanish_ci);

1064

create table t1 as

1065

(select a from t2) union

1066

(select b from t2);

1067

ERROR HY000: Illegal mix of collations for operation 'UNION'

1068

create table t1 as

1069

(select a collate utf8_swedish_ci from t2) union

1070

(select b from t2);

1071

ERROR 42000: Incorrect column name 'a collate utf8_swedish_ci '

1072

create table t1 as

1073

(select a from t2) union

1074

(select b collate utf8_swedish_ci from t2);

1075

show create table t1;

1076

Table Create Table

1077

t1 CREATE TABLE `t1` (

1078

`a` varchar(1) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL

1079

) ENGINE=DEFAULT

1080

drop table t1;

1081

create table t1 as

1082

(select a from t2) union

1083

(select b from t2) union

1084

(select 'c' collate utf8_spanish_ci from t2);

1085

show create table t1;

1086

Table Create Table

1087

t1 CREATE TABLE `t1` (

1088

`a` varchar(1) CHARACTER SET utf8 COLLATE utf8_spanish_ci DEFAULT NULL

1089

) ENGINE=DEFAULT

1090

drop table t1;

1091

drop table t2;

1092

create table t1(a1 int, f1 char(10));

1093

create table t2

1094

select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a

1095

union

1096

select f2,a1 from (select a1, CAST('2004-12-31' AS DATE) f2 from t1) a

1097

order by f2, a1;

1098

show columns from t2;

1099

Field Type Null Key Default Extra

1100

f2 date YES NULL

1101

a1 int YES NULL

1102

drop table t1, t2;

1103

create table t1 (f1 int);

1104

create table t2 (f1 int, f2 int ,f3 date);

1105

create table t3 (f1 int, f2 char(10));

1106

create table t4

1107

(

1108

select t2.f3 as sdate

1109

from t1

1110

left outer join t2 on (t1.f1 = t2.f1)

1111

inner join t3 on (t2.f2 = t3.f1)

1112

order by t1.f1, t3.f1, t2.f3

1113

)

1114

union

1115

(

1116

select cast('2004-12-31' as date) as sdate

1117

from t1

1118

left outer join t2 on (t1.f1 = t2.f1)

1119

inner join t3 on (t2.f2 = t3.f1)

1120

group by t1.f1

1121

order by t1.f1, t3.f1, t2.f3

1122

)

1123

order by sdate;

1124

show columns from t4;

1125

Field Type Null Key Default Extra

1126

sdate date YES NULL

1127

drop table t1, t2, t3, t4;

1128

create table t1 (a int not null, b char (10) not null);

1129

insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');

1130

select * from ((select * from t1 limit 1)) a;

1131

a b

1132

1 a

1133

select * from ((select * from t1 limit 1) union (select * from t1 limit 1)) a;

1134

a b

1135

1 a

1136

select * from ((select * from t1 limit 1) union (select * from t1 limit 1) union (select * from t1 limit 1)) a;

1137

a b

1138

1 a

1139

select * from ((((select * from t1))) union (select * from t1) union (select * from t1)) a;

1140

a b

1141

1 a

1142

2 b

1143

3 c

1144

select * from ((select * from t1) union (((select * from t1))) union (select * from t1)) a;

1145

a b

1146

1 a

1147

2 b

1148

3 c

1149

drop table t1;

1150

set @val:=6;

1151

select concat('value is: ', @val) union select 'some text';

1152

concat('value is: ', @val)

1153

value is: 6

1154

some text

1155

select concat('a', 'b' collate utf8_bin);

1156

concat('a', 'b' collate utf8_bin)

1157

1158

create table t1 (foo varchar(100)) collate utf8_bin;

1159

insert into t1 (foo) values ("foo");

1160

select foo from t1 union select 'bar' as foo from dual;

1161

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

1162

select foo from t1 union select 'bar' as foo;

1163

foo

1164

foo

1165

bar

1166

drop table t1;

1167

create table t1 (f1 decimal(60,25), f2 decimal(60,25));

1168

insert into t1 values (0.0,0.0);

1169

select f1 from t1 union all select f2 from t1;

1170

1171

0.0000000000000000000000000

1172

0.0000000000000000000000000

1173

select 'XXXXXXXXXXXXXXXXXXXX' as description, f1 from t1

1174

union all

1175

select 'YYYYYYYYYYYYYYYYYYYY' as description, f2 from t1;

1176

description f1

1177

XXXXXXXXXXXXXXXXXXXX 0.0000000000000000000000000

1178

YYYYYYYYYYYYYYYYYYYY 0.0000000000000000000000000

1179

drop table t1;

1180

create table t1 (f1 decimal(60,24), f2 decimal(60,24));

1181

insert into t1 values (0.0,0.0);

1182

select f1 from t1 union all select f2 from t1;

1183

1184

0.000000000000000000000000

1185

0.000000000000000000000000

1186

select 'XXXXXXXXXXXXXXXXXXXX' as description, f1 from t1

1187

union all

1188

select 'YYYYYYYYYYYYYYYYYYYY' as description, f2 from t1;

1189

description f1

1190

XXXXXXXXXXXXXXXXXXXX 0.000000000000000000000000

1191

YYYYYYYYYYYYYYYYYYYY 0.000000000000000000000000

1192

drop table t1;

1193

create table t1 (a varchar(5));

1194

create table t2 select * from t1 union select 'abcdefghijkl';

1195

show create table t2;

1196

Table Create Table

1197

t2 CREATE TABLE `t2` (

1198

`a` varchar(5) DEFAULT NULL

1199

) ENGINE=DEFAULT

1200

select row_format from information_schema.TABLES where table_schema="test" and table_name="t2";

1201

row_format

1202

Dynamic

1203

alter table t2 ROW_FORMAT=fixed;

1204

show create table t2;

1205

Table Create Table

1206

t2 CREATE TABLE `t2` (

1207

`a` varchar(5) DEFAULT NULL

1208

) ENGINE=DEFAULT ROW_FORMAT=FIXED

1209

drop table t1,t2;

1210

CREATE TABLE t1 (a mediumtext);

1211

CREATE TABLE t2 (b varchar(20));

1212

INSERT INTO t1 VALUES ('a'),('b');

1213

SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2;

1214

left(a,100000000)

1215

1216

1217

create table t3 SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2;

1218

show create table t3;

1219

Table Create Table

1220

t3 CREATE TABLE `t3` (

1221

`left(a,100000000)` text

1222

) ENGINE=DEFAULT

1223

drop tables t1,t2,t3;

1224

CREATE TABLE t1 (a longtext);

1225

CREATE TABLE t2 (b varchar(20));

1226

INSERT INTO t1 VALUES ('a'),('b');

1227

SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2;

1228

left(a,100000000)

1229

1230

1231

create table t3 SELECT left(a,100000000) FROM t1 UNION SELECT b FROM t2;

1232

show create table t3;

1233

Table Create Table

1234

t3 CREATE TABLE `t3` (

1235

`left(a,100000000)` text

1236

) ENGINE=DEFAULT

1237

drop tables t1,t2,t3;

1238

SELECT @tmp_max:= @@max_allowed_packet;

1239

@tmp_max:= @@max_allowed_packet

1240

1048576

1241

SET max_allowed_packet=25000000;

1242

CREATE TABLE t1 (a mediumtext);

1243

CREATE TABLE t2 (b varchar(20));

1244

INSERT INTO t1 VALUES ('a');

1245

CREATE TABLE t3 SELECT REPEAT(a,20000000) AS a FROM t1 UNION SELECT b FROM t2;

1246

SHOW CREATE TABLE t3;

1247

Table Create Table

1248

t3 CREATE TABLE `t3` (

1249

`a` text

1250

) ENGINE=DEFAULT

1251

DROP TABLES t1,t3;

1252

CREATE TABLE t1 (a tinytext);

1253

INSERT INTO t1 VALUES ('a');

1254

CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2;

1255

SHOW CREATE TABLE t3;

1256

Table Create Table

1257

t3 CREATE TABLE `t3` (

1258

`a` text

1259

) ENGINE=DEFAULT

1260

DROP TABLES t1,t3;

1261

CREATE TABLE t1 (a mediumtext);

1262

INSERT INTO t1 VALUES ('a');

1263

CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2;

1264

SHOW CREATE TABLE t3;

1265

Table Create Table

1266

t3 CREATE TABLE `t3` (

1267

`a` text

1268

) ENGINE=DEFAULT

1269

DROP TABLES t1,t3;

1270

CREATE TABLE t1 (a tinyblob);

1271

INSERT INTO t1 VALUES ('a');

1272

CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2;

1273

SHOW CREATE TABLE t3;

1274

Table Create Table

1275

t3 CREATE TABLE `t3` (

1276

`a` blob

1277

) ENGINE=DEFAULT

1278

DROP TABLES t1,t2,t3;

1279

SET max_allowed_packet:= @tmp_max;

1280

create table t1 ( id int not null auto_increment, primary key (id), col1 int);

1281

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

1282

select 99 union all select id from t1 order by 1;

1283

1284

1285

1286

1287

1288

1289

1290

select id from t1 union all select 99 order by 1;

1291

1292

1293

1294

1295

1296

1297

1298

drop table t1;

1299

create table t1(f1 char(1), f2 char(5), f3 blob, f4 blob, f5 timestamp, f6 varchar(1) collate utf8_general_ci, f7 text);

1300

create table t2 as select *, f6 as f8 from t1 union select *, f7 from t1;

1301

show create table t2;

1302

Table Create Table

1303

t2 CREATE TABLE `t2` (

1304

`f1` varchar(1) DEFAULT NULL,

1305

`f2` varchar(5) DEFAULT NULL,

1306

`f3` blob,

1307

`f4` blob,

1308

`f5` timestamp NULL DEFAULT NULL,

1309

`f6` varchar(1) DEFAULT NULL,

1310

`f7` text,

1311

`f8` text

1312

) ENGINE=DEFAULT

1313

drop table t1, t2;

1314