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