github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/view/alter_view.result (about) 1 DROP TABLE IF EXISTS t00; 2 DROP TABLE IF EXISTS t01; 3 DROP VIEW IF EXISTS v0; 4 CREATE TABLE t00(a INTEGER); 5 INSERT INTO t00 VALUES (1),(2); 6 CREATE TABLE t01(a INTEGER); 7 INSERT INTO t01 VALUES (1); 8 CREATE VIEW v0 AS SELECT t00.a, t01.a AS b FROM t00 LEFT JOIN t01 USING(a); 9 SELECT t00.a, t01.a AS b FROM t00 LEFT JOIN t01 USING(a); 10 a b 11 1 1 12 2 null 13 SELECT * FROM v0 WHERE b >= 0; 14 a b 15 1 1 16 SHOW CREATE VIEW v0; 17 View Create View character_set_client collation_connection 18 v0 CREATE VIEW v0 AS SELECT t00.a, t01.a AS b FROM t00 LEFT JOIN t01 USING(a); utf8mb4 utf8mb4_general_ci 19 ALTER VIEW v0 AS SELECT t00.a FROM t00; 20 SELECT t00.a FROM t00; 21 a 22 1 23 2 24 SELECT * FROM v0; 25 a 26 1 27 2 28 SHOW CREATE VIEW v0; 29 View Create View character_set_client collation_connection 30 v0 CREATE VIEW v0 AS SELECT t00.a FROM t00; utf8mb4 utf8mb4_general_ci 31 SELECT * FROM t00; 32 a 33 1 34 2 35 DROP TABLE IF EXISTS t00; 36 DROP TABLE IF EXISTS t01; 37 DROP TABLE IF EXISTS v0; 38 CREATE VIEW v1 AS SELECT 1; 39 create view v2 as select 'foo' from dual; 40 SELECT * from v1; 41 1 42 1 43 SELECT * from v2; 44 foo 45 foo 46 alter view v1 as select 'foo' from dual; 47 alter view v2 as SELECT 1; 48 SELECT * from v1; 49 foo 50 foo 51 SELECT * from v2; 52 1 53 1 54 DROP TABLE IF EXISTS v1; 55 DROP TABLE IF EXISTS v2; 56 CREATE VIEW v1 AS SELECT CAST(1/3 AS DOUBLE), CAST(1/3 AS FLOAT(2)), CAST(1/3 AS FLOAT(50)); 57 table v1 already exists 58 SHOW CREATE VIEW v1; 59 View Create View character_set_client collation_connection 60 v1 create view v1 as select 'foo' from dual; utf8mb4 utf8mb4_general_ci 61 SELECT * FROM v1; 62 foo 63 foo 64 alter view if exists v1 AS SELECT CAST(1/3 AS DOUBLE), CAST(1/3 AS FLOAT(2)), CAST(1/3 AS FLOAT(50)); 65 alter view v1 AS SELECT CAST(1/3 AS DOUBLE), CAST(1/3 AS FLOAT(2)), CAST(1/3 AS FLOAT(50)); 66 SHOW CREATE VIEW v1; 67 View Create View character_set_client collation_connection 68 v1 create view v1 AS SELECT CAST(1/3 AS DOUBLE), CAST(1/3 AS FLOAT(2)), CAST(1/3 AS FLOAT(50)); utf8mb4 utf8mb4_general_ci 69 SELECT * FROM v1; 70 cast(1 / 3 as double) cast(1 / 3 as float(2)) cast(1 / 3 as float(50)) 71 0.3333333333333333 0.33333334 0.3333333333333333 72 DROP VIEW v1; 73 drop table if exists t; 74 drop VIEW if exists v; 75 CREATE TABLE t(f1 INTEGER); 76 insert into t values(1),(2),(3),(6); 77 CREATE VIEW v AS SELECT f1 FROM t; 78 SELECT f1 FROM (SELECT f1 FROM v) AS dt1 NATURAL JOIN v dt2 WHERE f1 > 5; 79 f1 80 6 81 SELECT f1 FROM v NATURAL JOIN v dt2 WHERE f1 > 5; 82 f1 83 6 84 ALTER VIEW v AS SELECT f1 FROM (SELECT f1 FROM v) AS dt1 NATURAL JOIN v dt2 WHERE f1 > 5; 85 internal error: there is a recursive reference to the view v 86 SELECT * FROM v; 87 f1 88 1 89 2 90 3 91 6 92 drop table if exists t; 93 drop VIEW if exists v; 94 DROP TABLE if exists t2; 95 DROP TABLE if exists t1; 96 DROP VIEW if exists v1; 97 DROP VIEW if exists v2; 98 CREATE TABLE t1(f1 int, f11 int); 99 CREATE TABLE t2(f2 int, f22 int); 100 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(5,5),(9,9),(7,7); 101 INSERT INTO t2 VALUES(1,1),(3,3),(2,2),(4,4),(8,8),(6,6); 102 CREATE VIEW v1 AS SELECT * FROM t1; 103 CREATE VIEW v2 AS SELECT * FROM t1 JOIN t2 ON f1=f2; 104 CREATE VIEW v3 AS SELECT * FROM t1 WHERE f1 IN (2,3); 105 CREATE VIEW v4 AS SELECT * FROM t2 WHERE f2 IN (2,3); 106 SELECT * FROM v1; 107 f1 f11 108 1 1 109 2 2 110 3 3 111 5 5 112 9 9 113 7 7 114 SELECT * FROM v2; 115 f1 f11 f2 f22 116 1 1 1 1 117 2 2 2 2 118 3 3 3 3 119 SELECT * FROM v3 WHERE f11 IN (1,3); 120 f1 f11 121 3 3 122 SELECT * FROM v3 JOIN v4 ON f1=f2; 123 f1 f11 f2 f22 124 2 2 2 2 125 3 3 3 3 126 SELECT * FROM v4 WHERE f2 IN (1,3); 127 f2 f22 128 3 3 129 ALTER VIEW v1 AS SELECT * FROM t2 WHERE f2 IN (2,3); 130 ALTER VIEW v2 AS SELECT * FROM t1 WHERE f1 IN (2,3); 131 ALTER VIEW v3 AS SELECT * FROM t1 JOIN t2 ON f1=f2; 132 ALTER VIEW v4 AS SELECT * FROM t1; 133 SELECT * FROM v1; 134 f2 f22 135 3 3 136 2 2 137 SELECT * FROM v2; 138 f1 f11 139 2 2 140 3 3 141 SELECT * FROM v3 WHERE f11 IN (1,3); 142 f1 f11 f2 f22 143 1 1 1 1 144 3 3 3 3 145 SELECT * FROM v3 JOIN v4 ON f1=f2; 146 invalid input: ambiguouse column reference to 'f1' 147 SELECT * FROM v4 WHERE f2 IN (1,3); 148 invalid input: column f2 does not exist 149 DROP TABLE if exists t2; 150 DROP TABLE if exists t1; 151 DROP VIEW v1; 152 DROP VIEW v2; 153 DROP VIEW v3; 154 DROP VIEW v4; 155 CREATE TABLE t1 (f1 VARCHAR(1), key(f1)); 156 INSERT INTO t1 VALUES ('a'); 157 CREATE VIEW v1 AS SELECT f1 FROM t1 ORDER BY 1 LIMIT 0; 158 CREATE VIEW v2 AS SELECT f1 FROM t1 ORDER BY 1 LIMIT 1; 159 SELECT * FROM v1; 160 f1 161 SELECT * FROM v2; 162 f1 163 a 164 ALTER VIEW v1 AS SELECT f1 FROM t1 ORDER BY 1 LIMIT 1; 165 ALTER VIEW v2 AS SELECT f1 FROM t1 ORDER BY 1 LIMIT 0; 166 SELECT * FROM v1; 167 f1 168 a 169 SELECT * FROM v2; 170 f1 171 DROP VIEW v1; 172 DROP VIEW v2; 173 DROP TABLE t1; 174 CREATE TABLE t1 (f1 VARCHAR(1), key(f1)); 175 CREATE TABLE t2 (f1 VARCHAR(1), key(f1)); 176 INSERT INTO t1 VALUES ('a'); 177 CREATE VIEW t2 AS SELECT * FROM t1; 178 table t2 already exists 179 DROP VIEW IF EXISTS t2; 180 invalid view 'alter_view.t2' 181 DROP TABLE t1; 182 DROP TABLE t2;