~drizzle-trunk/drizzle/development

--echo # Initialise

--disable_warnings

drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12;

--enable_warnings

select (select 2);

explain extended select (select 2);

SELECT (SELECT 1) UNION SELECT (SELECT 2);

explain extended SELECT (SELECT 1) UNION SELECT (SELECT 2);

SELECT (SELECT (SELECT 0 UNION SELECT 0));

explain extended SELECT (SELECT (SELECT 0 UNION SELECT 0));

-- error 1247

SELECT (SELECT 1 FROM (SELECT 1) as b HAVING a=1) as a;

-- error 1247

SELECT (SELECT 1 FROM (SELECT 1) as b HAVING b=1) as a,(SELECT 1 FROM (SELECT 1) as c HAVING a=1) as b;

SELECT (SELECT 1),MAX(1) FROM (SELECT 1) as a;

-- error 1247

SELECT (SELECT a) as a;

EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b  HAVING (SELECT a)=1;

SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;

-- error 1054

SELECT (SELECT 1), a;

SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;

-- error 1054

SELECT 1 FROM (SELECT (SELECT a) b) c;

SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id);

-- error 1241

SELECT * FROM (SELECT 1) a  WHERE 1 IN (SELECT 1,1);

SELECT 1 IN (SELECT 1);

SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));

-- error ER_BAD_FIELD_ERROR

SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;

-- error ER_BAD_FIELD_ERROR

SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL;

SELECT (SELECT 1,2,3) = ROW(1,2,3);

SELECT (SELECT 1,2,3) = ROW(1,2,1);

SELECT (SELECT 1,2,3) < ROW(1,2,1);

SELECT (SELECT 1,2,3) > ROW(1,2,1);

SELECT (SELECT 1,2,3) = ROW(1,2,NULL);

SELECT ROW(1,2,3) = (SELECT 1,2,3);

SELECT ROW(1,2,3) = (SELECT 1,2,1);

SELECT ROW(1,2,3) < (SELECT 1,2,1);

SELECT ROW(1,2,3) > (SELECT 1,2,1);

SELECT ROW(1,2,3) = (SELECT 1,2,NULL);

SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a');

SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b');

SELECT (SELECT 1.5,2,'a') = ROW('1.5b',2,'b');

SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a');

SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a');

SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a');

-- error 1241

SELECT (SELECT * FROM (SELECT 'test' a,'test' b) a);

SELECT 1 as a,(SELECT a+a) b,(SELECT b);

create table t1 (a int);

create table t2 (a int, b int);

create table t3 (a int);

create table t4 (a int not null, b int not null);

insert into t1 values (2);

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

insert into t4 values (4,8),(3,8),(5,9);

-- error 1247

select (select a from t1 where t1.a = a1) as a2, (select b from t2 where t2.b=a2) as a1;

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

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

select (select a from t1), a, (select 1 union select 2 limit 1) from t2;

select (select a from t3), a from t2;

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

insert into t3 values (6),(7),(3);

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

(select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 order by a limit 2) limit 3;

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

--replace_column 9 #

explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc limit 1)) union (select * from t4 where t4.b=(select max(t2.a)*4 from t2) order by a);

select (select a from t3 where a<t2.a*4 order by 1 desc limit 1), a from t2;

select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 

(select * from t2 where a>1) as tt;

--replace_column 9 #

explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 

(select * from t2 where a>1) as tt;

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

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

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

select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;

--replace_column 9 #

explain extended select b,(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2) from t4;

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

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

select * from t3 where a in (select b from t2);

select * from t3 where a not in (select b from t2);

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

select * from t3 where a <> any (select b from t2);

--echo # Rewrite: select * from t3 where not exists (select b from t2 where a <> b);

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

select * from t3 where a <> all (select b from t2);

insert into t2 values (100, 5);

select * from t3 where a < any (select b from t2);

select * from t3 where a < all (select b from t2);

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

--replace_column 9 #

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

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

delete from t2 where a=100;

-- error 1241

select * from t3 where a in (select a,b from t2);

-- error 1241

select * from t3 where a in (select * from t2);

insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);

--echo # empty set

select b,max(a) as ma from t4 group by b having b < (select max(t2.a) from t2 where t2.b=t4.b);

insert into t2 values (2,10);

select b,max(a) as ma from t4 group by b having ma < (select max(t2.a) from t2 where t2.b=t4.b);

delete from t2 where a=2 and b=10;

select b,max(a) as ma from t4 group by b having b >= (select max(t2.a) from t2 where t2.b=t4.b);

