000001 # So far, this only runs well on SQLite and MySQL.
000002
000003 # skip this entire file if ms sql server
000004 onlyif mssql
000005 halt
000006
000007 # skip this entire file if oracle
000008 onlyif oracle
000009 halt
000010
000011 # EVIDENCE-OF: R-52275-55503 When the right operand is an empty set, the
000012 # result of IN is false and the result of NOT IN is true, regardless of
000013 # the left operand and even if the left operand is NULL.
000014 #
000015
000016 # EVIDENCE-OF: R-13595-45863 Note that SQLite allows the parenthesized
000017 # list of scalar values on the right-hand side of an IN or NOT IN
000018 # operator to be an empty list but most other SQL database database
000019 # engines and the SQL92 standard require the list to contain at least
000020 # one element.
000021 #
000022
000023 onlyif sqlite # empty RHS
000024 query I nosort
000025 SELECT 1 IN ()
000026 ----
000027 0
000028
000029 query I nosort
000030 SELECT 1 IN (2)
000031 ----
000032 0
000033
000034 query I nosort
000035 SELECT 1 IN (2,3,4,5,6,7,8,9)
000036 ----
000037 0
000038
000039 onlyif sqlite # empty RHS
000040 query I nosort
000041 SELECT 1 NOT IN ()
000042 ----
000043 1
000044
000045 query I nosort
000046 SELECT 1 NOT IN (2)
000047 ----
000048 1
000049
000050 query I nosort
000051 SELECT 1 NOT IN (2,3,4,5,6,7,8,9)
000052 ----
000053 1
000054
000055 onlyif sqlite # empty RHS
000056 query I nosort
000057 SELECT null IN ()
000058 ----
000059 0
000060
000061 onlyif sqlite # empty RHS
000062 query I nosort
000063 SELECT null NOT IN ()
000064 ----
000065 1
000066
000067 statement ok
000068 CREATE TABLE t1(x INTEGER)
000069
000070 onlyif sqlite
000071 query I nosort label-1
000072 SELECT 1 IN t1
000073 ----
000074 0
000075
000076 query I nosort label-1
000077 SELECT 1 IN (SELECT * FROM t1)
000078 ----
000079 0
000080
000081 onlyif sqlite
000082 query I nosort label-2
000083 SELECT 1 NOT IN t1
000084 ----
000085 1
000086
000087 query I nosort label-2
000088 SELECT 1 NOT IN (SELECT * FROM t1)
000089 ----
000090 1
000091
000092 onlyif sqlite
000093 query I nosort label-3
000094 SELECT null IN t1
000095 ----
000096 0
000097
000098 query I nosort label-3
000099 SELECT null IN (SELECT * FROM t1)
000100 ----
000101 0
000102
000103 onlyif sqlite
000104 query I nosort label-4
000105 SELECT null NOT IN t1
000106 ----
000107 1
000108
000109 query I nosort label-4
000110 SELECT null NOT IN (SELECT * FROM t1)
000111 ----
000112 1
000113
000114 statement ok
000115 CREATE TABLE t2(y INTEGER PRIMARY KEY)
000116
000117 onlyif sqlite
000118 query I nosort label-5
000119 SELECT 1 IN t2
000120 ----
000121 0
000122
000123 query I nosort label-5
000124 SELECT 1 IN (SELECT * FROM t2)
000125 ----
000126 0
000127
000128 onlyif sqlite
000129 query I nosort label-6
000130 SELECT 1 NOT IN t2
000131 ----
000132 1
000133
000134 query I nosort label-6
000135 SELECT 1 NOT IN (SELECT * FROM t2)
000136 ----
000137 1
000138
000139 onlyif sqlite
000140 query I nosort label-7
000141 SELECT null IN t2
000142 ----
000143 0
000144
000145 query I nosort label-7
000146 SELECT null IN (SELECT * FROM t2)
000147 ----
000148 0
000149
000150 onlyif sqlite
000151 query I nosort label-8
000152 SELECT null NOT IN t2
000153 ----
000154 1
000155
000156 query I nosort label-8
000157 SELECT null NOT IN (SELECT * FROM t2)
000158 ----
000159 1
000160
000161 statement ok
000162 CREATE TABLE t3(z INTEGER UNIQUE)
000163
000164 onlyif sqlite
000165 query I nosort label-9
000166 SELECT 1 IN t3
000167 ----
000168 0
000169
000170 query I nosort label-9
000171 SELECT 1 IN (SELECT * FROM t3)
000172 ----
000173 0
000174
000175 onlyif sqlite
000176 query I nosort label-10
000177 SELECT 1 NOT IN t3
000178 ----
000179 1
000180
000181 query I nosort label-10
000182 SELECT 1 NOT IN (SELECT * FROM t3)
000183 ----
000184 1
000185
000186 onlyif sqlite
000187 query I nosort label-11
000188 SELECT null IN t3
000189 ----
000190 0
000191
000192 query I nosort label-11
000193 SELECT null IN (SELECT * FROM t3)
000194 ----
000195 0
000196
000197 onlyif sqlite
000198 query I nosort label-12
000199 SELECT null NOT IN t3
000200 ----
000201 1
000202
000203 query I nosort label-12
000204 SELECT null NOT IN (SELECT * FROM t3)
000205 ----
000206 1
000207
000208 query I nosort
000209 SELECT 1 IN (SELECT x+y FROM t1, t2)
000210 ----
000211 0
000212
000213 query I nosort
000214 SELECT 1 NOT IN (SELECT x+y FROM t1,t2)
000215 ----
000216 1
000217
000218 query I nosort
000219 SELECT null IN (SELECT x+y FROM t1,t2)
000220 ----
000221 0
000222
000223 query I nosort
000224 SELECT null NOT IN (SELECT x+y FROM t1,t2)
000225 ----
000226 1
000227
000228 onlyif sqlite # empty RHS
000229 query I nosort
000230 SELECT 1.23 IN ()
000231 ----
000232 0
000233
000234 onlyif sqlite # empty RHS
000235 query I nosort
000236 SELECT 1.23 NOT IN ()
000237 ----
000238 1
000239
000240 onlyif sqlite
000241 query I nosort label-13
000242 SELECT 1.23 IN t1
000243 ----
000244 0
000245
000246 query I nosort label-13
000247 SELECT 1.23 IN (SELECT * FROM t1)
000248 ----
000249 0
000250
000251 onlyif sqlite
000252 query I nosort label-14
000253 SELECT 1.23 NOT IN t1
000254 ----
000255 1
000256
000257 query I nosort label-14
000258 SELECT 1.23 NOT IN (SELECT * FROM t1)
000259 ----
000260 1
000261
000262 onlyif sqlite # empty RHS
000263 query I nosort
000264 SELECT 'hello' IN ()
000265 ----
000266 0
000267
000268 onlyif sqlite # empty RHS
000269 query I nosort
000270 SELECT 'hello' NOT IN ()
000271 ----
000272 1
000273
000274 onlyif sqlite
000275 query I nosort label-15
000276 SELECT 'hello' IN t1
000277 ----
000278 0
000279
000280 query I nosort label-15
000281 SELECT 'hello' IN (SELECT * FROM t1)
000282 ----
000283 0
000284
000285 onlyif sqlite
000286 query I nosort label-16
000287 SELECT 'hello' NOT IN t1
000288 ----
000289 1
000290
000291 query I nosort label-16
000292 SELECT 'hello' NOT IN (SELECT * FROM t1)
000293 ----
000294 1
000295
000296 onlyif sqlite # empty RHS
000297 query I nosort
000298 SELECT x'303132' IN ()
000299 ----
000300 0
000301
000302 onlyif sqlite # empty RHS
000303 query I nosort
000304 SELECT x'303132' NOT IN ()
000305 ----
000306 1
000307
000308 onlyif sqlite
000309 query I nosort label-17
000310 SELECT x'303132' IN t1
000311 ----
000312 0
000313
000314 query I nosort label-17
000315 SELECT x'303132' IN (SELECT * FROM t1)
000316 ----
000317 0
000318
000319 onlyif sqlite
000320 query I nosort label-18
000321 SELECT x'303132' NOT IN t1
000322 ----
000323 1
000324
000325 query I nosort label-18
000326 SELECT x'303132' NOT IN (SELECT * FROM t1)
000327 ----
000328 1
000329
000330 # EVIDENCE-OF: R-50221-42915 The result of an IN or NOT IN operator is
000331 # determined by the following matrix: Left operand is NULL Right operand
000332 # contains NULL Right operand is an empty set Left operand found within
000333 # right operand Result of IN operator Result of NOT IN operator no no no
000334 # no false true does not matter no yes no false true no does not matter
000335 # no yes true false no yes no no NULL NULL yes does not matter no does
000336 # not matter NULL NULL
000337 #
000338 # Row 1:
000339
000340 query I nosort
000341 SELECT 1 IN (2,3,4)
000342 ----
000343 0
000344
000345 query I nosort
000346 SELECT 1 NOT IN (2,3,4)
000347 ----
000348 1
000349
000350 query I nosort
000351 SELECT 'a' IN ('b','c','d')
000352 ----
000353 0
000354
000355 query I nosort
000356 SELECT 'a' NOT IN ('b','c','d')
000357 ----
000358 1
000359
000360 statement ok
000361 CREATE TABLE t4(a INTEGER UNIQUE)
000362
000363 statement ok
000364 CREATE TABLE t5(b INTEGER PRIMARY KEY)
000365
000366 statement ok
000367 CREATE TABLE t6(c INTEGER)
000368
000369 statement ok
000370 INSERT INTO t4 VALUES(2)
000371
000372 statement ok
000373 INSERT INTO t4 VALUES(3)
000374
000375 statement ok
000376 INSERT INTO t4 VALUES(4)
000377
000378 statement ok
000379 INSERT INTO t5 SELECT * FROM t4
000380
000381 statement ok
000382 INSERT INTO t6 SELECT * FROM t4
000383
000384 statement ok
000385 CREATE TABLE t4n(a INTEGER UNIQUE)
000386
000387 statement ok
000388 CREATE TABLE t6n(c INTEGER)
000389
000390 statement ok
000391 INSERT INTO t4n SELECT * FROM t4
000392
000393 statement ok
000394 INSERT INTO t4n VALUES(null)
000395
000396 statement ok
000397 INSERT INTO t6n SELECT * FROM t4n
000398
000399 skipif mysql
000400 statement ok
000401 CREATE TABLE t7(a TEXT UNIQUE)
000402
000403 onlyif mysql
000404 statement ok
000405 CREATE TABLE t7(a TEXT, UNIQUE (a(1)))
000406
000407 statement ok
000408 CREATE TABLE t8(c TEXT)
000409
000410 statement ok
000411 INSERT INTO t7 VALUES('b')
000412
000413 statement ok
000414 INSERT INTO t7 VALUES('c')
000415
000416 statement ok
000417 INSERT INTO t7 VALUES('d')
000418
000419 statement ok
000420 INSERT INTO t8 SELECT * FROM t7
000421
000422 skipif mysql
000423 statement ok
000424 CREATE TABLE t7n(a TEXT UNIQUE)
000425
000426 onlyif mysql
000427 statement ok
000428 CREATE TABLE t7n(a TEXT, UNIQUE (a(1)))
000429
000430 statement ok
000431 CREATE TABLE t8n(c TEXT)
000432
000433 statement ok
000434 INSERT INTO t7n SELECT * FROM t7
000435
000436 statement ok
000437 INSERT INTO t7n VALUES(null)
000438
000439 statement ok
000440 INSERT INTO t8n SELECT * FROM t7n
000441
000442 onlyif sqlite
000443 query I nosort label-19
000444 SELECT 1 IN t4
000445 ----
000446 0
000447
000448 query I nosort label-19
000449 SELECT 1 IN (SELECT * FROM t4)
000450 ----
000451 0
000452
000453 onlyif sqlite
000454 query I nosort label-20
000455 SELECT 1 NOT IN t4
000456 ----
000457 1
000458
000459 query I nosort label-20
000460 SELECT 1 NOT IN (SELECT * FROM t4)
000461 ----
000462 1
000463
000464 onlyif sqlite
000465 query I nosort label-21
000466 SELECT 1 IN t5
000467 ----
000468 0
000469
000470 query I nosort label-21
000471 SELECT 1 IN (SELECT * FROM t5)
000472 ----
000473 0
000474
000475 onlyif sqlite
000476 query I nosort label-22
000477 SELECT 1 NOT IN t5
000478 ----
000479 1
000480
000481 query I nosort label-22
000482 SELECT 1 NOT IN (SELECT * FROM t5)
000483 ----
000484 1
000485
000486 onlyif sqlite
000487 query I nosort label-23
000488 SELECT 1 IN t6
000489 ----
000490 0
000491
000492 query I nosort label-23
000493 SELECT 1 IN (SELECT * FROM t6)
000494 ----
000495 0
000496
000497 onlyif sqlite
000498 query I nosort label-24
000499 SELECT 1 NOT IN t6
000500 ----
000501 1
000502
000503 query I nosort label-24
000504 SELECT 1 NOT IN (SELECT * FROM t6)
000505 ----
000506 1
000507
000508 onlyif sqlite
000509 query I nosort label-25
000510 SELECT 'a' IN t7
000511 ----
000512 0
000513
000514 query I nosort label-25
000515 SELECT 'a' IN (SELECT * FROM t7)
000516 ----
000517 0
000518
000519 onlyif sqlite
000520 query I nosort label-26
000521 SELECT 'a' NOT IN t7
000522 ----
000523 1
000524
000525 query I nosort label-26
000526 SELECT 'a' NOT IN (SELECT * FROM t7)
000527 ----
000528 1
000529
000530 onlyif sqlite
000531 query I nosort label-27
000532 SELECT 'a' IN t8
000533 ----
000534 0
000535
000536 query I nosort label-27
000537 SELECT 'a' IN (SELECT * FROM t8)
000538 ----
000539 0
000540
000541 onlyif sqlite
000542 query I nosort label-28
000543 SELECT 'a' NOT IN t8
000544 ----
000545 1
000546
000547 query I nosort label-28
000548 SELECT 'a' NOT IN (SELECT * FROM t8)
000549 ----
000550 1
000551
000552 #
000553 # Row 2 is tested by cases 1-32 above.
000554 # Row 3:
000555
000556 query I nosort
000557 SELECT 2 IN (2,3,4,null)
000558 ----
000559 1
000560
000561 query I nosort
000562 SELECT 3 NOT IN (2,3,4,null)
000563 ----
000564 0
000565
000566 query I nosort
000567 SELECT 4 IN (2,3,4)
000568 ----
000569 1
000570
000571 query I nosort
000572 SELECT 2 NOT IN (2,3,4)
000573 ----
000574 0
000575
000576 query I nosort
000577 SELECT 'b' IN ('b','c','d')
000578 ----
000579 1
000580
000581 query I nosort
000582 SELECT 'c' NOT IN ('b','c','d')
000583 ----
000584 0
000585
000586 query I nosort
000587 SELECT 'd' IN ('b','c',null,'d')
000588 ----
000589 1
000590
000591 query I nosort
000592 SELECT 'b' NOT IN (null,'b','c','d')
000593 ----
000594 0
000595
000596 onlyif sqlite
000597 query I nosort label-29
000598 SELECT 2 IN t4
000599 ----
000600 1
000601
000602 query I nosort label-29
000603 SELECT 2 IN (SELECT * FROM t4)
000604 ----
000605 1
000606
000607 onlyif sqlite
000608 query I nosort label-30
000609 SELECT 3 NOT IN t4
000610 ----
000611 0
000612
000613 query I nosort label-30
000614 SELECT 3 NOT IN (SELECT * FROM t4)
000615 ----
000616 0
000617
000618 onlyif sqlite
000619 query I nosort label-31
000620 SELECT 4 IN t4n
000621 ----
000622 1
000623
000624 query I nosort label-31
000625 SELECT 4 IN (SELECT * FROM t4n)
000626 ----
000627 1
000628
000629 onlyif sqlite
000630 query I nosort label-32
000631 SELECT 2 NOT IN t4n
000632 ----
000633 0
000634
000635 query I nosort label-32
000636 SELECT 2 NOT IN (SELECT * FROM t4n)
000637 ----
000638 0
000639
000640 onlyif sqlite
000641 query I nosort label-33
000642 SELECT 2 IN t5
000643 ----
000644 1
000645
000646 query I nosort label-33
000647 SELECT 2 IN (SELECT * FROM t5)
000648 ----
000649 1
000650
000651 onlyif sqlite
000652 query I nosort label-34
000653 SELECT 3 NOT IN t5
000654 ----
000655 0
000656
000657 query I nosort label-34
000658 SELECT 3 NOT IN (SELECT * FROM t5)
000659 ----
000660 0
000661
000662 onlyif sqlite
000663 query I nosort label-35
000664 SELECT 2 IN t6
000665 ----
000666 1
000667
000668 query I nosort label-35
000669 SELECT 2 IN (SELECT * FROM t6)
000670 ----
000671 1
000672
000673 onlyif sqlite
000674 query I nosort label-36
000675 SELECT 3 NOT IN t6
000676 ----
000677 0
000678
000679 query I nosort label-36
000680 SELECT 3 NOT IN (SELECT * FROM t6)
000681 ----
000682 0
000683
000684 onlyif sqlite
000685 query I nosort label-37
000686 SELECT 4 IN t6n
000687 ----
000688 1
000689
000690 query I nosort label-37
000691 SELECT 4 IN (SELECT * FROM t6n)
000692 ----
000693 1
000694
000695 onlyif sqlite
000696 query I nosort label-38
000697 SELECT 2 NOT IN t6n
000698 ----
000699 0
000700
000701 query I nosort label-38
000702 SELECT 2 NOT IN (SELECT * FROM t6n)
000703 ----
000704 0
000705
000706 onlyif sqlite
000707 query I nosort label-39
000708 SELECT 'b' IN t7
000709 ----
000710 1
000711
000712 query I nosort label-39
000713 SELECT 'b' IN (SELECT * FROM t7)
000714 ----
000715 1
000716
000717 onlyif sqlite
000718 query I nosort label-40
000719 SELECT 'c' NOT IN t7
000720 ----
000721 0
000722
000723 query I nosort label-40
000724 SELECT 'c' NOT IN (SELECT * FROM t7)
000725 ----
000726 0
000727
000728 onlyif sqlite
000729 query I nosort label-41
000730 SELECT 'c' IN t7n
000731 ----
000732 1
000733
000734 query I nosort label-41
000735 SELECT 'c' IN (SELECT * FROM t7n)
000736 ----
000737 1
000738
000739 onlyif sqlite
000740 query I nosort label-42
000741 SELECT 'd' NOT IN t7n
000742 ----
000743 0
000744
000745 query I nosort label-42
000746 SELECT 'd' NOT IN (SELECT * FROM t7n)
000747 ----
000748 0
000749
000750 onlyif sqlite
000751 query I nosort label-43
000752 SELECT 'b' IN t8
000753 ----
000754 1
000755
000756 query I nosort label-43
000757 SELECT 'b' IN (SELECT * FROM t8)
000758 ----
000759 1
000760
000761 onlyif sqlite
000762 query I nosort label-44
000763 SELECT 'c' NOT IN t8
000764 ----
000765 0
000766
000767 query I nosort label-44
000768 SELECT 'c' NOT IN (SELECT * FROM t8)
000769 ----
000770 0
000771
000772 onlyif sqlite
000773 query I nosort label-45
000774 SELECT 'c' IN t8n
000775 ----
000776 1
000777
000778 query I nosort label-45
000779 SELECT 'c' IN (SELECT * FROM t8n)
000780 ----
000781 1
000782
000783 onlyif sqlite
000784 query I nosort label-46
000785 SELECT 'd' NOT IN t8n
000786 ----
000787 0
000788
000789 query I nosort label-46
000790 SELECT 'd' NOT IN (SELECT * FROM t8n)
000791 ----
000792 0
000793
000794 #
000795 # Row 4:
000796 query I nosort
000797 SELECT 1 IN (2,3,4,null)
000798 ----
000799 NULL
000800
000801 query I nosort
000802 SELECT 1 NOT IN (2,3,4,null)
000803 ----
000804 NULL
000805
000806 query I nosort
000807 SELECT 'a' IN ('b','c',null,'d')
000808 ----
000809 NULL
000810
000811 query I nosort
000812 SELECT 'a' NOT IN (null,'b','c','d')
000813 ----
000814 NULL
000815
000816 onlyif sqlite
000817 query I nosort label-47
000818 SELECT 1 IN t4n
000819 ----
000820 NULL
000821
000822 query I nosort label-47
000823 SELECT 1 IN (SELECT * FROM t4n)
000824 ----
000825 NULL
000826
000827 onlyif sqlite
000828 query I nosort label-48
000829 SELECT 5 NOT IN t4n
000830 ----
000831 NULL
000832
000833 query I nosort label-48
000834 SELECT 5 NOT IN (SELECT * FROM t4n)
000835 ----
000836 NULL
000837
000838 onlyif sqlite
000839 query I nosort label-49
000840 SELECT 6 IN t6n
000841 ----
000842 NULL
000843
000844 query I nosort label-49
000845 SELECT 6 IN (SELECT * FROM t6n)
000846 ----
000847 NULL
000848
000849 onlyif sqlite
000850 query I nosort label-50
000851 SELECT 7 NOT IN t6n
000852 ----
000853 NULL
000854
000855 query I nosort label-50
000856 SELECT 7 NOT IN (SELECT * FROM t6n)
000857 ----
000858 NULL
000859
000860 onlyif sqlite
000861 query I nosort label-51
000862 SELECT 'a' IN t7n
000863 ----
000864 NULL
000865
000866 query I nosort label-51
000867 SELECT 'a' IN (SELECT * FROM t7n)
000868 ----
000869 NULL
000870
000871 onlyif sqlite
000872 query I nosort label-52
000873 SELECT 'e' NOT IN t7n
000874 ----
000875 NULL
000876
000877 query I nosort label-52
000878 SELECT 'e' NOT IN (SELECT * FROM t7n)
000879 ----
000880 NULL
000881
000882 onlyif sqlite
000883 query I nosort label-53
000884 SELECT 'f' IN t8n
000885 ----
000886 NULL
000887
000888 query I nosort label-53
000889 SELECT 'f' IN (SELECT * FROM t8n)
000890 ----
000891 NULL
000892
000893 onlyif sqlite
000894 query I nosort label-54
000895 SELECT 'g' NOT IN t8n
000896 ----
000897 NULL
000898
000899 query I nosort label-54
000900 SELECT 'g' NOT IN (SELECT * FROM t8n)
000901 ----
000902 NULL
000903
000904 #
000905 # Row 5:
000906 query I nosort
000907 SELECT null IN (2,3,4,null)
000908 ----
000909 NULL
000910
000911 query I nosort
000912 SELECT null NOT IN (2,3,4,null)
000913 ----
000914 NULL
000915
000916 query I nosort
000917 SELECT null IN (2,3,4)
000918 ----
000919 NULL
000920
000921 query I nosort
000922 SELECT null NOT IN (2,3,4)
000923 ----
000924 NULL
000925
000926 query I nosort
000927 SELECT null IN ('b','c','d')
000928 ----
000929 NULL
000930
000931 query I nosort
000932 SELECT null NOT IN ('b','c','d')
000933 ----
000934 NULL
000935
000936 query I nosort
000937 SELECT null IN ('b','c',null,'d')
000938 ----
000939 NULL
000940
000941 query I nosort
000942 SELECT null NOT IN (null,'b','c','d')
000943 ----
000944 NULL
000945
000946 onlyif sqlite
000947 query I nosort label-55
000948 SELECT null IN t4
000949 ----
000950 NULL
000951
000952 # mysql is failing this one
000953 skipif mysql
000954 query I nosort label-55
000955 SELECT null IN (SELECT * FROM t4)
000956 ----
000957 NULL
000958
000959 # t4 should be 2,3,4... so this should be same
000960 query I nosort label-55
000961 SELECT null IN (2,3,4)
000962 ----
000963 NULL
000964
000965 onlyif sqlite
000966 query I nosort label-56
000967 SELECT null NOT IN t4
000968 ----
000969 NULL
000970
000971 # mysql is failing this one
000972 skipif mysql
000973 query I nosort label-56
000974 SELECT null NOT IN (SELECT * FROM t4)
000975 ----
000976 NULL
000977
000978 onlyif sqlite
000979 query I nosort label-57
000980 SELECT null IN t4n
000981 ----
000982 NULL
000983
000984 query I nosort label-57
000985 SELECT null IN (SELECT * FROM t4n)
000986 ----
000987 NULL
000988
000989 onlyif sqlite
000990 query I nosort label-58
000991 SELECT null NOT IN t4n
000992 ----
000993 NULL
000994
000995 query I nosort label-58
000996 SELECT null NOT IN (SELECT * FROM t4n)
000997 ----
000998 NULL
000999
001000 onlyif sqlite
001001 query I nosort label-59
001002 SELECT null IN t5
001003 ----
001004 NULL
001005
001006 # mysql is failing this one
001007 skipif mysql
001008 query I nosort label-59
001009 SELECT null IN (SELECT * FROM t5)
001010 ----
001011 NULL
001012
001013 onlyif sqlite
001014 query I nosort label-60
001015 SELECT null NOT IN t5
001016 ----
001017 NULL
001018
001019 # mysql is failing this one
001020 skipif mysql
001021 query I nosort label-60
001022 SELECT null NOT IN (SELECT * FROM t5)
001023 ----
001024 NULL
001025
001026 onlyif sqlite
001027 query I nosort label-61
001028 SELECT null IN t6
001029 ----
001030 NULL
001031
001032 query I nosort label-61
001033 SELECT null IN (SELECT * FROM t6)
001034 ----
001035 NULL
001036
001037 onlyif sqlite
001038 query I nosort label-62
001039 SELECT null NOT IN t6
001040 ----
001041 NULL
001042
001043 query I nosort label-62
001044 SELECT null NOT IN (SELECT * FROM t6)
001045 ----
001046 NULL
001047
001048 onlyif sqlite
001049 query I nosort label-63
001050 SELECT null IN t6n
001051 ----
001052 NULL
001053
001054 query I nosort label-63
001055 SELECT null IN (SELECT * FROM t6n)
001056 ----
001057 NULL
001058
001059 onlyif sqlite
001060 query I nosort label-64
001061 SELECT null NOT IN t6n
001062 ----
001063 NULL
001064
001065 query I nosort label-64
001066 SELECT null NOT IN (SELECT * FROM t6n)
001067 ----
001068 NULL
001069
001070 onlyif sqlite
001071 query I nosort label-65
001072 SELECT null IN t7
001073 ----
001074 NULL
001075
001076 query I nosort label-65
001077 SELECT null IN (SELECT * FROM t7)
001078 ----
001079 NULL
001080
001081 onlyif sqlite
001082 query I nosort label-66
001083 SELECT null NOT IN t7
001084 ----
001085 NULL
001086
001087 query I nosort label-66
001088 SELECT null NOT IN (SELECT * FROM t7)
001089 ----
001090 NULL
001091
001092 onlyif sqlite
001093 query I nosort label-67
001094 SELECT null IN t7n
001095 ----
001096 NULL
001097
001098 query I nosort label-67
001099 SELECT null IN (SELECT * FROM t7n)
001100 ----
001101 NULL
001102
001103 onlyif sqlite
001104 query I nosort label-68
001105 SELECT null NOT IN t7n
001106 ----
001107 NULL
001108
001109 query I nosort label-68
001110 SELECT null NOT IN (SELECT * FROM t7n)
001111 ----
001112 NULL
001113
001114 onlyif sqlite
001115 query I nosort label-69
001116 SELECT null IN t8
001117 ----
001118 NULL
001119
001120 query I nosort label-69
001121 SELECT null IN (SELECT * FROM t8)
001122 ----
001123 NULL
001124
001125 onlyif sqlite
001126 query I nosort label-70
001127 SELECT null NOT IN t8
001128 ----
001129 NULL
001130
001131 query I nosort label-70
001132 SELECT null NOT IN (SELECT * FROM t8)
001133 ----
001134 NULL
001135
001136 onlyif sqlite
001137 query I nosort label-71
001138 SELECT null IN t8n
001139 ----
001140 NULL
001141
001142 query I nosort label-71
001143 SELECT null IN (SELECT * FROM t8n)
001144 ----
001145 NULL
001146
001147 onlyif sqlite
001148 query I nosort label-72
001149 SELECT null NOT IN t8n
001150 ----
001151 NULL
001152
001153 query I nosort label-72
001154 SELECT null NOT IN (SELECT * FROM t8n)
001155 ----
001156 NULL