views_master.inc

这个文件是windows mysql源码

                        tb.f1 AS col2
                 FROM test$num_db1.t$num_tab1 ta, test$num_db2.t$num_tab2 tb;
            eval SELECT * FROM $view;

            dec $num_db2;
         }

         dec $num_db1;
      }

      dec $num_tab2;
   }

   dec $num_tab1;
}


let $message= Testcase 3.3.1.38 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.38: Ensure that a view can be based on a join of multiple
#                    tables and/or views, some of which reside in the same
#                    database and some of which reside two or more other
#                    databases.
###############################################################################
--disable_warnings
Drop table if exists test1.t1 ;
Drop view  if exists test.v1 ;
Drop view  if exists test.v1_main;
Drop view  if exists test1.v1_1 ;
Drop database if exists test3 ;
--enable_warnings
Create view test.v1 as Select f59, f60 FROM test.tb2;
Select * from test.v1 order by f59,f60 limit 20;

Create table test1.t1 (f59 int,f60 int)  ;
Insert into test1.t1 values (199,507) ;
Create view test1.v1_1 as Select f59,f60 from test1.t1 ;
Select * from test1.v1_1 ;

--disable_warnings
--enable_warnings
Create database test3 ;

Create table test3.t1(f59 int,f60 int) ;
Insert into test3.t1 values (1023,7670) ;
Create view test3.v1_2 as Select f59,f60 from test3.t1 ;
Select * from test3.v1_2 ;
use test ;

# mleich: FIXME The SELECT should deliver at least one row.
Create view v1_main
as SELECT test.tb2.f59 as f1, test1.v1_1.f59 as f2,
          test3.v1_2.f59 as f3
FROM (test.tb2,test1.v1_1,test.v1) JOIN test3.v1_2
ON (test.v1.f59 = test1.v1_1.f59) ;
Select * from v1_main ;

DROP VIEW test.v1 ;
DROP VIEW test1.v1_1 ;
DROP VIEW test.v1_main ;
DROP DATABASE test3;


let $message= Testcase 3.3.1.39 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.39: Ensure that a view definition that includes a subquery in
#                    a FROM clause is rejected with an appropriate error
#                    message at create time.
###############################################################################
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings
--error ER_VIEW_SELECT_DERIVED
CREATE VIEW test.v1
AS Select f59 from (Select * FROM tb2 limit 20) tx ;
--error ER_NO_SUCH_TABLE
SELECT * FROM test.v1 order by f59 ;
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings


let $message= Testcase 3.3.1.40 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.40: Ensure that a view definition that includes references to
#                    one or more user variables is rejected with an appropriate
#                    error message at create time.
###############################################################################
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings
Set @var1 = 'ABC' ;
Set @var2 = 'XYZ' ;
--error ER_VIEW_SELECT_VARIABLE
CREATE VIEW test.v1 AS SELECT @var1, @var2 ;
# System variables (name starts with '@@') are also not allowed
--error ER_VIEW_SELECT_VARIABLE
CREATE VIEW test.v1 AS SELECT @@global.sort_buffer_size;
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings


let $message= Testcase 3.3.1.41 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.41: Ensure that a view definition within a stored procedure
#                    definition cannot include references to any of the stored
#                    procedures parameters.
###############################################################################
--disable_warnings
Drop view if exists test.v1 ;
Drop procedure if exists sp1 ;
--enable_warnings

delimiter //;
Create procedure sp1() DETERMINISTIC
      Begin
              DECLARE x char;
              Set x = 200 ;
              Create view test.v1  as SELECT * FROM tb2 WHERE f59 = x ;
      End   //
delimiter ;//
--error ER_SP_DOES_NOT_EXIST
Call sp1() ;
Drop view if exists test.v1 ;
Drop procedure sp1 ;