create temporary table t5 (a int) ENGINE=MyISAM;

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

insert into t5 values (5);

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

insert into t5 values (2);

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

--replace_column 9 #

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

-- error 1242

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

create temporary table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq)) ENGINE=MyISAM;

create temporary table t7( uq int primary key, name char(25)) ENGINE=MyISAM;

insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");

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

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

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

--echo # not unique fields

-- error 1052

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

--echo # different tipes & group functions

drop table t1,t2,t3;

CREATE TABLE t3 (a varchar(20),b char(1) NOT NULL default '0');

INSERT INTO t3 VALUES ('W','a'),('A','c'),('J','b');

CREATE TABLE t2 (a varchar(20),b int NOT NULL default '0');

INSERT INTO t2 VALUES ('W','1'),('A','3'),('J','2');

CREATE TABLE t1 (a varchar(20),b date NULL);

INSERT INTO t1 VALUES ('W','1732-02-22'),('A','1735-10-30'),('J','1743-04-13');

SELECT * FROM t1 WHERE b = (SELECT MIN(b) FROM t1);

SELECT * FROM t2 WHERE b = (SELECT MIN(b) FROM t2);

SELECT * FROM t3 WHERE b = (SELECT MIN(b) FROM t3);

CREATE TABLE `t8` (

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

  `email` varchar(60) NOT NULL default '',

  PRIMARY KEY  (`pseudo`),

  UNIQUE KEY `email` (`email`)

);

INSERT INTO t8 (pseudo,email) VALUES ('joce','test');

INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');

INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');

--replace_column 9 #

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

-- error 1241

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

t8 WHERE pseudo='joce');

-- error 1241

SELECT pseudo FROM t8 WHERE pseudo=(SELECT * FROM t8 WHERE

pseudo='joce');

SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');

-- error 1242

SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%joce%');

drop table if exists t1,t2,t3,t4,t5,t6,t7,t8;

--echo #searchconthardwarefr3 forumconthardwarefr7

CREATE TEMPORARY TABLE `t1` (

  `topic` bigint NOT NULL default '0',

  `date` date NULL,

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

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

  KEY `topic` (`topic`)

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

INSERT INTO t1 (topic,date,pseudo) VALUES

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

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

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

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

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

SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1) UNION ALL SELECT 1;

-- error 1242

SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1) UNION SELECT 1;

EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1=(SELECT 1 UNION SELECT 1);

drop table t1;

--echo #forumconthardwarefr7 searchconthardwarefr7

CREATE TABLE `t1` (

  `numeropost` bigint NOT NULL auto_increment,

  `maxnumrep` int NOT NULL default '0',

  PRIMARY KEY  (`numeropost`),

  UNIQUE KEY `maxnumrep` (`maxnumrep`)

);

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

CREATE TABLE `t2` (

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

      `topic` bigint NOT NULL default '0',

      `date` date NULL,

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

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

    );

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

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

SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND date >= '2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20;

-- error 1054

SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) b;

-- error 1054

SELECT 1 IN (SELECT 1 FROM t2 HAVING a);

SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic);

SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);

SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1);

SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);

SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);

SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);

SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic);

SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);

SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100) from t2;

SELECT * from t2 where topic = all (SELECT SUM(topic) FROM t2);

SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);

SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);

SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);

SELECT * from t2 where topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);

SELECT *, topic = all (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000) from t2;

drop table t1,t2;

--echo #forumconthardwarefr7

CREATE TABLE `t1` (

  `numeropost` bigint NOT NULL auto_increment,

  `maxnumrep` int NOT NULL default '0',

  PRIMARY KEY  (`numeropost`),

  UNIQUE KEY `maxnumrep` (`maxnumrep`)

);

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

-- error 1242

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

-- error 1242

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

drop table t1;

create table t1 (a int);

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

(select * from t1) union (select * from t1) order by (select a from t1 limit 1);

drop table t1;

--echo #iftest

CREATE TEMPORARY TABLE t1 (field char(1) NOT NULL DEFAULT 'b') ENGINE=MyISAM;

INSERT INTO t1 VALUES ();

-- error 1242

SELECT field FROM t1 WHERE 1=(SELECT 1 UNION ALL SELECT 1 FROM (SELECT 1) a HAVING field='b');

drop table t1;

--echo # threadhardwarefr7

CREATE TABLE `t1` (

  `numeropost` bigint NOT NULL default '0',

  `numreponse` int NOT NULL auto_increment,

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

  PRIMARY KEY  (`numeropost`,`numreponse`),

  UNIQUE KEY `numreponse` (`numreponse`),

  KEY `pseudo` (`pseudo`,`numeropost`)

);

