Cleanup tests and results after merge from 4.1 of embedded

server testing cleanups.


mysql-test/r/innodb.result:
  Update results
mysql-test/r/insert_select-binlog.result:
  Update results
mysql-test/r/mix_innodb_myisam_binlog.result:
  Update results
mysql-test/r/ps_1general.result:
  Update results
mysql-test/r/ps_grant.result:
  Update results
mysql-test/r/view.result:
  Update results
mysql-test/t/client_xml.test:
  Skip test with embedded server
mysql-test/t/flush_read_lock_kill.test:
  Skip test with embedded server
mysql-test/t/grant3.test:
  Skip test with embedded server
mysql-test/t/information_schema.test:
  Skip test with embedded server
mysql-test/t/innodb.test:
  Fix up filenames
mysql-test/t/multi_update.test:
  Skip test with embedded server
mysql-test/t/mysqlshow.test:
  Skip test with embedded server
mysql-test/t/ps_1general.test:
  Move grant-related tests to ps_grant, and fix cleanup of filename
mysql-test/t/ps_grant.test:
  Add additional tests
mysql-test/t/sp-security.test:
  Skip test with embedded server
mysql-test/t/user_limits.test:
  Skip test with embedded server
mysql-test/t/view.test:
  Split grant-related tests to new test file view_grant

13 files changed, 445 insertions, 417 deletions

