views_master.inc

这个文件是windows mysql源码

# Object name  object type in  object type in
#              database test   database test2
# t1           TABLE           TABLE
# t2           TABLE           VIEW
# v1           VIEW            TABLE
# v2           VIEW            VIEW
--disable_warnings
DROP TABLE IF EXISTS test.t0, test.t1, test.t2;
DROP VIEW  IF EXISTS test.v1;
DROP VIEW  IF EXISTS test.v2;
--enable_warnings
CREATE TABLE  test.t1 ( f1 VARCHAR(20));
CREATE TABLE test2.t1 ( f1 VARCHAR(20));
CREATE TABLE  test.t2 ( f1 VARCHAR(20));
CREATE TABLE test2.v1 ( f1 VARCHAR(20));
# t0 is an auxiliary table needed for the VIEWs
CREATE TABLE  test.t0 ( f1 VARCHAR(20));
CREATE TABLE test2.t0 ( f1 VARCHAR(20));

CREATE VIEW  test2.t2 AS SELECT * FROM test2.t0;
CREATE VIEW   test.v1 AS SELECT * FROM  test.t0;
CREATE VIEW   test.v2 AS SELECT * FROM  test.t0;
CREATE VIEW  test2.v2 AS SELECT * FROM test2.t0;

# Some additional tests on the just created objects to show that they are
# accessable and do have the expected content.
# INSERTs with full qualified table
INSERT INTO  test.t1 VALUES('test.t1  - 1');
INSERT INTO test2.t1 VALUES('test2.t1 - 1');
INSERT INTO  test.t2 VALUES('test.t2  - 1');
INSERT INTO test2.v1 VALUES('test2.v1 - 1');
INSERT INTO  test.t0 VALUES('test.t0  - 1');
INSERT INTO test2.t0 VALUES('test2.t0 - 1');
# INSERTs with not full qualified table name.
USE test;
INSERT INTO  t1 VALUES('test.t1  - 2');
INSERT INTO  t2 VALUES('test.t2  - 2');
INSERT INTO  t0 VALUES('test.t0  - 2');
USE test2;
INSERT INTO  t1 VALUES('test2.t1 - 2');
INSERT INTO  v1 VALUES('test2.v1 - 2');
INSERT INTO  t0 VALUES('test2.t0 - 2');
# SELECTs with full qualified table
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM v1;
SELECT * FROM v2;
USE test;
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM v1;
SELECT * FROM v2;


let $message= Testcase 3.3.1.13 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.13: Ensure that, if the CREATE OR REPLACE VIEW statement is
#                    used to create a view using the name of an existing view,
#                    it first cleanly drops the existing view and then creates
#                    the new view.
###############################################################################
--disable_warnings
DROP TABLE IF EXISTS t1;
DROP VIEW  IF EXISTS v1;
--enable_warnings
CREATE TABLE t1 (f1 BIGINT);
INSERT INTO t1 VALUES(1);
CREATE VIEW test.v1 AS SELECT * FROM t1 limit 2;
SHOW CREATE VIEW test.v1;
--sorted_result
SELECT * FROM test.v1;
# Switch the algorithm
CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW test.v1
AS SELECT * FROM t1 limit 2;
SHOW CREATE VIEW test.v1;
--sorted_result
SELECT * FROM test.v1;
# Switch the base table
CREATE OR REPLACE VIEW test.v1 AS SELECT * FROM tb2 order by f59 limit 2;
SHOW CREATE VIEW test.v1;
if ($have_bug_11589)
{
--disable_ps_protocol
}
--vertical_results
SELECT * FROM test.v1 order by f59,f60,f61,f62,f63,f64,f65;
--horizontal_results
--enable_ps_protocol
# Switch the SELECT but not the base table
CREATE OR REPLACE VIEW test.v1 AS SELECT F59 FROM tb2;
SHOW CREATE VIEW test.v1;
SELECT * FROM test.v1 order by F59 limit 10,100;
Drop table test.t1 ;
Drop view  test.v1 ;


let $message= Testcase 3.3.1.14 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.14: Ensure that, if the CREATE OR REPLACE VIEW statement is
#                    used to create a view using the name of an existing base
#                    table, it fails with an appropriate error message.
###############################################################################
--error ER_WRONG_OBJECT
CREATE OR REPLACE VIEW test.tb2 AS SELECT * From tb2 LIMIT 2;
--error ER_WRONG_OBJECT
CREATE OR REPLACE VIEW tb2 AS SELECT * From tb2 LIMIT 2;


let $message= Testcase 3.3.1.15 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.15: Ensure that, if the CREATE OR REPLACE VIEW statement is
#                    used to create a view using a name that does not already
#                    belong to an existing view or base table, it cleanly
#                    creates the view.
###############################################################################
--disable_warnings
Drop table if exists test.v1 ;
--enable_warnings
CREATE OR REPLACE view test.v1 as select * from tb2;
if ($have_bug_32285)
{
--disable_ps_protocol
}
--sorted_result
SELECT * FROM test.v1;
--enable_ps_protocol
Drop view test.v1 ;


