github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/view/view-subquery-with-in.result (about) 1 SELECT 1 IN (SELECT 1); 2 1 in (select 1) 3 true 4 create view v1 as SELECT 1 IN (SELECT 1); 5 select * from v1; 6 1 in (select 1) 7 true 8 drop view v1; 9 SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); 10 1 11 1 12 create view v1 as SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); 13 select * from v1; 14 1 15 1 16 drop view v1; 17 SELECT 1 FROM (SELECT 1 as a) b WHERE 1 not IN (SELECT (SELECT a)); 18 1 19 create view v1 as SELECT 1 FROM (SELECT 1 as a) b WHERE 1 not IN (SELECT (SELECT a)); 20 select * from v1; 21 1 22 drop view v1; 23 SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id); 24 id 25 1 26 SELECT * FROM (SELECT 1) a WHERE 1 IN (SELECT 1,1); 27 invalid input: subquery returns more than 1 column 28 SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *); 29 SQL parser error: No tables used 30 create view v1 as SELECT * FROM (SELECT 1 as id) b WHERE id IN (SELECT * FROM (SELECT 1 as id) c ORDER BY id); 31 create view v2 as SELECT * FROM (SELECT 1) a WHERE 1 IN (SELECT 1,1); 32 invalid input: subquery returns more than 1 column 33 create view v3 as SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *); 34 SQL parser error: No tables used 35 select * from v1; 36 id 37 1 38 select * from v2; 39 SQL parser error: table "v2" does not exist 40 select * from v3; 41 SQL parser error: table "v3" does not exist 42 drop view v1; 43 drop view v2; 44 invalid view 'view-subquery-with-in.v2' 45 drop view v3; 46 invalid view 'view-subquery-with-in.v3' 47 SELECT ((0,1) NOT IN (SELECT NULL,1)) IS NULL; 48 ((0, 1) not in (select null, 1)) is null 49 true 50 create view v1 as SELECT ((0,1) NOT IN (SELECT NULL,1)) IS NULL; 51 select * from v1; 52 ((0, 1) not in (select null, 1)) is null 53 true 54 drop view v1; 55 drop table if exists t1; 56 drop table if exists t2; 57 drop table if exists t3; 58 create table t1 (a int); 59 create table t2 (a int, b int); 60 create table t3 (a int); 61 create table t4 (a int not null, b int not null); 62 insert into t1 values (2); 63 insert into t2 values (1,7),(2,7); 64 insert into t4 values (4,8),(3,8),(5,9); 65 insert into t2 values (100, 5); 66 select * from t3 where a in (select b from t2); 67 a 68 select * from t3 where a in (select b from t2 where b > 7); 69 a 70 select * from t3 where a not in (select b from t2); 71 a 72 create view v1 as select * from t3 where a in (select b from t2); 73 create view v2 as select * from t3 where a in (select b from t2 where b > 7); 74 create view v3 as select * from t3 where a not in (select b from t2); 75 select * from v1; 76 a 77 select * from v2; 78 a 79 select * from v3; 80 a 81 drop view v1; 82 drop view v2; 83 drop view v3; 84 SELECT 0 IN (SELECT 1 FROM t1 a); 85 0 in (select 1 from t1 as a) 86 false 87 create view v1 as SELECT 0 IN (SELECT 1 FROM t1 a); 88 select * from v1; 89 0 in (select 1 from t1 as a) 90 false 91 drop view v1; 92 select * from t3 where a in (select a,b from t2); 93 invalid input: subquery returns more than 1 column 94 select * from t3 where a in (select * from t2); 95 invalid input: subquery returns more than 1 column 96 create view v1 as select * from t3 where a in (select a,b from t2); 97 invalid input: subquery returns more than 1 column 98 create view v2 as select * from t3 where a in (select * from t2); 99 invalid input: subquery returns more than 1 column 100 select * from v1; 101 SQL parser error: table "v1" does not exist 102 select * from v2; 103 SQL parser error: table "v2" does not exist 104 drop view v1; 105 invalid view 'view-subquery-with-in.v1' 106 drop view v2; 107 invalid view 'view-subquery-with-in.v2' 108 drop table if exists t1; 109 drop table if exists t2; 110 drop table if exists t3; 111 create table t1 (s1 char(5), index s1(s1)); 112 create table t2 (s1 char(5), index s1(s1)); 113 insert into t1 values ('a1'),('a2'),('a3'); 114 insert into t2 values ('a1'),('a2'); 115 select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; 116 s1 s1 not in (select s1 from t2) 117 a1 false 118 a2 false 119 a3 true 120 select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; 121 s1 s1 not in (select s1 from t2 where s1 < a2) 122 a1 false 123 a2 true 124 a3 true 125 create view v1 as select s1, s1 NOT IN (SELECT s1 FROM t2) from t1; 126 create view v2 as select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1; 127 select * from v1; 128 s1 s1 not in (select s1 from t2) 129 a1 false 130 a2 false 131 a3 true 132 select * from v2; 133 s1 s1 not in (select s1 from t2 where s1 < a2) 134 a1 false 135 a2 true 136 a3 true 137 drop view v1; 138 drop view v2; 139 drop table if exists t1; 140 drop table if exists t2; 141 create table t1(val varchar(10)); 142 insert into t1 values ('aaa'), ('bbb'),('eee'),('mmm'),('ppp'); 143 select count(*) from t1 as w1 where w1.val in (select w2.val from t1 as w2 where w2.val like 'm%') and w1.val in (select w3.val from t1 as w3 where w3.val like 'e%'); 144 count(*) 145 0 146 create view v1 as select count(*) from t1 as w1 where w1.val in (select w2.val from t1 as w2 where w2.val like 'm%') and w1.val in (select w3.val from t1 as w3 where w3.val like 'e%'); 147 select * from v1; 148 count(*) 149 0 150 drop view v1; 151 DROP TABLE IF EXISTS t1; 152 create table t1 (id int not null, text varchar(20) not null default '', primary key (id)); 153 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'); 154 select * from t1 where id not in (select id from t1 where id < 8); 155 id text 156 8 text8 157 9 text9 158 10 text10 159 11 text11 160 12 text12 161 create view v1 as select * from t1 where id not in (select id from t1 where id < 8); 162 select * from v1; 163 id text 164 8 text8 165 9 text9 166 10 text10 167 11 text11 168 12 text12 169 drop view v1; 170 drop table if exists t1; 171 drop table if exists t2; 172 drop table if exists t3; 173 CREATE TABLE t1 (a int); 174 CREATE TABLE t2 (a int, b int); 175 CREATE TABLE t3 (b int NOT NULL); 176 INSERT INTO t1 VALUES (1), (2), (3), (4); 177 INSERT INTO t2 VALUES (1,10), (3,30); 178 select * from t1 where t1.a in (SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.a=t1.a); 179 a 180 1 181 2 182 3 183 4 184 SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t3.b IS NOT NULL OR t2.a > 10; 185 a b b 186 SELECT * FROM t1 WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t3.b IS NOT NULL OR t2.a > 10); 187 a 188 1 189 2 190 3 191 4 192 create view v1 as select * from t1 where t1.a in (SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.a=t1.a); 193 create view v2 as SELECT * FROM t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t3.b IS NOT NULL OR t2.a > 10; 194 constraint violation: duplicate column "b" 195 create view v3 as SELECT * FROM t1 WHERE t1.a NOT IN (SELECT a FROM t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t3.b IS NOT NULL OR t2.a > 10); 196 select * from v1; 197 a 198 1 199 2 200 3 201 4 202 select * from v2; 203 SQL parser error: table "v2" does not exist 204 select * from v3; 205 a 206 1 207 2 208 3 209 4 210 drop view v1; 211 drop view v2; 212 invalid view 'view-subquery-with-in.v2' 213 drop view v3; 214 drop table if exists t1; 215 drop table if exists t2; 216 drop table if exists t3; 217 CREATE TABLE t1(a int, INDEX (a)); 218 INSERT INTO t1 VALUES (1), (3), (5), (7); 219 INSERT INTO t1 VALUES (NULL); 220 CREATE TABLE t2(a int); 221 INSERT INTO t2 VALUES (1),(2),(3); 222 SELECT a, a IN (SELECT a FROM t1) FROM t2; 223 a a in (select a from t1) 224 1 true 225 2 null 226 3 true 227 create view v1 as SELECT a, a IN (SELECT a FROM t1) FROM t2; 228 select * from v1; 229 a a in (select a from t1) 230 1 true 231 2 null 232 3 true 233 drop view v1; 234 drop table if exists t1; 235 drop table if exists t2; 236 drop table if exists t3; 237 CREATE table t1 ( c1 int ); 238 INSERT INTO t1 VALUES ( 1 ); 239 INSERT INTO t1 VALUES ( 2 ); 240 INSERT INTO t1 VALUES ( 3 ); 241 CREATE TABLE t2 ( c2 int ); 242 INSERT INTO t2 VALUES ( 1 ); 243 INSERT INTO t2 VALUES ( 4 ); 244 INSERT INTO t2 VALUES ( 5 ); 245 SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1); 246 c1 c2 247 1 1 248 SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) ); 249 c1 c2 250 1 1 251 create view v1 as SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN (1); 252 create view v2 as SELECT * FROM t1 LEFT JOIN t2 ON c1 = c2 WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) ); 253 select * from v1; 254 c1 c2 255 1 1 256 select * from v2; 257 c1 c2 258 1 1 259 drop view v1; 260 drop view v2; 261 drop table if exists t1; 262 drop table if exists t2; 263 DROP TABLE IF EXISTS c; 264 CREATE TABLE `c` ( 265 `int_nokey` int(11) NOT NULL, 266 `int_key` int(11) NOT NULL 267 ); 268 INSERT INTO `c` VALUES (9,9), (0,0), (8,6), (3,6), (7,6), (0,4), 269 (1,7), (9,4), (0,8), (9,4), (0,7), (5,5), (0,0), (8,5), (8,7), 270 (5,2), (1,8), (7,0), (0,9), (9,5); 271 SELECT * FROM c WHERE `int_key` IN (SELECT `int_nokey`); 272 int_nokey int_key 273 9 9 274 0 0 275 5 5 276 0 0 277 create view v1 as SELECT * FROM c WHERE `int_key` IN (SELECT `int_nokey`); 278 select * from v1; 279 int_nokey int_key 280 9 9 281 0 0 282 5 5 283 0 0 284 drop view v1; 285 DROP TABLE IF EXISTS c; 286 drop table if exists t1; 287 drop table if exists t2; 288 CREATE TABLE t1(c INT); 289 CREATE TABLE t2(a INT, b INT); 290 INSERT INTO t2 VALUES (1, 10), (2, NULL); 291 INSERT INTO t1 VALUES (1), (3); 292 SELECT * FROM t2 WHERE b NOT IN (SELECT max(t.c) FROM t1, t1 t WHERE t.c>10); 293 a b 294 create view v1 as SELECT * FROM t2 WHERE b NOT IN (SELECT max(t.c) FROM t1, t1 t WHERE t.c>10); 295 select * from v1; 296 a b 297 drop view v1; 298 drop table if exists t1; 299 drop table if exists t2; 300 CREATE TABLE t1 ( 301 a1 char(8) DEFAULT NULL, 302 a2 char(8) DEFAULT NULL 303 ); 304 CREATE TABLE t2 ( 305 b1 char(8) DEFAULT NULL, 306 b2 char(8) DEFAULT NULL 307 ); 308 INSERT INTO t1 VALUES 309 ('1 - 00', '2 - 00'),('1 - 01', '2 - 01'),('1 - 02', '2 - 02'); 310 INSERT INTO t2 VALUES 311 ('1 - 01', '2 - 01'),('1 - 01', '2 - 01'), 312 ('1 - 02', '2 - 02'),('1 - 02', '2 - 02'),('1 - 03', '2 - 03'); 313 SELECT * FROM t2 WHERE b1 NOT IN ('1 - 00', '1 - 01', '1 - 02'); 314 b1 b2 315 1 - 03 2 - 03 316 create view v1 as SELECT * FROM t2 WHERE b1 NOT IN ('1 - 00', '1 - 01', '1 - 02'); 317 select * from v1; 318 b1 b2 319 1 - 03 2 - 03 320 drop view v1; 321 drop table if exists t1; 322 drop table if exists t2; 323 drop table if exists t1; 324 drop table if exists t2; 325 drop table if exists t3; 326 drop table if exists t4; 327 CREATE TABLE `t1` ( 328 `numeropost` int(8) unsigned NOT NULL, 329 `maxnumrep` int(10) unsigned NOT NULL default 0, 330 PRIMARY KEY (`numeropost`) 331 ) ; 332 INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2); 333 CREATE TABLE `t2` ( 334 `mot` varchar(30) NOT NULL default '', 335 `topic` int(8) unsigned NOT NULL default 0, 336 `dt` date, 337 `pseudo` varchar(35) NOT NULL default '', 338 PRIMARY KEY (`topic`) 339 ) ; 340 INSERT INTO t2 (mot,topic,dt,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce'); 341 SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic); 342 mot topic dt pseudo 343 joce 40143 2002-10-22 joce 344 joce 43506 2002-10-22 joce 345 SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100); 346 mot topic dt pseudo 347 SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1); 348 [unknown result because it is related to issue#3307] 349 create view v3 as SELECT * from t2 where topic IN (SELECT SUM(topic) FROM t1); 350 [unknown result because it is related to issue#3307] 351 select * from v3; 352 [unknown result because it is related to issue#3307] 353 SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000); 354 mot topic dt pseudo 355 joce 40143 2002-10-22 joce 356 SELECT * from t2 where topic NOT IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000); 357 mot topic dt pseudo 358 joce 43506 2002-10-22 joce 359 SELECT * FROM t2 WHERE mot IN (SELECT 'joce'); 360 mot topic dt pseudo 361 joce 40143 2002-10-22 joce 362 joce 43506 2002-10-22 joce 363 create view v1 as SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic); 364 create view v2 as SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100); 365 create view v4 as SELECT * from t2 where topic IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000); 366 create view v5 as SELECT * from t2 where topic NOT IN (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000); 367 create view v6 as SELECT * FROM t2 WHERE mot IN (SELECT 'joce'); 368 select * from v1; 369 mot topic dt pseudo 370 joce 40143 2002-10-22 joce 371 joce 43506 2002-10-22 joce 372 select * from v2; 373 mot topic dt pseudo 374 select * from v4; 375 mot topic dt pseudo 376 joce 40143 2002-10-22 joce 377 select * from v5; 378 mot topic dt pseudo 379 joce 43506 2002-10-22 joce 380 select * from v6; 381 mot topic dt pseudo 382 joce 40143 2002-10-22 joce 383 joce 43506 2002-10-22 joce 384 drop view v1; 385 drop view v2; 386 drop view v3; 387 invalid view 'view-subquery-with-in.v3' 388 drop view v4; 389 drop view v5; 390 drop view v6; 391 drop table if exists t1; 392 drop table if exists t2; 393 create table t1 (a int); 394 create table t2 (a int); 395 insert into t1 values (1),(2); 396 insert into t2 values (0),(1),(2),(3); 397 select a from t2 where a in (select a from t1); 398 a 399 1 400 2 401 select a from t2 having a in (select a from t1); 402 SQL syntax error: column "t2.a" must appear in the GROUP BY clause or be used in an aggregate function 403 create view v1 as select a from t2 where a in (select a from t1); 404 create view v2 as select a from t2 having a in (select a from t1); 405 SQL syntax error: column "t2.a" must appear in the GROUP BY clause or be used in an aggregate function 406 select * from v1; 407 a 408 1 409 2 410 select * from v2; 411 SQL parser error: table "v2" does not exist 412 drop view v1; 413 drop view v2; 414 invalid view 'view-subquery-with-in.v2' 415 drop table if exists t1; 416 drop table if exists t2; 417 create table t1 (oref int, grp int, ie int) ; 418 insert into t1 (oref, grp, ie) values(1, 1, 1),(1, 1, 1), (1, 2, NULL),(2, 1, 3),(3, 1, 4),(3, 2, NULL); 419 create table t2 (oref int, a int); 420 insert into t2 values(1, 1),(2, 2),(3, 3), (4, NULL),(2, NULL); 421 create table t3 (a int); 422 insert into t3 values (NULL), (NULL); 423 select a, oref, a in (select max(ie) from t1 where oref=t2.oref group by grp) Z from t2; 424 a oref z 425 1 1 true 426 2 2 false 427 3 3 null 428 null 4 false 429 null 2 null 430 select a, oref from t2 where a in (select max(ie) from t1 where oref=t2.oref group by grp); 431 a oref 432 1 1 433 select a, oref, a in ( 434 select max(ie) from t1 where oref=t2.oref group by grp union 435 select max(ie) from t1 where oref=t2.oref group by grp 436 ) Z from t2; 437 a oref z 438 1 1 true 439 2 2 false 440 3 3 null 441 null 4 false 442 null 2 null 443 select a in (select max(ie) from t1 where oref=4 group by grp) from t3; 444 a in (select max(ie) from t1 where oref = 4 group by grp) 445 false 446 false 447 create view v1 as select a, oref, a in (select max(ie) from t1 where oref=t2.oref group by grp) Z from t2; 448 create view v2 as select a, oref from t2 where a in (select max(ie) from t1 where oref=t2.oref group by grp); 449 create view v3 as select a, oref, a in ( 450 select max(ie) from t1 where oref=t2.oref group by grp union 451 select max(ie) from t1 where oref=t2.oref group by grp 452 ) Z from t2; 453 create view v4 as select a in (select max(ie) from t1 where oref=4 group by grp) from t3; 454 select * from v1; 455 a oref z 456 1 1 true 457 2 2 false 458 3 3 null 459 null 4 false 460 null 2 null 461 select * from v2; 462 a oref 463 1 1 464 select * from v3; 465 a oref z 466 1 1 true 467 2 2 false 468 3 3 null 469 null 4 false 470 null 2 null 471 select * from v4; 472 a in (select max(ie) from t1 where oref = 4 group by grp) 473 false 474 false 475 drop view v1; 476 drop view v2; 477 drop view v3; 478 drop view v4; 479 drop table if exists t1; 480 drop table if exists t2; 481 CREATE TABLE t2 (id int(11) default NULL); 482 INSERT INTO t2 VALUES (1),(2); 483 SELECT * FROM t2 WHERE id IN (SELECT 1); 484 id 485 1 486 create view v1 as SELECT * FROM t2 WHERE id IN (SELECT 1); 487 select * from v1; 488 id 489 1 490 select * from v1; 491 id 492 1 493 SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); 494 id 495 1 496 SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); 497 id 498 2 499 SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3); 500 id 501 SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); 502 id 503 2 504 SELECT * FROM t2 WHERE id NOT IN (SELECT 5 UNION SELECT 2); 505 id 506 1 507 create view v2 as SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3); 508 create view v3 as SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); 509 create view v4 as SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3); 510 create view v5 as SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2); 511 create view v6 as SELECT * FROM t2 WHERE id NOT IN (SELECT 5 UNION SELECT 2); 512 select * from v2; 513 id 514 1 515 select * from v3; 516 id 517 2 518 select * from v4; 519 id 520 select * from v5; 521 id 522 2 523 select * from v6; 524 id 525 1 526 drop view v1; 527 drop view v2; 528 drop view v3; 529 drop view v4; 530 drop view v5; 531 drop view v6; 532 drop table if exists t1; 533 drop table if exists t2; 534 create table t1 (a int); 535 insert into t1 values (1),(2),(3); 536 select 1 IN (SELECT * from t1); 537 1 in (select * from t1) 538 true 539 select 10 IN (SELECT * from t1); 540 10 in (select * from t1) 541 false 542 select NULL IN (SELECT * from t1); 543 null in (select * from t1) 544 null 545 update t1 set a=NULL where a=2; 546 select 1 IN (SELECT * from t1); 547 1 in (select * from t1) 548 true 549 select 3 IN (SELECT * from t1); 550 3 in (select * from t1) 551 true 552 select 10 IN (SELECT * from t1); 553 10 in (select * from t1) 554 null 555 create view v1 as select 1 IN (SELECT * from t1); 556 create view v2 as select 10 IN (SELECT * from t1); 557 create view v3 as select NULL IN (SELECT * from t1); 558 create view v5 as select 1 IN (SELECT * from t1); 559 create view v6 as select 3 IN (SELECT * from t1); 560 create view v7 as select 10 IN (SELECT * from t1); 561 select * from v1; 562 1 in (select * from t1) 563 true 564 select * from v2; 565 10 in (select * from t1) 566 null 567 select * from v3; 568 null in (select * from t1) 569 null 570 select * from v5; 571 1 in (select * from t1) 572 true 573 select * from v6; 574 3 in (select * from t1) 575 true 576 select * from v7; 577 10 in (select * from t1) 578 null 579 drop view v1; 580 drop view v2; 581 drop view v3; 582 drop view v5; 583 drop view v6; 584 drop view v7; 585 DROP TABLE IF EXISTS t1; 586 create table t1 (a varchar(20)); 587 insert into t1 values ('A'),('BC'),('DEF'); 588 select 'A' IN (SELECT * from t1); 589 A in (select * from t1) 590 true 591 select 'XYZS' IN (SELECT * from t1); 592 XYZS in (select * from t1) 593 false 594 select NULL IN (SELECT * from t1); 595 null in (select * from t1) 596 null 597 create view v1 as select 'A' IN (SELECT * from t1); 598 create view v2 as select 'XYZS' IN (SELECT * from t1); 599 create view v3 as select NULL IN (SELECT * from t1); 600 select * from v1; 601 A in (select * from t1) 602 true 603 select * from v2; 604 XYZS in (select * from t1) 605 false 606 select * from v3; 607 null in (select * from t1) 608 null 609 drop view v1; 610 drop view v2; 611 drop view v3; 612 update t1 set a=NULL where a='BC'; 613 select 'A' IN (SELECT * from t1); 614 A in (select * from t1) 615 true 616 select 'DEF' IN (SELECT * from t1); 617 DEF in (select * from t1) 618 true 619 select 'XYZS' IN (SELECT * from t1); 620 XYZS in (select * from t1) 621 null 622 create view v1 as select 'A' IN (SELECT * from t1); 623 create view v2 as select 'DEF' IN (SELECT * from t1); 624 create view v3 as select 'XYZS' IN (SELECT * from t1); 625 select * from v1; 626 A in (select * from t1) 627 true 628 select * from v2; 629 DEF in (select * from t1) 630 true 631 select * from v3; 632 XYZS in (select * from t1) 633 null 634 drop view v1; 635 drop view v2; 636 drop view v3; 637 DROP TABLE IF EXISTS t1; 638 create table t1 (a float); 639 insert into t1 values (1.5),(2.5),(3.5); 640 select 1.5 IN (SELECT * from t1); 641 1.5 in (select * from t1) 642 true 643 select 10.5 IN (SELECT * from t1); 644 10.5 in (select * from t1) 645 false 646 select NULL IN (SELECT * from t1); 647 null in (select * from t1) 648 null 649 create view v1 as select 1.5 IN (SELECT * from t1); 650 create view v2 as select 10.5 IN (SELECT * from t1); 651 create view v3 as select NULL IN (SELECT * from t1); 652 select * from v1; 653 1.5 in (select * from t1) 654 true 655 select * from v2; 656 10.5 in (select * from t1) 657 false 658 select * from v3; 659 null in (select * from t1) 660 null 661 drop view v1; 662 drop view v2; 663 drop view v3; 664 update t1 set a=NULL where a=2.5; 665 select 1.5 IN (SELECT * from t1); 666 1.5 in (select * from t1) 667 true 668 select 3.5 IN (SELECT * from t1); 669 3.5 in (select * from t1) 670 true 671 select 10.5 IN (SELECT * from t1); 672 10.5 in (select * from t1) 673 null 674 create view v1 as select 1.5 IN (SELECT * from t1); 675 create view v2 as select 3.5 IN (SELECT * from t1); 676 create view v3 as select 10.5 IN (SELECT * from t1); 677 select * from v1; 678 1.5 in (select * from t1) 679 true 680 select * from v2; 681 3.5 in (select * from t1) 682 true 683 select * from v3; 684 10.5 in (select * from t1) 685 null 686 drop view v1; 687 drop view v2; 688 drop view v3; 689 drop table if exists t1; 690 drop table if exists t2; 691 CREATE TABLE t1 (a int(11) NOT NULL default 0, PRIMARY KEY (a)); 692 CREATE TABLE t2 (a int(11) default 0, INDEX (a)); 693 CREATE TABLE t3 (a int(11) default 0); 694 table t3 already exists 695 INSERT INTO t3 VALUES (1),(2),(3); 696 INSERT INTO t1 VALUES (1),(2),(3),(4); 697 INSERT INTO t2 VALUES (1),(2),(3); 698 SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; 699 a t1.a in (select t2.a from t2) 700 1 true 701 2 true 702 3 true 703 4 false 704 SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; 705 a t1.a in (select t2.a from t2 cross join t3 where t3.a = t2.a) 706 1 true 707 2 true 708 3 true 709 4 false 710 create view v1 as SELECT t1.a, t1.a in (select t2.a from t2) FROM t1; 711 create view v2 as SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; 712 select * from v1; 713 a t1.a in (select t2.a from t2) 714 1 true 715 2 true 716 3 true 717 4 false 718 select * from v2; 719 a t1.a in (select t2.a from t2 cross join t3 where t3.a = t2.a) 720 1 true 721 2 true 722 3 true 723 4 false 724 drop view v1; 725 drop view v2; 726 drop table if exists t1; 727 drop table if exists t2; 728 drop table if exists t3; 729 create table t1 (a int); 730 insert into t1 values (-1), (-4), (-2), (NULL); 731 select -10 IN (select a from t1); 732 -10 in (select a from t1) 733 null 734 create view v1 as select -10 IN (select a from t1); 735 select * from v1; 736 -10 in (select a from t1) 737 null 738 drop view v1; 739 DROP TABLE IF EXISTS t1; 740 create table t1 (a float); 741 select 10.5 IN (SELECT * from t1 LIMIT 1); 742 10.5 in (select * from t1 limit 1) 743 false 744 select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5); 745 [unknown result because it is related to issue#4354] 746 select 10.5 IN (SELECT * from t1 UNION SELECT 1.5 LIMIT 1); 747 10.5 in (select * from t1 union select 1.5 limit 1) 748 false 749 create view v1 as select 10.5 IN (SELECT * from t1 LIMIT 1); 750 create view v2 as select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5); 751 create view v3 as select 10.5 IN (SELECT * from t1 UNION SELECT 1.5 LIMIT 1); 752 select * from v1; 753 10.5 in (select * from t1 limit 1) 754 false 755 select * from v2; 756 [unknown result because it is related to issue#4354] 757 select * from v3; 758 10.5 in (select * from t1 union select 1.5 limit 1) 759 false 760 drop view v1; 761 drop view v2; 762 drop view v3; 763 DROP TABLE IF EXISTS t1; 764 create table t1 (a int, b real, c varchar(10)); 765 insert into t1 values (1, 1, 'a'), (2,2,'b'), (NULL, 2, 'b'); 766 select (1, 1, 'a') IN (select a,b,c from t1); 767 (1, 1, a) in (select a, b, c from t1) 768 true 769 select (1, 2, 'a') IN (select a,b,c from t1); 770 (1, 2, a) in (select a, b, c from t1) 771 false 772 select (1, 1, 'a') IN (select b,a,c from t1); 773 (1, 1, a) in (select b, a, c from t1) 774 true 775 select (1, 1, 'a') IN (select a,b,c from t1 where a is not null); 776 (1, 1, a) in (select a, b, c from t1 where a is not null) 777 true 778 select (1, 2, 'a') IN (select a,b,c from t1 where a is not null); 779 (1, 2, a) in (select a, b, c from t1 where a is not null) 780 false 781 select (1, 1, 'a') IN (select b,a,c from t1 where a is not null); 782 (1, 1, a) in (select b, a, c from t1 where a is not null) 783 true 784 select (1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a'); 785 (1, 1, a) in (select a, b, c from t1 where c = b or c = a) 786 true 787 select (1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a'); 788 (1, 2, a) in (select a, b, c from t1 where c = b or c = a) 789 false 790 select (1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a'); 791 (1, 1, a) in (select b, a, c from t1 where c = b or c = a) 792 true 793 select (1, 1, 'a') IN (select b,a,c from t1 limit 2); 794 (1, 1, a) in (select b, a, c from t1 limit 2) 795 true 796 create view v1 as select (1, 1, 'a') IN (select a,b,c from t1); 797 create view v2 as select (1, 2, 'a') IN (select a,b,c from t1); 798 create view v3 as select (1, 1, 'a') IN (select b,a,c from t1); 799 create view v4 as select (1, 1, 'a') IN (select a,b,c from t1 where a is not null); 800 create view v5 as select (1, 2, 'a') IN (select a,b,c from t1 where a is not null); 801 create view v6 as select (1, 1, 'a') IN (select b,a,c from t1 where a is not null); 802 create view v7 as select (1, 1, 'a') IN (select a,b,c from t1 where c='b' or c='a'); 803 create view v8 as select (1, 2, 'a') IN (select a,b,c from t1 where c='b' or c='a'); 804 create view v9 as select (1, 1, 'a') IN (select b,a,c from t1 where c='b' or c='a'); 805 create view v10 as select (1, 1, 'a') IN (select b,a,c from t1 limit 2); 806 select * from v1; 807 (1, 1, a) in (select a, b, c from t1) 808 true 809 select * from v2; 810 (1, 2, a) in (select a, b, c from t1) 811 false 812 select * from v3; 813 (1, 1, a) in (select b, a, c from t1) 814 true 815 select * from v4; 816 (1, 1, a) in (select a, b, c from t1 where a is not null) 817 true 818 select * from v5; 819 (1, 2, a) in (select a, b, c from t1 where a is not null) 820 false 821 select * from v6; 822 (1, 1, a) in (select b, a, c from t1 where a is not null) 823 true 824 select * from v7; 825 (1, 1, a) in (select a, b, c from t1 where c = b or c = a) 826 true 827 select * from v8; 828 (1, 2, a) in (select a, b, c from t1 where c = b or c = a) 829 false 830 select * from v9; 831 (1, 1, a) in (select b, a, c from t1 where c = b or c = a) 832 true 833 select * from v10; 834 (1, 1, a) in (select b, a, c from t1 limit 2) 835 true 836 drop view v1; 837 drop view v2; 838 drop view v3; 839 drop view v4; 840 drop view v5; 841 drop view v6; 842 drop view v7; 843 drop view v8; 844 drop view v9; 845 drop view v10; 846 DROP TABLE IF EXISTS t1; 847 create table t1 (a integer, b integer); 848 select (1,(2,2)) in (select * from t1 ); 849 [unknown result because it is related to issue#7691] 850 select (1,(2,2)) = (select * from t1 ); 851 [unknown result because it is related to issue#7691] 852 select (select * from t1) = (1,(2,2)); 853 [unknown result because it is related to issue#7691] 854 create view v1 as select (1,(2,2)) in (select * from t1 ); 855 [unknown result because it is related to issue#7691] 856 create view v2 as select (1,(2,2)) = (select * from t1 ); 857 [unknown result because it is related to issue#7691] 858 create view v3 as select (select * from t1) = (1,(2,2)); 859 [unknown result because it is related to issue#7691] 860 select * from v1; 861 [unknown result because it is related to issue#7691] 862 select * from v2; 863 [unknown result because it is related to issue#7691] 864 select * from v3; 865 [unknown result because it is related to issue#7691] 866 drop view v1; 867 [unknown result because it is related to issue#7691] 868 drop view v2; 869 [unknown result because it is related to issue#7691] 870 drop view v3; 871 [unknown result because it is related to issue#7691] 872 DROP TABLE IF EXISTS t1; 873 drop table if exists t2; 874 CREATE TABLE t1 (a1 INT, a2 INT); 875 CREATE TABLE t2 (b1 INT, b2 INT); 876 INSERT INTO t1 VALUES (100, 200); 877 INSERT INTO t1 VALUES (101, 201); 878 INSERT INTO t2 VALUES (101, 201); 879 INSERT INTO t2 VALUES (103, 203); 880 SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1; 881 ((a1, a2) in (select * from t2 where b2 > 0)) is null 882 false 883 false 884 create view v1 as SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1; 885 select * from v1; 886 ((a1, a2) in (select * from t2 where b2 > 0)) is null 887 false 888 false 889 drop view v1; 890 DROP TABLE IF EXISTS t1; 891 drop table if exists t2; 892 create table t1 (a int, b int); 893 insert into t1 values (0,0), (2,2), (3,3); 894 create table t2 (a int, b int); 895 insert into t2 values (1,1), (3,3); 896 select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; 897 a b z 898 0 0 false 899 2 2 false 900 3 3 true 901 create view v1 as select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; 902 select * from v1; 903 a b z 904 0 0 false 905 2 2 false 906 3 3 true 907 drop view v1; 908 insert into t2 values (NULL,4); 909 select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; 910 a b z 911 0 0 false 912 2 2 false 913 3 3 true 914 create view v1 as select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; 915 select * from v1; 916 a b z 917 0 0 false 918 2 2 false 919 3 3 true 920 drop view v1; 921 DROP TABLE IF EXISTS t1; 922 drop table if exists t2; 923 DROP TABLE IF EXISTS t1; 924 CREATE TABLE t1 (a INT); 925 INSERT INTO t1 VALUES (1), (2), (11); 926 SELECT a, (11, 12) = (SELECT a, 22), (11, 12) IN (SELECT a, 22) FROM t1 GROUP BY t1.a; 927 [unknown result because it is related to issue#7691] 928 SELECT a, (11, 12) = (SELECT a, 12), (11, 12) IN (SELECT a, 12) FROM t1 GROUP BY t1.a; 929 [unknown result because it is related to issue#7691] 930 SELECT a, (11, 12) = (SELECT a, 22), (11, 12) IN (SELECT a, 22) FROM t1; 931 [unknown result because it is related to issue#7691] 932 SELECT a, (11, 12) = (SELECT a, 12), (11, 12) IN (SELECT a, 12) FROM t1; 933 [unknown result because it is related to issue#7691] 934 SELECT a AS x, (11, 12) = (SELECT MAX(x), 22), (11, 12) IN (SELECT MAX(x), 22) FROM t1; 935 [unknown result because it is related to issue#7691] 936 SELECT a AS x, (11, 12) = (SELECT MAX(x), 12), (11, 12) IN (SELECT MAX(x), 12) FROM t1; 937 [unknown result because it is related to issue#7691] 938 create view v1 as SELECT a, (11, 12) = (SELECT a, 22), (11, 12) IN (SELECT a, 22) FROM t1 GROUP BY t1.a; 939 [unknown result because it is related to issue#7691] 940 create view v2 as SELECT a, (11, 12) = (SELECT a, 12), (11, 12) IN (SELECT a, 12) FROM t1 GROUP BY t1.a; 941 [unknown result because it is related to issue#7691] 942 create view v3 as SELECT a, (11, 12) = (SELECT a, 22), (11, 12) IN (SELECT a, 22) FROM t1; 943 [unknown result because it is related to issue#7691] 944 create view v4 as SELECT a, (11, 12) = (SELECT a, 12), (11, 12) IN (SELECT a, 12) FROM t1; 945 [unknown result because it is related to issue#7691] 946 create view v5 as SELECT a AS x, (11, 12) = (SELECT MAX(x), 22), (11, 12) IN (SELECT MAX(x), 22) FROM t1; 947 [unknown result because it is related to issue#7691] 948 create view v6 as SELECT a AS x, (11, 12) = (SELECT MAX(x), 12), (11, 12) IN (SELECT MAX(x), 12) FROM t1; 949 [unknown result because it is related to issue#7691] 950 select * from v1; 951 [unknown result because it is related to issue#7691] 952 select * from v2; 953 [unknown result because it is related to issue#7691] 954 select * from v3; 955 [unknown result because it is related to issue#7691] 956 select * from v4; 957 [unknown result because it is related to issue#7691] 958 select * from v5; 959 [unknown result because it is related to issue#7691] 960 select * from v6; 961 [unknown result because it is related to issue#7691] 962 drop view v1; 963 [unknown result because it is related to issue#7691] 964 drop view v2; 965 [unknown result because it is related to issue#7691] 966 drop view v3; 967 [unknown result because it is related to issue#7691] 968 drop view v4; 969 [unknown result because it is related to issue#7691] 970 drop view v5; 971 [unknown result because it is related to issue#7691] 972 drop view v6; 973 [unknown result because it is related to issue#7691] 974 DROP TABLE IF EXISTS t1; 975 SELECT (1,2) = (SELECT NULL, NULL), (1,2) IN (SELECT NULL, NULL); 976 [unknown result because it is related to issue#7691] 977 SELECT (1,2) = (SELECT 1, NULL), (1,2) IN (SELECT 1, NULL); 978 [unknown result because it is related to issue#7691] 979 SELECT (1,2) = (SELECT NULL, 2), (1,2) IN (SELECT NULL, 2); 980 [unknown result because it is related to issue#7691] 981 SELECT (1,2) = (SELECT NULL, 1), (1,2) IN (SELECT NULL, 1); 982 [unknown result because it is related to issue#7691] 983 SELECT (1,2) = (SELECT 1, 1), (1,2) IN (SELECT 1, 1); 984 [unknown result because it is related to issue#7691] 985 SELECT (1,2) = (SELECT 1, 2), (1,2) IN (SELECT 1, 2); 986 [unknown result because it is related to issue#7691] 987 create view v1 as SELECT (1,2) = (SELECT NULL, NULL), (1,2) IN (SELECT NULL, NULL); 988 [unknown result because it is related to issue#7691] 989 create view v2 as SELECT (1,2) = (SELECT 1, NULL), (1,2) IN (SELECT 1, NULL); 990 [unknown result because it is related to issue#7691] 991 create view v3 as SELECT (1,2) = (SELECT NULL, 2), (1,2) IN (SELECT NULL, 2); 992 [unknown result because it is related to issue#7691] 993 create view v4 as SELECT (1,2) = (SELECT NULL, 1), (1,2) IN (SELECT NULL, 1); 994 [unknown result because it is related to issue#7691] 995 create view v5 as SELECT (1,2) = (SELECT 1, 1), (1,2) IN (SELECT 1, 1); 996 [unknown result because it is related to issue#7691] 997 create view v6 as SELECT (1,2) = (SELECT 1, 2), (1,2) IN (SELECT 1, 2); 998 [unknown result because it is related to issue#7691] 999 select * from v1; 1000 [unknown result because it is related to issue#7691] 1001 select * from v2; 1002 [unknown result because it is related to issue#7691] 1003 select * from v3; 1004 [unknown result because it is related to issue#7691] 1005 select * from v4; 1006 [unknown result because it is related to issue#7691] 1007 select * from v5; 1008 [unknown result because it is related to issue#7691] 1009 select * from v6; 1010 [unknown result because it is related to issue#7691] 1011 drop view v1; 1012 [unknown result because it is related to issue#7691] 1013 drop view v2; 1014 [unknown result because it is related to issue#7691] 1015 drop view v3; 1016 [unknown result because it is related to issue#7691] 1017 drop view v4; 1018 [unknown result because it is related to issue#7691] 1019 drop view v5; 1020 [unknown result because it is related to issue#7691] 1021 drop view v6; 1022 [unknown result because it is related to issue#7691] 1023 DROP TABLE IF EXISTS t_out; 1024 DROP TABLE IF EXISTS t_in; 1025 DROP TABLE IF EXISTS t1; 1026 drop table if exists t2; 1027 create table t1 (a int); 1028 create table t2 (b int); 1029 insert into t1 values (1),(2); 1030 insert into t2 values (1); 1031 select a from t1 where a in (select a from t1 where a in (select b from t2)); 1032 a 1033 1 1034 create view v1 as select a from t1 where a in (select a from t1 where a in (select b from t2)); 1035 select * from v1; 1036 a 1037 1 1038 drop view v1; 1039 DROP TABLE IF EXISTS t1; 1040 drop table if exists t2; 1041 create table t1 (a int, b int); 1042 create table t2 (a int, b int); 1043 select * from t1 where (a,b) in (select a,b from t2); 1044 a b 1045 create view v1 as select * from t1 where (a,b) in (select a,b from t2); 1046 select * from v1; 1047 a b 1048 drop view v1; 1049 DROP TABLE IF EXISTS t1; 1050 drop table if exists t2; 1051 DROP TABLE IF EXISTS t1; 1052 drop table if exists t2; 1053 CREATE TABLE t1( a INT ); 1054 INSERT INTO t1 VALUES (1),(2); 1055 CREATE TABLE t2( a INT, b INT ); 1056 SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2); 1057 a b 1058 SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); 1059 a 1060 1 1061 SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); 1062 a 1063 1 1064 SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); 1065 a 1066 1 1067 create view v1 as SELECT * FROM t2 WHERE (a, b) IN (SELECT a, b FROM t2); 1068 create view v2 as SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) ); 1069 create view v3 as SELECT * FROM t1 WHERE a IN ( ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ); 1070 create view v4 as SELECT * FROM t1 WHERE a IN ( SELECT 1 UNION SELECT 1 UNION SELECT 1 ); 1071 select * from v1; 1072 a b 1073 select * from v2; 1074 a 1075 1 1076 select * from v3; 1077 a 1078 1 1079 select * from v4; 1080 a 1081 1 1082 drop view v1; 1083 drop view v2; 1084 drop view v3; 1085 drop view v4; 1086 CREATE TABLE t1 (i1 int DEFAULT NULL,i2 int DEFAULT NULL) ; 1087 table t1 already exists 1088 INSERT INTO t1 VALUES (1, NULL); 1089 Column count doesn't match value count at row 1 1090 INSERT INTO t1 VALUES (2, 3); 1091 Column count doesn't match value count at row 1 1092 INSERT INTO t1 VALUES (4, NULL); 1093 Column count doesn't match value count at row 1 1094 INSERT INTO t1 VALUES (4, 0); 1095 Column count doesn't match value count at row 1 1096 INSERT INTO t1 VALUES (NULL, NULL); 1097 Column count doesn't match value count at row 1 1098 CREATE TABLE t2 (i1 int DEFAULT NULL,i2 int DEFAULT NULL) ; 1099 table t2 already exists 1100 INSERT INTO t2 VALUES (4, NULL); 1101 INSERT INTO t2 VALUES (5, 0); 1102 SELECT i1, i2 1103 FROM t1 1104 WHERE (i1, i2) 1105 NOT IN (SELECT i1, i2 FROM t2); 1106 invalid input: column i1 does not exist 1107 create view v1 as SELECT i1, i2 1108 FROM t1 1109 WHERE (i1, i2) 1110 NOT IN (SELECT i1, i2 FROM t2); 1111 invalid input: column i1 does not exist 1112 select * from v1; 1113 SQL parser error: table "v1" does not exist 1114 drop view v1; 1115 invalid view 'view-subquery-with-in.v1' 1116 INSERT INTO t1 VALUES (NULL, NULL); 1117 Column count doesn't match value count at row 1 1118 SELECT i1, i2 1119 FROM t1 1120 WHERE (i1, i2) 1121 NOT IN (SELECT i1, i2 FROM t2); 1122 invalid input: column i1 does not exist 1123 create view v1 as SELECT i1, i2 1124 FROM t1 1125 WHERE (i1, i2) 1126 NOT IN (SELECT i1, i2 FROM t2); 1127 invalid input: column i1 does not exist 1128 select * from v1; 1129 SQL parser error: table "v1" does not exist 1130 drop view v1; 1131 invalid view 'view-subquery-with-in.v1' 1132 DROP TABLE IF EXISTS t1; 1133 drop table if exists t2; 1134 DROP TABLE IF EXISTS t3; 1135 CREATE TABLE t1 (a int(11) NOT NULL default 0, PRIMARY KEY (a)); 1136 CREATE TABLE t2 (a int(11) default 0); 1137 CREATE TABLE t3 (a int(11) default 0); 1138 INSERT INTO t3 VALUES (1),(2),(3); 1139 INSERT INTO t1 VALUES (1),(2),(3),(4); 1140 INSERT INTO t2 VALUES (1),(2),(3); 1141 SELECT t1.a FROM t1 where t1.a in (select t2.a from t2 order by t2.a desc) ; 1142 a 1143 1 1144 2 1145 3 1146 create view v1 as SELECT t1.a FROM t1 where t1.a in (select t2.a from t2 order by t2.a desc) ; 1147 select * from v1; 1148 a 1149 1 1150 2 1151 3 1152 drop view v1; 1153 DROP TABLE IF EXISTS t1; 1154 drop table if exists t2; 1155 CREATE TABLE `t1` ( `aid` int(11) NOT NULL default 0, `bid` int(11) NOT NULL default 0); 1156 CREATE TABLE `t2` ( `aid` int(11) NOT NULL default 0, `bid` int(11) NOT NULL default 0); 1157 insert into t1 values (1,1),(1,2),(2,1),(2,2); 1158 insert into t2 values (1,2),(2,2); 1159 select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid); 1160 aid bid 1161 1 1 1162 2 1 1163 select * from t1 where t1.aid in (select aid from t2 where bid=t1.bid); 1164 aid bid 1165 1 2 1166 2 2 1167 SELECT t1.a FROM t1 where t1.a in (select t2.a from t2 order by t2.a desc) ; 1168 invalid input: column 't1.a' does not exist 1169 create view v1 as select * from t1 where t1.aid not in (select aid from t2 where bid=t1.bid); 1170 create view v2 as SELECT t1.a FROM t1 where t1.a in (select t2.a from t2 order by t2.a desc) ; 1171 invalid input: column 't1.a' does not exist 1172 select * from v1; 1173 aid bid 1174 1 1 1175 2 1 1176 select * from v2; 1177 SQL parser error: table "v2" does not exist 1178 drop view v1; 1179 drop view v2; 1180 invalid view 'view-subquery-with-in.v2' 1181 drop table if exists t2; 1182 CREATE TABLE t1(select_id BIGINT, values_id BIGINT); 1183 table t1 already exists 1184 INSERT INTO t1 VALUES (1, 1); 1185 CREATE TABLE t2 (select_id BIGINT, values_id BIGINT); 1186 INSERT INTO t2 VALUES (0, 1), (0, 2), (0, 3), (1, 5); 1187 SELECT values_id FROM t1 1188 WHERE values_id IN (SELECT values_id FROM t2 WHERE select_id IN (1, 0)); 1189 invalid input: column values_id does not exist 1190 SELECT values_id FROM t1 WHERE values_id IN (SELECT values_id FROM t2 WHERE select_id BETWEEN 0 AND 1); 1191 invalid input: column values_id does not exist 1192 SELECT values_id FROM t1 WHERE values_id IN (SELECT values_id FROM t2 WHERE select_id = 0 OR select_id = 1); 1193 invalid input: column values_id does not exist 1194 create view v1 as SELECT values_id FROM t1 WHERE values_id IN (SELECT values_id FROM t2 WHERE select_id BETWEEN 0 AND 1); 1195 invalid input: column values_id does not exist 1196 create view v2 as SELECT values_id FROM t1 WHERE values_id IN (SELECT values_id FROM t2 WHERE select_id = 0 OR select_id = 1); 1197 invalid input: column values_id does not exist 1198 select * from v1; 1199 SQL parser error: table "v1" does not exist 1200 select * from v2; 1201 SQL parser error: table "v2" does not exist 1202 drop view v1; 1203 invalid view 'view-subquery-with-in.v1' 1204 drop view v2; 1205 invalid view 'view-subquery-with-in.v2' 1206 DROP TABLE IF EXISTS t1; 1207 drop table if exists t2; 1208 CREATE TABLE t1 (a INT NOT NULL); 1209 INSERT INTO t1 VALUES (1),(-1), (65),(66); 1210 CREATE TABLE t2 (a INT UNSIGNED NOT NULL PRIMARY KEY); 1211 INSERT INTO t2 VALUES (65),(66); 1212 SELECT a FROM t1 WHERE a NOT IN (65,66); 1213 a 1214 1 1215 -1 1216 SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); 1217 a 1218 1 1219 -1 1220 create view v1 as SELECT a FROM t1 WHERE a NOT IN (65,66); 1221 create view v2 as SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); 1222 select * from v1; 1223 a 1224 1 1225 -1 1226 select * from v2; 1227 a 1228 1 1229 -1 1230 drop view v1; 1231 drop view v2; 1232 DROP TABLE IF EXISTS t1; 1233 drop table if exists t2; 1234 CREATE TABLE t1 (a INT); 1235 INSERT INTO t1 VALUES (1),(2),(3); 1236 CREATE TABLE t2 (a INT); 1237 INSERT INTO t1 VALUES (1),(2),(3); 1238 SELECT 1 FROM t1 WHERE t1.a NOT IN (SELECT 1 FROM t1, t2 WHERE false); 1239 1 1240 1 1241 1 1242 1 1243 1 1244 1 1245 1 1246 create view v1 as SELECT 1 FROM t1 WHERE t1.a NOT IN (SELECT 1 FROM t1, t2 WHERE false); 1247 select * from v1; 1248 1 1249 1 1250 1 1251 1 1252 1 1253 1 1254 1 1255 drop view v1; 1256 DROP TABLE IF EXISTS t1; 1257 drop table if exists t2; 1258 DROP TABLE IF EXISTS t1; 1259 drop table if exists t2; 1260 drop table if exists t3; 1261 CREATE TABLE t1 (a int, b int); 1262 CREATE TABLE t2 (c int, d int); 1263 CREATE TABLE t3 (e int); 1264 INSERT INTO t1 VALUES 1265 (1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40); 1266 INSERT INTO t2 VALUES 1267 (2,10), (2,20), (4,10), (5,10), (3,20), (2,40); 1268 INSERT INTO t3 VALUES (10), (30), (10), (20) ; 1269 SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20); 1270 SQL syntax error: aggregate function max not allowed in WHERE clause 1271 SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d); 1272 SQL syntax error: aggregate function max not allowed in WHERE clause 1273 SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d); 1274 SQL syntax error: aggregate function max not allowed in WHERE clause 1275 SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e)); 1276 SQL syntax error: aggregate function max not allowed in WHERE clause 1277 SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d)); 1278 SQL syntax error: aggregate function max not allowed in WHERE clause 1279 SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e)); 1280 SQL syntax error: aggregate function max not allowed in WHERE clause 1281 SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d)); 1282 SQL syntax error: aggregate function max not allowed in WHERE clause 1283 SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE MIN(b) < d AND EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d)); 1284 SQL syntax error: aggregate function min not allowed in WHERE clause 1285 create view v1 as SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>20); 1286 SQL syntax error: aggregate function max not allowed in WHERE clause 1287 create view v2 as SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE MAX(b)<d); 1288 SQL syntax error: aggregate function max not allowed in WHERE clause 1289 create view v3 as SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE MAX(b)>d); 1290 SQL syntax error: aggregate function max not allowed in WHERE clause 1291 create view v4 as SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE d >= SOME(SELECT e FROM t3 WHERE MAX(b)=e)); 1292 SQL syntax error: aggregate function max not allowed in WHERE clause 1293 create view v5 as SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d)); 1294 SQL syntax error: aggregate function max not allowed in WHERE clause 1295 create view v6 as SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE d > SOME(SELECT e FROM t3 WHERE MAX(b)=e)); 1296 SQL syntax error: aggregate function max not allowed in WHERE clause 1297 create view v7 as SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d)); 1298 SQL syntax error: aggregate function max not allowed in WHERE clause 1299 create view v8 as SELECT a FROM t1 GROUP BY a HAVING a IN (SELECT c FROM t2 WHERE MIN(b) < d AND EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d)); 1300 SQL syntax error: aggregate function min not allowed in WHERE clause 1301 select * from v1; 1302 SQL parser error: table "v1" does not exist 1303 select * from v2; 1304 SQL parser error: table "v2" does not exist 1305 select * from v3; 1306 SQL parser error: table "v3" does not exist 1307 select * from v4; 1308 SQL parser error: table "v4" does not exist 1309 select * from v5; 1310 SQL parser error: table "v5" does not exist 1311 select * from v6; 1312 SQL parser error: table "v6" does not exist 1313 select * from v7; 1314 SQL parser error: table "v7" does not exist 1315 select * from v8; 1316 SQL parser error: table "v8" does not exist 1317 drop view v1; 1318 invalid view 'view-subquery-with-in.v1' 1319 drop view v2; 1320 invalid view 'view-subquery-with-in.v2' 1321 drop view v3; 1322 invalid view 'view-subquery-with-in.v3' 1323 drop view v4; 1324 invalid view 'view-subquery-with-in.v4' 1325 drop view v5; 1326 invalid view 'view-subquery-with-in.v5' 1327 drop view v6; 1328 invalid view 'view-subquery-with-in.v6' 1329 drop view v7; 1330 invalid view 'view-subquery-with-in.v7' 1331 drop view v8; 1332 invalid view 'view-subquery-with-in.v8' 1333 DROP TABLE IF EXISTS t1; 1334 DROP TABLE IF EXISTS t2; 1335 DROP TABLE IF EXISTS t1xt2; 1336 CREATE TABLE t1 ( 1337 id_1 int(5) NOT NULL, 1338 t varchar(4) DEFAULT NULL 1339 ); 1340 CREATE TABLE t2 ( 1341 id_2 int(5) NOT NULL, 1342 t varchar(4) DEFAULT NULL 1343 ); 1344 CREATE TABLE t1xt2 ( 1345 id_1 int(5) NOT NULL, 1346 id_2 int(5) NOT NULL 1347 ); 1348 INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'); 1349 INSERT INTO t2 VALUES (2, 'bb'), (3, 'cc'), (4, 'dd'), (12, 'aa'); 1350 INSERT INTO t1xt2 VALUES (2, 2), (3, 3), (4, 4); 1351 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); 1352 id_1 1353 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); 1354 id_1 1355 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); 1356 id_1 1357 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); 1358 id_1 1359 1 1360 2 1361 3 1362 4 1363 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))); 1364 id_1 1365 1 1366 2 1367 3 1368 4 1369 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)))); 1370 id_1 1371 1 1372 2 1373 3 1374 4 1375 insert INTO t1xt2 VALUES (1, 12); 1376 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); 1377 id_1 1378 1 1379 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); 1380 id_1 1381 1 1382 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); 1383 id_1 1384 1 1385 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); 1386 id_1 1387 2 1388 3 1389 4 1390 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); 1391 id_1 1392 2 1393 3 1394 4 1395 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); 1396 id_1 1397 2 1398 3 1399 4 1400 insert INTO t1xt2 VALUES (2, 12); 1401 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); 1402 id_1 1403 1 1404 2 1405 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); 1406 id_1 1407 1 1408 2 1409 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); 1410 id_1 1411 1 1412 2 1413 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); 1414 id_1 1415 3 1416 4 1417 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); 1418 id_1 1419 3 1420 4 1421 SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); 1422 id_1 1423 3 1424 4 1425 create view v1 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); 1426 create view v2 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); 1427 create view v3 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); 1428 create view v4 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); 1429 create view v5 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1))); 1430 create view v6 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 where t1.id_1 = t1xt2.id_1)))); 1431 create view v7 as insert INTO t1xt2 VALUES (1, 12); 1432 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 24 near " insert INTO t1xt2 VALUES (1, 12);"; 1433 create view v8 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); 1434 create view v9 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); 1435 create view v10 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); 1436 create view v11 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); 1437 create view v12 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); 1438 create view v13 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); 1439 create view v14 as insert INTO t1xt2 VALUES (2, 12); 1440 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 25 near " insert INTO t1xt2 VALUES (2, 12);"; 1441 create view v15 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); 1442 create view v16 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); 1443 create view v17 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); 1444 create view v18 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)); 1445 create view v19 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN ((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1))); 1446 create view v20 as SELECT DISTINCT t1.id_1 FROM t1 WHERE(12 NOT IN (((SELECT t1xt2.id_2 FROM t1xt2 WHERE t1.id_1 = t1xt2.id_1)))); 1447 select * from v1; 1448 id_1 1449 1 1450 2 1451 select * from v2; 1452 id_1 1453 1 1454 2 1455 select * from v3; 1456 id_1 1457 1 1458 2 1459 select * from v4; 1460 id_1 1461 3 1462 4 1463 select * from v5; 1464 id_1 1465 3 1466 4 1467 select * from v6; 1468 id_1 1469 3 1470 4 1471 select * from v7; 1472 SQL parser error: table "v7" does not exist 1473 select * from v8; 1474 id_1 1475 1 1476 2 1477 select * from v9; 1478 id_1 1479 1 1480 2 1481 select * from v10; 1482 id_1 1483 1 1484 2 1485 select * from v11; 1486 id_1 1487 3 1488 4 1489 select * from v12; 1490 id_1 1491 3 1492 4 1493 select * from v13; 1494 id_1 1495 3 1496 4 1497 select * from v14; 1498 SQL parser error: table "v14" does not exist 1499 select * from v15; 1500 id_1 1501 1 1502 2 1503 select * from v16; 1504 id_1 1505 1 1506 2 1507 select * from v17; 1508 id_1 1509 1 1510 2 1511 select * from v18; 1512 id_1 1513 3 1514 4 1515 select * from v19; 1516 id_1 1517 3 1518 4 1519 select * from v20; 1520 id_1 1521 3 1522 4 1523 drop view v1; 1524 drop view v2; 1525 drop view v3; 1526 drop view v4; 1527 drop view v5; 1528 drop view v6; 1529 drop view v7; 1530 invalid view 'view-subquery-with-in.v7' 1531 drop view v8; 1532 drop view v9; 1533 drop view v10; 1534 drop view v11; 1535 drop view v12; 1536 drop view v13; 1537 drop view v14; 1538 invalid view 'view-subquery-with-in.v14' 1539 drop view v15; 1540 drop view v16; 1541 drop view v17; 1542 drop view v18; 1543 drop view v19; 1544 drop view v20; 1545 DROP TABLE IF EXISTS t1; 1546 DROP TABLE IF EXISTS t2; 1547 DROP TABLE IF EXISTS t1xt2;