merging

1 files changed, 40 insertions, 6 deletions

diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 5040f400514..07bcb35242a 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -59,7 +59,7 @@ 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, b 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);
@@ -106,11 +106,13 @@ delete from t2 where a=100;
 select * from t3 where a in (select a,b from t2);
 -- error 1239
 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);
-select b,max(a) as ma from t4 group by b having b < (select max(t2.a)
-from t2 where t2.b=t4.b);
-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 t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
+-- 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 table t5 (a int);
 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);
@@ -402,6 +404,8 @@ 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 TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
 INSERT INTO t1 values (1),(1);
@@ -720,3 +724,33 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 drop table t1;
+
+CREATE TABLE t1 (
+  ID int(10) unsigned NOT NULL auto_increment,
+  SUB_ID int(3) unsigned NOT NULL default '0',
+  REF_ID int(10) unsigned default NULL,
+  REF_SUB int(3) unsigned 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)
+) TYPE=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;
+#
+# reduced subselect in ORDER BY & GROUP BY clauses
+#
+(SELECT 1 as a) UNION (SELECT 1) ORDER BY (SELECT a+0);
+
+CREATE TABLE `t1` (
+  `id` mediumint(8) unsigned 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`),
+) TYPE=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;