github.com/dolthub/dolt/go@v0.40.5-0.20240520175717-68db7794bea6/libraries/doltcore/sqle/index/mergeable_indexes_test.go (about)

     1  // Copyright 2020 Dolthub, Inc.
     2  //
     3  // Licensed under the Apache License, Version 2.0 (the "License");
     4  // you may not use this file except in compliance with the License.
     5  // You may obtain a copy of the License at
     6  //
     7  //     http://www.apache.org/licenses/LICENSE-2.0
     8  //
     9  // Unless required by applicable law or agreed to in writing, software
    10  // distributed under the License is distributed on an "AS IS" BASIS,
    11  // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    12  // See the License for the specific language governing permissions and
    13  // limitations under the License.
    14  
    15  package index_test
    16  
    17  import (
    18  	"fmt"
    19  	"testing"
    20  
    21  	sqle "github.com/dolthub/go-mysql-server"
    22  	"github.com/dolthub/go-mysql-server/sql"
    23  	"github.com/dolthub/go-mysql-server/sql/plan"
    24  	"github.com/dolthub/go-mysql-server/sql/planbuilder"
    25  	"github.com/dolthub/go-mysql-server/sql/transform"
    26  	"github.com/stretchr/testify/assert"
    27  	"github.com/stretchr/testify/require"
    28  
    29  	"github.com/dolthub/dolt/go/libraries/doltcore/sqle/index"
    30  	"github.com/dolthub/dolt/go/libraries/doltcore/table/typed/noms"
    31  	"github.com/dolthub/dolt/go/store/types"
    32  )
    33  
    34  // This tests mergeable indexes by using the SQL engine and intercepting specific calls. This way, we can verify that
    35  // the engine is intersecting and combining the proper number of lookups, and we can also examine the ranges before
    36  // they're converted into a format that Noms understands to verify that they were handled correctly. Lastly, we ensure
    37  // that the final output is as expected.
    38  func TestMergeableIndexes(t *testing.T) {
    39  	if types.Format_Default != types.Format_LD_1 {
    40  		t.Skip() // this test is specific to Noms ranges
    41  	}
    42  
    43  	engine, sqlCtx, indexTuples := setupIndexes(t, "test", `INSERT INTO test VALUES
    44  		(-3, NULL, NULL),
    45  		(-2, NULL, NULL),
    46  		(-1, NULL, NULL),
    47  		(0, 10, 20),
    48  		(1, 11, 21),
    49  		(2, 12, 22),
    50  		(3, 13, 23),
    51  		(4, 14, 24),
    52  		(5, 15, 25),
    53  		(6, 16, 26),
    54  		(7, 17, 27),
    55  		(8, 18, 28),
    56  		(9, 19, 29);`)
    57  	idxv1, idxv2v1, idxv2v1Gen := indexTuples[0], indexTuples[1], indexTuples[2]
    58  
    59  	tests := []struct {
    60  		whereStmt   string
    61  		finalRanges []*noms.ReadRange
    62  		pks         []int64
    63  	}{
    64  		{
    65  			"v1 = 11",
    66  			[]*noms.ReadRange{
    67  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
    68  			},
    69  			[]int64{1},
    70  		},
    71  		{
    72  			"v1 = 11 OR v1 = 15",
    73  			[]*noms.ReadRange{
    74  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
    75  				index.ClosedRange(idxv1.tuple(15), idxv1.tuple(15)),
    76  			},
    77  			[]int64{1, 5},
    78  		},
    79  		{
    80  			"v1 = 11 AND v1 = 15",
    81  			nil,
    82  			[]int64{},
    83  		},
    84  		{
    85  			"v1 = 11 OR v1 = 15 OR v1 = 19",
    86  			[]*noms.ReadRange{
    87  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
    88  				index.ClosedRange(idxv1.tuple(15), idxv1.tuple(15)),
    89  				index.ClosedRange(idxv1.tuple(19), idxv1.tuple(19)),
    90  			},
    91  			[]int64{1, 5, 9},
    92  		},
    93  		{
    94  			"v1 = 11 OR v1 = 15 AND v1 = 19",
    95  			[]*noms.ReadRange{
    96  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
    97  			},
    98  			[]int64{1},
    99  		},
   100  		{
   101  			"v1 = 11 AND v1 = 15 AND v1 = 19",
   102  			nil,
   103  			[]int64{},
   104  		},
   105  		{
   106  			"v1 = 11 OR v1 != 11",
   107  			[]*noms.ReadRange{
   108  				index.NotNullRange(),
   109  			},
   110  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   111  		},
   112  		{
   113  			"v1 = 11 OR v1 != 15",
   114  			[]*noms.ReadRange{
   115  				index.GreaterThanRange(idxv1.tuple(15)),
   116  				index.LessThanRange(idxv1.tuple(15)),
   117  			},
   118  			[]int64{0, 1, 2, 3, 4, 6, 7, 8, 9},
   119  		},
   120  		{
   121  			"v1 = 11 AND v1 != 15",
   122  			[]*noms.ReadRange{
   123  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
   124  			},
   125  			[]int64{1},
   126  		},
   127  		{
   128  			"v1 = 11 OR v1 = 15 OR v1 != 19",
   129  			[]*noms.ReadRange{
   130  				index.LessThanRange(idxv1.tuple(19)),
   131  				index.GreaterThanRange(idxv1.tuple(19)),
   132  			},
   133  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8},
   134  		},
   135  		{
   136  			"v1 = 11 OR v1 = 15 AND v1 != 19",
   137  			[]*noms.ReadRange{
   138  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
   139  				index.ClosedRange(idxv1.tuple(15), idxv1.tuple(15)),
   140  			},
   141  			[]int64{1, 5},
   142  		},
   143  		{
   144  			"v1 = 11 AND v1 = 15 OR v1 != 19",
   145  			[]*noms.ReadRange{
   146  				index.LessThanRange(idxv1.tuple(19)),
   147  				index.GreaterThanRange(idxv1.tuple(19)),
   148  			},
   149  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8},
   150  		},
   151  		{
   152  			"v1 = 11 AND v1 = 15 AND v1 != 19",
   153  			nil,
   154  			[]int64{},
   155  		},
   156  		{
   157  			"v1 = 11 OR v1 > 15",
   158  			[]*noms.ReadRange{
   159  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
   160  				index.GreaterThanRange(idxv1.tuple(15)),
   161  			},
   162  			[]int64{1, 6, 7, 8, 9},
   163  		},
   164  		{
   165  			"v1 = 11 AND v1 > 15",
   166  			nil,
   167  			[]int64{},
   168  		},
   169  		{
   170  			"v1 = 11 OR v1 = 15 OR v1 > 19",
   171  			[]*noms.ReadRange{
   172  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
   173  				index.ClosedRange(idxv1.tuple(15), idxv1.tuple(15)),
   174  				index.GreaterThanRange(idxv1.tuple(19)),
   175  			},
   176  			[]int64{1, 5},
   177  		},
   178  		{
   179  			"v1 = 11 OR v1 = 15 AND v1 > 19",
   180  			[]*noms.ReadRange{
   181  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
   182  			},
   183  			[]int64{1},
   184  		},
   185  		{
   186  			"v1 = 11 AND v1 = 15 OR v1 > 19",
   187  			[]*noms.ReadRange{
   188  				index.GreaterThanRange(idxv1.tuple(19)),
   189  			},
   190  			[]int64{},
   191  		},
   192  		{
   193  			"v1 = 11 AND v1 = 15 AND v1 > 19",
   194  			nil,
   195  			[]int64{},
   196  		},
   197  		{
   198  			"v1 = 11 OR v1 >= 15",
   199  			[]*noms.ReadRange{
   200  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
   201  				index.GreaterOrEqualRange(idxv1.tuple(15)),
   202  			},
   203  			[]int64{1, 5, 6, 7, 8, 9},
   204  		},
   205  		{
   206  			"v1 = 11 AND v1 >= 15",
   207  			nil,
   208  			[]int64{},
   209  		},
   210  		{
   211  			"v1 = 11 OR v1 = 15 OR v1 >= 19",
   212  			[]*noms.ReadRange{
   213  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
   214  				index.ClosedRange(idxv1.tuple(15), idxv1.tuple(15)),
   215  				index.GreaterOrEqualRange(idxv1.tuple(19)),
   216  			},
   217  			[]int64{1, 5, 9},
   218  		},
   219  		{
   220  			"v1 = 11 OR v1 = 15 AND v1 >= 19",
   221  			[]*noms.ReadRange{
   222  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
   223  			},
   224  			[]int64{1},
   225  		},
   226  		{
   227  			"v1 = 11 AND v1 = 15 OR v1 >= 19",
   228  			[]*noms.ReadRange{
   229  				index.GreaterOrEqualRange(idxv1.tuple(19)),
   230  			},
   231  			[]int64{9},
   232  		},
   233  		{
   234  			"v1 = 11 AND v1 = 15 AND v1 >= 19",
   235  			nil,
   236  			[]int64{},
   237  		},
   238  		{
   239  			"v1 = 11 OR v1 < 15",
   240  			[]*noms.ReadRange{
   241  				index.LessThanRange(idxv1.tuple(15)),
   242  			},
   243  			[]int64{0, 1, 2, 3, 4},
   244  		},
   245  		{
   246  			"v1 = 11 AND v1 < 15",
   247  			[]*noms.ReadRange{
   248  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
   249  			},
   250  			[]int64{1},
   251  		},
   252  		{
   253  			"v1 = 11 OR v1 = 15 OR v1 < 19",
   254  			[]*noms.ReadRange{
   255  				index.LessThanRange(idxv1.tuple(19)),
   256  			},
   257  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8},
   258  		},
   259  		{
   260  			"v1 = 11 OR v1 = 15 AND v1 < 19",
   261  			[]*noms.ReadRange{
   262  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
   263  				index.ClosedRange(idxv1.tuple(15), idxv1.tuple(15)),
   264  			},
   265  			[]int64{1, 5},
   266  		},
   267  		{
   268  			"v1 = 11 AND v1 = 15 OR v1 < 19",
   269  			[]*noms.ReadRange{
   270  				index.LessThanRange(idxv1.tuple(19)),
   271  			},
   272  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8},
   273  		},
   274  		{
   275  			"v1 = 11 AND v1 = 15 AND v1 < 19",
   276  			nil,
   277  			[]int64{},
   278  		},
   279  		{
   280  			"v1 = 11 OR v1 <= 15",
   281  			[]*noms.ReadRange{
   282  				index.LessOrEqualRange(idxv1.tuple(15)),
   283  			},
   284  			[]int64{0, 1, 2, 3, 4, 5},
   285  		},
   286  		{
   287  			"v1 = 11 AND v1 <= 15",
   288  			[]*noms.ReadRange{
   289  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
   290  			},
   291  			[]int64{1},
   292  		},
   293  		{
   294  			"v1 = 11 OR v1 = 15 OR v1 <= 19",
   295  			[]*noms.ReadRange{
   296  				index.LessOrEqualRange(idxv1.tuple(19)),
   297  			},
   298  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   299  		},
   300  		{
   301  			"v1 = 11 OR v1 = 15 AND v1 <= 19",
   302  			[]*noms.ReadRange{
   303  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
   304  				index.ClosedRange(idxv1.tuple(15), idxv1.tuple(15)),
   305  			},
   306  			[]int64{1, 5},
   307  		},
   308  		{
   309  			"v1 = 11 AND v1 = 15 OR v1 <= 19",
   310  			[]*noms.ReadRange{
   311  				index.LessOrEqualRange(idxv1.tuple(19)),
   312  			},
   313  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   314  		},
   315  		{
   316  			"v1 = 11 AND v1 = 15 AND v1 <= 19",
   317  			nil,
   318  			[]int64{},
   319  		},
   320  		{
   321  			"v1 != 11",
   322  			[]*noms.ReadRange{
   323  				index.LessThanRange(idxv1.tuple(11)),
   324  				index.GreaterThanRange(idxv1.tuple(11)),
   325  			},
   326  			[]int64{0, 2, 3, 4, 5, 6, 7, 8, 9},
   327  		},
   328  		{
   329  			"v1 <> 11",
   330  			[]*noms.ReadRange{
   331  				index.LessThanRange(idxv1.tuple(11)),
   332  				index.GreaterThanRange(idxv1.tuple(11)),
   333  			},
   334  			[]int64{0, 2, 3, 4, 5, 6, 7, 8, 9},
   335  		},
   336  		{
   337  			"v1 != 11 OR v1 != 15",
   338  			[]*noms.ReadRange{
   339  				index.NotNullRange(),
   340  			},
   341  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   342  		},
   343  		{
   344  			"v1 <> 11 OR v1 <> 15",
   345  			[]*noms.ReadRange{
   346  				index.NotNullRange(),
   347  			},
   348  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   349  		},
   350  		{
   351  			"v1 != 11 AND v1 != 15",
   352  			[]*noms.ReadRange{
   353  				index.LessThanRange(idxv1.tuple(11)),
   354  				index.OpenRange(idxv1.tuple(11), idxv1.tuple(15)),
   355  				index.GreaterThanRange(idxv1.tuple(15)),
   356  			},
   357  			[]int64{0, 2, 3, 4, 6, 7, 8, 9},
   358  		},
   359  		{
   360  			"v1 <> 11 AND v1 <> 15",
   361  			[]*noms.ReadRange{
   362  				index.LessThanRange(idxv1.tuple(11)),
   363  				index.OpenRange(idxv1.tuple(11), idxv1.tuple(15)),
   364  				index.GreaterThanRange(idxv1.tuple(15)),
   365  			},
   366  			[]int64{0, 2, 3, 4, 6, 7, 8, 9},
   367  		},
   368  		{
   369  			"v1 != 11 OR v1 != 15 OR v1 != 19",
   370  			[]*noms.ReadRange{
   371  				index.NotNullRange(),
   372  			},
   373  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   374  		},
   375  		{
   376  			"v1 <> 11 OR v1 <> 15 OR v1 <> 19",
   377  			[]*noms.ReadRange{
   378  				index.NotNullRange(),
   379  			},
   380  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   381  		},
   382  		{
   383  			"v1 != 11 OR v1 != 15 AND v1 != 19",
   384  			[]*noms.ReadRange{
   385  				index.NotNullRange(),
   386  			},
   387  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   388  		},
   389  		{
   390  			"v1 <> 11 OR v1 <> 15 AND v1 <> 19",
   391  			[]*noms.ReadRange{
   392  				index.NotNullRange(),
   393  			},
   394  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   395  		},
   396  		{
   397  			"v1 != 11 AND v1 != 15 AND v1 != 19",
   398  			[]*noms.ReadRange{
   399  				index.LessThanRange(idxv1.tuple(11)),
   400  				index.OpenRange(idxv1.tuple(11), idxv1.tuple(15)),
   401  				index.OpenRange(idxv1.tuple(15), idxv1.tuple(19)),
   402  				index.GreaterThanRange(idxv1.tuple(19)),
   403  			},
   404  			[]int64{0, 2, 3, 4, 6, 7, 8},
   405  		},
   406  		{
   407  			"v1 <> 11 AND v1 <> 15 AND v1 <> 19",
   408  			[]*noms.ReadRange{
   409  				index.LessThanRange(idxv1.tuple(11)),
   410  				index.OpenRange(idxv1.tuple(11), idxv1.tuple(15)),
   411  				index.OpenRange(idxv1.tuple(15), idxv1.tuple(19)),
   412  				index.GreaterThanRange(idxv1.tuple(19)),
   413  			},
   414  			[]int64{0, 2, 3, 4, 6, 7, 8},
   415  		},
   416  		{
   417  			"v1 != 11 OR v1 > 15",
   418  			[]*noms.ReadRange{
   419  				index.LessThanRange(idxv1.tuple(11)),
   420  				index.GreaterThanRange(idxv1.tuple(11)),
   421  			},
   422  			[]int64{0, 2, 3, 4, 5, 6, 7, 8, 9},
   423  		},
   424  		{
   425  			"v1 != 11 AND v1 > 15",
   426  			[]*noms.ReadRange{
   427  				index.GreaterThanRange(idxv1.tuple(15)),
   428  			},
   429  			[]int64{6, 7, 8, 9},
   430  		},
   431  		{
   432  			"v1 != 11 OR v1 != 15 OR v1 > 19",
   433  			[]*noms.ReadRange{
   434  				index.NotNullRange(),
   435  			},
   436  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   437  		},
   438  		{
   439  			"v1 != 11 OR v1 != 15 AND v1 > 19",
   440  			[]*noms.ReadRange{
   441  				index.LessThanRange(idxv1.tuple(11)),
   442  				index.GreaterThanRange(idxv1.tuple(11)),
   443  			},
   444  			[]int64{0, 2, 3, 4, 5, 6, 7, 8, 9},
   445  		},
   446  		{
   447  			"v1 != 11 AND v1 != 15 OR v1 > 19",
   448  			[]*noms.ReadRange{
   449  				index.LessThanRange(idxv1.tuple(11)),
   450  				index.OpenRange(idxv1.tuple(11), idxv1.tuple(15)),
   451  				index.GreaterThanRange(idxv1.tuple(15)),
   452  			},
   453  			[]int64{0, 2, 3, 4, 6, 7, 8, 9},
   454  		},
   455  		{
   456  			"v1 != 11 AND v1 != 15 AND v1 > 19",
   457  			[]*noms.ReadRange{
   458  				index.GreaterThanRange(idxv1.tuple(19)),
   459  			},
   460  			[]int64{},
   461  		},
   462  		{
   463  			"v1 != 11 OR v1 >= 15",
   464  			[]*noms.ReadRange{
   465  				index.LessThanRange(idxv1.tuple(11)),
   466  				index.GreaterThanRange(idxv1.tuple(11)),
   467  			},
   468  			[]int64{0, 2, 3, 4, 5, 6, 7, 8, 9},
   469  		},
   470  		{
   471  			"v1 != 11 AND v1 >= 15",
   472  			[]*noms.ReadRange{
   473  				index.GreaterOrEqualRange(idxv1.tuple(15)),
   474  			},
   475  			[]int64{5, 6, 7, 8, 9},
   476  		},
   477  		{
   478  			"v1 != 11 OR v1 != 15 OR v1 >= 19",
   479  			[]*noms.ReadRange{
   480  				index.NotNullRange(),
   481  			},
   482  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   483  		},
   484  		{
   485  			"v1 != 11 OR v1 != 15 AND v1 >= 19",
   486  			[]*noms.ReadRange{
   487  				index.LessThanRange(idxv1.tuple(11)),
   488  				index.GreaterThanRange(idxv1.tuple(11)),
   489  			},
   490  			[]int64{0, 2, 3, 4, 5, 6, 7, 8, 9},
   491  		},
   492  		{
   493  			"v1 != 11 AND v1 != 15 OR v1 >= 19",
   494  			[]*noms.ReadRange{
   495  				index.LessThanRange(idxv1.tuple(11)),
   496  				index.OpenRange(idxv1.tuple(11), idxv1.tuple(15)),
   497  				index.GreaterThanRange(idxv1.tuple(15)),
   498  			},
   499  			[]int64{0, 2, 3, 4, 6, 7, 8, 9},
   500  		},
   501  		{
   502  			"v1 != 11 AND v1 != 15 AND v1 >= 19",
   503  			[]*noms.ReadRange{
   504  				index.GreaterOrEqualRange(idxv1.tuple(19)),
   505  			},
   506  			[]int64{9},
   507  		},
   508  		{
   509  			"v1 != 11 OR v1 < 15",
   510  			[]*noms.ReadRange{
   511  				index.NotNullRange(),
   512  			},
   513  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   514  		},
   515  		{
   516  			"v1 != 11 AND v1 < 15",
   517  			[]*noms.ReadRange{
   518  				index.LessThanRange(idxv1.tuple(11)),
   519  				index.OpenRange(idxv1.tuple(11), idxv1.tuple(15)),
   520  			},
   521  			[]int64{0, 2, 3, 4},
   522  		},
   523  		{
   524  			"v1 != 11 OR v1 != 15 OR v1 < 19",
   525  			[]*noms.ReadRange{
   526  				index.NotNullRange(),
   527  			},
   528  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   529  		},
   530  		{
   531  			"v1 != 11 OR v1 != 15 AND v1 < 19",
   532  			[]*noms.ReadRange{
   533  				index.NotNullRange(),
   534  			},
   535  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   536  		},
   537  		{
   538  			"v1 != 11 AND v1 != 15 OR v1 < 19",
   539  			[]*noms.ReadRange{
   540  				index.NotNullRange(),
   541  			},
   542  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   543  		},
   544  		{
   545  			"v1 != 11 AND v1 != 15 AND v1 < 19",
   546  			[]*noms.ReadRange{
   547  				index.LessThanRange(idxv1.tuple(11)),
   548  				index.OpenRange(idxv1.tuple(11), idxv1.tuple(15)),
   549  				index.OpenRange(idxv1.tuple(15), idxv1.tuple(19)),
   550  			},
   551  			[]int64{0, 2, 3, 4, 6, 7, 8},
   552  		},
   553  		{
   554  			"v1 != 11 OR v1 <= 15",
   555  			[]*noms.ReadRange{
   556  				index.NotNullRange(),
   557  			},
   558  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   559  		},
   560  		{
   561  			"v1 != 11 AND v1 <= 15",
   562  			[]*noms.ReadRange{
   563  				index.LessThanRange(idxv1.tuple(11)),
   564  				index.CustomRange(idxv1.tuple(11), idxv1.tuple(15), sql.Open, sql.Closed),
   565  			},
   566  			[]int64{0, 2, 3, 4, 5},
   567  		},
   568  		{
   569  			"v1 != 11 OR v1 != 15 OR v1 <= 19",
   570  			[]*noms.ReadRange{
   571  				index.NotNullRange(),
   572  			},
   573  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   574  		},
   575  		{
   576  			"v1 != 11 OR v1 != 15 AND v1 <= 19",
   577  			[]*noms.ReadRange{
   578  				index.NotNullRange(),
   579  			},
   580  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   581  		},
   582  		{
   583  			"v1 != 11 AND v1 != 15 OR v1 <= 19",
   584  			[]*noms.ReadRange{
   585  				index.NotNullRange(),
   586  			},
   587  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   588  		},
   589  		{
   590  			"v1 != 11 AND v1 != 15 AND v1 <= 19",
   591  			[]*noms.ReadRange{
   592  				index.LessThanRange(idxv1.tuple(11)),
   593  				index.OpenRange(idxv1.tuple(11), idxv1.tuple(15)),
   594  				index.CustomRange(idxv1.tuple(15), idxv1.tuple(19), sql.Open, sql.Closed),
   595  			},
   596  			[]int64{0, 2, 3, 4, 6, 7, 8, 9},
   597  		},
   598  		{
   599  			"v1 > 11",
   600  			[]*noms.ReadRange{
   601  				index.GreaterThanRange(idxv1.tuple(11)),
   602  			},
   603  			[]int64{2, 3, 4, 5, 6, 7, 8, 9},
   604  		},
   605  		{
   606  			"v1 > 11 OR v1 > 15",
   607  			[]*noms.ReadRange{
   608  				index.GreaterThanRange(idxv1.tuple(11)),
   609  			},
   610  			[]int64{2, 3, 4, 5, 6, 7, 8, 9},
   611  		},
   612  		{
   613  			"v1 > 11 AND v1 > 15",
   614  			[]*noms.ReadRange{
   615  				index.GreaterThanRange(idxv1.tuple(15)),
   616  			},
   617  			[]int64{6, 7, 8, 9},
   618  		},
   619  		{
   620  			"v1 > 11 OR v1 > 15 OR v1 > 19",
   621  			[]*noms.ReadRange{
   622  				index.GreaterThanRange(idxv1.tuple(11)),
   623  			},
   624  			[]int64{2, 3, 4, 5, 6, 7, 8, 9},
   625  		},
   626  		{
   627  			"v1 > 11 OR v1 > 15 AND v1 > 19",
   628  			[]*noms.ReadRange{
   629  				index.GreaterThanRange(idxv1.tuple(11)),
   630  			},
   631  			[]int64{2, 3, 4, 5, 6, 7, 8, 9},
   632  		},
   633  		{
   634  			"v1 > 11 AND v1 > 15 AND v1 > 19",
   635  			[]*noms.ReadRange{
   636  				index.GreaterThanRange(idxv1.tuple(19)),
   637  			},
   638  			[]int64{},
   639  		},
   640  		{
   641  			"v1 > 11 OR v1 >= 15",
   642  			[]*noms.ReadRange{
   643  				index.GreaterThanRange(idxv1.tuple(11)),
   644  			},
   645  			[]int64{2, 3, 4, 5, 6, 7, 8, 9},
   646  		},
   647  		{
   648  			"v1 > 11 AND v1 >= 15",
   649  			[]*noms.ReadRange{
   650  				index.GreaterOrEqualRange(idxv1.tuple(15)),
   651  			},
   652  			[]int64{5, 6, 7, 8, 9},
   653  		},
   654  		{
   655  			"v1 > 11 OR v1 > 15 OR v1 >= 19",
   656  			[]*noms.ReadRange{
   657  				index.GreaterThanRange(idxv1.tuple(11)),
   658  			},
   659  			[]int64{2, 3, 4, 5, 6, 7, 8, 9},
   660  		},
   661  		{
   662  			"v1 > 11 OR v1 > 15 AND v1 >= 19",
   663  			[]*noms.ReadRange{
   664  				index.GreaterThanRange(idxv1.tuple(11)),
   665  			},
   666  			[]int64{2, 3, 4, 5, 6, 7, 8, 9},
   667  		},
   668  		{
   669  			"v1 > 11 AND v1 > 15 OR v1 >= 19",
   670  			[]*noms.ReadRange{
   671  				index.GreaterThanRange(idxv1.tuple(15)),
   672  			},
   673  			[]int64{6, 7, 8, 9},
   674  		},
   675  		{
   676  			"v1 > 11 AND v1 > 15 AND v1 >= 19",
   677  			[]*noms.ReadRange{
   678  				index.GreaterOrEqualRange(idxv1.tuple(19)),
   679  			},
   680  			[]int64{9},
   681  		},
   682  		{
   683  			"v1 > 11 OR v1 < 15",
   684  			[]*noms.ReadRange{
   685  				index.NotNullRange(),
   686  			},
   687  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   688  		},
   689  		{
   690  			"v1 > 11 AND v1 < 15",
   691  			[]*noms.ReadRange{
   692  				index.OpenRange(idxv1.tuple(11), idxv1.tuple(15)),
   693  			},
   694  			[]int64{2, 3, 4},
   695  		},
   696  		{
   697  			"v1 > 11 OR v1 > 15 OR v1 < 19",
   698  			[]*noms.ReadRange{
   699  				index.NotNullRange(),
   700  			},
   701  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   702  		},
   703  		{
   704  			"v1 > 11 OR v1 > 15 AND v1 < 19",
   705  			[]*noms.ReadRange{
   706  				index.GreaterThanRange(idxv1.tuple(11)),
   707  			},
   708  			[]int64{2, 3, 4, 5, 6, 7, 8, 9},
   709  		},
   710  		{
   711  			"v1 > 11 AND v1 > 15 OR v1 < 19",
   712  			[]*noms.ReadRange{
   713  				index.NotNullRange(),
   714  			},
   715  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   716  		},
   717  		{
   718  			"v1 > 11 AND v1 > 15 AND v1 < 19",
   719  			[]*noms.ReadRange{
   720  				index.OpenRange(idxv1.tuple(15), idxv1.tuple(19)),
   721  			},
   722  			[]int64{6, 7, 8},
   723  		},
   724  		{
   725  			"v1 > 11 OR v1 <= 15",
   726  			[]*noms.ReadRange{
   727  				index.NotNullRange(),
   728  			},
   729  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   730  		},
   731  		{
   732  			"v1 > 11 AND v1 <= 15",
   733  			[]*noms.ReadRange{
   734  				index.CustomRange(idxv1.tuple(11), idxv1.tuple(15), sql.Open, sql.Closed),
   735  			},
   736  			[]int64{2, 3, 4, 5},
   737  		},
   738  		{
   739  			"v1 > 11 OR v1 > 15 OR v1 <= 19",
   740  			[]*noms.ReadRange{
   741  				index.NotNullRange(),
   742  			},
   743  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   744  		},
   745  		{
   746  			"v1 > 11 OR v1 > 15 AND v1 <= 19",
   747  			[]*noms.ReadRange{
   748  				index.GreaterThanRange(idxv1.tuple(11)),
   749  			},
   750  			[]int64{2, 3, 4, 5, 6, 7, 8, 9},
   751  		},
   752  		{
   753  			"v1 > 11 AND v1 > 15 OR v1 <= 19",
   754  			[]*noms.ReadRange{
   755  				index.NotNullRange(),
   756  			},
   757  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   758  		},
   759  		{
   760  			"v1 > 11 AND v1 > 15 AND v1 <= 19",
   761  			[]*noms.ReadRange{
   762  				index.CustomRange(idxv1.tuple(15), idxv1.tuple(19), sql.Open, sql.Closed),
   763  			},
   764  			[]int64{6, 7, 8, 9},
   765  		},
   766  		{
   767  			"v1 > 11 AND v1 > 15 AND v1 <= 19",
   768  			[]*noms.ReadRange{
   769  				index.CustomRange(idxv1.tuple(15), idxv1.tuple(19), sql.Open, sql.Closed),
   770  			},
   771  			[]int64{6, 7, 8, 9},
   772  		},
   773  		{
   774  			"v1 > 11 AND v1 < 15 OR v1 > 15 AND v1 < 19",
   775  			[]*noms.ReadRange{
   776  				index.OpenRange(idxv1.tuple(11), idxv1.tuple(15)),
   777  				index.OpenRange(idxv1.tuple(15), idxv1.tuple(19)),
   778  			},
   779  			[]int64{2, 3, 4, 6, 7, 8},
   780  		},
   781  		{
   782  			"v1 >= 11",
   783  			[]*noms.ReadRange{
   784  				index.GreaterOrEqualRange(idxv1.tuple(11)),
   785  			},
   786  			[]int64{1, 2, 3, 4, 5, 6, 7, 8, 9},
   787  		},
   788  		{
   789  			"v1 >= 11 OR v1 >= 15",
   790  			[]*noms.ReadRange{
   791  				index.GreaterOrEqualRange(idxv1.tuple(11)),
   792  			},
   793  			[]int64{1, 2, 3, 4, 5, 6, 7, 8, 9},
   794  		},
   795  		{
   796  			"v1 >= 11 AND v1 >= 15",
   797  			[]*noms.ReadRange{
   798  				index.GreaterOrEqualRange(idxv1.tuple(15)),
   799  			},
   800  			[]int64{5, 6, 7, 8, 9},
   801  		},
   802  		{
   803  			"v1 >= 11 OR v1 >= 15 OR v1 >= 19",
   804  			[]*noms.ReadRange{
   805  				index.GreaterOrEqualRange(idxv1.tuple(11)),
   806  			},
   807  			[]int64{1, 2, 3, 4, 5, 6, 7, 8, 9},
   808  		},
   809  		{
   810  			"v1 >= 11 OR v1 >= 15 AND v1 >= 19",
   811  			[]*noms.ReadRange{
   812  				index.GreaterOrEqualRange(idxv1.tuple(11)),
   813  			},
   814  			[]int64{1, 2, 3, 4, 5, 6, 7, 8, 9},
   815  		},
   816  		{
   817  			"v1 >= 11 AND v1 >= 15 AND v1 >= 19",
   818  			[]*noms.ReadRange{
   819  				index.GreaterOrEqualRange(idxv1.tuple(19)),
   820  			},
   821  			[]int64{9},
   822  		},
   823  		{
   824  			"v1 >= 11 OR v1 < 15",
   825  			[]*noms.ReadRange{
   826  				index.NotNullRange(),
   827  			},
   828  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   829  		},
   830  		{
   831  			"v1 >= 11 AND v1 < 15",
   832  			[]*noms.ReadRange{
   833  				index.CustomRange(idxv1.tuple(11), idxv1.tuple(15), sql.Closed, sql.Open),
   834  			},
   835  			[]int64{1, 2, 3, 4},
   836  		},
   837  		{
   838  			"v1 >= 11 OR v1 >= 15 OR v1 < 19",
   839  			[]*noms.ReadRange{
   840  				index.NotNullRange(),
   841  			},
   842  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   843  		},
   844  		{
   845  			"v1 >= 11 OR v1 >= 15 AND v1 < 19",
   846  			[]*noms.ReadRange{
   847  				index.GreaterOrEqualRange(idxv1.tuple(11)),
   848  			},
   849  			[]int64{1, 2, 3, 4, 5, 6, 7, 8, 9},
   850  		},
   851  		{
   852  			"v1 >= 11 AND v1 >= 15 OR v1 < 19",
   853  			[]*noms.ReadRange{
   854  				index.NotNullRange(),
   855  			},
   856  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   857  		},
   858  		{
   859  			"v1 >= 11 AND v1 >= 15 AND v1 < 19",
   860  			[]*noms.ReadRange{
   861  				index.CustomRange(idxv1.tuple(15), idxv1.tuple(19), sql.Closed, sql.Open),
   862  			},
   863  			[]int64{5, 6, 7, 8},
   864  		},
   865  		{
   866  			"v1 >= 11 OR v1 <= 15",
   867  			[]*noms.ReadRange{
   868  				index.NotNullRange(),
   869  			},
   870  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   871  		},
   872  		{
   873  			"v1 >= 11 AND v1 <= 15",
   874  			[]*noms.ReadRange{
   875  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(15)),
   876  			},
   877  			[]int64{1, 2, 3, 4, 5},
   878  		},
   879  		{
   880  			"v1 >= 11 OR v1 >= 15 OR v1 <= 19",
   881  			[]*noms.ReadRange{
   882  				index.NotNullRange(),
   883  			},
   884  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   885  		},
   886  		{
   887  			"v1 >= 11 OR v1 >= 15 AND v1 <= 19",
   888  			[]*noms.ReadRange{
   889  				index.GreaterOrEqualRange(idxv1.tuple(11)),
   890  			},
   891  			[]int64{1, 2, 3, 4, 5, 6, 7, 8, 9},
   892  		},
   893  		{
   894  			"v1 >= 11 AND v1 >= 15 OR v1 <= 19",
   895  			[]*noms.ReadRange{
   896  				index.NotNullRange(),
   897  			},
   898  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   899  		},
   900  		{
   901  			"v1 >= 11 AND v1 >= 15 AND v1 <= 19",
   902  			[]*noms.ReadRange{
   903  				index.ClosedRange(idxv1.tuple(15), idxv1.tuple(19)),
   904  			},
   905  			[]int64{5, 6, 7, 8, 9},
   906  		},
   907  		{
   908  			"v1 >= 11 AND v1 <= 14 OR v1 >= 16 AND v1 <= 19",
   909  			[]*noms.ReadRange{
   910  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(14)),
   911  				index.ClosedRange(idxv1.tuple(16), idxv1.tuple(19)),
   912  			},
   913  			[]int64{1, 2, 3, 4, 6, 7, 8, 9},
   914  		},
   915  		{
   916  			"v1 < 11",
   917  			[]*noms.ReadRange{
   918  				index.LessThanRange(idxv1.tuple(11)),
   919  			},
   920  			[]int64{0},
   921  		},
   922  		{
   923  			"v1 < 11 OR v1 < 15",
   924  			[]*noms.ReadRange{
   925  				index.LessThanRange(idxv1.tuple(15)),
   926  			},
   927  			[]int64{0, 1, 2, 3, 4},
   928  		},
   929  		{
   930  			"v1 < 11 AND v1 < 15",
   931  			[]*noms.ReadRange{
   932  				index.LessThanRange(idxv1.tuple(11)),
   933  			},
   934  			[]int64{0},
   935  		},
   936  		{
   937  			"v1 < 11 OR v1 < 15 OR v1 < 19",
   938  			[]*noms.ReadRange{
   939  				index.LessThanRange(idxv1.tuple(19)),
   940  			},
   941  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8},
   942  		},
   943  		{
   944  			"v1 < 11 OR v1 < 15 AND v1 < 19",
   945  			[]*noms.ReadRange{
   946  				index.LessThanRange(idxv1.tuple(15)),
   947  			},
   948  			[]int64{0, 1, 2, 3, 4},
   949  		},
   950  		{
   951  			"v1 < 11 AND v1 < 15 AND v1 < 19",
   952  			[]*noms.ReadRange{
   953  				index.LessThanRange(idxv1.tuple(11)),
   954  			},
   955  			[]int64{0},
   956  		},
   957  		{
   958  			"v1 < 11 OR v1 > 15",
   959  			[]*noms.ReadRange{
   960  				index.LessThanRange(idxv1.tuple(11)),
   961  				index.GreaterThanRange(idxv1.tuple(15)),
   962  			},
   963  			[]int64{0, 6, 7, 8, 9},
   964  		},
   965  		{
   966  			"v1 < 11 AND v1 > 15",
   967  			nil,
   968  			[]int64{},
   969  		},
   970  		{
   971  			"v1 < 11 OR v1 <= 15",
   972  			[]*noms.ReadRange{
   973  				index.LessOrEqualRange(idxv1.tuple(15)),
   974  			},
   975  			[]int64{0, 1, 2, 3, 4, 5},
   976  		},
   977  		{
   978  			"v1 < 11 AND v1 <= 15",
   979  			[]*noms.ReadRange{
   980  				index.LessThanRange(idxv1.tuple(11)),
   981  			},
   982  			[]int64{0},
   983  		},
   984  		{
   985  			"v1 < 11 OR v1 < 15 OR v1 <= 19",
   986  			[]*noms.ReadRange{
   987  				index.LessOrEqualRange(idxv1.tuple(19)),
   988  			},
   989  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
   990  		},
   991  		{
   992  			"v1 < 11 OR v1 < 15 AND v1 <= 19",
   993  			[]*noms.ReadRange{
   994  				index.LessThanRange(idxv1.tuple(15)),
   995  			},
   996  			[]int64{0, 1, 2, 3, 4},
   997  		},
   998  		{
   999  			"v1 < 11 AND v1 < 15 OR v1 <= 19",
  1000  			[]*noms.ReadRange{
  1001  				index.LessOrEqualRange(idxv1.tuple(19)),
  1002  			},
  1003  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
  1004  		},
  1005  		{
  1006  			"v1 < 11 AND v1 < 15 AND v1 <= 19",
  1007  			[]*noms.ReadRange{
  1008  				index.LessThanRange(idxv1.tuple(11)),
  1009  			},
  1010  			[]int64{0},
  1011  		},
  1012  		{
  1013  			"v1 < 11 OR v1 >= 15",
  1014  			[]*noms.ReadRange{
  1015  				index.LessThanRange(idxv1.tuple(11)),
  1016  				index.GreaterOrEqualRange(idxv1.tuple(15)),
  1017  			},
  1018  			[]int64{0, 5, 6, 7, 8, 9},
  1019  		},
  1020  		{
  1021  			"v1 < 11 AND v1 >= 15",
  1022  			nil,
  1023  			[]int64{},
  1024  		},
  1025  		{
  1026  			"(v1 < 13 OR v1 > 16) AND (v1 > 10 OR v1 < 19)",
  1027  			[]*noms.ReadRange{
  1028  				index.LessThanRange(idxv1.tuple(13)),
  1029  				index.GreaterThanRange(idxv1.tuple(16)),
  1030  			},
  1031  			[]int64{0, 1, 2, 7, 8, 9},
  1032  		},
  1033  		{
  1034  			"v1 <= 11",
  1035  			[]*noms.ReadRange{
  1036  				index.LessOrEqualRange(idxv1.tuple(11)),
  1037  			},
  1038  			[]int64{0, 1},
  1039  		},
  1040  		{
  1041  			"v1 <= 11 OR v1 <= 15",
  1042  			[]*noms.ReadRange{
  1043  				index.LessOrEqualRange(idxv1.tuple(15)),
  1044  			},
  1045  			[]int64{0, 1, 2, 3, 4, 5},
  1046  		},
  1047  		{
  1048  			"v1 <= 11 AND v1 <= 15",
  1049  			[]*noms.ReadRange{
  1050  				index.LessOrEqualRange(idxv1.tuple(11)),
  1051  			},
  1052  			[]int64{0, 1},
  1053  		},
  1054  		{
  1055  			"v1 <= 11 OR v1 <= 15 OR v1 <= 19",
  1056  			[]*noms.ReadRange{
  1057  				index.LessOrEqualRange(idxv1.tuple(19)),
  1058  			},
  1059  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
  1060  		},
  1061  		{
  1062  			"v1 <= 11 OR v1 <= 15 AND v1 <= 19",
  1063  			[]*noms.ReadRange{
  1064  				index.LessOrEqualRange(idxv1.tuple(15)),
  1065  			},
  1066  			[]int64{0, 1, 2, 3, 4, 5},
  1067  		},
  1068  		{
  1069  			"v1 <= 11 AND v1 <= 15 AND v1 <= 19",
  1070  			[]*noms.ReadRange{
  1071  				index.LessOrEqualRange(idxv1.tuple(11)),
  1072  			},
  1073  			[]int64{0, 1},
  1074  		},
  1075  		{
  1076  			"v1 <= 11 OR v1 > 15",
  1077  			[]*noms.ReadRange{
  1078  				index.LessOrEqualRange(idxv1.tuple(11)),
  1079  				index.GreaterThanRange(idxv1.tuple(15)),
  1080  			},
  1081  			[]int64{0, 1, 6, 7, 8, 9},
  1082  		},
  1083  		{
  1084  			"v1 <= 11 AND v1 > 15",
  1085  			nil,
  1086  			[]int64{},
  1087  		},
  1088  		{
  1089  			"v1 <= 11 OR v1 >= 15",
  1090  			[]*noms.ReadRange{
  1091  				index.LessOrEqualRange(idxv1.tuple(11)),
  1092  				index.GreaterOrEqualRange(idxv1.tuple(15)),
  1093  			},
  1094  			[]int64{0, 1, 5, 6, 7, 8, 9},
  1095  		},
  1096  		{
  1097  			"v1 <= 11 AND v1 >= 15",
  1098  			nil,
  1099  			[]int64{},
  1100  		},
  1101  		{
  1102  			"v1 BETWEEN 11 AND 15",
  1103  			[]*noms.ReadRange{
  1104  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(15)),
  1105  			},
  1106  			[]int64{1, 2, 3, 4, 5},
  1107  		},
  1108  		{
  1109  			"v1 BETWEEN 11 AND 15 OR v1 BETWEEN 15 AND 19",
  1110  			[]*noms.ReadRange{
  1111  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(19)),
  1112  			},
  1113  			[]int64{1, 2, 3, 4, 5, 6, 7, 8, 9},
  1114  		},
  1115  		{
  1116  			"v1 BETWEEN 11 AND 15 AND v1 BETWEEN 15 AND 19",
  1117  			[]*noms.ReadRange{
  1118  				index.ClosedRange(idxv1.tuple(15), idxv1.tuple(15)),
  1119  			},
  1120  			[]int64{5},
  1121  		},
  1122  		{
  1123  			"v1 BETWEEN 11 AND 15 OR v1 = 13",
  1124  			[]*noms.ReadRange{
  1125  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(15)),
  1126  			},
  1127  			[]int64{1, 2, 3, 4, 5},
  1128  		},
  1129  		{
  1130  			"v1 BETWEEN 11 AND 15 OR v1 != 13",
  1131  			[]*noms.ReadRange{
  1132  				index.NotNullRange(),
  1133  			},
  1134  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
  1135  		},
  1136  		{
  1137  			"v1 BETWEEN 11 AND 15 AND v1 != 13",
  1138  			[]*noms.ReadRange{
  1139  				index.CustomRange(idxv1.tuple(11), idxv1.tuple(13), sql.Closed, sql.Open),
  1140  				index.CustomRange(idxv1.tuple(13), idxv1.tuple(15), sql.Open, sql.Closed),
  1141  			},
  1142  			[]int64{1, 2, 4, 5},
  1143  		},
  1144  		{
  1145  			"v1 BETWEEN 11 AND 15 AND v1 <= 19",
  1146  			[]*noms.ReadRange{
  1147  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(15)),
  1148  			},
  1149  			[]int64{1, 2, 3, 4, 5},
  1150  		},
  1151  		{
  1152  			"v1 BETWEEN 11 AND 15 AND v1 <= 19",
  1153  			[]*noms.ReadRange{
  1154  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(15)),
  1155  			},
  1156  			[]int64{1, 2, 3, 4, 5},
  1157  		},
  1158  		{
  1159  			"v1 IN (11, 12, 13)",
  1160  			[]*noms.ReadRange{
  1161  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
  1162  				index.ClosedRange(idxv1.tuple(12), idxv1.tuple(12)),
  1163  				index.ClosedRange(idxv1.tuple(13), idxv1.tuple(13)),
  1164  			},
  1165  			[]int64{1, 2, 3},
  1166  		},
  1167  		{
  1168  			"v1 IN (11, 12, 13) OR v1 BETWEEN 11 and 13",
  1169  			[]*noms.ReadRange{
  1170  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(13)),
  1171  			},
  1172  			[]int64{1, 2, 3},
  1173  		},
  1174  		{
  1175  			"v1 IN (11, 12, 13) AND v1 > 11",
  1176  			[]*noms.ReadRange{
  1177  				index.ClosedRange(idxv1.tuple(12), idxv1.tuple(12)),
  1178  				index.ClosedRange(idxv1.tuple(13), idxv1.tuple(13)),
  1179  			},
  1180  			[]int64{2, 3},
  1181  		},
  1182  		{
  1183  			"v1 IN (11, 12, 13) OR v1 != 12",
  1184  			[]*noms.ReadRange{
  1185  				index.NotNullRange(),
  1186  			},
  1187  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
  1188  		},
  1189  		{
  1190  			"v1 IN (11, 12, 13) AND v1 != 12",
  1191  			[]*noms.ReadRange{
  1192  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
  1193  				index.ClosedRange(idxv1.tuple(13), idxv1.tuple(13)),
  1194  			},
  1195  			[]int64{1, 3},
  1196  		},
  1197  		{
  1198  			"v1 IN (11, 12, 13) OR v1 >= 13 AND v1 < 15",
  1199  			[]*noms.ReadRange{
  1200  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
  1201  				index.ClosedRange(idxv1.tuple(12), idxv1.tuple(12)),
  1202  				index.CustomRange(idxv1.tuple(13), idxv1.tuple(15), sql.Closed, sql.Open),
  1203  			},
  1204  			[]int64{1, 2, 3, 4},
  1205  		},
  1206  		{
  1207  			"v2 = 21 AND v1 = 11 OR v2 > 25 AND v1 > 11",
  1208  			[]*noms.ReadRange{
  1209  				index.ClosedRange(idxv2v1.tuple(21, 11), idxv2v1.tuple(21, 11)),
  1210  				index.GreaterThanRange(idxv2v1.tuple(25, 11)),
  1211  			},
  1212  			[]int64{1, 6, 7, 8, 9},
  1213  		},
  1214  		{
  1215  			"v2 > 21 AND v1 > 11 AND v2 < 25 AND v1 < 15",
  1216  			[]*noms.ReadRange{
  1217  				index.OpenRange(idxv2v1.tuple(21, 11), idxv2v1.tuple(25, 15)),
  1218  			},
  1219  			[]int64{2, 3, 4},
  1220  		},
  1221  		{
  1222  			"v2 = 21",
  1223  			[]*noms.ReadRange{
  1224  				index.ClosedRange(idxv2v1Gen.tuple(21), idxv2v1Gen.tuple(21)),
  1225  			},
  1226  			[]int64{1},
  1227  		},
  1228  		{
  1229  			"v2 = 21 OR v2 = 25",
  1230  			[]*noms.ReadRange{
  1231  				index.ClosedRange(idxv2v1Gen.tuple(21), idxv2v1Gen.tuple(21)),
  1232  				index.ClosedRange(idxv2v1Gen.tuple(25), idxv2v1Gen.tuple(25)),
  1233  			},
  1234  			[]int64{1, 5},
  1235  		},
  1236  		{
  1237  			"v2 = 21 AND v2 = 25",
  1238  			nil,
  1239  			[]int64{},
  1240  		},
  1241  		{
  1242  			"v2 = 21 OR v2 = 25 OR v2 = 29",
  1243  			[]*noms.ReadRange{
  1244  				index.ClosedRange(idxv2v1Gen.tuple(21), idxv2v1Gen.tuple(21)),
  1245  				index.ClosedRange(idxv2v1Gen.tuple(25), idxv2v1Gen.tuple(25)),
  1246  				index.ClosedRange(idxv2v1Gen.tuple(29), idxv2v1Gen.tuple(29)),
  1247  			},
  1248  			[]int64{1, 5, 9},
  1249  		},
  1250  		{
  1251  			"v2 = 21 OR v2 = 25 AND v2 = 29",
  1252  			[]*noms.ReadRange{
  1253  				index.ClosedRange(idxv2v1Gen.tuple(21), idxv2v1Gen.tuple(21)),
  1254  			},
  1255  			[]int64{1},
  1256  		},
  1257  		{
  1258  			"v2 = 21 AND v2 = 25 AND v2 = 29",
  1259  			nil,
  1260  			[]int64{},
  1261  		},
  1262  		{
  1263  			"v2 = 21 OR v2 != 21",
  1264  			[]*noms.ReadRange{
  1265  				index.NotNullRange(),
  1266  			},
  1267  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
  1268  		},
  1269  		{
  1270  			"v2 = 21 OR v2 != 25",
  1271  			[]*noms.ReadRange{
  1272  				index.LessThanRange(idxv2v1Gen.tuple(25)),
  1273  				index.GreaterThanRange(idxv2v1Gen.tuple(25)),
  1274  			},
  1275  			[]int64{0, 1, 2, 3, 4, 6, 7, 8, 9},
  1276  		},
  1277  		{
  1278  			"v2 = 21 AND v2 != 25",
  1279  			[]*noms.ReadRange{
  1280  				index.ClosedRange(idxv2v1Gen.tuple(21), idxv2v1Gen.tuple(21)),
  1281  			},
  1282  			[]int64{1},
  1283  		},
  1284  		{
  1285  			"v2 = 21 OR v2 = 25 OR v2 != 29",
  1286  			[]*noms.ReadRange{
  1287  				index.LessThanRange(idxv2v1Gen.tuple(29)),
  1288  				index.GreaterThanRange(idxv2v1Gen.tuple(29)),
  1289  			},
  1290  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8},
  1291  		},
  1292  		{
  1293  			"v2 = 21 OR v2 = 25 AND v2 != 29",
  1294  			[]*noms.ReadRange{
  1295  				index.ClosedRange(idxv2v1Gen.tuple(21), idxv2v1Gen.tuple(21)),
  1296  				index.ClosedRange(idxv2v1Gen.tuple(25), idxv2v1Gen.tuple(25)),
  1297  			},
  1298  			[]int64{1, 5},
  1299  		},
  1300  		{
  1301  			"v2 = 21 AND v2 = 25 OR v2 != 29",
  1302  			[]*noms.ReadRange{
  1303  				index.LessThanRange(idxv2v1Gen.tuple(29)),
  1304  				index.GreaterThanRange(idxv2v1Gen.tuple(29)),
  1305  			},
  1306  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8},
  1307  		},
  1308  		{
  1309  			"v2 = 21 AND v2 = 25 AND v2 != 29",
  1310  			nil,
  1311  			[]int64{},
  1312  		},
  1313  	}
  1314  
  1315  	for _, test := range tests {
  1316  		t.Run(test.whereStmt, func(t *testing.T) {
  1317  			query := fmt.Sprintf(`SELECT pk FROM test WHERE %s ORDER BY 1`, test.whereStmt)
  1318  
  1319  			finalRanges, err := ReadRangesFromQuery(sqlCtx, engine, query)
  1320  			require.NoError(t, err)
  1321  
  1322  			_, iter, err := engine.Query(sqlCtx, query)
  1323  			require.NoError(t, err)
  1324  			res, err := sql.RowIterToRows(sqlCtx, iter)
  1325  			require.NoError(t, err)
  1326  
  1327  			if assert.Equal(t, len(test.pks), len(res)) {
  1328  				for i, pk := range test.pks {
  1329  					if assert.Equal(t, 1, len(res[i])) {
  1330  						assert.Equal(t, pk, res[i][0])
  1331  					}
  1332  				}
  1333  			}
  1334  
  1335  			if assert.Equal(t, len(test.finalRanges), len(finalRanges)) {
  1336  				finalRangeMatches := make([]bool, len(finalRanges))
  1337  				for i, testFinalRange := range test.finalRanges {
  1338  					for _, finalRange := range finalRanges {
  1339  						if index.ReadRangesEqual(finalRange, testFinalRange) {
  1340  							if finalRangeMatches[i] {
  1341  								require.FailNow(t, fmt.Sprintf("Duplicate ReadRange: `%v`", finalRange))
  1342  							} else {
  1343  								finalRangeMatches[i] = true
  1344  							}
  1345  						}
  1346  					}
  1347  				}
  1348  				for _, finalRangeMatch := range finalRangeMatches {
  1349  					if !finalRangeMatch {
  1350  						require.FailNow(t, fmt.Sprintf("Expected: `%v`\nActual:   `%v`", test.finalRanges, finalRanges))
  1351  					}
  1352  				}
  1353  			} else {
  1354  				t.Logf("%v != %v", test.finalRanges, finalRanges)
  1355  			}
  1356  		})
  1357  	}
  1358  }
  1359  
  1360  // TestMergeableIndexesNulls is based on TestMergeableIndexes, but specifically handles IS NULL and IS NOT NULL.
  1361  // For now, some of these tests are broken, but they return the correct end result. As NULL is encoded as being a value
  1362  // larger than all integers, == NULL becomes a subset of > x and >= x, thus the intersection returns == NULL.
  1363  // The correct behavior would be to return the empty range in that example. However, as the SQL engine still filters the
  1364  // returned results, we end up with zero values actually being returned, just like we'd expect from the empty range.
  1365  // As a consequence, I'm leaving these tests in to verify that the overall result is correct, but the intermediate
  1366  // ranges may be incorrect.
  1367  // TODO: disassociate NULL ranges from value ranges and fix the intermediate ranges (finalRanges).
  1368  func TestMergeableIndexesNulls(t *testing.T) {
  1369  	if types.Format_Default != types.Format_LD_1 {
  1370  		t.Skip() // this test is specific to Noms ranges
  1371  	}
  1372  
  1373  	engine, sqlCtx, indexTuples := setupIndexes(t, "test", `INSERT INTO test VALUES
  1374  		(0, 10, 20),
  1375  		(1, 11, 21),
  1376  		(2, NULL, NULL),
  1377  		(3, 13, 23),
  1378  		(4, NULL, NULL),
  1379  		(5, 15, 25),
  1380  		(6, NULL, NULL),
  1381  		(7, 17, 27),
  1382  		(8, 18, 28),
  1383  		(9, 19, 29);`)
  1384  	idxv1 := indexTuples[0]
  1385  
  1386  	tests := []struct {
  1387  		whereStmt   string
  1388  		finalRanges []*noms.ReadRange
  1389  		pks         []int64
  1390  	}{
  1391  		{
  1392  			"v1 IS NULL",
  1393  			[]*noms.ReadRange{
  1394  				index.NullRange(),
  1395  			},
  1396  			[]int64{2, 4, 6},
  1397  		},
  1398  		{
  1399  			"v1 IS NULL OR v1 IS NULL",
  1400  			[]*noms.ReadRange{
  1401  				index.NullRange(),
  1402  			},
  1403  			[]int64{2, 4, 6},
  1404  		},
  1405  		{
  1406  			"v1 IS NULL AND v1 IS NULL",
  1407  			[]*noms.ReadRange{
  1408  				index.NullRange(),
  1409  			},
  1410  			[]int64{2, 4, 6},
  1411  		},
  1412  		{
  1413  			"v1 IS NULL OR v1 = 11",
  1414  			[]*noms.ReadRange{
  1415  				index.ClosedRange(idxv1.tuple(11), idxv1.tuple(11)),
  1416  				index.NullRange(),
  1417  			},
  1418  			[]int64{1, 2, 4, 6},
  1419  		},
  1420  		{
  1421  			"v1 IS NULL OR v1 < 16",
  1422  			[]*noms.ReadRange{
  1423  				index.LessThanRange(idxv1.tuple(16)),
  1424  				index.NullRange(),
  1425  			},
  1426  			[]int64{0, 1, 2, 3, 4, 5, 6},
  1427  		},
  1428  		{
  1429  			"v1 IS NULL OR v1 > 16",
  1430  			[]*noms.ReadRange{
  1431  				index.NullRange(),
  1432  				index.GreaterThanRange(idxv1.tuple(16)),
  1433  			},
  1434  			[]int64{2, 4, 6, 7, 8, 9},
  1435  		},
  1436  		{
  1437  			"v1 IS NULL AND v1 < 16",
  1438  			nil,
  1439  			[]int64{},
  1440  		},
  1441  		{
  1442  			"v1 IS NULL AND v1 > 16",
  1443  			[]*noms.ReadRange{},
  1444  			[]int64{},
  1445  		},
  1446  		{
  1447  			"v1 IS NULL OR v1 IS NOT NULL",
  1448  			[]*noms.ReadRange{
  1449  				index.AllRange(),
  1450  			},
  1451  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
  1452  		},
  1453  		{
  1454  			"v1 IS NULL AND v1 IS NOT NULL",
  1455  			nil,
  1456  			[]int64{},
  1457  		},
  1458  		{
  1459  			"v1 IS NOT NULL",
  1460  			[]*noms.ReadRange{
  1461  				index.NotNullRange(),
  1462  			},
  1463  			[]int64{0, 1, 3, 5, 7, 8, 9},
  1464  		},
  1465  		{
  1466  			"v1 IS NOT NULL OR v1 IS NULL",
  1467  			[]*noms.ReadRange{
  1468  				index.AllRange(),
  1469  			},
  1470  			[]int64{0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
  1471  		},
  1472  		{
  1473  			"v1 IS NOT NULL AND v1 IS NULL",
  1474  			nil,
  1475  			[]int64{},
  1476  		},
  1477  		{
  1478  			"v1 IS NOT NULL OR v1 = 15",
  1479  			[]*noms.ReadRange{
  1480  				index.NotNullRange(),
  1481  			},
  1482  			[]int64{0, 1, 3, 5, 7, 8, 9},
  1483  		},
  1484  		{
  1485  			"v1 IS NOT NULL OR v1 > 16",
  1486  			[]*noms.ReadRange{
  1487  				index.NotNullRange(),
  1488  			},
  1489  			[]int64{0, 1, 3, 5, 7, 8, 9},
  1490  		},
  1491  		{
  1492  			"v1 IS NOT NULL OR v1 < 16",
  1493  			[]*noms.ReadRange{
  1494  				index.NotNullRange(),
  1495  			},
  1496  			[]int64{0, 1, 3, 5, 7, 8, 9},
  1497  		},
  1498  		{
  1499  			"v1 IS NOT NULL AND v1 = 15",
  1500  			[]*noms.ReadRange{
  1501  				index.ClosedRange(idxv1.tuple(15), idxv1.tuple(15)),
  1502  			},
  1503  			[]int64{5},
  1504  		},
  1505  		{
  1506  			"v1 IS NOT NULL AND v1 > 16",
  1507  			[]*noms.ReadRange{
  1508  				index.GreaterThanRange(idxv1.tuple(16)),
  1509  			},
  1510  			[]int64{7, 8, 9},
  1511  		},
  1512  		{
  1513  			"v1 IS NOT NULL AND v1 < 16",
  1514  			[]*noms.ReadRange{
  1515  				index.LessThanRange(idxv1.tuple(16)),
  1516  			},
  1517  			[]int64{0, 1, 3, 5},
  1518  		},
  1519  	}
  1520  
  1521  	for _, test := range tests {
  1522  		t.Run(test.whereStmt, func(t *testing.T) {
  1523  			query := fmt.Sprintf(`SELECT pk FROM test WHERE %s ORDER BY 1`, test.whereStmt)
  1524  
  1525  			finalRanges, err := ReadRangesFromQuery(sqlCtx, engine, query)
  1526  			require.NoError(t, err)
  1527  
  1528  			_, iter, err := engine.Query(sqlCtx, query)
  1529  			require.NoError(t, err)
  1530  
  1531  			res, err := sql.RowIterToRows(sqlCtx, iter)
  1532  			require.NoError(t, err)
  1533  			if assert.Equal(t, len(test.pks), len(res)) {
  1534  				for i, pk := range test.pks {
  1535  					if assert.Equal(t, 1, len(res[i])) {
  1536  						assert.Equal(t, pk, res[i][0])
  1537  					}
  1538  				}
  1539  			}
  1540  
  1541  			if assert.Equal(t, len(test.finalRanges), len(finalRanges)) {
  1542  				finalRangeMatches := make([]bool, len(finalRanges))
  1543  				for _, finalRange := range finalRanges {
  1544  					for i, testFinalRange := range test.finalRanges {
  1545  						if index.ReadRangesEqual(finalRange, testFinalRange) {
  1546  							if finalRangeMatches[i] {
  1547  								require.FailNow(t, fmt.Sprintf("Duplicate ReadRange: `%v`", finalRange))
  1548  							} else {
  1549  								finalRangeMatches[i] = true
  1550  							}
  1551  						}
  1552  					}
  1553  				}
  1554  				for _, finalRangeMatch := range finalRangeMatches {
  1555  					if !finalRangeMatch {
  1556  						require.FailNow(t, fmt.Sprintf("Expected: `%v`\nActual:   `%v`", test.finalRanges, finalRanges))
  1557  					}
  1558  				}
  1559  			} else {
  1560  				t.Logf("%v != %v", test.finalRanges, finalRanges)
  1561  			}
  1562  		})
  1563  	}
  1564  }
  1565  
  1566  func ReadRangesFromQuery(ctx *sql.Context, eng *sqle.Engine, query string) ([]*noms.ReadRange, error) {
  1567  	binder := planbuilder.New(ctx, eng.Analyzer.Catalog, eng.Parser)
  1568  	parsed, _, _, err := binder.Parse(query, false)
  1569  	if err != nil {
  1570  		return nil, err
  1571  	}
  1572  
  1573  	analyzed, err := eng.Analyzer.Analyze(ctx, parsed, nil)
  1574  	if err != nil {
  1575  		return nil, err
  1576  	}
  1577  
  1578  	var lookup sql.IndexLookup
  1579  	transform.Inspect(analyzed, func(n sql.Node) bool {
  1580  		switch node := n.(type) {
  1581  		case *plan.IndexedTableAccess:
  1582  			lookup = plan.GetIndexLookup(node)
  1583  		}
  1584  		return true
  1585  	})
  1586  
  1587  	return index.NomsRangesFromIndexLookup(ctx, lookup)
  1588  }