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