views_master.inc

这个文件是windows mysql源码

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 ER_VIEW_SELECT_TMPTABLE
CREATE OR REPLACE VIEW v1 AS SELECT f2 FROM t2;
# JOIN - temporary table first
--error ER_VIEW_SELECT_TMPTABLE
CREATE OR REPLACE VIEW v1 AS SELECT *      FROM t2, t1;
--error ER_VIEW_SELECT_TMPTABLE
CREATE OR REPLACE VIEW v1 AS SELECT f2, f1 FROM t2, t1;
# JOIN - temporary table last
--error ER_VIEW_SELECT_TMPTABLE
CREATE OR REPLACE VIEW v1 AS SELECT *      FROM t1, t2;
--error ER_VIEW_SELECT_TMPTABLE
CREATE OR REPLACE VIEW v1 AS SELECT f1, f2 FROM t1, t2;
# UNION - temporary table first
--error ER_VIEW_SELECT_TMPTABLE
CREATE OR REPLACE VIEW v1 AS SELECT *  FROM t2 UNION SELECT *  FROM t1;
--error ER_VIEW_SELECT_TMPTABLE
CREATE OR REPLACE VIEW v1 AS SELECT f2 FROM t2 UNION SELECT f1 FROM t1;
# UNION - temporary table last
--error ER_VIEW_SELECT_TMPTABLE
CREATE OR REPLACE VIEW v1 AS SELECT *  FROM t1 UNION SELECT *  FROM t2;
--error ER_VIEW_SELECT_TMPTABLE
CREATE OR REPLACE VIEW v1 AS SELECT f1 FROM t1 UNION SELECT f2 FROM t2;
# SUBQUERY - temporary table first
--error ER_VIEW_SELECT_TMPTABLE
CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2
WHERE f2 = ( SELECT f1 FROM t1 );
# SUBQUERY - temporary table last
--error ER_VIEW_SELECT_TMPTABLE
CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t1
WHERE f1 = ( SELECT f2 FROM t2 );
DROP TABLE t1;
DROP TEMPORARY TABLE t2;


let $message= Testcase 3.3.1.26 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.26: Ensure that a view can be based on an underlying table
#                    within the same database
###############################################################################
--disable_warnings
DROP VIEW  IF EXISTS v1;
--enable_warnings
Create view test.v1 AS Select * from test.tb2;
if ($have_bug_11589)
{
--disable_ps_protocol
}
--sorted_result
Select * from test.v1;
--enable_ps_protocol
Drop view test.v1 ;


let $message= Testcase 3.3.1.27 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.27: Ensure that a view can be based on an underlying view
#                    within the same database.
###############################################################################
--disable_warnings
DROP VIEW  IF EXISTS test.v1;
Drop VIEW  IF EXISTS test.v1_1 ;
--enable_warnings
Create view test.v1 AS Select * from test.tb2;
Create view test.v1_1 AS Select F59 from test.v1 ;
Select * from test.v1_1 order by F59 limit 2;
Drop view test.v1 ;
Drop view test.v1_1 ;


let $message= Testcase 3.3.1.28 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.28:  Ensure that a view can be based on an underlying table
#                     from another database.
###############################################################################
--disable_warnings
Drop database if exists test2 ;
--enable_warnings
create database test2 ;
Create view test2.v2 AS Select * from test.tb2 limit 50,50;
use test2 ;
Create view v1 AS Select * from test.tb2 limit 50 ;
if ($have_bug_32285)
{
--disable_ps_protocol
}
--vertical_results
Select * from v1 order by f59,f60,f61,f62,f63,f64,f65;
--horizontal_results
--enable_ps_protocol
--sorted_result
Select * from test2.v2 ;
Drop view if exists test2.v1 ;
Drop view if exists test2.v2 ;
Drop database test2 ;


let $message= Testcase 3.3.1.29 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.29: Ensure that a view can be based on an underlying view from
#                    another database.
###############################################################################
--disable_warnings
Drop database if exists test2 ;
Drop view if exists test.v1 ;
--enable_warnings
create database test2 ;

use test2;
Create view test.v1 AS Select * from test.tb2 limit 50 ;
Create view test2.v2 AS Select F59 from test.v1 ;
Drop view if exists test.v1 ;
Drop view if exists test2.v2 ;

# Note(mleich): Testcase 3.3.1.30 (identical requirements like 3.3.1.26)
#               --> omitted