-- error 1247

SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;

-- error 1054

SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a;

SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a;

INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test');

-- error 1242

EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1');

--replace_column 9 #

EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1';

--replace_column 9 #

EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');

drop table t1;

CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1);

SELECT 1 FROM (SELECT a FROM t1) b HAVING (SELECT b.a)=1;

drop table t1;

--echo #update with subselects

create table t1 (a int NOT NULL, b int, primary key (a));

create table t2 (a int NOT NULL, b int, primary key (a));

insert into t1 values (0, 10),(1, 11),(2, 12);

insert into t2 values (1, 21),(2, 22),(3, 23);

select * from t1;

-- error 1093

update t1 set b= (select b from t1);

-- error 1242

update t1 set b= (select b from t2);

update t1 set b= (select b from t2 where t1.a = t2.a);

select * from t1;

drop table t1, t2;

--echo #delete with subselects

create table t1 (a int NOT NULL, b int, primary key (a));

create table t2 (a int NOT NULL, b int, primary key (a));

insert into t1 values (0, 10),(1, 11),(2, 12);

insert into t2 values (1, 21),(2, 12),(3, 23);

select * from t1;

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

-- error 1093

delete from t1 where b = (select b from t1);

-- error 1242

delete from t1 where b = (select b from t2);

delete from t1 where b = (select b from t2 where t1.a = t2.a);

select * from t1;

drop table t1, t2;

--echo #insert with subselects

CREATE TABLE t1 (x int);

create table t2 (a int);

create table t3 (b int);

insert into t2 values (1);

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

-- error 1093

INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));

-- error 1242

INSERT INTO t1 (x) VALUES ((SELECT b FROM t3));

INSERT INTO t1 (x) VALUES ((SELECT a FROM t2));

select * from t1;

insert into t2 values (1);

INSERT INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));

select * from t1;

INSERT INTO t1 (x) select (SELECT SUM(a)+1 FROM t2) FROM t2;

select * from t1;

--echo # After this, only data based on old t1 records should have been added.

INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;

select * from t1;

-- error 1054

INSERT INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));

INSERT INTO t1 (x) VALUES ((SELECT SUM(a) FROM t2));

select * from t1;

--echo #

--echo #TODO: should be uncommented after bug 380 fix pushed

--echo #INSERT INTO t1 (x) SELECT (SELECT SUM(a)+b FROM t2) from t3;

--echo #select * from t1;

drop table t1, t2, t3;

--echo #replace with subselects

CREATE TABLE t1 (x int not null, y int, primary key (x));

create table t2 (a int);

create temporary table t3 (a int) ENGINE=MyISAM;

insert into t2 values (1);

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

select * from t1;

-- error 1093

replace into t1 (x, y) VALUES ((SELECT x FROM t1), (SELECT a+1 FROM t2));

-- error 1242

replace into t1 (x, y) VALUES ((SELECT a FROM t3), (SELECT a+1 FROM t2));

replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+1 FROM t2));

select * from t1;

replace into t1 (x, y) VALUES ((SELECT a FROM t2), (SELECT a+2 FROM t2));

select * from t1;

replace into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a FROM t2));

--sorted_result

select * from t1;

replace into t1 (x, y) VALUES ((SELECT a+3 FROM t2), (SELECT a+1 FROM t2));

select * from t1;

replace into t1 (x, y) VALUES ((SELECT a+1 FROM t2), (SELECT a FROM t2));

--sorted_result

select * from t1;

drop table t1, t2, t3;

-- error 1096

SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);

CREATE TABLE t2 (id int default NULL, KEY id (id));

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

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

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

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

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

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

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

SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);

SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);

-- error 1093

INSERT INTO t2 VALUES ((SELECT * FROM t2));

-- error 1093

INSERT INTO t2 VALUES ((SELECT id FROM t2));

SELECT * FROM t2;

CREATE TEMPORARY TABLE t1 (id int default NULL, KEY id (id)) ENGINE=MyISAM;

INSERT INTO t1 values (1),(1);

-- error 1242

UPDATE t2 SET id=(SELECT * FROM t1);

drop table t2, t1;

--echo #NULL test

create temporary table t1 (a int) ENGINE=MyISAM;

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

select 1 IN (SELECT * from t1);

select 10 IN (SELECT * from t1);

select NULL IN (SELECT * from t1);

update t1 set a=NULL where a=2;

