~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

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

704

705

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

706

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

707

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

708

Warnings:

709

Note 1249 Select 2 was reduced during optimization

710

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

716

717

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

718

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

719

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

720

Warnings:

721

Note 1249 Select 3 was reduced during optimization

722

Note 1249 Select 2 was reduced during optimization

892

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

893

id select_type table type possible_keys key key_len ref rows filtered Extra

894

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

895

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

896

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

897

Warnings:

898

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

drop table t1,t2,t3;

900

#LIMIT is not supported now

901

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

902

#--error ER_NOT_SUPPORTED_YET

902

#-- error 1235

903

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

904

#--error ER_NOT_SUPPORTED_YET

904

#-- error 1235

905

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

906

#drop table t1;

907

925

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

926

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

927

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

928

#--error ER_NOT_SUPPORTED_YET

928

#-- error 1235

929

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

930

#drop table t1;

931

#test of uncacheable subqueries

991

SHOW CREATE TABLE t1;

992

Table Create Table

993

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

994

`a` int NOT NULL DEFAULT '0',

995

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

996

) ENGINE=MyISAM

997

drop table t1;

998

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

999

SHOW CREATE TABLE t1;

1000

Table Create Table

1001

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

1002

`a` int NOT NULL DEFAULT '0',

1003

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

1004

) ENGINE=MyISAM

1005

drop table t1;

1006

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

1007

SHOW CREATE TABLE t1;

1008

Table Create Table

1009

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

1010

`a` int NOT NULL DEFAULT '0',

1011

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

1012

) ENGINE=MyISAM

1013

drop table t1;

1014

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

1015

select * from t1;

1018

SHOW CREATE TABLE t1;

1019

Table Create Table

1020

t1 CREATE TEMPORARY TABLE `t1` (

1021

`a` BIGINT NOT NULL

1022

) ENGINE=MyISAM COLLATE = utf8_general_ci

1021

`a` bigint NOT NULL

1022

) ENGINE=MyISAM

1023

drop table t1;

1024

create table t1 (a int);

1025

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

1036

# error in IN

1037

1038

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'

1039

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

1040

1041

# complex subquery

1042

1330

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

1331

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

1332

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

1333

#--error ER_CANT_AGGREGATE_2COLLATIONS

1333

#-- error 1267

1334

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

1335

#--error ER_CANT_AGGREGATE_2COLLATIONS

1335

#-- error 1267

1336

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

1337

#drop table t1;

1338

1434

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

1435

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

1436

Warnings:

1437

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

1437

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

1438

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

1439

1440

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

1442

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

1443

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

1444

Warnings:

1445

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

1445

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

1446

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

1447

1448

1453

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

1454

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

1455

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

1456

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

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

1458

1459

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

1461

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

1462

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

1463

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

1464

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

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

1466

1467

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

2889

id select_type table type possible_keys key key_len ref rows Extra

2890

1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index

2891

1 PRIMARY r const PRIMARY PRIMARY 4 const 1

2892

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

2892

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

2893

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

2894

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

2895

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

2966

SHOW CREATE TABLE t2;

2967

Table Create Table

2968

t2 CREATE TEMPORARY TABLE `t2` (

2969

`sub_a` DATETIME DEFAULT NULL

2970

) ENGINE=MyISAM COLLATE = utf8_general_ci

2969

`sub_a` datetime DEFAULT NULL

2970

) ENGINE=MyISAM

2971

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

SHOW CREATE TABLE t3;

2973

Table Create Table

2974

t3 CREATE TEMPORARY TABLE `t3` (

2975

`a` DATETIME DEFAULT NULL

2976

) ENGINE=MyISAM COLLATE = utf8_general_ci

2975

`a` datetime DEFAULT NULL

2976

) ENGINE=MyISAM

2977

DROP TABLE t1,t2,t3;

2978

2979

# Bug 24653: sorting by expressions containing subselects

4524

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

4525

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

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

4527

ERROR HY000: Implicit cartesian join attempted.

4527

a a

4528

1 1

4529

2 1

4530

1 2

4531

2 2

4528

4532

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

4529

ERROR HY000: Implicit cartesian join attempted.

4533

a a

4534

1 1

4535

2 1

4536

1 2

4537

2 2

4530

4538

SELECT * FROM (t1 t1a);

4531

4539

4532

4540

Older »