diff --git a/mysql-test/t/client_xml.test b/mysql-test/t/client_xml.test
index dd7a812b54f..58e9178ef3a 100644
--- a/mysql-test/t/client_xml.test
+++ b/mysql-test/t/client_xml.test
@@ -1,3 +1,6 @@
+# Can't run with embedded server
+-- source include/not_embedded.inc
+
 # Test of the xml output of the 'mysql' and 'mysqldump' clients -- makes
 # sure that basic encoding issues are handled properly
 create table t1 (
diff --git a/mysql-test/t/flush_read_lock_kill.test b/mysql-test/t/flush_read_lock_kill.test
index b711bc63e0e..02384357711 100644
--- a/mysql-test/t/flush_read_lock_kill.test
+++ b/mysql-test/t/flush_read_lock_kill.test
@@ -7,6 +7,9 @@
 # -master.opt. But this test is designed to still pass then (though it
 # won't test anything interesting).
 
+# This also won't work with the embedded server test
+-- source include/not_embedded.inc
+
 -- source include/have_debug.inc
 
 connect (con1,localhost,root,,);
diff --git a/mysql-test/t/grant3.test b/mysql-test/t/grant3.test
index 1488f910bc8..115586e807d 100644
--- a/mysql-test/t/grant3.test
+++ b/mysql-test/t/grant3.test
@@ -1,3 +1,6 @@
+# Can't run with embedded server
+-- source include/not_embedded.inc
+
 # Test of GRANT commands
 
 SET NAMES binary;
diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
index 1a9849869d9..e515cd8c77a 100644
--- a/mysql-test/t/information_schema.test
+++ b/mysql-test/t/information_schema.test
@@ -1,3 +1,5 @@
+# This test uses grants, which can't get tested for embedded server
+-- source include/not_embedded.inc
 
 # Test for information_schema.schemata &
 # show databases
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index c3dc251ce3a..cca3e7ed3ad 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -1125,7 +1125,10 @@ show create table t2;
 drop table t2;
 
 # Test error handling
---replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ /
+
+# Clean up filename -- embedded server reports whole path without .frm,
+# regular server reports relative path with .frm (argh!)
+--replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / t2.frm t2
 --error 1005
 create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb;
 
@@ -1280,6 +1283,9 @@ source include/varchar.inc;
 # Some errors/warnings on create
 #
 
+# Clean up filename -- embedded server reports whole path without .frm,
+# regular server reports relative path with .frm (argh!)
+--replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / t1.frm t1
 --error 1005
 create table t1 (v varchar(65530), key(v));
 create table t1 (v varchar(65536));
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index f3b6216e3cf..0ca42e86204 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -2,6 +2,9 @@
 # Test of update statement that uses many tables.
 #
 
+# Requires grants, so won't work with embedded server test
+-- source include/not_embedded.inc
+
 --disable_warnings
 drop table if exists t1,t2,t3;
 drop database if exists mysqltest;
diff --git a/mysql-test/t/mysqlshow.test b/mysql-test/t/mysqlshow.test
index 8da29b91819..33ae8aef9a0 100644
--- a/mysql-test/t/mysqlshow.test
+++ b/mysql-test/t/mysqlshow.test
@@ -1,3 +1,6 @@
+# Can't run test of external client with embedded server
+-- source include/not_embedded.inc
+
 #
 ## Bug #5036 mysqlshow is missing a column
 #
diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test
index 38f03fe4cc2..1fa9d30eaba 100644
--- a/mysql-test/t/ps_1general.test
+++ b/mysql-test/t/ps_1general.test
@@ -436,20 +436,6 @@ create database mysqltest ;
 prepare stmt3 from ' drop database mysqltest ';
 drop database mysqltest ;
 
-## grant/revoke + drop user
---error 1295
-prepare stmt3 from ' grant all on test.t1 to drop_user@localhost
-identified by ''looser'' ';
-grant all on test.t1 to drop_user@localhost
-identified by 'looser' ;
---error 1295
-prepare stmt3 from ' revoke all privileges on test.t1 from 
-drop_user@localhost ';
-revoke all privileges on test.t1 from drop_user@localhost ;
---error 1295
-prepare stmt3 from ' drop user drop_user@localhost ';
-drop user drop_user@localhost;
-
 #### table related commands
 ## describe
 prepare stmt3 from ' describe t2 ';
@@ -606,7 +592,7 @@ prepare stmt1 from ' rename table t5 to t6, t7 to t8 ' ;
 create table t5 (a int) ;
 # rename must fail, t7 does not exist
 # Clean up the filename here because embedded server reports whole path
---replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ /
+--replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / t7.frm t7
 --error 1017
 execute stmt1 ;
 create table t7 (a int) ;
diff --git a/mysql-test/t/ps_grant.test b/mysql-test/t/ps_grant.test
index 06613072824..07bd70f6cff 100644
--- a/mysql-test/t/ps_grant.test
+++ b/mysql-test/t/ps_grant.test
@@ -116,4 +116,17 @@ show grants for second_user@localhost ;
 
 drop database mysqltest;
 
+## grant/revoke + drop user
+--error 1295
+prepare stmt3 from ' grant all on test.t1 to drop_user@localhost
+identified by ''looser'' ';
+grant all on test.t1 to drop_user@localhost
+identified by 'looser' ;
+--error 1295
+prepare stmt3 from ' revoke all privileges on test.t1 from 
+drop_user@localhost ';
+revoke all privileges on test.t1 from drop_user@localhost ;
+--error 1295
+prepare stmt3 from ' drop user drop_user@localhost ';
+drop user drop_user@localhost;
 
diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test
index aad5f4eaf9e..5a8dfc54920 100644
--- a/mysql-test/t/sp-security.test
+++ b/mysql-test/t/sp-security.test
@@ -2,6 +2,9 @@
 # Testing SQL SECURITY of stored procedures
 #
 
+# Can't test with embedded server that doesn't support grants
+-- source include/not_embedded.inc
+
 connect (con1root,localhost,root,,);
 
 connection con1root;
diff --git a/mysql-test/t/user_limits.test b/mysql-test/t/user_limits.test
index 729894a588a..af0f6545ac4 100644
--- a/mysql-test/t/user_limits.test
+++ b/mysql-test/t/user_limits.test
@@ -2,6 +2,9 @@
 # Test behavior of various per-account limits (aka quotas)
 #
 
+# Requires privileges to be enabled
+-- source include/not_embedded.inc
+
 # Prepare play-ground 
 --disable_warnings
 drop table if exists t1;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 527dcda0f1b..8205680627d 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -122,12 +122,6 @@ alter view v1 (c,d) as select a,max(b) from t1 group by a;
 select * from v1;
 select * from v2;
 
-# simple test of grants
-grant create view on test.* to test@localhost;
-show grants for test@localhost;
-revoke create view on test.* from test@localhost;
-show grants for test@localhost;
-
 # try to drop nonexistent VIEW
 -- error 1051
 drop view v100;
@@ -163,182 +157,6 @@ drop table t1;
 
 
 #
-# grant create view test
-#
-connect (root,localhost,root,,test);
-connection root;
---disable_warnings
-create database mysqltest;
---enable_warnings
-
-create table mysqltest.t1 (a int, b int);
-create table mysqltest.t2 (a int, b int);
-
-grant select on mysqltest.t1 to mysqltest_1@localhost;
-grant create view,select on test.* to mysqltest_1@localhost;
-
-connect (user1,localhost,mysqltest_1,,test);
-connection user1;
-
-create view v1 as select * from mysqltest.t1;
-# try to modify view without DROP privilege on it
--- error 1142
-alter view v1 as select * from mysqltest.t1;
--- error 1142
-create or replace view v1 as select * from mysqltest.t1;
-# no CRETE VIEW privilege
--- error 1142
-create view mysqltest.v2  as select * from mysqltest.t1;
-# no SELECT privilege
--- error 1142
-create view v2 as select * from mysqltest.t2;
-
-connection root;
-grant create view,drop,select on test.* to mysqltest_1@localhost;
-
-connection user1;
-# following 'use' command is workaround of bug #9582 and should be removed
-# when that bug will be fixed
-use test;
-alter view v1 as select * from mysqltest.t1;
-create or replace view v1 as select * from mysqltest.t1;
-
-connection root;
-revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
-revoke all privileges on test.* from mysqltest_1@localhost;
-
-drop database mysqltest;
-drop view test.v1;
-
-#
-# grants per columns
-#
-# MERGE algorithm
---disable_warnings
-create database mysqltest;
---enable_warnings
-
-create table mysqltest.t1 (a int, b int);
-create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
-grant select (c) on mysqltest.v1 to mysqltest_1@localhost;
-
-connection user1;
-select c from mysqltest.v1;
-# there are no privileges on column 'd'
--- error 1143
-select d from mysqltest.v1;
-
-connection root;
-revoke all privileges on mysqltest.v1 from mysqltest_1@localhost;
-delete from mysql.user where user='mysqltest_1';
-drop database mysqltest;
-
-# TEMPORARY TABLE algorithm
---disable_warnings
-create database mysqltest;
---enable_warnings
-
-create table mysqltest.t1 (a int, b int);
-create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
-grant select (c) on mysqltest.v1 to mysqltest_1@localhost;
-
-connection user1;
-select c from mysqltest.v1;
-# there are no privileges on column 'd'
--- error 1143
-select d from mysqltest.v1;
-
-connection root;
-revoke all privileges on mysqltest.v1 from mysqltest_1@localhost;
-delete from mysql.user where user='mysqltest_1';
-drop database mysqltest;
-
-#
-# EXPLAIN rights
-#
-connection root;
---disable_warnings
-create database mysqltest;
---enable_warnings
-#prepare views and tables
-create table mysqltest.t1 (a int, b int);
-create table mysqltest.t2 (a int, b int);
-create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
-create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1;
-create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2;
-create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2;
-grant select on mysqltest.v1 to mysqltest_1@localhost;
-grant select on mysqltest.v2 to mysqltest_1@localhost;
-grant select on mysqltest.v3 to mysqltest_1@localhost;
-grant select on mysqltest.v4 to mysqltest_1@localhost;
-
-connection user1;
-# all selects works
-select c from mysqltest.v1;
-select c from mysqltest.v2;
-select c from mysqltest.v3;
-select c from mysqltest.v4;
-# test of show coluns
-show columns from mysqltest.v1;
-show columns from mysqltest.v2;
-# but explain/show do not
--- error 1345
-explain select c from mysqltest.v1;
--- error 1142
-show create view mysqltest.v1;
--- error 1345
-explain select c from mysqltest.v2;
--- error 1142
-show create view mysqltest.v2;
--- error 1345
-explain select c from mysqltest.v3;
--- error 1142
-show create view mysqltest.v3;
--- error 1345
-explain select c from mysqltest.v4;
--- error 1142
-show create view mysqltest.v4;
-
-# allow to see one of underlying table
-connection root;
-grant select on mysqltest.t1 to mysqltest_1@localhost;
-connection user1;
-# EXPLAIN of view on above table works
-explain select c from mysqltest.v1;
--- error 1142
-show create view mysqltest.v1;
-explain select c from mysqltest.v2;
--- error 1142
-show create view mysqltest.v2;
-# but other EXPLAINs do not
--- error 1345
-explain select c from mysqltest.v3;
--- error 1142
-show create view mysqltest.v3;
--- error 1345
-explain select c from mysqltest.v4;
--- error 1142
-show create view mysqltest.v4;
-
-# allow to see any view in mysqltest database
-connection root;
-grant show view on mysqltest.* to mysqltest_1@localhost;
-connection user1;
-explain select c from mysqltest.v1;
-show create view mysqltest.v1;
-explain select c from mysqltest.v2;
-show create view mysqltest.v2;
-explain select c from mysqltest.v3;
-show create view mysqltest.v3;
-explain select c from mysqltest.v4;
-show create view mysqltest.v4;
-
-connection root;
-revoke all privileges on mysqltest.* from mysqltest_1@localhost;
-delete from mysql.user where user='mysqltest_1';
-drop database mysqltest;
-
-#
 # DISTINCT option for VIEW
 #
 create table t1 (a int);
@@ -444,54 +262,6 @@ drop table t1,t2;
 drop view v1,v2;
 
 #
-# UPDATE privileges on VIEW columns and whole VIEW
-#
-connection root;
---disable_warnings
-create database mysqltest;
---enable_warnings
-
-create table mysqltest.t1 (a int, b int, primary key(a));
-insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10);
-create table mysqltest.t2 (x int);
-insert into mysqltest.t2 values (3), (4), (5), (6);
-create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1;
-create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1;
-create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1;
-
-grant update (a) on mysqltest.v2 to mysqltest_1@localhost;
-grant update on mysqltest.v1 to mysqltest_1@localhost;
-grant select on mysqltest.* to mysqltest_1@localhost;
-
-connection user1;
-use mysqltest;
-# update with rights on VIEW column
-update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c;
-select * from t1;
-update v1 set a=a+c;
-select * from t1;
-# update with rights on whole VIEW
-update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c;
-select * from t1;
-update v2 set a=a+c;
-select * from t1;
-# no rights on column
--- error 1143
-update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c;
--- error 1143
-update v2 set c=a+c;
-# no rights for view
--- error 1142
-update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c;
--- error 1142
-update v3 set a=a+c;
-
-use test;
-connection root;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
-drop database mysqltest;
-
-#
 # MERGE VIEW with WHERE clause
 #
 create table t1 (a int, b int, primary key(b));
