summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/rpl_view.result44
-rw-r--r--mysql-test/r/view.result20
-rw-r--r--mysql-test/t/rpl_view.test44
-rw-r--r--mysql-test/t/view.test18
-rw-r--r--sql/sql_acl.cc3
-rw-r--r--sql/sql_parse.cc14
-rw-r--r--sql/table.cc17
7 files changed, 156 insertions, 4 deletions
diff --git a/mysql-test/r/rpl_view.result b/mysql-test/r/rpl_view.result
new file mode 100644
index 00000000000..ce807a361ba
--- /dev/null
+++ b/mysql-test/r/rpl_view.result
@@ -0,0 +1,44 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+drop table if exists t1,v1;
+drop view if exists t1,v1;
+create table t1 (a int);
+insert into t1 values (1);
+create view v1 as select a from t1;
+insert into v1 values (2);
+select * from v1 order by a;
+a
+1
+2
+select * from v1 order by a;
+a
+1
+2
+update v1 set a=3 where a=1;
+select * from v1 order by a;
+a
+2
+3
+select * from v1 order by a;
+a
+2
+3
+delete from v1 where a=2;
+select * from v1 order by a;
+a
+3
+select * from v1 order by a;
+a
+3
+alter view v1 as select a as b from t1;
+select * from v1 order by 1;
+b
+3
+drop view v1;
+select * from v1 order by a;
+ERROR 42S02: Table 'test.v1' doesn't exist
+drop table t1;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 239849ed8d1..01f1fcbcbf4 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -1730,3 +1730,23 @@ select * from v1 where F1 = 1;
f1
drop view v1;
drop table t1;
+create table t1(c1 int);
+create table t2(c2 int);
+insert into t1 values (1),(2),(3);
+insert into t2 values (1);
+SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
+c1
+1
+SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1);
+c1
+1
+create view v1 as SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
+create view v2 as SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1);
+select * from v1;
+c1
+1
+select * from v2;
+c1
+1
+drop view v2, v1;
+drop table t1, t2;
diff --git a/mysql-test/t/rpl_view.test b/mysql-test/t/rpl_view.test
new file mode 100644
index 00000000000..c50e9fc6dc9
--- /dev/null
+++ b/mysql-test/t/rpl_view.test
@@ -0,0 +1,44 @@
+source include/master-slave.inc;
+--disable_warnings
+drop table if exists t1,v1;
+drop view if exists t1,v1;
+sync_slave_with_master;
+--enable_warnings
+
+#
+# Check that createion drop of view is replicated, also check replication of
+# updating of view
+#
+connection master;
+create table t1 (a int);
+insert into t1 values (1);
+create view v1 as select a from t1;
+insert into v1 values (2);
+select * from v1 order by a;
+sync_slave_with_master;
+# view already have to be on slave
+select * from v1 order by a;
+connection master;
+update v1 set a=3 where a=1;
+select * from v1 order by a;
+sync_slave_with_master;
+select * from v1 order by a;
+connection master;
+delete from v1 where a=2;
+select * from v1 order by a;
+sync_slave_with_master;
+select * from v1 order by a;
+connection master;
+# 'alter view' internally maped to creation, but still check that it works
+alter view v1 as select a as b from t1;
+sync_slave_with_master;
+select * from v1 order by 1;
+connection master;
+drop view v1;
+sync_slave_with_master;
+#error, because view have to be removed from slave
+-- error 1146
+select * from v1 order by a;
+connection master;
+drop table t1;
+sync_slave_with_master;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index ed7401adaab..81155fc2335 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -1655,9 +1655,27 @@ select * from v3;
drop view v3;
drop tables t1,t2;
+#
# View field names should be case insensitive
+#
create table t1(f1 int);
create view v1 as select f1 from t1;
select * from v1 where F1 = 1;
drop view v1;
drop table t1;
+
+#
+# Resolving view fields in subqueries in VIEW (Bug #6394)
+#
+create table t1(c1 int);
+create table t2(c2 int);
+insert into t1 values (1),(2),(3);
+insert into t2 values (1);
+SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
+SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1);
+create view v1 as SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
+create view v2 as SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1);
+select * from v1;
+select * from v2;
+drop view v2, v1;
+drop table t1, t2;
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index bcad0e627f4..5f7d51ffe07 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -5516,6 +5516,9 @@ void fill_effective_table_privileges(THD *thd, GRANT_INFO *grant,
/* global privileges */
grant->privilege= thd->master_access;
+ if (!thd->priv_user)
+ return; // it is slave
+
/* db privileges */
grant->privilege|= acl_get(thd->host, thd->ip, thd->priv_user, db, 0);
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index b7a75a0bdd6..13e7a9c7fab 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4059,7 +4059,12 @@ unsent_create_error:
}
case SQLCOM_CREATE_VIEW:
{
- res= mysql_create_view(thd, thd->lex->create_view_mode);
+ if (!(res= mysql_create_view(thd, thd->lex->create_view_mode)) &&
+ mysql_bin_log.is_open())
+ {
+ Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE);
+ mysql_bin_log.write(&qinfo);
+ }
break;
}
case SQLCOM_DROP_VIEW:
@@ -4067,7 +4072,12 @@ unsent_create_error:
if (check_table_access(thd, DROP_ACL, all_tables, 0) ||
end_active_trans(thd))
goto error;
- res= mysql_drop_view(thd, first_table, thd->lex->drop_mode);
+ if (!(res= mysql_drop_view(thd, first_table, thd->lex->drop_mode)) &&
+ mysql_bin_log.is_open())
+ {
+ Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE);
+ mysql_bin_log.write(&qinfo);
+ }
break;
}
case SQLCOM_CREATE_TRIGGER:
diff --git a/sql/table.cc b/sql/table.cc
index a030da95db4..ff8841fc8ca 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -1695,6 +1695,7 @@ bool st_table_list::setup_ancestor(THD *thd, Item **conds,
Field_translator *transl;
SELECT_LEX *select= &view->select_lex;
SELECT_LEX *current_select_save= thd->lex->current_select;
+ byte *main_table_list_save= thd->lex->select_lex.table_list.first;
Item *item;
TABLE_LIST *tbl;
List_iterator_fast<Item> it(select->item_list);
@@ -1717,8 +1718,13 @@ bool st_table_list::setup_ancestor(THD *thd, Item **conds,
if (field_translation)
{
DBUG_PRINT("info", ("there are already translation table"));
- /* prevent look up in SELECTs tree */
+ /*
+ prevent look up in SELECTs tree, and emulate main table list by
+ ancestor table list for subquery processing
+ */
thd->lex->current_select= &thd->lex->select_lex;
+ thd->lex->select_lex.table_list.first= (byte *)ancestor;
+
thd->lex->select_lex.no_wrap_view_item= 1;
thd->set_query_id= 1;
/* this view was prepared already on previous PS/SP execution */
@@ -1763,8 +1769,13 @@ bool st_table_list::setup_ancestor(THD *thd, Item **conds,
DBUG_RETURN(1);
}
- /* prevent look up in SELECTs tree */
+ /*
+ prevent look up in SELECTs tree, and emulate main table list by ancestor
+ table list for subquery processing
+ */
thd->lex->current_select= &thd->lex->select_lex;
+ thd->lex->select_lex.table_list.first= (byte *)ancestor;
+
thd->lex->select_lex.no_wrap_view_item= 1;
/*
@@ -1909,6 +1920,7 @@ bool st_table_list::setup_ancestor(THD *thd, Item **conds,
ok:
thd->lex->select_lex.no_wrap_view_item= save_wrapper;
thd->lex->current_select= current_select_save;
+ thd->lex->select_lex.table_list.first= main_table_list_save;
thd->set_query_id= save_set_query_id;
thd->allow_sum_func= save_allow_sum_func;
DBUG_RETURN(0);
@@ -1923,6 +1935,7 @@ err:
}
thd->lex->select_lex.no_wrap_view_item= save_wrapper;
thd->lex->current_select= current_select_save;
+ thd->lex->select_lex.table_list.first= main_table_list_save;
thd->set_query_id= save_set_query_id;
thd->allow_sum_func= save_allow_sum_func;
DBUG_RETURN(1);
View Lorry Controller Status