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

     1  drop table if exists t1;
     2  drop table if exists t2;
     3  create table t1 (a int, b int);
     4  create table t2 (aa int, bb varchar(20));
     5  create view v1 as select * from t1;
     6  select * from v1;
     7  a    b
     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  Field    Type    Null    Key    Default    Extra    Comment
    12  a    INT(32)    YES        null        
    13  b    INT(32)    YES        null        
    14  select * from v1 where a > 1;
    15  a    b
    16  2    22
    17  3    33
    18  select * from v1, t2 where v1.a = t2.aa;
    19  a    b    aa    bb
    20  1    11    1    aa
    21  2    22    2    bb
    22  create database db2;
    23  use db2;
    24  select * from view.v1 where a > 1;
    25  a    b
    26  2    22
    27  3    33
    28  use view;
    29  drop database db2;
    30  drop table t1;
    31  select * from v1;
    32  SQL parser error: table "t1" does not exist
    33  drop table v1;
    34  no such table view.v1
    35  drop view v1;
    36  show create view vvvv;
    37  invalid input: show view 'vvvv' is not a valid view
    38  create view v1 as select "a";
    39  show create view v1;
    40  View    Create View    character_set_client    collation_connection
    41  v1    create view v1 as select "a";    utf8mb4    utf8mb4_general_ci
    42  create view v2 as select 'a';
    43  show create view v2;
    44  View    Create View    character_set_client    collation_connection
    45  v2    create view v2 as select 'a';    utf8mb4    utf8mb4_general_ci
    46  show create table v2;
    47  View    Create View    character_set_client    collation_connection
    48  v2    create view v2 as select 'a';    utf8mb4    utf8mb4_general_ci
    49  drop view v1;
    50  drop view v2;
    51  create table tt(a int);
    52  create view vv as select * from tt;
    53  drop table if exists tt;
    54  drop table if exists vv;
    55  drop view vv;
    56  drop table if exists t1;
    57  create table t1 (a int);
    58  insert into t1 values(1);
    59  drop table if exists t2;
    60  create table t2 (a int);
    61  insert into t2 values(1);
    62  create view vvvv as select a from t1 union all select a from t2;
    63  select * from vvvv;
    64  a
    65  1
    66  1
    67  drop view vvvv;
    68  drop table if exists t1;
    69  create table t1 (a int);
    70  insert into t1 values(1),(2),(3),(4);
    71  create view v5 as select * from t1;
    72  select * from v5;
    73  a
    74  1
    75  2
    76  3
    77  4
    78  alter view v5 as select * from t1 where a=1;
    79  select * from v5;
    80  a
    81  1
    82  alter view v5 as select * from t1 where a > 2;
    83  select * from v5;
    84  a
    85  3
    86  4
    87  alter view if exists v6 as select * from t1;
    88  drop view v5;
    89  drop table if exists t1;
    90  create table t1( a int primary key, b varchar(15) );
    91  insert into t1 values(1, 'aaaa'),(2, 'bbbbbb'),(3, 'cccccccc');
    92  create view v1 as select (case when a>1 then 'NO' else 'YES' end) as IS_NULLABLE from t1;
    93  desc v1;
    94  Field    Type    Null    Key    Default    Extra    Comment
    95  is_nullable    VARCHAR(3)    NO        null        
    96  drop view v1;
    97  drop database if exists test;
    98  create database test;
    99  use test;
   100  drop table if exists t1;
   101  create table t1(a int);
   102  create view v1 as select * from t1;
   103  SELECT relname, rel_createsql,relkind FROM mo_catalog.mo_tables WHERE reldatabase='test' order by relname;
   104  relname    rel_createsql    relkind
   105  t1    create table t1 (a int)    r
   106  v1    create view v1 as select * from t1;    v
   107  create table t2(a int not null, b varchar(20), c char(20));
   108  create view v2 as select * from t2;
   109  desc v2;
   110  Field    Type    Null    Key    Default    Extra    Comment
   111  a    INT(32)    NO        null        
   112  b    VARCHAR(20)    YES        null        
   113  c    CHAR(20)    YES        null        
   114  drop database test;
   115  drop database if exists test;
   116  create database test;
   117  use test;
   118  drop table if exists t1;
   119  create table t1 (project_id varchar(64)   NOT NULL);
   120  drop table if exists t2;
   121  create table t2  (project_id varchar(64) NOT NULL, project_name varchar(255) NOT NULL);
   122  drop view if exists v1;
   123  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;
   124  desc v1;
   125  Field    Type    Null    Key    Default    Extra    Comment
   126  project_id    VARCHAR(64)    NO        null        
   127  project_name    VARCHAR(255)    YES        null        
   128  drop table t1;
   129  drop table t2;
   130  drop view v1;
   131  drop database test;