github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/view/view.test (about)

     1  -- @label:bvt
     2  drop table if exists t1;
     3  drop table if exists t2;
     4  create table t1 (a int, b int);
     5  create table t2 (aa int, bb varchar(20));
     6  create view v1 as select * from t1;
     7  select * from v1;
     8  insert into t1 values (1, 11), (2, 22), (3, 33);
     9  insert into t2 values (1, "aa"), (2, "bb");
    10  show columns from v1;
    11  select * from v1 where a > 1;
    12  select * from v1, t2 where v1.a = t2.aa;
    13  create database db2;
    14  use db2;
    15  select * from view.v1 where a > 1;
    16  use view;
    17  drop database db2;
    18  drop table t1;
    19  select * from v1;
    20  drop table v1;
    21  drop view v1;
    22  
    23  show create view vvvv;
    24  create view v1 as select "a";
    25  show create view v1;
    26  create view v2 as select 'a';
    27  show create view v2;
    28  show create table v2;
    29  drop view v1;
    30  drop view v2;
    31  
    32  create table tt(a int);
    33  create view vv as select * from tt;
    34  drop table if exists tt;
    35  drop table if exists vv;
    36  drop view vv;
    37  
    38  drop table if exists t1;
    39  create table t1 (a int);
    40  insert into t1 values(1);
    41  drop table if exists t2;
    42  create table t2 (a int);
    43  insert into t2 values(1);
    44  create view vvvv as select a from t1 union all select a from t2;
    45  select * from vvvv;
    46  drop view vvvv;
    47  
    48  drop table if exists t1;
    49  create table t1 (a int);
    50  insert into t1 values(1),(2),(3),(4);
    51  create view v5 as select * from t1;
    52  select * from v5;
    53  alter view v5 as select * from t1 where a=1;
    54  select * from v5;
    55  alter view v5 as select * from t1 where a > 2;
    56  select * from v5;
    57  alter view if exists v6 as select * from t1;
    58  drop view v5;
    59  
    60  
    61  drop table if exists t1;
    62  create table t1( a int primary key, b varchar(15) );
    63  insert into t1 values(1, 'aaaa'),(2, 'bbbbbb'),(3, 'cccccccc');
    64  create view v1 as select (case when a>1 then 'NO' else 'YES' end) as IS_NULLABLE from t1;
    65  desc v1;
    66  drop view v1;
    67  
    68  drop database if exists test;
    69  create database test;
    70  use test;
    71  drop table if exists t1;
    72  create table t1(a int);
    73  create view v1 as select * from t1;
    74  SELECT relname, rel_createsql,relkind FROM mo_catalog.mo_tables WHERE reldatabase='test' order by relname;
    75  create table t2(a int not null, b varchar(20), c char(20));
    76  create view v2 as select * from t2;
    77  desc v2;
    78  drop database test;
    79  
    80  drop database if exists test;
    81  create database test;
    82  use test;
    83  drop table if exists t1;
    84  create table t1 (project_id varchar(64)   NOT NULL);
    85  
    86  drop table if exists t2;
    87  create table t2  (project_id varchar(64) NOT NULL, project_name varchar(255) NOT NULL);
    88  drop view if exists v1;
    89  create view v1 as select a.project_id as project_id, b.project_name as project_name from t1 a left join t2 b on a.project_id = b.project_id;
    90  desc v1;
    91  drop table t1;
    92  drop table t2;
    93  drop view v1;
    94  drop database test;