/ Check-in [7e7356f1]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

3795
3796
3797
3798
3799
3800
3801




3802
3803
3804
3805
3806
3807



3808
3809
3810
3811
3812
3813
3814
    int iTab;
    struct InLoop *pIn;
    u8 bRev = (pLevel->plan.wsFlags & WHERE_REVERSE)!=0;

    if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 
      && pLevel->plan.u.pIdx->aSortOrder[iEq]
    ){




      bRev = !bRev;
    }
    assert( pX->op==TK_IN );
    iReg = iTarget;
    eType = sqlite3FindInIndex(pParse, pX, 0);
    if( eType==IN_INDEX_INDEX_DESC ) bRev = !bRev;



    iTab = pX->iTable;
    sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
    assert( pLevel->plan.wsFlags & WHERE_IN_ABLE );
    if( pLevel->u.in.nIn==0 ){
      pLevel->addrNxt = sqlite3VdbeMakeLabel(v);
    }
    pLevel->u.in.nIn++;







>
>
>
>





|
>
>
>







3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
3807
3808
3809
3810
3811
3812
3813
3814
3815
3816
3817
3818
3819
3820
3821
    int iTab;
    struct InLoop *pIn;
    u8 bRev = (pLevel->plan.wsFlags & WHERE_REVERSE)!=0;

    if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 
      && pLevel->plan.u.pIdx->aSortOrder[iEq]
    ){
      testcase( iEq==0 );
      testcase( iEq==pLevel->plan.u.pIdx->nColumn-1 );
      testcase( iEq>0 && iEq+1<pLevel->plan.u.pIdx->nColumn );
      testcase( bRev );
      bRev = !bRev;
    }
    assert( pX->op==TK_IN );
    iReg = iTarget;
    eType = sqlite3FindInIndex(pParse, pX, 0);
    if( eType==IN_INDEX_INDEX_DESC ){
      testcase( bRev );
      bRev = !bRev;
    }
    iTab = pX->iTable;
    sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
    assert( pLevel->plan.wsFlags & WHERE_IN_ABLE );
    if( pLevel->u.in.nIn==0 ){
      pLevel->addrNxt = sqlite3VdbeMakeLabel(v);
    }
    pLevel->u.in.nIn++;

Changes to test/tkt-4dd95f6943.test.

82
83
84
85
86
87
88
89



90








    " {3 2  3 4  3 5}
    do_execsql_test 2.$tn1.6 "
      SELECT x, y FROM t2 WHERE x = 4 AND y IN $inexpr ORDER BY x DESC, y DESC;
    " {4 5  4 4  4 2}
  }
}

finish_test



















|
>
>
>

>
>
>
>
>
>
>
>
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
    " {3 2  3 4  3 5}
    do_execsql_test 2.$tn1.6 "
      SELECT x, y FROM t2 WHERE x = 4 AND y IN $inexpr ORDER BY x DESC, y DESC;
    " {4 5  4 4  4 2}
  }
}

do_execsql_test 3.0 {
  CREATE TABLE t7(x);
  INSERT INTO t7 VALUES (1), (2), (3);
  CREATE INDEX i7 ON t7(x);

  CREATE TABLE t8(y);
  INSERT INTO t8 VALUES (1), (2), (3);

  CREATE UNIQUE INDEX i8 ON t8(y DESC);
  SELECT x FROM t7 WHERE x IN (SELECT y FROM t8) ORDER BY x ASC;
} {1 2 3}

finish_test