From dc1e55f8194db83e7a40dea5bf49e9fef970826c Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 13 May 2003 18:58:26 +0300 Subject: Fix for UNION and LEFT JOIN (Bug #386) Fixed wrong logging of Access denied error (Bug #398) include/my_global.h: Fix for QNX mysql-test/r/union.result: new test case mysql-test/t/union.test: Test of bug in union and left join mysys/my_seek.c: Safety fix to find out when pos gets a wrong value sql/field.h: Fix for UNION and LEFT JOIN sql/mysql_priv.h: Fix for UNION and LEFT JOIN sql/sql_base.cc: Fix for UNION and LEFT JOIN sql/sql_insert.cc: Fix for UNION and LEFT JOIN sql/sql_parse.cc: Fixed wrong logging of Access denied error sql/sql_union.cc: Fix for UNION and LEFT JOIN sql/sql_update.cc: Fix for UNION and LEFT JOIN --- mysql-test/r/union.result | 59 ++++++++++++++++++++++++++++++++++++++++++++- mysql-test/t/union.test | 61 ++++++++++++++++++++++++++++++++++++++++++++++- 2 files changed, 118 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 2af9d5a3584..970e83c6752 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1,4 +1,4 @@ -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4,t5,t6; CREATE TABLE t1 (a int not null, b char (10) not null); insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); CREATE TABLE t2 (a int not null, b char (10) not null); @@ -202,3 +202,60 @@ a 11 set SQL_SELECT_LIMIT=DEFAULT; drop table t1,t2; +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"; +uid rl g1 cid gg +1 NULL V1 NULL 1 +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"; +uid rl g1 cid gg +(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"); +uid rl g1 cid gg +1 NULL V1 NULL 1 +drop table t1,t2,t3,t4,t5,t6; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 6e6b43ebe3b..7262b8f78d7 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -2,7 +2,7 @@ # Test of unions # -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4,t5,t6; CREATE TABLE t1 (a int not null, b char (10) not null); insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); CREATE TABLE t2 (a int not null, b char (10) not null); @@ -98,3 +98,62 @@ set SQL_SELECT_LIMIT=2; (select * from t1 limit 1) union (select * from t2 limit 3); set SQL_SELECT_LIMIT=DEFAULT; drop table t1,t2; + +# +# 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; -- cgit v1.2.1