~drizzle-trunk/drizzle/development

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

405

408

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

430

433

`maxnumrep` int NOT NULL default '0',

431

434

PRIMARY KEY (`numeropost`),

432

435

UNIQUE KEY `maxnumrep` (`maxnumrep`)

433

);

436

) ROW_FORMAT=FIXED;

437

Warnings:

438

Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

434

439

INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);

435

440

CREATE TABLE `t2` (

436

441

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

438

443

`date` date NULL,

439

444

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

440

445

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

441

);

446

) ROW_FORMAT=DYNAMIC;

447

Warnings:

448

Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table.

449

Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

442

450

INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');

443

451

select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);

444

452

503

511

`maxnumrep` int NOT NULL default '0',

504

512

PRIMARY KEY (`numeropost`),

505

513

UNIQUE KEY `maxnumrep` (`maxnumrep`)

506

);

514

) ROW_FORMAT=FIXED;

515

Warnings:

516

Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

507

517

INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);

508

518

select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);

509

519

ERROR 21000: Subquery returns more than 1 row

704

714

705

715

EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);

706

716

id select_type table type possible_keys key key_len ref rows filtered Extra

707

1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index

717

1 PRIMARY t2 ref id id 5 const 1 100.00 Using index

708

718

Warnings:

709

719

Note 1249 Select 2 was reduced during optimization

710

720

Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)

716

726

717

727

EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));

718

728

id select_type table type possible_keys key key_len ref rows filtered Extra

719

1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index

729

1 PRIMARY t2 ref id id 5 const 1 100.00 Using index

720

730

Warnings:

721

731

Note 1249 Select 3 was reduced during optimization

722

732

Note 1249 Select 2 was reduced during optimization

723

Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = <cache>((1 + 1)))

733

Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = (1 + 1))

724

734

EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);

725

735

id select_type table type possible_keys key key_len ref rows filtered Extra

726

736

1 PRIMARY t2 index NULL id 5 NULL 2 100.00 Using where; Using index

892

902

explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;

893

903

id select_type table type possible_keys key key_len ref rows filtered Extra

894

904

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

895

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

905

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

896

906

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

897

907

Warnings:

898

