summaryrefslogtreecommitdiff
path: root/mysql-test/t/subselect.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t/subselect.test')
-rw-r--r--mysql-test/t/subselect.test56
1 files changed, 28 insertions, 28 deletions
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index e53679b444e..95e4f022f2d 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -152,7 +152,7 @@ CREATE TABLE `t8` (
`email` varchar(60) character set latin1 NOT NULL default '',
PRIMARY KEY (`pseudo`),
UNIQUE KEY `email` (`email`)
-) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
+) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
INSERT INTO t8 (pseudo,email) VALUES ('joce','test');
INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
@@ -177,7 +177,7 @@ CREATE TABLE `t1` (
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
-) TYPE=MyISAM ROW_FORMAT=DYNAMIC;
+) 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';
@@ -196,7 +196,7 @@ CREATE TABLE `t1` (
`maxnumrep` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`numeropost`),
UNIQUE KEY `maxnumrep` (`maxnumrep`)
-) TYPE=MyISAM ROW_FORMAT=FIXED;
+) ENGINE=MyISAM ROW_FORMAT=FIXED;
INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
@@ -206,7 +206,7 @@ CREATE TABLE `t2` (
`date` date NOT NULL default '0000-00-00',
`pseudo` varchar(35) NOT NULL default '',
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`)
- ) TYPE=MyISAM ROW_FORMAT=DYNAMIC;
+ ) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
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);
@@ -239,7 +239,7 @@ CREATE TABLE `t1` (
`maxnumrep` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`numeropost`),
UNIQUE KEY `maxnumrep` (`maxnumrep`)
-) TYPE=MyISAM ROW_FORMAT=FIXED;
+) ENGINE=MyISAM ROW_FORMAT=FIXED;
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
-- error 1241
@@ -268,7 +268,7 @@ CREATE TABLE `t1` (
PRIMARY KEY (`numeropost`,`numreponse`),
UNIQUE KEY `numreponse` (`numreponse`),
KEY `pseudo` (`pseudo`,`numeropost`)
-) TYPE=MyISAM;
+) ENGINE=MyISAM;
-- error 1246
SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a;
-- error 1054
@@ -392,7 +392,7 @@ drop table t1, t2, t3;
-- error 1096
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
-CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
+CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t2 VALUES (1),(2);
SELECT * FROM t2 WHERE id IN (SELECT 1);
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
@@ -407,7 +407,7 @@ INSERT INTO t2 VALUES ((SELECT * FROM t2));
-- error 1093
INSERT INTO t2 VALUES ((SELECT id FROM t2));
SELECT * FROM t2;
-CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
+CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 values (1),(1);
-- error 1241
UPDATE t2 SET id=(SELECT * FROM t1);
@@ -541,7 +541,7 @@ insert into t2 values (1,2),(1,3);
select * from t1 where row(a,b) in (select a,b from t2);
drop table t1, t2;
-CREATE TABLE `t1` (`i` int(11) NOT NULL default '0',PRIMARY KEY (`i`)) TYPE=MyISAM CHARSET=latin1;
+CREATE TABLE `t1` (`i` int(11) NOT NULL default '0',PRIMARY KEY (`i`)) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES (1);
-- error 1111
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
@@ -563,7 +563,7 @@ CREATE TABLE `t1` (
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
KEY `pseudo` (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
-) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
+) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
CREATE TABLE `t2` (
`mot` varchar(30) character set latin1 NOT NULL default '',
@@ -573,14 +573,14 @@ CREATE TABLE `t2` (
PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`),
KEY `pseudo` (`pseudo`,`date`,`topic`),
KEY `topic` (`topic`)
-) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
+) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC;
CREATE TABLE `t3` (
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`maxnumrep` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`numeropost`),
UNIQUE KEY `maxnumrep` (`maxnumrep`)
-) TYPE=MyISAM CHARSET=latin1;
+) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test');
INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test');
@@ -634,7 +634,7 @@ CREATE TABLE t1 (
District char(20) NOT NULL default '',
Population int(11) NOT NULL default '0',
PRIMARY KEY (ID)
-) TYPE=MyISAM;
+) ENGINE=MyISAM;
INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207);
INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329);
@@ -657,7 +657,7 @@ CREATE TABLE t2 (
Capital int(11) default NULL,
Code2 char(2) NOT NULL default '',
PRIMARY KEY (Code)
-) TYPE=MyISAM;
+) ENGINE=MyISAM;
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');
@@ -674,7 +674,7 @@ CREATE TABLE `t1` (
`pseudo` varchar(35) character set latin1 NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `pseudo` (`pseudo`),
-) TYPE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
+) ENGINE=MyISAM PACK_KEYS=1 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);
@@ -686,7 +686,7 @@ drop table t1;
CREATE TABLE `t1` (
`i` int(11) NOT NULL default '0',
PRIMARY KEY (`i`)
-) TYPE=MyISAM CHARSET=latin1;
+) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES (1);
-- error 1111
@@ -702,12 +702,12 @@ drop table t1;
#
CREATE TABLE t1 (
id int(11) default NULL
-) TYPE=MyISAM CHARSET=latin1;
+) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3);
CREATE TABLE t2 (
id int(11) default NULL,
name varchar(15) default NULL
-) TYPE=MyISAM CHARSET=latin1;
+) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita');
update t1, t2 set t2.name='lenka' where t2.id in (select id from t1);
@@ -739,7 +739,7 @@ CREATE TABLE t1 (
UNIQUE KEY t1_PK (ID,SUB_ID),
KEY t1_FK (REF_ID,REF_SUB),
KEY t1_REFID (REF_ID)
-) TYPE=MyISAM CHARSET=cp1251;
+) ENGINE=MyISAM CHARSET=cp1251;
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;
@@ -777,7 +777,7 @@ CREATE TABLE `t1` (
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `pseudo` (`pseudo`),
-) TYPE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC;
+) ENGINE=MyISAM CHARSET=latin1 PACK_KEYS=1 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;
@@ -933,13 +933,13 @@ drop table t2, t3;
# correct used_tables()
#
-CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ;
+CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 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 TABLE `t2` (`db_id` int(11) NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint(6) NOT NULL default '0',`secondary_uid` smallint(6) NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`),FULLTEXT KEY `name` (`name`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647;
+CREATE TABLE `t2` (`db_id` int(11) NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint(6) NOT NULL default '0',`secondary_uid` smallint(6) NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`),FULLTEXT KEY `name` (`name`)) ENGINE=MyISAM CHARSET=latin1 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 TABLE `t3` (`taskgenid` mediumint(9) NOT NULL auto_increment,`dbid` int(11) NOT NULL default '0',`taskid` int(11) NOT NULL default '0',`mon` tinyint(4) NOT NULL default '1',`tues` tinyint(4) NOT NULL default '1',`wed` tinyint(4) NOT NULL default '1',`thur` tinyint(4) NOT NULL default '1',`fri` tinyint(4) NOT NULL default '1',`sat` tinyint(4) NOT NULL default '0',`sun` tinyint(4) NOT NULL default '0',`how_often` smallint(6) NOT NULL default '1',`userid` smallint(6) NOT NULL default '0',`active` tinyint(4) NOT NULL default '1',PRIMARY KEY (`taskgenid`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;
+CREATE TABLE `t3` (`taskgenid` mediumint(9) NOT NULL auto_increment,`dbid` int(11) NOT NULL default '0',`taskid` int(11) NOT NULL default '0',`mon` tinyint(4) NOT NULL default '1',`tues` tinyint(4) NOT NULL default '1',`wed` tinyint(4) NOT NULL default '1',`thur` tinyint(4) NOT NULL default '1',`fri` tinyint(4) NOT NULL default '1',`sat` tinyint(4) NOT NULL default '0',`sun` tinyint(4) NOT NULL default '0',`how_often` smallint(6) NOT NULL default '1',`userid` smallint(6) NOT NULL default '0',`active` tinyint(4) NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM CHARSET=latin1 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 TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') TYPE=MyISAM CHARSET=latin1;
+CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
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;
@@ -948,9 +948,9 @@ drop table t1,t2,t3,t4;
#
# cardinality check
#
-CREATE TABLE t1 (id int(11) default NULL) TYPE=MyISAM CHARSET=latin1;
+CREATE TABLE t1 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES (1),(5);
-CREATE TABLE t2 (id int(11) default NULL) TYPE=MyISAM CHARSET=latin1;
+CREATE TABLE t2 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t2 VALUES (2),(6);
-- error 1240
select * from t1 where (1,2,6) in (select * from t2);
@@ -980,9 +980,9 @@ drop table t1;
#
# filesort in subquery (restoring join_tab)
#
-CREATE TABLE t1 (number char(11) NOT NULL default '') TYPE=MyISAM CHARSET=latin1;
+CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
-CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) TYPE=MyISAM CHARSET=latin1;
+CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM CHARSET=latin1;
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;
View Lorry Controller Status