views_master.inc

这个文件是windows mysql源码

FROM test.tb2 my_table WITH CASCADED OPTION CHECK;
# CHECK OPTION before WITH
--error ER_PARSE_ERROR
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
FROM test.tb2 my_table CHECK OPTION WITH CASCADED;
# CHECK OPTION before AS SELECT
--error ER_PARSE_ERROR
CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION
AS SELECT F59, F60 FROM test.tb2 my_table;
# VIEW <viewname> after AS SELECT
--error ER_PARSE_ERROR
CREATE OR REPLACE AS SELECT F59, F60
FROM test.tb2 my_table VIEW v1 WITH CASCADED CHECK OPTION;
# VIEW <viewname> after CHECK OPTION
--error ER_PARSE_ERROR
CREATE OR REPLACE AS SELECT F59, F60
FROM test.tb2 my_table WITH CASCADED CHECK OPTION VIEW v1;

# Variants with LOCAL CHECK OPTION
--error ER_PARSE_ERROR
REPLACE OR CREATE VIEW v1 AS SELECT F59, F60
FROM test.tb2 my_table WITH LOCAL CHECK OPTION;
--error ER_PARSE_ERROR
CREATE OR REPLACE VIEW v1 SELECT AS F59, F60
FROM test.tb2 my_table WITH LOCAL CHECK OPTION;
--error ER_PARSE_ERROR
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
FROM test.tb2 my_table LOCAL WITH CHECK OPTION;
--error ER_PARSE_ERROR
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
FROM test.tb2 my_table WITH LOCAL OPTION CHECK;
--error ER_PARSE_ERROR
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
FROM test.tb2 my_table CHECK OPTION WITH LOCAL;
--error ER_PARSE_ERROR
CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION
AS SELECT F59, F60 FROM test.tb2 my_table;
--error ER_PARSE_ERROR
CREATE OR REPLACE AS SELECT F59, F60
FROM test.tb2 my_table VIEW v1 WITH LOCAL CHECK OPTION;
--error ER_PARSE_ERROR
CREATE OR REPLACE AS SELECT F59, F60
FROM test.tb2 my_table WITH LOCAL CHECK OPTION VIEW v1;

--disable_warnings
Drop table if exists t1 ;
--enable_warnings
CREATE table t1 (f1 int ,f2 int)  ;
INSERT INTO t1 values (235, 22);
INSERT INTO t1 values (554, 11);
# SELECTs of UNION in braces
--error ER_PARSE_ERROR
CREATE or REPLACE view v1 as (Select from f59 tb2)
Union ALL (Select from f1 t1);
# by before order
--error ER_PARSE_ERROR
CREATE or REPLACE view v1 as Select f59, f60
from tb2 by order f59;
# by before group
--error ER_PARSE_ERROR
CREATE or REPLACE view v1 as Select f59, f60
from tb2 by group f59  ;


let $message= Testcase 3.3.1.5 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.5:  Ensure that all clauses that are defined to be mandatory
#                    are indeed required to be mandatory by the MySQL server
#                    and tools.
###############################################################################
--disable_warnings
DROP VIEW  IF EXISTS v1 ;
--enable_warnings
--error ER_PARSE_ERROR
CREATE VIEW v1 SELECT * FROM tb2;
--error ER_PARSE_ERROR
CREATE v1 AS SELECT * FROM tb2;
--error ER_PARSE_ERROR
VIEW v1 AS SELECT * FROM tb2;
# positive case
CREATE VIEW v1 AS SELECT 1;
DROP VIEW v1;
--error ER_PARSE_ERROR
       VIEW v1 AS SELECT 1;
--error ER_PARSE_ERROR
CREATE      v1 AS SELECT 1;
--error ER_PARSE_ERROR
CREATE VIEW    AS SELECT 1;
--error ER_PARSE_ERROR
CREATE VIEW v1    SELECT 1;
--error ER_PARSE_ERROR
CREATE VIEW v1 AS         ;


