github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/view/view-subquery-with-exists.result (about) 1 SELECT EXISTS(SELECT 1+1); 2 exists (select 1 + 1) 3 true 4 create view v1 as SELECT EXISTS(SELECT 1+1); 5 select * from v1; 6 exists (select 1 + 1) 7 true 8 drop view v1; 9 drop table if exists t1; 10 drop table if exists t2; 11 drop table if exists t3; 12 drop table if exists t4; 13 drop table if exists t5; 14 drop table if exists t6; 15 drop table if exists t7; 16 create table t1 (a int); 17 create table t2 (a int, b int); 18 create table t3 (a int); 19 create table t4 (a int not null, b int not null); 20 insert into t1 values (2); 21 insert into t2 values (1,7),(2,7); 22 insert into t4 values (4,8),(3,8),(5,9); 23 insert into t3 values (6),(7),(3); 24 select * from t3 where exists (select * from t2 where t2.b=t3.a); 25 a 26 7 27 select * from t3 where not exists (select * from t2 where t2.b=t3.a); 28 a 29 6 30 3 31 create view v1 as select * from t3 where exists (select * from t2 where t2.b=t3.a); 32 create view v2 as select * from t3 where not exists (select * from t2 where t2.b=t3.a); 33 select * from v1; 34 a 35 7 36 select * from v2; 37 a 38 6 39 3 40 drop view v1; 41 drop view v2; 42 insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10); 43 insert into t2 values (2,10); 44 create table t5 (a int); 45 insert into t5 values (5); 46 insert into t5 values (2); 47 create table t6 (patient_uq int, clinic_uq int); 48 create table t7( uq int primary key, name char(25)); 49 insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta"); 50 insert into t6 values (1,1),(1,2),(2,2),(1,3); 51 select * from t6 where exists (select * from t7 where uq = clinic_uq); 52 patient_uq clinic_uq 53 1 1 54 1 2 55 2 2 56 create view v1 as select * from t6 where exists (select * from t7 where uq = clinic_uq); 57 select * from v1; 58 patient_uq clinic_uq 59 1 1 60 1 2 61 2 2 62 drop view v1; 63 drop table if exists t1; 64 drop table if exists t2; 65 drop table if exists t3; 66 drop table if exists t4; 67 drop table if exists t5; 68 drop table if exists t6; 69 drop table if exists t7; 70 CREATE TABLE `t1` ( 71 `numeropost` int(8) unsigned NOT NULL, 72 `maxnumrep` int(10) unsigned NOT NULL default 0, 73 PRIMARY KEY (`numeropost`) 74 ); 75 INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2); 76 CREATE TABLE `t2` ( 77 `mot` varchar(30) NOT NULL default '', 78 `topic` int(8) unsigned NOT NULL default 0, 79 `dt` date, 80 `pseudo` varchar(35) NOT NULL default '' 81 ); 82 INSERT INTO t2 (mot,topic,dt,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce'); 83 SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND dt >= date'2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20; 84 numeropost maxnumrep 85 43506 2 86 40143 1 87 create view v1 as SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND dt >= date'2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20; 88 select * from v1; 89 numeropost maxnumrep 90 43506 2 91 40143 1 92 drop view v1; 93 drop table if exists t1; 94 drop table if exists t2; 95 drop table if exists t3; 96 CREATE TABLE `t1` ( 97 `mot` varchar(30) NOT NULL default '', 98 `topic` int(8) unsigned NOT NULL default 0, 99 `dt` date , 100 `pseudo` varchar(35) NOT NULL default '' 101 ); 102 CREATE TABLE `t2` ( 103 `mot` varchar(30) NOT NULL default '', 104 `topic` int(8) unsigned NOT NULL default 0, 105 `dt` date, 106 `pseudo` varchar(35) NOT NULL default '' 107 ) ; 108 CREATE TABLE `t3` ( 109 `numeropost` int(8) unsigned NOT NULL, 110 `maxnumrep` int(10) unsigned NOT NULL default 0, 111 PRIMARY KEY (`numeropost`) 112 ); 113 INSERT INTO t1 VALUES ('joce','1',null,'joce'),('test','2',null,'test'); 114 INSERT INTO t2 VALUES ('joce','1',null,'joce'),('test','2',null,'test'); 115 INSERT INTO t3 VALUES (1,1); 116 SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE numeropost=topic); 117 topic 118 2 119 DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE numeropost=topic)); 120 select * from t1; 121 mot topic dt pseudo 122 joce 1 null joce 123 create view v1 as select * from t1; 124 select * from v1; 125 mot topic dt pseudo 126 joce 1 null joce 127 drop view v1; 128 drop table if exists t1; 129 drop table if exists t2; 130 drop table if exists t3; 131 create table t1 (a int, b int); 132 insert into t1 values (1,2),(3,4); 133 select * from t1 up where exists (select * from t1 where t1.a=up.a); 134 a b 135 1 2 136 3 4 137 create view v1 as select * from t1 up where exists (select * from t1 where t1.a=up.a); 138 select * from v1; 139 a b 140 1 2 141 3 4 142 drop view v1; 143 drop table if exists t1; 144 drop table if exists t2; 145 drop table if exists t3; 146 CREATE TABLE t1 (a INT, b INT); 147 INSERT INTO t1 VALUES (1,1),(2,2); 148 CREATE TABLE t2 (a INT, b INT); 149 INSERT INTO t2 VALUES (1,1),(2,2); 150 CREATE TABLE t3 (a INT, b INT); 151 SELECT COUNT(*) FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY COUNT(*); 152 [unknown result because it is related to issue#3307] 153 SELECT COUNT(*) FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY COUNT(*); 154 [unknown result because it is related to issue#3307] 155 create view v1 as SELECT COUNT(*) FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY COUNT(*); 156 [unknown result because it is related to issue#3307] 157 create view v2 as SELECT COUNT(*) FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY COUNT(*); 158 [unknown result because it is related to issue#3307] 159 select * from v1; 160 [unknown result because it is related to issue#3307] 161 select * from v2; 162 [unknown result because it is related to issue#3307] 163 drop view v1; 164 [unknown result because it is related to issue#3307] 165 drop view v2; 166 [unknown result because it is related to issue#3307] 167 drop table if exists t1; 168 drop table if exists t2; 169 drop table if exists t3; 170 CREATE TABLE t1 (f1 varchar(1)); 171 INSERT INTO t1 VALUES ('v'),('s'); 172 CREATE TABLE t2 (f1_key varchar(1)); 173 INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'),('d'),('y'),('t'),('d'),('s'); 174 SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2 175 WHERE EXISTS 176 ( 177 SELECT DISTINCT f1_key 178 FROM t2 179 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 ); 180 f1 f1_key 181 v j 182 s j 183 v v 184 s v 185 v c 186 s c 187 v m 188 s m 189 v d 190 s d 191 v d 192 s d 193 v y 194 s y 195 v t 196 s t 197 v d 198 s d 199 v s 200 s s 201 create view v1 as SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2 202 WHERE EXISTS 203 ( 204 SELECT DISTINCT f1_key 205 FROM t2 206 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 ); 207 select * from v1; 208 f1 f1_key 209 v j 210 s j 211 v v 212 s v 213 v c 214 s c 215 v m 216 s m 217 v d 218 s d 219 v d 220 s d 221 v y 222 s y 223 v t 224 s t 225 v d 226 s d 227 v s 228 s s 229 drop view v1; 230 drop table if exists t1; 231 drop table if exists t2; 232 drop table if exists t3; 233 CREATE TABLE t1( pk int PRIMARY KEY,uk int,ukn int NOT NULL,ik int,d int); 234 INSERT INTO t1 VALUES (0, NULL, 0, NULL, NULL),(1, 10, 20, 30, 40),(2, 20, 40, 60, 80); 235 CREATE TABLE t2(pk int PRIMARY KEY); 236 INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9),(10), 237 (11),(12),(13),(14),(15),(16),(17),(18),(19),(20), 238 (21),(22),(23),(24),(25),(26),(27),(28),(29),(30), 239 (31),(32),(33),(34),(35),(36),(37),(38),(39),(40), 240 (41),(42),(43),(44),(45),(46),(47),(48),(49),(50), 241 (51),(52),(53),(54),(55),(56),(57),(58),(59),(60), 242 (61),(62),(63),(64),(65),(66),(67),(68),(69),(70), 243 (71),(72),(73),(74),(75),(76),(77),(78),(79),(80); 244 SELECT 1 WHERE EXISTS (SELECT * FROM t1 AS it); 245 1 246 1 247 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT 1); 248 pk uk ukn ik d 249 0 null 0 null null 250 1 10 20 30 40 251 2 20 40 60 80 252 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT 1 WHERE FALSE); 253 pk uk ukn ik d 254 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it); 255 pk uk ukn ik d 256 0 null 0 null null 257 1 10 20 30 40 258 2 20 40 60 80 259 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk = 1); 260 pk uk ukn ik d 261 0 null 0 null null 262 1 10 20 30 40 263 2 20 40 60 80 264 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk = 1); 265 pk uk ukn ik d 266 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ik = 1); 267 pk uk ukn ik d 268 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d = 1); 269 pk uk ukn ik d 270 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk = ot.pk); 271 pk uk ukn ik d 272 0 null 0 null null 273 1 10 20 30 40 274 2 20 40 60 80 275 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk = ot.uk); 276 pk uk ukn ik d 277 1 10 20 30 40 278 2 20 40 60 80 279 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ukn = ot.ukn); 280 pk uk ukn ik d 281 0 null 0 null null 282 1 10 20 30 40 283 2 20 40 60 80 284 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d = ot.d); 285 pk uk ukn ik d 286 1 10 20 30 40 287 2 20 40 60 80 288 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk > ot.pk); 289 pk uk ukn ik d 290 0 null 0 null null 291 1 10 20 30 40 292 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk > ot.uk); 293 pk uk ukn ik d 294 1 10 20 30 40 295 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ukn > ot.ukn); 296 pk uk ukn ik d 297 0 null 0 null null 298 1 10 20 30 40 299 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ik > ot.ik); 300 pk uk ukn ik d 301 1 10 20 30 40 302 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d > ot.d); 303 pk uk ukn ik d 304 1 10 20 30 40 305 SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk = ot.pk); 306 pk 307 1 308 2 309 SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk = ot.pk); 310 pk 311 10 312 20 313 SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ukn = ot.pk); 314 pk 315 20 316 40 317 SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d = ot.pk); 318 pk 319 40 320 80 321 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t2 AS it WHERE ot.d = it.pk - 1); 322 pk uk ukn ik d 323 1 10 20 30 40 324 SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it1 JOIN t2 AS it2 ON it1.pk > it2.pk WHERE ot.d = it2.pk); 325 pk uk ukn ik d 326 create view v1 as SELECT 1 WHERE EXISTS (SELECT * FROM t1 AS it); 327 create view v2 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT 1); 328 create view v3 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT 1 WHERE FALSE); 329 create view v4 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it); 330 create view v5 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk = 1); 331 create view v6 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk = 1); 332 create view v7 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ik = 1); 333 create view v8 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d = 1); 334 create view v9 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk = ot.pk); 335 create view v10 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk = ot.uk); 336 create view v11 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ukn = ot.ukn); 337 create view v12 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d = ot.d); 338 create view v13 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk > ot.pk); 339 create view v14 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk > ot.uk); 340 create view v15 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ukn > ot.ukn); 341 create view v16 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ik > ot.ik); 342 create view v17 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d > ot.d); 343 create view v18 as SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk = ot.pk); 344 create view v19 as SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk = ot.pk); 345 create view v20 as SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ukn = ot.pk); 346 create view v21 as SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d = ot.pk); 347 create view v22 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t2 AS it WHERE ot.d = it.pk - 1); 348 create view v23 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it1 JOIN t2 AS it2 ON it1.pk > it2.pk WHERE ot.d = it2.pk); 349 select * from v1; 350 1 351 1 352 select * from v2; 353 pk uk ukn ik d 354 0 null 0 null null 355 1 10 20 30 40 356 2 20 40 60 80 357 select * from v3; 358 pk uk ukn ik d 359 select * from v4; 360 pk uk ukn ik d 361 0 null 0 null null 362 1 10 20 30 40 363 2 20 40 60 80 364 select * from v5; 365 pk uk ukn ik d 366 0 null 0 null null 367 1 10 20 30 40 368 2 20 40 60 80 369 select * from v6; 370 pk uk ukn ik d 371 select * from v7; 372 pk uk ukn ik d 373 select * from v8; 374 pk uk ukn ik d 375 select * from v9; 376 pk uk ukn ik d 377 0 null 0 null null 378 1 10 20 30 40 379 2 20 40 60 80 380 select * from v10; 381 pk uk ukn ik d 382 1 10 20 30 40 383 2 20 40 60 80 384 select * from v11; 385 pk uk ukn ik d 386 0 null 0 null null 387 1 10 20 30 40 388 2 20 40 60 80 389 select * from v12; 390 pk uk ukn ik d 391 1 10 20 30 40 392 2 20 40 60 80 393 select * from v13; 394 pk uk ukn ik d 395 0 null 0 null null 396 1 10 20 30 40 397 select * from v14; 398 pk uk ukn ik d 399 1 10 20 30 40 400 select * from v15; 401 pk uk ukn ik d 402 0 null 0 null null 403 1 10 20 30 40 404 select * from v16; 405 pk uk ukn ik d 406 1 10 20 30 40 407 select * from v17; 408 pk uk ukn ik d 409 1 10 20 30 40 410 select * from v18; 411 pk 412 1 413 2 414 select * from v19; 415 pk 416 10 417 20 418 select * from v20; 419 pk 420 20 421 40 422 select * from v21; 423 pk 424 40 425 80 426 select * from v22; 427 pk uk ukn ik d 428 1 10 20 30 40 429 select * from v23; 430 pk uk ukn ik d 431 drop view v1; 432 drop view v2; 433 drop view v3; 434 drop view v4; 435 drop view v5; 436 drop view v6; 437 drop view v7; 438 drop view v8; 439 drop view v9; 440 drop view v10; 441 drop view v11; 442 drop view v12; 443 drop view v13; 444 drop view v14; 445 drop view v15; 446 drop view v16; 447 drop view v17; 448 drop view v18; 449 drop view v19; 450 drop view v20; 451 drop view v21; 452 drop view v22; 453 drop view v23; 454 drop table if exists t1; 455 drop table if exists t2; 456 drop table if exists t3; 457 CREATE TABLE t1 (a int); 458 SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE 127 = 55); 459 a 460 drop table if exists t1; 461 drop table if exists t2; 462 drop table if exists t3; 463 create table t1 (s1 int); 464 create table t2 (s1 int); 465 insert into t1 values (1); 466 insert into t2 values (1); 467 select * from t1 where exists (select s1 from t2 group by s1 having max(t2.s1)=t1.s1); 468 SQL syntax error: column "t1.s1" must appear in the GROUP BY clause or be used in an aggregate function 469 create view v1 as select * from t1 where exists (select s1 from t2 group by s1 having max(t2.s1)=t1.s1); 470 SQL syntax error: column "t1.s1" must appear in the GROUP BY clause or be used in an aggregate function 471 select * from v1; 472 SQL parser error: table "v1" does not exist 473 drop view v1; 474 invalid view 'view-subquery-with-exists.v1' 475 drop table if exists t1; 476 drop table if exists t2; 477 create table t1 (id int not null, text varchar(20) not null default '', primary key (id)); 478 insert into t1 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text11'), (12, 'text12'); 479 select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null); 480 SQL syntax error: column "t1.id" must appear in the GROUP BY clause or be used in an aggregate function 481 create view v1 as select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null); 482 SQL syntax error: column "t1.id" must appear in the GROUP BY clause or be used in an aggregate function 483 select * from v1; 484 SQL parser error: table "v1" does not exist 485 drop view v1; 486 invalid view 'view-subquery-with-exists.v1' 487 drop table if exists t1; 488 drop table if exists t2; 489 drop table if exists t3; 490 CREATE TABLE t1 (id int NOT NULL, st CHAR(2)); 491 INSERT INTO t1 VALUES (3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY'); 492 CREATE TABLE t2 (id int NOT NULL); 493 INSERT INTO t2 VALUES (7), (5), (1), (3); 494 SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); 495 id st 496 3 FL 497 1 GA 498 7 FL 499 SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) GROUP BY id; 500 SQL syntax error: column "t1.st" must appear in the GROUP BY clause or be used in an aggregate function 501 SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); 502 id st 503 2 GA 504 4 FL 505 SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) GROUP BY id; 506 SQL syntax error: column "t1.st" must appear in the GROUP BY clause or be used in an aggregate function 507 create view v1 as SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); 508 create view v2 as SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) GROUP BY id; 509 SQL syntax error: column "t1.st" must appear in the GROUP BY clause or be used in an aggregate function 510 create view v3 as SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id); 511 create view v4 as SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) GROUP BY id; 512 SQL syntax error: column "t1.st" must appear in the GROUP BY clause or be used in an aggregate function 513 select * from v1; 514 id st 515 3 FL 516 1 GA 517 7 FL 518 select * from v2; 519 SQL parser error: table "v2" does not exist 520 select * from v3; 521 id st 522 2 GA 523 4 FL 524 select * from v4; 525 SQL parser error: table "v4" does not exist 526 drop view v1; 527 drop view v2; 528 invalid view 'view-subquery-with-exists.v2' 529 drop view v3; 530 drop view v4; 531 invalid view 'view-subquery-with-exists.v4' 532 drop table if exists t1; 533 drop table if exists t2; 534 drop table if exists t3; 535 CREATE TABLE t1 (a INT, b INT); 536 INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2); 537 SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1 AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a) GROUP BY a1.a; 538 a count(*) 539 1 3 540 create view v1 as SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1 AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a) GROUP BY a1.a; 541 select * from v1; 542 a count(*) 543 1 3 544 drop view v1; 545 DROP TABLE if exists t1; 546 drop table if exists t1; 547 drop table if exists t2; 548 CREATE TABLE t1 ( a int, b int ); 549 INSERT INTO t1 VALUES (1,1),(2,2),(3,3); 550 SELECT EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a) IS NULL from t1 a; 551 exists (select a from t1 where b = 2 and a.a > t1.a) is null 552 false 553 false 554 false 555 SELECT EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a) IS NOT NULL from t1 a; 556 exists (select a from t1 where b = 2 and a.a < t1.a) is not null 557 true 558 true 559 true 560 SELECT EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a) IS NULL from t1 a; 561 exists (select a from t1 where b = 2 and a.a = t1.a) is null 562 false 563 false 564 false 565 create view v1 as SELECT EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a) IS NULL from t1 a; 566 create view v2 as SELECT EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a) IS NOT NULL from t1 a; 567 create view v3 as SELECT EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a) IS NULL from t1 a; 568 select * from v1; 569 exists (select a from t1 where b = 2 and a.a > t1.a) is null 570 false 571 false 572 false 573 select * from v2; 574 exists (select a from t1 where b = 2 and a.a < t1.a) is not null 575 true 576 true 577 true 578 select * from v3; 579 exists (select a from t1 where b = 2 and a.a = t1.a) is null 580 false 581 false 582 false 583 drop view v1; 584 drop view v2; 585 drop view v3; 586 drop table if exists t1; 587 drop table if exists t1; 588 create table t1 (df decimal(5,1)); 589 insert into t1 values(1.1); 590 select 1.1 * exists(select * from t1); 591 invalid argument operator *, bad value [DECIMAL64 BOOL] 592 create view v1 as select 1.1 * exists(select * from t1); 593 invalid argument operator *, bad value [DECIMAL64 BOOL] 594 select * from v1; 595 SQL parser error: table "v1" does not exist 596 drop view v1; 597 invalid view 'view-subquery-with-exists.v1' 598 drop table if exists t1; 599 drop table if exists t1; 600 CREATE TABLE t1 (i INT); 601 SELECT * FROM t1 WHERE NOT EXISTS 602 ( 603 (SELECT i FROM t1) UNION 604 (SELECT i FROM t1) 605 ); 606 i 607 SELECT * FROM t1 WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); 608 i 609 create view v1 as SELECT * FROM t1 WHERE NOT EXISTS 610 ( 611 (SELECT i FROM t1) UNION 612 (SELECT i FROM t1) 613 ); 614 create view v2 as SELECT * FROM t1 WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); 615 select * from v1; 616 i 617 select * from v2; 618 i 619 drop view v1; 620 drop view v2; 621 drop table if exists t1; 622 drop table if exists t2; 623 drop table if exists t3; 624 CREATE TABLE t1 (id char(4) PRIMARY KEY, c int); 625 CREATE TABLE t2 (c int); 626 INSERT INTO t1 VALUES ('aa', 1); 627 INSERT INTO t2 VALUES (1); 628 SELECT * FROM t1 629 WHERE EXISTS (SELECT c FROM t2 WHERE c=1 630 UNION 631 SELECT c from t2 WHERE c=t1.c); 632 [unknown result because it is related to issue#4354] 633 create view v1 as SELECT * FROM t1 634 WHERE EXISTS (SELECT c FROM t2 WHERE c=1 635 UNION 636 SELECT c from t2 WHERE c=t1.c); 637 [unknown result because it is related to issue#4354] 638 select * from v1; 639 [unknown result because it is related to issue#4354] 640 drop view v1; 641 [unknown result because it is related to issue#4354] 642 INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1); 643 SELECT * FROM t1 644 WHERE EXISTS (SELECT c FROM t2 WHERE c=1 645 UNION 646 SELECT c from t2 WHERE c=t1.c); 647 [unknown result because it is related to issue#4354] 648 create view v1 as SELECT * FROM t1 649 WHERE EXISTS (SELECT c FROM t2 WHERE c=1 650 UNION 651 SELECT c from t2 WHERE c=t1.c); 652 [unknown result because it is related to issue#4354] 653 select * from v1; 654 [unknown result because it is related to issue#4354] 655 drop view v1; 656 [unknown result because it is related to issue#4354] 657 INSERT INTO t2 VALUES (2); 658 CREATE TABLE t3 (c int); 659 INSERT INTO t3 VALUES (1); 660 SELECT * FROM t1 661 WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1 662 UNION 663 SELECT c from t2 WHERE c=t1.c); 664 [unknown result because it is related to issue#4354] 665 create view v1 as SELECT * FROM t1 666 WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1 667 UNION 668 SELECT c from t2 WHERE c=t1.c); 669 [unknown result because it is related to issue#4354] 670 select * from v1; 671 [unknown result because it is related to issue#4354] 672 drop view v1; 673 [unknown result because it is related to issue#4354] 674 drop table if exists t1; 675 drop table if exists t2; 676 drop table if exists t3; 677 CREATE TABLE t1 (a INT); 678 CREATE TABLE t2 (a INT); 679 INSERT INTO t1 VALUES (1),(2); 680 INSERT INTO t2 VALUES (1),(2); 681 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); 682 2 683 2 684 2 685 SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a)); 686 2 687 2 688 2 689 create view v1 as SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a)); 690 create view v2 as SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a)); 691 select * from v1; 692 2 693 2 694 2 695 select * from v2; 696 2 697 2 698 2 699 drop view v1; 700 drop view v2; 701 drop table if exists t1; 702 drop table if exists t2; 703 drop table if exists t1; 704 drop table if exists t2; 705 drop table if exists t3; 706 CREATE TABLE t1 ( c1 int ); 707 INSERT INTO t1 VALUES ( 1 ); 708 INSERT INTO t1 VALUES ( 2 ); 709 INSERT INTO t1 VALUES ( 3 ); 710 INSERT INTO t1 VALUES ( 6 ); 711 CREATE TABLE t2 ( c2 int ); 712 INSERT INTO t2 VALUES ( 1 ); 713 INSERT INTO t2 VALUES ( 4 ); 714 INSERT INTO t2 VALUES ( 5 ); 715 INSERT INTO t2 VALUES ( 6 ); 716 CREATE TABLE t3 ( c3 int ); 717 INSERT INTO t3 VALUES ( 7 ); 718 INSERT INTO t3 VALUES ( 8 ); 719 SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 720 WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL ); 721 c1 c2 722 2 null 723 3 null 724 create view v1 as SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 725 WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL ); 726 select * from v1; 727 c1 c2 728 2 null 729 3 null 730 drop view v1; 731 drop table if exists t1; 732 drop table if exists t2; 733 drop table if exists t3;