@@ -546,42 +316,6 @@ drop table t1,t2;
 drop view v1,v2;
 
 #
-# DELETE privileges on VIEW
-#
-connection root;
---disable_warnings
-create database mysqltest;
---enable_warnings
-
-create table mysqltest.t1 (a int, b int, primary key(a));
-insert into mysqltest.t1 values (1,2), (2,3), (3,4), (4,5), (5,10);
-create table mysqltest.t2 (x int);
-insert into mysqltest.t2 values (3), (4), (5), (6);
-create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1;
-create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1;
-
-grant delete on mysqltest.v1 to mysqltest_1@localhost;
-grant select on mysqltest.* to mysqltest_1@localhost;
-
-connection user1;
-use mysqltest;
-# update with rights on VIEW column
-delete from v1 where c < 4;
-select * from t1;
-delete v1 from t2,v1 where t2.x=v1.c;
-select * from t1;
-# no rights for view
--- error 1142
-delete v2 from t2,v2 where t2.x=v2.c;
--- error 1142
-delete from v2 where c < 4;
-
-use test;
-connection root;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
-drop database mysqltest;
-
-#
 # key presence check
 #
 create table t1 (a int, b int, c int, primary key(a,b));
@@ -658,42 +392,6 @@ drop table t1, t2;
 drop view v1,v2,v3,v4,v5;
 
 #
