commit into mysql-5.1 branch (horst:2658) WL#4029

[Horst Hunger]

#At file:///work/bzr/mysql-5.1-wl4029/

 2658 Horst Hunger	2008-06-02
      wl4029: 1 set of subquery test.
added:
  mysql-test/suite/funcs_1/r/csv_subquery2.result
  mysql-test/suite/funcs_1/r/csv_subquery3.result
  mysql-test/suite/funcs_1/r/csv_subquery4.result
  mysql-test/suite/funcs_1/r/innodb_subquery2.result
  mysql-test/suite/funcs_1/r/innodb_subquery3.result
  mysql-test/suite/funcs_1/r/innodb_subquery4.result
  mysql-test/suite/funcs_1/r/memory_subquery2.result
  mysql-test/suite/funcs_1/r/memory_subquery3.result
  mysql-test/suite/funcs_1/r/memory_subquery4.result
  mysql-test/suite/funcs_1/r/myisam_subquery2.result
  mysql-test/suite/funcs_1/r/myisam_subquery3.result
  mysql-test/suite/funcs_1/r/myisam_subquery4.result
  mysql-test/suite/funcs_1/r/ndb_subquery2.result
  mysql-test/suite/funcs_1/r/ndb_subquery3.result
  mysql-test/suite/funcs_1/r/ndb_subquery4.result
  mysql-test/suite/funcs_1/subquery/
  mysql-test/suite/funcs_1/subquery/subquery1.inc
  mysql-test/suite/funcs_1/subquery/subquery2.inc
  mysql-test/suite/funcs_1/subquery/subquery3.inc
  mysql-test/suite/funcs_1/subquery/subquery4.inc
  mysql-test/suite/funcs_1/t/csv_subquery2.test
  mysql-test/suite/funcs_1/t/csv_subquery3.test
  mysql-test/suite/funcs_1/t/csv_subquery4.test
  mysql-test/suite/funcs_1/t/innodb_subquery2.test
  mysql-test/suite/funcs_1/t/innodb_subquery3.test
  mysql-test/suite/funcs_1/t/innodb_subquery4.test
  mysql-test/suite/funcs_1/t/memory_subquery2.test
  mysql-test/suite/funcs_1/t/memory_subquery3.test
  mysql-test/suite/funcs_1/t/memory_subquery4.test
  mysql-test/suite/funcs_1/t/myisam_subquery2.test
  mysql-test/suite/funcs_1/t/myisam_subquery3.test
  mysql-test/suite/funcs_1/t/myisam_subquery4.test
  mysql-test/suite/funcs_1/t/ndb_subquery2.test
  mysql-test/suite/funcs_1/t/ndb_subquery3.test
  mysql-test/suite/funcs_1/t/ndb_subquery4.test
modified:
  .bzrignore


=== modified file '.bzrignore'
--- a/.bzrignore	2008-05-16 16:03:50 +0000
+++ b/.bzrignore	2008-06-02 15:01:21 +0000
@@ -3029,3 +3029,4 @@
 mysql-test/t.log
 mysql-test/tps.log
 libmysqld/event_parse_data.cc
+subquery/SCCS