select 1 IN (SELECT * from t1);

select 3 IN (SELECT * from t1);

select 10 IN (SELECT * from t1);

select 1 > ALL (SELECT * from t1);

select 10 > ALL (SELECT * from t1);

select 1 > ANY (SELECT * from t1);

select 10 > ANY (SELECT * from t1);

drop table t1;

create temporary table t1 (a varchar(20)) ENGINE=MyISAM;

insert into t1 values ('A'),('BC'),('DEF');

select 'A' IN (SELECT * from t1);

select 'XYZS' IN (SELECT * from t1);

select NULL IN (SELECT * from t1);

update t1 set a=NULL where a='BC';

select 'A' IN (SELECT * from t1);

select 'DEF' IN (SELECT * from t1);

select 'XYZS' IN (SELECT * from t1);

select 'A' > ALL (SELECT * from t1);

select 'XYZS' > ALL (SELECT * from t1);

select 'A' > ANY (SELECT * from t1);

select 'XYZS' > ANY (SELECT * from t1);

drop table t1;

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

insert into t1 values (1.5),(2.5),(3.5);

select 1.5 IN (SELECT * from t1);

select 10.5 IN (SELECT * from t1);

select NULL IN (SELECT * from t1);

update t1 set a=NULL where a=2.5;

select 1.5 IN (SELECT * from t1);

select 3.5 IN (SELECT * from t1);

select 10.5 IN (SELECT * from t1);

select 1.5 > ALL (SELECT * from t1);

select 10.5 > ALL (SELECT * from t1);

select 1.5 > ANY (SELECT * from t1);

select 10.5 > ANY (SELECT * from t1);

explain extended select (select a+1) from t1;

--sorted_result

select (select a+1) from t1;

drop table t1;

--echo #

--echo # Null with keys

--echo #

CREATE TEMPORARY TABLE t1 (a int NOT NULL default '0', PRIMARY KEY  (a)) ENGINE=MyISAM;

CREATE TEMPORARY TABLE t2 (a int default '0', INDEX (a)) ENGINE=MyISAM;

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

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

SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;

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

CREATE TEMPORARY TABLE t3 (a int default '0') ENGINE=MyISAM;

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

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

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

drop table t1,t2,t3;

--echo #LIMIT is not supported now

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

--echo #-- error 1235

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

--echo #-- error 1235

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

--echo #drop table t1;

--echo #

--echo #create table t1 (a int, b int, c varchar(10)) ENGINE=MyISAM;

--echo #create table t2 (a int) ENGINE=MyISAM;

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

--echo #insert into t2 values (1),(2),(NULL);

--echo #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;

--echo #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;

--echo #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;

--echo #drop table t1,t2;

--echo #

--echo #create table t1 (a int, b real, c varchar(10)) ENGINE=MyISAM;

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

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

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

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

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

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

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

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

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

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

--echo #-- error 1235

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

--echo #drop table t1;

--echo #test of uncacheable subqueries

CREATE TABLE t1 (a int);

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

EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;

drop table t1;

CREATE TEMPORARY TABLE `t1` (

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

  `topic` bigint NOT NULL default '0',

  `date` date NULL DEFAULT '2009-01-20',

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

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

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

  KEY `topic` (`topic`)

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

CREATE TEMPORARY TABLE `t2` (

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

  `topic` bigint NOT NULL default '0',

  `date` date NULL default '1997-08-29',

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

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

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

  KEY `topic` (`topic`)

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

CREATE TEMPORARY TABLE `t3` (

  `numeropost` bigint NOT NULL auto_increment,

  `maxnumrep` int NOT NULL default '0',

  PRIMARY KEY  (`numeropost`),

  UNIQUE KEY `maxnumrep` (`maxnumrep`)

) ENGINE=MyISAM;

INSERT INTO t1 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');

INSERT INTO t2 (mot, topic, pseudo) VALUES ('joce','1','joce'),('test','2','test');

INSERT INTO t3 VALUES (1,1);

SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE

numeropost=topic);

select * from t1;

DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT

EXISTS(SELECT * FROM t3 WHERE numeropost=topic));

select * from t1;

drop table t1, t2, t3;

SELECT * FROM (SELECT 1 as a,(SELECT a)) a;

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

SHOW CREATE TABLE t1;

drop table t1;

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

SHOW CREATE TABLE t1;

drop table t1;

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

SHOW CREATE TABLE t1;

drop table t1;

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

select * from t1;

SHOW CREATE TABLE t1;

drop table t1;

create table t1 (a int);

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

--replace_column 9 #