let $message= Testcase 3.3.1.31 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.31: Ensure that a view can be based on a join of multiple
#                    tables within the same database.
###############################################################################
--disable_warnings
Drop table if exists test.t1 ;
--enable_warnings
CREATE TABLE test.t1 ( f59 int, f60 int );
INSERT INTO test.t1 VALUES( 34, 654 );
INSERT INTO test.t1 VALUES( 906, 434 );
INSERT INTO test.t1 VALUES( 445, 765 );
Create or replace view test.v1
AS SELECT test.t1.F59, test.tb2.F60
FROM test.tb2 JOIN test.t1 ON test.tb2.F59 = test.t1.F59     ;
--sorted_result
Select * from test.v1;
Drop view test.v1 ;


let $message= Testcase 3.3.1.32 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.32: Ensure that a view can be based on a join of multiple
#                    tables from another database.
###############################################################################
--disable_warnings
Drop table    if exists test.t1 ;
Drop database if exists test2 ;
Drop view     if exists test.v1 ;
--enable_warnings
create database test2 ;
use test2 ;
CREATE TABLE t1 ( f59 int, f60 int );
INSERT INTO t1 VALUES( 34, 654 );
INSERT INTO t1 VALUES( 906, 434 );
INSERT INTO t1 VALUES( 445, 765 );
CREATE VIEW test2.v1
AS SELECT test.tb2.F59, test.tb2.F60
FROM test.tb2 INNER JOIN test2.t1 ON tb2.f59 = t1.f59;
--sorted_result
Select * from test2.v1;

Use test;


let $message= Testcase 3.3.1.33 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.33: Ensure that a view can be based on a join of multiple
#                    views within the same database.
###############################################################################
--disable_warnings
Drop view if exists test.v1_firstview  ;
Drop view if exists test.v1_secondview ;
Drop view if exists test.v1 ;
--enable_warnings
CREATE VIEW test.v1_firstview AS SELECT * FROM test.tb2;
CREATE VIEW test.v1_secondview AS SELECT * FROM test.tb2;
CREATE VIEW test.v1
AS SELECT test.v1_firstview.f59, test.v1_firstview.f60
FROM test.v1_firstview INNER JOIN test.v1_secondview
ON test.v1_firstview.f59 = test.v1_secondview.f59 ;
SELECT * FROM test.v1 order by f59,f60 limit 0,10;
Drop view if exists test.v1_firstview  ;
Drop view if exists test.v1_secondview ;
Drop view if exists test.v1 ;


let $message= Testcase 3.3.1.34 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.34: Ensure that a view can be based on a join of multiple
#                    views from another database.
###############################################################################
--disable_warnings
Drop database if exists test2 ;
Drop view if exists test.v1_firstview  ;
Drop view if exists test.v1_secondview ;
--enable_warnings

create database test2 ;
use test2 ;
CREATE VIEW test.v1_firstview AS SELECT * FROM test.tb2 ;
CREATE VIEW test.v1_secondview AS SELECT * FROM test.tb2 ;

CREATE VIEW v1
AS SELECT test.v1_firstview.F59, test.v1_firstview.F60
FROM test.v1_firstview INNER JOIN test.v1_secondview
ON test.v1_firstview.f59 = test.v1_secondview.f59 ;
SELECT * FROM v1 order by f59,f60 limit 0,10;
Drop view v1 ;
Drop view test.v1_firstview ;
Drop view test.v1_secondview ;


let $message= Testcase 3.3.1.35 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.35: Ensure that a view can be based on a join of multiple
#                    tables and/or views within the same database.
###############################################################################
use test;

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

CREATE VIEW test.v1_firstview AS SELECT * FROM test.tb2;

CREATE VIEW test.v1
AS SELECT test.v1_firstview.f59, test.v1_firstview.f60
FROM test.v1_firstview INNER JOIN test.tb2
ON test.v1_firstview.f59 = test.tb2.f59;
SELECT * FROM test.v1 order by f59,f60 limit 0,10;
Drop view test.v1 ;
Drop view test.v1_firstview;


let $message= Testcase 3.3.1.36 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.36: Ensure that a view can be based on a join of multiple
#                    tables and/or views from another database.
###############################################################################
--disable_warnings
Drop database if exists test2 ;
--enable_warnings
create database test2 ;
use test2 ;

