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

     1  
     2  DROP TABLE IF EXISTS t00;
     3  DROP TABLE IF EXISTS t01;
     4  DROP VIEW IF EXISTS v0;
     5  CREATE TABLE t00(a INTEGER);
     6  INSERT INTO t00 VALUES (1),(2);
     7  CREATE TABLE t01(a INTEGER);
     8  INSERT INTO t01 VALUES (1);
     9  
    10  CREATE VIEW v0 AS SELECT t00.a, t01.a AS b FROM t00 LEFT JOIN t01 USING(a);
    11  SELECT t00.a, t01.a AS b FROM t00 LEFT JOIN t01 USING(a);
    12  SELECT * FROM v0 WHERE b >= 0;
    13  SHOW CREATE VIEW v0;
    14  
    15  ALTER VIEW v0 AS SELECT t00.a FROM t00;
    16  SELECT t00.a FROM t00;
    17  SELECT * FROM v0;
    18  SHOW CREATE VIEW v0;
    19  SELECT * FROM t00;
    20  DROP TABLE IF EXISTS t00;
    21  DROP TABLE IF EXISTS t01;
    22  DROP TABLE IF EXISTS v0;
    23  -- ---
    24  
    25  CREATE VIEW v1 AS SELECT 1;
    26  create view v2 as select 'foo' from dual;
    27  SELECT * from v1;
    28  SELECT * from v2;
    29  alter view v1 as select 'foo' from dual;
    30  alter view v2 as SELECT 1;
    31  SELECT * from v1;
    32  SELECT * from v2;
    33  DROP TABLE IF EXISTS v1;
    34  DROP TABLE IF EXISTS v2;
    35  
    36  CREATE VIEW v1 AS SELECT CAST(1/3 AS DOUBLE), CAST(1/3 AS FLOAT(2)), CAST(1/3 AS FLOAT(50));
    37  SHOW CREATE VIEW v1;
    38  SELECT * FROM v1;
    39  
    40  alter view if exists v1 AS SELECT CAST(1/3 AS DOUBLE), CAST(1/3 AS FLOAT(2)), CAST(1/3 AS FLOAT(50));
    41  alter view v1 AS SELECT CAST(1/3 AS DOUBLE), CAST(1/3 AS FLOAT(2)), CAST(1/3 AS FLOAT(50));
    42  SHOW CREATE VIEW v1;
    43  SELECT * FROM v1;
    44  DROP VIEW v1;
    45  
    46  -- test for view join table
    47  drop table if exists t;
    48  drop VIEW if exists v;
    49  CREATE TABLE t(f1 INTEGER);
    50  insert into t values(1),(2),(3),(6);
    51  CREATE VIEW v AS SELECT f1 FROM t;
    52  SELECT f1 FROM (SELECT f1 FROM v) AS dt1 NATURAL JOIN v dt2 WHERE f1 > 5;
    53  SELECT f1 FROM v NATURAL JOIN v dt2 WHERE f1 > 5;
    54  
    55  ALTER VIEW v AS SELECT f1 FROM (SELECT f1 FROM v) AS dt1 NATURAL JOIN v dt2 WHERE f1 > 5;
    56  SELECT * FROM v;
    57  
    58  drop table if exists t;
    59  drop VIEW if exists v;
    60  
    61  --
    62  DROP TABLE if exists t2;
    63  DROP TABLE if exists t1;
    64  DROP VIEW if exists v1;
    65  DROP VIEW if exists v2;
    66  
    67  CREATE TABLE t1(f1 int, f11 int);
    68  CREATE TABLE t2(f2 int, f22 int);
    69  INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(5,5),(9,9),(7,7);
    70  INSERT INTO t2 VALUES(1,1),(3,3),(2,2),(4,4),(8,8),(6,6);
    71  CREATE VIEW v1 AS SELECT * FROM t1;
    72  CREATE VIEW v2 AS SELECT * FROM t1 JOIN t2 ON f1=f2;
    73  CREATE VIEW v3 AS SELECT * FROM t1 WHERE f1 IN (2,3);
    74  CREATE VIEW v4 AS SELECT * FROM t2 WHERE f2 IN (2,3);
    75  
    76  SELECT * FROM v1;
    77  SELECT * FROM v2;
    78  SELECT * FROM v3 WHERE f11 IN (1,3);
    79  SELECT * FROM v3 JOIN v4 ON f1=f2;
    80  SELECT * FROM v4 WHERE f2 IN (1,3);
    81  
    82  ALTER VIEW v1 AS SELECT * FROM t2 WHERE f2 IN (2,3);
    83  ALTER VIEW v2 AS SELECT * FROM t1 WHERE f1 IN (2,3);
    84  ALTER VIEW v3 AS SELECT * FROM t1 JOIN t2 ON f1=f2;
    85  ALTER VIEW v4 AS SELECT * FROM t1;
    86  
    87  SELECT * FROM v1;
    88  SELECT * FROM v2;
    89  SELECT * FROM v3 WHERE f11 IN (1,3);
    90  SELECT * FROM v3 JOIN v4 ON f1=f2;
    91  SELECT * FROM v4 WHERE f2 IN (1,3);
    92  
    93  DROP TABLE if exists t2;
    94  DROP TABLE if exists t1;
    95  DROP VIEW v1;
    96  DROP VIEW v2;
    97  DROP VIEW v3;
    98  DROP VIEW v4;
    99  
   100  -- test for view with order by
   101  CREATE TABLE t1 (f1 VARCHAR(1), key(f1));
   102  INSERT INTO t1 VALUES ('a');
   103  CREATE VIEW v1 AS SELECT f1 FROM t1 ORDER BY 1 LIMIT 0;
   104  CREATE VIEW v2 AS SELECT f1 FROM t1 ORDER BY 1 LIMIT 1;
   105  SELECT * FROM v1;
   106  SELECT * FROM v2;
   107  
   108  ALTER VIEW v1 AS SELECT f1 FROM t1 ORDER BY 1 LIMIT 1;
   109  ALTER VIEW v2 AS SELECT f1 FROM t1 ORDER BY 1 LIMIT 0;
   110  
   111  SELECT * FROM v1;
   112  SELECT * FROM v2;
   113  
   114  DROP VIEW v1;
   115  DROP VIEW v2;
   116  DROP TABLE t1;
   117  
   118  -- test for view with name duplicated
   119  CREATE TABLE t1 (f1 VARCHAR(1), key(f1));
   120  CREATE TABLE t2 (f1 VARCHAR(1), key(f1));
   121  INSERT INTO t1 VALUES ('a');
   122  CREATE VIEW t2 AS SELECT * FROM t1;
   123  DROP VIEW IF EXISTS t2;
   124  DROP TABLE t1;
   125  DROP TABLE t2;
   126  
   127  
   128  
   129  
   130  
   131  
   132  
   133  
   134