~mdcallag/+junk/5.1-map

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

315

Warnings:

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`

318

Note 1003 select (select '2' AS `a` from `test`.`t1` where ('2' = `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

321

create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));

330

explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);

331

id select_type table type possible_keys key key_len ref rows filtered Extra

332

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

333

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

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 '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`))

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)

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);

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',

1430

id select_type table type possible_keys key key_len ref rows filtered Extra

1431

1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00

1432

Warnings:

1433

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

1433

Note 1003 (select 'tttt' AS `s1` from `test`.`t1`)

1434

(select * from t1);

1435

1436

tttt

1497

1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where

1498

2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found

1499

Warnings:

1500

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max(`test`.`t2`.`b`) from `test`.`t2`)))

1500

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < (select max('0') from `test`.`t2`)))

1501

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

1502

1503

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

1505

1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where

1506

2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found

1507

Warnings:

1508

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min(`test`.`t2`.`b`) from `test`.`t2`)))

1508

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= (select min('0') from `test`.`t2`)))

1509

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

1510

1511

1516

1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where

1517

2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found

1518

Warnings:

1519

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select `test`.`t2`.`b` AS `b` from `test`.`t2` group by 1)))

1519

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>((`test`.`t3`.`a` < <max>(select '0' AS `b` from `test`.`t2` group by 1)))

1520

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

1521

1522

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

1524

1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where

1525

2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found

1526

Warnings:

1527

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select `test`.`t2`.`b` AS `b` from `test`.`t2` group by 1)))

1527

Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>((`test`.`t3`.`a` >= <min>(select '0' AS `b` from `test`.`t2` group by 1)))

1528

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

1529

1530

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

1532

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

1533

2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found

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 system NULL NULL NULL NULL 0 0.00 const row not found

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 system NULL NULL NULL NULL 0 0.00 const row not found

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 system NULL NULL NULL NULL 0 0.00 const row not found

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 system 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 'e' 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');

1741

explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);

1742

id select_type table type possible_keys key key_len ref rows filtered Extra

1743

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

1744

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

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 '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))))

3712

cc 3

3713

dd 1

3714

DROP TABLE t1,t2,t3;

3715

CREATE TABLE t1 (s1 char(1));

3716

INSERT INTO t1 VALUES ('a');

3717

SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);

3718

3719

3720

DROP TABLE t1;

3721

3715

CREATE TABLE t1(f1 int);

3722

3716

CREATE TABLE t2(f2 int, f21 int, f3 timestamp);

3723

3717

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

3873

3867

DROP TABLE t1;

3874

3868

DROP TABLE t2;

3875

3869

DROP TABLE t1xt2;

3870

CREATE TABLE t1 (a int);

3871

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

3872

SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;

3873

col1 col2

3874

this is a test. 3

3875

this is a test. 1

3876

this is a test. 2

3877

SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;

3878

col1 t2

3879

this is a test. 3

3880

this is a test. 1

3881

this is a test. 2

3882

DROP table t1;

3883

CREATE TABLE t1 (a int, b int);

3884

CREATE TABLE t2 (m int, n int);

3885

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

3886

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3887

SELECT COUNT(*), a,

3888

(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)

3889

FROM t1 GROUP BY a;

3890

COUNT(*) a (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)

3891

2 2 2

3892

3 3 3

3893

1 4 1

3894

SELECT COUNT(*), a,

3895

(SELECT MIN(m) FROM t2 WHERE m = count(*))

3896

FROM t1 GROUP BY a;

3897

COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*))

3898

2 2 2

3899

3 3 3

3900

1 4 1

3901

SELECT COUNT(*), a

3902

FROM t1 GROUP BY a

3903

HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;

3904

COUNT(*) a

3905

2 2

3906

3 3

3907

DROP TABLE t1,t2;

3908

CREATE TABLE t1 (a int, b int);

3909

CREATE TABLE t2 (m int, n int);

3910

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

3911

INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);

3912

SELECT COUNT(*) c, a,

3913

(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)

3914

FROM t1 GROUP BY a;

3915

c a (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)

3916

2 2 2

3917

3 3 3

3918

1 4 1,1

3919

SELECT COUNT(*) c, a,

3920

(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)

3921

FROM t1 GROUP BY a;

3922

c a (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)

3923

2 2 3

3924

3 3 4

3925

1 4 2,2

3926

DROP table t1,t2;

3927

CREATE TABLE t1 (a int, b INT, d INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));

3928

INSERT INTO t1 VALUES (1,1,0,'a'), (1,2,0,'b'), (1,3,0,'c'), (1,4,0,'d'),

3929

(1,5,0,'e'), (2,1,0,'f'), (2,2,0,'g'), (2,3,0,'h'), (3,4,0,'i'), (3,3,0,'j'),

3930

(3,2,0,'k'), (3,1,0,'l'), (1,9,0,'m'), (1,0,10,'n'), (2,0,5,'o'), (3,0,7,'p');

3931

SELECT a, MAX(b),

3932

(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b + 0)) as test

3933

FROM t1 GROUP BY a;

3934

a MAX(b) test

3935

1 9 m

3936

2 3 h

3937

3 4 i

3938

SELECT a x, MAX(b),

3939

(SELECT t.c FROM t1 AS t WHERE x=t.a AND t.b=MAX(t1.b + 0)) as test

3940

FROM t1 GROUP BY a;

3941

x MAX(b) test

3942

1 9 m

3943

2 3 h

3944

3 4 i

3945

SELECT a, AVG(b),

3946

(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) AS test

3947

FROM t1 WHERE t1.d=0 GROUP BY a;

3948

a AVG(b) test

3949

1 4.0000 d

3950

2 2.0000 g

3951

3 2.5000 NULL

3952

SELECT tt.a,

3953

(SELECT (SELECT c FROM t1 as t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3954

LIMIT 1) FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test

3955

FROM t1 as tt;

3956

a test

3957

1 n

3958

1 n

3959

1 n

3960

1 n

3961

1 n

3962

1 n

3963

1 n

3964

2 o

3965

2 o

3966

2 o

3967

2 o

3968

3 p

3969

3 p

3970

3 p

3971

3 p

3972

3 p

3973

SELECT tt.a,

3974

(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3975

LIMIT 1)

3976

FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1) as test

3977

FROM t1 as tt GROUP BY tt.a;

3978

a test

3979

1 n

3980

2 o

3981

3 p

3982

SELECT tt.a, MAX(

3983

(SELECT (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.d=MAX(t1.b + tt.a)

3984

LIMIT 1)

3985

FROM t1 WHERE t1.a=tt.a GROUP BY a LIMIT 1)) as test

3986

FROM t1 as tt GROUP BY tt.a;

3987

a test

3988

1 n

3989

2 o

3990

3 p

3991

DROP TABLE t1;

3992

CREATE TABLE t1 (a int, b int);

3993

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

3994

SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a;

3995

3996

3997

3998

SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a;

3999

4000

SELECT a FROM t1 t0

4001

WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;

4002

4003

4004

4005

SET @@sql_mode='ansi';

4006

SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a;

4007

ERROR HY000: Invalid use of group function

4008

SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a;

4009

ERROR HY000: Invalid use of group function

4010

SELECT a FROM t1 t0

4011

WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;

4012

ERROR HY000: Invalid use of group function

4013

SET @@sql_mode=default;

4014

DROP TABLE t1;

4015

CREATE TABLE t1 (s1 char(1));

4016

INSERT INTO t1 VALUES ('a');

4017

SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);

4018

4019

4020

DROP TABLE t1;

Older »