bk commit into 5.0 tree (igor:1.2215) BUG#21231

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor 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-24 19:05:46-07:00, igor@stripped +3 -0
  Fixed bug #21231: wrong results for a simple query with a
  a non-correlated single-row subquery over information schema.
  
  The function get_all_tables filling all information schema
  tables reset lex->sql_command to SQLCOM_SHOW_FIELDS. After
  this the function could evaluate partial conditions related to
  some columns. If these conditions contained a subquery over
  information schema it led to a wrong evaluation and a wrong 
  result set.
  This bug was already fixed in 5.1.
  This patch follows the way how it was done in 5.1 where
  the value of lex->sql_command is set to SQLCOM_SHOW_FIELDS
  in get_all_tables only for the calls of the function 
  open_normal_and_derived_tables and is restored after these
  calls.

  mysql-test/r/information_schema.result@stripped, 2006-07-24 19:05:43-07:00, igor@stripped +8 -0
    Added a test case for bug #21231.

  mysql-test/t/information_schema.test@stripped, 2006-07-24 19:05:44-07:00, igor@stripped +10 -0
    Added a test case for bug #21231.

  sql/sql_show.cc@stripped, 2006-07-24 19:05:44-07:00, igor@stripped +10 -6
    Fixed bug #21231: wrong results for a simple query with a
    a non-correlated single-row subquery over information schema.
    
    The function get_all_tables filling all information schema
    tables reset lex->sql_command to SQLCOM_SHOW_FIELDS. After
    this the function could evaluate partial conditions related to
    some columns. If these conditions contained a subquery over
    information schema it led to a wrong evaluation and a wrong 
    result set.
    This bug was already fixed in 5.1.
    This patch follows the way how it was done in 5.1 where
    the value of lex->sql_command is set to SQLCOM_SHOW_FIELDS
    in get_all_tables only for the calls of the function 
    open_normal_and_derived_tables and is restored after these
    calls.

# 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:	igor
# Host:	olga.mysql.com
# Root:	/home/igor/mysql-5.0-opt

--- 1.324/sql/sql_show.cc	2006-07-24 19:05:51 -07:00
+++ 1.325/sql/sql_show.cc	2006-07-24 19:05:51 -07:00
@@ -2116,12 +2116,6 @@
   LINT_INIT(end);
   LINT_INIT(len);
 
-  /*
-    Let us set fake sql_command so views won't try to merge
-    themselves into main statement.
-  */
-  lex->sql_command= SQLCOM_SHOW_FIELDS;
-
   lex->reset_n_backup_query_tables_list(&query_tables_list_backup);
 
   /*
@@ -2144,8 +2138,16 @@
       I_S tables will be done.
     */
     thd->temporary_tables= open_tables_state_backup.temporary_tables;
+    /*
+      Let us set fake sql_command so views won't try to merge
+      themselves into main statement. If we don't do this,
+      SELECT * from information_schema.xxxx will cause problems.
+      SQLCOM_SHOW_FIELDS is used because it satisfies 'only_view_structure()' 
+    */
+    lex->sql_command= SQLCOM_SHOW_FIELDS;
     res= open_normal_and_derived_tables(thd, show_table_list,
                                         MYSQL_LOCK_IGNORE_FLUSH);
+    lex->sql_command= save_sql_command;
     /*
       get_all_tables() returns 1 on failure and 0 on success thus
       return only these and not the result code of ::process_table()
@@ -2267,8 +2269,10 @@
             TABLE_LIST *show_table_list= (TABLE_LIST*) sel.table_list.first;
             lex->all_selects_list= &sel;
             lex->derived_tables= 0;
+            lex->sql_command= SQLCOM_SHOW_FIELDS;
             res= open_normal_and_derived_tables(thd, show_table_list,
                                                 MYSQL_LOCK_IGNORE_FLUSH);
+            lex->sql_command= save_sql_command;
             /*
               We should use show_table_list->alias instead of 
               show_table_list->table_name because table_name

--- 1.112/mysql-test/r/information_schema.result	2006-07-24 19:05:51 -07:00
+++ 1.113/mysql-test/r/information_schema.result	2006-07-24 19:05:51 -07:00
@@ -1232,3 +1232,11 @@
 TRIGGERS	TRIGGER_SCHEMA
 USER_PRIVILEGES	GRANTEE
 VIEWS	TABLE_SCHEMA
+SELECT MAX(table_name) FROM information_schema.tables;
+MAX(table_name)
+VIEWS
+SELECT table_name from information_schema.tables
+WHERE table_name=(SELECT MAX(table_name)
+FROM information_schema.tables);
+table_name
+VIEWS

--- 1.84/mysql-test/t/information_schema.test	2006-07-24 19:05:51 -07:00
+++ 1.85/mysql-test/t/information_schema.test	2006-07-24 19:05:51 -07:00
@@ -920,4 +920,14 @@
                   c2.column_name LIKE '%SCHEMA%'
         );
 
+#
+# Bug#21231: query with a simple non-correlated subquery over
+#            INFORMARTION_SCHEMA.TABLES 
+#
+
+SELECT MAX(table_name) FROM information_schema.tables;
+SELECT table_name from information_schema.tables
+  WHERE table_name=(SELECT MAX(table_name)
+                      FROM information_schema.tables);
+
 # End of 5.0 tests.