github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/expression/cte.result (about) 1 drop table if exists t1; 2 create table t1(a int, b int, c int); 3 insert into t1 values(null,null,null),(2,3,4); 4 WITH qn AS (SELECT a FROM t1) SELECT * FROM qn; 5 a 6 null 7 2 8 WITH qn AS (SELECT a FROM t1), qn2 as (select b from t1) 9 SELECT * FROM qn; 10 a 11 null 12 2 13 WITH qn AS (SELECT a FROM t1), qn2 as (select b from t1) 14 SELECT * FROM qn2; 15 b 16 null 17 3 18 WITH qn AS (SELECT a FROM t1), qn as (select b from t1) 19 SELECT 1 FROM qn; 20 SQL syntax error: WITH query name "qn" specified more than once 21 with test.qn as (select "with") select * from test.qn; 22 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 10 near ".qn as (select "with") select * from test.qn;"; 23 with qn as (select "with" as a) 24 with qn2 as (select "with" as a) 25 select a from qn; 26 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 2 column 5 near " 27 with qn2 as (select "with" as a) 28 select a from qn;"; 29 with qne as (select a from t1), 30 qnm as (select a from t1), 31 qnea as (select a from t1), 32 qnma as (select a from t1) 33 select qne.a,qnm.a,alias1.a,alias2.a 34 from qne, qnm, qnea as alias1, qnma as alias2 limit 2; 35 a a a a 36 null null null null 37 2 null null null 38 drop table if exists t1; 39 create table t1(a int, b int, c int); 40 insert into t1 values(null,null,null),(2,3,4); 41 WITH qn AS (SELECT b as a FROM t1) 42 SELECT qn.a, qn2.a FROM qn, qn as qn2; 43 a a 44 3 null 45 null null 46 3 3 47 null 3 48 WITH qn AS (SELECT b as a FROM t1), 49 qn2 AS (SELECT c FROM t1 WHERE a IS NULL or a>0) 50 SELECT qn.a, qn2.c FROM qn, qn2; 51 a c 52 null 4 53 null null 54 3 4 55 3 null 56 drop table if exists t1; 57 create table t1(a int, b int, c int); 58 insert into t1 values(null,null,null),(2,3,4); 59 WITH qn AS (SELECT 10*a as a FROM t1),qn2 AS (SELECT 3*a FROM qn) 60 SELECT * from qn2; 61 3*a 62 null 63 60 64 WITH qn AS (SELECT a FROM t1), qn2 AS (SELECT a FROM qn) 65 SELECT * from qn2; 66 a 67 null 68 2 69 WITH qn AS (SELECT b as a FROM t1), 70 qn2 AS (SELECT a FROM qn WHERE a IS NULL or a>0) 71 SELECT qn.a, qn2.a FROM qn, qn2; 72 a a 73 null 3 74 null null 75 3 3 76 3 null 77 with qn0 as (select 1), qn1 as (select * from qn0), qn2 as (select 1), qn3 as (select 1 from qn1, qn2) select 1 from qn3; 78 1 79 1 80 WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL or a>0), 81 qn AS (SELECT b as a FROM t1) 82 SELECT qn2.a FROM qn2; 83 SQL parser error: table "qn" does not exist 84 with qn1 as (with qn3 as (select * from qn2) select * from qn3), 85 qn2 as (select 1) 86 select * from qn1; 87 SQL parser error: table "qn2" does not exist 88 WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL or a>0), 89 qn AS (SELECT b as a FROM qn2) 90 SELECT qn.a FROM qn; 91 SQL parser error: table "qn" does not exist 92 drop table if exists t1; 93 create table t1(a int, b int, c int); 94 insert into t1 values(null,null,null),(2,3,4); 95 with qn as (select 1) select 2; 96 2 97 2 98 drop table if exists t1; 99 create table t1(a int, b int, c int); 100 insert into t1 values(null,null,null),(2,3,4),(4,5,6); 101 with qn as (select * from t1) select (select max(a) from qn); 102 (select max(a) from qn) 103 4 104 SELECT (WITH qn AS (SELECT 10*a as a FROM t1), 105 qn2 AS (SELECT 3*a AS b FROM qn) 106 SELECT * from qn2 LIMIT 1) 107 FROM t1; 108 (with qn as (select 10 * a as a from t1), qn2 as (select 3 * a as b from qn) select * from qn2 limit 1) 109 null 110 null 111 null 112 SELECT * 113 FROM (WITH qn AS (SELECT 10*a as a FROM t1), 114 qn2 AS (SELECT 3*a AS b FROM qn) 115 SELECT * from qn2) 116 AS dt; 117 b 118 null 119 60 120 120 121 with qn as (select * from t1 limit 10) 122 select (select max(a) from qn where a=0), 123 (select min(b) from qn where b=3); 124 (select max(a) from qn where a=0) (select min(b) from qn where b=3) 125 null 3 126 drop table if exists sales_days; 127 create table sales_days(day_of_sale DATE, amount INT); 128 insert into sales_days values('2015-01-02', 100), ('2015-01-05', 200),('2015-02-02', 10), ('2015-02-10', 100),('2015-03-02', 10), ('2015-03-18', 1); 129 with sales_by_month(month,total) as 130 (select month(day_of_sale), sum(amount) from sales_days 131 where year(day_of_sale)=2015 132 group by month(day_of_sale)), 133 best_month(month, total, award) as 134 (select month, total, "best" from sales_by_month 135 where total=(select max(total) from sales_by_month)), 136 worst_month(month, total, award) as 137 (select month, total, "worst" from sales_by_month 138 where total=(select min(total) from sales_by_month)) 139 select * from best_month union all select * from worst_month; 140 month total award 141 1 300 best 142 3 11 worst 143 drop table if exists sales_days; 144 drop table if exists t1; 145 create table t1(a int); 146 insert into t1 values(1),(2); 147 with qn(a) as (select 1 from t1 limit 2) 148 select * from qn where qn.a=(select * from qn qn1 limit 1) union select 2; 149 a 150 1 151 2 152 drop table if exists t1; 153 create table t1(a int, b int, c int); 154 insert into t1 values(null,null,null),(2,3,4),(4,5,6); 155 with qn as 156 (with qn2 as (select "qn2" as a from t1) select "qn", a from qn2) 157 select * from qn; 158 qn a 159 qn qn2 160 qn qn2 161 qn qn2 162 SELECT (WITH qn AS (SELECT t2.a*a as a FROM t1), 163 qn2 AS (SELECT 3*a AS b FROM qn) 164 SELECT * from qn2 LIMIT 1) 165 FROM t1 as t2; 166 (WITH qn AS (SELECT t2.a*a as a FROM t1), 167 qn2 AS (SELECT 3*a AS b FROM qn) 168 SELECT * from qn2 LIMIT 1) 169 null 170 null 171 null 172 WITH qn AS (SELECT b as a FROM t1) 173 SELECT (WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL or a>0) 174 SELECT qn2.a FROM qn2) FROM qn; 175 internal error: scalar subquery returns more than 1 row 176 WITH qn AS (select "outer" as a) 177 SELECT (WITH qn AS (SELECT "inner" as a) SELECT a from qn), 178 qn.a 179 FROM qn; 180 (WITH qn AS (SELECT "inner" as a) SELECT a from qn) a 181 inner outer 182 drop table if exists t1; 183 drop table if exists t2; 184 create table t1(a int, b int, c int); 185 create table t2(a int); 186 insert into t1 values(null,null,null),(2,3,4),(4,5,6); 187 INSERT INTO t2 188 WITH qn AS (SELECT 10*a as a FROM t1), 189 qn2 AS (SELECT 3*a AS b FROM qn) 190 SELECT * from qn2; 191 SELECT * FROM t2; 192 a 193 null 194 60 195 120 196 drop table if exists t1; 197 drop table if exists t2; 198 drop table if exists t1; 199 drop table if exists t2; 200 create table t1(a int, b int, c int); 201 insert into t1 values(null,null,null),(2,3,4),(4,5,6); 202 with qn as (select a from t1 order by 1) 203 select a from qn; 204 a 205 null 206 2 207 4 208 with qn as (select a from t1 order by 1) 209 select qn.a from qn, t1 as t2; 210 a 211 null 212 null 213 null 214 2 215 2 216 2 217 4 218 4 219 4 220 with qn as (select a from t1 order by 1 limit 10) 221 select qn.a from qn, t1 as t2; 222 a 223 null 224 null 225 null 226 2 227 2 228 2 229 4 230 4 231 4 232 drop table if exists t1; 233 drop table if exists t2; 234 create table t1(a int, b int, c int); 235 insert into t1 values(null,null,null),(2,3,4),(4,5,6); 236 with qn as (select a, b from t1) 237 select b from qn group by a; 238 SQL syntax error: column "qn.b" must appear in the GROUP BY clause or be used in an aggregate function 239 with qn as (select a, b from t1 where a=b) 240 select b from qn group by a; 241 SQL syntax error: column "qn.b" must appear in the GROUP BY clause or be used in an aggregate function 242 with qn as (select a, sum(b) as s from t1 group by a) 243 select s from qn group by a; 244 SQL syntax error: column "qn.s" must appear in the GROUP BY clause or be used in an aggregate function 245 drop table if exists t1; 246 drop table if exists t2; 247 create table t1(a int, b int, c int); 248 insert into t1 values(null,null,null),(2,3,4),(4,5,6),(4,5,6),(8,9,10); 249 with qn () as (select 1) select * from qn, qn qn1; 250 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 10 near ") as (select 1) select * from qn, qn qn1;"; 251 with qn (foo, bar) as (select 1) select * from qn, qn qn1; 252 SQL syntax error: table "qn" has 1 columns available but 2 columns specified 253 with qn as (select 1,1) select * from qn; 254 invalid input: ambiguous column reference 'qn.1' 255 with qn as (select 1,1 from t1) select * from qn; 256 invalid input: ambiguous column reference 'qn.1' 257 with qn (foo, foo) as (select 1,2) select * from qn; 258 invalid input: ambiguous column reference 'qn.foo' 259 with qn (foo, bar) as (select 1,1 from t1) select * from qn; 260 foo bar 261 1 1 262 1 1 263 1 1 264 1 1 265 1 1 266 with qn (foo, bar) as (select 1,1) select * from qn; 267 foo bar 268 1 1 269 with qn (foo, bar) as (select 1, 2 from t1 limit 2) select * from qn, qn qn1; 270 foo bar foo bar 271 1 2 1 2 272 1 2 1 2 273 1 2 1 2 274 1 2 1 2 275 with qn (foo, bar) as (select 1 as col, 2 as coll from t1 limit 2) select * from qn, qn qn1; 276 foo bar foo bar 277 1 2 1 2 278 1 2 1 2 279 1 2 1 2 280 1 2 1 2 281 with qn (foo, bar) as (select 1 as col, 2 as coll union 282 select a,b from t1 order by col) select qn1.bar from qn qn1; 283 bar 284 null 285 2 286 3 287 5 288 9 289 with qn (foo, bar) as (select a, b from t1 limit 2) select qn.bar,foo from qn; 290 bar foo 291 null null 292 3 2 293 drop table if exists t1; 294 drop table if exists t2; 295 DROP TABLE IF EXISTS t3; 296 create table t1 (s1 char(5), index s1(s1)); 297 create table t2 (s1 char(5), index s1(s1)); 298 insert into t1 values ('a1'),('a2'),('a3'); 299 insert into t2 values ('a1'),('a2'); 300 with qn as (SELECT s1 FROM t2) 301 select s1, s1 = ANY (select * from qn) from t1; 302 s1 s1 = ANY (select * from qn) 303 a1 true 304 a2 true 305 a3 false 306 with qn as (SELECT s1 FROM t2) 307 select s1, s1 < ANY (select * from qn) from t1; 308 s1 s1 < ANY (select * from qn) 309 a1 true 310 a2 false 311 a3 false 312 with qn as (SELECT s1 FROM t2) 313 select s1, s1 = ANY (select * from qn) from t1; 314 s1 s1 = ANY (select * from qn) 315 a1 true 316 a2 true 317 a3 false 318 drop table if exists t1; 319 drop table if exists t2; 320 DROP TABLE IF EXISTS t3; 321 create table t1 (a int); 322 create table t2 (a int, b int); 323 create table t3 (a int); 324 create table t4 (a int not null, b int not null); 325 insert into t1 values (2); 326 insert into t2 values (1,7),(2,7),(2,9); 327 insert into t4 values (4,8),(3,8),(5,9); 328 insert into t3 values(1),(0),(2),(9); 329 insert into t2 values (100, 5); 330 with qn as (select b from t2) 331 select * from t3 where a in (select * from qn); 332 a 333 9 334 with qn as (select b from t2 where b > 7) 335 select * from t3 where a in (select * from qn); 336 a 337 9 338 with qn as (select b from t2 where b > 7) 339 select * from t3 where a not in (select * from qn); 340 a 341 1 342 0 343 2 344 drop table if exists t1; 345 drop table if exists t2; 346 DROP TABLE IF EXISTS t3; 347 DROP TABLE IF EXISTS t4; 348 DROP TABLE IF EXISTS t5; 349 DROP TABLE IF EXISTS t6; 350 DROP TABLE IF EXISTS t7; 351 create table t1 (a int); 352 create table t2 (a int, b int); 353 create table t3 (a int); 354 create table t4 (a int not null, b int not null); 355 insert into t1 values (2); 356 insert into t2 values (1,7),(2,7); 357 insert into t4 values (4,8),(3,8),(5,9); 358 insert into t3 values (6),(7),(3); 359 with qn as (select * from t2 where t2.b=t3.a) 360 select * from t3 where exists (select * from qn); 361 invalid input: missing FROM-clause entry for table 't3' 362 with qn as (select * from t2 where t2.b=t3.a) 363 select * from t3 where not exists (select * from qn); 364 invalid input: missing FROM-clause entry for table 't3' 365 drop table if exists t1; 366 drop table if exists t2; 367 DROP TABLE IF EXISTS t3; 368 DROP TABLE IF EXISTS t4; 369 DROP TABLE IF EXISTS t5; 370 DROP TABLE IF EXISTS t6; 371 DROP TABLE IF EXISTS t7; 372 drop table if exists `t`; 373 CREATE TABLE `t` ( 374 `c1` int(11) DEFAULT NULL, 375 `c2` int(11) DEFAULT NULL, 376 `c3` int(11) DEFAULT NULL, 377 `c4` int(11) DEFAULT NULL, 378 `c5` int(11) DEFAULT NULL, 379 `c6` int(11) DEFAULT NULL, 380 `c7` int(11) DEFAULT NULL, 381 `c8` int(11) DEFAULT NULL, 382 `c9` int(11) DEFAULT NULL, 383 `c10` int(11) DEFAULT NULL, 384 `c11` int(11) DEFAULT NULL, 385 `c12` int(11) DEFAULT NULL, 386 `c13` int(11) DEFAULT NULL, 387 `c14` int(11) DEFAULT NULL, 388 `c15` int(11) DEFAULT NULL, 389 `c16` int(11) DEFAULT NULL, 390 `c17` int(11) DEFAULT NULL, 391 `c18` int(11) DEFAULT NULL, 392 `c19` int(11) DEFAULT NULL, 393 `c20` int(11) DEFAULT NULL, 394 `c21` int(11) DEFAULT NULL, 395 `c22` int(11) DEFAULT NULL, 396 `c23` int(11) DEFAULT NULL, 397 `c24` int(11) DEFAULT NULL, 398 `c25` int(11) DEFAULT NULL, 399 `c26` int(11) DEFAULT NULL, 400 `c27` int(11) DEFAULT NULL, 401 `c28` int(11) DEFAULT NULL, 402 `c29` int(11) DEFAULT NULL, 403 `c30` int(11) DEFAULT NULL, 404 `c31` int(11) DEFAULT NULL 405 ); 406 with qn as (select * from t limit 2) 407 select 408 (select max(c1) from qn where qn.c1=1), 409 (select max(c2) from qn where qn.c2=1), 410 (select max(c3) from qn where qn.c3=1), 411 (select max(c4) from qn where qn.c4=1), 412 (select max(c5) from qn where qn.c5=1), 413 (select max(c6) from qn where qn.c6=1), 414 (select max(c7) from qn where qn.c7=1), 415 (select max(c8) from qn where qn.c8=1), 416 (select max(c9) from qn where qn.c9=1), 417 (select max(c10) from qn where qn.c10=1), 418 (select max(c11) from qn where qn.c11=1), 419 (select max(c12) from qn where qn.c12=1), 420 (select max(c13) from qn where qn.c13=1), 421 (select max(c14) from qn where qn.c14=1), 422 (select max(c15) from qn where qn.c15=1), 423 (select max(c16) from qn where qn.c16=1), 424 (select max(c17) from qn where qn.c17=1), 425 (select max(c18) from qn where qn.c18=1), 426 (select max(c19) from qn where qn.c19=1), 427 (select max(c20) from qn where qn.c20=1), 428 (select max(c21) from qn where qn.c21=1), 429 (select max(c22) from qn where qn.c22=1), 430 (select max(c23) from qn where qn.c23=1), 431 (select max(c24) from qn where qn.c24=1), 432 (select max(c25) from qn where qn.c25=1), 433 (select max(c26) from qn where qn.c26=1), 434 (select max(c27) from qn where qn.c27=1), 435 (select max(c28) from qn where qn.c28=1), 436 (select max(c29) from qn where qn.c29=1), 437 (select max(c30) from qn where qn.c30=1), 438 (select max(c31) from qn where qn.c31=1) from qn; 439 (select max(c1) from qn where qn.c1=1) (select max(c2) from qn where qn.c2=1) (select max(c3) from qn where qn.c3=1) (select max(c4) from qn where qn.c4=1) (select max(c5) from qn where qn.c5=1) (select max(c6) from qn where qn.c6=1) (select max(c7) from qn where qn.c7=1) (select max(c8) from qn where qn.c8=1) (select max(c9) from qn where qn.c9=1) (select max(c10) from qn where qn.c10=1) (select max(c11) from qn where qn.c11=1) (select max(c12) from qn where qn.c12=1) (select max(c13) from qn where qn.c13=1) (select max(c14) from qn where qn.c14=1) (select max(c15) from qn where qn.c15=1) (select max(c16) from qn where qn.c16=1) (select max(c17) from qn where qn.c17=1) (select max(c18) from qn where qn.c18=1) (select max(c19) from qn where qn.c19=1) (select max(c20) from qn where qn.c20=1) (select max(c21) from qn where qn.c21=1) (select max(c22) from qn where qn.c22=1) (select max(c23) from qn where qn.c23=1) (select max(c24) from qn where qn.c24=1) (select max(c25) from qn where qn.c25=1) (select max(c26) from qn where qn.c26=1) (select max(c27) from qn where qn.c27=1) (select max(c28) from qn where qn.c28=1) (select max(c29) from qn where qn.c29=1) (select max(c30) from qn where qn.c30=1) (select max(c31) from qn where qn.c31=1) 440 drop table if exists `t`;