/ Check-in [1559f4c4]
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 | SQL archive
Timelines: family | ancestors | descendants | both | rowvalue
Files: files | file ages | folders
SHA1: 1559f4c43473e107f7196eea3ee91c53ede22999
User & Date: dan 2016-08-03 16:39:04
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: 3c2f908f user: dan tags: rowvalue
16:39
Fix another problem involving vector range constraints and mixed ASC/DESC indexes. check-in: 1559f4c4 user: dan tags: rowvalue
16:14
Fix stat4-based cost estimates for vector range constraints. check-in: 18af74ab user: dan tags: rowvalue
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  2230   2230       /* Check that the LHS of the comparison is a column reference to
  2231   2231       ** the right column of the right source table. And that the sort
  2232   2232       ** order of the index column is the same as the sort order of the
  2233   2233       ** leftmost index column.  */
  2234   2234       if( pLhs->op!=TK_COLUMN 
  2235   2235        || pLhs->iTable!=iCur 
  2236   2236        || pLhs->iColumn!=pIdx->aiColumn[i+nEq] 
  2237         -     || pIdx->aSortOrder[i]!=pIdx->aSortOrder[0]
         2237  +     || pIdx->aSortOrder[i+nEq]!=pIdx->aSortOrder[nEq]
  2238   2238       ){
  2239   2239         break;
  2240   2240       }
  2241   2241   
  2242   2242       aff = sqlite3CompareAffinity(pRhs, sqlite3ExprAffinity(pLhs));
  2243   2243       idxaff = pIdx->pTable->aCol[pLhs->iColumn].affinity;
  2244   2244       if( aff!=idxaff ) break;

Changes to test/rowvalue4.test.

    91     91     idx3 { CREATE INDEX t2abc ON t2(a, b DESC, c); }
    92     92     idx4 { CREATE INDEX t2abc ON t2(a DESC, b DESC, c DESC); }
    93     93     idx5 { CREATE INDEX t2abc ON t2(a ASC, b ASC, c ASC); }
    94     94     idx6 { CREATE INDEX t2abc ON t2(a DESC, b, c); }
    95     95     idx7 { CREATE INDEX t2abc ON t2(a DESC, b DESC) }
    96     96     idx8 { CREATE INDEX t2abc ON t2(c, b, a); }
    97     97     idx9 { CREATE INDEX t2d ON t2(d); }
           98  +  idx10 { CREATE INDEX t2abc ON t2(a DESC, b, c DESC); }
    98     99   } {
    99    100     drop_all_indexes
   100    101     execsql $idx
   101    102   
   102    103     foreach {tn where res} {
   103    104       1 "(a, b, c) < (2, 2, 2)"  {1 2 3 4 5 6 7 8 9 10 11 12 13}
   104    105       2 "(a, b, c) <= (2, 2, 2)" {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
................................................................................
   109    110       7 "(a, b, c) >= (2, NULL, NULL)" {19 20 21 22 23 24 25 26 27}
   110    111       8 "(a, b, c) <= (2, NULL, NULL)" {1 2 3 4 5 6 7 8 9}
   111    112   
   112    113       9 "(a, b, c) < (SELECT a, b, c FROM t2 WHERE d=14)"  
   113    114         {1 2 3 4 5 6 7 8 9 10 11 12 13}
   114    115   
   115    116       10 "(a, b, c) = (SELECT a, b, c FROM t2 WHERE d=14)" 14
          117  +
          118  +    11 "a = 2 AND (b, c) > (2, 2)" {15 16 17 18}
          119  +    12 "a = 2 AND (b, c) < (3, 3) AND (b, c) > (1, 1)" {11 12 13 14 15 16 17}
   116    120     } {
   117    121       set result [db eval "SELECT d FROM t2 WHERE $where"]
   118    122       do_test 2.1.$nm.$tn { lsort -integer $result } $res
   119    123     }
   120    124   
   121    125     foreach {tn e res} {
   122    126       1 "(2, 1) IN (SELECT a, b FROM t2)" 1