MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:dlenev Date:April 22 2006 7:54am
Subject:bk commit into 5.0 tree (dlenev:1.2164) BUG#15153
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of dlenev. When dlenev 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
  1.2164 06/04/22 11:54:25 dlenev@stripped +5 -0
  Fix for bug#15153 "CONVERT_TZ() is not allowed in all places in VIEWs".
  
  Error was emitted when one tried to select information from view which used
  merge algorithm and which also had CONVERT_TZ() function in its select list.
  
  This bug was caused by wrong assumption that global table list for view
  which is handled using merge algorithm begins from tables belonging to
  the main select of this view. Nowadays the above assumption is not true only
  when one uses convert_tz() function in view's select list, but in future
  other cases may be added (for example we may support merging of views
  with subqueries in select list one day). Relying on this false assumption
  led to the usage of wrong table list for field lookups and therefor errors. 
  
  With this fix we explicitly use pointer to the beginning of main select's
  table list.

  sql/sql_view.cc
    1.86 06/04/22 11:54:21 dlenev@stripped +12 -4
    mysql_make_view():
      We should not assume that global table list for view which is handled using
      merge algorithm begins from tables belonging to the main select of this
      view. Nowadays the above assumption is not true only when one uses
      convert_tz() function in view's select list, but in future other cases
      may be added (for example we may support merging of views with subqueries
      in select list one day). So let us instead explicitly use pointer to the
      beginning of main select's table list.

  mysql-test/t/view.test
    1.142 06/04/22 11:54:21 dlenev@stripped +27 -0
    Added test case for bug#15153 "CONVERT_TZ() is not allowed in all places in 
    VIEWs".

  mysql-test/t/timezone_grant.test
    1.5 06/04/22 11:54:21 dlenev@stripped +31 -0
    Added additional test case for bug#15153 "CONVERT_TZ() is not allowed in
    all places in VIEWs" that checks that usage of CONVERT_TZ() function in view
    does not require additional privileges.

  mysql-test/r/view.result
    1.153 06/04/22 11:54:21 dlenev@stripped +26 -0
    Added test case for bug#15153 "CONVERT_TZ() is not allowed in all places in 
    VIEWs".

  mysql-test/r/timezone_grant.result
    1.4 06/04/22 11:54:21 dlenev@stripped +17 -0
    Added additional test case for bug#15153 "CONVERT_TZ() is not allowed in
    all places in VIEWs" that checks that usage of CONVERT_TZ() function in view
    does not require additional privileges.

# 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:	dlenev
# Host:	jabberwock.site
# Root:	/home/dlenev/mysql-5.0-bg15153-2

--- 1.152/mysql-test/r/view.result	2006-04-14 00:12:22 +04:00
+++ 1.153/mysql-test/r/view.result	2006-04-22 11:54:21 +04:00
@@ -2623,3 +2623,29 @@
 ERROR HY000: Recursive stored functions and triggers are not allowed.
 drop function f1;
 drop view t1, v1;
+create table t1 (dt datetime);
+insert into t1 values (20040101000000), (20050101000000), (20060101000000);
+create view v1 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from t1;
+select * from v1;
+ldt
+2004-01-01 03:00:00
+2005-01-01 03:00:00
+2006-01-01 03:00:00
+drop view v1;
+create view v1 as select * from t1 where convert_tz(dt, 'UTC', 'Europe/Moscow') >= 20050101000000;
+select * from v1;
+dt
+2005-01-01 00:00:00
+2006-01-01 00:00:00
+create view v2 as select * from v1 where dt < 20060101000000;
+select * from v2;
+dt
+2005-01-01 00:00:00
+drop view v2;
+create view v2 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from v1;
+select * from v2;
+ldt
+2005-01-01 03:00:00
+2006-01-01 03:00:00
+drop view v1, v2;
+drop table t1;

--- 1.141/mysql-test/t/view.test	2006-04-14 00:12:22 +04:00
+++ 1.142/mysql-test/t/view.test	2006-04-22 11:54:21 +04:00
@@ -2485,3 +2485,30 @@
 select * from v1;
 drop function f1;
 drop view t1, v1;
+
+#
+# Bug #15153: CONVERT_TZ() is not allowed in all places in VIEWs
+#
+# Error was reported when one tried to use CONVERT_TZ() function
+# select list of view which was processed using MERGE algorithm.
+# (Also see additional test in timezone_grant.test)
+create table t1 (dt datetime);
+insert into t1 values (20040101000000), (20050101000000), (20060101000000);
+# Let us test that convert_tz() can be used in view's select list
+create view v1 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from t1;
+select * from v1;
+drop view v1;
+# And in its where part 
+create view v1 as select * from t1 where convert_tz(dt, 'UTC', 'Europe/Moscow') >= 20050101000000;
+select * from v1;
+# Other interesting case - a view which uses convert_tz() function
+# through other view.
+create view v2 as select * from v1 where dt < 20060101000000;
+select * from v2;
+drop view v2;
+# And even more interesting case when view uses convert_tz() both
+# directly and indirectly
+create view v2 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from v1;
+select * from v2;
+drop view v1, v2;
+drop table t1;