explain extended select a,(select (select rand() from t1 limit 1)  from t1 limit 1)

from t1;

drop table t1;

--echo #

--echo # error in IN

--echo #

-- error 1146

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

--echo #

--echo # complex subquery

--echo #

CREATE TABLE t1 (

  ID int NOT NULL auto_increment,

  name char(35) NOT NULL default '',

  t2 char(3) NOT NULL default '',

  District char(20) NOT NULL default '',

  Population int NOT NULL default '0',

  PRIMARY KEY  (ID)

);

INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);

INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);

INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117);

CREATE TABLE t2 (

  Code char(3) NOT NULL default '',

  Name char(52) NOT NULL default '',

  Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia',

  Region char(26) NOT NULL default '',

  SurfaceArea float(10,2) NOT NULL default '0.00',

  IndepYear int default NULL,

  Population int NOT NULL default '0',

  LifeExpectancy float(3,1) default NULL,

  GNP float(10,2) default NULL,

  GNPOld float(10,2) default NULL,

  LocalName char(45) NOT NULL default '',

  GovernmentForm char(45) NOT NULL default '',

  HeadOfState char(60) default NULL,

  Capital int default NULL,

  Code2 char(2) NOT NULL default '',

  PRIMARY KEY  (Code)

);

INSERT INTO t2 VALUES ('AUS','Australia','Oceania','Australia and New Zealand',7741220.00,1901,18886000,79.8,351182.00,392911.00,'Australia','Constitutional Monarchy, Federation','Elisabeth II',135,'AU');

INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ');

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

drop table t1, t2;

--echo #

--echo # constants in IN

--echo #

CREATE TEMPORARY TABLE `t1` (

  `id` bigint NOT NULL auto_increment,

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

  PRIMARY KEY  (`id`),

  UNIQUE KEY `pseudo` (`pseudo`)

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

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

SELECT 0 IN (SELECT 1 FROM t1 a);

EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);

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

SELECT 0 IN (SELECT 1 FROM t1 a);

EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a);

drop table t1;

CREATE TEMPORARY TABLE `t1` (

  `i` int NOT NULL default '0',

  PRIMARY KEY  (`i`)

) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1);

UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i));

UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));

-- error 1054

UPDATE t1 SET t.i=i+(SELECT MAX(i) FROM (SELECT 1) t);

select * from t1;

drop table t1;

--echo #

--echo # correct NULL in <CONSTANT> IN (SELECT ...)

--echo #

create temporary table t1 (a int, unique index indexa (a)) ENGINE=MyISAM; 

insert into t1 values (-1), (-4), (-2), (NULL); 

select -10 IN (select a from t1 FORCE INDEX (indexa)); 

drop table t1;

--echo #

--echo # Test optimization for sub selects

--echo #

create table t1 (id int not null auto_increment primary key, salary int, key(salary));

insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);

--replace_column 9 #

explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);

drop table t1;

CREATE TABLE t1 (

  ID int NOT NULL auto_increment,

  SUB_ID int NOT NULL default '0',

  REF_ID int default NULL,

  REF_SUB int default '0',

  PRIMARY KEY (ID,SUB_ID),

  UNIQUE KEY t1_PK (ID,SUB_ID),

  KEY t1_FK (REF_ID,REF_SUB),

  KEY t1_REFID (REF_ID)

);

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

SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2);

DROP TABLE t1;

--echo #

--echo # uninterruptable update

--echo #

create temporary table t1 (a int, b int) ENGINE=MyISAM;

create temporary table t2 (a int, b int) ENGINE=MyISAM;

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

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

update ignore t1 set b=(select b from t2 where t1.a=t2.a);

select * from t1;

drop table t1, t2;

--echo #

--echo # reduced subselect in ORDER BY & GROUP BY clauses

--echo #

CREATE TEMPORARY TABLE `t1` (

  `id` bigint NOT NULL auto_increment,

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

  `email` varchar(60) NOT NULL default '',

  PRIMARY KEY  (`id`),

  UNIQUE KEY `email` (`email`),

  UNIQUE KEY `pseudo` (`pseudo`)

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1');

SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1);

drop table if exists t1;

(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);

--echo #

--echo # IN subselect optimization test

--echo #

create temporary table t1 (a int not null, b int, primary key (a)) ENGINE=MyISAM;

create temporary table t2 (a int not null, primary key (a)) ENGINE=MyISAM;

create temporary table t3 (a int not null, b int, primary key (a)) ENGINE=MyISAM;

