bk commit into 5.0 tree (kostja:1.2228) BUG#21002

[konstantin]

Below is the list of changes that have just been committed into a local
5.0 repository of kostja. When kostja does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-07-19 22:31:34+04:00, kostja@stripped +9 -0
  A fix and a test case for Bug#21002 "Derived table not selecting from a 
  "real" table fails in JOINs".
  
  This is a regression caused by the fix for Bug 18444. 
  This fix removed the assignment of empty_c_string to table->db performed 
  in add_table_to_list, as neither me nor anyone else knew what it was 
  there for. Now we know it and it's covered with tests: the only case 
  when a table database name can be empty is when the table is a derived 
  table. The fix puts the assignment back but makes it a bit more explicit.
  
  Additionally, finally drop sp.result.orig which was checked in by mistake. 

  BitKeeper/deleted/.del-sp.result.orig@stripped, 2006-07-15 02:33:17+04:00, kostja@stripped
+0 -0
    Delete: mysql-test/r/sp.result.orig

  mysql-test/r/derived.result@stripped, 2006-07-19 22:31:31+04:00, kostja@stripped +1 -1
    Updated result file.

  mysql-test/r/sp.result@stripped, 2006-07-19 22:31:31+04:00, kostja@stripped +15 -0
    Test results fixed (Bug#21002)

  mysql-test/t/derived.test@stripped, 2006-07-19 22:31:31+04:00, kostja@stripped +1 -1
    New error return for the case when MULTI-DELETE tries to delete from
    a derived table: now derived tables belong to their own db (""), and
    MUTLI-DELETE can't find the correspondent table for it in the 
    DELETE list, as it can't resolve tables in different dbs by alias
    (See Bug#21148 for details)

  mysql-test/t/sp.test@stripped, 2006-07-19 22:31:31+04:00, kostja@stripped +25 -0
    Add a test case for Bug#21002 "Derived table not selecting from a "real"
     table fails in JOINs"

  sql/sp.cc@stripped, 2006-07-19 22:31:31+04:00, kostja@stripped +0 -1
    Make empty_c_string globally accessible.

  sql/sql_class.cc@stripped, 2006-07-19 22:31:31+04:00, kostja@stripped +1 -0
    Add empty_c_string definition.

  sql/sql_class.h@stripped, 2006-07-19 22:31:31+04:00, kostja@stripped +12 -1
    Add a comment for the constructor of Table_ident which is
    used for derived tables. Make sure this constructor also initializes
    the database name, not only the table name.

  sql/sql_parse.cc@stripped, 2006-07-19 22:31:31+04:00, kostja@stripped +6 -2
    Don't call check_db_name for empty database. 
    Currently the only case when a table database name can be empty
    is when the table is a derived table.
    Report the right error if the database name is wrong (ER_WRONG_DB_NAME,
    not ER_WRONG_TABLE_NAME). 

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	kostja
# Host:	bodhi.local
# Root:	/opt/local/work/mysql-5.0-21002

--- 1.236/sql/sql_class.cc	2006-07-19 22:31:43 +04:00
+++ 1.237/sql/sql_class.cc	2006-07-19 22:31:43 +04:00
@@ -43,6 +43,7 @@
   table name
 */
 char internal_table_name[2]= "*";
+char empty_c_string[1]= {0};    /* used for not defined db */
 
 const char * const THD::DEFAULT_WHERE= "field list";
 

--- 1.292/sql/sql_class.h	2006-07-19 22:31:43 +04:00
+++ 1.293/sql/sql_class.h	2006-07-19 22:31:43 +04:00
@@ -41,6 +41,7 @@ enum enum_check_fields { CHECK_FIELD_IGN
 			 CHECK_FIELD_ERROR_FOR_NULL };
 
 extern char internal_table_name[2];
+extern char empty_c_string[1];
 extern const char **errmesg;
 
 #define TC_LOG_PAGE_SIZE   8192
@@ -1977,11 +1978,21 @@ public:
   {
     db.str=0;
   }
+  /*
+    This constructor is used only for the case when we create a derived
+    table. A derived table has no name and doesn't belong to any database.
+    Later, if there was an alias specified for the table, it will be set
+    by add_table_to_list.
+  */
   inline Table_ident(SELECT_LEX_UNIT *s) : sel(s)
   {
     /* We must have a table name here as this is used with add_table_to_list */
-    db.str=0; table.str= internal_table_name; table.length=1;
+    db.str= empty_c_string;                    /* a subject to casedn_str */
+    db.length= 0;
+    table.str= internal_table_name;
+    table.length=1;
   }
+  bool is_derived_table() const { return test(sel); }
   inline void change_db(char *db_name)
   {
     db.str= db_name; db.length= (uint) strlen(db_name);

--- 1.556/sql/sql_parse.cc	2006-07-19 22:31:43 +04:00
+++ 1.557/sql/sql_parse.cc	2006-07-19 22:31:43 +04:00
@@ -6088,8 +6088,7 @@ TABLE_LIST *st_select_lex::add_table_to_
   if (!table)
     DBUG_RETURN(0);				// End of memory
   alias_str= alias ? alias->str : table->table.str;
-  if (check_table_name(table->table.str,table->table.length) ||
-      table->db.str && check_db_name(table->db.str))
+  if (check_table_name(table->table.str, table->table.length))
   {
     my_error(ER_WRONG_TABLE_NAME, MYF(0), table->table.str);
     DBUG_RETURN(0);
@@ -6110,6 +6109,11 @@ TABLE_LIST *st_select_lex::add_table_to_
     DBUG_RETURN(0);				/* purecov: inspected */
   if (table->db.str)
   {
+    if (table->is_derived_table() == FALSE && check_db_name(table->db.str))
+    {
+      my_error(ER_WRONG_DB_NAME, MYF(0), table->db.str);
+      DBUG_RETURN(0);
+    }
     ptr->db= table->db.str;
     ptr->db_length= table->db.length;
   }

--- 1.56/mysql-test/r/derived.result	2006-07-19 22:31:43 +04:00
+++ 1.57/mysql-test/r/derived.result	2006-07-19 22:31:43 +04:00
@@ -276,7 +276,7 @@ select * from t1;
 N	M
 3	0
 delete P1.*,p2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING
Count(M) > 1) AS p2 ON P1.N = p2.N;
-ERROR HY000: The target table p2 of the DELETE is not updatable
+ERROR 42S02: Unknown table 'p2' in MULTI DELETE
 delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M)
> 1) AS P2 ON P1.N = P2.N;
 ERROR 42S22: Unknown column 'aaa' in 'field list'
 drop table t1;

--- 1.51/mysql-test/t/derived.test	2006-07-19 22:31:43 +04:00
+++ 1.52/mysql-test/t/derived.test	2006-07-19 22:31:43 +04:00
@@ -157,7 +157,7 @@ UPDATE `t1` AS P1 INNER JOIN (SELECT aaa
 delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M)
> 1) AS P2 ON P1.N = P2.N;
 select * from t1;
 --replace_result P2 p2
