diff options
Diffstat (limited to 'mysql-test/t/subselect.test')
| -rw-r--r-- | mysql-test/t/subselect.test | 20 |
1 files changed, 17 insertions, 3 deletions
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 88bba3b5ce6..2e39d224649 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -171,7 +171,9 @@ 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'); +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; CREATE TABLE t1 (a varchar(20),b date NOT NULL default '0000-00-00'); +SET sql_mode = DEFAULT; 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); @@ -201,6 +203,7 @@ SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo LIKE '%jo drop table if exists t1,t2,t3,t4,t5,t6,t7,t8; #searchconthardwarefr3 forumconthardwarefr7 +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; CREATE TABLE `t1` ( `topic` mediumint(8) unsigned NOT NULL default '0', `date` date NOT NULL default '0000-00-00', @@ -208,6 +211,7 @@ CREATE TABLE `t1` ( PRIMARY KEY (`pseudo`,`date`,`topic`), KEY `topic` (`topic`) ) ENGINE=MyISAM ROW_FORMAT=DYNAMIC; +SET sql_mode = DEFAULT; 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'; @@ -230,6 +234,7 @@ CREATE TABLE `t1` ( INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2); +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; CREATE TABLE `t2` ( `mot` varchar(30) NOT NULL default '', `topic` mediumint(8) unsigned NOT NULL default '0', @@ -237,6 +242,7 @@ CREATE TABLE `t2` ( `pseudo` varchar(35) NOT NULL default '', PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`) ) ENGINE=MyISAM ROW_FORMAT=DYNAMIC; +SET sql_mode = DEFAULT; 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); @@ -652,6 +658,7 @@ EXPLAIN EXTENDED SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1; drop table t1; +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; CREATE TABLE `t1` ( `mot` varchar(30) character set latin1 NOT NULL default '', `topic` mediumint(8) unsigned NOT NULL default '0', @@ -661,7 +668,9 @@ CREATE TABLE `t1` ( KEY `pseudo` (`pseudo`,`date`,`topic`), KEY `topic` (`topic`) ) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC; +SET sql_mode = DEFAULT; +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; CREATE TABLE `t2` ( `mot` varchar(30) character set latin1 NOT NULL default '', `topic` mediumint(8) unsigned NOT NULL default '0', @@ -671,6 +680,7 @@ CREATE TABLE `t2` ( KEY `pseudo` (`pseudo`,`date`,`topic`), KEY `topic` (`topic`) ) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC; +SET sql_mode = DEFAULT; CREATE TABLE `t3` ( `numeropost` mediumint(8) unsigned NOT NULL auto_increment, @@ -678,9 +688,9 @@ CREATE TABLE `t3` ( PRIMARY KEY (`numeropost`), UNIQUE KEY `maxnumrep` (`maxnumrep`) ) ENGINE=MyISAM CHARSET=latin1; -INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test'); +INSERT IGNORE INTO t1 VALUES ('joce','1','','joce'),('test','2','','test'); -INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test'); +INSERT IGNORE INTO t2 VALUES ('joce','1','','joce'),('test','2','','test'); INSERT INTO t3 VALUES (1,1); @@ -1031,7 +1041,9 @@ drop table t2, t3; # correct used_tables() # +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 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 ; +SET sql_mode = DEFAULT; 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`)) 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); @@ -2555,12 +2567,14 @@ DROP TABLE t1,t2; # Bug#20869 subselect with range access by DESC # +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; CREATE TABLE t1 ( i1 int(11) NOT NULL default '0', i2 int(11) NOT NULL default '0', t datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (i1,i2,t) ); +SET sql_mode = DEFAULT; INSERT INTO t1 VALUES (24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'), (24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'), @@ -3341,7 +3355,7 @@ create table t1(f11 int, f12 int); create table t2(f21 int unsigned not null, f22 int, f23 varchar(10)); insert into t1 values(1,1),(2,2), (3, 3); --disable_warnings -insert into t2 +insert ignore into t2 select -1 , (@a:=(A.a + 10 * (B.a + 10 * (C.a+10*D.a))))/5000 + 1, @a from t0 A, t0 B, t0 C, t0 D; --enable_warnings |