insert into t1 values (1,10), (2,20), (3,30),  (4,40);

insert into t2 values (2), (3), (4), (5);

insert into t3 values (10,3), (20,4), (30,5);

select * from t2 where t2.a in (select a from t1);

explain extended select * from t2 where t2.a in (select a from t1);

select * from t2 where t2.a in (select a from t1 where t1.b <> 30);

explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);

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

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

drop table t1, t2, t3;

create temporary table t1 (a int, b int, index a (a,b)) ENGINE=MyISAM;

create temporary table t2 (a int, index a (a)) ENGINE=MyISAM;

create temporary table t3 (a int, b int, index a (a)) ENGINE=MyISAM;

insert into t1 values (1,10), (2,20), (3,30), (4,40);

disable_query_log;

--echo # making table large enough

set autocommit=0;

begin;

let $1 = 10000;

while ($1)

 {

  eval insert into t1 values (rand()*100000+200,rand()*100000); 

  dec $1;

 }

commit;

set autocommit=1;

enable_query_log;

insert into t2 values (2), (3), (4), (5);

insert into t3 values (10,3), (20,4), (30,5);

select * from t2 where t2.a in (select a from t1);

explain extended select * from t2 where t2.a in (select a from t1);

select * from t2 where t2.a in (select a from t1 where t1.b <> 30);

explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);

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

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

insert into t1 values (3,31);

select * from t2 where t2.a in (select a from t1 where t1.b <> 30);

select * from t2 where t2.a in (select a from t1 where t1.b <> 30 and t1.b <> 31);

explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);

drop table t1, t2, t3;

--echo #

--echo # alloc_group_fields() working

--echo #

create temporary table t1 (a int, b int) ENGINE=MyISAM;

create temporary table t2 (a int, b int) ENGINE=MyISAM;

create temporary table t3 (a int, b int) ENGINE=MyISAM;

insert into t1 values (0,100),(1,2), (1,3), (2,2), (2,7), (2,-1), (3,10);

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

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

select a,(select count(distinct t1.b) as sum from t1,t2 where t1.a=t2.a and t2.b > 0 and t1.a <= t3.b group by t1.a order by sum limit 1) from t3;

drop table t1,t2,t3;

--echo #

--echo # aggregate functions in HAVING test

--echo #

create temporary table t1 (s1 int) ENGINE=MyISAM;

create temporary table t2 (s1 int) ENGINE=MyISAM;

insert into t1 values (1);

insert into t2 values (1);

select * from t1 where exists (select s1 from t2 having max(t2.s1)=t1.s1);

drop table t1,t2;

--echo #

--echo # update subquery with wrong field (to force name resolving

--echo # in UPDATE name space)

--echo #

create temporary table t1 (s1 int) ENGINE=MyISAM;

create temporary table t2 (s1 int) ENGINE=MyISAM;

insert into t1 values (1);

insert into t2 values (1);

-- error 1054

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

DROP TABLE t1, t2;

--echo #

--echo # collation test

--echo #

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

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

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

--echo #-- error 1267

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

--echo #-- error 1267

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

--echo #drop table t1;

--echo #

--echo # aggregate functions reinitialization

--echo #

create table t1(toid int,rd int);

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

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

insert into t1 values(1,0),(1,0),(1,0),(1,12),(1,15),(1,123),(1,12312),(1,12312),(1,123),(2,0),(2,0),(2,1),(2,2);

select userid,pmtotal,pmnew, (select count(rd) from t1 where toid=t2.userid) calc_total, (select count(rd) from t1 where rd=0 and toid=t2.userid) calc_new from t2 where userid in (select distinct toid from t1);

drop table t1, t2;

--echo #

--echo # row union

--echo #

create table t1 (s1 char(5));

-- error 1241

select (select 'a','b' from t1 union select 'a','b' from t1) from t1;

insert into t1 values ('tttt');

select * from t1 where ('a','b')=(select 'a','b' from t1 union select 'a','b' from t1);

--replace_column 9 #

explain extended (select * from t1);

(select * from t1);

drop table t1;

--echo #

--echo # IN optimisation test results

--echo #

create temporary table t1 (s1 char(5), index s1(s1)) ENGINE=MyISAM;

create temporary table t2 (s1 char(5), index s1(s1)) ENGINE=MyISAM;

insert into t1 values ('a1'),('a2'),('a3');

insert into t2 values ('a1'),('a2');

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

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

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

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

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

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

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

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

drop table t1,t2;

--echo #

--echo # correct ALL optimisation

--echo #