let $message= Testcase 3.3.1.42 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.42: Ensure that a view definition that attempts to create a
#                    temporary view (e.g. CREATE TEMPORARY VIEW or CREATE OR
#                    REPLACE TEMPORARY VIEW) fails, with an appropriate
#                    error message.
###############################################################################
#(01)
--disable_warnings
Drop VIEW if exists test.v1 ;
--enable_warnings
--error ER_PARSE_ERROR
CREATE TEMPORARY VIEW test.v1 AS
SELECT * FROM test.tb2 limit 2 ;
#(02)
--error ER_PARSE_ERROR
CREATE OR REPLACE TEMPORARY VIEW test.v1 AS
SELECT * FROM test.tb2 limit 2 ;

--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings

Use test;


let $message= Testcase 3.3.1.43 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.43: Ensure that all valid changes (i.e. INSERT, UPDATE, DELETE
#                    statements) to a view are shown in the view and are
#                    accepted as changes by the underlying table(s).
###############################################################################
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings

CREATE VIEW test.v1 AS SELECT f59,f60 FROM test.tb2;

INSERT INTO test.v1 values(122,432);

if ($have_bug_32285)
{
--disable_ps_protocol
}
--vertical_results
SELECT * FROM test.tb2 where f59 = 122 and f60 = 432 limit 0,20;
--horizontal_results
--enable_ps_protocol

UPDATE test.v1 SET f59 = 3000 WHERE test.v1.f59 = 122 ;

if ($have_bug_32285)
{
--disable_ps_protocol
}
--vertical_results
SELECT * FROM test.tb2  where f59 = 3000 limit 0,20;
--horizontal_results
--enable_ps_protocol

DELETE FROM test.v1
where test.v1.f59 = 3000 and test.v1.f60 = 432;

SELECT * FROM test.tb2 where f59 = 3000 and f60 = 432;

drop view test.v1 ;


let $message= Testcase 3.3.1.44 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.44: Ensure that all invalid changes to a view are rejected
#                    with an appropriate error message and do not affect the
#                    data in the underlying tables(s).
###############################################################################
# mleich: Maybe we need some more tests here.
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings

# Note(mleich): The modification will fail, because the VIEW contains 'limit'
CREATE VIEW test.v1 AS SELECT f59,f60 FROM test.tb2 limit 100;

--error ER_NON_INSERTABLE_TABLE
INSERT INTO test.v1 values(31, 32, 33) ;

Drop view test.v1 ;


let $message= Testcase 3.3.1.45 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.45: Ensure that, for a view with a definition that does not
#                    include WITH CHECK OPTION, all changes to the view which
#                    violate the view definition do not show in the view but
#                    are accepted as changes by the underlying table(s) unless
#                    a constraint on an underlying table also makes the change
#                    invalid.
###############################################################################
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings
CREATE VIEW test.v1 AS SELECT * FROM test.tb2 where f59 = 04;

--enable_info
UPDATE test.v1 SET f59 = 30 where F59 = 04 ;
--disable_info
SELECT * FROM test.v1   where f59 = 30 order by f59;
if ($have_bug_32285)
{
--disable_ps_protocol
}
--vertical_results
SELECT * FROM test.tb2        where f59 = 30 ;
--horizontal_results
--enable_ps_protocol

--enable_info
UPDATE tb2 SET f59 = 100 where f59 = 30 ;
--disable_info
if ($have_bug_32285)
{
--disable_ps_protocol
}
--vertical_results
SELECT * FROM tb2 where f59 = 100 ;
--horizontal_results
--enable_ps_protocol
SELECT * FROM test.v1 order by f59 ;

drop view if exists test.v1 ;

--disable_warnings
Drop TABLE IF EXISTS test.t1 ;
Drop VIEW  IF EXISTS test.v1 ;
--enable_warnings
eval CREATE TABLE t1 (f1 BIGINT, f2 VARCHAR(20), PRIMARY KEY(f1))
     ENGINE = $engine_type;
INSERT INTO t1 VALUES(1,'one');
INSERT INTO t1 VALUES(2,'two');
INSERT INTO t1 VALUES(3,'three');
INSERT INTO t1 VALUES(5,'five');
CREATE VIEW v1 AS SELECT * FROM t1 WHERE f1 BETWEEN 2 AND 4;

### SELECTs
# 1.   Searched record is within the scope of the view
# 1.1  + exists within the base table
SELECT COUNT(*) FROM v1 WHERE f1 = 2;
# 1.2  + does not exists within the base table
SELECT COUNT(*) FROM v1 WHERE f1 = 4;
# 2.   Searched record is outside of the scope of the view
# 2.1  + exists within the base table
SELECT COUNT(*) FROM v1 WHERE f1 = 5;
# 2.2  + does not exists within the base table
SELECT COUNT(*) FROM v1 WHERE f1 = 10;

INSERT INTO t1 VALUES(4,'four');

### DELETEs
--enable_info
# 1.   Searched record is within the scope of the view
#      + exists within the base table
DELETE FROM v1 WHERE f1 = 3;
# 2.   Searched record is outside of the scope of the view
#      + exists within the base table
DELETE FROM v1 WHERE f1 = 5;
--disable_info
SELECT * FROM t1 ORDER BY f1;
SELECT * FROM v1 ORDER BY f1;

### INSERTs
--enable_info
# 1.   The record to be inserted will be within the scope of the view.
#      But there is already a record with the PRIMARY KEY f1 = 2 .
# OBN change for 5.1.21 --error ER_DUP_ENTRY_WITH_KEY_NAME
--error ER_DUP_ENTRY
INSERT INTO v1 VALUES(2,'two');
# 2.   The record to be inserted will be within the scope of the view.
#      There is no already existing record with the PRIMARY KEY f1 = 3 .
INSERT INTO v1 VALUES(3,'three');
# 3.   The record to be inserted will be outside of the scope of the view.
#      There is no already existing record with the PRIMARY KEY f1 = 6 .
INSERT INTO v1 VALUES(6,'six');
--disable_info
SELECT * FROM t1 ORDER BY f1;
SELECT * FROM v1 ORDER BY f1;

### UPDATEs
--enable_info
# 1.   The record to be updated is within the scope of the view
#      and will stay inside the scope.
#      But there is already a record with the PRIMARY KEY f1 = 2 .
# OBN change for 5.1.21 --error ER_DUP_ENTRY_WITH_KEY_NAME
--error ER_DUP_ENTRY
UPDATE v1 SET f1 = 2 WHERE f1 = 3;
# 2.   The record to be updated is within the scope of the view
#      and will stay inside the scope.
UPDATE v1 SET f2 = 'number' WHERE f1 = 3;
# 3.   The record to be updated is within the scope of the view
#      and will leave the scope.
UPDATE v1 SET f1 = 10 WHERE f1 = 3;
# 4.   The record to be updated is outside of the scope of the view.
UPDATE v1 SET f2 = 'number' WHERE f1 = 1;
--disable_info


let $message= Testcase 3.3.1.46 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.46: Ensure that, for a view with a definition that does
#                    include WITH CHECK OPTION, all changes to the view which
#                    violate the view definition are rejected with an
#                    appropriate error message and are not accepted as changes
#                    by the underlying table(s).
###############################################################################
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings
CREATE VIEW test.v1 AS SELECT f59,f60
FROM test.tb2 where f59 = 195 WITH CHECK OPTION ;

--error ER_VIEW_CHECK_FAILED
UPDATE test.v1 SET f59 = 198 where f59=195 ;
SELECT * FROM test.v1 order by f59 ;

drop view if exists test.v1 ;


let $message= Testcase 3.3.1.47 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.47: Ensure that, for a view with a definition that does
#                    include WITH LOCAL CHECK OPTION, all changes to the view
#                    which violate the view definition are rejected with an
#