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