github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/idxconstraint/testdata/tuple (about)

     1  # Tests with variable IN tuple.
     2  
     3  index-constraints vars=(int) index=(@1)
     4  @1 IN (1, 2, 3)
     5  ----
     6  [/1 - /3]
     7  
     8  index-constraints vars=(int) index=(@1 desc)
     9  @1 IN (1, 2, 3)
    10  ----
    11  [/3 - /1]
    12  
    13  index-constraints vars=(int) index=(@1)
    14  @1 IN (1, 5, 1, 4)
    15  ----
    16  [/1 - /1]
    17  [/4 - /5]
    18  
    19  index-constraints vars=(int) index=(@1 desc)
    20  @1 IN (1, 5, 1, 4)
    21  ----
    22  [/5 - /4]
    23  [/1 - /1]
    24  
    25  index-constraints vars=(int) index=(@1)
    26  @1 IN (1, 2, 3, NULL)
    27  ----
    28  [/1 - /3]
    29  
    30  index-constraints vars=(int, int) index=(@1, @2)
    31  @1 = 1 AND @2 IN (1, 2, 3)
    32  ----
    33  [/1/1 - /1/3]
    34  
    35  index-constraints vars=(int, int) index=(@1, @2 desc)
    36  @1 = 1 AND @2 IN (1, 2, 3)
    37  ----
    38  [/1/3 - /1/1]
    39  
    40  index-constraints vars=(int, int) index=(@1, @2)
    41  @1 IN (1, 2) AND @2 IN (1, 2, 3)
    42  ----
    43  [/1/1 - /1/3]
    44  [/2/1 - /2/3]
    45  
    46  index-constraints vars=(int, int) index=(@1 desc, @2 desc)
    47  @1 IN (1, 2) AND @2 IN (1, 2, 3)
    48  ----
    49  [/2/3 - /2/1]
    50  [/1/3 - /1/1]
    51  
    52  index-constraints vars=(int, int) index=(@1, @2)
    53  @1 >= 2 AND @1 <= 4 AND @2 IN (1, 2, 3)
    54  ----
    55  [/2/1 - /4/3]
    56  Remaining filter: @2 IN (1, 2, 3)
    57  
    58  index-constraints vars=(int, int) index=(@1 desc, @2 desc)
    59  @1 >= 2 AND @1 <= 4 AND @2 IN (1, 2, 3)
    60  ----
    61  [/4/3 - /2/1]
    62  Remaining filter: @2 IN (1, 2, 3)
    63  
    64  
    65  index-constraints vars=(int, int) index=(@1, @2)
    66  @1 IN (1, 2, 3) AND @2 = 4
    67  ----
    68  [/1/4 - /1/4]
    69  [/2/4 - /2/4]
    70  [/3/4 - /3/4]
    71  
    72  index-constraints vars=(int, int) index=(@1 desc, @2)
    73  @1 IN (1, 2, 3) AND @2 = 4
    74  ----
    75  [/3/4 - /3/4]
    76  [/2/4 - /2/4]
    77  [/1/4 - /1/4]
    78  
    79  index-constraints vars=(int, int) index=(@1, @2 desc)
    80  @1 IN (1, 2, 3) AND @2 = 4
    81  ----
    82  [/1/4 - /1/4]
    83  [/2/4 - /2/4]
    84  [/3/4 - /3/4]
    85  
    86  index-constraints vars=(int, int) index=(@1, @2)
    87  @1 IN (1, 2, 3) AND @2 >= 2 AND @2 <= 4
    88  ----
    89  [/1/2 - /1/4]
    90  [/2/2 - /2/4]
    91  [/3/2 - /3/4]
    92  
    93  # Tests with tuple equality.
    94  
    95  index-constraints vars=(int, int, int) index=(@1, @2, @3)
    96  (@1, @2, @3) = (1, 2, 3)
    97  ----
    98  [/1/2/3 - /1/2/3]
    99  
   100  index-constraints vars=(int, int, int) index=(@1, @3)
   101  (@1, @2, @3) = (1, 2, 3)
   102  ----
   103  [/1/3 - /1/3]
   104  Remaining filter: @2 = 2
   105  
   106  index-constraints vars=(int, int, int) index=(@3, @2)
   107  (@1, @2, @3) = (1, 2, 3)
   108  ----
   109  [/3/2 - /3/2]
   110  Remaining filter: @1 = 1
   111  
   112  index-constraints vars=(int, int, int, int, int) index=(@1, @2, @3, @4, @5)
   113  (@1, @2, 3, (4, @5)) = (1, 2, @3, (@4, 5))
   114  ----
   115  [/1/2/3/4/5 - /1/2/3/4/5]
   116  
   117  index-constraints vars=(int, int, int, int) index=(@1, @2, @3, @4)
   118  (@1, @2, @3) = (1, 2, 3) AND @4 > 4
   119  ----
   120  [/1/2/3/5 - /1/2/3]
   121  
   122  index-constraints vars=(int, int, int, int) index=(@1, @2, @3, @4)
   123  @1 > 5 AND @1 < 10 AND (@2, @3, @4) = (2, 3, 4)
   124  ----
   125  [/6/2/3/4 - /9/2/3/4]
   126  Remaining filter: ((@2 = 2) AND (@3 = 3)) AND (@4 = 4)
   127  
   128  index-constraints \
   129    vars=(int, int, int, int) \
   130    index=(@1 desc, @2 desc, @3 desc, @4 desc)
   131  @1 > 5 AND @1 < 10 AND (@2, @3, @4) = (2, 3, 4)
   132  ----
   133  [/9/2/3/4 - /6/2/3/4]
   134  Remaining filter: ((@2 = 2) AND (@3 = 3)) AND (@4 = 4)
   135  
   136  # Tests with tuple inequalities.
   137  
   138  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   139  (@1, @2, @3) >= (1, 2, 3)
   140  ----
   141  [/1/2/3 - ]
   142  
   143  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   144  (@1, @2, @3) >= (1, 2, @1)
   145  ----
   146  [/1/2 - ]
   147  Remaining filter: (@1, @2, @3) >= (1, 2, @1)
   148  
   149  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   150  (@1, @2, @3) > (1, 2, 3)
   151  ----
   152  [/1/2/4 - ]
   153  
   154  index-constraints vars=(int, int, int) index=(@1, @2)
   155  (@1, @2, @3) > (1, 2, 3)
   156  ----
   157  [/1/2 - ]
   158  Remaining filter: (@1, @2, @3) > (1, 2, 3)
   159  
   160  index-constraints vars=(int, int, int) index=(@1, @2)
   161  (@1, @2, @3) < (1, 2, 3)
   162  ----
   163  (/NULL - /1/2]
   164  Remaining filter: (@1, @2, @3) < (1, 2, 3)
   165  
   166  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   167  (@1, @2, @3) <= (1, 2, 3)
   168  ----
   169  (/NULL - /1/2/3]
   170  Remaining filter: (@1, @2, @3) <= (1, 2, 3)
   171  
   172  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   173  (@1, @2, @3) <= (1, 2, @1)
   174  ----
   175  (/NULL - /1/2]
   176  Remaining filter: (@1, @2, @3) <= (1, 2, @1)
   177  
   178  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   179  (@1, @2, @3) < (1, 2, 3)
   180  ----
   181  (/NULL - /1/2/2]
   182  Remaining filter: (@1, @2, @3) < (1, 2, 3)
   183  
   184  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   185  (@1, @2, @3) < (1, 2, @1)
   186  ----
   187  (/NULL - /1/2]
   188  Remaining filter: (@1, @2, @3) < (1, 2, @1)
   189  
   190  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   191  (@1, @2, @3) != (1, 2, 3)
   192  ----
   193  [ - /1/2/2]
   194  [/1/2/4 - ]
   195  Remaining filter: (@1, @2, @3) != (1, 2, 3)
   196  
   197  index-constraints vars=(int, int, int) index=(@1 desc, @2 desc, @3 desc)
   198  (@1, @2, @3) != (1, 2, 3)
   199  ----
   200  [ - /1/2/4]
   201  [/1/2/2 - ]
   202  Remaining filter: (@1, @2, @3) != (1, 2, 3)
   203  
   204  index-constraints vars=(int, int, int) index=(@1 desc, @2, @3)
   205  (@1, @2, @3) != (1, 2, 3)
   206  ----
   207  [ - /1/2/2]
   208  [/1/2/4 - ]
   209  Remaining filter: (@1, @2, @3) != (1, 2, 3)
   210  
   211  index-constraints vars=(int, int, int) index=(@1 not null, @2, @3)
   212  (@1, @2, @3) != (1, 2, 3)
   213  ----
   214  [ - /1/2/2]
   215  [/1/2/4 - ]
   216  Remaining filter: (@1, @2, @3) != (1, 2, 3)
   217  
   218  index-constraints vars=(int, int, int) index=(@1 not null, @2 not null, @3 not null)
   219  (@1, @2, @3) != (1, 2, 3)
   220  ----
   221  [ - /1/2/2]
   222  [/1/2/4 - ]
   223  
   224  index-constraints vars=(int, int, int) index=(@1, @2 not null, @3 not null)
   225  (@1, @2, @3) != (1, 2, 3)
   226  ----
   227  [ - /1/2/2]
   228  [/1/2/4 - ]
   229  Remaining filter: (@1, @2, @3) != (1, 2, 3)
   230  
   231  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   232  (@1, @2, @3) != (1, 2, @1)
   233  ----
   234  [ - ]
   235  Remaining filter: (@1, @2, @3) != (1, 2, @1)
   236  
   237  index-constraints vars=(int, int, int) index=(@1 desc, @2 desc, @3 desc)
   238  (@1, @2, @3) >= (1, 2, 3)
   239  ----
   240  [ - /1/2/3]
   241  Remaining filter: (@1, @2, @3) >= (1, 2, 3)
   242  
   243  index-constraints vars=(int, int, int) index=(@1 desc, @2 desc, @3)
   244  (@1, @2, @3) > (1, 2, 3)
   245  ----
   246  [ - /1/2]
   247  Remaining filter: (@1, @2, @3) > (1, 2, 3)
   248  
   249  index-constraints vars=(int, int, int) index=(@1, @2, @3 desc)
   250  (@1, @2, @3) > (1, 2, 3)
   251  ----
   252  [/1/2 - ]
   253  Remaining filter: (@1, @2, @3) > (1, 2, 3)
   254  
   255  index-constraints vars=(int, int, int) index=(@1, @2, @3 desc)
   256  (@2, @3) > (1, 2)
   257  ----
   258  [ - ]
   259  Remaining filter: (@2, @3) > (1, 2)
   260  
   261  index-constraints vars=(int, int) index=(@1, @2)
   262  (@1, @2) >= (1, 2) AND (@1, @2) <= (3, 4)
   263  ----
   264  [/1/2 - /3/4]
   265  Remaining filter: (@1, @2) <= (3, 4)
   266  
   267  index-constraints vars=(int, int) index=(@1, @2)
   268  (@1, @2) BETWEEN (1, 2) AND (3, 4)
   269  ----
   270  [/1/2 - /3/4]
   271  Remaining filter: (@1, @2) <= (3, 4)
   272  
   273  index-constraints vars=(int, int, int, int) index=(@1, @2, @3, @4)
   274  (@1, @2, @4) BETWEEN (1, 2, 3) AND (4, 5, 6)
   275  ----
   276  [/1/2 - /4/5]
   277  Remaining filter: ((@1, @2, @4) >= (1, 2, 3)) AND ((@1, @2, @4) <= (4, 5, 6))
   278  
   279  index-constraints vars=(int, bool) index=(@1, @2)
   280  (@1, @2) > (1, true)
   281  ----
   282  (/1/true - ]
   283  
   284  index-constraints vars=(int, bool) index=(@1, @2)
   285  (@1, @2) < (1, false)
   286  ----
   287  (/NULL - /1/false)
   288  Remaining filter: (@1, @2) < (1, false)
   289  
   290  index-constraints vars=(int, int, int) index=(@1 not null, @2 not null, @3 not null)
   291  (@1, @2, @3) <= (1, 2, 3)
   292  ----
   293  [ - /1/2/3]
   294  
   295  index-constraints vars=(int, int, int) index=(@1 not null, @2 not null, @3 not null)
   296  (@1, @2, @3) >= (1, 2, 3)
   297  ----
   298  [/1/2/3 - ]
   299  
   300  index-constraints vars=(int, int, int) index=(@1 not null, @2 not null, @3 not null)
   301  (@1, @2, @3) < (1, 2, 3)
   302  ----
   303  [ - /1/2/2]
   304  
   305  index-constraints vars=(int, int, int) index=(@1 not null, @2 not null, @3 not null)
   306  (@1, @2, @3) > (1, 2, 3)
   307  ----
   308  [/1/2/4 - ]
   309  
   310  index-constraints vars=(int, int, int) index=(@1, @2 not null, @3 not null)
   311  (@1, @2, @3) <= (1, 2, 3)
   312  ----
   313  (/NULL - /1/2/3]
   314  
   315  index-constraints vars=(int, int, int) index=(@1, @2 not null, @3)
   316  (@1, @2, @3) <= (1, 2, 3)
   317  ----
   318  (/NULL - /1/2/3]
   319  Remaining filter: (@1, @2, @3) <= (1, 2, 3)
   320  
   321  index-constraints vars=(int, int, int) index=(@1, @2, @3 not null)
   322  (@1, @2, @3) <= (1, 2, 3)
   323  ----
   324  (/NULL - /1/2/3]
   325  Remaining filter: (@1, @2, @3) <= (1, 2, 3)
   326  
   327  index-constraints \
   328    vars=(int, int, int) \
   329    index=(@1 desc not null, @2 desc not null, @3 desc not null)
   330  (@1, @2, @3) > (1, 2, 3)
   331  ----
   332  [ - /1/2/4]
   333  
   334  index-constraints vars=(int, int, int) index=(@1 desc, @2 desc not null, @3 desc)
   335  (@1, @2, @3) > (1, 2, 3)
   336  ----
   337  [ - /1/2/4]
   338  Remaining filter: (@1, @2, @3) > (1, 2, 3)
   339  
   340  index-constraints vars=(int, int, int) index=(@1 desc, @2 desc, @3 desc not null)
   341  (@1, @2, @3) > (1, 2, 3)
   342  ----
   343  [ - /1/2/4]
   344  Remaining filter: (@1, @2, @3) > (1, 2, 3)
   345  
   346  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   347  (@1, @3, @2) != (1, NULL, 2)
   348  ----
   349  [ - ]
   350  Remaining filter: (@1, @3, @2) != (1, NULL, 2)
   351  
   352  index-constraints vars=(int, int, int) index=(@1 not null, @2 not null)
   353  (@1, @2, @3) > (1, 2, 3)
   354  ----
   355  [/1/2 - ]
   356  Remaining filter: (@1, @2, @3) > (1, 2, 3)
   357  
   358  index-constraints vars=(int, int, int) index=(@1 not null, @2 not null)
   359  (@1, @2, @3) <= (1, 2, 3)
   360  ----
   361  [ - /1/2]
   362  Remaining filter: (@1, @2, @3) <= (1, 2, 3)
   363  
   364  # Cases with NULLs in tuple inequalities. These conditions are true only when
   365  # they don't depend on the NULL value, i.e. when the inequality holds true for
   366  # the prefix up to the first NULL.
   367  
   368  index-constraints vars=(int, int) index=(@1, @2)
   369  (@1, @2) > (1, NULL)
   370  ----
   371  [/2 - ]
   372  
   373  index-constraints vars=(int, int) index=(@1, @2)
   374  (@1, @2) >= (1, NULL)
   375  ----
   376  [/2 - ]
   377  
   378  index-constraints vars=(int, int) index=(@1, @2)
   379  (@1, @2) < (1, NULL)
   380  ----
   381  (/NULL - /0]
   382  
   383  index-constraints vars=(int, int) index=(@1 not null, @2)
   384  (@1, @2) < (1, NULL)
   385  ----
   386  [ - /0]
   387  
   388  index-constraints vars=(int, int) index=(@1, @2)
   389  (@1, @2) <= (1, NULL)
   390  ----
   391  (/NULL - /0]
   392  
   393  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   394  (@1, @2, @3) < (1, NULL, 1)
   395  ----
   396  (/NULL - /0]
   397  
   398  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   399  (@1, @2, @3) >= (1, NULL, 1)
   400  ----
   401  [/2 - ]
   402  
   403  # TODO(radu): here we could be smarter - the condition below is equivalent to
   404  # (@1, @3) != (1, 3).
   405  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   406  (@1, @2, @3) != (1, NULL, 3)
   407  ----
   408  [ - ]
   409  Remaining filter: (@1, @2, @3) != (1, NULL, 3)
   410  
   411  # Tests with tuple IN tuple.
   412  
   413  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   414  (@1, @2, @3) IN ((1, 2, 3), (4, 5, 6))
   415  ----
   416  [/1/2/3 - /1/2/3]
   417  [/4/5/6 - /4/5/6]
   418  
   419  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   420  (@1, @2, @3) IN ((4, 5, 6), (1, 2, 3))
   421  ----
   422  [/1/2/3 - /1/2/3]
   423  [/4/5/6 - /4/5/6]
   424  
   425  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   426  (@1, @2, @3) IN ((1, 2, 3), (1, 2, 3))
   427  ----
   428  [/1/2/3 - /1/2/3]
   429  
   430  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   431  (@1, @2, @3) IN ((1, 2, 3), (4, 5, 6), (1, 2, 3))
   432  ----
   433  [/1/2/3 - /1/2/3]
   434  [/4/5/6 - /4/5/6]
   435  
   436  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   437  (@1+5, @1, @1+@2, @2) IN ((1, 5, 1, 6), (2, 7, 2, 8), (3, 9, 3, 10))
   438  ----
   439  [/5/6 - /5/6]
   440  [/7/8 - /7/8]
   441  [/9/10 - /9/10]
   442  Remaining filter: (@1 + 5, @1, @1 + @2, @2) IN ((1, 5, 1, 6), (2, 7, 2, 8), (3, 9, 3, 10))
   443  
   444  # Test that we properly handle NULLs inside IN tuples.
   445  index-constraints vars=(int, int) index=(@1, @2)
   446  (@1, @2) IN ((1, 2), (3, NULL))
   447  ----
   448  [/1/2 - /1/2]
   449  
   450  index-constraints vars=(int, int) index=(@1, @2)
   451  (@1, @2) IN ((3, NULL))
   452  ----
   453  
   454  index-constraints vars=(int, int) index=(@1, @2)
   455  (@1, @2) IN ((1, 2), (NULL, 4))
   456  ----
   457  [/1/2 - /1/2]
   458  
   459  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   460  (@1, @2, @3) IN ((1, 2, 3), (4, 5, 6), (NULL, 8, 9))
   461  ----
   462  [/1/2/3 - /1/2/3]
   463  [/4/5/6 - /4/5/6]
   464  
   465  # Verify that we sort and de-duplicate if we "project" the tuples;
   466  # in this case the expression becomes:
   467  #   (@1, @2) IN ((5, 5), (4, 4), (5, 5))
   468  index-constraints vars=(int, int, int, int) index=(@2, @4)
   469  (@1, @2, @3, @4) IN ((1, 5, 1, 5), (2, 4, 2, 4), (3, 5, 3, 5))
   470  ----
   471  [/4/4 - /4/4]
   472  [/5/5 - /5/5]
   473  Remaining filter: (@1, @2, @3, @4) IN ((1, 5, 1, 5), (2, 4, 2, 4), (3, 5, 3, 5))
   474  
   475  index-constraints vars=(int, int, int, int) index=(@2)
   476  (@1, @2, @3, @4) IN ((1, 5, 1, 5), (2, 4, 2, 4), (3, 5, 3, 5))
   477  ----
   478  [/4 - /5]
   479  Remaining filter: (@1, @2, @3, @4) IN ((1, 5, 1, 5), (2, 4, 2, 4), (3, 5, 3, 5))
   480  
   481  index-constraints vars=(int, int) index=(@1, @2)
   482  (@2, @1) IN ((1, 5), (2, 1), (3, 4), (4, 1))
   483  ----
   484  [/1/2 - /1/2]
   485  [/1/4 - /1/4]
   486  [/4/3 - /4/3]
   487  [/5/1 - /5/1]
   488  
   489  index-constraints vars=(int, int) index=(@1 desc, @2)
   490  (@2, @1) IN ((1, 5), (2, 1), (3, 4), (4, 1))
   491  ----
   492  [/5/1 - /5/1]
   493  [/4/3 - /4/3]
   494  [/1/2 - /1/2]
   495  [/1/4 - /1/4]
   496  
   497  index-constraints vars=(int, int) index=(@1, @2 desc)
   498  (@2, @1) IN ((1, 5), (2, 1), (3, 4), (4, 1))
   499  ----
   500  [/1/4 - /1/4]
   501  [/1/2 - /1/2]
   502  [/4/3 - /4/3]
   503  [/5/1 - /5/1]
   504  
   505  index-constraints vars=(int, int) index=(@1 desc, @2 desc)
   506  (@2, @1) IN ((1, 5), (2, 1), (3, 4), (4, 1))
   507  ----
   508  [/5/1 - /5/1]
   509  [/4/3 - /4/3]
   510  [/1/4 - /1/4]
   511  [/1/2 - /1/2]
   512  
   513  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   514  @1 = 1 AND (@2, @3) IN ((2, 3), (4, 5), (6, 7))
   515  ----
   516  [/1/2/3 - /1/2/3]
   517  [/1/4/5 - /1/4/5]
   518  [/1/6/7 - /1/6/7]
   519  
   520  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   521  @3 = 1 AND (@1, @2) IN ((2, 3), (4, 5), (6, 7))
   522  ----
   523  [/2/3/1 - /2/3/1]
   524  [/4/5/1 - /4/5/1]
   525  [/6/7/1 - /6/7/1]
   526  
   527  # Here the best we can do is to effectively break up the IN constraint into
   528  # constraints on @1 and on @3, which results in more spans than we need.
   529  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   530  @2 = 1 AND (@1, @3) IN ((2, 3), (4, 5), (6, 7))
   531  ----
   532  [/2/1/3 - /2/1/3]
   533  [/2/1/5 - /2/1/5]
   534  [/2/1/7 - /2/1/7]
   535  [/4/1/3 - /4/1/3]
   536  [/4/1/5 - /4/1/5]
   537  [/4/1/7 - /4/1/7]
   538  [/6/1/3 - /6/1/3]
   539  [/6/1/5 - /6/1/5]
   540  [/6/1/7 - /6/1/7]
   541  Remaining filter: (@1, @3) IN ((2, 3), (4, 5), (6, 7))
   542  
   543  index-constraints vars=(int, int, int) index=(@1, @2, @3)
   544  @1 > 1 AND (@2, @3) IN ((2, 3), (4, 5), (6, 7))
   545  ----
   546  [/2/2/3 - ]
   547  Remaining filter: (@2, @3) IN ((2, 3), (4, 5), (6, 7))