From: Date: July 22 2006 9:45pm Subject: bk commit into 5.0 tree (igor:1.2209) BUG#18925 List-Archive: http://lists.mysql.com/commits/9454 X-Bug: 18925 Message-Id: <20060722194500.69DC57B035@olga.mysql.com> 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-22 12:44:54-07:00, igor@stripped +3 -0 Fixed bug #18925: wrong results for queries with subqueries on information schema that use MIN/MAX aggregation. Execution of some correlated subqueries may set the value of null_row to 1 for tables used in the subquery. If the the subquery is on information schema it causes rejection of any row for the following executions of the subquery in the case when an optimization filtering by some condition is applied. The fix restores the value of the null_row flag for each execution of a subquery on information schema. mysql-test/r/information_schema.result@stripped, 2006-07-22 12:44:51-07:00, igor@stripped +62 -0 Added a test case for bug #18925. mysql-test/t/information_schema.test@stripped, 2006-07-22 12:44:51-07:00, igor@stripped +33 -0 Added a test case for bug #18925. sql/sql_show.cc@stripped, 2006-07-22 12:44:51-07:00, igor@stripped +1 -0 Fixed bug #18925. Execution of some correlated subqueries may set the value of null_row to 1 for tables used in the subquery. If the the subquery is on information schema it causes rejection of any row for the following execitions of the subquery in the case when an optimization filtering by some condition is applied. The fix restores the value of the null_row flag for each execution of a subquery on information schema. # 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.323/sql/sql_show.cc 2006-07-22 12:45:00 -07:00 +++ 1.324/sql/sql_show.cc 2006-07-22 12:45:00 -07:00 @@ -3963,6 +3963,7 @@ table_list->table->file->delete_all_rows(); free_io_cache(table_list->table); filesort_free_buffers(table_list->table); + table_list->table->null_row= 0; } else table_list->table->file->records= 0; --- 1.111/mysql-test/r/information_schema.result 2006-07-22 12:45:00 -07:00 +++ 1.112/mysql-test/r/information_schema.result 2006-07-22 12:45:00 -07:00 @@ -1170,3 +1170,65 @@ DROP FUNCTION f1; DROP PROCEDURE p1; DROP USER mysql_bug20230@localhost; +SELECT t.table_name, c1.column_name +FROM information_schema.tables t +INNER JOIN +information_schema.columns c1 +ON t.table_schema = c1.table_schema AND +t.table_name = c1.table_name +WHERE t.table_schema = 'information_schema' AND +c1.ordinal_position = +( SELECT COALESCE(MIN(c2.ordinal_position),1) +FROM information_schema.columns c2 +WHERE c2.table_schema = t.table_schema AND +c2.table_name = t.table_name AND +c2.column_name LIKE '%SCHEMA%' + ); +table_name column_name +CHARACTER_SETS CHARACTER_SET_NAME +COLLATIONS COLLATION_NAME +COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME +COLUMNS TABLE_SCHEMA +COLUMN_PRIVILEGES TABLE_SCHEMA +KEY_COLUMN_USAGE CONSTRAINT_SCHEMA +ROUTINES ROUTINE_SCHEMA +SCHEMATA SCHEMA_NAME +SCHEMA_PRIVILEGES TABLE_SCHEMA +STATISTICS TABLE_SCHEMA +TABLES TABLE_SCHEMA +TABLE_CONSTRAINTS CONSTRAINT_SCHEMA +TABLE_PRIVILEGES TABLE_SCHEMA +TRIGGERS TRIGGER_SCHEMA +USER_PRIVILEGES GRANTEE +VIEWS TABLE_SCHEMA +SELECT t.table_name, c1.column_name +FROM information_schema.tables t +INNER JOIN +information_schema.columns c1 +ON t.table_schema = c1.table_schema AND +t.table_name = c1.table_name +WHERE t.table_schema = 'information_schema' AND +c1.ordinal_position = +( SELECT COALESCE(MIN(c2.ordinal_position),1) +FROM information_schema.columns c2 +WHERE c2.table_schema = 'information_schema' AND +c2.table_name = t.table_name AND +c2.column_name LIKE '%SCHEMA%' + ); +table_name column_name +CHARACTER_SETS CHARACTER_SET_NAME +COLLATIONS COLLATION_NAME +COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME +COLUMNS TABLE_SCHEMA +COLUMN_PRIVILEGES TABLE_SCHEMA +KEY_COLUMN_USAGE CONSTRAINT_SCHEMA +ROUTINES ROUTINE_SCHEMA +SCHEMATA SCHEMA_NAME +SCHEMA_PRIVILEGES TABLE_SCHEMA +STATISTICS TABLE_SCHEMA +TABLES TABLE_SCHEMA +TABLE_CONSTRAINTS CONSTRAINT_SCHEMA +TABLE_PRIVILEGES TABLE_SCHEMA +TRIGGERS TRIGGER_SCHEMA +USER_PRIVILEGES GRANTEE +VIEWS TABLE_SCHEMA --- 1.83/mysql-test/t/information_schema.test 2006-07-22 12:45:00 -07:00 +++ 1.84/mysql-test/t/information_schema.test 2006-07-22 12:45:00 -07:00 @@ -887,4 +887,37 @@ DROP PROCEDURE p1; DROP USER mysql_bug20230@localhost; +# +# Bug#18925: subqueries with MIN/MAX functions on INFORMARTION_SCHEMA +# + +SELECT t.table_name, c1.column_name + FROM information_schema.tables t + INNER JOIN + information_schema.columns c1 + ON t.table_schema = c1.table_schema AND + t.table_name = c1.table_name + WHERE t.table_schema = 'information_schema' AND + c1.ordinal_position = + ( SELECT COALESCE(MIN(c2.ordinal_position),1) + FROM information_schema.columns c2 + WHERE c2.table_schema = t.table_schema AND + c2.table_name = t.table_name AND + c2.column_name LIKE '%SCHEMA%' + ); +SELECT t.table_name, c1.column_name + FROM information_schema.tables t + INNER JOIN + information_schema.columns c1 + ON t.table_schema = c1.table_schema AND + t.table_name = c1.table_name + WHERE t.table_schema = 'information_schema' AND + c1.ordinal_position = + ( SELECT COALESCE(MIN(c2.ordinal_position),1) + FROM information_schema.columns c2 + WHERE c2.table_schema = 'information_schema' AND + c2.table_name = t.table_name AND + c2.column_name LIKE '%SCHEMA%' + ); + # End of 5.0 tests.