diff options
Diffstat (limited to 'mysql-test/t/insert_select.test')
| -rw-r--r-- | mysql-test/t/insert_select.test | 137 |
1 files changed, 0 insertions, 137 deletions
diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 6f86ed897ac..404d67390ab 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -266,140 +266,3 @@ INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1; DROP DATABASE bug21774_1; DROP DATABASE bug21774_2; -# -# Bug #20989: View '(null).(null)' references invalid table(s)... on -# SQL SECURITY INVOKER -# -# this is really the fact that REPLACE ... SELECT required additional -# INSERT privs (on tables that are part of a view) over the related -# REPLACE, SELECT -# - -CREATE DATABASE meow; - -connect (root,localhost,root,,meow); -connection root; - -CREATE TABLE table_target ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); -CREATE TABLE table_target2 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); -CREATE TABLE table_target3 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); -CREATE VIEW view_target2 AS SELECT mexs_id,messzeit FROM table_target2; -CREATE SQL SECURITY INVOKER VIEW view_target3 AS SELECT mexs_id,messzeit FROM table_target3; - -CREATE TABLE table_stations ( mexs_id VARCHAR(8), icao VARCHAR(4), country CHAR(2), PRIMARY KEY (mexs_id), UNIQUE KEY icao (icao), KEY country (country), CONSTRAINT stations_ibfk_8 FOREIGN KEY (country) REFERENCES countries (country) ON UPDATE CASCADE); -INSERT INTO table_stations VALUES ('87654321','XXXX','YY'); - -CREATE TABLE table_countries ( country CHAR(2), iso_short_en VARCHAR(64), PRIMARY KEY (country)); -INSERT INTO table_countries VALUES ('YY','Entenhausen'); - -CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW view_stations AS select table_stations.mexs_id AS mexs_id, table_stations.icao AS icao, table_stations.country AS landescode from (table_stations join table_countries on((table_stations.country = table_countries.country))); - -CREATE TABLE table_source ( id varchar(4), datetime TIMESTAMP, PRIMARY KEY (id)); -INSERT INTO table_source VALUES ('XXXX','2006-07-12 07:50:00'); - -GRANT SELECT ON table_source TO user20989@localhost; -GRANT SELECT ON table_countries TO user20989@localhost; -GRANT SELECT ON table_stations TO user20989@localhost; -GRANT SELECT ON view_stations TO user20989@localhost; -GRANT SELECT ON table_target TO user20989@localhost; -GRANT SELECT ON table_target2 TO user20989@localhost; -GRANT INSERT,DELETE,SELECT ON view_target3 TO user20989@localhost; - -connect (user20989,localhost,user20989,,meow); -connection user20989; - ---error 1142 -REPLACE INTO table_target -SELECT stations.mexs_id AS mexs_id, datetime AS messzeit -FROM table_source -INNER JOIN view_stations AS stations -ON table_source.id = stations.icao -LEFT JOIN table_target AS old -USING (mexs_id); - ---error 1142 -REPLACE INTO view_target2 -SELECT stations.mexs_id AS mexs_id, datetime AS messzeit -FROM table_source -INNER JOIN view_stations AS stations -ON table_source.id = stations.icao -LEFT JOIN view_target2 AS old -USING (mexs_id); - ---error 1356 -REPLACE INTO view_target3 -SELECT stations.mexs_id AS mexs_id, datetime AS messzeit -FROM table_source -INNER JOIN view_stations AS stations -ON table_source.id = stations.icao -LEFT JOIN view_target3 AS old -USING (mexs_id); - -connection root; -disconnect user20989; - -GRANT INSERT,DELETE ON table_target TO user20989@localhost; -GRANT INSERT,DELETE,SELECT ON view_target2 TO user20989@localhost; -GRANT INSERT,DELETE,SELECT ON table_target3 TO user20989@localhost; - -connect (user20989,localhost,user20989,,meow); -connection user20989; - -REPLACE INTO table_target -SELECT stations.mexs_id AS mexs_id, datetime AS messzeit -FROM table_source -INNER JOIN view_stations AS stations -ON table_source.id = stations.icao -LEFT JOIN table_target AS old -USING (mexs_id); - ---error 1142 -REPLACE INTO table_target2 VALUES ('00X45Y78','2006-07-12 07:50:00'); -REPLACE INTO view_target2 VALUES ('12X45Y78','2006-07-12 07:50:00'); - -SELECT stations.mexs_id AS mexs_id, datetime AS messzeit -FROM table_source -INNER JOIN view_stations AS stations -ON table_source.id = stations.icao -LEFT JOIN view_target2 AS old -USING (mexs_id); - -REPLACE INTO view_target2 -SELECT stations.mexs_id AS mexs_id, datetime AS messzeit -FROM table_source -INNER JOIN view_stations AS stations -ON table_source.id = stations.icao -LEFT JOIN view_target2 AS old -USING (mexs_id); - -REPLACE INTO view_target3 -SELECT stations.mexs_id AS mexs_id, datetime AS messzeit -FROM table_source -INNER JOIN view_stations AS stations -ON table_source.id = stations.icao -LEFT JOIN view_target3 AS old -USING (mexs_id); - -connection root; -disconnect user20989; - -SELECT * FROM table_target; -SELECT * FROM view_target2; -SELECT * FROM view_target3; - -DROP VIEW view_stations; -DROP TABLE table_source; -DROP TABLE table_countries; -DROP TABLE table_stations; -DROP TABLE table_target; -DROP TABLE table_target2; -DROP TABLE table_target3; -DROP VIEW view_target2; -DROP VIEW view_target3; -DROP USER user20989@localhost; - -disconnect root; - -connection default; - -DROP DATABASE meow; |