github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/view/view_all.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 DROP TABLE IF EXISTS t00; 20 DROP TABLE IF EXISTS t01; 21 DROP TABLE IF EXISTS v0; 22 CREATE VIEW v1 AS SELECT 1; 23 create view v2 as select 'foo' from dual; 24 SELECT * from v1; 25 1 26 1 27 SELECT * from v2; 28 foo 29 foo 30 DROP TABLE IF EXISTS v1; 31 DROP TABLE IF EXISTS v2; 32 CREATE VIEW v1 AS SELECT CAST(1/3 AS DOUBLE), CAST(1/3 AS FLOAT(2)), CAST(1/3 AS FLOAT(50)); 33 table v1 already exists 34 SHOW CREATE VIEW v1; 35 View Create View character_set_client collation_connection 36 v1 CREATE VIEW v1 AS SELECT 1; utf8mb4 utf8mb4_general_ci 37 SELECT * FROM v1; 38 1 39 1 40 DROP VIEW v1; 41 DROP TABLE IF EXISTS t1; 42 CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT); 43 CREATE VIEW v1 AS SELECT * FROM t1; 44 INSERT INTO v1 VALUES(1,20); 45 invalid input: cannot insert/update/delete from view 46 INSERT INTO v1 VALUES(1,5); 47 invalid input: cannot insert/update/delete from view 48 SELECT * FROM t1; 49 c1 c2 50 SELECT * FROM v1; 51 c1 c2 52 DROP VIEW v1; 53 DROP TABLE t1; 54 drop table if exists t1; 55 create table t1 (i int); 56 insert into t1 values (0), (1); 57 create view v1 as select * from t1; 58 select count(distinct i) from v1; 59 count(distinct i) 60 2 61 drop table t1; 62 drop view v1; 63 drop table if exists t; 64 CREATE TABLE t (x char(3)); 65 INSERT INTO t VALUES ('foo'), ('bar'); 66 CREATE VIEW v AS SELECT 'x' AS x FROM t; 67 SELECT DISTINCT x FROM v; 68 x 69 x 70 DROP TABLE t; 71 DROP VIEW v; 72 drop table if exists t1; 73 drop table if exists t2; 74 CREATE TABLE t1 (a INT); 75 CREATE TABLE t2 (a INT); 76 INSERT INTO t1 VALUES (1),(2),(3); 77 INSERT INTO t2 VALUES (1),(2),(3); 78 CREATE VIEW v1 AS SELECT t1.a FROM t1, t2; 79 CREATE TABLE v1 (a INT); 80 table v1 already exists 81 select * from v1; 82 a 83 1 84 2 85 3 86 1 87 2 88 3 89 1 90 2 91 3 92 DROP VIEW v1; 93 drop table if exists t1; 94 drop table if exists t2; 95 create table t2 (a int); 96 create view t1 as select a from t2; 97 insert into t1 (a) values (1); 98 invalid input: cannot insert/update/delete from view 99 select * from t1; 100 a 101 create table t1 (a int); 102 table t1 already exists 103 create table if not exists t1 (a int,b int); 104 show create table t1; 105 View Create View character_set_client collation_connection 106 t1 create view t1 as select a from t2; utf8mb4 utf8mb4_general_ci 107 select * from t1; 108 a 109 drop table t2; 110 drop view t1; 111 drop view if exists t1; 112 drop table if exists t2; 113 create table t2 (a int); 114 create view t1 as select a + 5 as a from t2; 115 insert into t1 (a) values (1); 116 invalid input: cannot insert/update/delete from view 117 update t1 set a=3 where a=2; 118 invalid input: cannot insert/update/delete from view 119 drop view if exists t1; 120 drop table if exists t2; 121 create view t1 as select 1 as a; 122 insert into t1 (a) values (1); 123 invalid input: cannot insert/update/delete from view 124 update t1 set a=3 where a=2; 125 invalid input: cannot insert/update/delete from view 126 drop view if exists t1; 127 DROP TABLE IF EXISTS t1; 128 DROP TABLE IF EXISTS t2; 129 DROP VIEW IF EXISTS v1; 130 DROP VIEW IF EXISTS v2; 131 CREATE TABLE t1(a INT); 132 CREATE TABLE t2(b INT); 133 insert into t1 values(1),(2); 134 insert into t2 values(1),(2); 135 CREATE VIEW v1 AS SELECT a, b FROM t1, t2; 136 CREATE VIEW v2 AS SELECT a FROM t1; 137 select * from v1; 138 a b 139 1 1 140 2 1 141 1 2 142 2 2 143 select * from v2; 144 a 145 1 146 2 147 DELETE FROM v1; 148 invalid input: cannot insert/update/delete from view 149 DELETE v2 FROM v2; 150 invalid input: cannot insert/update/delete from view 151 select * from v1; 152 a b 153 1 1 154 2 1 155 1 2 156 2 2 157 select * from v2; 158 a 159 1 160 2 161 DROP TABLE IF EXISTS t1; 162 DROP TABLE IF EXISTS t2; 163 DROP VIEW IF EXISTS v1; 164 DROP VIEW IF EXISTS v2; 165 drop table if exists t; 166 drop VIEW if exists v; 167 CREATE TABLE t(f1 INTEGER); 168 insert into t values(1),(2),(3),(6); 169 CREATE VIEW v AS SELECT f1 FROM t; 170 SELECT f1 FROM (SELECT f1 FROM v) AS dt1 NATURAL JOIN v dt2 WHERE f1 > 5; 171 f1 172 6 173 SELECT f1 FROM v NATURAL JOIN v dt2 WHERE f1 > 5; 174 f1 175 6 176 drop table if exists t; 177 drop VIEW if exists v; 178 drop table if exists t1; 179 drop table if exists t2; 180 CREATE TABLE t1(f1 INTEGER PRIMARY KEY); 181 CREATE TABLE t2(f1 INTEGER); 182 INSERT INTO t1 VALUES (1),(2),(3),(4),(5); 183 CREATE VIEW v AS SELECT f1 FROM t1; 184 INSERT INTO t2 SELECT * FROM v WHERE f1=2; 185 select * from t2; 186 f1 187 2 188 UPDATE t2 SET f1=3 WHERE f1 IN (SELECT f1 FROM v WHERE f1=2); 189 select * from t2; 190 f1 191 3 192 DELETE FROM t2 WHERE f1 IN (SELECT f1 FROM v WHERE f1=3); 193 select * from t2; 194 f1 195 DROP TABLE t1; 196 DROP TABLE t2; 197 DROp VIEW v; 198 CREATE TABLE C ( 199 col_varchar_10_key varchar(10) DEFAULT NULL, 200 col_int_key int DEFAULT NULL, 201 pk int NOT NULL AUTO_INCREMENT, 202 col_date_key date DEFAULT NULL, 203 PRIMARY KEY (`pk`), 204 KEY `col_varchar_10_key` (`col_varchar_10_key`), 205 KEY `col_int_key` (`col_int_key`), 206 KEY `col_date_key` (`col_date_key`) 207 ); 208 INSERT INTO C VALUES ('ok',3,1,'2003-04-02'); 209 INSERT INTO C VALUES ('ok',3,2,'2003-04-02'); 210 CREATE VIEW viewC AS SELECT * FROM C; 211 SELECT table1.col_date_key AS field1 212 FROM 213 C AS table1 214 WHERE 215 (table1.col_int_key <=ANY 216 ( SELECT SUBQUERY1_t1.col_int_key 217 FROM viewC AS SUBQUERY1_t1 218 WHERE SUBQUERY1_t1.col_varchar_10_key <= table1.col_varchar_10_key 219 ) 220 ) 221 ; 222 field1 223 2003-04-02 224 2003-04-02 225 DROP TABLE C; 226 DROP VIEW viewC; 227 DROP TABLE if exists t2; 228 DROP TABLE if exists t1; 229 DROP VIEW if exists v1; 230 DROP VIEW if exists v2; 231 CREATE TABLE t1(f1 int, f11 int); 232 CREATE TABLE t2(f2 int, f22 int); 233 INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(5,5),(9,9),(7,7); 234 INSERT INTO t2 VALUES(1,1),(3,3),(2,2),(4,4),(8,8),(6,6); 235 CREATE VIEW v1 AS SELECT * FROM t1; 236 CREATE VIEW v2 AS SELECT * FROM t1 JOIN t2 ON f1=f2; 237 CREATE VIEW v3 AS SELECT * FROM t1 WHERE f1 IN (2,3); 238 CREATE VIEW v4 AS SELECT * FROM t2 WHERE f2 IN (2,3); 239 SELECT * FROM v1; 240 f1 f11 241 1 1 242 2 2 243 3 3 244 5 5 245 9 9 246 7 7 247 SELECT * FROM v2; 248 f1 f11 f2 f22 249 1 1 1 1 250 2 2 2 2 251 3 3 3 3 252 SELECT * FROM v3 WHERE f11 IN (1,3); 253 f1 f11 254 3 3 255 SELECT * FROM v3 JOIN v4 ON f1=f2; 256 f1 f11 f2 f22 257 2 2 2 2 258 3 3 3 3 259 SELECT * FROM v4 WHERE f2 IN (1,3); 260 f2 f22 261 3 3 262 SELECT * FROM (SELECT * FROM t1 group by f1 HAVING f1=f1) tt; 263 SQL syntax error: column "t1.f11" must appear in the GROUP BY clause or be used in an aggregate function 264 SELECT * FROM t1 JOIN (SELECT * FROM t2 GROUP BY f2) tt ON f1=f2; 265 SQL syntax error: column "t2.f22" must appear in the GROUP BY clause or be used in an aggregate function 266 DROP TABLE if exists t2; 267 DROP TABLE if exists t1; 268 DROP VIEW v1; 269 DROP VIEW v2; 270 DROP VIEW v3; 271 DROP VIEW v4; 272 CREATE TABLE t1 (f1 VARCHAR(1), key(f1)); 273 INSERT INTO t1 VALUES ('a'); 274 CREATE VIEW v1 AS SELECT f1 FROM t1 ORDER BY 1 LIMIT 0; 275 CREATE VIEW v2 AS SELECT f1 FROM t1 ORDER BY 1 LIMIT 1; 276 SELECT * FROM v1; 277 f1 278 SELECT * FROM v2; 279 f1 280 a 281 DROP VIEW v1; 282 DROP VIEW v2; 283 DROP TABLE t1;