---error 1288
+--error ER_UNKNOWN_TABLE 
 delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING
Count(M) > 1) AS P2 ON P1.N = P2.N;
 -- error 1054
 delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M)
> 1) AS P2 ON P1.N = P2.N;

--- 1.202/mysql-test/r/sp.result	2006-07-19 22:31:43 +04:00
+++ 1.203/mysql-test/r/sp.result	2006-07-19 22:31:43 +04:00
@@ -5057,4 +5057,19 @@ concat('data was: /', var1, '/')
 data was: /1/
 drop table t3|
 drop procedure bug15217|
+drop table if exists t3|
+drop database if exists mysqltest1|
+create table t3 (a int)|
+insert into t3 (a) values (1), (2)|
+create database mysqltest1|
+use mysqltest1|
+drop database mysqltest1|
+select database()|
+database()
+NULL
+select * from (select 1 as a) as t1 natural join (select * from test.t3) as t2|
+a
+1
+use test|
+drop table t3|
 drop table t1,t2;

--- 1.190/mysql-test/t/sp.test	2006-07-19 22:31:43 +04:00
+++ 1.191/mysql-test/t/sp.test	2006-07-19 22:31:43 +04:00
@@ -5963,6 +5963,31 @@ drop table t3|
 drop procedure bug15217|
 
 #
+# Bug#21002 "Derived table not selecting from a "real" table fails in JOINs"
+#         
+# A regression caused by the fix for Bug#18444: for derived tables we should
+# set an empty string as the current database. They do not belong to any
+# database and must be usable even if there is no database
+# selected.
+--disable_warnings
+drop table if exists t3|
+drop database if exists mysqltest1|
+--enable_warnings
+create table t3 (a int)|
+insert into t3 (a) values (1), (2)|
+
+create database mysqltest1|
+use mysqltest1|
+drop database mysqltest1|
+
+# No current database
+select database()|
+
+select * from (select 1 as a) as t1 natural join (select * from test.t3) as t2|
+use test|
+drop table t3|
+
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings

--- 1.113/sql/sp.cc	2006-07-19 22:31:43 +04:00
+++ 1.114/sql/sp.cc	2006-07-19 22:31:43 +04:00
@@ -1846,7 +1846,6 @@ sp_use_new_db(THD *thd, LEX_STRING new_d
 	      bool no_access_check, bool *dbchangedp)
 {
   int ret;
-  static char empty_c_string[1]= {0};          /* used for not defined db */
   DBUG_ENTER("sp_use_new_db");
   DBUG_PRINT("enter", ("newdb: %s", new_db.str));