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

     1  exec-ddl
     2  CREATE TABLE a (x INT, y INT)
     3  ----
     4  
     5  exec-ddl
     6  CREATE TABLE kuv (k INT PRIMARY KEY, u FLOAT, v STRING)
     7  ----
     8  
     9  opt
    10  SELECT * FROM a WHERE x > 1
    11  ----
    12  select
    13   ├── columns: x:1(int!null) y:2(int)
    14   ├── prune: (2)
    15   ├── scan a
    16   │    ├── columns: x:1(int) y:2(int)
    17   │    └── prune: (1,2)
    18   └── filters
    19        └── gt [type=bool, outer=(1), constraints=(/1: [/2 - ]; tight)]
    20             ├── variable: x:1 [type=int]
    21             └── const: 1 [type=int]
    22  
    23  # Verify that 1 is determined to be constant (from the intersection of the
    24  # constraints).
    25  opt
    26  SELECT * FROM a WHERE x > 0 AND x < 2
    27  ----
    28  select
    29   ├── columns: x:1(int!null) y:2(int)
    30   ├── fd: ()-->(1)
    31   ├── prune: (2)
    32   ├── scan a
    33   │    ├── columns: x:1(int) y:2(int)
    34   │    └── prune: (1,2)
    35   └── filters
    36        └── range [type=bool, outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)]
    37             └── and [type=bool]
    38                  ├── gt [type=bool]
    39                  │    ├── variable: x:1 [type=int]
    40                  │    └── const: 0 [type=int]
    41                  └── lt [type=bool]
    42                       ├── variable: x:1 [type=int]
    43                       └── const: 2 [type=int]
    44  
    45  opt
    46  SELECT * FROM a WHERE x >= 1
    47  ----
    48  select
    49   ├── columns: x:1(int!null) y:2(int)
    50   ├── prune: (2)
    51   ├── scan a
    52   │    ├── columns: x:1(int) y:2(int)
    53   │    └── prune: (1,2)
    54   └── filters
    55        └── ge [type=bool, outer=(1), constraints=(/1: [/1 - ]; tight)]
    56             ├── variable: x:1 [type=int]
    57             └── const: 1 [type=int]
    58  
    59  opt
    60  SELECT * FROM a WHERE x < 1
    61  ----
    62  select
    63   ├── columns: x:1(int!null) y:2(int)
    64   ├── prune: (2)
    65   ├── scan a
    66   │    ├── columns: x:1(int) y:2(int)
    67   │    └── prune: (1,2)
    68   └── filters
    69        └── lt [type=bool, outer=(1), constraints=(/1: (/NULL - /0]; tight)]
    70             ├── variable: x:1 [type=int]
    71             └── const: 1 [type=int]
    72  
    73  opt
    74  SELECT * FROM a WHERE x <= 1
    75  ----
    76  select
    77   ├── columns: x:1(int!null) y:2(int)
    78   ├── prune: (2)
    79   ├── scan a
    80   │    ├── columns: x:1(int) y:2(int)
    81   │    └── prune: (1,2)
    82   └── filters
    83        └── le [type=bool, outer=(1), constraints=(/1: (/NULL - /1]; tight)]
    84             ├── variable: x:1 [type=int]
    85             └── const: 1 [type=int]
    86  
    87  opt
    88  SELECT * FROM a WHERE x = 1
    89  ----
    90  select
    91   ├── columns: x:1(int!null) y:2(int)
    92   ├── fd: ()-->(1)
    93   ├── prune: (2)
    94   ├── scan a
    95   │    ├── columns: x:1(int) y:2(int)
    96   │    └── prune: (1,2)
    97   └── filters
    98        └── eq [type=bool, outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)]
    99             ├── variable: x:1 [type=int]
   100             └── const: 1 [type=int]
   101  
   102  opt
   103  SELECT * FROM a WHERE x > 1 AND x < 5
   104  ----
   105  select
   106   ├── columns: x:1(int!null) y:2(int)
   107   ├── prune: (2)
   108   ├── scan a
   109   │    ├── columns: x:1(int) y:2(int)
   110   │    └── prune: (1,2)
   111   └── filters
   112        └── range [type=bool, outer=(1), constraints=(/1: [/2 - /4]; tight)]
   113             └── and [type=bool]
   114                  ├── gt [type=bool]
   115                  │    ├── variable: x:1 [type=int]
   116                  │    └── const: 1 [type=int]
   117                  └── lt [type=bool]
   118                       ├── variable: x:1 [type=int]
   119                       └── const: 5 [type=int]
   120  
   121  opt
   122  SELECT * FROM a WHERE x = 1 AND y = 5
   123  ----
   124  select
   125   ├── columns: x:1(int!null) y:2(int!null)
   126   ├── fd: ()-->(1,2)
   127   ├── scan a
   128   │    ├── columns: x:1(int) y:2(int)
   129   │    └── prune: (1,2)
   130   └── filters
   131        ├── eq [type=bool, outer=(1), constraints=(/1: [/1 - /1]; tight), fd=()-->(1)]
   132        │    ├── variable: x:1 [type=int]
   133        │    └── const: 1 [type=int]
   134        └── eq [type=bool, outer=(2), constraints=(/2: [/5 - /5]; tight), fd=()-->(2)]
   135             ├── variable: y:2 [type=int]
   136             └── const: 5 [type=int]
   137  
   138  opt
   139  SELECT * FROM a WHERE x > 1 AND x < 5 AND y >= 7 AND y <= 9
   140  ----
   141  select
   142   ├── columns: x:1(int!null) y:2(int!null)
   143   ├── scan a
   144   │    ├── columns: x:1(int) y:2(int)
   145   │    └── prune: (1,2)
   146   └── filters
   147        ├── range [type=bool, outer=(1), constraints=(/1: [/2 - /4]; tight)]
   148        │    └── and [type=bool]
   149        │         ├── gt [type=bool]
   150        │         │    ├── variable: x:1 [type=int]
   151        │         │    └── const: 1 [type=int]
   152        │         └── lt [type=bool]
   153        │              ├── variable: x:1 [type=int]
   154        │              └── const: 5 [type=int]
   155        └── range [type=bool, outer=(2), constraints=(/2: [/7 - /9]; tight)]
   156             └── and [type=bool]
   157                  ├── ge [type=bool]
   158                  │    ├── variable: y:2 [type=int]
   159                  │    └── const: 7 [type=int]
   160                  └── le [type=bool]
   161                       ├── variable: y:2 [type=int]
   162                       └── const: 9 [type=int]
   163  
   164  # Verify the resulting constraints are not tight.
   165  opt
   166  SELECT * FROM a WHERE x > 1 AND x < 5 AND x + y = 5
   167  ----
   168  select
   169   ├── columns: x:1(int!null) y:2(int)
   170   ├── scan a
   171   │    ├── columns: x:1(int) y:2(int)
   172   │    └── prune: (1,2)
   173   └── filters
   174        ├── range [type=bool, outer=(1), constraints=(/1: [/2 - /4]; tight)]
   175        │    └── and [type=bool]
   176        │         ├── gt [type=bool]
   177        │         │    ├── variable: x:1 [type=int]
   178        │         │    └── const: 1 [type=int]
   179        │         └── lt [type=bool]
   180        │              ├── variable: x:1 [type=int]
   181        │              └── const: 5 [type=int]
   182        └── eq [type=bool, outer=(1,2)]
   183             ├── plus [type=int]
   184             │    ├── variable: x:1 [type=int]
   185             │    └── variable: y:2 [type=int]
   186             └── const: 5 [type=int]
   187  
   188  opt
   189  SELECT * FROM a WHERE x > 1 AND x + y >= 5 AND x + y <= 7
   190  ----
   191  select
   192   ├── columns: x:1(int!null) y:2(int)
   193   ├── scan a
   194   │    ├── columns: x:1(int) y:2(int)
   195   │    └── prune: (1,2)
   196   └── filters
   197        ├── gt [type=bool, outer=(1), constraints=(/1: [/2 - ]; tight)]
   198        │    ├── variable: x:1 [type=int]
   199        │    └── const: 1 [type=int]
   200        ├── ge [type=bool, outer=(1,2)]
   201        │    ├── plus [type=int]
   202        │    │    ├── variable: x:1 [type=int]
   203        │    │    └── variable: y:2 [type=int]
   204        │    └── const: 5 [type=int]
   205        └── le [type=bool, outer=(1,2)]
   206             ├── plus [type=int]
   207             │    ├── variable: x:1 [type=int]
   208             │    └── variable: y:2 [type=int]
   209             └── const: 7 [type=int]
   210  
   211  # Verify that we ignore some mixed-type comparisons.
   212  opt
   213  SELECT * FROM a WHERE x > 1.5
   214  ----
   215  select
   216   ├── columns: x:1(int!null) y:2(int)
   217   ├── prune: (2)
   218   ├── scan a
   219   │    ├── columns: x:1(int) y:2(int)
   220   │    └── prune: (1,2)
   221   └── filters
   222        └── gt [type=bool, outer=(1), constraints=(/1: (/NULL - ])]
   223             ├── variable: x:1 [type=int]
   224             └── const: 1.5 [type=decimal]
   225  
   226  # This is a safe mixed-type comparison.
   227  opt
   228  SELECT * FROM kuv WHERE u > 1::INT
   229  ----
   230  select
   231   ├── columns: k:1(int!null) u:2(float!null) v:3(string)
   232   ├── key: (1)
   233   ├── fd: (1)-->(2,3)
   234   ├── prune: (1,3)
   235   ├── interesting orderings: (+1)
   236   ├── scan kuv
   237   │    ├── columns: k:1(int!null) u:2(float) v:3(string)
   238   │    ├── key: (1)
   239   │    ├── fd: (1)-->(2,3)
   240   │    ├── prune: (1-3)
   241   │    └── interesting orderings: (+1)
   242   └── filters
   243        └── gt [type=bool, outer=(2), constraints=(/2: [/1.0000000000000002 - ]; tight)]
   244             ├── variable: u:2 [type=float]
   245             └── const: 1.0 [type=float]
   246  
   247  opt
   248  SELECT * FROM kuv WHERE v <= 'foo' AND v >= 'bar'
   249  ----
   250  select
   251   ├── columns: k:1(int!null) u:2(float) v:3(string!null)
   252   ├── key: (1)
   253   ├── fd: (1)-->(2,3)
   254   ├── prune: (1,2)
   255   ├── interesting orderings: (+1)
   256   ├── scan kuv
   257   │    ├── columns: k:1(int!null) u:2(float) v:3(string)
   258   │    ├── key: (1)
   259   │    ├── fd: (1)-->(2,3)
   260   │    ├── prune: (1-3)
   261   │    └── interesting orderings: (+1)
   262   └── filters
   263        └── range [type=bool, outer=(3), constraints=(/3: [/'bar' - /'foo']; tight)]
   264             └── and [type=bool]
   265                  ├── le [type=bool]
   266                  │    ├── variable: v:3 [type=string]
   267                  │    └── const: 'foo' [type=string]
   268                  └── ge [type=bool]
   269                       ├── variable: v:3 [type=string]
   270                       └── const: 'bar' [type=string]
   271  
   272  # Test IN.
   273  opt
   274  SELECT * FROM a WHERE x IN (1, 2, 3, NULL)
   275  ----
   276  select
   277   ├── columns: x:1(int!null) y:2(int)
   278   ├── prune: (2)
   279   ├── scan a
   280   │    ├── columns: x:1(int) y:2(int)
   281   │    └── prune: (1,2)
   282   └── filters
   283        └── in [type=bool, outer=(1), constraints=(/1: [/1 - /1] [/2 - /2] [/3 - /3]; tight)]
   284             ├── variable: x:1 [type=int]
   285             └── tuple [type=tuple{unknown, int, int, int}]
   286                  ├── null [type=unknown]
   287                  ├── const: 1 [type=int]
   288                  ├── const: 2 [type=int]
   289                  └── const: 3 [type=int]
   290  
   291  opt
   292  SELECT * FROM a WHERE rowid IS NULL
   293  ----
   294  project
   295   ├── columns: x:1(int) y:2(int)
   296   ├── cardinality: [0 - 1]
   297   ├── key: ()
   298   ├── fd: ()-->(1,2)
   299   ├── prune: (1,2)
   300   └── scan a
   301        ├── columns: x:1(int) y:2(int) rowid:3(int!null)
   302        ├── constraint: contradiction
   303        ├── cardinality: [0 - 1]
   304        ├── key: ()
   305        ├── fd: ()-->(1-3)
   306        ├── prune: (1-3)
   307        └── interesting orderings: (+3)
   308  
   309  # Test IN in combination with another condition on the same column (which rules
   310  # out some of the entries in the IN condition).
   311  opt
   312  SELECT * FROM a WHERE x IN (1, 3, 5, 7, 9) AND x > 6
   313  ----
   314  select
   315   ├── columns: x:1(int!null) y:2(int)
   316   ├── prune: (2)
   317   ├── scan a
   318   │    ├── columns: x:1(int) y:2(int)
   319   │    └── prune: (1,2)
   320   └── filters
   321        └── range [type=bool, outer=(1), constraints=(/1: [/7 - /7] [/9 - /9]; tight)]
   322             └── and [type=bool]
   323                  ├── in [type=bool]
   324                  │    ├── variable: x:1 [type=int]
   325                  │    └── tuple [type=tuple{int, int, int, int, int}]
   326                  │         ├── const: 1 [type=int]
   327                  │         ├── const: 3 [type=int]
   328                  │         ├── const: 5 [type=int]
   329                  │         ├── const: 7 [type=int]
   330                  │         └── const: 9 [type=int]
   331                  └── gt [type=bool]
   332                       ├── variable: x:1 [type=int]
   333                       └── const: 6 [type=int]
   334  
   335  # Test IN in combination with a condition on another column.
   336  opt
   337  SELECT * FROM a WHERE x IN (1, 3) AND y > 4
   338  ----
   339  select
   340   ├── columns: x:1(int!null) y:2(int!null)
   341   ├── scan a
   342   │    ├── columns: x:1(int) y:2(int)
   343   │    └── prune: (1,2)
   344   └── filters
   345        ├── in [type=bool, outer=(1), constraints=(/1: [/1 - /1] [/3 - /3]; tight)]
   346        │    ├── variable: x:1 [type=int]
   347        │    └── tuple [type=tuple{int, int}]
   348        │         ├── const: 1 [type=int]
   349        │         └── const: 3 [type=int]
   350        └── gt [type=bool, outer=(2), constraints=(/2: [/5 - ]; tight)]
   351             ├── variable: y:2 [type=int]
   352             └── const: 4 [type=int]
   353  
   354  # Test tuple inequality.
   355  opt
   356  SELECT * FROM a WHERE (x, y) > (1, 2)
   357  ----
   358  select
   359   ├── columns: x:1(int!null) y:2(int)
   360   ├── scan a
   361   │    ├── columns: x:1(int) y:2(int)
   362   │    └── prune: (1,2)
   363   └── filters
   364        └── gt [type=bool, outer=(1,2), constraints=(/1/2: [/1/3 - ]; tight)]
   365             ├── tuple [type=tuple{int, int}]
   366             │    ├── variable: x:1 [type=int]
   367             │    └── variable: y:2 [type=int]
   368             └── tuple [type=tuple{int, int}]
   369                  ├── const: 1 [type=int]
   370                  └── const: 2 [type=int]
   371  
   372  opt
   373  SELECT * FROM a WHERE (x, y) >= (1, 2)
   374  ----
   375  select
   376   ├── columns: x:1(int!null) y:2(int)
   377   ├── scan a
   378   │    ├── columns: x:1(int) y:2(int)
   379   │    └── prune: (1,2)
   380   └── filters
   381        └── ge [type=bool, outer=(1,2), constraints=(/1/2: [/1/2 - ]; tight)]
   382             ├── tuple [type=tuple{int, int}]
   383             │    ├── variable: x:1 [type=int]
   384             │    └── variable: y:2 [type=int]
   385             └── tuple [type=tuple{int, int}]
   386                  ├── const: 1 [type=int]
   387                  └── const: 2 [type=int]
   388  
   389  opt
   390  SELECT * FROM a WHERE (x, y) < (1, 2)
   391  ----
   392  select
   393   ├── columns: x:1(int!null) y:2(int)
   394   ├── scan a
   395   │    ├── columns: x:1(int) y:2(int)
   396   │    └── prune: (1,2)
   397   └── filters
   398        └── lt [type=bool, outer=(1,2), constraints=(/1/2: (/NULL - /1/1]; tight)]
   399             ├── tuple [type=tuple{int, int}]
   400             │    ├── variable: x:1 [type=int]
   401             │    └── variable: y:2 [type=int]
   402             └── tuple [type=tuple{int, int}]
   403                  ├── const: 1 [type=int]
   404                  └── const: 2 [type=int]
   405  
   406  opt
   407  SELECT * FROM a WHERE (x, y) <= (1, 2)
   408  ----
   409  select
   410   ├── columns: x:1(int!null) y:2(int)
   411   ├── scan a
   412   │    ├── columns: x:1(int) y:2(int)
   413   │    └── prune: (1,2)
   414   └── filters
   415        └── le [type=bool, outer=(1,2), constraints=(/1/2: (/NULL - /1/2]; tight)]
   416             ├── tuple [type=tuple{int, int}]
   417             │    ├── variable: x:1 [type=int]
   418             │    └── variable: y:2 [type=int]
   419             └── tuple [type=tuple{int, int}]
   420                  ├── const: 1 [type=int]
   421                  └── const: 2 [type=int]
   422  
   423  # Test that we ignore tuple inequalities when the types don't match up.
   424  opt
   425  SELECT * FROM a WHERE (x, y) >= (1, 2.5)
   426  ----
   427  select
   428   ├── columns: x:1(int) y:2(int)
   429   ├── scan a
   430   │    ├── columns: x:1(int) y:2(int)
   431   │    └── prune: (1,2)
   432   └── filters
   433        └── ge [type=bool, outer=(1,2)]
   434             ├── tuple [type=tuple{int, int}]
   435             │    ├── variable: x:1 [type=int]
   436             │    └── variable: y:2 [type=int]
   437             └── tuple [type=tuple{int, decimal}]
   438                  ├── const: 1 [type=int]
   439                  └── const: 2.5 [type=decimal]
   440  
   441  # Test that we ignore tuple inequalities when they contain NULLs.
   442  opt
   443  SELECT * FROM a WHERE (x, y) >= (1, NULL)
   444  ----
   445  select
   446   ├── columns: x:1(int) y:2(int)
   447   ├── scan a
   448   │    ├── columns: x:1(int) y:2(int)
   449   │    └── prune: (1,2)
   450   └── filters
   451        └── ge [type=bool, outer=(1,2)]
   452             ├── tuple [type=tuple{int, int}]
   453             │    ├── variable: x:1 [type=int]
   454             │    └── variable: y:2 [type=int]
   455             └── tuple [type=tuple{int, unknown}]
   456                  ├── const: 1 [type=int]
   457                  └── null [type=unknown]
   458  
   459  # Test that we ignore tuple inequalities when we have something other than
   460  # simple variables in the left tuple.
   461  opt
   462  SELECT * FROM a WHERE (x, 1) >= (1, 2)
   463  ----
   464  select
   465   ├── columns: x:1(int) y:2(int)
   466   ├── prune: (2)
   467   ├── scan a
   468   │    ├── columns: x:1(int) y:2(int)
   469   │    └── prune: (1,2)
   470   └── filters
   471        └── ge [type=bool, outer=(1)]
   472             ├── tuple [type=tuple{int, int}]
   473             │    ├── variable: x:1 [type=int]
   474             │    └── const: 1 [type=int]
   475             └── tuple [type=tuple{int, int}]
   476                  ├── const: 1 [type=int]
   477                  └── const: 2 [type=int]
   478  
   479  exec-ddl
   480  CREATE TABLE abc (a INT, b BOOL, c STRING)
   481  ----
   482  
   483  opt
   484  SELECT * FROM abc WHERE a != 5
   485  ----
   486  select
   487   ├── columns: a:1(int!null) b:2(bool) c:3(string)
   488   ├── prune: (2,3)
   489   ├── scan abc
   490   │    ├── columns: a:1(int) b:2(bool) c:3(string)
   491   │    └── prune: (1-3)
   492   └── filters
   493        └── ne [type=bool, outer=(1), constraints=(/1: (/NULL - /4] [/6 - ]; tight)]
   494             ├── variable: a:1 [type=int]
   495             └── const: 5 [type=int]
   496  
   497  opt
   498  SELECT * FROM abc WHERE a IS DISTINCT FROM 5
   499  ----
   500  select
   501   ├── columns: a:1(int) b:2(bool) c:3(string)
   502   ├── prune: (2,3)
   503   ├── scan abc
   504   │    ├── columns: a:1(int) b:2(bool) c:3(string)
   505   │    └── prune: (1-3)
   506   └── filters
   507        └── is-not [type=bool, outer=(1), constraints=(/1: [ - /4] [/6 - ]; tight)]
   508             ├── variable: a:1 [type=int]
   509             └── const: 5 [type=int]
   510  
   511  opt
   512  SELECT * FROM abc WHERE b != true
   513  ----
   514  select
   515   ├── columns: a:1(int) b:2(bool!null) c:3(string)
   516   ├── prune: (1,3)
   517   ├── scan abc
   518   │    ├── columns: a:1(int) b:2(bool) c:3(string)
   519   │    └── prune: (1-3)
   520   └── filters
   521        └── ne [type=bool, outer=(2), constraints=(/2: (/NULL - /false]; tight)]
   522             ├── variable: b:2 [type=bool]
   523             └── true [type=bool]
   524  
   525  opt
   526  SELECT * FROM abc WHERE b != false
   527  ----
   528  select
   529   ├── columns: a:1(int) b:2(bool!null) c:3(string)
   530   ├── prune: (1,3)
   531   ├── scan abc
   532   │    ├── columns: a:1(int) b:2(bool) c:3(string)
   533   │    └── prune: (1-3)
   534   └── filters
   535        └── ne [type=bool, outer=(2), constraints=(/2: [/true - ]; tight)]
   536             ├── variable: b:2 [type=bool]
   537             └── false [type=bool]
   538  
   539  opt
   540  SELECT * FROM abc WHERE b IS NOT true
   541  ----
   542  select
   543   ├── columns: a:1(int) b:2(bool) c:3(string)
   544   ├── prune: (1,3)
   545   ├── scan abc
   546   │    ├── columns: a:1(int) b:2(bool) c:3(string)
   547   │    └── prune: (1-3)
   548   └── filters
   549        └── is-not [type=bool, outer=(2), constraints=(/2: [ - /false]; tight)]
   550             ├── variable: b:2 [type=bool]
   551             └── true [type=bool]
   552  
   553  opt
   554  SELECT * FROM abc WHERE b IS NOT false
   555  ----
   556  select
   557   ├── columns: a:1(int) b:2(bool) c:3(string)
   558   ├── prune: (1,3)
   559   ├── scan abc
   560   │    ├── columns: a:1(int) b:2(bool) c:3(string)
   561   │    └── prune: (1-3)
   562   └── filters
   563        └── is-not [type=bool, outer=(2), constraints=(/2: [ - /false) [/true - ]; tight)]
   564             ├── variable: b:2 [type=bool]
   565             └── false [type=bool]
   566  
   567  opt
   568  SELECT * FROM abc WHERE b
   569  ----
   570  select
   571   ├── columns: a:1(int) b:2(bool!null) c:3(string)
   572   ├── fd: ()-->(2)
   573   ├── prune: (1,3)
   574   ├── scan abc
   575   │    ├── columns: a:1(int) b:2(bool) c:3(string)
   576   │    └── prune: (1-3)
   577   └── filters
   578        └── variable: b:2 [type=bool, outer=(2), constraints=(/2: [/true - /true]; tight), fd=()-->(2)]
   579  
   580  opt
   581  SELECT * FROM abc WHERE NOT b
   582  ----
   583  select
   584   ├── columns: a:1(int) b:2(bool!null) c:3(string)
   585   ├── fd: ()-->(2)
   586   ├── prune: (1,3)
   587   ├── scan abc
   588   │    ├── columns: a:1(int) b:2(bool) c:3(string)
   589   │    └── prune: (1-3)
   590   └── filters
   591        └── not [type=bool, outer=(2), constraints=(/2: [/false - /false]; tight), fd=()-->(2)]
   592             └── variable: b:2 [type=bool]
   593  
   594  opt
   595  SELECT * FROM abc WHERE a > 5 AND b
   596  ----
   597  select
   598   ├── columns: a:1(int!null) b:2(bool!null) c:3(string)
   599   ├── fd: ()-->(2)
   600   ├── prune: (3)
   601   ├── scan abc
   602   │    ├── columns: a:1(int) b:2(bool) c:3(string)
   603   │    └── prune: (1-3)
   604   └── filters
   605        ├── gt [type=bool, outer=(1), constraints=(/1: [/6 - ]; tight)]
   606        │    ├── variable: a:1 [type=int]
   607        │    └── const: 5 [type=int]
   608        └── variable: b:2 [type=bool, outer=(2), constraints=(/2: [/true - /true]; tight), fd=()-->(2)]
   609  
   610  opt
   611  SELECT * FROM abc WHERE c != 'foo'
   612  ----
   613  select
   614   ├── columns: a:1(int) b:2(bool) c:3(string!null)
   615   ├── prune: (1,2)
   616   ├── scan abc
   617   │    ├── columns: a:1(int) b:2(bool) c:3(string)
   618   │    └── prune: (1-3)
   619   └── filters
   620        └── ne [type=bool, outer=(3), constraints=(/3: (/NULL - /'foo') [/e'foo\x00' - ]; tight)]
   621             ├── variable: c:3 [type=string]
   622             └── const: 'foo' [type=string]
   623  
   624  opt
   625  SELECT * FROM abc WHERE c IS DISTINCT FROM 'foo'
   626  ----
   627  select
   628   ├── columns: a:1(int) b:2(bool) c:3(string)
   629   ├── prune: (1,2)
   630   ├── scan abc
   631   │    ├── columns: a:1(int) b:2(bool) c:3(string)
   632   │    └── prune: (1-3)
   633   └── filters
   634        └── is-not [type=bool, outer=(3), constraints=(/3: [ - /'foo') [/e'foo\x00' - ]; tight)]
   635             ├── variable: c:3 [type=string]
   636             └── const: 'foo' [type=string]
   637  
   638  opt
   639  SELECT * FROM (SELECT (x, y) AS col FROM a) WHERE col > (1, 2)
   640  ----
   641  select
   642   ├── columns: col:4(tuple{int, int}!null)
   643   ├── project
   644   │    ├── columns: col:4(tuple{int, int})
   645   │    ├── prune: (4)
   646   │    ├── scan a
   647   │    │    ├── columns: x:1(int) y:2(int)
   648   │    │    └── prune: (1,2)
   649   │    └── projections
   650   │         └── tuple [as=col:4, type=tuple{int, int}, outer=(1,2)]
   651   │              ├── variable: x:1 [type=int]
   652   │              └── variable: y:2 [type=int]
   653   └── filters
   654        └── gt [type=bool, outer=(4), constraints=(/4: [/(1, 3) - ]; tight)]
   655             ├── variable: col:4 [type=tuple{int, int}]
   656             └── tuple [type=tuple{int, int}]
   657                  ├── const: 1 [type=int]
   658                  └── const: 2 [type=int]
   659  
   660  exec-ddl
   661  CREATE TABLE c
   662  (
   663      k INT PRIMARY KEY,
   664      u INT,
   665      v INT,
   666      INDEX v (v, u)
   667  )
   668  ----
   669  
   670  opt
   671  SELECT * FROM c WHERE (v, u) IN ((1, 2), (3, 50), (5, 100))
   672  ----
   673  scan c@v
   674   ├── columns: k:1(int!null) u:2(int!null) v:3(int!null)
   675   ├── constraint: /3/2/1
   676   │    ├── [/1/2 - /1/2]
   677   │    ├── [/3/50 - /3/50]
   678   │    └── [/5/100 - /5/100]
   679   ├── key: (1)
   680   ├── fd: (1)-->(2,3)
   681   ├── prune: (1)
   682   └── interesting orderings: (+1) (+3,+2,+1)
   683  
   684  opt format=hide-qual
   685  SELECT * FROM c WHERE (v, u) IN ((1, 2), (1, 3), (1, 4))
   686  ----
   687  scan c@v
   688   ├── columns: k:1(int!null) u:2(int!null) v:3(int!null)
   689   ├── constraint: /3/2/1: [/1/2 - /1/4]
   690   ├── key: (1)
   691   ├── fd: ()-->(3), (1)-->(2)
   692   ├── prune: (1)
   693   └── interesting orderings: (+1) (+3,+2,+1)
   694  
   695  opt format=hide-qual
   696  SELECT * FROM c WHERE (v, u) IN ((1, 2), (3, 2), (5, 2))
   697  ----
   698  scan c@v
   699   ├── columns: k:1(int!null) u:2(int!null) v:3(int!null)
   700   ├── constraint: /3/2/1
   701   │    ├── [/1/2 - /1/2]
   702   │    ├── [/3/2 - /3/2]
   703   │    └── [/5/2 - /5/2]
   704   ├── key: (1)
   705   ├── fd: ()-->(2), (1)-->(3)
   706   ├── prune: (1)
   707   └── interesting orderings: (+1) (+3,+2,+1)
   708  
   709  opt format=hide-qual
   710  SELECT * FROM c WHERE (v, u) IN ((1, 2), (1, 2), (1, 2))
   711  ----
   712  scan c@v
   713   ├── columns: k:1(int!null) u:2(int!null) v:3(int!null)
   714   ├── constraint: /3/2/1: [/1/2 - /1/2]
   715   ├── key: (1)
   716   ├── fd: ()-->(2,3)
   717   ├── prune: (1)
   718   └── interesting orderings: (+1) (+3,+2,+1)
   719  
   720  opt format=hide-qual
   721  SELECT * FROM c WHERE (v, u) IN ((1, 2), (1, 3), (1, 4))
   722  ----
   723  scan c@v
   724   ├── columns: k:1(int!null) u:2(int!null) v:3(int!null)
   725   ├── constraint: /3/2/1: [/1/2 - /1/4]
   726   ├── key: (1)
   727   ├── fd: ()-->(3), (1)-->(2)
   728   ├── prune: (1)
   729   └── interesting orderings: (+1) (+3,+2,+1)
   730  
   731  opt format=hide-qual
   732  SELECT * FROM c WHERE (v, u) IN ((1, 2), (3, 2), (5, 2))
   733  ----
   734  scan c@v
   735   ├── columns: k:1(int!null) u:2(int!null) v:3(int!null)
   736   ├── constraint: /3/2/1
   737   │    ├── [/1/2 - /1/2]
   738   │    ├── [/3/2 - /3/2]
   739   │    └── [/5/2 - /5/2]
   740   ├── key: (1)
   741   ├── fd: ()-->(2), (1)-->(3)
   742   ├── prune: (1)
   743   └── interesting orderings: (+1) (+3,+2,+1)
   744  
   745  opt format=hide-qual
   746  SELECT * FROM c WHERE (v, u) IN ((1, 2), (1, 2), (1, 2))
   747  ----
   748  scan c@v
   749   ├── columns: k:1(int!null) u:2(int!null) v:3(int!null)
   750   ├── constraint: /3/2/1: [/1/2 - /1/2]
   751   ├── key: (1)
   752   ├── fd: ()-->(2,3)
   753   ├── prune: (1)
   754   └── interesting orderings: (+1) (+3,+2,+1)
   755  
   756  # A tuple with NULL in it can't match anything, so it should be excluded from the constraints.
   757  opt
   758  SELECT * FROM c WHERE (v, u) IN ((1, 2), (3, 50), (5, NULL))
   759  ----
   760  scan c@v
   761   ├── columns: k:1(int!null) u:2(int!null) v:3(int!null)
   762   ├── constraint: /3/2/1
   763   │    ├── [/1/2 - /1/2]
   764   │    └── [/3/50 - /3/50]
   765   ├── key: (1)
   766   ├── fd: (1)-->(2,3)
   767   ├── prune: (1)
   768   └── interesting orderings: (+1) (+3,+2,+1)
   769  
   770  # TODO(justin): ideally we would be normalizing away the 2 on the LHS here to
   771  # get v = 1 and tight spans.
   772  opt
   773  SELECT * FROM c WHERE (v, 2) IN ((1, 2), (3, 50), (5, 100))
   774  ----
   775  select
   776   ├── columns: k:1(int!null) u:2(int) v:3(int!null)
   777   ├── key: (1)
   778   ├── fd: (1)-->(2,3)
   779   ├── prune: (1,2)
   780   ├── interesting orderings: (+1) (+3,+2,+1)
   781   ├── scan c@v
   782   │    ├── columns: k:1(int!null) u:2(int) v:3(int!null)
   783   │    ├── constraint: /3/2/1
   784   │    │    ├── [/1 - /1]
   785   │    │    ├── [/3 - /3]
   786   │    │    └── [/5 - /5]
   787   │    ├── key: (1)
   788   │    ├── fd: (1)-->(2,3)
   789   │    ├── prune: (1-3)
   790   │    └── interesting orderings: (+1) (+3,+2,+1)
   791   └── filters
   792        └── in [type=bool, outer=(3), constraints=(/3: [/1 - /1] [/3 - /3] [/5 - /5])]
   793             ├── tuple [type=tuple{int, int}]
   794             │    ├── variable: v:3 [type=int]
   795             │    └── const: 2 [type=int]
   796             └── tuple [type=tuple{tuple{int, int}, tuple{int, int}, tuple{int, int}}]
   797                  ├── tuple [type=tuple{int, int}]
   798                  │    ├── const: 1 [type=int]
   799                  │    └── const: 2 [type=int]
   800                  ├── tuple [type=tuple{int, int}]
   801                  │    ├── const: 3 [type=int]
   802                  │    └── const: 50 [type=int]
   803                  └── tuple [type=tuple{int, int}]
   804                       ├── const: 5 [type=int]
   805                       └── const: 100 [type=int]
   806  
   807  # TODO(justin): in a perfect world we would be able to somehow transform this
   808  # filter to (v, u) IN ((1, 1), (3, 47), (5, 95)) in order to get tight spans.
   809  # This could be achieved via row-reduction.
   810  opt
   811  SELECT * FROM c WHERE (v, u + v) IN ((1, 2), (3, 50), (5, 100))
   812  ----
   813  select
   814   ├── columns: k:1(int!null) u:2(int) v:3(int!null)
   815   ├── key: (1)
   816   ├── fd: (1)-->(2,3)
   817   ├── prune: (1)
   818   ├── interesting orderings: (+1) (+3,+2,+1)
   819   ├── scan c@v
   820   │    ├── columns: k:1(int!null) u:2(int) v:3(int!null)
   821   │    ├── constraint: /3/2/1
   822   │    │    ├── [/1 - /1]
   823   │    │    ├── [/3 - /3]
   824   │    │    └── [/5 - /5]
   825   │    ├── key: (1)
   826   │    ├── fd: (1)-->(2,3)
   827   │    ├── prune: (1-3)
   828   │    └── interesting orderings: (+1) (+3,+2,+1)
   829   └── filters
   830        └── in [type=bool, outer=(2,3), constraints=(/3: [/1 - /1] [/3 - /3] [/5 - /5])]
   831             ├── tuple [type=tuple{int, int}]
   832             │    ├── variable: v:3 [type=int]
   833             │    └── plus [type=int]
   834             │         ├── variable: u:2 [type=int]
   835             │         └── variable: v:3 [type=int]
   836             └── tuple [type=tuple{tuple{int, int}, tuple{int, int}, tuple{int, int}}]
   837                  ├── tuple [type=tuple{int, int}]
   838                  │    ├── const: 1 [type=int]
   839                  │    └── const: 2 [type=int]
   840                  ├── tuple [type=tuple{int, int}]
   841                  │    ├── const: 3 [type=int]
   842                  │    └── const: 50 [type=int]
   843                  └── tuple [type=tuple{int, int}]
   844                       ├── const: 5 [type=int]
   845                       └── const: 100 [type=int]
   846  
   847  opt
   848  SELECT * FROM c WHERE (v, u) IN ((1, 2), (k, 50), (5, 100))
   849  ----
   850  select
   851   ├── columns: k:1(int!null) u:2(int!null) v:3(int)
   852   ├── key: (1)
   853   ├── fd: (1)-->(2,3)
   854   ├── interesting orderings: (+1) (+3,+2,+1)
   855   ├── scan c
   856   │    ├── columns: k:1(int!null) u:2(int) v:3(int)
   857   │    ├── key: (1)
   858   │    ├── fd: (1)-->(2,3)
   859   │    ├── prune: (1-3)
   860   │    └── interesting orderings: (+1) (+3,+2,+1)
   861   └── filters
   862        └── in [type=bool, outer=(1-3), constraints=(/2: [/2 - /2] [/50 - /50] [/100 - /100])]
   863             ├── tuple [type=tuple{int, int}]
   864             │    ├── variable: v:3 [type=int]
   865             │    └── variable: u:2 [type=int]
   866             └── tuple [type=tuple{tuple{int, int}, tuple{int, int}, tuple{int, int}}]
   867                  ├── tuple [type=tuple{int, int}]
   868                  │    ├── const: 1 [type=int]
   869                  │    └── const: 2 [type=int]
   870                  ├── tuple [type=tuple{int, int}]
   871                  │    ├── variable: k:1 [type=int]
   872                  │    └── const: 50 [type=int]
   873                  └── tuple [type=tuple{int, int}]
   874                       ├── const: 5 [type=int]
   875                       └── const: 100 [type=int]
   876  
   877  exec-ddl
   878  CREATE TABLE d
   879  (
   880      k INT PRIMARY KEY,
   881      p INT,
   882      q INT
   883  )
   884  ----
   885  
   886  opt format=hide-qual
   887  SELECT * FROM d WHERE (p, q) IN ((1, 2), (1, 3), (1, 4))
   888  ----
   889  select
   890   ├── columns: k:1(int!null) p:2(int!null) q:3(int!null)
   891   ├── key: (1)
   892   ├── fd: ()-->(2), (1)-->(3)
   893   ├── prune: (1)
   894   ├── interesting orderings: (+1)
   895   ├── scan d
   896   │    ├── columns: k:1(int!null) p:2(int) q:3(int)
   897   │    ├── key: (1)
   898   │    ├── fd: (1)-->(2,3)
   899   │    ├── prune: (1-3)
   900   │    └── interesting orderings: (+1)
   901   └── filters
   902        └── in [type=bool, outer=(2,3), constraints=(/2/3: [/1/2 - /1/2] [/1/3 - /1/3] [/1/4 - /1/4]; /3: [/2 - /2] [/3 - /3] [/4 - /4]; tight), fd=()-->(2)]
   903             ├── tuple [type=tuple{int, int}]
   904             │    ├── variable: p:2 [type=int]
   905             │    └── variable: q:3 [type=int]
   906             └── tuple [type=tuple{tuple{int, int}, tuple{int, int}, tuple{int, int}}]
   907                  ├── tuple [type=tuple{int, int}]
   908                  │    ├── const: 1 [type=int]
   909                  │    └── const: 2 [type=int]
   910                  ├── tuple [type=tuple{int, int}]
   911                  │    ├── const: 1 [type=int]
   912                  │    └── const: 3 [type=int]
   913                  └── tuple [type=tuple{int, int}]
   914                       ├── const: 1 [type=int]
   915                       └── const: 4 [type=int]
   916  
   917  opt format=hide-qual
   918  SELECT * FROM d WHERE (p, q) IN ((2, 1), (3, 1), (4, 1))
   919  ----
   920  select
   921   ├── columns: k:1(int!null) p:2(int!null) q:3(int!null)
   922   ├── key: (1)
   923   ├── fd: ()-->(3), (1)-->(2)
   924   ├── prune: (1)
   925   ├── interesting orderings: (+1)
   926   ├── scan d
   927   │    ├── columns: k:1(int!null) p:2(int) q:3(int)
   928   │    ├── key: (1)
   929   │    ├── fd: (1)-->(2,3)
   930   │    ├── prune: (1-3)
   931   │    └── interesting orderings: (+1)
   932   └── filters
   933        └── in [type=bool, outer=(2,3), constraints=(/2/3: [/2/1 - /2/1] [/3/1 - /3/1] [/4/1 - /4/1]; /3: [/1 - /1]; tight), fd=()-->(3)]
   934             ├── tuple [type=tuple{int, int}]
   935             │    ├── variable: p:2 [type=int]
   936             │    └── variable: q:3 [type=int]
   937             └── tuple [type=tuple{tuple{int, int}, tuple{int, int}, tuple{int, int}}]
   938                  ├── tuple [type=tuple{int, int}]
   939                  │    ├── const: 2 [type=int]
   940                  │    └── const: 1 [type=int]
   941                  ├── tuple [type=tuple{int, int}]
   942                  │    ├── const: 3 [type=int]
   943                  │    └── const: 1 [type=int]
   944                  └── tuple [type=tuple{int, int}]
   945                       ├── const: 4 [type=int]
   946                       └── const: 1 [type=int]
   947  
   948  exec-ddl
   949  CREATE TABLE e
   950  (
   951      k INT PRIMARY KEY,
   952      t TIMESTAMP,
   953      d TIMESTAMP,
   954      INDEX (t),
   955      INDEX (d)
   956  )
   957  ----
   958  
   959  opt
   960  SELECT k FROM e WHERE d > '2018-07-01' AND d < '2018-07-01'::DATE + '1w'::INTERVAL
   961  ----
   962  project
   963   ├── columns: k:1(int!null)
   964   ├── key: (1)
   965   ├── prune: (1)
   966   ├── interesting orderings: (+1)
   967   └── scan e@secondary
   968        ├── columns: k:1(int!null) d:3(timestamp!null)
   969        ├── constraint: /3/1: [/'2018-07-01 00:00:00.000001+00:00' - /'2018-07-07 23:59:59.999999+00:00']
   970        ├── key: (1)
   971        ├── fd: (1)-->(3)
   972        ├── prune: (1,3)
   973        └── interesting orderings: (+1) (+3,+1)
   974  
   975  # Verify constraints for tuple IN (tuple, ..), when the tuples are not sorted.
   976  opt
   977  SELECT * FROM (SELECT (x, y) AS foo FROM a) WHERE foo IN ((3, 4), (1, 2))
   978  ----
   979  select
   980   ├── columns: foo:4(tuple{int, int}!null)
   981   ├── project
   982   │    ├── columns: foo:4(tuple{int, int})
   983   │    ├── prune: (4)
   984   │    ├── scan a
   985   │    │    ├── columns: x:1(int) y:2(int)
   986   │    │    └── prune: (1,2)
   987   │    └── projections
   988   │         └── tuple [as=foo:4, type=tuple{int, int}, outer=(1,2)]
   989   │              ├── variable: x:1 [type=int]
   990   │              └── variable: y:2 [type=int]
   991   └── filters
   992        └── in [type=bool, outer=(4), constraints=(/4: [/(1, 2) - /(1, 2)] [/(3, 4) - /(3, 4)]; tight)]
   993             ├── variable: foo:4 [type=tuple{int, int}]
   994             └── tuple [type=tuple{tuple{int, int}, tuple{int, int}}]
   995                  ├── tuple [type=tuple{int, int}]
   996                  │    ├── const: 3 [type=int]
   997                  │    └── const: 4 [type=int]
   998                  └── tuple [type=tuple{int, int}]
   999                       ├── const: 1 [type=int]
  1000                       └── const: 2 [type=int]
  1001  
  1002  # Tests for string operators (LIKE, SIMILAR TO).
  1003  opt
  1004  SELECT * FROM kuv WHERE v LIKE 'ABC%'
  1005  ----
  1006  select
  1007   ├── columns: k:1(int!null) u:2(float) v:3(string!null)
  1008   ├── key: (1)
  1009   ├── fd: (1)-->(2,3)
  1010   ├── prune: (1,2)
  1011   ├── interesting orderings: (+1)
  1012   ├── scan kuv
  1013   │    ├── columns: k:1(int!null) u:2(float) v:3(string)
  1014   │    ├── key: (1)
  1015   │    ├── fd: (1)-->(2,3)
  1016   │    ├── prune: (1-3)
  1017   │    └── interesting orderings: (+1)
  1018   └── filters
  1019        └── like [type=bool, outer=(3), constraints=(/3: [/'ABC' - /'ABD'); tight)]
  1020             ├── variable: v:3 [type=string]
  1021             └── const: 'ABC%' [type=string]
  1022  
  1023  opt
  1024  SELECT * FROM kuv WHERE v LIKE 'ABC_'
  1025  ----
  1026  select
  1027   ├── columns: k:1(int!null) u:2(float) v:3(string!null)
  1028   ├── key: (1)
  1029   ├── fd: (1)-->(2,3)
  1030   ├── prune: (1,2)
  1031   ├── interesting orderings: (+1)
  1032   ├── scan kuv
  1033   │    ├── columns: k:1(int!null) u:2(float) v:3(string)
  1034   │    ├── key: (1)
  1035   │    ├── fd: (1)-->(2,3)
  1036   │    ├── prune: (1-3)
  1037   │    └── interesting orderings: (+1)
  1038   └── filters
  1039        └── like [type=bool, outer=(3), constraints=(/3: [/'ABC' - /'ABD'))]
  1040             ├── variable: v:3 [type=string]
  1041             └── const: 'ABC_' [type=string]
  1042  
  1043  opt
  1044  SELECT * FROM kuv WHERE v LIKE 'ABC%Z'
  1045  ----
  1046  select
  1047   ├── columns: k:1(int!null) u:2(float) v:3(string!null)
  1048   ├── key: (1)
  1049   ├── fd: (1)-->(2,3)
  1050   ├── prune: (1,2)
  1051   ├── interesting orderings: (+1)
  1052   ├── scan kuv
  1053   │    ├── columns: k:1(int!null) u:2(float) v:3(string)
  1054   │    ├── key: (1)
  1055   │    ├── fd: (1)-->(2,3)
  1056   │    ├── prune: (1-3)
  1057   │    └── interesting orderings: (+1)
  1058   └── filters
  1059        └── like [type=bool, outer=(3), constraints=(/3: [/'ABC' - /'ABD'))]
  1060             ├── variable: v:3 [type=string]
  1061             └── const: 'ABC%Z' [type=string]
  1062  
  1063  opt
  1064  SELECT * FROM kuv WHERE v LIKE 'ABC'
  1065  ----
  1066  select
  1067   ├── columns: k:1(int!null) u:2(float) v:3(string!null)
  1068   ├── key: (1)
  1069   ├── fd: ()-->(3), (1)-->(2)
  1070   ├── prune: (1,2)
  1071   ├── interesting orderings: (+1)
  1072   ├── scan kuv
  1073   │    ├── columns: k:1(int!null) u:2(float) v:3(string)
  1074   │    ├── key: (1)
  1075   │    ├── fd: (1)-->(2,3)
  1076   │    ├── prune: (1-3)
  1077   │    └── interesting orderings: (+1)
  1078   └── filters
  1079        └── like [type=bool, outer=(3), constraints=(/3: [/'ABC' - /'ABC']; tight), fd=()-->(3)]
  1080             ├── variable: v:3 [type=string]
  1081             └── const: 'ABC' [type=string]
  1082  
  1083  opt
  1084  SELECT * FROM kuv WHERE v LIKE '%'
  1085  ----
  1086  select
  1087   ├── columns: k:1(int!null) u:2(float) v:3(string!null)
  1088   ├── key: (1)
  1089   ├── fd: (1)-->(2,3)
  1090   ├── prune: (1,2)
  1091   ├── interesting orderings: (+1)
  1092   ├── scan kuv
  1093   │    ├── columns: k:1(int!null) u:2(float) v:3(string)
  1094   │    ├── key: (1)
  1095   │    ├── fd: (1)-->(2,3)
  1096   │    ├── prune: (1-3)
  1097   │    └── interesting orderings: (+1)
  1098   └── filters
  1099        └── like [type=bool, outer=(3), constraints=(/3: (/NULL - ])]
  1100             ├── variable: v:3 [type=string]
  1101             └── const: '%' [type=string]
  1102  
  1103  opt
  1104  SELECT * FROM kuv WHERE v LIKE '%XY'
  1105  ----
  1106  select
  1107   ├── columns: k:1(int!null) u:2(float) v:3(string!null)
  1108   ├── key: (1)
  1109   ├── fd: (1)-->(2,3)
  1110   ├── prune: (1,2)
  1111   ├── interesting orderings: (+1)
  1112   ├── scan kuv
  1113   │    ├── columns: k:1(int!null) u:2(float) v:3(string)
  1114   │    ├── key: (1)
  1115   │    ├── fd: (1)-->(2,3)
  1116   │    ├── prune: (1-3)
  1117   │    └── interesting orderings: (+1)
  1118   └── filters
  1119        └── like [type=bool, outer=(3), constraints=(/3: (/NULL - ])]
  1120             ├── variable: v:3 [type=string]
  1121             └── const: '%XY' [type=string]
  1122  
  1123  opt
  1124  SELECT * FROM kuv WHERE v SIMILAR TO 'ABC.*'
  1125  ----
  1126  select
  1127   ├── columns: k:1(int!null) u:2(float) v:3(string!null)
  1128   ├── key: (1)
  1129   ├── fd: (1)-->(2,3)
  1130   ├── prune: (1,2)
  1131   ├── interesting orderings: (+1)
  1132   ├── scan kuv
  1133   │    ├── columns: k:1(int!null) u:2(float) v:3(string)
  1134   │    ├── key: (1)
  1135   │    ├── fd: (1)-->(2,3)
  1136   │    ├── prune: (1-3)
  1137   │    └── interesting orderings: (+1)
  1138   └── filters
  1139        └── similar-to [type=bool, outer=(3), constraints=(/3: [/'ABC' - /'ABD'))]
  1140             ├── variable: v:3 [type=string]
  1141             └── const: 'ABC.*' [type=string]
  1142  
  1143  opt
  1144  SELECT * FROM kuv WHERE v SIMILAR TO 'ABC.*Z'
  1145  ----
  1146  select
  1147   ├── columns: k:1(int!null) u:2(float) v:3(string!null)
  1148   ├── key: (1)
  1149   ├── fd: (1)-->(2,3)
  1150   ├── prune: (1,2)
  1151   ├── interesting orderings: (+1)
  1152   ├── scan kuv
  1153   │    ├── columns: k:1(int!null) u:2(float) v:3(string)
  1154   │    ├── key: (1)
  1155   │    ├── fd: (1)-->(2,3)
  1156   │    ├── prune: (1-3)
  1157   │    └── interesting orderings: (+1)
  1158   └── filters
  1159        └── similar-to [type=bool, outer=(3), constraints=(/3: [/'ABC' - /'ABD'))]
  1160             ├── variable: v:3 [type=string]
  1161             └── const: 'ABC.*Z' [type=string]
  1162  
  1163  opt
  1164  SELECT * FROM kuv WHERE v SIMILAR TO 'ABC'
  1165  ----
  1166  select
  1167   ├── columns: k:1(int!null) u:2(float) v:3(string!null)
  1168   ├── key: (1)
  1169   ├── fd: ()-->(3), (1)-->(2)
  1170   ├── prune: (1,2)
  1171   ├── interesting orderings: (+1)
  1172   ├── scan kuv
  1173   │    ├── columns: k:1(int!null) u:2(float) v:3(string)
  1174   │    ├── key: (1)
  1175   │    ├── fd: (1)-->(2,3)
  1176   │    ├── prune: (1-3)
  1177   │    └── interesting orderings: (+1)
  1178   └── filters
  1179        └── similar-to [type=bool, outer=(3), constraints=(/3: [/'ABC' - /'ABC']; tight), fd=()-->(3)]
  1180             ├── variable: v:3 [type=string]
  1181             └── const: 'ABC' [type=string]
  1182  
  1183  opt
  1184  SELECT * FROM kuv WHERE v SIMILAR TO '(ABC|ABCDEF).*'
  1185  ----
  1186  select
  1187   ├── columns: k:1(int!null) u:2(float) v:3(string!null)
  1188   ├── key: (1)
  1189   ├── fd: (1)-->(2,3)
  1190   ├── prune: (1,2)
  1191   ├── interesting orderings: (+1)
  1192   ├── scan kuv
  1193   │    ├── columns: k:1(int!null) u:2(float) v:3(string)
  1194   │    ├── key: (1)
  1195   │    ├── fd: (1)-->(2,3)
  1196   │    ├── prune: (1-3)
  1197   │    └── interesting orderings: (+1)
  1198   └── filters
  1199        └── similar-to [type=bool, outer=(3), constraints=(/3: [/'ABC' - /'ABD'))]
  1200             ├── variable: v:3 [type=string]
  1201             └── const: '(ABC|ABCDEF).*' [type=string]
  1202  
  1203  opt
  1204  SELECT * FROM kuv WHERE v SIMILAR TO '.*'
  1205  ----
  1206  select
  1207   ├── columns: k:1(int!null) u:2(float) v:3(string!null)
  1208   ├── key: (1)
  1209   ├── fd: (1)-->(2,3)
  1210   ├── prune: (1,2)
  1211   ├── interesting orderings: (+1)
  1212   ├── scan kuv
  1213   │    ├── columns: k:1(int!null) u:2(float) v:3(string)
  1214   │    ├── key: (1)
  1215   │    ├── fd: (1)-->(2,3)
  1216   │    ├── prune: (1-3)
  1217   │    └── interesting orderings: (+1)
  1218   └── filters
  1219        └── similar-to [type=bool, outer=(3), constraints=(/3: [/'' - ])]
  1220             ├── variable: v:3 [type=string]
  1221             └── const: '.*' [type=string]
  1222  
  1223  # We can determine that the constraint set is tight when there is a single
  1224  # variable and tight constraints are combined with OR.
  1225  opt
  1226  SELECT * FROM a WHERE x <= 5 OR x = 10 OR x = 15
  1227  ----
  1228  select
  1229   ├── columns: x:1(int!null) y:2(int)
  1230   ├── prune: (2)
  1231   ├── scan a
  1232   │    ├── columns: x:1(int) y:2(int)
  1233   │    └── prune: (1,2)
  1234   └── filters
  1235        └── or [type=bool, outer=(1), constraints=(/1: (/NULL - /5] [/10 - /10] [/15 - /15]; tight)]
  1236             ├── or [type=bool]
  1237             │    ├── le [type=bool]
  1238             │    │    ├── variable: x:1 [type=int]
  1239             │    │    └── const: 5 [type=int]
  1240             │    └── eq [type=bool]
  1241             │         ├── variable: x:1 [type=int]
  1242             │         └── const: 10 [type=int]
  1243             └── eq [type=bool]
  1244                  ├── variable: x:1 [type=int]
  1245                  └── const: 15 [type=int]
  1246  
  1247  # The constraint set is also tight when each side has a single constraint with
  1248  # matching columns.
  1249  opt
  1250  SELECT * FROM a WHERE (x, y) < (1, 2) OR (x, y) > (3, 4)
  1251  ----
  1252  select
  1253   ├── columns: x:1(int!null) y:2(int)
  1254   ├── scan a
  1255   │    ├── columns: x:1(int) y:2(int)
  1256   │    └── prune: (1,2)
  1257   └── filters
  1258        └── or [type=bool, outer=(1,2), constraints=(/1/2: (/NULL - /1/1] [/3/5 - ]; tight)]
  1259             ├── lt [type=bool]
  1260             │    ├── tuple [type=tuple{int, int}]
  1261             │    │    ├── variable: x:1 [type=int]
  1262             │    │    └── variable: y:2 [type=int]
  1263             │    └── tuple [type=tuple{int, int}]
  1264             │         ├── const: 1 [type=int]
  1265             │         └── const: 2 [type=int]
  1266             └── gt [type=bool]
  1267                  ├── tuple [type=tuple{int, int}]
  1268                  │    ├── variable: x:1 [type=int]
  1269                  │    └── variable: y:2 [type=int]
  1270                  └── tuple [type=tuple{int, int}]
  1271                       ├── const: 3 [type=int]
  1272                       └── const: 4 [type=int]
  1273  
  1274  
  1275  # The constraint set is not tight if there are multiple constraints with
  1276  # different variables.
  1277  opt
  1278  SELECT * FROM a WHERE (x > 1 AND y > 10) OR (x < 5 AND y < 50)
  1279  ----
  1280  select
  1281   ├── columns: x:1(int!null) y:2(int!null)
  1282   ├── scan a
  1283   │    ├── columns: x:1(int) y:2(int)
  1284   │    └── prune: (1,2)
  1285   └── filters
  1286        └── or [type=bool, outer=(1,2), constraints=(/1: (/NULL - ]; /2: (/NULL - ])]
  1287             ├── and [type=bool]
  1288             │    ├── gt [type=bool]
  1289             │    │    ├── variable: x:1 [type=int]
  1290             │    │    └── const: 1 [type=int]
  1291             │    └── gt [type=bool]
  1292             │         ├── variable: y:2 [type=int]
  1293             │         └── const: 10 [type=int]
  1294             └── and [type=bool]
  1295                  ├── lt [type=bool]
  1296                  │    ├── variable: x:1 [type=int]
  1297                  │    └── const: 5 [type=int]
  1298                  └── lt [type=bool]
  1299                       ├── variable: y:2 [type=int]
  1300                       └── const: 50 [type=int]
  1301  
  1302  # A union constraint set is tight if the left is a contradiction and the right
  1303  # is tight.
  1304  opt
  1305  SELECT * FROM a WHERE (x = 1 AND x = 3) OR (x = 10 AND y = 20)
  1306  ----
  1307  select
  1308   ├── columns: x:1(int!null) y:2(int!null)
  1309   ├── fd: ()-->(1,2)
  1310   ├── scan a
  1311   │    ├── columns: x:1(int) y:2(int)
  1312   │    └── prune: (1,2)
  1313   └── filters
  1314        └── or [type=bool, outer=(1,2), constraints=(/1: [/10 - /10]; /2: [/20 - /20]; tight), fd=()-->(1,2)]
  1315             ├── and [type=bool]
  1316             │    ├── eq [type=bool]
  1317             │    │    ├── variable: x:1 [type=int]
  1318             │    │    └── const: 1 [type=int]
  1319             │    └── eq [type=bool]
  1320             │         ├── variable: x:1 [type=int]
  1321             │         └── const: 3 [type=int]
  1322             └── and [type=bool]
  1323                  ├── eq [type=bool]
  1324                  │    ├── variable: x:1 [type=int]
  1325                  │    └── const: 10 [type=int]
  1326                  └── eq [type=bool]
  1327                       ├── variable: y:2 [type=int]
  1328                       └── const: 20 [type=int]
  1329  
  1330  # A union constraint set is tight if the right is a contradiction and the left
  1331  # is tight.
  1332  opt
  1333  SELECT * FROM a WHERE (x = 10 AND y = 20) OR (x = 1 AND x = 3) 
  1334  ----
  1335  select
  1336   ├── columns: x:1(int!null) y:2(int!null)
  1337   ├── fd: ()-->(1,2)
  1338   ├── scan a
  1339   │    ├── columns: x:1(int) y:2(int)
  1340   │    └── prune: (1,2)
  1341   └── filters
  1342        └── or [type=bool, outer=(1,2), constraints=(/1: [/10 - /10]; /2: [/20 - /20]; tight), fd=()-->(1,2)]
  1343             ├── and [type=bool]
  1344             │    ├── eq [type=bool]
  1345             │    │    ├── variable: x:1 [type=int]
  1346             │    │    └── const: 10 [type=int]
  1347             │    └── eq [type=bool]
  1348             │         ├── variable: y:2 [type=int]
  1349             │         └── const: 20 [type=int]
  1350             └── and [type=bool]
  1351                  ├── eq [type=bool]
  1352                  │    ├── variable: x:1 [type=int]
  1353                  │    └── const: 1 [type=int]
  1354                  └── eq [type=bool]
  1355                       ├── variable: x:1 [type=int]
  1356                       └── const: 3 [type=int]