SQLite

Check-in [1559f4c434]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix another problem involving vector range constraints and mixed ASC/DESC indexes.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | rowvalue
Files: files | file ages | folders
SHA1: 1559f4c43473e107f7196eea3ee91c53ede22999
User & Date: dan 2016-08-03 16:39:04.109
Context
2016-08-03
18:00
Fix a problem with estimating the number of rows visited by a query that uses a multi-column IN(SELECT...) constraint. (check-in: 3c2f908f5b user: dan tags: rowvalue)
16:39
Fix another problem involving vector range constraints and mixed ASC/DESC indexes. (check-in: 1559f4c434 user: dan tags: rowvalue)
16:14
Fix stat4-based cost estimates for vector range constraints. (check-in: 18af74abc8 user: dan tags: rowvalue)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244

    /* Check that the LHS of the comparison is a column reference to
    ** the right column of the right source table. And that the sort
    ** order of the index column is the same as the sort order of the
    ** leftmost index column.  */
    if( pLhs->op!=TK_COLUMN 
     || pLhs->iTable!=iCur 
     || pLhs->iColumn!=pIdx->aiColumn[i+nEq] 
     || pIdx->aSortOrder[i]!=pIdx->aSortOrder[0]
    ){
      break;
    }

    aff = sqlite3CompareAffinity(pRhs, sqlite3ExprAffinity(pLhs));
    idxaff = pIdx->pTable->aCol[pLhs->iColumn].affinity;
    if( aff!=idxaff ) break;








|








2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244

    /* Check that the LHS of the comparison is a column reference to
    ** the right column of the right source table. And that the sort
    ** order of the index column is the same as the sort order of the
    ** leftmost index column.  */
    if( pLhs->op!=TK_COLUMN 
     || pLhs->iTable!=iCur 
     || pLhs->iColumn!=pIdx->aiColumn[i+nEq] 
     || pIdx->aSortOrder[i+nEq]!=pIdx->aSortOrder[nEq]
    ){
      break;
    }

    aff = sqlite3CompareAffinity(pRhs, sqlite3ExprAffinity(pLhs));
    idxaff = pIdx->pTable->aCol[pLhs->iColumn].affinity;
    if( aff!=idxaff ) break;
︙︙
Changes to test/rowvalue4.test.
︙︙
91
92
93
94
95
96
97

98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115



116
117
118
119
120
121
122

  idx3 { CREATE INDEX t2abc ON t2(a, b DESC, c); }
  idx4 { CREATE INDEX t2abc ON t2(a DESC, b DESC, c DESC); }
  idx5 { CREATE INDEX t2abc ON t2(a ASC, b ASC, c ASC); }
  idx6 { CREATE INDEX t2abc ON t2(a DESC, b, c); }
  idx7 { CREATE INDEX t2abc ON t2(a DESC, b DESC) }
  idx8 { CREATE INDEX t2abc ON t2(c, b, a); }
  idx9 { CREATE INDEX t2d ON t2(d); }

} {
  drop_all_indexes
  execsql $idx

  foreach {tn where res} {
    1 "(a, b, c) < (2, 2, 2)"  {1 2 3 4 5 6 7 8 9 10 11 12 13}
    2 "(a, b, c) <= (2, 2, 2)" {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
    3 "(a, b, c) > (2, 2, 2)"  {15 16 17 18 19 20 21 22 23 24 25 26 27}
    4 "(a, b, c) >= (2, 2, 2)" {14 15 16 17 18 19 20 21 22 23 24 25 26 27}
    5 "(a, b, c) >= (2, 2, NULL)" {16 17 18 19 20 21 22 23 24 25 26 27}
    6 "(a, b, c) <= (2, 2, NULL)" {1 2 3 4 5 6 7 8 9 10 11 12}
    7 "(a, b, c) >= (2, NULL, NULL)" {19 20 21 22 23 24 25 26 27}
    8 "(a, b, c) <= (2, NULL, NULL)" {1 2 3 4 5 6 7 8 9}

    9 "(a, b, c) < (SELECT a, b, c FROM t2 WHERE d=14)"  
      {1 2 3 4 5 6 7 8 9 10 11 12 13}

    10 "(a, b, c) = (SELECT a, b, c FROM t2 WHERE d=14)" 14



  } {
    set result [db eval "SELECT d FROM t2 WHERE $where"]
    do_test 2.1.$nm.$tn { lsort -integer $result } $res
  }

  foreach {tn e res} {
    1 "(2, 1) IN (SELECT a, b FROM t2)" 1








>


















>
>
>








91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126

  idx3 { CREATE INDEX t2abc ON t2(a, b DESC, c); }
  idx4 { CREATE INDEX t2abc ON t2(a DESC, b DESC, c DESC); }
  idx5 { CREATE INDEX t2abc ON t2(a ASC, b ASC, c ASC); }
  idx6 { CREATE INDEX t2abc ON t2(a DESC, b, c); }
  idx7 { CREATE INDEX t2abc ON t2(a DESC, b DESC) }
  idx8 { CREATE INDEX t2abc ON t2(c, b, a); }
  idx9 { CREATE INDEX t2d ON t2(d); }
  idx10 { CREATE INDEX t2abc ON t2(a DESC, b, c DESC); }
} {
  drop_all_indexes
  execsql $idx

  foreach {tn where res} {
    1 "(a, b, c) < (2, 2, 2)"  {1 2 3 4 5 6 7 8 9 10 11 12 13}
    2 "(a, b, c) <= (2, 2, 2)" {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
    3 "(a, b, c) > (2, 2, 2)"  {15 16 17 18 19 20 21 22 23 24 25 26 27}
    4 "(a, b, c) >= (2, 2, 2)" {14 15 16 17 18 19 20 21 22 23 24 25 26 27}
    5 "(a, b, c) >= (2, 2, NULL)" {16 17 18 19 20 21 22 23 24 25 26 27}
    6 "(a, b, c) <= (2, 2, NULL)" {1 2 3 4 5 6 7 8 9 10 11 12}
    7 "(a, b, c) >= (2, NULL, NULL)" {19 20 21 22 23 24 25 26 27}
    8 "(a, b, c) <= (2, NULL, NULL)" {1 2 3 4 5 6 7 8 9}

    9 "(a, b, c) < (SELECT a, b, c FROM t2 WHERE d=14)"  
      {1 2 3 4 5 6 7 8 9 10 11 12 13}

    10 "(a, b, c) = (SELECT a, b, c FROM t2 WHERE d=14)" 14

    11 "a = 2 AND (b, c) > (2, 2)" {15 16 17 18}
    12 "a = 2 AND (b, c) < (3, 3) AND (b, c) > (1, 1)" {11 12 13 14 15 16 17}
  } {
    set result [db eval "SELECT d FROM t2 WHERE $where"]
    do_test 2.1.$nm.$tn { lsort -integer $result } $res
  }

  foreach {tn e res} {
    1 "(2, 1) IN (SELECT a, b FROM t2)" 1
︙︙