908

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`

899

909

drop table t1,t2,t3;

900

910

#LIMIT is not supported now

901

911

#create table t1 (a float) ENGINE=MyISAM;

902

#--error ER_NOT_SUPPORTED_YET

912

#-- error 1235

903

913

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

904

#--error ER_NOT_SUPPORTED_YET

914

#-- error 1235

905

915

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

906

916

#drop table t1;

907

917

925

935

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

926

936

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

927

937

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

928

#--error ER_NOT_SUPPORTED_YET

938

#-- error 1235

929

939

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

930

940

#drop table t1;

931

941

#test of uncacheable subqueries

991

1001

SHOW CREATE TABLE t1;

992

1002

Table Create Table

993

1003

t1 CREATE TEMPORARY TABLE `t1` (

994

`a` INT NOT NULL DEFAULT '0',

995

`(SELECT 1)` INT NOT NULL DEFAULT '0'

996

) ENGINE=MyISAM COLLATE = utf8_general_ci

1004

`a` int NOT NULL DEFAULT '0',

1005

`(SELECT 1)` int NOT NULL DEFAULT '0'

1006

) ENGINE=MyISAM

997

1007

drop table t1;

998

1008

CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a)) a;

999

1009

SHOW CREATE TABLE t1;

1000

1010

Table Create Table

1001

1011

t1 CREATE TEMPORARY TABLE `t1` (

1002

`a` INT NOT NULL DEFAULT '0',

1003

`(SELECT a)` INT NOT NULL DEFAULT '0'

1004

) ENGINE=MyISAM COLLATE = utf8_general_ci

1012

`a` int NOT NULL DEFAULT '0',

1013

`(SELECT a)` int NOT NULL DEFAULT '0'

1014

) ENGINE=MyISAM

1005

1015

drop table t1;

1006

1016

CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;

1007

1017

SHOW CREATE TABLE t1;

1008

1018

Table Create Table

1009

1019

t1 CREATE TEMPORARY TABLE `t1` (

1010

`a` INT NOT NULL DEFAULT '0',

1011

`(SELECT a+0)` INT NOT NULL DEFAULT '0'

1012

) ENGINE=MyISAM COLLATE = utf8_general_ci

1020

`a` int NOT NULL DEFAULT '0',

1021

`(SELECT a+0)` int NOT NULL DEFAULT '0'

1022

) ENGINE=MyISAM

1013

1023

drop table t1;

1014

1024

CREATE TEMPORARY TABLE t1 ENGINE=MyISAM SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;

1015

1025

select * from t1;

1018

1028

SHOW CREATE TABLE t1;

1019

1029

Table Create Table

1020

1030

t1 CREATE TEMPORARY TABLE `t1` (

1021

`a` BIGINT NOT NULL

1022

) ENGINE=MyISAM COLLATE = utf8_general_ci

1031

`a` bigint NOT NULL

1032

) ENGINE=MyISAM

1023

1033

drop table t1;

1024

1034

create table t1 (a int);

1025

1035

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

1036

1046

# error in IN

1037

1047

1038

1048

select t1.Continent, t2.Name, t2.Population from t1 LEFT JOIN t2 ON t1.Code = t2.Country where t2.Population IN (select max(t2.Population) AS Population from t2, t1 where t2.Country = t1.Code group by Continent);

1039

ERROR 42S02: Unknown table 'test.t1'

1049

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

1040

1050

1041

1051

# complex subquery

1042

1052

1330

1340

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

1331

1341

# s2 CHAR(5) COLLATE latin1_swedish_ci) ENGINE=MyISAM;

1332

1342

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

1333

#--error ER_CANT_AGGREGATE_2COLLATIONS

1343

#-- error 1267

1334

1344

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

1335

#--error ER_CANT_AGGREGATE_2COLLATIONS

1345

#-- error 1267

1336

1346

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

1337

1347

#drop table t1;

1338

1348

1434

1444

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

1435

1445

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

1436

1446

Warnings:

1437

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

1447

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

1438

1448

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

1439

1449

1440

1450

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

1442

1452

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

1443

1453

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

1444

1454

Warnings:

1445

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

1455

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

1446

1456

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

1447

1457

1448

1458

1453

1463

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

1454

1464

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

1455

1465

Warnings:

1456

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

1466

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

1457

1467

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

1458

1468

1459

1469

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

1461

1471

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

1462

1472

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

1463

1473

Warnings:

1464

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

1474

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

1465

1475

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

1466

1476

1467

1477

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

2889

2899

id select_type table type possible_keys key key_len ref rows Extra

2890

2900

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index

2891

2901

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2892

2 DEPENDENT SUBQUERY t2 ref b b 5 3 Using where; Using index

2902

2 DEPENDENT SUBQUERY t2 ref b b 5 1 Using where; Using index

2893

2903

SELECT t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r

2894

2904

ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899'

2895

2905

ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10;

2966

2976

SHOW CREATE TABLE t2;

2967

2977

Table Create Table

2968

2978

t2 CREATE TEMPORARY TABLE `t2` (

2969

`sub_a` DATETIME DEFAULT NULL

2970

) ENGINE=MyISAM COLLATE = utf8_general_ci

2979

`sub_a` datetime DEFAULT NULL

2980

) ENGINE=MyISAM

2971

2981

CREATE TEMPORARY TABLE t3 ENGINE=MyISAM AS (SELECT a FROM t1 WHERE a < '2000-01-01') UNION (SELECT a FROM t1 WHERE a > '2000-01-01');

2972

2982

SHOW CREATE TABLE t3;

2973

2983

Table Create Table

2974

2984

t3 CREATE TEMPORARY TABLE `t3` (

2975

`a` DATETIME DEFAULT NULL

2976

) ENGINE=MyISAM COLLATE = utf8_general_ci

2985

`a` datetime DEFAULT NULL

2986

) ENGINE=MyISAM

2977

2987

DROP TABLE t1,t2,t3;

2978

2988

2979

2989

# Bug 24653: sorting by expressions containing subselects

3964

3974

a varchar(255) default NULL,

3965

3975

b timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

3966

3976

INDEX idx(a,b));

3977

Warnings:

3978

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

3967

3979

CREATE TABLE t2 (

3968

3980

a varchar(255) default NULL);

3969

3981

INSERT INTO t1 VALUES ('abcdefghijk','2007-05-07 06:00:24');

4524

4536

SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1;

4525

4537

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 't1a ON 1' at line 1

4526

4538

SELECT * FROM t1 JOIN (t1 t1a) ON 1;

4527

ERROR HY000: Implicit cartesian join attempted.

4539

a a

4540

1 1

4541

2 1

4542

1 2

4543

2 2

4528

4544

SELECT * FROM t1 JOIN ((t1 t1a)) ON 1;

4529

ERROR HY000: Implicit cartesian join attempted.

4545

a a

4546

1 1

4547

2 1

4548

1 2

4549

2 2

4530

4550

SELECT * FROM (t1 t1a);

4531

4551

4532

4552

Older »