github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/subquery/subquery-with-any.result (about) 1 DROP TABLE IF EXISTS t1; 2 DROP TABLE IF EXISTS t2; 3 DROP TABLE IF EXISTS t3; 4 DROP TABLE IF EXISTS t4; 5 create table t1 (a int); 6 create table t2 (a int, b int); 7 create table t3 (a int); 8 create table t4 (a int not null, b int not null); 9 insert into t1 values (2); 10 insert into t2 values (1,7),(2,7); 11 insert into t4 values (4,8),(3,8),(5,9); 12 insert into t3 values (6),(7),(3); 13 select * from t3 where a <> any (select b from t2); 14 a 15 6 16 3 17 select * from t3 where a <> some (select b from t2); 18 a 19 6 20 3 21 select * from t3 where a = some (select b from t2); 22 a 23 7 24 select * from t3 where a = any (select b from t2); 25 a 26 7 27 insert into t2 values (100, 5); 28 select * from t3 where a < any (select b from t2); 29 a 30 6 31 3 32 select * from t3 where a >= any (select b from t2); 33 a 34 6 35 7 36 select * from t3 where a < some (select b from t2); 37 a 38 6 39 3 40 select * from t3 where a >= some (select b from t2); 41 a 42 6 43 7 44 select * from t3 where a >= some (select b from t2); 45 a 46 6 47 7 48 DROP TABLE IF EXISTS t1; 49 DROP TABLE IF EXISTS t2; 50 DROP TABLE IF EXISTS t3; 51 DROP TABLE IF EXISTS t4; 52 create table t1 (s1 char(5)); 53 create table t2 (s1 char(5)); 54 insert into t1 values ('a1'),('a2'),('a3'); 55 insert into t2 values ('a1'),('a2'); 56 select s1, s1 = ANY (SELECT s1 FROM t2) from t1; 57 s1 s1 = ANY (SELECT s1 FROM t2) 58 a1 true 59 a2 true 60 a3 false 61 select s1, s1 < ANY (SELECT s1 FROM t2) from t1; 62 s1 s1 < ANY (SELECT s1 FROM t2) 63 a1 true 64 a2 false 65 a3 false 66 select s1, s1 = ANY (SELECT s1 FROM t2) from t1; 67 s1 s1 = ANY (SELECT s1 FROM t2) 68 a1 true 69 a2 true 70 a3 false 71 DROP TABLE IF EXISTS t1; 72 DROP TABLE IF EXISTS t2; 73 DROP TABLE IF EXISTS t3; 74 create table t2 (a int, b int); 75 create table t3 (a int); 76 insert into t3 values (6),(7),(3); 77 select * from t3 where a >= some (select b from t2); 78 a 79 select * from t3 where a >= some (select b from t2 group by 1); 80 SQL syntax error: column "t2.b" must appear in the GROUP BY clause or be used in an aggregate function 81 select * from t3 where NULL >= any (select b from t2); 82 a 83 select * from t3 where NULL >= any (select b from t2 group by 1); 84 SQL syntax error: column "t2.b" must appear in the GROUP BY clause or be used in an aggregate function 85 select * from t3 where NULL >= some (select b from t2); 86 a 87 select * from t3 where NULL >= some (select b from t2 group by 1); 88 SQL syntax error: column "t2.b" must appear in the GROUP BY clause or be used in an aggregate function 89 insert into t2 values (2,2), (2,1), (3,3), (3,1); 90 DROP TABLE IF EXISTS t1; 91 DROP TABLE IF EXISTS t2; 92 DROP TABLE IF EXISTS t3; 93 CREATE TABLE t1 ( a int, b int ); 94 INSERT INTO t1 VALUES (1,1),(2,2),(3,3); 95 SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 ); 96 a 97 3 98 SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 ); 99 a 100 1 101 SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 ); 102 a 103 2 104 SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 ); 105 a 106 2 107 3 108 SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 ); 109 a 110 1 111 2 112 SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 ); 113 a 114 1 115 3 116 SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2); 117 invalid input: subquery should return 2 columns 118 SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2); 119 invalid input: subquery should return 1 column 120 SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2); 121 a 122 SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2); 123 a 124 1 125 2 126 3 127 SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2); 128 invalid input: subquery should return 2 columns 129 SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2); 130 invalid input: subquery should return 1 column 131 SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2); 132 a 133 SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2); 134 invalid input: subquery should return 2 columns 135 SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2); 136 a 137 2 138 SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2); 139 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 140 SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2); 141 a 142 2 143 SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2); 144 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 145 DROP TABLE IF EXISTS t1; 146 CREATE TABLE t1 ( a double, b double ); 147 INSERT INTO t1 VALUES (1,1),(2,2),(3,3); 148 SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0); 149 a 150 3.0 151 SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0); 152 a 153 1.0 154 SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0); 155 a 156 2.0 157 SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0); 158 a 159 2.0 160 3.0 161 SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0); 162 a 163 1.0 164 2.0 165 SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0); 166 a 167 1.0 168 3.0 169 DROP TABLE IF EXISTS t1; 170 CREATE TABLE t1 ( a char(1), b char(1)); 171 INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3'); 172 SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2'); 173 a 174 3 175 SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2'); 176 a 177 1 178 SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2'); 179 a 180 2 181 SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2'); 182 a 183 2 184 3 185 SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2'); 186 a 187 1 188 2 189 SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2'); 190 a 191 1 192 3 193 DROP TABLE IF EXISTS t1; 194 DROP TABLE IF EXISTS t2; 195 create table t1 (a1 int); 196 create table t2 (b1 int); 197 select * from t1 where a2 > any(select b1 from t2); 198 invalid input: column a2 does not exist 199 select * from t1 where a1 > any(select b1 from t2); 200 a1 201 DROP TABLE IF EXISTS t1; 202 DROP TABLE IF EXISTS t2; 203 create table t1 (s1 char); 204 insert into t1 values ('1'),('2'); 205 select * from t1 where (s1 < any (select s1 from t1)); 206 s1 207 1 208 select * from t1 where not (s1 < any (select s1 from t1)); 209 s1 210 2 211 select * from t1 where (s1+1 = ANY (select s1 from t1)); 212 s1 213 1 214 select * from t1 where NOT(s1+1 = ANY (select s1 from t1)); 215 s1 216 2 217 DROP TABLE IF EXISTS t1; 218 DROP TABLE IF EXISTS t2; 219 CREATE TABLE t1 (s1 CHAR(1)); 220 INSERT INTO t1 VALUES ('a'); 221 SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1); 222 s1 223 a 224 DROP TABLE IF EXISTS t1; 225 DROP TABLE IF EXISTS t2; 226 create table t1 (a integer, b integer); 227 select (select * from t1) = (select 1,2); 228 (select * from t1) = (select 1,2) 229 null 230 select (select 1,2) = (select * from t1); 231 (select 1,2) = (select * from t1) 232 null 233 select (1,2) = ANY (select * from t1); 234 (1,2) = ANY (select * from t1) 235 false 236 select (1,2) != ALL (select * from t1); 237 (1,2) != ALL (select * from t1) 238 true 239 DROP TABLE IF EXISTS t1; 240 select 1 from dual where 1 < any (select 2); 241 1 242 1 243 select 1 from dual where 2 > any (select 1); 244 1 245 1 246 CREATE TABLE `t1` ( 247 `numeropost` int(8) unsigned NOT NULL, 248 `maxnumrep` int(10) unsigned NOT NULL default 0, 249 PRIMARY KEY (`numeropost`) 250 ); 251 INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2); 252 CREATE TABLE `t2` ( 253 `mot` varchar(30) NOT NULL default '', 254 `topic` int(8) unsigned NOT NULL default 0, 255 `dt` date, 256 `pseudo` varchar(35) NOT NULL default '' 257 ); 258 INSERT INTO t2 (mot,topic,dt,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce'); 259 SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic); 260 mot topic dt pseudo 261 joce 40143 2002-10-22 joce 262 joce 43506 2002-10-22 joce 263 SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100); 264 mot topic dt pseudo 265 SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1); 266 Table 'subquery-with-any.t1' doesn't exist 267 SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2); 268 mot topic dt pseudo 269 joce 40143 2002-10-22 joce 270 joce 43506 2002-10-22 joce 271 SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000); 272 mot topic dt pseudo 273 joce 40143 2002-10-22 joce 274 DROP TABLE IF EXISTS t1; 275 DROP TABLE IF EXISTS t2; 276 CREATE TABLE t1 ( a int, b int ); 277 INSERT INTO t1 VALUES (1,1),(2,2),(3,3); 278 SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2); 279 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 280 SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2); 281 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 282 SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2); 283 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 284 SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2); 285 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 286 SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2); 287 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 288 SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2); 289 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 290 SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a); 291 a 292 3 293 SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a); 294 a 295 1 296 SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a); 297 a 298 2 299 SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a); 300 a 301 2 302 3 303 SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a); 304 a 305 1 306 2 307 SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a); 308 a 309 1 310 3 311 SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2); 312 a 313 3 314 SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2); 315 a 316 1 317 SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2); 318 a 319 2 320 SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2); 321 a 322 2 323 3 324 SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2); 325 a 326 1 327 2 328 SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2); 329 a 330 1 331 3 332 DROP TABLE IF EXISTS t1; 333 DROP TABLE IF EXISTS t2; 334 CREATE TABLE `t1` ( `a` int(11) default NULL); 335 insert into t1 values (1); 336 CREATE TABLE `t2` ( `b` int(11) default NULL, `a` int(11) default NULL); 337 insert into t2 values (1,2); 338 select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1); 339 a C 340 1 1 341 DROP TABLE IF EXISTS t1; 342 DROP TABLE IF EXISTS t2; 343 CREATE TABLE t1 ( 344 field1 int NOT NULL, 345 field2 int NOT NULL, 346 field3 int NOT NULL 347 ); 348 CREATE TABLE t2 ( 349 fieldA int NOT NULL, 350 fieldB int NOT NULL 351 ); 352 INSERT INTO t1 VALUES (1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1); 353 INSERT INTO t2 VALUES (1,1), (1,2), (1,3); 354 SELECT field1, field2 355 FROM t1 356 GROUP BY field1, field2 357 HAVING COUNT(*) < ANY (SELECT fieldB 358 FROM t2 WHERE fieldA = field1); 359 SQL syntax error: column "field1" must appear in the GROUP BY clause or be used in an aggregate function 360 DROP TABLE IF EXISTS t1; 361 DROP TABLE IF EXISTS t2; 362 CREATE TABLE t1 ( 363 pk INT NOT NULL PRIMARY KEY, 364 number INT 365 ); 366 INSERT INTO t1 VALUES (8,8); 367 CREATE TABLE t2 ( 368 pk INT NOT NULL PRIMARY KEY, 369 number INT 370 ); 371 INSERT INTO t2 VALUES (1,2); 372 INSERT INTO t2 VALUES (2,8); 373 INSERT INTO t2 VALUES (3,NULL); 374 INSERT INTO t2 VALUES (4,166); 375 SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2 GROUP BY number); 376 pk number 377 8 8 378 SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2); 379 pk number 380 8 8 381 DROP TABLE IF EXISTS t1; 382 DROP TABLE IF EXISTS t2; 383 CREATE TABLE t1 (a varchar(5), b varchar(10)); 384 INSERT INTO t1 VALUES ('AAA', '5'), ('BBB', '4'), ('BBB', '1'), ('CCC', '2'), ('CCC', '7'), ('AAA', '2'), ('AAA', '4'), ('BBB', '3'), ('AAA', '8'); 385 SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a); 386 a b 387 BBB 4 388 CCC 7 389 AAA 8 390 DROP TABLE IF EXISTS t1; 391 DROP TABLE IF EXISTS t2; 392 create table t1 (s1 char); 393 insert into t1 values ('e'); 394 select * from t1 where 'f' > any (select s1 from t1); 395 s1 396 e 397 DROP TABLE IF EXISTS t1; 398 DROP TABLE IF EXISTS t2; 399 CREATE TABLE t1 ( a int, b int ); 400 INSERT INTO t1 VALUES (1,1),(2,2),(3,3); 401 SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2); 402 a 403 3 404 SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2); 405 a 406 1 407 SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2); 408 a 409 2 410 SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2); 411 a 412 2 413 3 414 SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2); 415 a 416 1 417 2 418 SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2); 419 a 420 1 421 3 422 SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2); 423 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 424 SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2); 425 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 426 SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2); 427 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 428 SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2); 429 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 430 SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2); 431 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 432 SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2); 433 SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function 434 DROP TABLE IF EXISTS t1; 435 create table t1 (a int); 436 insert into t1 values (1),(2),(3); 437 update t1 set a=NULL where a=2; 438 select 1 > ANY (SELECT * from t1); 439 1 > ANY (SELECT * from t1) 440 null 441 select 10 > ANY (SELECT * from t1); 442 10 > ANY (SELECT * from t1) 443 true 444 DROP TABLE IF EXISTS t1; 445 create table t1 (a varchar(20)); 446 insert into t1 values ('A'),('BC'),('DEF'); 447 update t1 set a=NULL where a='BC'; 448 select 'A' > ANY (SELECT * from t1); 449 'A' > ANY (SELECT * from t1) 450 null 451 select 'XYZS' > ANY (SELECT * from t1); 452 'XYZS' > ANY (SELECT * from t1) 453 true 454 DROP TABLE IF EXISTS t1; 455 create table t1 (a float); 456 insert into t1 values (1.5),(2.5),(3.5); 457 update t1 set a=NULL where a=2.5; 458 select 1.5 > ANY (SELECT * from t1); 459 1.5 > ANY (SELECT * from t1) 460 null 461 select 10.5 > ANY (SELECT * from t1); 462 10.5 > ANY (SELECT * from t1) 463 true 464 DROP TABLE IF EXISTS t1; 465 create table t1 (s1 int); 466 insert into t1 values (1),(null); 467 select * from t1 where s1 < all (select s1 from t1); 468 s1 469 select s1, s1 < all (select s1 from t1) from t1; 470 s1 s1 < all (select s1 from t1) 471 1 false 472 null null 473 DROP TABLE IF EXISTS t1; 474 CREATE TABLE t1( a INT ); 475 INSERT INTO t1 VALUES (1),(2); 476 CREATE TABLE t2( a INT, b INT ); 477 SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); 478 a 479 1 480 SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); 481 a 482 1 483 SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); 484 a 485 1 486 DROP TABLE IF EXISTS t1; 487 DROP TABLE IF EXISTS t2; 488 CREATE TABLE t1(i INT); 489 INSERT INTO t1 VALUES (1), (2), (3); 490 CREATE TABLE t1s(i INT); 491 INSERT INTO t1s VALUES (10), (20), (30); 492 CREATE TABLE t2s(i INT); 493 INSERT INTO t2s VALUES (100), (200), (300); 494 SELECT * FROM t1 495 WHERE NOT t1.I = ANY 496 ( 497 SELECT t2s.i 498 FROM 499 t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i 500 HAVING t2s.i = 999 501 ); 502 SQL syntax error: column "t2s.i" must appear in the GROUP BY clause or be used in an aggregate function 503 DROP TABLE IF EXISTS t1; 504 DROP TABLE IF EXISTS t1s; 505 DROP TABLE IF EXISTS t2s;