github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/view/view_all.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  DROP TABLE IF EXISTS t00;
    15  DROP TABLE IF EXISTS t01;
    16  DROP TABLE IF EXISTS v0;
    17  
    18  -----
    19  
    20  CREATE VIEW v1 AS SELECT 1;
    21  create view v2 as select 'foo' from dual;
    22  SELECT * from v1;
    23  SELECT * from v2;
    24  DROP TABLE IF EXISTS v1;
    25  DROP TABLE IF EXISTS v2;
    26  
    27  CREATE VIEW v1 AS SELECT CAST(1/3 AS DOUBLE), CAST(1/3 AS FLOAT(2)), CAST(1/3 AS FLOAT(50));
    28  SHOW CREATE VIEW v1;
    29  SELECT * FROM v1;
    30  DROP VIEW v1;
    31  
    32  -- test insert to view duplicated
    33  DROP TABLE IF EXISTS t1;
    34  CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);
    35  CREATE VIEW v1 AS SELECT * FROM t1;
    36  INSERT INTO v1 VALUES(1,20);
    37  INSERT INTO v1 VALUES(1,5);
    38  SELECT * FROM t1;
    39  SELECT * FROM v1;
    40  DROP VIEW v1;
    41  DROP TABLE t1;
    42  
    43  drop table if exists t1;
    44  create table t1 (i int);
    45  insert into t1 values (0), (1);
    46  create view v1 as select * from t1;
    47  select count(distinct i) from v1;
    48  drop table t1;
    49  drop view v1;
    50  
    51  drop table if exists t;
    52  CREATE TABLE t (x char(3));
    53  INSERT INTO t VALUES ('foo'), ('bar'); 
    54  CREATE VIEW v AS SELECT 'x' AS x FROM t;
    55  SELECT DISTINCT x FROM v;
    56  DROP TABLE t;
    57  DROP VIEW v;
    58  
    59  -- test view duplicate with table
    60  drop table if exists t1;
    61  drop table if exists t2;
    62  CREATE TABLE t1 (a INT);
    63  CREATE TABLE t2 (a INT);
    64  INSERT INTO t1 VALUES (1),(2),(3);
    65  INSERT INTO t2 VALUES (1),(2),(3);
    66  
    67  CREATE VIEW v1 AS SELECT t1.a FROM t1, t2;
    68  CREATE TABLE v1 (a INT);
    69  select * from v1;
    70  
    71  DROP VIEW v1;
    72  drop table if exists t1;
    73  drop table if exists t2;
    74  
    75  create table t2 (a int);
    76  create view t1 as select a from t2;
    77  insert into t1 (a) values (1);
    78  select * from t1;
    79  create table t1 (a int);
    80  create table if not exists t1 (a int,b int);
    81  show create table t1;
    82  select * from t1;
    83  drop table t2;
    84  drop view t1;
    85  
    86  -- test insert to view when view is not a table 
    87  drop view if exists t1;
    88  drop table if exists t2;
    89  create table t2 (a int);
    90  create view t1 as select a + 5 as a from t2;
    91  -- error ER_NONUPDATEABLE_COLUMN
    92  insert into t1 (a) values (1);
    93  -- error ER_NONUPDATEABLE_COLUMN
    94  update t1 set a=3 where a=2;
    95  drop view if exists t1;
    96  drop table if exists t2;
    97  
    98  create view t1 as select 1 as a;
    99  -- error ER_NON_INSERTABLE_TABLE
   100  insert into t1 (a) values (1);
   101  -- error ER_NON_UPDATABLE_TABLE
   102  update t1 set a=3 where a=2;
   103  drop view if exists t1;
   104  
   105  
   106  -- test for delete from join view
   107  DROP TABLE IF EXISTS t1;
   108  DROP TABLE IF EXISTS t2;
   109  DROP VIEW IF EXISTS v1;
   110  DROP VIEW IF EXISTS v2;
   111  
   112  CREATE TABLE t1(a INT);
   113  CREATE TABLE t2(b INT);
   114  insert into t1 values(1),(2);
   115  insert into t2 values(1),(2);
   116  CREATE VIEW v1 AS SELECT a, b FROM t1, t2;
   117  CREATE VIEW v2 AS SELECT a FROM t1;
   118  select * from v1;
   119  select * from v2;
   120  DELETE FROM v1;
   121  DELETE v2 FROM v2;
   122  select * from v1;
   123  select * from v2;
   124  
   125  DROP TABLE IF EXISTS t1;
   126  DROP TABLE IF EXISTS t2;
   127  DROP VIEW IF EXISTS v1;
   128  DROP VIEW IF EXISTS v2;
   129  
   130  -- test for view join table
   131  drop table if exists t;
   132  drop VIEW if exists v;
   133  CREATE TABLE t(f1 INTEGER);
   134  insert into t values(1),(2),(3),(6);
   135  CREATE VIEW v AS SELECT f1 FROM t;
   136  SELECT f1 FROM (SELECT f1 FROM v) AS dt1 NATURAL JOIN v dt2 WHERE f1 > 5;
   137  SELECT f1 FROM v NATURAL JOIN v dt2 WHERE f1 > 5;
   138  drop table if exists t;
   139  drop VIEW if exists v;
   140  
   141  -- test for view as DERIVED CONDITION
   142  drop table if exists t1;
   143  drop table if exists t2;
   144  CREATE TABLE t1(f1 INTEGER PRIMARY KEY);
   145  CREATE TABLE t2(f1 INTEGER);
   146  INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
   147  CREATE VIEW v AS SELECT f1 FROM t1;
   148  INSERT INTO t2 SELECT * FROM v WHERE f1=2;
   149  select * from t2;
   150  UPDATE t2 SET f1=3 WHERE f1 IN (SELECT f1 FROM v WHERE f1=2);
   151  select * from t2;
   152  DELETE FROM t2 WHERE f1 IN (SELECT f1 FROM v WHERE f1=3);
   153  select * from t2;
   154  
   155  DROP TABLE t1;
   156  DROP TABLE t2;
   157  DROp VIEW v;
   158  
   159  -- 
   160  CREATE TABLE C (
   161    col_varchar_10_key varchar(10) DEFAULT NULL,
   162    col_int_key int DEFAULT NULL,
   163    pk int NOT NULL AUTO_INCREMENT,
   164    col_date_key date DEFAULT NULL,
   165    PRIMARY KEY (`pk`),
   166    KEY `col_varchar_10_key` (`col_varchar_10_key`),
   167    KEY `col_int_key` (`col_int_key`),
   168    KEY `col_date_key` (`col_date_key`)
   169  );
   170  INSERT INTO C VALUES ('ok',3,1,'2003-04-02');
   171  INSERT INTO C VALUES ('ok',3,2,'2003-04-02');
   172  
   173  CREATE VIEW viewC AS SELECT * FROM C;
   174  
   175  SELECT  table1.col_date_key AS field1 
   176  FROM
   177    C AS table1
   178  WHERE 
   179    (table1.col_int_key <=ANY 
   180      ( SELECT SUBQUERY1_t1.col_int_key 
   181        FROM viewC AS SUBQUERY1_t1 
   182        WHERE SUBQUERY1_t1.col_varchar_10_key <= table1.col_varchar_10_key 
   183      ) 
   184    )
   185  ;
   186  DROP TABLE C;
   187  DROP VIEW viewC;
   188  
   189  -- 
   190  DROP TABLE if exists t2;
   191  DROP TABLE if exists t1;
   192  DROP VIEW if exists v1;
   193  DROP VIEW if exists v2;
   194  
   195  CREATE TABLE t1(f1 int, f11 int);
   196  CREATE TABLE t2(f2 int, f22 int);
   197  INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(5,5),(9,9),(7,7);
   198  INSERT INTO t2 VALUES(1,1),(3,3),(2,2),(4,4),(8,8),(6,6);
   199  CREATE VIEW v1 AS SELECT * FROM t1;
   200  CREATE VIEW v2 AS SELECT * FROM t1 JOIN t2 ON f1=f2;
   201  CREATE VIEW v3 AS SELECT * FROM t1 WHERE f1 IN (2,3);
   202  CREATE VIEW v4 AS SELECT * FROM t2 WHERE f2 IN (2,3);
   203  
   204  SELECT * FROM v1;
   205  SELECT * FROM v2;
   206  SELECT * FROM v3 WHERE f11 IN (1,3);
   207  SELECT * FROM v3 JOIN v4 ON f1=f2;
   208  SELECT * FROM v4 WHERE f2 IN (1,3);
   209  SELECT * FROM (SELECT * FROM t1 group by f1 HAVING f1=f1) tt;
   210  SELECT * FROM t1 JOIN (SELECT * FROM t2 GROUP BY f2) tt ON f1=f2;
   211  DROP TABLE if exists t2;
   212  DROP TABLE if exists t1;
   213  DROP VIEW v1;
   214  DROP VIEW v2;
   215  DROP VIEW v3;
   216  DROP VIEW v4;
   217  
   218  -- test for view with order by 
   219  CREATE TABLE t1 (f1 VARCHAR(1), key(f1));
   220  INSERT INTO t1 VALUES ('a');
   221  CREATE VIEW v1 AS SELECT f1 FROM t1 ORDER BY 1 LIMIT 0;
   222  CREATE VIEW v2 AS SELECT f1 FROM t1 ORDER BY 1 LIMIT 1;
   223  SELECT * FROM v1;
   224  SELECT * FROM v2;
   225  DROP VIEW v1;
   226  DROP VIEW v2;
   227  DROP TABLE t1;
   228  
   229  
   230  
   231  
   232  
   233  
   234  
   235