let $message= Testcase 3.3.1.16 + 3.3.1.17 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.16: Ensure that a view with a definition that does not include
#                    an explicit column-name list takes its column names from
#                    the underlying base table(s).
# Testcase 3.3.1.17: Ensure that a view with a definition that does include an
#                    explicit column-name list uses the explicit names and not
#                    the name of the columns from the underlying base tables(s)
###############################################################################
--disable_warnings
Drop table if exists test.v1 ;
--enable_warnings
CREATE OR REPLACE VIEW v1 AS SELECT * From tb2;
# Note(mleich): The empty result is intended, because I want to compare
#               column names only.
SELECT * FROM tb2 WHERE 1 = 2;
SELECT * FROM v1  WHERE 1 = 2;
Drop view v1;

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP VIEW  IF EXISTS v1;
--enable_warnings
CREATE TABLE t1 (f1 NUMERIC(15,3));
INSERT INTO t1 VALUES(8.8);
# 1. no explicit column in VIEW definition or SELECT
CREATE VIEW v1 AS SELECT * FROM t1;
SHOW CREATE VIEW v1;
SELECT * FROM v1;
# 2. no explicit column in VIEW definition, but in SELECT column_list
CREATE OR REPLACE VIEW v1 AS SELECT f1 FROM t1;
SHOW CREATE VIEW v1;
SELECT * FROM v1;
# 3. no explicit column in VIEW definition, but alias from SELECT column_list
CREATE OR REPLACE VIEW v1 AS SELECT f1 As my_column FROM t1;
SHOW CREATE VIEW v1;
SELECT * FROM v1;
# 4. Finally the requirement: explicit column_list in VIEW definition
CREATE OR REPLACE VIEW v1(column1,column2)
AS SELECT f1 As my_column, f1 FROM t1;
SHOW CREATE VIEW v1;
SELECT * FROM v1;
CREATE OR REPLACE VIEW test.v1(column1,column2)
AS SELECT f1 As my_column, f1 FROM test.t1;
SHOW CREATE VIEW v1;
SELECT * FROM v1;


let $message= Testcase 3.3.1.18 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.18: Ensure that a reference to a view with a definition that
#                    includes an explicit column-name fails, with an appropriate
#                    error message, if the reference includes columns names
#                    from the underlying base table(s) rather than the view
#                    column names.
###############################################################################
# Note(mleich): The goal is to check the merge algorithm.
--disable_warnings
Drop view if exists v1 ;
Drop view if exists v1_1 ;
--enable_warnings
Create view v1
as Select test.tb2.f59 as NewNameF1, test.tb2.f60
from test.tb2 limit 0,100 ;
Create view v1_1
as Select test.tb2.f59 as NewNameF1, test.tb2.f60 as NewNameF2
from tb2 limit 0,100 ;
--error ER_BAD_FIELD_ERROR
SELECT NewNameF1,f60             FROM test.v1_1 ;
--error ER_BAD_FIELD_ERROR
SELECT NewNameF1, v1_1.f60 FROM test.v1_1 ;
--error ER_BAD_FIELD_ERROR
SELECT f59, f60 FROM test.v1 ;
Use test ;
--error ER_BAD_FIELD_ERROR
SELECT F59 FROM v1 ;


let $message= Testcase 3.3.1.19 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.19: Ensure that every column of a view must have a
#                    distinct name
###############################################################################
--disable_warnings
DROP TABLE IF EXISTS t1, t2;
DROP VIEW  IF EXISTS v1;
--enable_warnings
CREATE TABLE t1( f1 BIGINT, f2 DECIMAL(5,2));
INSERT INTO t1 VALUES(7, 7.7);
CREATE TABLE t2( f1 BIGINT, f2 DECIMAL(5,2));
INSERT INTO t2 VALUES(6, 6.6);
# positive testcases
CREATE VIEW v1 AS SELECT * FROM t1;
SELECT * FROM v1;
CREATE OR REPLACE VIEW v1 AS SELECT f1, f2 FROM t1;
SELECT * FROM v1;
CREATE OR REPLACE VIEW v1 AS SELECT f1 AS my_f1, f2 AS my_f2 FROM t1;
SELECT * FROM v1;
CREATE OR REPLACE VIEW v1 (my_f1, my_f2) AS SELECT f1, f2 FROM t1;
SELECT * FROM v1;
CREATE OR REPLACE VIEW v1 (my_f1, my_f2) AS SELECT t1.f1, t2.f2 FROM t1, t2;
SELECT * FROM v1;
# negative testcases (sometimes including the underlying SELECT)
# duplicate via alias in SELECT
SELECT f1, f2 AS f1 FROM t1;
--error ER_DUP_FIELDNAME
CREATE OR REPLACE VIEW v1 AS SELECT f1, f2 AS f1 FROM t1;
# duplicate via JOIN SELECT
SELECT t1.f1, t2.f1 AS f1 FROM t1, t2;
--error ER_DUP_FIELDNAME
CREATE OR REPLACE VIEW v1 AS SELECT t1.f1, t2.f1 AS f1 FROM t1, t2;
# duplicate via VIEW definition
--error ER_DUP_FIELDNAME
CREATE OR REPLACE VIEW v1 (my_col, my_col) AS SELECT * FROM t1;


