diff options
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 64 |
1 files changed, 32 insertions, 32 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 71755aac52d..672a39299dd 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -351,7 +351,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'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); @@ -381,7 +381,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'; @@ -421,7 +421,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); CREATE TABLE `t2` ( `mot` varchar(30) NOT NULL default '', @@ -429,7 +429,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); a @@ -493,7 +493,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); select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1); ERROR 21000: Subquery returns more than 1 row @@ -520,7 +520,7 @@ CREATE TABLE `t1` ( PRIMARY KEY (`numeropost`,`numreponse`), UNIQUE KEY `numreponse` (`numreponse`), KEY `pseudo` (`pseudo`,`numeropost`) -) TYPE=MyISAM; +) ENGINE=MyISAM; SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a; ERROR 42S22: Reference 'numreponse' not supported (forward reference in item list) SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a; @@ -698,7 +698,7 @@ x y drop table t1, t2, t3; SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *); ERROR HY000: No tables used -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); id @@ -742,7 +742,7 @@ SELECT * FROM t2; id 1 2 -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); UPDATE t2 SET id=(SELECT * FROM t1); ERROR 21000: Subquery returns more than 1 row @@ -990,7 +990,7 @@ a b 1 2 1 3 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); UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i)); ERROR HY000: Invalid use of group function @@ -1023,7 +1023,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 '', `topic` mediumint(8) unsigned NOT NULL default '0', @@ -1032,13 +1032,13 @@ 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'); Warnings: Warning 1264 Data truncated for column 'date' at row 1 @@ -1071,7 +1071,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` bigint(1) NOT NULL default '0', `(SELECT 1)` bigint(1) NOT NULL default '0' -) TYPE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a; SHOW CREATE TABLE t1; @@ -1079,7 +1079,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` bigint(1) NOT NULL default '0', `(SELECT a)` bigint(1) NOT NULL default '0' -) TYPE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a; SHOW CREATE TABLE t1; @@ -1087,7 +1087,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` bigint(1) NOT NULL default '0', `(SELECT a+0)` bigint(17) NOT NULL default '0' -) TYPE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a; select * from t1; @@ -1097,7 +1097,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` bigint(17) NOT NULL default '0' -) TYPE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); insert into t1 values (1), (2), (3); @@ -1119,7 +1119,7 @@ t2 char(3) NOT NULL default '', 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); INSERT INTO t1 VALUES (132,'Brisbane','AUS','Queensland',1291117); @@ -1140,7 +1140,7 @@ HeadOfState char(60) default NULL, 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'); 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); @@ -1152,7 +1152,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); 0 IN (SELECT 1 FROM t1 a) @@ -1177,7 +1177,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); UPDATE t1 SET i=i+(SELECT MAX(i) FROM (SELECT 1) t) WHERE i=(SELECT MAX(i)); ERROR HY000: Invalid use of group function @@ -1188,12 +1188,12 @@ ERROR 42S02: Unknown table 't' in field list 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); select * from t2; @@ -1227,7 +1227,7 @@ PRIMARY KEY (ID,SUB_ID), 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); REF_ID @@ -1257,7 +1257,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); a b @@ -1496,13 +1496,13 @@ id select_type table type possible_keys key key_len ref rows Extra Warnings: Note 1003 select high_priority test.t3.a AS `a` from test.t3 where <not>((test.t3.a <= <max>(select max(test.t2.b) AS `max(b)` from test.t2 group by test.t2.a))) drop table t2, t3; -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; 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') @@ -1514,9 +1514,9 @@ dbid name -1 Valid -1 Valid 2 drop table t1,t2,t3,t4; -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); select * from t1 where (1,2,6) in (select * from t2); ERROR 21000: Operand should contain 3 column(s) @@ -1542,9 +1542,9 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 system NULL NULL NULL NULL 1 3 UNION t1 system NULL NULL NULL NULL 1 drop table t1; -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; phone code |