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

11 files changed, 150 insertions, 21 deletions

diff --git a/include/my_global.h b/include/my_global.h
index 98034fc1cff..0cd1352a290 100644
--- a/include/my_global.h
+++ b/include/my_global.h
@@ -361,7 +361,7 @@ typedef unsigned short ushort;
 #define set_bits(type, bit_count) (sizeof(type)*8 <= (bit_count) ? ~(type) 0 : ((((type) 1) << (bit_count)) - (type) 1))
 #define array_elements(A) ((uint) (sizeof(A)/sizeof(A[0])))
 #ifndef HAVE_RINT
-#define rint(A) floor((A)+0.5)
+#define rint(A) floor((A)+((A) < 0) -0.5 : 0.5)
 #endif
 
 /* Define some general constants */
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;
diff --git a/mysys/my_seek.c b/mysys/my_seek.c
index 177a5cee953..ec24a26b3d9 100644
--- a/mysys/my_seek.c
+++ b/mysys/my_seek.c
@@ -15,6 +15,7 @@
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
 
 #include "mysys_priv.h"
+#include <assert.h>
 
 	/* Seek to position in file */
 	/*ARGSUSED*/
@@ -27,6 +28,8 @@ my_off_t my_seek(File fd, my_off_t pos, int whence,
   DBUG_PRINT("my",("Fd: %d  Hpos: %lu  Pos: %lu  Whence: %d  MyFlags: %d",
 		   fd, (ulong) (((ulonglong) pos) >> 32), (ulong) pos, 
 		   whence, MyFlags));
+  DBUG_ASSERT(pos != MY_FILEPOS_ERROR);		/* safety check */
+
   newpos=lseek(fd, pos, whence);
   if (newpos == (os_off_t) -1)
   {
diff --git a/sql/field.h b/sql/field.h
index 842bbb89988..e63802d8c00 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -122,6 +122,8 @@ public:
     Field *tmp= (Field*) memdup_root(root,(char*) this,size_of());
     if (tmp)
     {
+      if (tmp->table->maybe_null)
+	tmp->flags&= ~NOT_NULL_FLAG;
       tmp->table=new_table;
       tmp->key_start=tmp->part_of_key=tmp->part_of_sortkey=0;
       tmp->unireg_check=Field::NONE;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 956fdf8cd45..d02202cd7c6 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -560,8 +560,8 @@ bool remove_table_from_cache(THD *thd, const char *db, const char *table,
 			     bool return_if_owned_by_thd=0);
 bool close_cached_tables(THD *thd, bool wait_for_refresh, TABLE_LIST *tables);
 void copy_field_from_tmp_record(Field *field,int offset);
-int fill_record(List<Item> &fields,List<Item> &values);
-int fill_record(Field **field,List<Item> &values);
+int fill_record(List<Item> &fields,List<Item> &values, bool ignore_errors);
+int fill_record(Field **field,List<Item> &values, bool ignore_errors);
 OPEN_TABLE_LIST *list_open_tables(THD *thd, const char *wild);
 
 /* sql_calc.cc */
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 4c607bd5698..3bfd5e14d43 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -2112,7 +2112,7 @@ int setup_conds(THD *thd,TABLE_LIST *tables,COND **conds)
 ******************************************************************************/
 
 int
-fill_record(List<Item> &fields,List<Item> &values)
+fill_record(List<Item> &fields,List<Item> &values, bool ignore_errors)
 {
   List_iterator_fast<Item> f(fields),v(values);
   Item *value;
@@ -2122,7 +2122,7 @@ fill_record(List<Item> &fields,List<Item> &values)
   while ((field=(Item_field*) f++))
   {
     value=v++;
-    if (value->save_in_field(field->field, 0))
+    if (value->save_in_field(field->field, 0) && !ignore_errors)
       DBUG_RETURN(1);
   }
   DBUG_RETURN(0);
@@ -2130,7 +2130,7 @@ fill_record(List<Item> &fields,List<Item> &values)
 
 
 int
-fill_record(Field **ptr,List<Item> &values)
+fill_record(Field **ptr,List<Item> &values, bool ignore_errors)
 {
   List_iterator_fast<Item> v(values);
   Item *value;
@@ -2140,7 +2140,7 @@ fill_record(Field **ptr,List<Item> &values)
   while ((field = *ptr++))
   {
     value=v++;
-    if (value->save_in_field(field, 0))
+    if (value->save_in_field(field, 0) && !ignore_errors)
       DBUG_RETURN(1);
   }
   DBUG_RETURN(0);
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index e02f457fd77..33a13fabdc6 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -219,7 +219,7 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, List<Item> &fields,
     if (fields.elements || !value_count)
     {
       restore_record(table,2);			// Get empty record
-      if (fill_record(fields,*values) || check_null_fields(thd,table))
+      if (fill_record(fields, *values, 0) || check_null_fields(thd,table))
       {
 	if (values_list.elements != 1)
 	{
@@ -236,7 +236,7 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, List<Item> &fields,
 	restore_record(table,2);		// Get empty record
       else
 	table->record[0][0]=table->record[2][0]; // Fix delete marker
-      if (fill_record(table->field,*values))
+      if (fill_record(table->field, *values, 0))
       {
 	if (values_list.elements != 1)
 	{
@@ -1330,9 +1330,9 @@ bool select_insert::send_data(List<Item> &values)
     return 0;
   }
   if (fields->elements)
-    fill_record(*fields,values);
+    fill_record(*fields, values, 1);
   else
-    fill_record(table->field,values);
+    fill_record(table->field, values, 1);
   if (write_record(table,&info))
     return 1;
   if (table->next_number_field)		// Clear for next record
@@ -1444,7 +1444,7 @@ bool select_create::send_data(List<Item> &values)
     thd->offset_limit--;
     return 0;
   }
-  fill_record(field,values);
+  fill_record(field, values, 1);
   if (write_record(table,&info))
     return 1;
   if (table->next_number_field)		// Clear for next record
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 73983bc03b1..7496d05be50 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -196,9 +196,17 @@ static bool check_user(THD *thd,enum_server_command command, const char *user,
   thd->db=0;
   thd->db_length=0;
   USER_RESOURCES ur;
+  char tmp_passwd[SCRAMBLE_LENGTH];
 
   if (passwd[0] && strlen(passwd) != SCRAMBLE_LENGTH)
     return 1;
+  /*
+    Move password to temporary buffer as it may be stored in communication
+    buffer
+  */
+  strmov(tmp_passwd, passwd);
+  passwd= tmp_passwd;				// Use local copy
+
   if (!(thd->user = my_strdup(user, MYF(0))))
   {
     send_error(net,ER_OUT_OF_RESOURCES);
@@ -264,6 +272,7 @@ static bool check_user(THD *thd,enum_server_command command, const char *user,
   }
   else
     send_ok(net);				// Ready to handle questions
+  thd->password= test(passwd[0]);		// Remember for error messages
   return 0;					// ok
 }
 
@@ -617,7 +626,6 @@ check_connections(THD *thd)
   net->read_timeout=(uint) thd->variables.net_read_timeout;
   if (check_user(thd,COM_CONNECT, user, passwd, db, 1))
     return (-1);
-  thd->password=test(passwd[0]);
   return 0;
 }
 
@@ -1007,7 +1015,6 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
       decrease_user_connections(save_uc);
     x_free((gptr) save_db);
     x_free((gptr) save_user);
-    thd->password=test(passwd[0]);
     break;
   }
 
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index e7afa7fbd23..55b5d57d07d 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -110,7 +110,8 @@ int mysql_union(THD *thd, LEX *lex,select_result *result)
     while ((item= it++))
       if (item_list.push_back(item))
 	DBUG_RETURN(-1);
-    if (setup_fields(thd,first_table,item_list,0,0,1))
+    if (setup_tables(first_table) ||
+	setup_fields(thd,first_table,item_list,0,0,1))
       DBUG_RETURN(-1);
   }
 
@@ -259,7 +260,7 @@ bool select_union::send_data(List<Item> &values)
     return 0;
   }
 
-  fill_record(table->field,values);
+  fill_record(table->field, values, 1);
   if ((write_record(table,&info)))
   {
     if (create_myisam_from_heap(thd, table, tmp_table_param, info.last_errno,
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 0dc6073278f..de953aa603b 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -277,7 +277,7 @@ int mysql_update(THD *thd,
     if (!(select && select->skipp_record()))
     {
       store_record(table,1);
-      if (fill_record(fields,values))
+      if (fill_record(fields, values, 0))
 	break; /* purecov: inspected */
       found++;
       if (compare_record(table, query_id))
@@ -726,7 +726,7 @@ bool multi_update::send_data(List<Item> &not_used_values)
     {
       table->status|= STATUS_UPDATED;
       store_record(table,1);
-      if (fill_record(*fields_for_table[offset], *values_for_table[offset]))
+      if (fill_record(*fields_for_table[offset], *values_for_table[offset],0 ))
 	DBUG_RETURN(1);
       found++;
       if (compare_record(table, thd->query_id))
@@ -754,7 +754,7 @@ bool multi_update::send_data(List<Item> &not_used_values)
     {
       int error;
       TABLE *tmp_table= tmp_tables[offset];
-      fill_record(tmp_table->field+1, *values_for_table[offset]);
+      fill_record(tmp_table->field+1, *values_for_table[offset], 1);
       found++;
       /* Store pointer to row */
       memcpy((char*) tmp_table->field[0]->ptr,