~vkolesnikov/pbxt/pbxt-preload-test-bug

Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,`tt`.`a` AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`

207

Note 1003 select (select `test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`

208

select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);

209

210

224

2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00

225

3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where

226

Warnings:

227

Note 1276 Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1

227

Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1

228

Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`

229

select * from t3 where exists (select * from t2 where t2.b=t3.a);

230

313

3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 100.00 Using where

314

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL

315

Warnings:

316

Note 1276 Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1

317

Note 1276 Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1

316

Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1

317

Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1

318

Note 1003 select (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`

319

select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;

320

ERROR 21000: Subquery returns more than 1 row

332

1 PRIMARY t6 ALL NULL NULL NULL NULL 4 100.00 Using where

333

2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index

334

Warnings:

335

Note 1276 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1

335

Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1

336

Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))

337

select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);

338

ERROR 23000: Column 'a' in field list is ambiguous

368

2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00

369

3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index

370

Warnings:

371

Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` AS `email` from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1

371

Note 1003 select 'joce' AS `pseudo`,(select 'test' AS `email` from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1

372

SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM

373

t8 WHERE pseudo='joce');

374

ERROR 21000: Operand should contain 1 column(s)

394

id select_type table type possible_keys key key_len ref rows filtered Extra

395

1 SIMPLE t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index

396

Warnings:

397

Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = 20020803)

397

Note 1003 select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = _latin1'2002-08-03')

398

EXPLAIN EXTENDED SELECT (SELECT DISTINCT date FROM t1 WHERE date='2002-08-03');

399

id select_type table type possible_keys key key_len ref rows filtered Extra

400

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used

401

2 SUBQUERY t1 index NULL PRIMARY 43 NULL 2 100.00 Using where; Using index

402

Warnings:

403

Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = 20020803)) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`

403

Note 1003 select (select distinct `test`.`t1`.`date` AS `date` from `test`.`t1` where (`test`.`t1`.`date` = _latin1'2002-08-03')) AS `(SELECT DISTINCT date FROM t1 WHERE date='2002-08-03')`

404

SELECT DISTINCT date FROM t1 WHERE date='2002-08-03';

405

date

406

2002-08-03

421

3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used

422

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL

423

Warnings:

424

Note 1003 select 1 AS `1` from `test`.`t1`

424

Note 1003 select 1 AS `1` from `test`.`t1` where 1

425

drop table t1;

426

CREATE TABLE `t1` (

427

`numeropost` mediumint(8) unsigned NOT NULL auto_increment,

547

1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index

548

2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away

549

Warnings:

550

Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = _latin1'1'))

550

Note 1003 select '3' AS `numreponse` from `test`.`t1` where (('1' = _latin1'1'))

551

drop table t1;

552

CREATE TABLE t1 (a int(1));

553

INSERT INTO t1 VALUES (1);

868

id select_type table type possible_keys key key_len ref rows filtered Extra

869

1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00

870

Warnings:

871

Note 1276 Field or reference 'a' of SELECT #2 was resolved in SELECT #1

871

Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1

872

Note 1249 Select 2 was reduced during optimization

873

Note 1003 select (`test`.`t1`.`a` + 1) AS `(select a+1)` from `test`.`t1`

874

select (select a+1) from t1;

905

id select_type table type possible_keys key key_len ref rows filtered Extra

906

1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index

907

2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 100.00 Using where; Using index

908

2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where

908

2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer

909

Warnings:

910

Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`

911

drop table t1,t2,t3;

1180

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used

1181

2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1182

Warnings:

1183

Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)`

1183

Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`

1184

INSERT INTO t1 (pseudo) VALUES ('test1');

1185

SELECT 0 IN (SELECT 1 FROM t1 a);

1186

0 IN (SELECT 1 FROM t1 a)

1190

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used

1191

2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1192

Warnings:

1193

Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a`)) AS `0 IN (SELECT 1 FROM t1 a)`

1193

Note 1003 select <in_optimizer>(0,<exists>(select 1 AS `Not_used` from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`

1194

drop table t1;

1195

CREATE TABLE `t1` (

1196

`i` int(11) NOT NULL default '0',

1355

id select_type table type possible_keys key key_len ref rows filtered Extra

1356

1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index

1357

2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using where; Using index

1358

2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index

1358

2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer

1359

Warnings:

1360

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))

1361

insert into t1 values (3,31);

1532

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1533

2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00

1534

Warnings:

1535

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3`

1535

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0

1536

select * from t3 where NULL >= any (select b from t2 group by 1);

1537

1538

explain extended select * from t3 where NULL >= any (select b from t2 group by 1);

1540

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1541

2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary; Using filesort

1542

Warnings:

1543

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3`

1543

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0

1544

select * from t3 where NULL >= some (select b from t2);

1545

1546

explain extended select * from t3 where NULL >= some (select b from t2);

1548

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1549

2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00

1550

Warnings:

1551

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3`

1551

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0

1552

select * from t3 where NULL >= some (select b from t2 group by 1);

1553

1554

explain extended select * from t3 where NULL >= some (select b from t2 group by 1);

1556

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

1557

2 SUBQUERY t2 ALL NULL NULL NULL NULL 0 0.00 Using temporary; Using filesort

1558

Warnings:

1559

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3`

1559

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where 0

1560

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

1561

select * from t3 where a > all (select max(b) from t2 group by a);

1562

1618

3 UNION t1 ALL NULL NULL NULL NULL 1 100.00

1619

NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL

1620

Warnings:

1621

Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1`

1621

Note 1003 select `test`.`t1`.`s1` AS `s1` from `test`.`t1` where 1

1622

drop table t1;

1623

CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;

1624

INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');

1743

1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where

1744

2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index

1745

Warnings:

1746

Note 1276 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1

1746

Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1

1747

Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))

1748

insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');

1749

create table t2 (id int not null, text varchar(20) not null default '', primary key (id));

2279

1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00 Using where

2280

2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where

2281

Warnings:

2282

Note 1276 Field or reference 'up.a' of SELECT #2 was resolved in SELECT #1

2282

Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1

2283

Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))

2284

drop table t1;

2285

CREATE TABLE t1 (t1_a int);

2853

2854

2855

DROP TABLE t1,t2,t3;

2856

purge master logs before (select adddate(current_timestamp(), interval -4 day));

2857

2856

CREATE TABLE t1 (f1 INT);

2858

2857

CREATE TABLE t2 (f2 INT);

2859

2858

INSERT INTO t1 VALUES (1);

3468

3467

ORDER BY t1.t DESC LIMIT 1);

3469

3468

id select_type table type possible_keys key key_len ref rows Extra

3470

3469

1 PRIMARY t2 ALL NULL NULL NULL NULL 1

3471

1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index

3470

1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index; Using join buffer

3472

3471

2 DEPENDENT SUBQUERY t1 ref PRIMARY PRIMARY 8 test.t2.i1,const 2 Using where; Using index; Using filesort

3473

3472

SELECT * FROM t1,t2

3474

3473

WHERE t1.t = (SELECT t1.t FROM t1

Older »