CREATE VIEW v1_firstview AS SELECT * FROM test.tb2 ;
CREATE VIEW v1
AS SELECT v1_firstview.f59, v1_firstview.f60
FROM v1_firstview INNER JOIN test.tb2 ON v1_firstview.f59 = test.tb2.f59 ;
SELECT * FROM v1 order by f59,f60 limit 0,10;

Drop database test2 ;


let $message= Testcase 3.3.1.37 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.37: 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 in one other database.
###############################################################################
use test;
--disable_warnings
Drop table if exists t1;
Drop view  if exists test.v1 ;
Drop view  if exists test.v1_1 ;
Drop view  if exists test.v1_1 ;
Drop view  if exists test.v1_main ;
--enable_warnings
Create view test.v1 as Select f59, f60 FROM test.tb2;
Select * from test.v1 order by f59,f60 limit 0,10;

Create table t1(f59 int, f60 int);
Insert into t1 values (90,507) ;

Create view v1_1 as Select f59,f60 from t1 ;
Select * from v1_1 ;

Create view v1_main
as SELECT test.tb2.f59 FROM test.tb2 JOIN test.v1
ON test.tb2.f59 = test.v1.f59;
Select * from v1_main order by f59 limit 0,10;

Drop table t1;
Drop view  test.v1 ;
Drop view  test.v1_1 ;
Drop view  test.v1_main ;


let $message= Testcase 3.3.1.31 - 3.3.1.37 New Implementation ;
--source include/show_msg80.inc
###############################################################################
# mleich: The testcases 3.3.1.31 - 3.3.1.37 should be tested more systematic.
#                       Ensure that a view can be based on a join of multiple
# Testcase 3.3.1.31:        tables              within the same database
# Testcase 3.3.1.32:        tables              from another    database.
# Testcase 3.3.1.33:        views               within the same database
# Testcase 3.3.1.34:        views               from another    database
# Testcase 3.3.1.35:        tables and/or views within the same database
# Testcase 3.3.1.36:        tables and/or views from another database
# Testcase 3.3.1.37:        tables and/or views, some of which reside in
#                             the same database and some of which reside in
#                             one other database.
###############################################################################
USE test;
--disable_warnings
DROP DATABASE IF EXISTS test2;
DROP TABLE IF EXISTS t0,t1;
DROP VIEW  IF EXISTS t3,t4;
--enable_warnings
CREATE DATABASE test2;

--disable_warnings
CREATE TABLE test1.t0 (f1 VARCHAR(20));
CREATE TABLE test1.t1 (f1 VARCHAR(20));
--enable_warnings
CREATE TABLE test2.t0 (f1 VARCHAR(20));
CREATE TABLE test2.t1 (f1 VARCHAR(20));
--disable_warnings
CREATE VIEW  test1.t2 AS SELECT * FROM test1.t0;
CREATE VIEW  test1.t3 AS SELECT * FROM test2.t0;
--enable_warnings
CREATE VIEW  test2.t2 AS SELECT * FROM test2.t0;
CREATE VIEW  test2.t3 AS SELECT * FROM test1.t0;
INSERT INTO  test1.t0 VALUES('test1.t0');
INSERT INTO  test1.t1 VALUES('test1.t1');
INSERT INTO  test2.t0 VALUES('test2.t0');
INSERT INTO  test2.t1 VALUES('test2.t1');

# The extreme simple standard JOIN VIEW is:
# CREATE OR REPLACE VIEW <database>.v1
# AS SELECT * FROM <table or view 1>,<table or view 2>
let $view= test.v1;
let $tab1= test.t0;
let $tab2= test.t1;
# eval CREATE OR REPLACE VIEW $view AS SELECT * FROM $tab1, $tab2;
# Produce at least all testcases via simple combinatorics, because it is better
# to check some useless combinations than to forget an important one.
let $view= test.v1;
let $num_tab1= 3;
while ($num_tab1)
{
   let $num_tab2= 3;
   while ($num_tab2)
   {
      let $num_db1= 2;
      while ($num_db1)
      {
         let $num_db2= 2;
         while ($num_db2)
         {
            # Maybe somebody needs to check the generated values
            # --disable_query_log
            # eval SELECT '$num_db1.$num_tab1,$num_db2.$num_tab2';
            # --enable_query_log
            eval CREATE OR REPLACE VIEW $view AS
                 SELECT ta.f1 AS col1,