-# insert privileges on VIEW
-#
-connection root;
---disable_warnings
-create database mysqltest;
---enable_warnings
-
-create table mysqltest.t1 (a int, b int, primary key(a));
-insert into mysqltest.t1 values (1,2), (2,3);
-create table mysqltest.t2 (x int, y int);
-insert into mysqltest.t2 values (3,4);
-create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1;
-create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1;
-
-grant insert on mysqltest.v1 to mysqltest_1@localhost;
-grant select on mysqltest.* to mysqltest_1@localhost;
-
-connection user1;
-use mysqltest;
-# update with rights on VIEW column
-insert into v1 values (5,6);
-select * from t1;
-insert into v1 select x,y from t2;
-select * from t1;
-# no rights for view
--- error 1142
-insert into v2 values (5,6);
--- error 1142
-insert into v2 select x,y from t2;
-
-use test;
-connection root;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
-drop database mysqltest;
-
-#
 # outer join based on VIEW with WHERE clause
 #
 create table t1 (a int, primary key(a));
@@ -740,87 +438,6 @@ drop view v1;
 drop table t1;
 
 #
-# test of CREATE VIEW privileges if we have limited privileges
-#
-connection root;
---disable_warnings
-create database mysqltest;
---enable_warnings
-
-create table mysqltest.t1 (a int, b int);
-create table mysqltest.t2 (a int, b int);
-
-grant update on mysqltest.t1 to mysqltest_1@localhost;
-grant update(b) on mysqltest.t2 to mysqltest_1@localhost;
-grant create view,update on test.* to mysqltest_1@localhost;
-
-connection user1;
-
-create view v1 as select * from mysqltest.t1;
-create view v2 as select b from mysqltest.t2;
-# There are not rights on mysqltest.v1
--- error 1142
-create view mysqltest.v1 as select * from mysqltest.t1;
-# There are not any rights on mysqltest.t2.a
--- error 1143
-create view v3 as select a from mysqltest.t2;
-
-# give CREATE VIEW privileges (without any privileges for result column)
-connection root;
-create table mysqltest.v3 (b int);
-grant create view on mysqltest.v3 to mysqltest_1@localhost;
-drop table mysqltest.v3;
-connection user1;
-create view mysqltest.v3 as select b from mysqltest.t2;
-
-# give UPDATE privileges
-connection root;
-grant create view, update on mysqltest.v3 to mysqltest_1@localhost;
-drop view mysqltest.v3;
-connection user1;
-create view mysqltest.v3 as select b from mysqltest.t2;
-
-# give UPDATE and INSERT privilege (to get more privileges then underlying
-# table)
-connection root;
-grant create view, update, insert on mysqltest.v3 to mysqltest_1@localhost;
-drop view mysqltest.v3;
-connection user1;
--- error 1143
-create view mysqltest.v3 as select b from mysqltest.t2;
-
-
-# If we would get more privileges on VIEW then we have on
-# underlying tables => creation prohibited
-connection root;
-create table mysqltest.v3 (b int);
-grant select(b) on mysqltest.v3 to mysqltest_1@localhost;
-drop table mysqltest.v3;
-connection user1;
--- error 1142
-create view mysqltest.v3 as select b from mysqltest.t2;
-
-# Expression need select privileges
--- error 1143
-create view v4 as select b+1 from mysqltest.t2;
-
-connection root;
-grant create view,update,select on test.* to mysqltest_1@localhost;
-connection user1;
--- error 1143
-create view v4 as select b+1 from mysqltest.t2;
-
-connection root;
-grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost;
-connection user1;
-create view v4 as select b+1 from mysqltest.t2;
-
-connection root;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
-drop database mysqltest;
-drop view v1,v2,v4;
-
-#
 # VIEW fields quoting
 #
 set sql_mode='ansi';
