views_master.inc

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· INC 代码 · 共 1,894 行 · 第 1/5 页

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;
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;
SELECT * FROM test.v1;
# Switch the base table
CREATE OR REPLACE VIEW test.v1 AS SELECT * FROM tb2 limit 2;
SHOW CREATE VIEW test.v1;
if ($have_bug_11589)
{
--disable_ps_protocol
}
SELECT * FROM test.v1 ;
--enable_ps_protocol
# Switch the SELECT but not the base table
CREATE OR REPLACE VIEW test.v1 AS SELECT F59 FROM tb2 limit 10,100;
SHOW CREATE VIEW test.v1;
SELECT * FROM test.v1;
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 1347
CREATE OR REPLACE VIEW test.tb2 AS SELECT * From tb2 LIMIT 2;
--error 1347
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 LIMIT 2;
if ($have_bug_11589)
{
--disable_ps_protocol
}
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(ML): 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(ML): 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 1054
SELECT NewNameF1,f60             FROM test.v1_1 ;
--error 1054
SELECT NewNameF1, v1_1.f60 FROM test.v1_1 ;
--error 1054
SELECT f59, f60 FROM test.v1 ;
Use test ;
--error 1054
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 1060
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 1060
CREATE OR REPLACE VIEW v1 AS SELECT t1.f1, t2.f1 AS f1 FROM t1, t2;
# duplicate via VIEW definition
--error 1060
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 1353
CREATE OR REPLACE VIEW v1 (my_f1       ) AS SELECT *      FROM t1;
--error 1353
CREATE OR REPLACE VIEW v1 (my_f1       ) AS SELECT f1, f2 FROM t1;
--error 1353
CREATE OR REPLACE VIEW v1 (my_f1, my_f2, my_f3) AS SELECT *      FROM t1;
--error 1353
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 LIMIT 2;
SELECT * FROM test.v1;
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 LIMIT 2;
SELECT * FROM test.v1;
CREATE OR REPLACE VIEW test.v1( product ) AS SELECT 1*2;
SELECT * FROM test.v1;
CREATE OR REPLACE VIEW test.v1( product ) AS SELECT USER();
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(ML): 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 1146
CREATE VIEW test.v2 AS SELECT * FROM test.t1;
--error 1146
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(ML): 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 1352
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 1352
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
CREATE           TABLE t1 (f1 char(10));
CREATE TEMPORARY TABLE t2 (f2 char(10));
INSERT INTO t1 VALUES('t1');
INSERT INTO t1 VALUES('A');
INSERT INTO t2 VALUES('t2');
INSERT INTO t2 VALUES('B');
# simple SELECT
--error 1352
CREATE OR REPLACE VIEW v1 AS SELECT f2 FROM t2;
# JOIN - temporary table first
--error 1352
CREATE OR REPLACE VIEW v1 AS SELECT *      FROM t2, t1;
--error 1352
CREATE OR REPLACE VIEW v1 AS SELECT f2, f1 FROM t2, t1;
# JOIN - temporary table last
--error 1352
CREATE OR REPLACE VIEW v1 AS SELECT *      FROM t1, t2;
--error 1352
CREATE OR REPLACE VIEW v1 AS SELECT f1, f2 FROM t1, t2;
# UNION - temporary table first
--error 1352
CREATE OR REPLACE VIEW v1 AS SELECT *  FROM t2 UNION SELECT *  FROM t1;
--error 1352
CREATE OR REPLACE VIEW v1 AS SELECT f2 FROM t2 UNION SELECT f1 FROM t1;
# UNION - temporary table last
--error 1352
CREATE OR REPLACE VIEW v1 AS SELECT *  FROM t1 UNION SELECT *  FROM t2;
--error 1352
CREATE OR REPLACE VIEW v1 AS SELECT f1 FROM t1 UNION SELECT f2 FROM t2;
# SUBQUERY - temporary table first
--error 1352
CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2