diff options
Diffstat (limited to 'mysql-test/t/union.test')
| -rw-r--r-- | mysql-test/t/union.test | 93 |
1 files changed, 77 insertions, 16 deletions
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 70e770cd2d3..6d857a4b40f 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -3,7 +3,7 @@ # --disable_warnings -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4,t5,t6; --enable_warnings CREATE TABLE t1 (a int not null, b char (10) not null); @@ -19,7 +19,7 @@ select 0,'#' union select a,b from t1 union all select a,b from t2 union select select a,b from t1 union select a,b from t1; select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 group by b; -#test alternate syntax for unions +# Test alternate syntax for unions (select a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 4; (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1); (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; @@ -27,7 +27,7 @@ select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 g (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by t1.b; explain (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; #(select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2; -select found_rows(); +#select found_rows(); # # Test some error conditions with UNION @@ -73,6 +73,19 @@ replace into t3 select a,b as c from t1 union all select a,b from t2; drop table t1,t2,t3; # +# Test some unions without tables +# +--error 1096 +select * union select 1; +select 1 as a,(select a union select a); +--error 1054 +(select 1) union (select 2) order by 0; +SELECT @a:=1 UNION SELECT @a:=@a+1; +--error 1054 +(SELECT 1) UNION (SELECT 2) ORDER BY (SELECT a); +(SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2); + +# # Test bug reported by joc@presence-pc.com # @@ -104,12 +117,67 @@ set SQL_SELECT_LIMIT=2; set SQL_SELECT_LIMIT=DEFAULT; drop table t1,t2; ---error 1096 -select * union select 1; -select 1 as a,(select a union select a); ---disable_warnings -drop table if exists t1,t2; ---enable_warnings +# +# Test error with left join +# + +CREATE TABLE t1 ( + cid smallint(5) unsigned NOT NULL default '0', + cv varchar(250) NOT NULL default '', + PRIMARY KEY (cid), + UNIQUE KEY cv (cv) +) ; +INSERT INTO t1 VALUES (8,'dummy'); +CREATE TABLE t2 ( + cid bigint(20) unsigned NOT NULL auto_increment, + cap varchar(255) NOT NULL default '', + PRIMARY KEY (cid), + KEY cap (cap) +) ; +CREATE TABLE t3 ( + gid bigint(20) unsigned NOT NULL auto_increment, + gn varchar(255) NOT NULL default '', + must tinyint(4) default NULL, + PRIMARY KEY (gid), + KEY gn (gn) +) ; +INSERT INTO t3 VALUES (1,'V1',NULL); +CREATE TABLE t4 ( + uid bigint(20) unsigned NOT NULL default '0', + gid bigint(20) unsigned default NULL, + rid bigint(20) unsigned default NULL, + cid bigint(20) unsigned default NULL, + UNIQUE KEY m (uid,gid,rid,cid), + KEY uid (uid), + KEY rid (rid), + KEY cid (cid), + KEY container (gid,rid,cid) +) ; +INSERT INTO t4 VALUES (1,1,NULL,NULL); +CREATE TABLE t5 ( + rid bigint(20) unsigned NOT NULL auto_increment, + rl varchar(255) NOT NULL default '', + PRIMARY KEY (rid), + KEY rl (rl) +) ; +CREATE TABLE t6 ( + uid bigint(20) unsigned NOT NULL auto_increment, + un varchar(250) NOT NULL default '', + uc smallint(5) unsigned NOT NULL default '0', + PRIMARY KEY (uid), + UNIQUE KEY nc (un,uc), + KEY un (un) +) ; +INSERT INTO t6 VALUES (1,'test',8); + +SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test"; +SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test"; +(SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t3.must IS NOT NULL AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test") UNION (SELECT t4.uid, t5.rl, t3.gn as g1, t4.cid, t4.gid as gg FROM t3, t6, t1, t4 left join t5 on t5.rid = t4.rid left join t2 on t2.cid = t4.cid WHERE t3.gid=t4.gid AND t6.uid = t4.uid AND t6.uc = t1.cid AND t1.cv = "dummy" AND t6.un = "test"); +drop table t1,t2,t3,t4,t5,t6; + +# +# Test for another bug with UNION and LEFT JOIN +# CREATE TABLE t1 ( id int(3) unsigned default '0') TYPE=MyISAM; INSERT INTO t1 (id) VALUES("1"); CREATE TABLE t2 ( id int(3) unsigned default '0', id_master int(5) default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL) TYPE=MyISAM; @@ -124,7 +192,6 @@ INSERT INTO t2 (id, id_master, text1, text2) VALUES("4", "1", SELECT 1 AS id_master, 1 AS id, NULL AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master; SELECT 1 AS id_master, 1 AS id, 'ABCDE' AS text1, 'ABCDE' AS text2 UNION SELECT id_master, t2.id, text1, text2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id_master; drop table if exists t1,t2; -(SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2); # # Test of bug when using the same table multiple times @@ -142,9 +209,3 @@ explain (select * from t1 where a=1) union (select * from t2 where a=1); explain (select * from t1 where a=1 and b=10) union (select t1.a,t2.a from t1,t2 where t1.a=t2.a); explain (select * from t1 where a=1) union (select * from t1 where b=1); drop table t1,t2; ---error 1054 -(select 1) union (select 2) order by 0; - -SELECT @a:=1 UNION SELECT @a:=@a+1; ---error 1054 -(SELECT 1) UNION (SELECT 2) ORDER BY (SELECT a); |