@@ -1613,24 +1230,6 @@ drop view v1;
 drop table t1;
 
 #
-# user with global DB privileges
-#
-connection root;
---disable_warnings
-create database mysqltest;
---enable_warnings
-create table mysqltest.t1 (a int);
-grant all privileges on mysqltest.* to mysqltest_1@localhost;
-
-connection user1;
-use mysqltest;
-create view v1 as select * from t1;
-
-connection root;
-revoke all privileges on mysqltest.* from mysqltest_1@localhost;
-drop database mysqltest;
-
-#
 # Trys update table from which we select using views and subqueries
 #
 create table t1 (s1 smallint);
diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test
new file mode 100644
index 00000000000..2fe7a420e66
--- /dev/null
+++ b/mysql-test/t/view_grant.test
@@ -0,0 +1,401 @@
+
+# simple test of grants
+grant create view on test.* to test@localhost;
+show grants for test@localhost;
+revoke create view on test.* from test@localhost;
+show grants for test@localhost;
+
+# grant create view test
+#
+connect (root,localhost,root,,test);
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create table mysqltest.t1 (a int, b int);
+create table mysqltest.t2 (a int, b int);
+
+grant select on mysqltest.t1 to mysqltest_1@localhost;
+grant create view,select on test.* to mysqltest_1@localhost;
+
+connect (user1,localhost,mysqltest_1,,test);
+connection user1;
+
+create view v1 as select * from mysqltest.t1;
+# try to modify view without DROP privilege on it
+-- error 1142
+alter view v1 as select * from mysqltest.t1;
+-- error 1142
+create or replace view v1 as select * from mysqltest.t1;
+# no CRETE VIEW privilege
+-- error 1142
+create view mysqltest.v2  as select * from mysqltest.t1;
+# no SELECT privilege
+-- error 1142
+create view v2 as select * from mysqltest.t2;
+
+connection root;
+grant create view,drop,select on test.* to mysqltest_1@localhost;
+
+connection user1;
+# following 'use' command is workaround of bug #9582 and should be removed
+# when that bug will be fixed
+use test;
+alter view v1 as select * from mysqltest.t1;
+create or replace view v1 as select * from mysqltest.t1;
+
+connection root;
+revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
+revoke all privileges on test.* from mysqltest_1@localhost;
+
+drop database mysqltest;
+drop view test.v1;
+
+#
+# grants per columns
+#
+# MERGE algorithm
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create table mysqltest.t1 (a int, b int);
+create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
+grant select (c) on mysqltest.v1 to mysqltest_1@localhost;
+
+connection user1;
+select c from mysqltest.v1;
+# there are no privileges on column 'd'
+-- error 1143
+select d from mysqltest.v1;
+
+connection root;
+revoke all privileges on mysqltest.v1 from mysqltest_1@localhost;
+delete from mysql.user where user='mysqltest_1';
+drop database mysqltest;
+
+# TEMPORARY TABLE algorithm
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create table mysqltest.t1 (a int, b int);
+create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
+grant select (c) on mysqltest.v1 to mysqltest_1@localhost;
+
+connection user1;
+select c from mysqltest.v1;
+# there are no privileges on column 'd'
+-- error 1143
+select d from mysqltest.v1;
+
+connection root;
+revoke all privileges on mysqltest.v1 from mysqltest_1@localhost;
+delete from mysql.user where user='mysqltest_1';
+drop database mysqltest;
+
+#
+# EXPLAIN rights
+#
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+#prepare views and tables
+create table mysqltest.t1 (a int, b int);
+create table mysqltest.t2 (a int, b int);
+create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
+create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1;
+create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2;
+create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2;
+grant select on mysqltest.v1 to mysqltest_1@localhost;
+grant select on mysqltest.v2 to mysqltest_1@localhost;
+grant select on mysqltest.v3 to mysqltest_1@localhost;
+grant select on mysqltest.v4 to mysqltest_1@localhost;
+
+connection user1;
+# all selects works
+select c from mysqltest.v1;
+select c from mysqltest.v2;
+select c from mysqltest.v3;
+select c from mysqltest.v4;
+# test of show coluns
+show columns from mysqltest.v1;
+show columns from mysqltest.v2;
+# but explain/show do not
+-- error 1345
+explain select c from mysqltest.v1;
+-- error 1142
+show create view mysqltest.v1;
+-- error 1345
+explain select c from mysqltest.v2;
+-- error 1142
+show create view mysqltest.v2;
+-- error 1345
+explain select c from mysqltest.v3;
+-- error 1142
+show create view mysqltest.v3;
+-- error 1345
+explain select c from mysqltest.v4;
+-- error 1142
+show create view mysqltest.v4;
+
+# allow to see one of underlying table
+connection root;
+grant select on mysqltest.t1 to mysqltest_1@localhost;
+connection user1;
+# EXPLAIN of view on above table works
+explain select c from mysqltest.v1;
+-- error 1142
+show create view mysqltest.v1;
+explain select c from mysqltest.v2;
+-- error 1142
+show create view mysqltest.v2;
+# but other EXPLAINs do not
+-- error 1345
+explain select c from mysqltest.v3;
+-- error 1142
+show create view mysqltest.v3;
+-- error 1345
+explain select c from mysqltest.v4;
+-- error 1142
+show create view mysqltest.v4;
+
+# allow to see any view in mysqltest database
+connection root;
+grant show view on mysqltest.* to mysqltest_1@localhost;
+connection user1;
+explain select c from mysqltest.v1;
+show create view mysqltest.v1;
+explain select c from mysqltest.v2;
+show create view mysqltest.v2;
+explain select c from mysqltest.v3;
+show create view mysqltest.v3;
+explain select c from mysqltest.v4;
+show create view mysqltest.v4;
+
+connection root;
+revoke all privileges on mysqltest.* from mysqltest_1@localhost;
+delete from mysql.user where user='mysqltest_1';
+drop database mysqltest;
+
+#
+# UPDATE privileges on VIEW columns and whole VIEW
+#
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create table mysqltest.t1 (a int, b int, primary key(a));
+insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10);
+create table mysqltest.t2 (x int);
+insert into mysqltest.t2 values (3), (4), (5), (6);
+create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1;
+create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1;
+create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1;
+
+grant update (a) on mysqltest.v2 to mysqltest_1@localhost;
+grant update on mysqltest.v1 to mysqltest_1@localhost;
+grant select on mysqltest.* to mysqltest_1@localhost;
+
+connection user1;
+use mysqltest;
+# update with rights on VIEW column
+update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c;
+select * from t1;
+update v1 set a=a+c;
+select * from t1;
+# update with rights on whole VIEW
+update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c;
+select * from t1;
+update v2 set a=a+c;
+select * from t1;
+# no rights on column
+-- error 1143
+update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c;
+-- error 1143
+update v2 set c=a+c;
+# no rights for view
+-- error 1142
+update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c;
+-- error 1142
+update v3 set a=a+c;
+
+use test;
+connection root;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;
+
+#
+# DELETE privileges on VIEW
+#
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create table mysqltest.t1 (a int, b int, primary key(a));
+insert into mysqltest.t1 values (1,2), (2,3), (3,4), (4,5), (5,10);
+create table mysqltest.t2 (x int);
+insert into mysqltest.t2 values (3), (4), (5), (6);
+create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1;
+create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1;
+
+grant delete on mysqltest.v1 to mysqltest_1@localhost;
+grant select on mysqltest.* to mysqltest_1@localhost;
+
+connection user1;
+use mysqltest;
+# update with rights on VIEW column
+delete from v1 where c < 4;
+select * from t1;
+delete v1 from t2,v1 where t2.x=v1.c;
+select * from t1;
+# no rights for view
+-- error 1142
+delete v2 from t2,v2 where t2.x=v2.c;
+-- error 1142
+delete from v2 where c < 4;
+
+use test;
+connection root;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;
+
+#
+# insert privileges on VIEW
+#
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create table mysqltest.t1 (a int, b int, primary key(a));
+insert into mysqltest.t1 values (1,2), (2,3);
+create table mysqltest.t2 (x int, y int);
+insert into mysqltest.t2 values (3,4);
+create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1;
+create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1;
+
+grant insert on mysqltest.v1 to mysqltest_1@localhost;
+grant select on mysqltest.* to mysqltest_1@localhost;
+
+connection user1;
+use mysqltest;
+# update with rights on VIEW column
+insert into v1 values (5,6);
+select * from t1;
+insert into v1 select x,y from t2;
+select * from t1;
+# no rights for view
+-- error 1142
+insert into v2 values (5,6);
+-- error 1142
+insert into v2 select x,y from t2;
+
+use test;
+connection root;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;
+
+#
+# test of CREATE VIEW privileges if we have limited privileges
+#
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create table mysqltest.t1 (a int, b int);
+create table mysqltest.t2 (a int, b int);
+
+grant update on mysqltest.t1 to mysqltest_1@localhost;
+grant update(b) on mysqltest.t2 to mysqltest_1@localhost;
+grant create view,update on test.* to mysqltest_1@localhost;
+
+connection user1;
+
+create view v1 as select * from mysqltest.t1;
+create view v2 as select b from mysqltest.t2;
+# There are not rights on mysqltest.v1
+-- error 1142
+create view mysqltest.v1 as select * from mysqltest.t1;
+# There are not any rights on mysqltest.t2.a
+-- error 1143
+create view v3 as select a from mysqltest.t2;
+
+# give CREATE VIEW privileges (without any privileges for result column)
+connection root;
+create table mysqltest.v3 (b int);
+grant create view on mysqltest.v3 to mysqltest_1@localhost;
+drop table mysqltest.v3;
+connection user1;
+create view mysqltest.v3 as select b from mysqltest.t2;
+
+# give UPDATE privileges
+connection root;
+grant create view, update on mysqltest.v3 to mysqltest_1@localhost;
+drop view mysqltest.v3;
+connection user1;
+create view mysqltest.v3 as select b from mysqltest.t2;
+
+# give UPDATE and INSERT privilege (to get more privileges then underlying
+# table)
+connection root;
+grant create view, update, insert on mysqltest.v3 to mysqltest_1@localhost;
+drop view mysqltest.v3;
+connection user1;
+-- error 1143
+create view mysqltest.v3 as select b from mysqltest.t2;
+
+
+# If we would get more privileges on VIEW then we have on
+# underlying tables => creation prohibited
+connection root;
+create table mysqltest.v3 (b int);
+grant select(b) on mysqltest.v3 to mysqltest_1@localhost;
+drop table mysqltest.v3;
+connection user1;
+-- error 1142
+create view mysqltest.v3 as select b from mysqltest.t2;
+
+# Expression need select privileges
+-- error 1143
+create view v4 as select b+1 from mysqltest.t2;
+
+connection root;
+grant create view,update,select on test.* to mysqltest_1@localhost;
+connection user1;
+-- error 1143
+create view v4 as select b+1 from mysqltest.t2;
+
+connection root;
+grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost;
+connection user1;
+create view v4 as select b+1 from mysqltest.t2;
+
+connection root;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;
+drop view v1,v2,v4;
+
+#
+# user with global DB privileges
+#
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+create table mysqltest.t1 (a int);
+grant all privileges on mysqltest.* to mysqltest_1@localhost;
+
+connection user1;
+use mysqltest;
+create view v1 as select * from t1;
+
+connection root;
+revoke all privileges on mysqltest.* from mysqltest_1@localhost;
+drop database mysqltest;
+