let $message= Testcase 3.3.1.20 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.20: Ensure that, if a column-name list is provided for a
#                    view definition, the list contains a name for every column
#                    in the view
###############################################################################
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1( f1 BIGINT, f2 DECIMAL(5,2));
# positive case
CREATE OR REPLACE VIEW v1 (my_f1, my_f2) AS SELECT *      FROM t1;
CREATE OR REPLACE VIEW v1 (my_f1, my_f2) AS SELECT f1, f2 FROM t1;
# negative cases, where we assign a wrong number of column names
--error ER_VIEW_WRONG_LIST
CREATE OR REPLACE VIEW v1 (my_f1       ) AS SELECT *      FROM t1;
--error ER_VIEW_WRONG_LIST
CREATE OR REPLACE VIEW v1 (my_f1       ) AS SELECT f1, f2 FROM t1;
--error ER_VIEW_WRONG_LIST
CREATE OR REPLACE VIEW v1 (my_f1, my_f2, my_f3) AS SELECT *      FROM t1;
--error ER_VIEW_WRONG_LIST
CREATE OR REPLACE VIEW v1 (my_f1, my_f2, my_f3) AS SELECT f1, f2 FROM t1;


let $message= Testcase 3.3.1.21 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.21:  Ensure that a view column can be a direct copy of a
#                     column from an underlying table.
###############################################################################
--disable_warnings
DROP VIEW  IF EXISTS v1;
--enable_warnings
CREATE VIEW test.v1( F59, F60 ) AS SELECT F59, F60 From tb2;
SELECT * FROM test.v1 order by F59, F60 desc LIMIT 2;
Drop view if exists test.v1 ;


let $message= Testcase 3.3.1.22 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.22: Ensure that a view column can be based on any valid
#                    expression, whether or not the expression includes a
#                    reference of the column of an underlying table.
###############################################################################
--disable_warnings
DROP VIEW  IF EXISTS v1;
--enable_warnings
CREATE VIEW test.v1( product ) AS SELECT f59*f60 From tb2 WHERE f59 < 3;
--sorted_result
SELECT * FROM test.v1;
CREATE OR REPLACE VIEW test.v1( product ) AS SELECT 1*2;
--sorted_result
SELECT * FROM test.v1;
CREATE OR REPLACE VIEW test.v1( product ) AS SELECT USER();
--sorted_result
SELECT * FROM test.v1;
Drop view if exists test.v1 ;


let $message= Testcase 3.3.1.23 + 3.3.1.24 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.23: Ensure that a view definition that includes a reference to
#                    a non-existent table fails, with an appropriate error
#                    message, at creation time.
# Testcase 3.3.1.24: Ensure that a view definition that includes a reference to
#                    a non-existent view fails, with an appropriate error
#                    message, at creation time.
###############################################################################
# Note(mleich): The SELECT statement syntax does not contain any functionality
#               to claim, that the object after FROM must be a VIEW.
#               Testcase 3.3.1.24 should be deleted.
USE test;
--disable_warnings
DROP TABLE IF EXISTS t1;
DROP VIEW  IF EXISTS v1;
DROP VIEW  IF EXISTS v2;
--enable_warnings
--error ER_NO_SUCH_TABLE
CREATE VIEW test.v2 AS SELECT * FROM test.t1;
--error ER_NO_SUCH_TABLE
CREATE VIEW      v2 AS Select * from test.v1;
DROP VIEW IF EXISTS v2;


let $message= Testcase 3.3.1.25 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.25: Ensure that a view cannot be based on one or more
#                    temporary tables.
###############################################################################
# Note(mleich): A temporary table hides permanent tables which have the same
#               name. So do not forget to drop the temporary table.
--disable_warnings
DROP TABLE IF EXISTS t1_temp;
DROP TABLE IF EXISTS t2_temp;
DROP VIEW  IF EXISTS v1;
--enable_warnings
Create table t1_temp(f59 char(10),f60 int) ;
Create temporary table t1_temp(f59 char(10),f60 int) ;
Insert into t1_temp values('FER',90);
Insert into t1_temp values('CAR',27);
--error ER_VIEW_SELECT_TMPTABLE
Create view v1 as select * from t1_temp ;

Create temporary table t2_temp(f59 char(10),f60 int) ;
Insert into t2_temp values('AAA',11);
Insert into t2_temp values('BBB',22);
--error ER_VIEW_SELECT_TMPTABLE
Create or replace view v1
as select t1_temp.f59,t2_temp.f59 from t1_temp,t2_temp ;
DROP temporary table t1_temp;
DROP table t1_temp;
DROP temporary table t2_temp;

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP VIEW  IF EXISTS v1;
--enable_warnings