000001  
000002  # EVIDENCE-OF: R-52275-55503 When the right operand is an empty set, the
000003  # result of IN is false and the result of NOT IN is true, regardless of
000004  # the left operand and even if the left operand is NULL.
000005  #
000006  
000007  # EVIDENCE-OF: R-13595-45863 Note that SQLite allows the parenthesized
000008  # list of scalar values on the right-hand side of an IN or NOT IN
000009  # operator to be an empty list but most other SQL database database
000010  # engines and the SQL92 standard require the list to contain at least
000011  # one element.
000012  #
000013  
000014  # EVIDENCE-OF: R-50221-42915 The result of an IN or NOT IN operator is
000015  # determined by the following matrix: Left operand is NULL Right operand
000016  # contains NULL Right operand is an empty set Left operand found within
000017  # right operand Result of IN operator Result of NOT IN operator no no no
000018  # no false true does not matter no yes no false true no does not matter
000019  # no yes true false no yes no no NULL NULL yes does not matter no does
000020  # not matter NULL NULL
000021  #
000022  
000023  skipif oracle
000024  statement ok
000025  CREATE TABLE t1( x INTEGER, y TEXT )
000026  
000027  onlyif oracle
000028  statement ok
000029  CREATE TABLE t1( x INTEGER, y VARCHAR(8) )
000030  
000031  statement ok
000032  INSERT INTO t1 VALUES(1,'true')
000033  
000034  statement ok
000035  INSERT INTO t1 VALUES(0,'false')
000036  
000037  statement ok
000038  INSERT INTO t1 VALUES(NULL,'NULL')
000039  
000040  # Row 1: 
000041  
000042  query I nosort
000043  SELECT 1 FROM t1 WHERE 1 IN (2)
000044  ----
000045  
000046  query I nosort
000047  SELECT 1 FROM t1 WHERE 1.0 IN (2.0)
000048  ----
000049  
000050  query I nosort
000051  SELECT 1 FROM t1 WHERE '1' IN ('2')
000052  ----
000053  
000054  query I nosort
000055  SELECT 1 FROM t1 WHERE 1 NOT IN (2)
000056  ----
000057  1
000058  1
000059  1
000060  
000061  query I nosort
000062  SELECT 1 FROM t1 WHERE 1.0 NOT IN (2.0)
000063  ----
000064  1
000065  1
000066  1
000067  
000068  query I nosort
000069  SELECT 1 FROM t1 WHERE '1' NOT IN ('2')
000070  ----
000071  1
000072  1
000073  1
000074  
000075  #
000076  # Row 2: empty RHS
000077  
000078  skipif mysql # empty RHS
000079  skipif mssql # empty RHS
000080  skipif oracle # empty RHS
000081  query I nosort
000082  SELECT 1 FROM t1 WHERE 1 IN ()
000083  ----
000084  
000085  skipif mysql # empty RHS
000086  skipif mssql # empty RHS
000087  skipif oracle # empty RHS
000088  query I nosort
000089  SELECT 1 FROM t1 WHERE 1.0 IN ()
000090  ----
000091  
000092  skipif mysql # empty RHS
000093  skipif mssql # empty RHS
000094  skipif oracle # empty RHS
000095  query I nosort
000096  SELECT 1 FROM t1 WHERE '1' IN ()
000097  ----
000098  
000099  skipif mysql # empty RHS
000100  skipif mssql # empty RHS
000101  skipif oracle # empty RHS
000102  query I nosort
000103  SELECT 1 FROM t1 WHERE NULL IN ()
000104  ----
000105  
000106  skipif mysql # empty RHS
000107  skipif mssql # empty RHS
000108  skipif oracle # empty RHS
000109  query I nosort
000110  SELECT 1 FROM t1 WHERE 1 NOT IN ()
000111  ----
000112  1
000113  1
000114  1
000115  
000116  skipif mysql # empty RHS
000117  skipif mssql # empty RHS
000118  skipif oracle # empty RHS
000119  query I nosort
000120  SELECT 1 FROM t1 WHERE 1.0 NOT IN ()
000121  ----
000122  1
000123  1
000124  1
000125  
000126  skipif mysql # empty RHS
000127  skipif mssql # empty RHS
000128  skipif oracle # empty RHS
000129  query I nosort
000130  SELECT 1 FROM t1 WHERE '1' NOT IN ()
000131  ----
000132  1
000133  1
000134  1
000135  
000136  skipif mysql # empty RHS
000137  skipif mssql # empty RHS
000138  skipif oracle # empty RHS
000139  query I nosort
000140  SELECT 1 FROM t1 WHERE NULL NOT IN ()
000141  ----
000142  1
000143  1
000144  1
000145  
000146  # Row 3:
000147  
000148  query I nosort
000149  SELECT 1 FROM t1 WHERE 1 IN ( NULL, 1 )
000150  ----
000151  1
000152  1
000153  1
000154  
000155  query I nosort
000156  SELECT 1 FROM t1 WHERE 1.0 IN ( NULL, 1.0 )
000157  ----
000158  1
000159  1
000160  1
000161  
000162  query I nosort
000163  SELECT 1 FROM t1 WHERE '1' IN ( NULL, '1' )
000164  ----
000165  1
000166  1
000167  1
000168  
000169  query I nosort
000170  SELECT 1 FROM t1 WHERE 1 NOT IN ( NULL, 1 )
000171  ----
000172  
000173  query I nosort
000174  SELECT 1 FROM t1 WHERE 1.0 NOT IN ( NULL, 1.0 )
000175  ----
000176  
000177  query I nosort
000178  SELECT 1 FROM t1 WHERE '1' NOT IN ( NULL, '1' )
000179  ----
000180  
000181  query I nosort
000182  SELECT 1 FROM t1 WHERE 1 IN ( 1 )
000183  ----
000184  1
000185  1
000186  1
000187  
000188  query I nosort
000189  SELECT 1 FROM t1 WHERE 1.0 IN ( 1.0 )
000190  ----
000191  1
000192  1
000193  1
000194  
000195  query I nosort
000196  SELECT 1 FROM t1 WHERE '1' IN ( '1' )
000197  ----
000198  1
000199  1
000200  1
000201  
000202  query I nosort
000203  SELECT 1 FROM t1 WHERE 1 NOT IN ( 1 )
000204  ----
000205  
000206  query I nosort
000207  SELECT 1 FROM t1 WHERE 1.0 NOT IN ( 1.0 )
000208  ----
000209  
000210  query I nosort
000211  SELECT 1 FROM t1 WHERE '1' NOT IN ( '1' )
000212  ----
000213  
000214  #
000215  # Row 4: RHS contains NULL, LHS not in RHS
000216  
000217  query I nosort
000218  SELECT 1 FROM t1 WHERE 1 IN ( NULL, 2 )
000219  ----
000220  
000221  query I nosort
000222  SELECT 1 FROM t1 WHERE 1.0 IN ( NULL, 2.0 )
000223  ----
000224  
000225  query I nosort
000226  SELECT 1 FROM t1 WHERE '1' IN ( NULL, '2' )
000227  ----
000228  
000229  query I nosort
000230  SELECT 1 FROM t1 WHERE 1 NOT IN ( NULL, 2 )
000231  ----
000232  
000233  query I nosort
000234  SELECT 1 FROM t1 WHERE 1.0 NOT IN ( NULL, 2.0 )
000235  ----
000236  
000237  query I nosort
000238  SELECT 1 FROM t1 WHERE '1' NOT IN ( NULL, '2' )
000239  ----
000240  
000241  #
000242  # Row 5: LHS is NULL
000243  
000244  query I nosort
000245  SELECT 1 FROM t1 WHERE NULL IN ( 1 )
000246  ----
000247  
000248  query I nosort
000249  SELECT 1 FROM t1 WHERE NULL IN ( 1.0 )
000250  ----
000251  
000252  query I nosort
000253  SELECT 1 FROM t1 WHERE NULL IN ( '1' )
000254  ----
000255  
000256  query I nosort
000257  SELECT 1 FROM t1 WHERE NULL NOT IN ( 1 )
000258  ----
000259  
000260  query I nosort
000261  SELECT 1 FROM t1 WHERE NULL NOT IN ( 1.0 )
000262  ----
000263  
000264  query I nosort
000265  SELECT 1 FROM t1 WHERE NULL NOT IN ( '1' )
000266  ----
000267  
000268  query I nosort
000269  SELECT 1 FROM t1 WHERE NULL IN ( NULL, 1 )
000270  ----
000271  
000272  query I nosort
000273  SELECT 1 FROM t1 WHERE NULL IN ( NULL, 1.0 )
000274  ----
000275  
000276  query I nosort
000277  SELECT 1 FROM t1 WHERE NULL IN ( NULL, '1' )
000278  ----
000279  
000280  query I nosort
000281  SELECT 1 FROM t1 WHERE NULL NOT IN ( NULL, 1 )
000282  ----
000283  
000284  query I nosort
000285  SELECT 1 FROM t1 WHERE NULL NOT IN ( NULL, 1.0 )
000286  ----
000287  
000288  query I nosort
000289  SELECT 1 FROM t1 WHERE NULL NOT IN ( NULL, '1' )
000290  ----
000291  
000292  # EVIDENCE-OF: R-35033-20570 The subquery on the right of an IN or NOT
000293  # IN operator must be a scalar subquery if the left expression is not a
000294  # row value expression.
000295  
000296  query I nosort
000297  SELECT 1 FROM t1 WHERE 1 IN (SELECT 1)
000298  ----
000299  1
000300  1
000301  1
000302  
000303  statement error
000304  SELECT 1 FROM t1 WHERE 1 IN (SELECT 1,2)
000305  
000306  statement error
000307  SELECT 1 FROM t1 WHERE 1 IN (SELECT x,y FROM t1)
000308  
000309  statement error
000310  SELECT 1 FROM t1 WHERE 1 IN (SELECT * FROM t1)
000311  
000312  statement error
000313  SELECT 1 FROM t1 WHERE 1 IN (SELECT min(x),max(x) FROM t1)