/ Check-in [7e7356f1]
Login

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

Overview
Comment:Add a single new test case to the ORDER BY with IN logic verify that if the RHS of the IN is a descending index that it still works. Add testcase() macros to the ORDER BY with IN logic to help verify that corner cases are tested.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | desc-orderby-fix-1
Files: files | file ages | folders
SHA1: 7e7356f1552cd53ea363d1ded3b2c221c9d0be01
User & Date: drh 2013-03-12 23:58:42
Context
2013-03-13
00:13
Fix the ORDER BY with IN constraint logic so that it works with all combinations of DESC on the ORDER BY clause, on the RHS of the IN operator, and in the index used by ORDER BY and IN. Fix for ticket [4dd95f6943fbd18]. check-in: 839aa91f user: drh tags: trunk
2013-03-12
23:58
Add a single new test case to the ORDER BY with IN logic verify that if the RHS of the IN is a descending index that it still works. Add testcase() macros to the ORDER BY with IN logic to help verify that corner cases are tested. Closed-Leaf check-in: 7e7356f1 user: drh tags: desc-orderby-fix-1
22:13
Fix a test case that had an ambiguous result. check-in: 38c6bddf user: drh tags: desc-orderby-fix-1
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3795   3795       int iTab;
  3796   3796       struct InLoop *pIn;
  3797   3797       u8 bRev = (pLevel->plan.wsFlags & WHERE_REVERSE)!=0;
  3798   3798   
  3799   3799       if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 
  3800   3800         && pLevel->plan.u.pIdx->aSortOrder[iEq]
  3801   3801       ){
         3802  +      testcase( iEq==0 );
         3803  +      testcase( iEq==pLevel->plan.u.pIdx->nColumn-1 );
         3804  +      testcase( iEq>0 && iEq+1<pLevel->plan.u.pIdx->nColumn );
         3805  +      testcase( bRev );
  3802   3806         bRev = !bRev;
  3803   3807       }
  3804   3808       assert( pX->op==TK_IN );
  3805   3809       iReg = iTarget;
  3806   3810       eType = sqlite3FindInIndex(pParse, pX, 0);
  3807         -    if( eType==IN_INDEX_INDEX_DESC ) bRev = !bRev;
         3811  +    if( eType==IN_INDEX_INDEX_DESC ){
         3812  +      testcase( bRev );
         3813  +      bRev = !bRev;
         3814  +    }
  3808   3815       iTab = pX->iTable;
  3809   3816       sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
  3810   3817       assert( pLevel->plan.wsFlags & WHERE_IN_ABLE );
  3811   3818       if( pLevel->u.in.nIn==0 ){
  3812   3819         pLevel->addrNxt = sqlite3VdbeMakeLabel(v);
  3813   3820       }
  3814   3821       pLevel->u.in.nIn++;

Changes to test/tkt-4dd95f6943.test.

    82     82       " {3 2  3 4  3 5}
    83     83       do_execsql_test 2.$tn1.6 "
    84     84         SELECT x, y FROM t2 WHERE x = 4 AND y IN $inexpr ORDER BY x DESC, y DESC;
    85     85       " {4 5  4 4  4 2}
    86     86     }
    87     87   }
    88     88   
    89         -finish_test
           89  +do_execsql_test 3.0 {
           90  +  CREATE TABLE t7(x);
           91  +  INSERT INTO t7 VALUES (1), (2), (3);
           92  +  CREATE INDEX i7 ON t7(x);
           93  +
           94  +  CREATE TABLE t8(y);
           95  +  INSERT INTO t8 VALUES (1), (2), (3);
           96  +
           97  +  CREATE UNIQUE INDEX i8 ON t8(y DESC);
           98  +  SELECT x FROM t7 WHERE x IN (SELECT y FROM t8) ORDER BY x ASC;
           99  +} {1 2 3}
    90    100   
          101  +finish_test