=== added file 'mysql-test/suite/funcs_1/r/csv_subquery2.result'
=== added file 'mysql-test/suite/funcs_1/r/csv_subquery3.result'
=== added file 'mysql-test/suite/funcs_1/r/csv_subquery4.result'
=== added file 'mysql-test/suite/funcs_1/r/innodb_subquery2.result'
--- a/mysql-test/suite/funcs_1/r/innodb_subquery2.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/r/innodb_subquery2.result	2008-06-02 15:01:21 +0000
@@ -0,0 +1,329 @@
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+CREATE DATABASE test1;
+USE test1;
+CREATE TABLE tb1 (f11 int , f12 char(10), f13 int, f14 char(20)) ENGINE InnoDB;
+CREATE TABLE tb2 (f21 int , f22 char(10), f23 int, f24 char(20)) ENGINE InnoDB;
+INSERT INTO tb1 VALUES ( 1,'aaaaaaaaaa', 0,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES ( 2,'bbbbbbbbbb', 2,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES ( 3,'cccccccccc', 4,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES ( 4,'dddddddddd', 6,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES ( 5,'eeeeeeeeee', 8,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES ( 6,'ffffffffff', 0,'pppppppppppppppppppp');
+INSERT INTO tb1 VALUES ( 7,'gggggggggg', 2,'qqqqqqqqqqqqqqqqqqqq');
+INSERT INTO tb1 VALUES ( 8,'hhhhhhhhhh', 4,'rrrrrrrrrrrrrrrrrrrr');
+INSERT INTO tb1 VALUES ( 9,'iiiiiiiiii', 6,'ssssssssssssssssssss');
+INSERT INTO tb1 VALUES (10,'jjjjjjjjjj', 8,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES (11,'aaaaaaaaaa', 0,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES (12,'bbbbbbbbbb', 2,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES (13,'cccccccccc', 4,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES (14,'dddddddddd', 6,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES (15,'eeeeeeeeee', 8,'pppppppppppppppppppp');
+INSERT INTO tb2 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb2 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb2 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb2 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb2 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+CREATE DATABASE test2;
+USE test2;
+CREATE TABLE tb3 (f31 int , f32 char(10), f33 int, f34 char(20)) ENGINE InnoDB;
+CREATE TABLE tb4 (f41 int , f42 char(10), f43 int, f44 char(20)) ENGINE InnoDB;
+INSERT INTO tb3 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb3 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb3 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb3 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb3 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb4 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb4 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb4 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb4 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb4 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+CREATE DATABASE test3;
+USE test3;
+CREATE TABLE tb5 (f51 int , f52 char(10), f53 int, f54 char(20)) ENGINE InnoDB;
+CREATE TABLE tb6 (f61 int , f62 char(10), f63 int, f64 char(20)) ENGINE InnoDB;
+INSERT INTO tb5 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb5 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb5 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb5 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb5 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb6 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb6 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb6 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb6 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb6 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+USE information_schema;
+####################################################################################
+1.1.2 Create 3 user
+####################################################################################
+DROP USER testuser1@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser1'@'localhost'
+DROP USER testuser2@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser2'@'localhost'
+DROP USER testuser3@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser3'@'localhost'
+CREATE USER testuser1@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+CREATE USER testuser3@'localhost';
+SET PASSWORD FOR testuser3@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser3@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser3@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser3@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+all subqueries must succeed
+Establish connection con100 (user=testuser1)
+####################################################################################
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+SELECT test1.tb1.f11,test2.tb3.f32 FROM test1.tb1,test2.tb3 WHERE f11 IN (SELECT f31 FROM
test2.tb3 WHERE f31=test1.tb1.f13);
+f11	f32
+2	aaaaaaaaaa
+2	aaaaaaaaaa
+2	bbbbbbbbbb
+2	bbbbbbbbbb
+2	cccccccccc
+2	cccccccccc
+2	dddddddddd
+2	dddddddddd
+2	eeeeeeeeee
+2	eeeeeeeeee
+2	ffffffffff
+2	gggggggggg
+2	hhhhhhhhhh
+2	iiiiiiiiii
+2	jjjjjjjjjj
+SELECT DISTINCT test1.tb1.f11,test2.tb3.f32 FROM test1.tb1,test2.tb3 WHERE f11 IN (SELECT
f31 FROM test2.tb3 WHERE f31=test1.tb1.f13);
+f11	f32
+2	aaaaaaaaaa
+2	bbbbbbbbbb
+2	cccccccccc
+2	dddddddddd
+2	eeeeeeeeee
+2	ffffffffff
+2	gggggggggg
+2	hhhhhhhhhh
+2	iiiiiiiiii
+2	jjjjjjjjjj
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+f11
+15
+6
+UPDATE test1.tb1 set f12 = NULL WHERE f13 = 0;
+UPDATE test2.tb3 set f32 = NULL WHERE f33 = 0;
+UPDATE test3.tb5 set f52 = NULL WHERE f53 = 0;
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f31 IN
(SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL));
+f12
+NULL
+NULL
+NULL
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 IS
NULL UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+f12
+NULL
+NULL
+NULL
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+11	NULL	0	llllllllllllllllllll
+12	bbbbbbbbbb	2	mmmmmmmmmmmmmmmmmmmm
+2	bbbbbbbbbb	2	llllllllllllllllllll
+6	NULL	0	pppppppppppppppppppp
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+f11	f12	f13	f14
+####################################################################################
+no SELECT privilege on of the inner tables
+connection default(user=root)
+REVOKE SELECT ON test2.* FROM testuser1@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+Establish connection con100 (user=testuser1)
+SELECT f11 FROM test1.tb1 WHERE f11 = 1;
+f11
+1
+SELECT f31 FROM test2.tb3 WHERE f31 = 1;
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT f51 FROM test3.tb5 WHERE f51 = 1;
+f51
+1
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL;
+f51
+1
+11
+6
+SELECT f32 FROM test2.tb3 WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f31 IN
(SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 IS
NULL UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+f11	f12	f13	f14
+####################################################################################
+connection default(user=root)
+GRANT SELECT ON test2.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+REVOKE SELECT ON test1.* FROM testuser1@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+no SELECT privilege on the outer table
+Establish connection con100 (user=testuser1)
+SELECT f11 FROM test1.tb1 WHERE f11 = 1;
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT f31 FROM test2.tb3 WHERE f31 = 1;
+f31
+1
+SELECT f51 FROM test3.tb5 WHERE f51 = 1;
+f51
+1
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f31 IN
(SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 IS
NULL UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+####################################################################################
+connection default(user=root)
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser2@'localhost' IDENTIFIED BY 'testpass'
WITH GRANT OPTION;
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser2@'localhost' IDENTIFIED BY 'testpass'
WITH GRANT OPTION;
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser2@'localhost' IDENTIFIED BY 'testpass'
WITH GRANT OPTION;
+REVOKE SELECT,INSERT,UPDATE ON test1.* FROM testuser1@'localhost' IDENTIFIED BY
'testpass';
+REVOKE SELECT,INSERT,UPDATE ON test2.* FROM testuser1@'localhost' IDENTIFIED BY
'testpass';
+REVOKE SELECT,INSERT,UPDATE ON test3.* FROM testuser1@'localhost' IDENTIFIED BY
'testpass';
+####################################################################################
+no privilege
+Establish connection con100 (user=testuser1)
+####################################################################################
+SHOW GRANTS;
+Grants for testuser1@localhost
+GRANT USAGE ON *.* TO 'testuser1'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+ERROR 42000: INSERT command denied to user 'testuser1'@'localhost' for table 'tb5'
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+ERROR 42000: INSERT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+UPDATE test1.tb1 set f12 = NULL WHERE f13 = 0;
+ERROR 42000: UPDATE command denied to user 'testuser1'@'localhost' for table 'tb1'
+UPDATE test2.tb3 set f32 = NULL WHERE f33 = 0;
+ERROR 42000: UPDATE command denied to user 'testuser1'@'localhost' for table 'tb3'
+UPDATE test3.tb5 set f52 = NULL WHERE f53 = 0;
+ERROR 42000: UPDATE command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+SHOW GRANTS;
+Grants for testuser2@localhost
+GRANT USAGE ON *.* TO 'testuser2'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+GRANT SELECT, INSERT, UPDATE ON `test1`.* TO 'testuser2'@'localhost' WITH GRANT OPTION
+GRANT SELECT, INSERT, UPDATE ON `test2`.* TO 'testuser2'@'localhost' WITH GRANT OPTION
+GRANT SELECT, INSERT, UPDATE ON `test3`.* TO 'testuser2'@'localhost' WITH GRANT OPTION
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser1@'localhost';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser1@'localhost';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser1@'localhost';
+####################################################################################
+INSERT,UPDATE,SELECT privilege granted by testuser2
+Establish connection con100 (user=testuser1)
+####################################################################################
+SHOW GRANTS;
+Grants for testuser1@localhost
+GRANT USAGE ON *.* TO 'testuser1'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+GRANT SELECT, INSERT, UPDATE ON `test2`.* TO 'testuser1'@'localhost'
+GRANT SELECT, INSERT, UPDATE ON `test1`.* TO 'testuser1'@'localhost'
+GRANT SELECT, INSERT, UPDATE ON `test3`.* TO 'testuser1'@'localhost'
+INSERT INTO test1.tb2 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test3.tb6 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb4 (f41,f42,f43,f44) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+f11
+15
+6
+UPDATE test1.tb2 set f22 = NULL WHERE f23 = 0;
+UPDATE test2.tb4 set f42 = NULL WHERE f43 = 0;
+UPDATE test3.tb6 set f62 = NULL WHERE f63 = 0;
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+11	NULL	0	llllllllllllllllllll
+12	bbbbbbbbbb	2	mmmmmmmmmmmmmmmmmmmm
+2	bbbbbbbbbb	2	llllllllllllllllllll
+6	NULL	0	pppppppppppppppppppp
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+f11	f12	f13	f14
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+REVOKE SELECT ON test3.* FROM testuser1@'localhost';
+####################################################################################
+no SELECT privilege on one of the inner tables
+Establish connection con100 (user=testuser1)
+####################################################################################
+SHOW GRANTS;
+Grants for testuser1@localhost
+GRANT USAGE ON *.* TO 'testuser1'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+GRANT SELECT, INSERT, UPDATE ON `test2`.* TO 'testuser1'@'localhost'
+GRANT SELECT, INSERT, UPDATE ON `test1`.* TO 'testuser1'@'localhost'
+GRANT INSERT, UPDATE ON `test3`.* TO 'testuser1'@'localhost'
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+####################################################################################
+lost SELECT privilege between PREPARE and EXECUTE and got it again
+Establish connection con200 (user=testuser2):
+####################################################################################
+PREPARE subsel1 FROM 'SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT
f34 FROM test2.tb3 WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 =
''pppppppppppppppppppp''))';
+EXECUTE subsel1;
+f11
+15
+6
+####################################################################################
+connection default(user=root with default privileges):
+####################################################################################
+REVOKE SELECT ON test3.* FROM testuser2@'localhost';
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+EXECUTE subsel1;
+ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 'tb5'
+####################################################################################
+connection default(user=root with default privileges):
+####################################################################################
+GRANT SELECT ON test3.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+EXECUTE subsel1;
+f11
+15
+6
+####################################################################################
+connection default(user=root)
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';

=== added file 'mysql-test/suite/funcs_1/r/innodb_subquery3.result'
--- a/mysql-test/suite/funcs_1/r/innodb_subquery3.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/r/innodb_subquery3.result	2008-06-02 15:01:21 +0000
@@ -0,0 +1,127 @@
+SET autocommit= off;
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+CREATE DATABASE test1;
+USE test1;
+CREATE TABLE tb1 (f11 int, f12 char(10), f13 int, f14 char(20)) ENGINE InnoDB;
+CREATE TABLE tb2 (f21 int, f22 char(10), f23 int, f24 char(20)) ENGINE InnoDB;
+INSERT INTO tb1 VALUES ( 1,'aaaaaaaaaa', 0,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES ( 2,'bbbbbbbbbb', 2,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES ( 3,'cccccccccc', 4,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES ( 4,'dddddddddd', 6,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES ( 5,'eeeeeeeeee', 8,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES ( 6,'ffffffffff', 0,'pppppppppppppppppppp');
+INSERT INTO tb1 VALUES ( 7,'gggggggggg', 2,'qqqqqqqqqqqqqqqqqqqq');
+INSERT INTO tb1 VALUES ( 8,'hhhhhhhhhh', 4,'rrrrrrrrrrrrrrrrrrrr');
+INSERT INTO tb1 VALUES ( 9,'iiiiiiiiii', 6,'ssssssssssssssssssss');
+INSERT INTO tb1 VALUES (10,'jjjjjjjjjj', 8,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES (11,'aaaaaaaaaa', 0,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES (12,'bbbbbbbbbb', 2,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES (13,'cccccccccc', 4,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES (14,'dddddddddd', 6,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES (15,'eeeeeeeeee', 8,'pppppppppppppppppppp');
+INSERT INTO tb2 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb2 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb2 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb2 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb2 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test2;
+USE test2;
+CREATE TABLE tb3 (f31 int, f32 char(10), f33 int, f34 char(20)) ENGINE InnoDB;
+CREATE TABLE tb4 (f41 int, f42 char(10), f43 int, f44 char(20)) ENGINE InnoDB;
+INSERT INTO tb3 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb3 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb3 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb3 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb3 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb4 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb4 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb4 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb4 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb4 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test3;
+USE test3;
+CREATE TABLE tb5 (f51 int, f52 char(10), f53 int, f54 char(20)) ENGINE InnoDB;
+CREATE TABLE tb6 (f61 int, f62 char(10), f63 int, f64 char(20)) ENGINE InnoDB;
+INSERT INTO tb5 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb5 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb5 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb5 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb5 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb6 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb6 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb6 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb6 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb6 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+USE information_schema;
+####################################################################################
+1.1.2 Create 3 user
+DROP USER testuser1@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser1'@'localhost'
+DROP USER testuser2@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser2'@'localhost'
+DROP USER testuser3@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser3'@'localhost'
+CREATE USER testuser1@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser3@'localhost';
+SET PASSWORD FOR testuser3@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+COMMIT;
+############# LOCK/UNLOCK test
+####################################################################################
+Establish connection con200 (user=testuser2)
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f33 = 0 UNION 
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+Establish connection con100 (user=testuser1)
+LOCK TABLE test1.tb1 READ,test2.tb3 WRITE,test3.tb5 WRITE;
+####################################################################################
+connection con200
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f33 = 0 UNION 
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+####################################################################################
+connection default
+# Sleep some time
+SHOW FULL PROCESSLIST;
+Id	User	Host	db	Command	Time	State	Info
+<ID>	root	<HOST>	information_schema	Query	<TIME>	NULL	SHOW FULL
PROCESSLIST
+<ID>	testuser2	<HOST>	test	Query	<TIME>	Locked	SELECT * FROM test1.tb1
WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f33 = 0 UNION 
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0)
+<ID>	testuser1	<HOST>	test	Sleep	<TIME>		NULL
+####################################################################################
+connection con100;
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+UNLOCK TABLES;
+COMMIT;
+####################################################################################
+connection con200;
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+11	aaaaaaaaaa	0	llllllllllllllllllll
+6	ffffffffff	0	pppppppppppppppppppp
+connection default;
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';

=== added file 'mysql-test/suite/funcs_1/r/innodb_subquery4.result'
--- a/mysql-test/suite/funcs_1/r/innodb_subquery4.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/r/innodb_subquery4.result	2008-06-02 15:01:21 +0000
@@ -0,0 +1,151 @@
+SET autocommit= off;
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+CREATE DATABASE test1;
+USE test1;
+CREATE TABLE tb1 (f11 int primary key, f12 char(10), f13 int, f14 char(20)) ENGINE
InnoDB;
+CREATE TABLE tb2 (f21 int primary key, f22 char(10), f23 int, f24 char(20)) ENGINE
InnoDB;
+INSERT INTO tb1 VALUES ( 1,'aaaaaaaaaa', 0,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES ( 2,'bbbbbbbbbb', 2,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES ( 3,'cccccccccc', 4,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES ( 4,'dddddddddd', 6,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES ( 5,'eeeeeeeeee', 8,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES ( 6,'ffffffffff', 0,'pppppppppppppppppppp');
+INSERT INTO tb1 VALUES ( 7,'gggggggggg', 2,'qqqqqqqqqqqqqqqqqqqq');
+INSERT INTO tb1 VALUES ( 8,'hhhhhhhhhh', 4,'rrrrrrrrrrrrrrrrrrrr');
+INSERT INTO tb1 VALUES ( 9,'iiiiiiiiii', 6,'ssssssssssssssssssss');
+INSERT INTO tb1 VALUES (10,'jjjjjjjjjj', 8,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES (11,'aaaaaaaaaa', 0,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES (12,'bbbbbbbbbb', 2,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES (13,'cccccccccc', 4,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES (14,'dddddddddd', 6,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES (15,'eeeeeeeeee', 8,'pppppppppppppppppppp');
+INSERT INTO tb2 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb2 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb2 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb2 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb2 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test2;
+USE test2;
+CREATE TABLE tb3 (f31 int primary key, f32 char(10), f33 int, f34 char(20)) ENGINE
InnoDB;
+CREATE TABLE tb4 (f41 int primary key, f42 char(10), f43 int, f44 char(20)) ENGINE
InnoDB;
+INSERT INTO tb3 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb3 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb3 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb3 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb3 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb4 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb4 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb4 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb4 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb4 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test3;
+USE test3;
+CREATE TABLE tb5 (f51 int primary key, f52 char(10), f53 int, f54 char(20)) ENGINE
InnoDB;
+CREATE TABLE tb6 (f61 int primary key, f62 char(10), f63 int, f64 char(20)) ENGINE
InnoDB;
+INSERT INTO tb5 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb5 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb5 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb5 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb5 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb6 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb6 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb6 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb6 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb6 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+USE information_schema;
+####################################################################################
+1.1.2 Create 3 user
+DROP USER testuser1@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser1'@'localhost'
+DROP USER testuser2@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser2'@'localhost'
+DROP USER testuser3@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser3'@'localhost'
+CREATE USER testuser1@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser3@'localhost';
+SET PASSWORD FOR testuser3@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+COMMIT;
+SELECT @@global.tx_isolation;
+@@global.tx_isolation
+REPEATABLE-READ
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+############# SELECT ... FOR UPDATE test
+####################################################################################
+Establish connection con200 (user=testuser2)
+SET autocommit= off;
+SELECT @@global.tx_isolation;
+@@global.tx_isolation
+REPEATABLE-READ
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+Establish connection con100 (user=testuser1)
+SET autocommit= off;
+SELECT @@global.tx_isolation;
+@@global.tx_isolation
+REPEATABLE-READ
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0)
FOR UPDATE;
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+connection con200
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+####################################################################################
+connection con100;
+UPDATE test1.tb1 SET f12 = NULL WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE
test3.tb5.f53 = 0);
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+connection con200;
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+COMMIT;
+####################################################################################
+connection con100;
+COMMIT;
+####################################################################################
+connection con200;
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+COMMIT;
+connection default;
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';

=== added file 'mysql-test/suite/funcs_1/r/memory_subquery2.result'
--- a/mysql-test/suite/funcs_1/r/memory_subquery2.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/r/memory_subquery2.result	2008-06-02 15:01:21 +0000
@@ -0,0 +1,329 @@
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+CREATE DATABASE test1;
+USE test1;
+CREATE TABLE tb1 (f11 int , f12 char(10), f13 int, f14 char(20)) ENGINE memory;
+CREATE TABLE tb2 (f21 int , f22 char(10), f23 int, f24 char(20)) ENGINE memory;
+INSERT INTO tb1 VALUES ( 1,'aaaaaaaaaa', 0,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES ( 2,'bbbbbbbbbb', 2,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES ( 3,'cccccccccc', 4,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES ( 4,'dddddddddd', 6,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES ( 5,'eeeeeeeeee', 8,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES ( 6,'ffffffffff', 0,'pppppppppppppppppppp');
+INSERT INTO tb1 VALUES ( 7,'gggggggggg', 2,'qqqqqqqqqqqqqqqqqqqq');
+INSERT INTO tb1 VALUES ( 8,'hhhhhhhhhh', 4,'rrrrrrrrrrrrrrrrrrrr');
+INSERT INTO tb1 VALUES ( 9,'iiiiiiiiii', 6,'ssssssssssssssssssss');
+INSERT INTO tb1 VALUES (10,'jjjjjjjjjj', 8,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES (11,'aaaaaaaaaa', 0,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES (12,'bbbbbbbbbb', 2,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES (13,'cccccccccc', 4,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES (14,'dddddddddd', 6,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES (15,'eeeeeeeeee', 8,'pppppppppppppppppppp');
+INSERT INTO tb2 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb2 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb2 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb2 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb2 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+CREATE DATABASE test2;
+USE test2;
+CREATE TABLE tb3 (f31 int , f32 char(10), f33 int, f34 char(20)) ENGINE memory;
+CREATE TABLE tb4 (f41 int , f42 char(10), f43 int, f44 char(20)) ENGINE memory;
+INSERT INTO tb3 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb3 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb3 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb3 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb3 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb4 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb4 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb4 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb4 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb4 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+CREATE DATABASE test3;
+USE test3;
+CREATE TABLE tb5 (f51 int , f52 char(10), f53 int, f54 char(20)) ENGINE memory;
+CREATE TABLE tb6 (f61 int , f62 char(10), f63 int, f64 char(20)) ENGINE memory;
+INSERT INTO tb5 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb5 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb5 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb5 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb5 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb6 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb6 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb6 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb6 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb6 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+USE information_schema;
+####################################################################################
+1.1.2 Create 3 user
+####################################################################################
+DROP USER testuser1@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser1'@'localhost'
+DROP USER testuser2@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser2'@'localhost'
+DROP USER testuser3@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser3'@'localhost'
+CREATE USER testuser1@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+CREATE USER testuser3@'localhost';
+SET PASSWORD FOR testuser3@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser3@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser3@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser3@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+all subqueries must succeed
+Establish connection con100 (user=testuser1)
+####################################################################################
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+SELECT test1.tb1.f11,test2.tb3.f32 FROM test1.tb1,test2.tb3 WHERE f11 IN (SELECT f31 FROM
test2.tb3 WHERE f31=test1.tb1.f13);
+f11	f32
+2	aaaaaaaaaa
+2	aaaaaaaaaa
+2	bbbbbbbbbb
+2	bbbbbbbbbb
+2	cccccccccc
+2	cccccccccc
+2	dddddddddd
+2	dddddddddd
+2	eeeeeeeeee
+2	eeeeeeeeee
+2	ffffffffff
+2	gggggggggg
+2	hhhhhhhhhh
+2	iiiiiiiiii
+2	jjjjjjjjjj
+SELECT DISTINCT test1.tb1.f11,test2.tb3.f32 FROM test1.tb1,test2.tb3 WHERE f11 IN (SELECT
f31 FROM test2.tb3 WHERE f31=test1.tb1.f13);
+f11	f32
+2	aaaaaaaaaa
+2	bbbbbbbbbb
+2	cccccccccc
+2	dddddddddd
+2	eeeeeeeeee
+2	ffffffffff
+2	gggggggggg
+2	hhhhhhhhhh
+2	iiiiiiiiii
+2	jjjjjjjjjj
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+f11
+15
+6
+UPDATE test1.tb1 set f12 = NULL WHERE f13 = 0;
+UPDATE test2.tb3 set f32 = NULL WHERE f33 = 0;
+UPDATE test3.tb5 set f52 = NULL WHERE f53 = 0;
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f31 IN
(SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL));
+f12
+NULL
+NULL
+NULL
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 IS
NULL UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+f12
+NULL
+NULL
+NULL
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+11	NULL	0	llllllllllllllllllll
+12	bbbbbbbbbb	2	mmmmmmmmmmmmmmmmmmmm
+2	bbbbbbbbbb	2	llllllllllllllllllll
+6	NULL	0	pppppppppppppppppppp
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+f11	f12	f13	f14
+####################################################################################
+no SELECT privilege on of the inner tables
+connection default(user=root)
+REVOKE SELECT ON test2.* FROM testuser1@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+Establish connection con100 (user=testuser1)
+SELECT f11 FROM test1.tb1 WHERE f11 = 1;
+f11
+1
+SELECT f31 FROM test2.tb3 WHERE f31 = 1;
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT f51 FROM test3.tb5 WHERE f51 = 1;
+f51
+1
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL;
+f51
+1
+11
+6
+SELECT f32 FROM test2.tb3 WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f31 IN
(SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 IS
NULL UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+f11	f12	f13	f14
+####################################################################################
+connection default(user=root)
+GRANT SELECT ON test2.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+REVOKE SELECT ON test1.* FROM testuser1@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+no SELECT privilege on the outer table
+Establish connection con100 (user=testuser1)
+SELECT f11 FROM test1.tb1 WHERE f11 = 1;
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT f31 FROM test2.tb3 WHERE f31 = 1;
+f31
+1
+SELECT f51 FROM test3.tb5 WHERE f51 = 1;
+f51
+1
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f31 IN
(SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 IS
NULL UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+####################################################################################
+connection default(user=root)
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser2@'localhost' IDENTIFIED BY 'testpass'
WITH GRANT OPTION;
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser2@'localhost' IDENTIFIED BY 'testpass'
WITH GRANT OPTION;
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser2@'localhost' IDENTIFIED BY 'testpass'
WITH GRANT OPTION;
+REVOKE SELECT,INSERT,UPDATE ON test1.* FROM testuser1@'localhost' IDENTIFIED BY
'testpass';
+REVOKE SELECT,INSERT,UPDATE ON test2.* FROM testuser1@'localhost' IDENTIFIED BY
'testpass';
+REVOKE SELECT,INSERT,UPDATE ON test3.* FROM testuser1@'localhost' IDENTIFIED BY
'testpass';
+####################################################################################
+no privilege
+Establish connection con100 (user=testuser1)
+####################################################################################
+SHOW GRANTS;
+Grants for testuser1@localhost
+GRANT USAGE ON *.* TO 'testuser1'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+ERROR 42000: INSERT command denied to user 'testuser1'@'localhost' for table 'tb5'
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+ERROR 42000: INSERT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+UPDATE test1.tb1 set f12 = NULL WHERE f13 = 0;
+ERROR 42000: UPDATE command denied to user 'testuser1'@'localhost' for table 'tb1'
+UPDATE test2.tb3 set f32 = NULL WHERE f33 = 0;
+ERROR 42000: UPDATE command denied to user 'testuser1'@'localhost' for table 'tb3'
+UPDATE test3.tb5 set f52 = NULL WHERE f53 = 0;
+ERROR 42000: UPDATE command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+SHOW GRANTS;
+Grants for testuser2@localhost
+GRANT USAGE ON *.* TO 'testuser2'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+GRANT SELECT, INSERT, UPDATE ON `test1`.* TO 'testuser2'@'localhost' WITH GRANT OPTION
+GRANT SELECT, INSERT, UPDATE ON `test2`.* TO 'testuser2'@'localhost' WITH GRANT OPTION
+GRANT SELECT, INSERT, UPDATE ON `test3`.* TO 'testuser2'@'localhost' WITH GRANT OPTION
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser1@'localhost';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser1@'localhost';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser1@'localhost';
+####################################################################################
+INSERT,UPDATE,SELECT privilege granted by testuser2
+Establish connection con100 (user=testuser1)
+####################################################################################
+SHOW GRANTS;
+Grants for testuser1@localhost
+GRANT USAGE ON *.* TO 'testuser1'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+GRANT SELECT, INSERT, UPDATE ON `test2`.* TO 'testuser1'@'localhost'
+GRANT SELECT, INSERT, UPDATE ON `test1`.* TO 'testuser1'@'localhost'
+GRANT SELECT, INSERT, UPDATE ON `test3`.* TO 'testuser1'@'localhost'
+INSERT INTO test1.tb2 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test3.tb6 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb4 (f41,f42,f43,f44) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+f11
+15
+6
+UPDATE test1.tb2 set f22 = NULL WHERE f23 = 0;
+UPDATE test2.tb4 set f42 = NULL WHERE f43 = 0;
+UPDATE test3.tb6 set f62 = NULL WHERE f63 = 0;
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+11	NULL	0	llllllllllllllllllll
+12	bbbbbbbbbb	2	mmmmmmmmmmmmmmmmmmmm
+2	bbbbbbbbbb	2	llllllllllllllllllll
+6	NULL	0	pppppppppppppppppppp
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+f11	f12	f13	f14
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+REVOKE SELECT ON test3.* FROM testuser1@'localhost';
+####################################################################################
+no SELECT privilege on one of the inner tables
+Establish connection con100 (user=testuser1)
+####################################################################################
+SHOW GRANTS;
+Grants for testuser1@localhost
+GRANT USAGE ON *.* TO 'testuser1'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+GRANT SELECT, INSERT, UPDATE ON `test2`.* TO 'testuser1'@'localhost'
+GRANT SELECT, INSERT, UPDATE ON `test1`.* TO 'testuser1'@'localhost'
+GRANT INSERT, UPDATE ON `test3`.* TO 'testuser1'@'localhost'
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+####################################################################################
+lost SELECT privilege between PREPARE and EXECUTE and got it again
+Establish connection con200 (user=testuser2):
+####################################################################################
+PREPARE subsel1 FROM 'SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT
f34 FROM test2.tb3 WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 =
''pppppppppppppppppppp''))';
+EXECUTE subsel1;
+f11
+15
+6
+####################################################################################
+connection default(user=root with default privileges):
+####################################################################################
+REVOKE SELECT ON test3.* FROM testuser2@'localhost';
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+EXECUTE subsel1;
+ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 'tb5'
+####################################################################################
+connection default(user=root with default privileges):
+####################################################################################
+GRANT SELECT ON test3.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+EXECUTE subsel1;
+f11
+15
+6
+####################################################################################
+connection default(user=root)
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';

=== added file 'mysql-test/suite/funcs_1/r/memory_subquery3.result'
--- a/mysql-test/suite/funcs_1/r/memory_subquery3.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/r/memory_subquery3.result	2008-06-02 15:01:21 +0000
@@ -0,0 +1,127 @@
+SET autocommit= off;
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+CREATE DATABASE test1;
+USE test1;
+CREATE TABLE tb1 (f11 int, f12 char(10), f13 int, f14 char(20)) ENGINE memory;
+CREATE TABLE tb2 (f21 int, f22 char(10), f23 int, f24 char(20)) ENGINE memory;
+INSERT INTO tb1 VALUES ( 1,'aaaaaaaaaa', 0,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES ( 2,'bbbbbbbbbb', 2,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES ( 3,'cccccccccc', 4,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES ( 4,'dddddddddd', 6,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES ( 5,'eeeeeeeeee', 8,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES ( 6,'ffffffffff', 0,'pppppppppppppppppppp');
+INSERT INTO tb1 VALUES ( 7,'gggggggggg', 2,'qqqqqqqqqqqqqqqqqqqq');
+INSERT INTO tb1 VALUES ( 8,'hhhhhhhhhh', 4,'rrrrrrrrrrrrrrrrrrrr');
+INSERT INTO tb1 VALUES ( 9,'iiiiiiiiii', 6,'ssssssssssssssssssss');
+INSERT INTO tb1 VALUES (10,'jjjjjjjjjj', 8,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES (11,'aaaaaaaaaa', 0,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES (12,'bbbbbbbbbb', 2,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES (13,'cccccccccc', 4,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES (14,'dddddddddd', 6,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES (15,'eeeeeeeeee', 8,'pppppppppppppppppppp');
+INSERT INTO tb2 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb2 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb2 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb2 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb2 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test2;
+USE test2;
+CREATE TABLE tb3 (f31 int, f32 char(10), f33 int, f34 char(20)) ENGINE memory;
+CREATE TABLE tb4 (f41 int, f42 char(10), f43 int, f44 char(20)) ENGINE memory;
+INSERT INTO tb3 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb3 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb3 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb3 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb3 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb4 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb4 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb4 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb4 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb4 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test3;
+USE test3;
+CREATE TABLE tb5 (f51 int, f52 char(10), f53 int, f54 char(20)) ENGINE memory;
+CREATE TABLE tb6 (f61 int, f62 char(10), f63 int, f64 char(20)) ENGINE memory;
+INSERT INTO tb5 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb5 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb5 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb5 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb5 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb6 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb6 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb6 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb6 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb6 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+USE information_schema;
+####################################################################################
+1.1.2 Create 3 user
+DROP USER testuser1@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser1'@'localhost'
+DROP USER testuser2@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser2'@'localhost'
+DROP USER testuser3@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser3'@'localhost'
+CREATE USER testuser1@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser3@'localhost';
+SET PASSWORD FOR testuser3@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+COMMIT;
+############# LOCK/UNLOCK test
+####################################################################################
+Establish connection con200 (user=testuser2)
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f33 = 0 UNION 
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+Establish connection con100 (user=testuser1)
+LOCK TABLE test1.tb1 READ,test2.tb3 WRITE,test3.tb5 WRITE;
+####################################################################################
+connection con200
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f33 = 0 UNION 
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+####################################################################################
+connection default
+# Sleep some time
+SHOW FULL PROCESSLIST;
+Id	User	Host	db	Command	Time	State	Info
+<ID>	root	<HOST>	information_schema	Query	<TIME>	NULL	SHOW FULL
PROCESSLIST
+<ID>	testuser2	<HOST>	test	Query	<TIME>	Locked	SELECT * FROM test1.tb1
WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f33 = 0 UNION 
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0)
+<ID>	testuser1	<HOST>	test	Sleep	<TIME>		NULL
+####################################################################################
+connection con100;
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+UNLOCK TABLES;
+COMMIT;
+####################################################################################
+connection con200;
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+11	aaaaaaaaaa	0	llllllllllllllllllll
+6	ffffffffff	0	pppppppppppppppppppp
+connection default;
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';

=== added file 'mysql-test/suite/funcs_1/r/memory_subquery4.result'
--- a/mysql-test/suite/funcs_1/r/memory_subquery4.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/r/memory_subquery4.result	2008-06-02 15:01:21 +0000
@@ -0,0 +1,151 @@
+SET autocommit= off;
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+CREATE DATABASE test1;
+USE test1;
+CREATE TABLE tb1 (f11 int primary key, f12 char(10), f13 int, f14 char(20)) ENGINE
memory;
+CREATE TABLE tb2 (f21 int primary key, f22 char(10), f23 int, f24 char(20)) ENGINE
memory;
+INSERT INTO tb1 VALUES ( 1,'aaaaaaaaaa', 0,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES ( 2,'bbbbbbbbbb', 2,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES ( 3,'cccccccccc', 4,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES ( 4,'dddddddddd', 6,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES ( 5,'eeeeeeeeee', 8,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES ( 6,'ffffffffff', 0,'pppppppppppppppppppp');
+INSERT INTO tb1 VALUES ( 7,'gggggggggg', 2,'qqqqqqqqqqqqqqqqqqqq');
+INSERT INTO tb1 VALUES ( 8,'hhhhhhhhhh', 4,'rrrrrrrrrrrrrrrrrrrr');
+INSERT INTO tb1 VALUES ( 9,'iiiiiiiiii', 6,'ssssssssssssssssssss');
+INSERT INTO tb1 VALUES (10,'jjjjjjjjjj', 8,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES (11,'aaaaaaaaaa', 0,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES (12,'bbbbbbbbbb', 2,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES (13,'cccccccccc', 4,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES (14,'dddddddddd', 6,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES (15,'eeeeeeeeee', 8,'pppppppppppppppppppp');
+INSERT INTO tb2 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb2 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb2 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb2 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb2 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test2;
+USE test2;
+CREATE TABLE tb3 (f31 int primary key, f32 char(10), f33 int, f34 char(20)) ENGINE
memory;
+CREATE TABLE tb4 (f41 int primary key, f42 char(10), f43 int, f44 char(20)) ENGINE
memory;
+INSERT INTO tb3 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb3 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb3 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb3 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb3 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb4 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb4 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb4 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb4 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb4 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test3;
+USE test3;
+CREATE TABLE tb5 (f51 int primary key, f52 char(10), f53 int, f54 char(20)) ENGINE
memory;
+CREATE TABLE tb6 (f61 int primary key, f62 char(10), f63 int, f64 char(20)) ENGINE
memory;
+INSERT INTO tb5 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb5 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb5 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb5 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb5 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb6 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb6 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb6 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb6 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb6 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+USE information_schema;
+####################################################################################
+1.1.2 Create 3 user
+DROP USER testuser1@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser1'@'localhost'
+DROP USER testuser2@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser2'@'localhost'
+DROP USER testuser3@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser3'@'localhost'
+CREATE USER testuser1@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser3@'localhost';
+SET PASSWORD FOR testuser3@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+COMMIT;
+SELECT @@global.tx_isolation;
+@@global.tx_isolation
+REPEATABLE-READ
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+############# SELECT ... FOR UPDATE test
+####################################################################################
+Establish connection con200 (user=testuser2)
+SET autocommit= off;
+SELECT @@global.tx_isolation;
+@@global.tx_isolation
+REPEATABLE-READ
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+Establish connection con100 (user=testuser1)
+SET autocommit= off;
+SELECT @@global.tx_isolation;
+@@global.tx_isolation
+REPEATABLE-READ
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0)
FOR UPDATE;
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+connection con200
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+####################################################################################
+connection con100;
+UPDATE test1.tb1 SET f12 = NULL WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE
test3.tb5.f53 = 0);
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+connection con200;
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+COMMIT;
+####################################################################################
+connection con100;
+COMMIT;
+####################################################################################
+connection con200;
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+COMMIT;
+connection default;
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';

=== added file 'mysql-test/suite/funcs_1/r/myisam_subquery2.result'
--- a/mysql-test/suite/funcs_1/r/myisam_subquery2.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/r/myisam_subquery2.result	2008-06-02 15:01:21 +0000
@@ -0,0 +1,329 @@
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+CREATE DATABASE test1;
+USE test1;
+CREATE TABLE tb1 (f11 int , f12 char(10), f13 int, f14 char(20)) ENGINE MyISAM;
+CREATE TABLE tb2 (f21 int , f22 char(10), f23 int, f24 char(20)) ENGINE MyISAM;
+INSERT INTO tb1 VALUES ( 1,'aaaaaaaaaa', 0,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES ( 2,'bbbbbbbbbb', 2,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES ( 3,'cccccccccc', 4,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES ( 4,'dddddddddd', 6,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES ( 5,'eeeeeeeeee', 8,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES ( 6,'ffffffffff', 0,'pppppppppppppppppppp');
+INSERT INTO tb1 VALUES ( 7,'gggggggggg', 2,'qqqqqqqqqqqqqqqqqqqq');
+INSERT INTO tb1 VALUES ( 8,'hhhhhhhhhh', 4,'rrrrrrrrrrrrrrrrrrrr');
+INSERT INTO tb1 VALUES ( 9,'iiiiiiiiii', 6,'ssssssssssssssssssss');
+INSERT INTO tb1 VALUES (10,'jjjjjjjjjj', 8,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES (11,'aaaaaaaaaa', 0,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES (12,'bbbbbbbbbb', 2,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES (13,'cccccccccc', 4,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES (14,'dddddddddd', 6,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES (15,'eeeeeeeeee', 8,'pppppppppppppppppppp');
+INSERT INTO tb2 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb2 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb2 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb2 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb2 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+CREATE DATABASE test2;
+USE test2;
+CREATE TABLE tb3 (f31 int , f32 char(10), f33 int, f34 char(20)) ENGINE MyISAM;
+CREATE TABLE tb4 (f41 int , f42 char(10), f43 int, f44 char(20)) ENGINE MyISAM;
+INSERT INTO tb3 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb3 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb3 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb3 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb3 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb4 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb4 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb4 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb4 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb4 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+CREATE DATABASE test3;
+USE test3;
+CREATE TABLE tb5 (f51 int , f52 char(10), f53 int, f54 char(20)) ENGINE MyISAM;
+CREATE TABLE tb6 (f61 int , f62 char(10), f63 int, f64 char(20)) ENGINE MyISAM;
+INSERT INTO tb5 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb5 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb5 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb5 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb5 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb6 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb6 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb6 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb6 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb6 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+USE information_schema;
+####################################################################################
+1.1.2 Create 3 user
+####################################################################################
+DROP USER testuser1@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser1'@'localhost'
+DROP USER testuser2@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser2'@'localhost'
+DROP USER testuser3@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser3'@'localhost'
+CREATE USER testuser1@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+CREATE USER testuser3@'localhost';
+SET PASSWORD FOR testuser3@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser3@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser3@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser3@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+all subqueries must succeed
+Establish connection con100 (user=testuser1)
+####################################################################################
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+SELECT test1.tb1.f11,test2.tb3.f32 FROM test1.tb1,test2.tb3 WHERE f11 IN (SELECT f31 FROM
test2.tb3 WHERE f31=test1.tb1.f13);
+f11	f32
+2	aaaaaaaaaa
+2	aaaaaaaaaa
+2	bbbbbbbbbb
+2	bbbbbbbbbb
+2	cccccccccc
+2	cccccccccc
+2	dddddddddd
+2	dddddddddd
+2	eeeeeeeeee
+2	eeeeeeeeee
+2	ffffffffff
+2	gggggggggg
+2	hhhhhhhhhh
+2	iiiiiiiiii
+2	jjjjjjjjjj
+SELECT DISTINCT test1.tb1.f11,test2.tb3.f32 FROM test1.tb1,test2.tb3 WHERE f11 IN (SELECT
f31 FROM test2.tb3 WHERE f31=test1.tb1.f13);
+f11	f32
+2	aaaaaaaaaa
+2	bbbbbbbbbb
+2	cccccccccc
+2	dddddddddd
+2	eeeeeeeeee
+2	ffffffffff
+2	gggggggggg
+2	hhhhhhhhhh
+2	iiiiiiiiii
+2	jjjjjjjjjj
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+f11
+15
+6
+UPDATE test1.tb1 set f12 = NULL WHERE f13 = 0;
+UPDATE test2.tb3 set f32 = NULL WHERE f33 = 0;
+UPDATE test3.tb5 set f52 = NULL WHERE f53 = 0;
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f31 IN
(SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL));
+f12
+NULL
+NULL
+NULL
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 IS
NULL UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+f12
+NULL
+NULL
+NULL
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+11	NULL	0	llllllllllllllllllll
+12	bbbbbbbbbb	2	mmmmmmmmmmmmmmmmmmmm
+2	bbbbbbbbbb	2	llllllllllllllllllll
+6	NULL	0	pppppppppppppppppppp
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+f11	f12	f13	f14
+####################################################################################
+no SELECT privilege on of the inner tables
+connection default(user=root)
+REVOKE SELECT ON test2.* FROM testuser1@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+Establish connection con100 (user=testuser1)
+SELECT f11 FROM test1.tb1 WHERE f11 = 1;
+f11
+1
+SELECT f31 FROM test2.tb3 WHERE f31 = 1;
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT f51 FROM test3.tb5 WHERE f51 = 1;
+f51
+1
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL;
+f51
+1
+11
+6
+SELECT f32 FROM test2.tb3 WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f31 IN
(SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 IS
NULL UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+f11	f12	f13	f14
+####################################################################################
+connection default(user=root)
+GRANT SELECT ON test2.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+REVOKE SELECT ON test1.* FROM testuser1@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+no SELECT privilege on the outer table
+Establish connection con100 (user=testuser1)
+SELECT f11 FROM test1.tb1 WHERE f11 = 1;
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT f31 FROM test2.tb3 WHERE f31 = 1;
+f31
+1
+SELECT f51 FROM test3.tb5 WHERE f51 = 1;
+f51
+1
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f31 IN
(SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 IS
NULL UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+####################################################################################
+connection default(user=root)
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser2@'localhost' IDENTIFIED BY 'testpass'
WITH GRANT OPTION;
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser2@'localhost' IDENTIFIED BY 'testpass'
WITH GRANT OPTION;
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser2@'localhost' IDENTIFIED BY 'testpass'
WITH GRANT OPTION;
+REVOKE SELECT,INSERT,UPDATE ON test1.* FROM testuser1@'localhost' IDENTIFIED BY
'testpass';
+REVOKE SELECT,INSERT,UPDATE ON test2.* FROM testuser1@'localhost' IDENTIFIED BY
'testpass';
+REVOKE SELECT,INSERT,UPDATE ON test3.* FROM testuser1@'localhost' IDENTIFIED BY
'testpass';
+####################################################################################
+no privilege
+Establish connection con100 (user=testuser1)
+####################################################################################
+SHOW GRANTS;
+Grants for testuser1@localhost
+GRANT USAGE ON *.* TO 'testuser1'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+ERROR 42000: INSERT command denied to user 'testuser1'@'localhost' for table 'tb5'
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+ERROR 42000: INSERT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+UPDATE test1.tb1 set f12 = NULL WHERE f13 = 0;
+ERROR 42000: UPDATE command denied to user 'testuser1'@'localhost' for table 'tb1'
+UPDATE test2.tb3 set f32 = NULL WHERE f33 = 0;
+ERROR 42000: UPDATE command denied to user 'testuser1'@'localhost' for table 'tb3'
+UPDATE test3.tb5 set f52 = NULL WHERE f53 = 0;
+ERROR 42000: UPDATE command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+SHOW GRANTS;
+Grants for testuser2@localhost
+GRANT USAGE ON *.* TO 'testuser2'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+GRANT SELECT, INSERT, UPDATE ON `test1`.* TO 'testuser2'@'localhost' WITH GRANT OPTION
+GRANT SELECT, INSERT, UPDATE ON `test2`.* TO 'testuser2'@'localhost' WITH GRANT OPTION
+GRANT SELECT, INSERT, UPDATE ON `test3`.* TO 'testuser2'@'localhost' WITH GRANT OPTION
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser1@'localhost';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser1@'localhost';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser1@'localhost';
+####################################################################################
+INSERT,UPDATE,SELECT privilege granted by testuser2
+Establish connection con100 (user=testuser1)
+####################################################################################
+SHOW GRANTS;
+Grants for testuser1@localhost
+GRANT USAGE ON *.* TO 'testuser1'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+GRANT SELECT, INSERT, UPDATE ON `test2`.* TO 'testuser1'@'localhost'
+GRANT SELECT, INSERT, UPDATE ON `test1`.* TO 'testuser1'@'localhost'
+GRANT SELECT, INSERT, UPDATE ON `test3`.* TO 'testuser1'@'localhost'
+INSERT INTO test1.tb2 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test3.tb6 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb4 (f41,f42,f43,f44) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+f11
+15
+6
+UPDATE test1.tb2 set f22 = NULL WHERE f23 = 0;
+UPDATE test2.tb4 set f42 = NULL WHERE f43 = 0;
+UPDATE test3.tb6 set f62 = NULL WHERE f63 = 0;
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+11	NULL	0	llllllllllllllllllll
+12	bbbbbbbbbb	2	mmmmmmmmmmmmmmmmmmmm
+2	bbbbbbbbbb	2	llllllllllllllllllll
+6	NULL	0	pppppppppppppppppppp
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+f11	f12	f13	f14
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+REVOKE SELECT ON test3.* FROM testuser1@'localhost';
+####################################################################################
+no SELECT privilege on one of the inner tables
+Establish connection con100 (user=testuser1)
+####################################################################################
+SHOW GRANTS;
+Grants for testuser1@localhost
+GRANT USAGE ON *.* TO 'testuser1'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+GRANT SELECT, INSERT, UPDATE ON `test2`.* TO 'testuser1'@'localhost'
+GRANT SELECT, INSERT, UPDATE ON `test1`.* TO 'testuser1'@'localhost'
+GRANT INSERT, UPDATE ON `test3`.* TO 'testuser1'@'localhost'
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+####################################################################################
+lost SELECT privilege between PREPARE and EXECUTE and got it again
+Establish connection con200 (user=testuser2):
+####################################################################################
+PREPARE subsel1 FROM 'SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT
f34 FROM test2.tb3 WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 =
''pppppppppppppppppppp''))';
+EXECUTE subsel1;
+f11
+15
+6
+####################################################################################
+connection default(user=root with default privileges):
+####################################################################################
+REVOKE SELECT ON test3.* FROM testuser2@'localhost';
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+EXECUTE subsel1;
+ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 'tb5'
+####################################################################################
+connection default(user=root with default privileges):
+####################################################################################
+GRANT SELECT ON test3.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+EXECUTE subsel1;
+f11
+15
+6
+####################################################################################
+connection default(user=root)
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';

=== added file 'mysql-test/suite/funcs_1/r/myisam_subquery3.result'
--- a/mysql-test/suite/funcs_1/r/myisam_subquery3.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/r/myisam_subquery3.result	2008-06-02 15:01:21 +0000
@@ -0,0 +1,127 @@
+SET autocommit= off;
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+CREATE DATABASE test1;
+USE test1;
+CREATE TABLE tb1 (f11 int, f12 char(10), f13 int, f14 char(20)) ENGINE myisam;
+CREATE TABLE tb2 (f21 int, f22 char(10), f23 int, f24 char(20)) ENGINE myisam;
+INSERT INTO tb1 VALUES ( 1,'aaaaaaaaaa', 0,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES ( 2,'bbbbbbbbbb', 2,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES ( 3,'cccccccccc', 4,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES ( 4,'dddddddddd', 6,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES ( 5,'eeeeeeeeee', 8,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES ( 6,'ffffffffff', 0,'pppppppppppppppppppp');
+INSERT INTO tb1 VALUES ( 7,'gggggggggg', 2,'qqqqqqqqqqqqqqqqqqqq');
+INSERT INTO tb1 VALUES ( 8,'hhhhhhhhhh', 4,'rrrrrrrrrrrrrrrrrrrr');
+INSERT INTO tb1 VALUES ( 9,'iiiiiiiiii', 6,'ssssssssssssssssssss');
+INSERT INTO tb1 VALUES (10,'jjjjjjjjjj', 8,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES (11,'aaaaaaaaaa', 0,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES (12,'bbbbbbbbbb', 2,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES (13,'cccccccccc', 4,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES (14,'dddddddddd', 6,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES (15,'eeeeeeeeee', 8,'pppppppppppppppppppp');
+INSERT INTO tb2 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb2 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb2 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb2 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb2 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test2;
+USE test2;
+CREATE TABLE tb3 (f31 int, f32 char(10), f33 int, f34 char(20)) ENGINE myisam;
+CREATE TABLE tb4 (f41 int, f42 char(10), f43 int, f44 char(20)) ENGINE myisam;
+INSERT INTO tb3 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb3 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb3 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb3 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb3 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb4 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb4 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb4 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb4 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb4 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test3;
+USE test3;
+CREATE TABLE tb5 (f51 int, f52 char(10), f53 int, f54 char(20)) ENGINE myisam;
+CREATE TABLE tb6 (f61 int, f62 char(10), f63 int, f64 char(20)) ENGINE myisam;
+INSERT INTO tb5 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb5 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb5 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb5 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb5 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb6 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb6 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb6 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb6 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb6 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+USE information_schema;
+####################################################################################
+1.1.2 Create 3 user
+DROP USER testuser1@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser1'@'localhost'
+DROP USER testuser2@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser2'@'localhost'
+DROP USER testuser3@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser3'@'localhost'
+CREATE USER testuser1@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser3@'localhost';
+SET PASSWORD FOR testuser3@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+COMMIT;
+############# LOCK/UNLOCK test
+####################################################################################
+Establish connection con200 (user=testuser2)
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f33 = 0 UNION 
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+Establish connection con100 (user=testuser1)
+LOCK TABLE test1.tb1 READ,test2.tb3 WRITE,test3.tb5 WRITE;
+####################################################################################
+connection con200
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f33 = 0 UNION 
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+####################################################################################
+connection default
+# Sleep some time
+SHOW FULL PROCESSLIST;
+Id	User	Host	db	Command	Time	State	Info
+<ID>	root	<HOST>	information_schema	Query	<TIME>	NULL	SHOW FULL
PROCESSLIST
+<ID>	testuser2	<HOST>	test	Query	<TIME>	Locked	SELECT * FROM test1.tb1
WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f33 = 0 UNION 
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0)
+<ID>	testuser1	<HOST>	test	Sleep	<TIME>		NULL
+####################################################################################
+connection con100;
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+UNLOCK TABLES;
+COMMIT;
+####################################################################################
+connection con200;
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+11	aaaaaaaaaa	0	llllllllllllllllllll
+6	ffffffffff	0	pppppppppppppppppppp
+connection default;
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';

=== added file 'mysql-test/suite/funcs_1/r/myisam_subquery4.result'
--- a/mysql-test/suite/funcs_1/r/myisam_subquery4.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/r/myisam_subquery4.result	2008-06-02 15:01:21 +0000
@@ -0,0 +1,151 @@
+SET autocommit= off;
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+CREATE DATABASE test1;
+USE test1;
+CREATE TABLE tb1 (f11 int primary key, f12 char(10), f13 int, f14 char(20)) ENGINE
MyISAM;
+CREATE TABLE tb2 (f21 int primary key, f22 char(10), f23 int, f24 char(20)) ENGINE
MyISAM;
+INSERT INTO tb1 VALUES ( 1,'aaaaaaaaaa', 0,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES ( 2,'bbbbbbbbbb', 2,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES ( 3,'cccccccccc', 4,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES ( 4,'dddddddddd', 6,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES ( 5,'eeeeeeeeee', 8,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES ( 6,'ffffffffff', 0,'pppppppppppppppppppp');
+INSERT INTO tb1 VALUES ( 7,'gggggggggg', 2,'qqqqqqqqqqqqqqqqqqqq');
+INSERT INTO tb1 VALUES ( 8,'hhhhhhhhhh', 4,'rrrrrrrrrrrrrrrrrrrr');
+INSERT INTO tb1 VALUES ( 9,'iiiiiiiiii', 6,'ssssssssssssssssssss');
+INSERT INTO tb1 VALUES (10,'jjjjjjjjjj', 8,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES (11,'aaaaaaaaaa', 0,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES (12,'bbbbbbbbbb', 2,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES (13,'cccccccccc', 4,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES (14,'dddddddddd', 6,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES (15,'eeeeeeeeee', 8,'pppppppppppppppppppp');
+INSERT INTO tb2 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb2 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb2 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb2 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb2 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test2;
+USE test2;
+CREATE TABLE tb3 (f31 int primary key, f32 char(10), f33 int, f34 char(20)) ENGINE
MyISAM;
+CREATE TABLE tb4 (f41 int primary key, f42 char(10), f43 int, f44 char(20)) ENGINE
MyISAM;
+INSERT INTO tb3 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb3 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb3 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb3 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb3 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb4 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb4 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb4 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb4 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb4 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test3;
+USE test3;
+CREATE TABLE tb5 (f51 int primary key, f52 char(10), f53 int, f54 char(20)) ENGINE
MyISAM;
+CREATE TABLE tb6 (f61 int primary key, f62 char(10), f63 int, f64 char(20)) ENGINE
MyISAM;
+INSERT INTO tb5 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb5 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb5 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb5 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb5 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb6 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb6 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb6 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb6 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb6 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+USE information_schema;
+####################################################################################
+1.1.2 Create 3 user
+DROP USER testuser1@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser1'@'localhost'
+DROP USER testuser2@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser2'@'localhost'
+DROP USER testuser3@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser3'@'localhost'
+CREATE USER testuser1@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser3@'localhost';
+SET PASSWORD FOR testuser3@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+COMMIT;
+SELECT @@global.tx_isolation;
+@@global.tx_isolation
+REPEATABLE-READ
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+############# SELECT ... FOR UPDATE test
+####################################################################################
+Establish connection con200 (user=testuser2)
+SET autocommit= off;
+SELECT @@global.tx_isolation;
+@@global.tx_isolation
+REPEATABLE-READ
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+Establish connection con100 (user=testuser1)
+SET autocommit= off;
+SELECT @@global.tx_isolation;
+@@global.tx_isolation
+REPEATABLE-READ
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0)
FOR UPDATE;
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+connection con200
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+####################################################################################
+connection con100;
+UPDATE test1.tb1 SET f12 = NULL WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE
test3.tb5.f53 = 0);
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+connection con200;
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+COMMIT;
+####################################################################################
+connection con100;
+COMMIT;
+####################################################################################
+connection con200;
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+COMMIT;
+connection default;
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';

=== added file 'mysql-test/suite/funcs_1/r/ndb_subquery2.result'
--- a/mysql-test/suite/funcs_1/r/ndb_subquery2.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/r/ndb_subquery2.result	2008-06-02 15:01:21 +0000
@@ -0,0 +1,329 @@
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+CREATE DATABASE test1;
+USE test1;
+CREATE TABLE tb1 (f11 int , f12 char(10), f13 int, f14 char(20)) ENGINE NDB;
+CREATE TABLE tb2 (f21 int , f22 char(10), f23 int, f24 char(20)) ENGINE NDB;
+INSERT INTO tb1 VALUES ( 1,'aaaaaaaaaa', 0,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES ( 2,'bbbbbbbbbb', 2,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES ( 3,'cccccccccc', 4,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES ( 4,'dddddddddd', 6,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES ( 5,'eeeeeeeeee', 8,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES ( 6,'ffffffffff', 0,'pppppppppppppppppppp');
+INSERT INTO tb1 VALUES ( 7,'gggggggggg', 2,'qqqqqqqqqqqqqqqqqqqq');
+INSERT INTO tb1 VALUES ( 8,'hhhhhhhhhh', 4,'rrrrrrrrrrrrrrrrrrrr');
+INSERT INTO tb1 VALUES ( 9,'iiiiiiiiii', 6,'ssssssssssssssssssss');
+INSERT INTO tb1 VALUES (10,'jjjjjjjjjj', 8,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES (11,'aaaaaaaaaa', 0,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES (12,'bbbbbbbbbb', 2,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES (13,'cccccccccc', 4,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES (14,'dddddddddd', 6,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES (15,'eeeeeeeeee', 8,'pppppppppppppppppppp');
+INSERT INTO tb2 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb2 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb2 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb2 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb2 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+CREATE DATABASE test2;
+USE test2;
+CREATE TABLE tb3 (f31 int , f32 char(10), f33 int, f34 char(20)) ENGINE NDB;
+CREATE TABLE tb4 (f41 int , f42 char(10), f43 int, f44 char(20)) ENGINE NDB;
+INSERT INTO tb3 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb3 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb3 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb3 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb3 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb4 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb4 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb4 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb4 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb4 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+CREATE DATABASE test3;
+USE test3;
+CREATE TABLE tb5 (f51 int , f52 char(10), f53 int, f54 char(20)) ENGINE NDB;
+CREATE TABLE tb6 (f61 int , f62 char(10), f63 int, f64 char(20)) ENGINE NDB;
+INSERT INTO tb5 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb5 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb5 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb5 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb5 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb6 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb6 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb6 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb6 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb6 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+USE information_schema;
+####################################################################################
+1.1.2 Create 3 user
+####################################################################################
+DROP USER testuser1@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser1'@'localhost'
+DROP USER testuser2@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser2'@'localhost'
+DROP USER testuser3@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser3'@'localhost'
+CREATE USER testuser1@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+CREATE USER testuser3@'localhost';
+SET PASSWORD FOR testuser3@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser3@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser3@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser3@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+all subqueries must succeed
+Establish connection con100 (user=testuser1)
+####################################################################################
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+SELECT test1.tb1.f11,test2.tb3.f32 FROM test1.tb1,test2.tb3 WHERE f11 IN (SELECT f31 FROM
test2.tb3 WHERE f31=test1.tb1.f13);
+f11	f32
+2	aaaaaaaaaa
+2	aaaaaaaaaa
+2	bbbbbbbbbb
+2	bbbbbbbbbb
+2	cccccccccc
+2	cccccccccc
+2	dddddddddd
+2	dddddddddd
+2	eeeeeeeeee
+2	eeeeeeeeee
+2	ffffffffff
+2	gggggggggg
+2	hhhhhhhhhh
+2	iiiiiiiiii
+2	jjjjjjjjjj
+SELECT DISTINCT test1.tb1.f11,test2.tb3.f32 FROM test1.tb1,test2.tb3 WHERE f11 IN (SELECT
f31 FROM test2.tb3 WHERE f31=test1.tb1.f13);
+f11	f32
+2	aaaaaaaaaa
+2	bbbbbbbbbb
+2	cccccccccc
+2	dddddddddd
+2	eeeeeeeeee
+2	ffffffffff
+2	gggggggggg
+2	hhhhhhhhhh
+2	iiiiiiiiii
+2	jjjjjjjjjj
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+f11
+15
+6
+UPDATE test1.tb1 set f12 = NULL WHERE f13 = 0;
+UPDATE test2.tb3 set f32 = NULL WHERE f33 = 0;
+UPDATE test3.tb5 set f52 = NULL WHERE f53 = 0;
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f31 IN
(SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL));
+f12
+NULL
+NULL
+NULL
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 IS
NULL UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+f12
+NULL
+NULL
+NULL
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+11	NULL	0	llllllllllllllllllll
+12	bbbbbbbbbb	2	mmmmmmmmmmmmmmmmmmmm
+2	bbbbbbbbbb	2	llllllllllllllllllll
+6	NULL	0	pppppppppppppppppppp
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+f11	f12	f13	f14
+####################################################################################
+no SELECT privilege on of the inner tables
+connection default(user=root)
+REVOKE SELECT ON test2.* FROM testuser1@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+Establish connection con100 (user=testuser1)
+SELECT f11 FROM test1.tb1 WHERE f11 = 1;
+f11
+1
+SELECT f31 FROM test2.tb3 WHERE f31 = 1;
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT f51 FROM test3.tb5 WHERE f51 = 1;
+f51
+1
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL;
+f51
+1
+11
+6
+SELECT f32 FROM test2.tb3 WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f31 IN
(SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 IS
NULL UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+f11	f12	f13	f14
+####################################################################################
+connection default(user=root)
+GRANT SELECT ON test2.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+REVOKE SELECT ON test1.* FROM testuser1@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+no SELECT privilege on the outer table
+Establish connection con100 (user=testuser1)
+SELECT f11 FROM test1.tb1 WHERE f11 = 1;
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT f31 FROM test2.tb3 WHERE f31 = 1;
+f31
+1
+SELECT f51 FROM test3.tb5 WHERE f51 = 1;
+f51
+1
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f31 IN
(SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 IS
NULL UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+####################################################################################
+connection default(user=root)
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser2@'localhost' IDENTIFIED BY 'testpass'
WITH GRANT OPTION;
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser2@'localhost' IDENTIFIED BY 'testpass'
WITH GRANT OPTION;
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser2@'localhost' IDENTIFIED BY 'testpass'
WITH GRANT OPTION;
+REVOKE SELECT,INSERT,UPDATE ON test1.* FROM testuser1@'localhost' IDENTIFIED BY
'testpass';
+REVOKE SELECT,INSERT,UPDATE ON test2.* FROM testuser1@'localhost' IDENTIFIED BY
'testpass';
+REVOKE SELECT,INSERT,UPDATE ON test3.* FROM testuser1@'localhost' IDENTIFIED BY
'testpass';
+####################################################################################
+no privilege
+Establish connection con100 (user=testuser1)
+####################################################################################
+SHOW GRANTS;
+Grants for testuser1@localhost
+GRANT USAGE ON *.* TO 'testuser1'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+ERROR 42000: INSERT command denied to user 'testuser1'@'localhost' for table 'tb5'
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+ERROR 42000: INSERT command denied to user 'testuser1'@'localhost' for table 'tb3'
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+UPDATE test1.tb1 set f12 = NULL WHERE f13 = 0;
+ERROR 42000: UPDATE command denied to user 'testuser1'@'localhost' for table 'tb1'
+UPDATE test2.tb3 set f32 = NULL WHERE f33 = 0;
+ERROR 42000: UPDATE command denied to user 'testuser1'@'localhost' for table 'tb3'
+UPDATE test3.tb5 set f52 = NULL WHERE f53 = 0;
+ERROR 42000: UPDATE command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb1'
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+SHOW GRANTS;
+Grants for testuser2@localhost
+GRANT USAGE ON *.* TO 'testuser2'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+GRANT SELECT, INSERT, UPDATE ON `test1`.* TO 'testuser2'@'localhost' WITH GRANT OPTION
+GRANT SELECT, INSERT, UPDATE ON `test2`.* TO 'testuser2'@'localhost' WITH GRANT OPTION
+GRANT SELECT, INSERT, UPDATE ON `test3`.* TO 'testuser2'@'localhost' WITH GRANT OPTION
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser1@'localhost';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser1@'localhost';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser1@'localhost';
+####################################################################################
+INSERT,UPDATE,SELECT privilege granted by testuser2
+Establish connection con100 (user=testuser1)
+####################################################################################
+SHOW GRANTS;
+Grants for testuser1@localhost
+GRANT USAGE ON *.* TO 'testuser1'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+GRANT SELECT, INSERT, UPDATE ON `test2`.* TO 'testuser1'@'localhost'
+GRANT SELECT, INSERT, UPDATE ON `test1`.* TO 'testuser1'@'localhost'
+GRANT SELECT, INSERT, UPDATE ON `test3`.* TO 'testuser1'@'localhost'
+INSERT INTO test1.tb2 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test3.tb6 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb4 (f41,f42,f43,f44) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+f11
+15
+6
+UPDATE test1.tb2 set f22 = NULL WHERE f23 = 0;
+UPDATE test2.tb4 set f42 = NULL WHERE f43 = 0;
+UPDATE test3.tb6 set f62 = NULL WHERE f63 = 0;
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+11	NULL	0	llllllllllllllllllll
+12	bbbbbbbbbb	2	mmmmmmmmmmmmmmmmmmmm
+2	bbbbbbbbbb	2	llllllllllllllllllll
+6	NULL	0	pppppppppppppppppppp
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+f11	f12	f13	f14
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+REVOKE SELECT ON test3.* FROM testuser1@'localhost';
+####################################################################################
+no SELECT privilege on one of the inner tables
+Establish connection con100 (user=testuser1)
+####################################################################################
+SHOW GRANTS;
+Grants for testuser1@localhost
+GRANT USAGE ON *.* TO 'testuser1'@'localhost' IDENTIFIED BY PASSWORD
'*00E247AC5F9AF26AE0194B41E1E769DEE1429A29'
+GRANT SELECT, INSERT, UPDATE ON `test2`.* TO 'testuser1'@'localhost'
+GRANT SELECT, INSERT, UPDATE ON `test1`.* TO 'testuser1'@'localhost'
+GRANT INSERT, UPDATE ON `test3`.* TO 'testuser1'@'localhost'
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'tb5'
+####################################################################################
+lost SELECT privilege between PREPARE and EXECUTE and got it again
+Establish connection con200 (user=testuser2):
+####################################################################################
+PREPARE subsel1 FROM 'SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT
f34 FROM test2.tb3 WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 =
''pppppppppppppppppppp''))';
+EXECUTE subsel1;
+f11
+15
+6
+####################################################################################
+connection default(user=root with default privileges):
+####################################################################################
+REVOKE SELECT ON test3.* FROM testuser2@'localhost';
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+EXECUTE subsel1;
+ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 'tb5'
+####################################################################################
+connection default(user=root with default privileges):
+####################################################################################
+GRANT SELECT ON test3.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+####################################################################################
+Establish connection con200 (user=testuser2):
+####################################################################################
+EXECUTE subsel1;
+f11
+15
+6
+####################################################################################
+connection default(user=root)
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';

=== added file 'mysql-test/suite/funcs_1/r/ndb_subquery3.result'
--- a/mysql-test/suite/funcs_1/r/ndb_subquery3.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/r/ndb_subquery3.result	2008-06-02 15:01:21 +0000
@@ -0,0 +1,129 @@
+SET autocommit= off;
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+CREATE DATABASE test1;
+USE test1;
+CREATE TABLE tb1 (f11 int, f12 char(10), f13 int, f14 char(20)) ENGINE NDB;
+CREATE TABLE tb2 (f21 int, f22 char(10), f23 int, f24 char(20)) ENGINE NDB;
+INSERT INTO tb1 VALUES ( 1,'aaaaaaaaaa', 0,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES ( 2,'bbbbbbbbbb', 2,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES ( 3,'cccccccccc', 4,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES ( 4,'dddddddddd', 6,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES ( 5,'eeeeeeeeee', 8,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES ( 6,'ffffffffff', 0,'pppppppppppppppppppp');
+INSERT INTO tb1 VALUES ( 7,'gggggggggg', 2,'qqqqqqqqqqqqqqqqqqqq');
+INSERT INTO tb1 VALUES ( 8,'hhhhhhhhhh', 4,'rrrrrrrrrrrrrrrrrrrr');
+INSERT INTO tb1 VALUES ( 9,'iiiiiiiiii', 6,'ssssssssssssssssssss');
+INSERT INTO tb1 VALUES (10,'jjjjjjjjjj', 8,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES (11,'aaaaaaaaaa', 0,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES (12,'bbbbbbbbbb', 2,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES (13,'cccccccccc', 4,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES (14,'dddddddddd', 6,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES (15,'eeeeeeeeee', 8,'pppppppppppppppppppp');
+INSERT INTO tb2 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb2 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb2 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb2 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb2 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test2;
+USE test2;
+CREATE TABLE tb3 (f31 int, f32 char(10), f33 int, f34 char(20)) ENGINE NDB;
+CREATE TABLE tb4 (f41 int, f42 char(10), f43 int, f44 char(20)) ENGINE NDB;
+INSERT INTO tb3 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb3 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb3 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb3 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb3 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb4 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb4 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb4 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb4 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb4 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test3;
+USE test3;
+CREATE TABLE tb5 (f51 int, f52 char(10), f53 int, f54 char(20)) ENGINE NDB;
+CREATE TABLE tb6 (f61 int, f62 char(10), f63 int, f64 char(20)) ENGINE NDB;
+INSERT INTO tb5 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb5 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb5 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb5 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb5 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb6 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb6 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb6 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb6 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb6 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+USE information_schema;
+####################################################################################
+1.1.2 Create 3 user
+DROP USER testuser1@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser1'@'localhost'
+DROP USER testuser2@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser2'@'localhost'
+DROP USER testuser3@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser3'@'localhost'
+CREATE USER testuser1@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser3@'localhost';
+SET PASSWORD FOR testuser3@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+COMMIT;
+############# LOCK/UNLOCK test
+####################################################################################
+Establish connection con200 (user=testuser2)
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f33 = 0 UNION 
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+Establish connection con100 (user=testuser1)
+LOCK TABLE test1.tb1 READ,test2.tb3 WRITE,test3.tb5 WRITE;
+####################################################################################
+connection con200
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f33 = 0 UNION 
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+####################################################################################
+connection default
+# Sleep some time
+SHOW FULL PROCESSLIST;
+Id	User	Host	db	Command	Time	State	Info
+<ID>	system user	<HOST>		Daemon	<TIME>	Waiting for event from
ndbcluster	NULL
+<ID>	root	<HOST>	test	Query	<TIME>	NULL	SHOW FULL PROCESSLIST
+<ID>	root	<HOST>	information_schema	Sleep	<TIME>		NULL
+<ID>	testuser2	<HOST>	test	Query	<TIME>	Locked	SELECT * FROM test1.tb1
WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f33 = 0 UNION 
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0)
+<ID>	testuser1	<HOST>	test	Sleep	<TIME>		NULL
+####################################################################################
+connection con100;
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+UNLOCK TABLES;
+COMMIT;
+####################################################################################
+connection con200;
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+11	aaaaaaaaaa	0	llllllllllllllllllll
+6	ffffffffff	0	pppppppppppppppppppp
+connection default;
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';

=== added file 'mysql-test/suite/funcs_1/r/ndb_subquery4.result'
--- a/mysql-test/suite/funcs_1/r/ndb_subquery4.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/r/ndb_subquery4.result	2008-06-02 15:01:21 +0000
@@ -0,0 +1,151 @@
+SET autocommit= off;
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+CREATE DATABASE test1;
+USE test1;
+CREATE TABLE tb1 (f11 int primary key, f12 char(10), f13 int, f14 char(20)) ENGINE NDB;
+CREATE TABLE tb2 (f21 int primary key, f22 char(10), f23 int, f24 char(20)) ENGINE NDB;
+INSERT INTO tb1 VALUES ( 1,'aaaaaaaaaa', 0,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES ( 2,'bbbbbbbbbb', 2,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES ( 3,'cccccccccc', 4,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES ( 4,'dddddddddd', 6,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES ( 5,'eeeeeeeeee', 8,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES ( 6,'ffffffffff', 0,'pppppppppppppppppppp');
+INSERT INTO tb1 VALUES ( 7,'gggggggggg', 2,'qqqqqqqqqqqqqqqqqqqq');
+INSERT INTO tb1 VALUES ( 8,'hhhhhhhhhh', 4,'rrrrrrrrrrrrrrrrrrrr');
+INSERT INTO tb1 VALUES ( 9,'iiiiiiiiii', 6,'ssssssssssssssssssss');
+INSERT INTO tb1 VALUES (10,'jjjjjjjjjj', 8,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES (11,'aaaaaaaaaa', 0,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES (12,'bbbbbbbbbb', 2,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES (13,'cccccccccc', 4,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES (14,'dddddddddd', 6,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES (15,'eeeeeeeeee', 8,'pppppppppppppppppppp');
+INSERT INTO tb2 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb2 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb2 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb2 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb2 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test2;
+USE test2;
+CREATE TABLE tb3 (f31 int primary key, f32 char(10), f33 int, f34 char(20)) ENGINE NDB;
+CREATE TABLE tb4 (f41 int primary key, f42 char(10), f43 int, f44 char(20)) ENGINE NDB;
+INSERT INTO tb3 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb3 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb3 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb3 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb3 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb4 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb4 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb4 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb4 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb4 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test3;
+USE test3;
+CREATE TABLE tb5 (f51 int primary key, f52 char(10), f53 int, f54 char(20)) ENGINE NDB;
+CREATE TABLE tb6 (f61 int primary key, f62 char(10), f63 int, f64 char(20)) ENGINE NDB;
+INSERT INTO tb5 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb5 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb5 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb5 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb5 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb6 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb6 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb6 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb6 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb6 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+USE information_schema;
+####################################################################################
+1.1.2 Create 3 user
+DROP USER testuser1@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser1'@'localhost'
+DROP USER testuser2@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser2'@'localhost'
+DROP USER testuser3@'localhost';
+ERROR HY000: Operation DROP USER failed for 'testuser3'@'localhost'
+CREATE USER testuser1@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+CREATE USER testuser3@'localhost';
+SET PASSWORD FOR testuser3@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+COMMIT;
+SELECT @@global.tx_isolation;
+@@global.tx_isolation
+REPEATABLE-READ
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+############# SELECT ... FOR UPDATE test
+####################################################################################
+Establish connection con200 (user=testuser2)
+SET autocommit= off;
+SELECT @@global.tx_isolation;
+@@global.tx_isolation
+REPEATABLE-READ
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+Establish connection con100 (user=testuser1)
+SET autocommit= off;
+SELECT @@global.tx_isolation;
+@@global.tx_isolation
+REPEATABLE-READ
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0)
FOR UPDATE;
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+connection con200
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+####################################################################################
+connection con100;
+UPDATE test1.tb1 SET f12 = NULL WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE
test3.tb5.f53 = 0);
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+####################################################################################
+connection con200;
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	aaaaaaaaaa	0	kkkkkkkkkkkkkkkkkkkk
+COMMIT;
+####################################################################################
+connection con100;
+COMMIT;
+####################################################################################
+connection con200;
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+COMMIT;
+connection default;
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+f11	f12	f13	f14
+1	NULL	0	kkkkkkkkkkkkkkkkkkkk
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';

=== added directory 'mysql-test/suite/funcs_1/subquery'
=== added file 'mysql-test/suite/funcs_1/subquery/subquery1.inc'
--- a/mysql-test/suite/funcs_1/subquery/subquery1.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/subquery/subquery1.inc	2008-06-02 15:01:21 +0000
@@ -0,0 +1,65 @@
+--echo
####################################################################################
+--echo 1.1.2 Create two user
+--echo
####################################################################################
+# access to info tables as normal user
+--disable_abort_on_error
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+--enable_abort_on_error
+CREATE USER testuser1@'localhost';
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+GRANT ALL ON *.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+
+USE information_schema;
+--echo
####################################################################################
+--echo Establish connection con100 (user=testuser1)
+connect (con100,localhost,testuser1,testpass,test);
+--echo
####################################################################################
+SELECT f17 FROM test.tb1;
+SELECT f59 FROM test2.tb2;
+INSERT INTO test.tb1.f17 VALUES (SELECT f59 FROM test2.tb2  WHERE f59=1);
+SELECT f17 FROM test.tb1,test.tb2 WHERE f17 = (SELECT f59 FROM test2.tb2 WHERE
f59=test.tb2.f59);
+SELECT f17 FROM test.tb1 WHERE (SELECT f59 FROM test2.tb2 WHERE f59=1)=1;
+disconnect con100;
+--echo connection default(user=root)
+connection default;
+REVOKE SELECT ON test2.* FROM testuser1@'localhost' IDENTIFIED BY 'testpass';
+--echo Establish connection con100 (user=testuser1)
+connect (con100,localhost,testuser1,testpass,test);
+SELECT f1 FROM test.tb1;
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT f1 FROM test2.tb1;
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT f1 FROM test.tb1 WHERE f1 = (SELECT f1 FROM test2.tb1 WHERE f1='!');
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT f1 FROM test.tb1 WHERE (SELECT f1 FROM test2.tb1 WHERE f1 = '!') = '!';
+disconnect con100;
+--echo connection default(user=root)
+connection default;
+GRANT SELECT ON test2.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+REVOKE SELECT ON test.* FROM testuser1@'localhost' IDENTIFIED BY 'testpass';
+--echo Establish connection con100 (user=testuser1)
+connect (con100,localhost,testuser1,testpass,test);
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT f1 FROM test.tb1;
+SELECT f1 FROM test2.tb1;
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT f1 FROM test.tb1 WHERE f1 = (SELECT f1 FROM test2.tb1 WHERE f1='!');
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT f1 FROM test.tb1 WHERE (SELECT f1 FROM test2.tb1 WHERE f1 = '!') = '!';
+
+--echo
####################################################################################
+--echo Establish connection con200 (user=testuser2):
+connect (con200,localhost,testuser2,testpass,test2);
+--echo
####################################################################################
+
+
+--echo
####################################################################################
+--echo 2 connection default(user=root with default privileges):
+--echo   
+--echo
####################################################################################
+connection default;

=== added file 'mysql-test/suite/funcs_1/subquery/subquery2.inc'
--- a/mysql-test/suite/funcs_1/subquery/subquery2.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/subquery/subquery2.inc	2008-06-02 15:01:21 +0000
@@ -0,0 +1,317 @@
+########## suite/funcs_1/subquery/subquery2.inc ########################
+#                                                                      #
+# Testing of privileges around                                         #
+#     Insert, Update, Select with subquery.                            #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-05 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+
+--disable_warnings
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+--enable_warnings
+CREATE DATABASE test1;
+USE test1;
+eval CREATE TABLE tb1 (f11 int $primarykey, f12 char(10), f13 int, f14 char(20)) ENGINE
$engine;
+eval CREATE TABLE tb2 (f21 int $primarykey, f22 char(10), f23 int, f24 char(20)) ENGINE
$engine;
+INSERT INTO tb1 VALUES ( 1,'aaaaaaaaaa', 0,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES ( 2,'bbbbbbbbbb', 2,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES ( 3,'cccccccccc', 4,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES ( 4,'dddddddddd', 6,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES ( 5,'eeeeeeeeee', 8,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES ( 6,'ffffffffff', 0,'pppppppppppppppppppp');
+INSERT INTO tb1 VALUES ( 7,'gggggggggg', 2,'qqqqqqqqqqqqqqqqqqqq');
+INSERT INTO tb1 VALUES ( 8,'hhhhhhhhhh', 4,'rrrrrrrrrrrrrrrrrrrr');
+INSERT INTO tb1 VALUES ( 9,'iiiiiiiiii', 6,'ssssssssssssssssssss');
+INSERT INTO tb1 VALUES (10,'jjjjjjjjjj', 8,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES (11,'aaaaaaaaaa', 0,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES (12,'bbbbbbbbbb', 2,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES (13,'cccccccccc', 4,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES (14,'dddddddddd', 6,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES (15,'eeeeeeeeee', 8,'pppppppppppppppppppp');
+#
+INSERT INTO tb2 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb2 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb2 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb2 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb2 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+CREATE DATABASE test2;
+USE test2;
+eval CREATE TABLE tb3 (f31 int $primarykey, f32 char(10), f33 int, f34 char(20)) ENGINE
$engine;
+eval CREATE TABLE tb4 (f41 int $primarykey, f42 char(10), f43 int, f44 char(20)) ENGINE
$engine;
+INSERT INTO tb3 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb3 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb3 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb3 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb3 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+#
+INSERT INTO tb4 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb4 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb4 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb4 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb4 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+CREATE DATABASE test3;
+USE test3;
+eval CREATE TABLE tb5 (f51 int $primarykey, f52 char(10), f53 int, f54 char(20)) ENGINE
$engine;
+eval CREATE TABLE tb6 (f61 int $primarykey, f62 char(10), f63 int, f64 char(20)) ENGINE
$engine;
+INSERT INTO tb5 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb5 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb5 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb5 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb5 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+#
+INSERT INTO tb6 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb6 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb6 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb6 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb6 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+USE information_schema;
+--echo
####################################################################################
+--echo 1.1.2 Create 3 user
+--echo
####################################################################################
+# access to info tables as normal user
+--disable_abort_on_error
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';
+--enable_abort_on_error
+CREATE USER testuser1@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+
+CREATE USER testuser3@'localhost';
+SET PASSWORD FOR testuser3@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser3@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser3@'localhost' IDENTIFIED BY 'testpass';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser3@'localhost' IDENTIFIED BY 'testpass';
+
+--echo
####################################################################################
+--echo all subqueries must succeed
+--echo Establish connection con100 (user=testuser1)
+connect (con100,localhost,testuser1,testpass,);
+--echo
####################################################################################
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+	SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+#only 1 row with f31=2 is the result of the inner select.
+--sorted_result
+SELECT test1.tb1.f11,test2.tb3.f32 FROM test1.tb1,test2.tb3 WHERE f11 IN (SELECT f31 FROM
test2.tb3 WHERE f31=test1.tb1.f13);
+--sorted_result
+SELECT DISTINCT test1.tb1.f11,test2.tb3.f32 FROM test1.tb1,test2.tb3 WHERE f11 IN (SELECT
f31 FROM test2.tb3 WHERE f31=test1.tb1.f13);
+--sorted_result
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+# to have NULL values in the tables.
+UPDATE test1.tb1 set f12 = NULL WHERE f13 = 0; 
+UPDATE test2.tb3 set f32 = NULL WHERE f33 = 0; 
+UPDATE test3.tb5 set f52 = NULL WHERE f53 = 0; 
+# result set consists of 3 NULL values
+--sorted_result
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f31 IN
(SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL));
+# result set consists of 3 NULL values
+--sorted_result
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 IS
NULL UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+# result set consists of 3 NULL values and 2 with 'bbbbbbbbbb'
+--sorted_result
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+# result set is empty
+--sorted_result
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+disconnect con100;
+--echo
####################################################################################
+--echo no SELECT privilege on of the inner tables
+--echo connection default(user=root)
+connection default;
+REVOKE SELECT ON test2.* FROM testuser1@'localhost' IDENTIFIED BY 'testpass';
+--echo
####################################################################################
+--echo Establish connection con100 (user=testuser1)
+connect (con100,localhost,testuser1,testpass,);
+--sorted_result
+SELECT f11 FROM test1.tb1 WHERE f11 = 1;
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT f31 FROM test2.tb3 WHERE f31 = 1;
+--sorted_result
+SELECT f51 FROM test3.tb5 WHERE f51 = 1;
+--sorted_result
+SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL;
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT f32 FROM test2.tb3 WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f31 IN
(SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL));
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 IS
NULL UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+--sorted_result
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+disconnect con100;
+--echo
####################################################################################
+--echo connection default(user=root)
+connection default;
+GRANT SELECT ON test2.* TO testuser1@'localhost' IDENTIFIED BY 'testpass';
+REVOKE SELECT ON test1.* FROM testuser1@'localhost' IDENTIFIED BY 'testpass';
+--echo
####################################################################################
+--echo no SELECT privilege on the outer table
+--echo Establish connection con100 (user=testuser1)
+connect (con100,localhost,testuser1,testpass,);
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT f11 FROM test1.tb1 WHERE f11 = 1;
+--sorted_result
+SELECT f31 FROM test2.tb3 WHERE f31 = 1;
+--sorted_result
+SELECT f51 FROM test3.tb5 WHERE f51 = 1;
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f31 IN
(SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL));
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT test1.tb1.f12 FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 IS
NULL UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+disconnect con100;
+--echo
####################################################################################
+--echo connection default(user=root)
+connection default;
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser2@'localhost' IDENTIFIED BY 'testpass'
WITH GRANT OPTION;
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser2@'localhost' IDENTIFIED BY 'testpass'
WITH GRANT OPTION;
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser2@'localhost' IDENTIFIED BY 'testpass'
WITH GRANT OPTION;
+REVOKE SELECT,INSERT,UPDATE ON test1.* FROM testuser1@'localhost' IDENTIFIED BY
'testpass';
+REVOKE SELECT,INSERT,UPDATE ON test2.* FROM testuser1@'localhost' IDENTIFIED BY
'testpass';
+REVOKE SELECT,INSERT,UPDATE ON test3.* FROM testuser1@'localhost' IDENTIFIED BY
'testpass';
+--echo
####################################################################################
+--echo no privilege
+--echo Establish connection con100 (user=testuser1)
+connect (con100,localhost,testuser1,testpass,);
+--echo
####################################################################################
+SHOW GRANTS;
+--error ER_TABLEACCESS_DENIED_ERROR
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+--error ER_TABLEACCESS_DENIED_ERROR
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+	SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+--error ER_TABLEACCESS_DENIED_ERROR
+UPDATE test1.tb1 set f12 = NULL WHERE f13 = 0; 
+--error ER_TABLEACCESS_DENIED_ERROR
+UPDATE test2.tb3 set f32 = NULL WHERE f33 = 0; 
+--error ER_TABLEACCESS_DENIED_ERROR
+UPDATE test3.tb5 set f52 = NULL WHERE f53 = 0; 
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+disconnect con100;
+--echo
####################################################################################
+--echo Establish connection con200 (user=testuser2):
+connect (con200,localhost,testuser2,testpass,);
+--echo
####################################################################################
+SHOW GRANTS;
+GRANT SELECT,INSERT,UPDATE ON test1.* TO testuser1@'localhost';
+GRANT SELECT,INSERT,UPDATE ON test2.* TO testuser1@'localhost';
+GRANT SELECT,INSERT,UPDATE ON test3.* TO testuser1@'localhost';
+
+--echo
####################################################################################
+--echo INSERT,UPDATE,SELECT privilege granted by testuser2
+--echo Establish connection con100 (user=testuser1)
+connect (con100,localhost,testuser1,testpass,);
+--echo
####################################################################################
+SHOW GRANTS;
+INSERT INTO test1.tb2 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test3.tb6 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb4 (f41,f42,f43,f44) 
+	SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+--sorted_result
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+UPDATE test1.tb2 set f22 = NULL WHERE f23 = 0; 
+UPDATE test2.tb4 set f42 = NULL WHERE f43 = 0; 
+UPDATE test3.tb6 set f62 = NULL WHERE f63 = 0; 
+--sorted_result
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+# empty result set
+--sorted_result
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+disconnect con100;
+--echo
####################################################################################
+--echo Establish connection con200 (user=testuser2):
+connection con200;
+--echo
####################################################################################
+REVOKE SELECT ON test3.* FROM testuser1@'localhost';
+
+--echo
####################################################################################
+--echo no SELECT privilege on one of the inner tables
+--echo Establish connection con100 (user=testuser1)
+connect (con100,localhost,testuser1,testpass,);
+--echo
####################################################################################
+SHOW GRANTS;
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+--error ER_TABLEACCESS_DENIED_ERROR
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+	SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT f34 FROM test2.tb3
WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 = 'pppppppppppppppppppp'));
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f32 = 'bbbbbbbbbb'
UNION SELECT f51 FROM test3.tb5 WHERE test3.tb5.f52 IS NULL);
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT * FROM test1.tb1 WHERE f12 IN (SELECT f52 FROM test3.tb5 WHERE test3.tb5.f52 IS
NULL);
+disconnect con100;
+disconnect con200;
+--echo
####################################################################################
+--echo lost SELECT privilege between PREPARE and EXECUTE and got it again
+--echo Establish connection con200 (user=testuser2):
+connect (con200,localhost,testuser2,testpass,);
+--echo
####################################################################################
+PREPARE subsel1 FROM 'SELECT test1.tb1.f11 FROM test1.tb1 WHERE f14 IN (SELECT  DISTINCT
f34 FROM test2.tb3 WHERE f31 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f54 =
''pppppppppppppppppppp''))';
+--sorted_result
+EXECUTE subsel1;
+
+--echo
####################################################################################
+--echo connection default(user=root with default privileges):
+--echo
####################################################################################
+connection default;
+REVOKE SELECT ON test3.* FROM testuser2@'localhost';
+
+--echo
####################################################################################
+--echo Establish connection con200 (user=testuser2):
+connection con200;
+--echo
####################################################################################
+--error ER_TABLEACCESS_DENIED_ERROR
+EXECUTE subsel1;
+
+--echo
####################################################################################
+--echo connection default(user=root with default privileges):
+--echo
####################################################################################
+connection default;
+GRANT SELECT ON test3.* TO testuser2@'localhost' IDENTIFIED BY 'testpass';
+
+--echo
####################################################################################
+--echo Establish connection con200 (user=testuser2):
+connection con200;
+--echo
####################################################################################
+--sorted_result
+EXECUTE subsel1;
+disconnect con200;
+--echo
####################################################################################
+--echo connection default(user=root)
+connection default;
+--disable_warnings
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+--enable_warnings
+--disable_abort_on_error
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';
+--enable_abort_on_error

=== added file 'mysql-test/suite/funcs_1/subquery/subquery3.inc'
--- a/mysql-test/suite/funcs_1/subquery/subquery3.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/subquery/subquery3.inc	2008-06-02 15:01:21 +0000
@@ -0,0 +1,166 @@
+########## suite/funcs_1/subquery/subquery3.inc ########################
+#                                                                      #
+# Testing of subquery in transactions (LOCK/UNLOCK.                    #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-11 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+SET autocommit= off;
+--disable_warnings
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+--enable_warnings
+CREATE DATABASE test1;
+USE test1;
+eval CREATE TABLE tb1 (f11 int, f12 char(10), f13 int, f14 char(20)) ENGINE $engine;
+eval CREATE TABLE tb2 (f21 int, f22 char(10), f23 int, f24 char(20)) ENGINE $engine;
+INSERT INTO tb1 VALUES ( 1,'aaaaaaaaaa', 0,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES ( 2,'bbbbbbbbbb', 2,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES ( 3,'cccccccccc', 4,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES ( 4,'dddddddddd', 6,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES ( 5,'eeeeeeeeee', 8,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES ( 6,'ffffffffff', 0,'pppppppppppppppppppp');
+INSERT INTO tb1 VALUES ( 7,'gggggggggg', 2,'qqqqqqqqqqqqqqqqqqqq');
+INSERT INTO tb1 VALUES ( 8,'hhhhhhhhhh', 4,'rrrrrrrrrrrrrrrrrrrr');
+INSERT INTO tb1 VALUES ( 9,'iiiiiiiiii', 6,'ssssssssssssssssssss');
+INSERT INTO tb1 VALUES (10,'jjjjjjjjjj', 8,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES (11,'aaaaaaaaaa', 0,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES (12,'bbbbbbbbbb', 2,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES (13,'cccccccccc', 4,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES (14,'dddddddddd', 6,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES (15,'eeeeeeeeee', 8,'pppppppppppppppppppp');
+#
+INSERT INTO tb2 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb2 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb2 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb2 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb2 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test2;
+USE test2;
+eval CREATE TABLE tb3 (f31 int, f32 char(10), f33 int, f34 char(20)) ENGINE $engine;
+eval CREATE TABLE tb4 (f41 int, f42 char(10), f43 int, f44 char(20)) ENGINE $engine;
+INSERT INTO tb3 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb3 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb3 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb3 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb3 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+#
+INSERT INTO tb4 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb4 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb4 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb4 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb4 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test3;
+USE test3;
+eval CREATE TABLE tb5 (f51 int, f52 char(10), f53 int, f54 char(20)) ENGINE $engine;
+eval CREATE TABLE tb6 (f61 int, f62 char(10), f63 int, f64 char(20)) ENGINE $engine;
+INSERT INTO tb5 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb5 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb5 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb5 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb5 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+#
+INSERT INTO tb6 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb6 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb6 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb6 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb6 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+USE information_schema;
+--echo
####################################################################################
+--echo 1.1.2 Create 3 user
+--disable_abort_on_error
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';
+--enable_abort_on_error
+CREATE USER testuser1@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+
+CREATE USER testuser3@'localhost';
+SET PASSWORD FOR testuser3@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+COMMIT;
+--echo ############# LOCK/UNLOCK test
+--echo
####################################################################################
+--echo Establish connection con200 (user=testuser2)
+connect (con200,localhost,testuser2,testpass,);
+# See result of subselects before one of the tables will be locked.
+--sorted_result
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f33 = 0 UNION 
+	SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+--echo
####################################################################################
+--echo Establish connection con100 (user=testuser1)
+connect (con100,localhost,testuser1,testpass,);
+# Need to lock all used tables for further inserts.
+LOCK TABLE test1.tb1 READ,test2.tb3 WRITE,test3.tb5 WRITE;
+--echo
####################################################################################
+--echo connection con200
+connection con200;
+# one of the tables is locked, so select has to wait.
+send SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f31 FROM test2.tb3 WHERE f33 = 0 UNION 
+	SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+--echo
####################################################################################
+--echo connection default
+connection default;
+# check if the select is waiting (state must be "locked" by other select.
+--echo # Sleep some time
+# The command must be at some time in work by the server.
+# So poll till INFO is no more NULL.
+let $run= 10;
+while ($run)
+{
+   dec $run;
+   if (`SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST
+        WHERE INFO IS NOT NULL`)
+   {
+      let $run= 0;
+   }
+   --real_sleep 0.2
+}
+--replace_column 1 <ID> 3 <HOST> 6 <TIME>
+SHOW FULL PROCESSLIST;
+--echo
####################################################################################
+--echo connection con100;
+connection con100;
+# do inserts and unlock table.
+INSERT INTO test3.tb5 SELECT * FROM test1.tb1 WHERE f11 > 5;
+INSERT INTO test2.tb3 (f31,f32,f33,f34) 
+	SELECT f11,f12,f53,f54 FROM test1.tb1 JOIN test3.tb5 WHERE f11 > 5 AND f11=f51;
+UNLOCK TABLES;
+COMMIT;
+disconnect con100;
+--echo
####################################################################################
+--echo connection con200;
+connection con200;
+# get the result of the sent select, which shall contain the inserted rows.
+--sorted_result
+reap;
+--echo connection default;
+connection default;
+--disable_warnings
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+--enable_warnings
+--disable_abort_on_error
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';
+--enable_abort_on_error

=== added file 'mysql-test/suite/funcs_1/subquery/subquery4.inc'
--- a/mysql-test/suite/funcs_1/subquery/subquery4.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/subquery/subquery4.inc	2008-06-02 15:01:21 +0000
@@ -0,0 +1,166 @@
+########## suite/funcs_1/subquery/subquery4.inc ########################
+#                                                                      #
+# Testing of subquery in transactions with row locking/for update.     #
+#                                                                      #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-11 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+SET autocommit= off;
+--disable_warnings
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+--enable_warnings
+CREATE DATABASE test1;
+USE test1;
+eval CREATE TABLE tb1 (f11 int $primarykey, f12 char(10), f13 int, f14 char(20)) ENGINE
$engine;
+eval CREATE TABLE tb2 (f21 int $primarykey, f22 char(10), f23 int, f24 char(20)) ENGINE
$engine;
+INSERT INTO tb1 VALUES ( 1,'aaaaaaaaaa', 0,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES ( 2,'bbbbbbbbbb', 2,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES ( 3,'cccccccccc', 4,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES ( 4,'dddddddddd', 6,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES ( 5,'eeeeeeeeee', 8,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES ( 6,'ffffffffff', 0,'pppppppppppppppppppp');
+INSERT INTO tb1 VALUES ( 7,'gggggggggg', 2,'qqqqqqqqqqqqqqqqqqqq');
+INSERT INTO tb1 VALUES ( 8,'hhhhhhhhhh', 4,'rrrrrrrrrrrrrrrrrrrr');
+INSERT INTO tb1 VALUES ( 9,'iiiiiiiiii', 6,'ssssssssssssssssssss');
+INSERT INTO tb1 VALUES (10,'jjjjjjjjjj', 8,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb1 VALUES (11,'aaaaaaaaaa', 0,'llllllllllllllllllll');
+INSERT INTO tb1 VALUES (12,'bbbbbbbbbb', 2,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb1 VALUES (13,'cccccccccc', 4,'nnnnnnnnnnnnnnnnnnnn');
+INSERT INTO tb1 VALUES (14,'dddddddddd', 6,'oooooooooooooooooooo');
+INSERT INTO tb1 VALUES (15,'eeeeeeeeee', 8,'pppppppppppppppppppp');
+#
+INSERT INTO tb2 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb2 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb2 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb2 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb2 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test2;
+USE test2;
+eval CREATE TABLE tb3 (f31 int $primarykey, f32 char(10), f33 int, f34 char(20)) ENGINE
$engine;
+eval CREATE TABLE tb4 (f41 int $primarykey, f42 char(10), f43 int, f44 char(20)) ENGINE
$engine;
+INSERT INTO tb3 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb3 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb3 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb3 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb3 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+#
+INSERT INTO tb4 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb4 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb4 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb4 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb4 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+CREATE DATABASE test3;
+USE test3;
+eval CREATE TABLE tb5 (f51 int $primarykey, f52 char(10), f53 int, f54 char(20)) ENGINE
$engine;
+eval CREATE TABLE tb6 (f61 int $primarykey, f62 char(10), f63 int, f64 char(20)) ENGINE
$engine;
+INSERT INTO tb5 VALUES ( 1,'aaaaaaaaaa', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb5 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb5 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb5 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb5 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+#
+INSERT INTO tb6 VALUES ( 1,'ffffffffff', 0,'jjjjjjjjjjjjjjjjjjjj');
+INSERT INTO tb6 VALUES ( 2,'bbbbbbbbbb', 2,'kkkkkkkkkkkkkkkkkkkk');
+INSERT INTO tb6 VALUES ( 3,'cccccccccc', 4,'llllllllllllllllllll');
+INSERT INTO tb6 VALUES ( 4,'dddddddddd', 6,'mmmmmmmmmmmmmmmmmmmm');
+INSERT INTO tb6 VALUES ( 5,'eeeeeeeeee', 8,'nnnnnnnnnnnnnnnnnnnn');
+COMMIT;
+USE information_schema;
+--echo
####################################################################################
+--echo 1.1.2 Create 3 user
+--disable_abort_on_error
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';
+--enable_abort_on_error
+CREATE USER testuser1@'localhost';
+SET PASSWORD FOR testuser1@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser1@'localhost' IDENTIFIED BY
'testpass';
+
+CREATE USER testuser2@'localhost';
+SET PASSWORD FOR testuser2@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser2@'localhost' IDENTIFIED BY
'testpass';
+
+CREATE USER testuser3@'localhost';
+SET PASSWORD FOR testuser3@'localhost' = PASSWORD('testpass');
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test1.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test2.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+GRANT SELECT,INSERT,UPDATE,LOCK TABLES ON test3.* TO testuser3@'localhost' IDENTIFIED BY
'testpass';
+COMMIT;
+#SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+#SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
+#SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+#SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+SELECT @@global.tx_isolation;
+SELECT @@tx_isolation;
+--echo ############# SELECT ... FOR UPDATE test
+--echo
####################################################################################
+--echo Establish connection con200 (user=testuser2)
+connect (con200,localhost,testuser2,testpass,);
+SET autocommit= off;
+SELECT @@global.tx_isolation;
+SELECT @@tx_isolation;
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+--echo
####################################################################################
+--echo Establish connection con100 (user=testuser1)
+connect (con100,localhost,testuser1,testpass,);
+SET autocommit= off;
+SELECT @@global.tx_isolation;
+SELECT @@tx_isolation;
+# Lock row for further updates.
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0)
FOR UPDATE;
+--echo
####################################################################################
+--echo connection con200
+connection con200;
+send SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53
= 0);
+--echo
####################################################################################
+--echo connection con100;
+connection con100;
+--real_sleep 0.3
+UPDATE test1.tb1 SET f12 = NULL WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE
test3.tb5.f53 = 0);
+# see the result of update.
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+--echo
####################################################################################
+--echo connection con200;
+connection con200;
+# see the result before update, as update not yet commited.
+reap;
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+COMMIT;
+--echo
####################################################################################
+--echo connection con100;
+connection con100;
+COMMIT;
+--echo
####################################################################################
+--echo connection con200;
+connection con200;
+# see now the result after the commited update.
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+COMMIT;
+disconnect con200;
+disconnect con100;
+--echo connection default;
+connection default;
+# every user sees now the result after the commited update.
+SELECT * FROM test1.tb1 WHERE f11 IN (SELECT f51 FROM test3.tb5 WHERE test3.tb5.f53 = 0);
+--disable_warnings
+DROP DATABASE IF EXISTS test1;
+DROP DATABASE IF EXISTS test2;
+DROP DATABASE IF EXISTS test3;
+--enable_warnings
+--disable_abort_on_error
+DROP USER testuser1@'localhost';
+DROP USER testuser2@'localhost';
+DROP USER testuser3@'localhost';
+--enable_abort_on_error

=== added file 'mysql-test/suite/funcs_1/t/csv_subquery2.test'
--- a/mysql-test/suite/funcs_1/t/csv_subquery2.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/t/csv_subquery2.test	2008-06-02 15:01:21 +0000
@@ -0,0 +1,17 @@
+########## suite/funcs_1/t/csv_subquery2.test #######################
+#                                                                      #
+# Testing of privileges around                                         #
+#     Insert, Update, Select with subquery.                            #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-05 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+# CSV tables should be used
+#
+--source include/have_csv.inc
+# Set $engine_type
+let $engine = CSV;
+--source suite/funcs_1/subquery/subquery2.inc
+

=== added file 'mysql-test/suite/funcs_1/t/csv_subquery3.test'
--- a/mysql-test/suite/funcs_1/t/csv_subquery3.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/t/csv_subquery3.test	2008-06-02 15:01:21 +0000
@@ -0,0 +1,16 @@
+########## suite/funcs_1/t/csv_subquery3.test ##########################
+#                                                                      #
+# Testing transactions using LOCK/UNLOCK with subquery.                #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-05 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+# MyISAM tables should be used
+#
+# Set $engine_type
+--source include/have_csv.inc
+let $engine = csv;
+--source suite/funcs_1/subquery/subquery3.inc
+

=== added file 'mysql-test/suite/funcs_1/t/csv_subquery4.test'
--- a/mysql-test/suite/funcs_1/t/csv_subquery4.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/t/csv_subquery4.test	2008-06-02 15:01:21 +0000
@@ -0,0 +1,17 @@
+########## suite/funcs_1/t/csv_subquery2.test #######################
+#                                                                      #
+# Testing of subqueries in transactions (...for update).               #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-13 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+# CSV tables should be used
+#
+--source include/have_csv.inc
+# Set $engine_type
+let $engine = csv;
+let $primarykey =;
+--source suite/funcs_1/subquery/subquery4.inc
+

=== added file 'mysql-test/suite/funcs_1/t/innodb_subquery2.test'
--- a/mysql-test/suite/funcs_1/t/innodb_subquery2.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/t/innodb_subquery2.test	2008-06-02 15:01:21 +0000
@@ -0,0 +1,17 @@
+########## suite/funcs_1/t/innodb_subquery2.test #######################
+#                                                                      #
+# Testing of privileges around                                         #
+#     Insert, Update, Select with subquery.                            #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-05 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+# InnoDB tables should be used
+#
+--source include/have_innodb.inc
+# Set $engine_type
+let $engine = InnoDB;
+--source suite/funcs_1/subquery/subquery2.inc
+

=== added file 'mysql-test/suite/funcs_1/t/innodb_subquery3.test'
--- a/mysql-test/suite/funcs_1/t/innodb_subquery3.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/t/innodb_subquery3.test	2008-06-02 15:01:21 +0000
@@ -0,0 +1,16 @@
+########## suite/funcs_1/t/myisam_subquery2.test #######################
+#                                                                      #
+# Testing transactions using LOCK/UNLOCK with subquery.                #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-05 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+# InnoDB tables should be used
+#
+--source include/have_innodb.inc
+# Set $engine_type
+let $engine = InnoDB;
+--source suite/funcs_1/subquery/subquery3.inc
+

=== added file 'mysql-test/suite/funcs_1/t/innodb_subquery4.test'
--- a/mysql-test/suite/funcs_1/t/innodb_subquery4.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/t/innodb_subquery4.test	2008-06-02 15:01:21 +0000
@@ -0,0 +1,17 @@
+########## suite/funcs_1/t/myisam_subquery2.test #######################
+#                                                                      #
+# Testing of subqueries in transactions (...for update).               #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-13 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+# InnoDB tables should be used
+#
+--source include/have_innodb.inc
+# Set $engine_type
+let $engine = InnoDB;
+let $primarykey = primary key;
+--source suite/funcs_1/subquery/subquery4.inc
+

=== added file 'mysql-test/suite/funcs_1/t/memory_subquery2.test'
--- a/mysql-test/suite/funcs_1/t/memory_subquery2.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/t/memory_subquery2.test	2008-06-02 15:01:21 +0000
@@ -0,0 +1,16 @@
+########## suite/funcs_1/t/memory_subquery2.test #######################
+#                                                                      #
+# Testing of privileges around                                         #
+#     Insert, Update, Select with subquery.                            #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-05 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+# MyISAM tables should be used
+#
+# Set $engine_type
+let $engine = memory;
+--source suite/funcs_1/subquery/subquery2.inc
+

=== added file 'mysql-test/suite/funcs_1/t/memory_subquery3.test'
--- a/mysql-test/suite/funcs_1/t/memory_subquery3.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/t/memory_subquery3.test	2008-06-02 15:01:21 +0000
@@ -0,0 +1,15 @@
+########## suite/funcs_1/t/memory_subquery3.test #######################
+#                                                                      #
+# Testing transactions using LOCK/UNLOCK with subquery.                #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-05 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+# MyISAM tables should be used
+#
+# Set $engine_type
+let $engine = memory;
+--source suite/funcs_1/subquery/subquery3.inc
+

=== added file 'mysql-test/suite/funcs_1/t/memory_subquery4.test'
--- a/mysql-test/suite/funcs_1/t/memory_subquery4.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/t/memory_subquery4.test	2008-06-02 15:01:21 +0000
@@ -0,0 +1,16 @@
+########## suite/funcs_1/t/memory_subquery2.test #######################
+#                                                                      #
+# Testing of subqueries in transactions (...for update).               #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-13 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+# Memory tables should be used
+#
+# Set $engine_type
+let $engine = memory;
+let $primarykey = primary key;
+--source suite/funcs_1/subquery/subquery4.inc
+

=== added file 'mysql-test/suite/funcs_1/t/myisam_subquery2.test'
--- a/mysql-test/suite/funcs_1/t/myisam_subquery2.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/t/myisam_subquery2.test	2008-06-02 15:01:21 +0000
@@ -0,0 +1,16 @@
+########## suite/funcs_1/t/myisam_subquery2.test #######################
+#                                                                      #
+# Testing of privileges around                                         #
+#     Insert, Update, Select with subquery.                            #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-05 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+# MyISAM tables should be used
+#
+# Set $engine_type
+let $engine = MyISAM;
+--source suite/funcs_1/subquery/subquery2.inc
+

=== added file 'mysql-test/suite/funcs_1/t/myisam_subquery3.test'
--- a/mysql-test/suite/funcs_1/t/myisam_subquery3.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/t/myisam_subquery3.test	2008-06-02 15:01:21 +0000
@@ -0,0 +1,15 @@
+########## suite/funcs_1/t/myisam_subquery2.test #######################
+#                                                                      #
+# Testing transactions using LOCK/UNLOCK with subquery.                #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-05 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+# MyISAM tables should be used
+#
+# Set $engine_type
+let $engine = myisam;
+--source suite/funcs_1/subquery/subquery3.inc
+

=== added file 'mysql-test/suite/funcs_1/t/myisam_subquery4.test'
--- a/mysql-test/suite/funcs_1/t/myisam_subquery4.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/t/myisam_subquery4.test	2008-06-02 15:01:21 +0000
@@ -0,0 +1,16 @@
+########## suite/funcs_1/t/myisam_subquery2.test #######################
+#                                                                      #
+# Testing of subqueries in transactions (...for update).               #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-13 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+# MyIsam tables should be used
+#
+# Set $engine_type
+let $engine = MyISAM;
+let $primarykey = primary key;
+--source suite/funcs_1/subquery/subquery4.inc
+

=== added file 'mysql-test/suite/funcs_1/t/ndb_subquery2.test'
--- a/mysql-test/suite/funcs_1/t/ndb_subquery2.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/t/ndb_subquery2.test	2008-06-02 15:01:21 +0000
@@ -0,0 +1,17 @@
+########## suite/funcs_1/t/ndb_subquery2.test #######################
+#                                                                      #
+# Testing of privileges around                                         #
+#     Insert, Update, Select with subquery.                            #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-05 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+# MyISAM tables should be used
+#
+--source include/have_ndb.inc
+# Set $engine_type
+let $engine = NDB;
+--source suite/funcs_1/subquery/subquery2.inc
+

=== added file 'mysql-test/suite/funcs_1/t/ndb_subquery3.test'
--- a/mysql-test/suite/funcs_1/t/ndb_subquery3.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/t/ndb_subquery3.test	2008-06-02 15:01:21 +0000
@@ -0,0 +1,16 @@
+########## suite/funcs_1/t/ndb_subquery3.test ##########################
+#                                                                      #
+# Testing transactions using LOCK/UNLOCK with subquery.                #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-05 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+# MyISAM tables should be used
+#
+# Set $engine_type
+--source include/have_ndb.inc
+let $engine = NDB;
+--source suite/funcs_1/subquery/subquery3.inc
+

=== added file 'mysql-test/suite/funcs_1/t/ndb_subquery4.test'
--- a/mysql-test/suite/funcs_1/t/ndb_subquery4.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/funcs_1/t/ndb_subquery4.test	2008-06-02 15:01:21 +0000
@@ -0,0 +1,17 @@
+########## suite/funcs_1/t/ndb_subquery2.test #######################
+#                                                                      #
+# Testing of subqueries in transactions (...for update).               #
+#                                                                      #
+# Creation:                                                            #
+# 2007-09-13 hhunger  Created this test as part of                     #
+#                     WL#4029 addition tests of subqueries             #
+#                                                                      #
+########################################################################
+# NDB tables should be used
+#
+--source include/have_ndb.inc
+# Set $engine_type
+let $engine = NDB;
+let $primarykey = primary key;
+--source suite/funcs_1/subquery/subquery4.inc
+