create temporary table t2 (a int, b int) ENGINE=MyISAM;

create temporary table t3 (a int) ENGINE=MyISAM;

insert into t3 values (6),(7),(3);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--echo #

--echo # optimized static ALL/ANY with grouping

--echo #

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

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

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

drop table t2, t3;

--echo #

--echo # correct used_tables()

--echo #

CREATE TEMPORARY TABLE `t1` ( `id` bigint NOT NULL auto_increment, `taskid` bigint NOT NULL default '0', `dbid` int NOT NULL default '0', `create_date` datetime, `last_update` datetime, PRIMARY KEY  (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ;

INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now());

CREATE TEMPORARY TABLE `t2` (`db_id` int NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` int NOT NULL default '0',`secondary_uid` int NOT NULL default '0',PRIMARY KEY  (`db_id`),UNIQUE KEY `name_2` (`name`)) ENGINE=MyISAM AUTO_INCREMENT=2147483647;

INSERT INTO `t2` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (18, 'Not Set 1', 0, 0),(19, 'Valid', 1, 2),(20, 'Valid 2', 1, 2),(21, 'Should Not Return', 1, 2),(26, 'Not Set 2', 0, 0),(-1, 'ALL DB\'S', 0, 0);

CREATE TEMPORARY TABLE `t3` (`taskgenid` bigint NOT NULL auto_increment,`dbid` int NOT NULL default '0',`taskid` int NOT NULL default '0',`mon` int NOT NULL default '1',`tues` int NOT NULL default '1',`wed` int NOT NULL default '1',`thur` int NOT NULL default '1',`fri` int NOT NULL default '1',`sat` int NOT NULL default '0',`sun` int NOT NULL default '0',`how_often` int NOT NULL default '1',`userid` int NOT NULL default '0',`active` int NOT NULL default '1',PRIMARY KEY  (`taskgenid`)) ENGINE=MyISAM AUTO_INCREMENT=2 ;

INSERT INTO `t3` (`taskgenid`, `dbid`, `taskid`, `mon`, `tues`,`wed`, `thur`, `fri`, `sat`, `sun`, `how_often`, `userid`, `active`) VALUES (1,-1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1);

CREATE TEMPORARY TABLE `t4` (`task_id` int NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM;

INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status');

select  dbid, name, (date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01') from t3 a, t2 b, t4  WHERE dbid = - 1 AND primary_uid = '1' AND t4.task_id = taskid;

SELECT dbid, name FROM t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND ((date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01')) AND t4.task_id = taskid;

drop table t1,t2,t3,t4;

--echo #

--echo # cardinality check

--echo #

CREATE TEMPORARY TABLE t1 (id int default NULL) ENGINE=MyISAM;

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

CREATE TEMPORARY TABLE t2 (id int default NULL) ENGINE=MyISAM;

INSERT INTO t2 VALUES (2),(6);

-- error 1241

select * from t1 where (1,2,6) in (select * from t2);

DROP TABLE t1,t2;

--echo #

--echo # optimized ALL/ANY with union

--echo #

create table t1 (s1 char);

insert into t1 values ('e');

select * from t1 where 'f' > any (select s1 from t1);

select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);

--replace_column 9 #

explain extended select * from t1 where 'f' > any (select s1 from t1 union select s1 from t1);

drop table t1;

--echo #

--echo # filesort in subquery (restoring join_tab)

--echo #

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

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

CREATE TEMPORARY TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM;

INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6');

select c.number as phone,(select p.code from t2 p where c.number like concat(p.code, '%') order by length(p.code) desc limit 1) as code from t1 c;

drop table t1, t2;

--echo #

--echo # unresolved field error

--echo #

create temporary table t1 (s1 int) ENGINE=MyISAM; 

create temporary table t2 (s1 int) ENGINE=MyISAM;

-- error 1054

select * from t1 where (select count(*) from t2 where t1.s2) = 1;

-- error 1054

select * from t1 where (select count(*) from t2 group by t1.s2) = 1;

-- error 1054

select count(*) from t2 group by t1.s2;

drop table t1, t2;

--echo #

--echo # fix_fields() in add_ref_to_table_cond()

--echo #

CREATE TEMPORARY TABLE t1(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC VARCHAR(20) DEFAULT NULL,PRIMARY KEY (COLA, COLB)) ENGINE=MyISAM;

CREATE TEMPORARY TABLE t2(COLA FLOAT NOT NULL,COLB FLOAT NOT NULL,COLC CHAR(1) NOT NULL,PRIMARY KEY (COLA)) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1,1,'1A3240'), (1,2,'4W2365');

INSERT INTO t2 VALUES (100, 200, 'C');

SELECT DISTINCT COLC FROM t1 WHERE COLA = (SELECT COLA FROM t2 WHERE COLB = 200 AND COLC ='C' LIMIT 1);

DROP TABLE t1, t2;

CREATE TEMPORARY TABLE t1 (a int) ENGINE=MyISAM;

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

SELECT DISTINCT (SELECT a) FROM t1 LIMIT 100;

DROP TABLE t1;

--echo #

--echo # Bug 2479

--echo #

CREATE TEMPORARY TABLE `t1` (

  `id` int NOT NULL auto_increment,

  `id_cns` int NOT NULL default '0',

  `tipo` enum('','UNO','DUE') NOT NULL default '',

  `anno_dep` int NOT NULL default '0',

  `particolare` bigint NOT NULL default '0',

  `generale` bigint NOT NULL default '0',

  `bis` int NOT NULL default '0',

  PRIMARY KEY  (`id`),

  UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`),

  UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`))

 ENGINE=MyISAM;

INSERT INTO `t1` VALUES (1,16,'UNO',1987,2048,9681,0),(2,50,'UNO',1987,1536,13987,0),(3,16,'UNO',1987,2432,14594,0),(4,16,'UNO',1987,1792,13422,0),(5,16,'UNO',1987,1025,10240,0),(6,16,'UNO',1987,1026,7089,0);

CREATE TEMPORARY TABLE `t2` (

  `id` int NOT NULL auto_increment,

  `max_anno_dep` int NOT NULL default '0',

  PRIMARY KEY  (`id`)) ENGINE=MyISAM;

INSERT INTO `t2` VALUES (16,1987),(50,1990),(51,1990);

SELECT cns.id, cns.max_anno_dep, cns.max_anno_dep = (SELECT s.anno_dep FROM t1 AS s WHERE s.id_cns = cns.id ORDER BY s.anno_dep DESC LIMIT 1) AS PIPPO FROM t2 AS cns;

DROP TABLE t1, t2;

--echo #

--echo # GLOBAL LIMIT

--echo #

create temporary table t1 (a int) ENGINE=MyISAM;

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

SET SQL_SELECT_LIMIT=1;

select sum(a) from (select * from t1) as a;

select 2 in (select * from t1);

SET SQL_SELECT_LIMIT=default;

drop table t1;

--echo #

--echo # Bug #3118: subselect + order by

--echo #

CREATE TABLE t1 (a int, b int, INDEX (a));

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

SELECT * FROM t1 WHERE a = (SELECT MAX(a) FROM t1 WHERE a = 1) ORDER BY b;

DROP TABLE t1;

--echo # Item_cond fix field

--echo #

create table t1(val varchar(10));

insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp');

select count(*) from t1 as w1 where w1.val in (select w2.val from t1 as w2 where w2.val like 'm%') and w1.val in (select w3.val from t1 as w3 where w3.val like 'e%');

drop table t1;

--echo #

--echo # ref_or_null replacing with ref

--echo #

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

insert into t1 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text11'), (12, 'text12');

select * from t1 where id not in (select id from t1 where id < 8);

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

--replace_column 9 #

explain extended select * from t1 where id not in (select id from t1 where id < 8);

--replace_column 9 #

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

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

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

insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');

select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);

--replace_column 9 #

explain extended select * from t1 a left join t2 b on (a.id=b.id or b.id is null) join t1 c on (if(isnull(b.id), 1000, b.id)=c.id);

drop table t1,t2;

--echo #

--echo # Static tables & rund() in subqueries

--echo #

create temporary table t1 (a int) ENGINE=MyISAM;

insert into t1 values (1);

explain select benchmark(1000, (select a from t1 where a=rand()));

drop table t1;

--echo #

--echo # bug 3188

--echo #

create temporary table t1(id int) ENGINE=MyISAM;

create temporary table t2(id int) ENGINE=MyISAM;

create temporary table t3(flag int) ENGINE=MyISAM;

-- error 1064

select (select * from t3 where id not null) from t1, t2;

drop table t1,t2,t3;

--echo #

--echo # aggregate functions (Bug #3505)

--echo #

CREATE TABLE t1 (id INT);

CREATE TABLE t2 (id INT);

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

INSERT INTO t2 VALUES (1);

SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);

SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id);

SELECT t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY t1.id;

SELECT id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c FROM t1 LEFT JOIN t2 USING (id) ORDER BY id;

DROP TABLE t1,t2;