let $message= Testcase 3.3.1.6 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.6: Ensure that any clauses that are defined to be optional
#                   are indeed treated as optional by the MySQL server
#                   and tools.
###############################################################################
# Note: The positive test in 3.3.1.5 shows, that ALGORITHM ..., CHECK OPTION
#       and any column_list after the VIEW name are optional.
#       Therefore check here:
#       - ALGORITHM = <all possible algorithms>
#       - all possible CHECK OPTIONs
#       - some incomplete or wrong stuff
--disable_warnings
DROP VIEW  IF EXISTS v1 ;
--enable_warnings
CREATE or REPLACE                       VIEW v1
as SELECT * from tb2;
CREATE or REPLACE ALGORITHM = UNDEFINED VIEW v1
as SELECT * from tb2;
CREATE or REPLACE ALGORITHM = MERGE     VIEW v1
as SELECT * from tb2;
CREATE or REPLACE ALGORITHM = TEMPTABLE VIEW v1
as SELECT * from tb2;
CREATE or REPLACE ALGORITHM = TEMPTABLE VIEW v1
as SELECT * from tb2;
# negative test cases
--error ER_PARSE_ERROR
CREATE or REPLACE           = TEMPTABLE VIEW v1
as SELECT * from tb2;
--error ER_PARSE_ERROR
CREATE or REPLACE ALGORITHM   TEMPTABLE VIEW v1
as SELECT * from tb2;
--error ER_PARSE_ERROR
CREATE or REPLACE ALGORITHM =           VIEW v1
as SELECT * from tb2;
--error ER_PARSE_ERROR
CREATE or REPLACE TEMPTABLE = ALGORITHM VIEW v1
as SELECT * from tb2;
--error ER_PARSE_ERROR
CREATE or REPLACE TEMPTABLE - ALGORITHM VIEW v1
as SELECT * from tb2;
--error ER_PARSE_ERROR
CREATE or REPLACE GARBAGE   = TEMPTABLE VIEW v1
as SELECT * from tb2;
--error ER_PARSE_ERROR
CREATE or REPLACE ALGORITHM = GARBAGE VIEW v1
as SELECT * from tb2;
Drop view if exists v1 ;

CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1;
CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1 WITH          CHECK OPTION;
CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1 WITH CASCADED CHECK OPTION;
CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1 WITH LOCAL    CHECK OPTION;
# negative test cases
--error ER_PARSE_ERROR
CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1 WITH NO       CHECK OPTION;
--error ER_PARSE_ERROR
CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1      CASCADED CHECK OPTION;
--error ER_PARSE_ERROR
CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1 WITH CASCADED       OPTION;
--error ER_PARSE_ERROR
CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1 WITH CASCADED CHECK       ;


let $message= Testcase 3.3.1.7 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.7: Ensure that all valid, fully-qualified, and non-qualified,
#                   view names are accepted, at creation time, alteration time,
#                   and drop time.
###############################################################################
# Note(mleich): non-qualified view name means a view name without preceeding
#               database name
--disable_warnings
DROP VIEW  IF EXISTS v1 ;
--enable_warnings
Create view test.v1 AS Select * from test.tb2;
Alter view test.v1 AS Select F59 from test. tb2 limit 100 ;
Drop view test.v1 ;
Create view v1 AS Select * from test.tb2 limit 100 ;
Alter view v1 AS Select F59 from test.tb2 limit 100 ;
Drop view v1 ;


let $message= Testcase 3.3.1.A0 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.A0: Ensure that view names are treated case sensitive.
###############################################################################
# Note(mleich): Maybe this test produces portability problems on Windows.
#               FIXME There should be a test outside this one checking the
#                     creation of objects with cases sensitive names.
#                     If we have this test the following sub testcase should
#                     be deleted.
--disable_warnings
DROP TABLE IF EXISTS t1 ;
DROP VIEW  IF EXISTS v1 ;
DROP VIEW  IF EXISTS V1 ;
--enable_warnings
eval CREATE TABLE t1 (f1 NUMERIC(4)) ENGINE = $engine_type;
INSERT INTO t1 VALUES(1111), (2222);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE f1 = 1111;
# We get here the sql code
# - 0    on OS with    cases sensitive view names (Example: UNIX)
# - ER_TABLE_EXISTS_ERROR on OS without cases sensitive view names (Example: WINDOWS)
--error 0,ER_TABLE_EXISTS_ERROR
CREATE VIEW V1 AS SELECT * FROM t1 WHERE f1 = 2222;
SELECT * FROM v1;
# SELECT * FROM V1;
--disable_warnings
DROP TABLE IF EXISTS t1 ;
DROP VIEW  IF EXISTS v1 ;
DROP VIEW  IF EXISTS V1 ;
--enable_warnings