--- 1.85/sql/sql_view.cc	2006-04-14 00:12:22 +04:00
+++ 1.86/sql/sql_view.cc	2006-04-22 11:54:21 +04:00
@@ -1057,15 +1057,23 @@
         !old_lex->can_not_use_merged())
     {
       List_iterator_fast<TABLE_LIST> ti(view_select->top_join_list);
+      /*
+        Currently 'view_main_select_tables' differs from 'view_tables'
+        only then view has CONVERT_TZ() function in its select list.
+        This may change in future, for example if we enable merging
+        of views with subqueries in select list.
+      */
+      TABLE_LIST *view_main_select_tables=
+                    (TABLE_LIST*)lex->select_lex.table_list.first;
       /* lex should contain at least one table */
-      DBUG_ASSERT(view_tables != 0);
+      DBUG_ASSERT(view_main_select_tables != 0);
 
       table->effective_algorithm= VIEW_ALGORITHM_MERGE;
       DBUG_PRINT("info", ("algorithm: MERGE"));
       table->updatable= (table->updatable_view != 0);
       table->effective_with_check=
         old_lex->get_effective_with_check(table);
-      table->merge_underlying_list= view_tables;
+      table->merge_underlying_list= view_main_select_tables;
       /*
         Let us set proper lock type for tables of the view's main select
         since we may want to perform update or insert on view. This won't
@@ -1081,7 +1089,7 @@
       }
 
       /* prepare view context */
-      lex->select_lex.context.resolve_in_table_list_only(view_tables);
+      lex->select_lex.context.resolve_in_table_list_only(view_main_select_tables);
       lex->select_lex.context.outer_context= 0;
       lex->select_lex.context.select_lex= table->select_lex;
       lex->select_lex.select_n_having_items+=
@@ -1097,7 +1105,7 @@
         tbl->select_lex= table->select_lex;
 
       {
-        if (view_tables->next_local)
+        if (view_main_select_tables->next_local)
         {
           table->multitable_view= TRUE;
           if (table->belong_to_view)

--- 1.3/mysql-test/r/timezone_grant.result	2005-07-19 00:49:14 +04:00
+++ 1.4/mysql-test/r/timezone_grant.result	2006-04-22 11:54:21 +04:00
@@ -1,3 +1,5 @@
+drop tables if exists t1, t2;
+drop view if exists v1;
 delete from mysql.user where user like 'mysqltest\_%';
 delete from mysql.db where user like 'mysqltest\_%';
 delete from mysql.tables_priv where user like 'mysqltest\_%';
@@ -59,3 +61,18 @@
 delete from mysql.tables_priv where user like 'mysqltest\_%';
 flush privileges;
 drop table t1, t2;
+create table t1 (a int, b datetime);
+insert into t1 values (1, 20010101000000), (2, 20020101000000);
+grant all privileges on test.* to mysqltest_1@localhost;
+create view v1 as select a, convert_tz(b, 'UTC', 'Europe/Moscow') as lb from t1;
+select * from v1;
+a	lb
+1	2001-01-01 03:00:00
+2	2002-01-01 03:00:00
+select * from v1, mysql.time_zone;
+ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'time_zone'
+drop view v1;
+create view v1 as select a, convert_tz(b, 'UTC', 'Europe/Moscow') as lb from t1, mysql.time_zone;
+ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for table 'time_zone'
+drop table t1;
+drop user mysqltest_1@localhost;

--- 1.4/mysql-test/t/timezone_grant.test	2005-07-28 17:12:37 +04:00
+++ 1.5/mysql-test/t/timezone_grant.test	2006-04-22 11:54:21 +04:00
@@ -1,6 +1,11 @@
 # Embedded server testing does not support grants
 -- source include/not_embedded.inc
 
+--disable_warnings
+drop tables if exists t1, t2;
+drop view if exists v1;
+--enable_warnings
+
 #
 # Test for bug #6116 "SET time_zone := ... requires access to mysql.time_zone
 # tables". We should allow implicit access to time zone description tables
@@ -82,3 +87,29 @@
 drop table t1, t2;
 
 # End of 4.1 tests
+
+#
+# Additional test for bug #15153: CONVERT_TZ() is not allowed in all
+# places in views.
+#
+# Let us check that usage of CONVERT_TZ() function in view does not 
+# require additional privileges.
+
+# Let us rely on that previous tests done proper cleanups
+create table t1 (a int, b datetime);
+insert into t1 values (1, 20010101000000), (2, 20020101000000);
+grant all privileges on test.* to mysqltest_1@localhost;
+connect (tzuser3, localhost, mysqltest_1,,);
+create view v1 as select a, convert_tz(b, 'UTC', 'Europe/Moscow') as lb from t1;
+select * from v1;
+# Of course we should not be able select from mysql.time_zone tables
+--error ER_TABLEACCESS_DENIED_ERROR
+select * from v1, mysql.time_zone;
+drop view v1;
+--error ER_TABLEACCESS_DENIED_ERROR
+create view v1 as select a, convert_tz(b, 'UTC', 'Europe/Moscow') as lb from t1, mysql.time_zone;
+connection default;
+drop table t1;
+drop user mysqltest_1@localhost;
+
+# End of 5.0 tests
Thread
bk commit into 5.0 tree (dlenev:1.2164) BUG#15153dlenev22 Apr