~drizzle-trunk/drizzle/development

EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');

360

id select_type table type possible_keys key key_len ref rows filtered Extra

361

1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index

362

4 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index

363

2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00

364

3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index

361

1 PRIMARY t8 const PRIMARY PRIMARY 142 const 1 100.00 Using index

362

4 SUBQUERY t8 const PRIMARY PRIMARY 142 1 100.00 Using index

363

2 SUBQUERY t8 const PRIMARY PRIMARY 142 const 1 100.00

364

3 SUBQUERY t8 const PRIMARY PRIMARY 142 1 100.00 Using index

365

Warnings:

366

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

366

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

367

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

368

t8 WHERE pseudo='joce');

369

ERROR 21000: Operand should contain 1 column(s)

379

CREATE TABLE `t1` (

380

`topic` bigint unsigned NOT NULL default '0',

381

`date` date NOT NULL default '0000-00-00',

382

`pseudo` varchar(35) character set latin1 NOT NULL default '',

382

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

383

PRIMARY KEY (`pseudo`,`date`,`topic`),

384

KEY `topic` (`topic`)

385

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

387

('43506','2002-10-02','joce'),('40143','2002-08-03','joce');

388

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

389

id select_type table type possible_keys key key_len ref rows filtered Extra

390

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

390

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

391

Warnings:

392

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

393

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

394

id select_type table type possible_keys key key_len ref rows filtered Extra

395

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

396

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

396

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

397

Warnings:

398

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

399

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

904

Warnings:

905

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`

906

drop table t1,t2,t3;

907

create table t1 (a float);

908

select 10.5 IN (SELECT * from t1 LIMIT 1);

909

ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

910

select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);

911

ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

912

drop table t1;

913

create table t1 (a int, b int, c varchar(10));

914

create table t2 (a int);

915

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

916

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

917

select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a'),(select c from t1 where a=t2.a) from t2;

918

a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a') (select c from t1 where a=t2.a)

919

1 1 a

920

2 0 b

921

NULL 0 NULL

922

select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b'),(select c from t1 where a=t2.a) from t2;

923

a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,3,'b') (select c from t1 where a=t2.a)

924

1 0 a

925

2 1 b

926

NULL NULL NULL

927

select a, (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c'),(select c from t1 where a=t2.a) from t2;

928

a (select a,b,c from t1 where t1.a=t2.a) = ROW(a,4,'c') (select c from t1 where a=t2.a)

929

1 0 a

930

2 0 b

931

NULL 0 NULL

932

drop table t1,t2;

933

create table t1 (a int, b real, c varchar(10));

934

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

935

select ROW(1, 1, 'a') IN (select a,b,c from t1);

936

ROW(1, 1, 'a') IN (select a,b,c from t1)

937

938

select ROW(1, 2, 'a') IN (select a,b,c from t1);

939

ROW(1, 2, 'a') IN (select a,b,c from t1)

940

941

select ROW(1, 1, 'a') IN (select b,a,c from t1);

942

ROW(1, 1, 'a') IN (select b,a,c from t1)

943

944

select ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null);

945

ROW(1, 1, 'a') IN (select a,b,c from t1 where a is not null)

946

947

select ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null);

948

ROW(1, 2, 'a') IN (select a,b,c from t1 where a is not null)

949

950

select ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null);

951

ROW(1, 1, 'a') IN (select b,a,c from t1 where a is not null)

952

953

select ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a');

954

ROW(1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a')

955

956

select ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a');

957

ROW(1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a')

958

959

select ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a');

960

ROW(1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a')

961

962

select ROW(1, 1, 'a') IN (select b,a,c from t1 limit 2);

963

ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

964

drop table t1;

965

907

CREATE TABLE t1 (a int);

966

908

EXPLAIN EXTENDED SELECT (SELECT RAND() FROM t1) FROM t1;

967

909

id select_type table type possible_keys key key_len ref rows filtered Extra

977

919

Note 1003 select (select benchmark(1,1) AS `BENCHMARK(1,1)` from `test`.`t1`) AS `(SELECT BENCHMARK(1,1) FROM t1)` from `test`.`t1`

978

920

drop table t1;

979

921

CREATE TABLE `t1` (

980

`mot` varchar(30) character set latin1 NOT NULL default '',

922

`mot` varchar(30) NOT NULL default '',

981

923

`topic` bigint unsigned NOT NULL default '0',

982

924

`date` date NOT NULL default '0000-00-00',

983

`pseudo` varchar(35) character set latin1 NOT NULL default '',

925

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

984

926

PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),

985

927

KEY `pseudo` (`pseudo`,`date`,`topic`),

986

928

KEY `topic` (`topic`)

987

929

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

988

930

CREATE TABLE `t2` (

989

`mot` varchar(30) character set latin1 NOT NULL default '',

931

`mot` varchar(30) NOT NULL default '',

990

932

`topic` bigint unsigned NOT NULL default '0',

991

933

`date` date NOT NULL default '0000-00-00',

992

`pseudo` varchar(35) character set latin1 NOT NULL default '',

934

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

993

935

PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),

994

936

KEY `pseudo` (`pseudo`,`date`,`topic`),

995

937

KEY `topic` (`topic`)

1104

1046

drop table t1, t2;

1105

1047

CREATE TABLE `t1` (

1106

1048

`id` bigint unsigned NOT NULL auto_increment,

1107

`pseudo` varchar(35) character set latin1 NOT NULL default '',

1049

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

1108

1050

PRIMARY KEY (`id`),

1109

1051

UNIQUE KEY `pseudo` (`pseudo`)

1110

1052

) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;

1339

1281

update t1 set s1 = s1 + 1 where 1 = (select x.s1 as A from t2 WHERE t2.s1 > t1.s1 order by A);

1340

1282

ERROR 42S22: Unknown column 'x.s1' in 'field list'

1341

1283

DROP TABLE t1, t2;

1342

CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci,

1343

s2 CHAR(5) COLLATE latin1_swedish_ci);

1344

INSERT INTO t1 VALUES ('z','?');

1345

select * from t1 where s1 > (select max(s2) from t1);

1346

ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'

1347

select * from t1 where s1 > any (select max(s2) from t1);

1348

ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '>'

1349

drop table t1;

1350

1284

create table t1(toid int,rd int);

1351

1285

create table t2(userid int,pmnew int,pmtotal int);

1352

1286

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

1398

1332

a3 1

1399

1333

explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;

1400

1334

id select_type table type possible_keys key key_len ref rows filtered Extra

1401

1 PRIMARY t1 index NULL s1 8 NULL 3 100.00 Using index

1402

2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 8 func 2 100.00 Using index; Full scan on NULL key

1335

1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index

1336

2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key

1403

1337

Warnings:

1404

1338

Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`

1405

1339

explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;

1406

1340

id select_type table type possible_keys key key_len ref rows filtered Extra

1407

1 PRIMARY t1 index NULL s1 8 NULL 3 100.00 Using index

1408

2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 8 func 2 100.00 Using index; Full scan on NULL key

1341

1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index

1342

2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key

1409

1343

Warnings:

1410

1344

Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`

1411

1345

explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;

1412

1346

id select_type table type possible_keys key key_len ref rows filtered Extra

1413

1 PRIMARY t1 index NULL s1 8 NULL 3 100.00 Using index

1414

2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 8 func 2 100.00 Using index; Full scan on NULL key

1347

1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index

1348

2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key

1415

1349

Warnings:

1416

1350

1417

1351

explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;

1418

1352

id select_type table type possible_keys key key_len ref rows filtered Extra

1419

1 PRIMARY t1 index NULL s1 8 NULL 3 100.00 Using index

1420

2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 8 func 2 100.00 Using index; Using where; Full scan on NULL key

1353

1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index

1354

2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Using where; Full scan on NULL key

1421

1355

Warnings:

1422

1356

Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`

1423

1357

drop table t1,t2;

2228

2162

a C

2229

2163

1 1

2230

2164

drop table t1,t2;

2231

CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`KUERZEL` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,`IZAANALYSEART_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin,`IZAPMKZ_ID` VARCHAR(11) CHARACTER SET latin1 COLLATE latin1_bin);

2165

CREATE TABLE t1(`IZAVORGANG_ID` VARCHAR(11) COLLATE utf8_bin,`KUERZEL` VARCHAR(10) COLLATE utf8_bin,`IZAANALYSEART_ID` VARCHAR(11) COLLATE utf8_bin,`IZAPMKZ_ID` VARCHAR(11) COLLATE utf8_bin);

2232

2166

CREATE INDEX AK01IZAVORGANG ON t1(izaAnalyseart_id,Kuerzel);

2233

2167

INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000001','601','D0000000001','I0000000001');

2234

2168

INSERT INTO t1(`IZAVORGANG_ID`,`KUERZEL`,`IZAANALYSEART_ID`,`IZAPMKZ_ID`)VALUES('D0000000002','602','D0000000001','I0000000001');

2527

2461

select (select * from t1) = (1,2,3);

2528

2462

ERROR 21000: Operand should contain 2 column(s)

2529

2463

drop table t1;

2530

CREATE TABLE `t1` (

2531

`itemid` bigint unsigned NOT NULL auto_increment,

2532

`sessionid` bigint unsigned default NULL,

2533

`time` int unsigned NOT NULL default '0',

2534

`data` text collate latin1_general_ci NOT NULL,

2535

PRIMARY KEY (`itemid`)

2536

);

2537

INSERT INTO `t1` VALUES (1, 1, 1, '');

2538

CREATE TABLE `t2` (

2539

`sessionid` bigint unsigned NOT NULL auto_increment,

2540

`pid` int unsigned NOT NULL default '0',

2541

`date` int unsigned NOT NULL default '0',

2542

`ip` varchar(15) collate latin1_general_ci NOT NULL default '',

2543

PRIMARY KEY (`sessionid`)

2544

);

2545

INSERT INTO `t2` VALUES (1, 1, 1, '10.10.10.1');

2546

SELECT s.ip, count( e.itemid ) FROM `t1` e JOIN t2 s ON s.sessionid = e.sessionid WHERE e.sessionid = ( SELECT sessionid FROM t2 ORDER BY sessionid DESC LIMIT 1 ) GROUP BY s.ip HAVING count( e.itemid ) >0 LIMIT 0 , 30;

2547

ip count( e.itemid )

2548

10.10.10.1 1

2549

drop tables t1,t2;

2550

2464

create table t1 (fld enum('0','1'));

2551

2465

insert into t1 values ('1');

2552

2466

select * from (select max(fld) from t1) as foo;

3173

3087

SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);

3174

3088

id select_type table type possible_keys key key_len ref rows Extra

3175

3089

1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where

3176

2 SUBQUERY t1 index NULL a 8 NULL 9

3090

2 SUBQUERY t1 index NULL a 23 NULL 9

3177

3091

DROP TABLE t1;

3178

3092

create table t1( f1 int,f2 int);

3179

3093

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

3715

3629

b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

3716

3630

INDEX idx(a,b)

3717

3631

);

3632

Warnings:

3633

Warning 1071 Specified key was too long; max key length is 767 bytes

3718

3634

CREATE TABLE t2 (

3719

3635

a varchar(255) default NULL

3720

3636

);

3791

3707

CREATE INDEX I2 ON t1 (b);

3792

3708

EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3793

3709

id select_type table type possible_keys key key_len ref rows Extra

3794

1 PRIMARY t1 index I1 I1 4 NULL 2 Using index; LooseScan

3795

1 PRIMARY t1 ref I2 I2 13 test.t1.a 1 Using index condition

3710

1 PRIMARY t1 index I1 I1 7 NULL 2 Using index; LooseScan

3711

1 PRIMARY t1 ref I2 I2 43 test.t1.a 1 Using index condition

3796

3712

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

3797

3713

a b

3798

3714

CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));

3801

3717

CREATE INDEX I2 ON t2 (b);

3802

3718

EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

3803

3719

id select_type table type possible_keys key key_len ref rows Extra

3804

1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan

3805

1 PRIMARY t2 ref I2 I2 13 test.t2.a 1 Using index condition

3720

1 PRIMARY t2 index I1 I1 7 NULL 2 Using index; LooseScan

3721

1 PRIMARY t2 ref I2 I2 43 test.t2.a 1 Using index condition

3806

3722

SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

3807

3723

a b

3808

3724

EXPLAIN

3809

3725

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

3810

3726

id select_type table type possible_keys key key_len ref rows Extra

3811

1 PRIMARY t1 index I1 I1 4 NULL 2 Using where; Using index; LooseScan

3812

1 PRIMARY t1 ref I2 I2 13 test.t1.a 1 Using index condition

3727

1 PRIMARY t1 index I1 I1 7 NULL 2 Using where; Using index; LooseScan

3728

1 PRIMARY t1 ref I2 I2 43 test.t1.a 1 Using index condition

3813

3729

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

3814

3730

a b

3815

3731

DROP TABLE t1,t2;

Older »