~drizzle-trunk/drizzle/development

--disable_warnings

drop table if exists t1;

--enable_warnings

# Test for INSERT DELAYED INTO a <view>

# BUG#13683: INSERT DELAYED into a view creates an infinite loop

#

create table t1 (n int);

create view  v1 as select * from t1;

--error 1347

insert delayed into v1 values (1);

drop table t1;

drop view  v1;

#

# 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;

#

# Bug#28587 SELECT is blocked by INSERT waiting on read lock, even with low_priority_updates

#

--echo connection: default

set low_priority_updates=1;

--disable_warnings

drop table if exists t1;

--enable_warnings

create table t1 (a int, b int, unique key t1$a (a));

lock table t1 read;

connect (update,localhost,root,,);

connection update;

--echo connection: update

set low_priority_updates=1;

show variables like 'low_priority_updates';

let $ID= `select connection_id()`;

--send insert into t1 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;

connection default;

# we must wait till the insert opens and locks the table

let $wait_condition=

  select count(*) = 1 from information_schema.processlist

  where state = "Table lock" and id = $ID;

--source include/wait_condition.inc

connect (select,localhost,root,,);

--echo connection: select

select * from t1;

connection default;

--echo connection: default

select * from t1;

connection default;

disconnect update;

disconnect select;

unlock tables;

drop table t1;

set low_priority_updates=default;