summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/view.result41
-rw-r--r--mysql-test/t/view.test24
-rw-r--r--sql/sql_base.cc4
-rw-r--r--sql/sql_delete.cc2
-rw-r--r--sql/sql_insert.cc8
-rw-r--r--sql/sql_parse.cc2
-rw-r--r--sql/sql_prepare.cc6
-rw-r--r--sql/sql_select.cc4
-rw-r--r--sql/sql_update.cc14
-rw-r--r--sql/sql_view.cc4
-rw-r--r--sql/table.cc28
-rw-r--r--sql/table.h7
12 files changed, 80 insertions, 64 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 59ff6abdde2..1d5b492724e 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -51,7 +51,7 @@ explain extended select c from v1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 5
Warnings:
-Note 1003 select (`test`.`t1`.`b` + 1) AS `c` from `test`.`v1`
+Note 1003 select (`test`.`t1`.`b` + 1) AS `c` from `test`.`t1`
create algorithm=temptable view v2 (c) as select b+1 from t1;
show create view v2;
View Create View
@@ -85,7 +85,7 @@ explain extended select c from v3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 5
Warnings:
-Note 1003 select ((`test`.`t1`.`b` + 1) + 1) AS `c` from `test`.`v3`
+Note 1003 select ((`test`.`t1`.`b` + 1) + 1) AS `c` from `test`.`t1`
create algorithm=temptable view v4 (c) as select c+1 from v2;
select c from v4;
c
@@ -114,7 +114,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5
3 DERIVED t1 ALL NULL NULL NULL NULL 5
Warnings:
-Note 1003 select (`v2`.`c` + 1) AS `c` from `test`.`v5`
+Note 1003 select (`v2`.`c` + 1) AS `c` from `test`.`v2`
create algorithm=temptable view v6 (c) as select c+1 from v1;
select c from v6;
c
@@ -204,21 +204,6 @@ create table t1 (a int);
insert into t1 values (1), (2), (3);
create view v1 (a) as select a+1 from t1;
create view v2 (a) as select a-1 from t1;
-select * from t1 natural left join v1;
-a a
-1 NULL
-2 2
-3 3
-select * from v2 natural left join t1;
-a a
-0 NULL
-1 1
-2 2
-select * from v2 natural left join v1;
-a a
-0 NULL
-1 NULL
-2 2
drop view v1, v2;
drop table t1;
create table t1 (a int);
@@ -378,7 +363,7 @@ explain extended select * from v1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where
Warnings:
-Note 1003 select `test`.`t1`.`b` AS `c` from `test`.`v1` where (`test`.`t1`.`a` < 3)
+Note 1003 select `test`.`t1`.`b` AS `c` from `test`.`t1` where (`test`.`t1`.`a` < 3)
update v1 set c=c+1;
select * from t1;
a b
@@ -1393,7 +1378,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t2 ALL NULL NULL NULL NULL 2
Warnings:
-Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`v1` left join `test`.`v2` on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1
+Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join (`test`.`t2`) on((`test`.`t1`.`a` = `test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1
prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);";
execute stmt1;
a a b
@@ -1694,3 +1679,19 @@ col1 col2 col2 col3
5 david NULL NULL
DROP VIEW v1,v2,v3;
DROP TABLE t1,t2;
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (b int);
+INSERT INTO t1 VALUES (1), (2), (3), (4);
+INSERT INTO t2 VALUES (4), (2);
+CREATE VIEW v1 AS SELECT * FROM t1,t2 WHERE t1.a=t2.b;
+SELECT * FROM v1;
+a b
+2 2
+4 4
+CREATE VIEW v2 AS SELECT * FROM v1;
+SELECT * FROM v2;
+a b
+2 2
+4 4
+DROP VIEW v2,v1;
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 0c3c81d0b89..e85c2751424 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -148,9 +148,10 @@ insert into t1 values (1), (2), (3);
create view v1 (a) as select a+1 from t1;
create view v2 (a) as select a-1 from t1;
-select * from t1 natural left join v1;
-select * from v2 natural left join t1;
-select * from v2 natural left join v1;
+# WL #2486 should enable these tests
+#select * from t1 natural left join v1;
+#select * from v2 natural left join t1;
+#select * from v2 natural left join v1;
drop view v1, v2;
drop table t1;
@@ -1519,3 +1520,20 @@ SELECT a.col1,a.col2,b.col2,b.col3
DROP VIEW v1,v2,v3;
DROP TABLE t1,t2;
+
+#
+# Test case for bug #8528: select from view over multi-table view
+#
+
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (b int);
+INSERT INTO t1 VALUES (1), (2), (3), (4);
+INSERT INTO t2 VALUES (4), (2);
+
+CREATE VIEW v1 AS SELECT * FROM t1,t2 WHERE t1.a=t2.b;
+SELECT * FROM v1;
+CREATE VIEW v2 AS SELECT * FROM v1;
+SELECT * FROM v2;
+
+DROP VIEW v2,v1;
+DROP TABLE t1,t2;
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index d79811aa4e2..7280dc89993 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -3157,10 +3157,8 @@ TABLE_LIST **make_leaves_list(TABLE_LIST **list, TABLE_LIST *tables)
{
for (TABLE_LIST *table= tables; table; table= table->next_local)
{
- if (table->view && !table->table)
+ if (table->view && table->effective_algorithm == VIEW_ALGORITHM_MERGE)
{
- /* it is for multi table views only, check it */
- DBUG_ASSERT(table->ancestor->next_local != 0);
list= make_leaves_list(list, table->ancestor);
}
else
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index d524bbcf164..cded9e2a13e 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -46,8 +46,6 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
DBUG_RETURN(TRUE);
if (!(table= table_list->table))
{
- DBUG_ASSERT(table_list->view &&
- table_list->ancestor && table_list->ancestor->next_local);
my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0),
table_list->view_db.str, table_list->view_name.str);
DBUG_RETURN(-1);
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index a38138ca5fd..2d882b477aa 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -81,8 +81,6 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list,
{
if (!table)
{
- DBUG_ASSERT(table_list->view &&
- table_list->ancestor && table_list->ancestor->next_local);
my_error(ER_VIEW_NO_INSERT_FIELD_LIST, MYF(0),
table_list->view_db.str, table_list->view_name.str);
return -1;
@@ -124,7 +122,7 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list,
thd->lex->select_lex.no_wrap_view_item= 0;
if (res)
return -1;
- if (table == 0)
+ if (table_list->effective_algorithm == VIEW_ALGORITHM_MERGE)
{
/* it is join view => we need to find table for update */
List_iterator_fast<Item> it(fields);
@@ -134,7 +132,7 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list,
while ((item= it++))
map|= item->used_tables();
- if (table_list->check_single_table(&tbl, map) || tbl == 0)
+ if (table_list->check_single_table(&tbl, map, table_list) || tbl == 0)
{
my_error(ER_VIEW_MULTIUPDATE, MYF(0),
table_list->view_db.str, table_list->view_name.str);
@@ -706,8 +704,6 @@ static bool mysql_prepare_insert_check_table(THD *thd, TABLE_LIST *table_list,
thd->lex->empty_field_list_on_rset= 1;
if (!table_list->table)
{
- DBUG_ASSERT(table_list->view &&
- table_list->ancestor && table_list->ancestor->next_local);
my_error(ER_VIEW_NO_INSERT_FIELD_LIST, MYF(0),
table_list->view_db.str, table_list->view_name.str);
DBUG_RETURN(TRUE);
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index c5ef9f4e713..058da7a803c 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1764,7 +1764,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
thd->lex->
select_lex.table_list.link_in_list((byte*) &table_list,
(byte**) &table_list.next_local);
- thd->lex->query_tables= &table_list;
+ thd->lex->add_to_query_tables(&table_list);
/* switch on VIEW optimisation: do not fill temporary tables */
thd->lex->sql_command= SQLCOM_SHOW_FIELDS;
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 56d09d7c563..28df681769b 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1006,7 +1006,7 @@ static int mysql_test_update(Prepared_statement *stmt,
if (!open_tables(thd, &table_list, &table_count))
{
- if (table_list->ancestor && table_list->ancestor->next_local)
+ if (table_list->multitable_view)
{
DBUG_ASSERT(table_list->view != 0);
DBUG_PRINT("info", ("Switch to multi-update"));
@@ -1095,8 +1095,6 @@ static int mysql_test_delete(Prepared_statement *stmt,
bool res;
if (!table_list->table)
{
- DBUG_ASSERT(table_list->view &&
- table_list->ancestor && table_list->ancestor->next_local);
my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0),
table_list->view_db.str, table_list->view_name.str);
DBUG_RETURN(-1);
@@ -1458,8 +1456,6 @@ static int mysql_test_multidelete(Prepared_statement *stmt,
return res;
if (!tables->table)
{
- DBUG_ASSERT(tables->view &&
- tables->ancestor && tables->ancestor->next_local);
my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0),
tables->view_db.str, tables->view_name.str);
return -1;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 9b93a92c6aa..10bb45c58f1 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7351,7 +7351,7 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top)
*/
if (table->on_expr)
{
- Item *expr;
+ Item *expr= table->prep_on_expr ? table->prep_on_expr : table->on_expr;
/*
If an on expression E is attached to the table,
check all null rejected predicates in this expression.
@@ -7361,7 +7361,7 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top)
the corresponding on expression is added to E.
*/
expr= simplify_joins(join, &nested_join->join_list,
- table->on_expr, FALSE);
+ expr, FALSE);
table->prep_on_expr= table->on_expr= expr;
}
nested_join->used_tables= (table_map) 0;
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 86aa0bf9890..95268c41aed 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -139,7 +139,7 @@ int mysql_update(THD *thd,
if (open_tables(thd, &table_list, &table_count))
DBUG_RETURN(1);
- if (table_list->ancestor && table_list->ancestor->next_local)
+ if (table_list->multitable_view)
{
DBUG_ASSERT(table_list->view != 0);
DBUG_PRINT("info", ("Switch to multi-update"));
@@ -706,9 +706,11 @@ bool mysql_multi_update_prepare(THD *thd)
if (!tl->placeholder() && !tl->schema_table && !using_lock_tables)
tl->table->reginfo.lock_type= tl->lock_type;
}
-
+ }
+ for(tl= table_list; tl; tl= tl->next_local)
+ {
/* Check access privileges for table */
- if (!tl->derived && !tl->belong_to_view)
+ if (!tl->derived)
{
uint want_privilege= tl->updating ? UPDATE_ACL : SELECT_ACL;
if (check_access(thd, want_privilege,
@@ -721,12 +723,10 @@ bool mysql_multi_update_prepare(THD *thd)
/* check single table update for view compound from several tables */
for (tl= table_list; tl; tl= tl->next_local)
{
- if (tl->table == 0)
+ if (tl->effective_algorithm == VIEW_ALGORITHM_MERGE)
{
- DBUG_ASSERT(tl->view &&
- tl->ancestor && tl->ancestor->next_local);
TABLE_LIST *for_update= 0;
- if (tl->check_single_table(&for_update, tables_for_update))
+ if (tl->check_single_table(&for_update, tables_for_update, tl))
{
my_error(ER_VIEW_MULTIUPDATE, MYF(0),
tl->view_db.str, tl->view_name.str);
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index be643c36d7d..333a713257f 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -773,9 +773,9 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
tbl->lock_type= table->lock_type;
}
- /* multi table view */
- if (view_tables->next_local)
{
+ if (view_tables->next_local)
+ table->multitable_view= TRUE;
/* make nested join structure for view tables */
NESTED_JOIN *nested_join;
if (!(nested_join= table->nested_join=
diff --git a/sql/table.cc b/sql/table.cc
index 8e0f52e1910..d9a092ea416 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -1644,11 +1644,13 @@ void st_table_list::set_ancestor()
*/
tbl->ancestor->set_ancestor();
}
- tbl->table->grant= grant;
+ if (tbl->multitable_view)
+ multitable_view= TRUE;
+ if (tbl->table)
+ tbl->table->grant= grant;
} while ((tbl= tbl->next_local));
- /* if view contain only one table, substitute TABLE of it */
- if (!ancestor->next_local)
+ if (!multitable_view)
{
table= ancestor->table;
schema_table= ancestor->schema_table;
@@ -1675,8 +1677,6 @@ void st_table_list::save_and_clear_want_privilege()
}
else
{
- DBUG_ASSERT(tbl->view && tbl->ancestor &&
- tbl->ancestor->next_local);
tbl->save_and_clear_want_privilege();
}
}
@@ -1698,8 +1698,6 @@ void st_table_list::restore_want_privilege()
tbl->table->grant.want_privilege= privilege_backup;
else
{
- DBUG_ASSERT(tbl->view && tbl->ancestor &&
- tbl->ancestor->next_local);
tbl->restore_want_privilege();
}
}
@@ -2053,16 +2051,17 @@ int st_table_list::view_check_option(THD *thd, bool ignore_failure)
{
if (check_option && check_option->val_int() == 0)
{
+ TABLE_LIST *view= (belong_to_view ? belong_to_view : this);
if (ignore_failure)
{
push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_ERROR,
ER_VIEW_CHECK_FAILED, ER(ER_VIEW_CHECK_FAILED),
- view_db.str, view_name.str);
+ view->view_db.str, view->view_name.str);
return(VIEW_CHECK_SKIP);
}
else
{
- my_error(ER_VIEW_CHECK_FAILED, MYF(0), view_db.str, view_name.str);
+ my_error(ER_VIEW_CHECK_FAILED, MYF(0), view->view_db.str, view->view_name.str);
return(VIEW_CHECK_ERROR);
}
}
@@ -2080,13 +2079,15 @@ int st_table_list::view_check_option(THD *thd, bool ignore_failure)
(should be 0 on call, to find table, or point to table for
unique test)
map bit mask of tables
+ view view for which we are looking table
RETURN
FALSE table not found or found only one
TRUE found several tables
*/
-bool st_table_list::check_single_table(st_table_list **table, table_map map)
+bool st_table_list::check_single_table(st_table_list **table, table_map map,
+ st_table_list *view)
{
for (TABLE_LIST *tbl= ancestor; tbl; tbl= tbl->next_local)
{
@@ -2097,11 +2098,14 @@ bool st_table_list::check_single_table(st_table_list **table, table_map map)
if (*table)
return TRUE;
else
+ {
*table= tbl;
+ tbl->check_option= view->check_option;
+ }
}
}
else
- if (tbl->check_single_table(table, map))
+ if (tbl->check_single_table(table, map, view))
return TRUE;
}
return FALSE;
@@ -2131,7 +2135,7 @@ bool st_table_list::set_insert_values(MEM_ROOT *mem_root)
}
else
{
- DBUG_ASSERT(view && ancestor && ancestor->next_local);
+ DBUG_ASSERT(view && ancestor);
for (TABLE_LIST *tbl= ancestor; tbl; tbl= tbl->next_local)
if (tbl->set_insert_values(mem_root))
return TRUE;
diff --git a/sql/table.h b/sql/table.h
index d00ac41140e..781b5e21f9b 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -432,6 +432,10 @@ typedef struct st_table_list
bool skip_temporary; /* this table shouldn't be temporary */
/* TRUE if this merged view contain auto_increment field */
bool contain_auto_increment;
+#if 0
+#else
+ bool multitable_view; /* TRUE iff this is multitable view */
+#endif
/* FRMTYPE_ERROR if any type is acceptable */
enum frm_type_enum required_type;
char timestamp_buffer[20]; /* buffer for timestamp (19+1) */
@@ -450,7 +454,8 @@ typedef struct st_table_list
void print(THD *thd, String *str);
void save_and_clear_want_privilege();
void restore_want_privilege();
- bool check_single_table(st_table_list **table, table_map map);
+ bool check_single_table(st_table_list **table, table_map map,
+ st_table_list *view);
bool set_insert_values(MEM_ROOT *mem_root);
st_table_list *find_underlying_table(TABLE *table);
} TABLE_LIST;
View Lorry Controller Status