github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/sem/tree/testdata/eval/any_some_all (about)

     1  # ANY, SOME, and ALL expressions.
     2  
     3  eval
     4  1   = ANY ARRAY[]
     5  ----
     6  false
     7  
     8  eval
     9  1   = ANY (ARRAY[2, 3, 4])
    10  ----
    11  false
    12  
    13  eval
    14  1   = ANY (ARRAY[1, 3, 4])
    15  ----
    16  true
    17  
    18  eval
    19  1+1 = ANY (ARRAY[2, 3, 4])
    20  ----
    21  true
    22  
    23  eval
    24  1+1 = ANY  (ARRAY[1, 3, 4])
    25  ----
    26  false
    27  
    28  eval
    29  'foo' LIKE ANY ARRAY[]
    30  ----
    31  false
    32  
    33  eval
    34  'foo' LIKE ANY (ARRAY['bar', 'baz'])
    35  ----
    36  false
    37  
    38  eval
    39  'foo' LIKE ANY (ARRAY['foo', 'bar', 'baz'])
    40  ----
    41  true
    42  
    43  eval
    44  1+1 = SOME (ARRAY[1, 3, 4])
    45  ----
    46  false
    47  
    48  eval
    49  1   = ALL (ARRAY[])
    50  ----
    51  true
    52  
    53  eval
    54  1   = ALL (ARRAY[1, 1, 1])
    55  ----
    56  true
    57  
    58  eval
    59  1+1 = ALL (ARRAY[2, 3, 4])
    60  ----
    61  false
    62  
    63  eval
    64  1+1 = ALL (ARRAY[2, 2, 2])
    65  ----
    66  true
    67  
    68  eval
    69  'foo' LIKE ALL ARRAY[]
    70  ----
    71  true
    72  
    73  eval
    74  'foo' LIKE ALL (ARRAY['foo', 'bar', 'baz'])
    75  ----
    76  false
    77  
    78  eval
    79  'foo' LIKE ALL (ARRAY['foo', 'foo'])
    80  ----
    81  true
    82  
    83  eval
    84  1 = ANY (ARRAY[1, 2, NULL])
    85  ----
    86  true
    87  
    88  eval
    89  1 = ANY (ARRAY[2, 3, NULL])
    90  ----
    91  NULL
    92  
    93  eval
    94  1 = ANY (ARRAY[NULL])
    95  ----
    96  NULL
    97  
    98  eval
    99  1 = ALL (ARRAY[1, 1, NULL])
   100  ----
   101  NULL
   102  
   103  # Regression test for #37547 - ensure that null RHS of comparisons with
   104  # suboperators are correctly handled. A null RHS always results in null.
   105  eval
   106  1 = ANY(NULL::int[])
   107  ----
   108  NULL
   109  
   110  eval
   111  1 = SOME(NULL::int[])
   112  ----
   113  NULL
   114  
   115  eval
   116  1 = ALL(NULL::int[])
   117  ----
   118  NULL
   119  
   120  eval
   121  'foo' LIKE ANY(NULL::string[])
   122  ----
   123  NULL
   124  
   125  eval
   126  NULL::int = ANY(NULL::int[])
   127  ----
   128  NULL
   129  
   130  eval
   131  NULL::int = SOME(NULL::int[])
   132  ----
   133  NULL
   134  
   135  eval
   136  NULL::int = ALL(NULL::int[])
   137  ----
   138  NULL
   139  
   140  eval
   141  NULL::string LIKE ANY(NULL::string[])
   142  ----
   143  NULL
   144  
   145  # A null LHS has different behavior if the array is empty or not
   146  eval
   147  NULL::int = ANY(ARRAY[1,2])
   148  ----
   149  NULL
   150  
   151  eval
   152  NULL::int = SOME(ARRAY[1,2])
   153  ----
   154  NULL
   155  
   156  eval
   157  NULL::int = ALL(ARRAY[1,2])
   158  ----
   159  NULL
   160  
   161  eval
   162  NULL::int = ANY(ARRAY[]::int[])
   163  ----
   164  false
   165  
   166  eval
   167  NULL::int = SOME(ARRAY[]::int[])
   168  ----
   169  false
   170  
   171  eval
   172  NULL::int = ALL(ARRAY[]::int[])
   173  ----
   174  true
   175  
   176  eval
   177  NULL::string LIKE ANY(ARRAY[]::string[])
   178  ----
   179  false
   180  
   181  eval
   182  NULL::string LIKE SOME(ARRAY[]::string[])
   183  ----
   184  false
   185  
   186  eval
   187  NULL::string LIKE ALL(ARRAY[]::string[])
   188  ----
   189  true
   190  
   191  eval
   192  1 = ALL (ARRAY[1, 2, NULL])
   193  ----
   194  false
   195  
   196  eval
   197  1 = ALL (ARRAY[NULL])
   198  ----
   199  NULL
   200  
   201  eval
   202  1 =  ANY (ARRAY[1, 3, 5])
   203  ----
   204  true
   205  
   206  eval
   207  1 <  ANY (ARRAY[1, 3, 5])
   208  ----
   209  true
   210  
   211  eval
   212  1 >  ANY (ARRAY[1, 3, 5])
   213  ----
   214  false
   215  
   216  eval
   217  1 <= ANY (ARRAY[1, 3, 5])
   218  ----
   219  true
   220  
   221  eval
   222  1 >= ANY (ARRAY[1, 3, 5])
   223  ----
   224  true
   225  
   226  eval
   227  5 =  ANY (ARRAY[1, 3, 5])
   228  ----
   229  true
   230  
   231  eval
   232  5 <  ANY (ARRAY[1, 3, 5])
   233  ----
   234  false
   235  
   236  eval
   237  5 >  ANY (ARRAY[1, 3, 5])
   238  ----
   239  true
   240  
   241  eval
   242  5 <= ANY (ARRAY[1, 3, 5])
   243  ----
   244  true
   245  
   246  eval
   247  5 >= ANY (ARRAY[1, 3, 5])
   248  ----
   249  true
   250  
   251  eval
   252  'AAA' LIKE ANY (ARRAY['%A%', '%B%'])
   253  ----
   254  true
   255  
   256  eval
   257  'CCC' LIKE ANY (ARRAY['%A%', '%B%'])
   258  ----
   259  false
   260  
   261  eval
   262  'AAA' NOT LIKE ANY (ARRAY['%A%', '%B%'])
   263  ----
   264  true
   265  
   266  eval
   267  'AAA' NOT LIKE ANY (ARRAY['%A%', '%A%'])
   268  ----
   269  false
   270  
   271  eval
   272  'aaa' ILIKE ANY (ARRAY['%A%', '%B%'])
   273  ----
   274  true
   275  
   276  eval
   277  'ccc' ILIKE ANY (ARRAY['%A%', '%B%'])
   278  ----
   279  false
   280  
   281  eval
   282  'aaa' NOT ILIKE ANY (ARRAY['%A%', '%B%'])
   283  ----
   284  true
   285  
   286  eval
   287  'aaa' NOT ILIKE ANY (ARRAY['%A%', '%A%'])
   288  ----
   289  false
   290  
   291  # Regression test for #40841 -- make sure LIKE can handle nulls.
   292  eval
   293  NULL::string LIKE ANY(ARRAY['bar', 'baz'])
   294  ----
   295  NULL
   296  
   297  eval
   298  NULL::string ILIKE ANY(ARRAY['bar%', 'baz'])
   299  ----
   300  NULL
   301  
   302  eval
   303  NULL::string LIKE ANY(ARRAY['bar', NULL])
   304  ----
   305  NULL