summaryrefslogtreecommitdiff
path: root/mysql-test/r/insert_select.result
diff options
context:
space:
mode:
authorunknown <tsmith@maint1.mysql.com>2006-08-03 10:18:04 +0200
committerunknown <tsmith@maint1.mysql.com>2006-08-03 10:18:04 +0200
commiteafb7605797830cc8fbcdfbbabb90ef5425df1c3 (patch)
treeb01dfa24284e02b3da656ba7ce5788d0b0d8fd3b /mysql-test/r/insert_select.result
parent0901cb0b295b6ca116bb02dbdfe98a7f1b5154e5 (diff)
parent8a08c8023534b9651b72d9528f06960feb7bfa3c (diff)
downloadmariadb-git-eafb7605797830cc8fbcdfbbabb90ef5425df1c3.tar.gz
Merge maint1.mysql.com:/data/localhome/tsmith/bk/mrg50-b
into maint1.mysql.com:/data/localhome/tsmith/bk/mrg51-b mysql-test/r/insert_select.result: SCCS merged sql/sql_insert.cc: SCCS merged
Diffstat (limited to 'mysql-test/r/insert_select.result')
-rw-r--r--mysql-test/r/insert_select.result100
1 files changed, 100 insertions, 0 deletions
diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index 390e76caf2c..1ec24701ae1 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -695,3 +695,103 @@ CREATE TABLE t2 (z int, y int);
CREATE TABLE t3 (a int, b int);
INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1);
DROP TABLE IF EXISTS t1,t2,t3;
+CREATE DATABASE meow;
+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;
+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 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'table_target'
+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 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'view_target2'
+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);
+ERROR HY000: View 'meow.view_target3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+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;
+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);
+REPLACE INTO table_target2 VALUES ('00X45Y78','2006-07-12 07:50:00');
+ERROR 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'table_target2'
+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);
+mexs_id messzeit
+87654321 2006-07-12 07:50:00
+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);
+SELECT * FROM table_target;
+mexs_id messzeit
+87654321 2006-07-12 07:50:00
+SELECT * FROM view_target2;
+mexs_id messzeit
+12X45Y78 2006-07-12 07:50:00
+87654321 2006-07-12 07:50:00
+SELECT * FROM view_target3;
+mexs_id messzeit
+87654321 2006-07-12 07:50:00
+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;
+DROP DATABASE meow;