let $message= Testcase 3.3.1.8 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.8: Ensure that any invalid view name is never accepted, and
#                   that an appropriate error message is returned when the name
#                   is rejected.
###############################################################################
# Note(mleich): There could be more negative tests here, but I assume that the
#               server routines checking if a table or view name is acceptable
#               are heavily tested in tests checking the creation of tables.
--error ER_PARSE_ERROR
Create view select AS Select * from test.tb2 limit 100;
--error ER_PARSE_ERROR
Create view as AS Select * from test.tb2 limit 100;
--error ER_PARSE_ERROR
Create view where AS Select * from test.tb2 limit 100;
--error ER_PARSE_ERROR
Create view from AS Select * from test.tb2 limit 100;
--error ER_PARSE_ERROR
Create view while AS Select * from test.tb2 limit 100;
--error ER_PARSE_ERROR
Create view asdkj*(&*&&^ as Select * from test.tb2 limit 100 ;
--disable_warnings
Drop view if exists test.procedure ;
--enable_warnings
Create view test.procedure as Select * from test.tb2 limit 100 ;
Drop view if exists test.procedure ;


let $message= Testcase 3.3.1.9 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.9: Ensure that a reference to a non-existent view is rejected
#                   with an appropriate error message
###############################################################################
# Note(mleich): The SELECT statement syntax does not contain any functionality
#               to claim, that the object after FROM must be a VIEW. SHOW's will
#               be checked in
#                  3.3.11 Checks on SHOW, EXPLAIN, and DESCRIBE statements.
#               Let's check here a view based on a dropped view or table.
--disable_warnings
Drop TABLE IF EXISTS t1 ;
Drop VIEW  IF EXISTS v1;
Drop VIEW  IF EXISTS v2;
Drop VIEW  IF EXISTS v3;
--enable_warnings
CREATE TABLE t1 ( f1 char(5));
INSERT INTO t1 SET f1 = 'abcde';
CREATE VIEW v1 AS SELECT f1 FROM t1;
CREATE VIEW v2 AS SELECT *  FROM v1;

# Only negative cases, positive cases will be checked later:
DROP TABLE t1;
--error ER_VIEW_INVALID
SELECT * FROM v1;
--error ER_VIEW_INVALID
DELETE FROM v1;
--error ER_VIEW_INVALID
UPDATE v1 SET f1 = 'aaaaa';
--error ER_VIEW_INVALID
INSERT INTO v1 SET f1 = "fffff";
# v2 is based on v1, which is now invalid
--error ER_VIEW_INVALID
SELECT * FROM v2;
--error ER_VIEW_INVALID
DELETE FROM v2;
--error ER_VIEW_INVALID
UPDATE v2 SET f1 = 'aaaaa';
--error ER_VIEW_INVALID
INSERT INTO v2 SET f1 = "fffff";
DROP VIEW v1;
# v2 is based on v1, which is now dropped
--error ER_VIEW_INVALID
SELECT * FROM v2;
--error ER_VIEW_INVALID
DELETE FROM v2;
--error ER_VIEW_INVALID
UPDATE v2 SET f1 = 'aaaaa';
--error ER_VIEW_INVALID
INSERT INTO v2 SET f1 = "fffff";

DROP VIEW v2;

# A VIEW based on itself is non sense.
--disable_warnings
DROP TABLE IF EXISTS t1 ;
DROP VIEW  IF EXISTS v1 ;
--enable_warnings
CREATE TABLE t1 (f1 FLOAT);
# Create a new VIEW based on itself
--error ER_NO_SUCH_TABLE
CREATE VIEW v1 AS SELECT * FROM v1;
# Replace a valid VIEW with one new based on itself
CREATE VIEW  v1 AS SELECT * FROM t1;
--error ER_NO_SUCH_TABLE
CREATE or REPLACE VIEW  v1 AS SELECT * FROM v1;

DROP VIEW  v1;
DROP TABLE t1;

let $message= Testcase 3.3.1.10 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.10: Ensure that it is not possible to create two views with
#                    the same name in the same database.
###############################################################################
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings
Create view test.v1 AS Select * from test.tb2 ;
--error ER_TABLE_EXISTS_ERROR
Create view test.v1 AS Select F59 from test.tb2 ;
--error ER_TABLE_EXISTS_ERROR
Create view      v1 AS Select F59 from test.tb2 ;


let $message= Testcase 3.3.1.11 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.11: Ensure that it is not possible to create a view and a base
#                    table with the same name in the same database.
###############################################################################
# The VIEW should get the same name like an already existing TABLE.
--error ER_TABLE_EXISTS_ERROR
Create view test.tb2 AS Select f59,f60 from test.tb2 limit 100 ;
--error ER_TABLE_EXISTS_ERROR
Create view      tb2 AS Select f59,f60 from test.tb2 limit 100 ;
# The TABLE should get the same name like an already existing VIEW.
--disable_warnings
Drop view if exists test.v111 ;
--enable_warnings
Create view test.v111 as select * from tb2 limit 50;
--error ER_TABLE_EXISTS_ERROR
Create table test.v111(f1 int );
--error ER_TABLE_EXISTS_ERROR
Create table      v111(f1 int );
DROP VIEW test.v111;


let $message= Testcase 3.3.1.12 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.12: Ensure that it is possible to create two or more views and
#                    base tables with the same name, providing each resides in
#                    a different database.
###############################################################################
USE test;
--disable_warnings
Drop database if exists test2 ;
--enable_warnings
Create database test2 ;
# Plan of sub tests