SQLite

  int nTerm;                    /* Number of ORDER BY terms */
  struct ExprList_item *pTerm;  /* A term of the ORDER BY clause */
  ExprList *pOrderBy;           /* The ORDER BY clause */
  Parse *pParse = p->pParse;    /* Parser context */
  sqlite3 *db = pParse->db;     /* Database connection */
  int nPriorSat;                /* ORDER BY terms satisfied by outer loops */
  int seenRowid = 0;            /* True if an ORDER BY rowid term is seen */
  int nEqOneRow;                /* Idx columns that ref unique values */

  if( OptimizationDisabled(db, SQLITE_OrderByIdx) ) return 0;
  if( p->i==0 ){
    nPriorSat = 0;
    nEqOneRow = nEqCol;
  }else{
    if( OptimizationDisabled(db, SQLITE_OrderByIdxJoin) ) return 0;
    nPriorSat = p->aLevel[p->i-1].plan.nOBSat;
    sortOrder = bOuterRev;
    nEqOneRow = 0;
  }
  if( p->i>0 && nEqCol==0 /*&& !allOuterLoopsUnique(p)*/ ) return nPriorSat;
  pOrderBy = p->pOrderBy;
  if( !pOrderBy ) return nPriorSat;
  if( wsFlags & WHERE_COLUMN_IN ) return nPriorSat;
  if( pIdx->bUnordered ) return nPriorSat;
  nTerm = pOrderBy->nExpr;

        return nPriorSat;
      }
    }
    assert( pIdx->aSortOrder!=0 || iColumn==-1 );
    assert( pTerm->sortOrder==0 || pTerm->sortOrder==1 );
    assert( iSortOrder==0 || iSortOrder==1 );
    termSortOrder = iSortOrder ^ pTerm->sortOrder;
    if( i>nEqOneRow ){
      if( termSortOrder!=sortOrder ){
        /* Indices can only be used if all ORDER BY terms past the
        ** equality constraints are all either DESC or ASC. */
        break;
      }
    }else{
      sortOrder = termSortOrder;
    }
    j++;
    pTerm++;
    if( iColumn<0 ){
      seenRowid = 1;
      break;
    }
  }
  *pbRev = sortOrder;

  /* If there was an "ORDER BY rowid" term that matched, or it is only
  ** possible for a single row from this table to match, then skip over
  ** any additional ORDER BY terms dealing with this table.
  */
  if( seenRowid ||
     (   (wsFlags & WHERE_COLUMN_NULL)==0

    ** on one page and hence more pages have to be fetched.
    **
    ** The ANALYZE command and the sqlite_stat1 and sqlite_stat3 tables do
    ** not give us data on the relative sizes of table and index records.
    ** So this computation assumes table records are about twice as big
    ** as index records
    */
    if( (wsFlags&~WHERE_REVERSE)==WHERE_IDX_ONLY
     && (pWC->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
     && sqlite3GlobalConfig.bUseCis
     && OptimizationEnabled(pParse->db, SQLITE_CoverIdxScan)
    ){
      /* This index is not useful for indexing, but it is a covering index.
      ** A full-scan of the index might be a little faster than a full-scan
      ** of the table, so give this case a cost slightly less than a table

        }
      }
      if( nRow<2 ) nRow = 2;
    }


    WHERETRACE((
      "%s(%s):\n"
      "    nEq=%d nInMul=%d rangeDiv=%d bSort=%d bLookup=%d wsFlags=0x%08x\n"
      "    notReady=0x%llx log10N=%.1f nRow=%.1f cost=%.1f\n"
      "    used=0x%llx nOrdered=%d nOBSat=%d\n",
      pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk"), 
      nEq, nInMul, (int)rangeDiv, bSort, bLookup, wsFlags,
      p->notReady, log10N, nRow, cost, used, nOrdered, nOBSat
    ));

    /* If this index is the best we have seen so far, then record this
    ** index and its cost in the pCost structure.

          bestJ = j;
        }
        if( doNotReorder ) break;
      }
    }
    assert( bestJ>=0 );
    assert( sWBI.notValid & getMask(pMaskSet, pTabList->a[bestJ].iCursor) );
    WHERETRACE(("*** Optimizer selects table %d for loop %d with:\n"
                "    cost=%.1f, nRow=%.1f, nOBSat=%d wsFlags=0x%08x\n",
                bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow,
                bestPlan.plan.nOBSat, bestPlan.plan.wsFlags));
    if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){
      pWInfo->nOBSat = pOrderBy->nExpr;
    }
    if( (bestPlan.plan.wsFlags & WHERE_DISTINCT)!=0 ){
      assert( pWInfo->eDistinct==0 );
      pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
    }

#   These tests also show that the following is not untrue:
#
# EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do
# not have to be expressions that appear in the result.
#
do_select_tests e_select-4.9 {
  1  "SELECT group_concat(one), two FROM b1 GROUP BY two" {
    /#,# f   1 o   #,#   s #,# t/
  }
  2  "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
    1,2,3,4 10    5,6,7 18
  }
  3  "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
    4  1,5    2,6   3,7
  }
  4  "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" {
    4,3,5,7,6    1,2
  }
}

# EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL
# values are considered equal.
#
do_select_tests e_select-4.10 {
  1  "SELECT group_concat(y) FROM b2 GROUP BY x" {/#,#   3   #,#/}
  2  "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1}
} 

# EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation
# sequence with which to compare text values apply when evaluating
# expressions in a GROUP BY clause.
#

     1 2 3    1 2 -20    1 4  93    1 5 -1   
  }
  7  "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3" {
     2 4 93   2 5 -1     1 2 -20    1 2 3    
     1 2 7    1 2 8      1 4  93    1 5 -1   
  }
  8  "SELECT z, x FROM d1 ORDER BY 2" {
     /# 1    # 1    # 1   # 1 
      # 1    # 1    # 2   # 2/
  }
  9  "SELECT z, x FROM d1 ORDER BY 1" {
     /-20 1  -1 #   -1 #   3 1
     7 1     8 1   93 #   93 #/   
  }
}

# EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier
# that corresponds to the alias of one of the output columns, then the
# expression is considered an alias for that column.
#
do_select_tests e_select-8.5 {
  1   "SELECT z+1 AS abc FROM d1 ORDER BY abc" {
    -19 0 0 4 8 9 94 94
  }
  2   "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" {
    94 94 9 8 4 0 0 -19
  }
  3  "SELECT z AS x, x AS z FROM d1 ORDER BY z" {
    /# 1    # 1    # 1    # 1    # 1    # 1    # 2    # 2/
  }
  4  "SELECT z AS x, x AS z FROM d1 ORDER BY x" {
    /-20 1    -1 #    -1 #    3 1    7 1    8 1    93 #    93 #/
  }
}

# EVIDENCE-OF: R-65068-27207 Otherwise, if the ORDER BY expression is
# any other expression, it is evaluated and the returned value used to
# order the output rows.
#

  }
} {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
do_test 1.4c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {/ORDER BY/}  ;# separate sorting pass due to mixed DESC/ASC


do_test 1.5a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}
do_test 1.5b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}  ;# verify same order after sorting
do_test 1.5c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {/ORDER BY/}  ;# separate sorting pass due to mixed DESC/ASC

do_test 1.6a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}
do_test 1.6b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
do_test 1.6c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {~/ORDER BY/}  ;# ORDER BY optimized-out


# Reconstruct the test data to use indices rather than integer primary keys.
#
do_test 2.0 {
  db eval {
    BEGIN;
    DROP TABLE album;
    DROP TABLE track;
    CREATE TABLE album(
      aid INT PRIMARY KEY,
      title TEXT NOT NULL
    );
    CREATE INDEX album_i1 ON album(title, aid);
    CREATE TABLE track(
      aid INTEGER NOT NULL REFERENCES album,
      tn INTEGER NOT NULL,
      name TEXT,
      UNIQUE(aid, tn)
    );
    INSERT INTO album VALUES(1, '1-one'), (2, '2-two'), (3, '3-three');
    INSERT INTO track VALUES
        (1, 1, 'one-a'),
        (2, 2, 'two-b'),
        (3, 3, 'three-c'),
        (1, 3, 'one-c'),
        (2, 1, 'two-a'),
        (3, 1, 'three-a');
    COMMIT;
  }
} {}
do_test 2.1a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {one-a one-c two-a two-b three-a three-c}

# Verify that the ORDER BY clause is optimized out
#
do_test 2.1b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {~/ORDER BY/}  ;# ORDER BY optimized out

# The same query with ORDER BY clause optimization disabled via + operators
# should give exactly the same answer.
#
do_test 2.2a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
  }
} {one-a one-c two-a two-b three-a three-c}

# The output is sorted manually in this case.
#
do_test 2.2b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
  }
} {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms

# The same query with ORDER BY optimizations turned off via built-in test.
#
do_test 2.3a {
  optimization_control db order-by-idx-join 0
  db cache flush
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {one-a one-c two-a two-b three-a three-c}
do_test 2.3b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
optimization_control db all 1
db cache flush

# Reverse order sorts
#
do_test 2.4a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {three-a three-c two-a two-b one-a one-c}
do_test 2.4b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn
  }
} {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
do_test 2.4c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {/ORDER BY/}  ;# separate sorting pass due to mixed DESC/ASC


do_test 2.5a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}
do_test 2.5b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}  ;# verify same order after sorting
do_test 2.5c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {/ORDER BY/}  ;# separate sorting pass due to mixed ASC/DESC

do_test 2.6a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}
do_test 2.6b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
do_test 2.6c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {~/ORDER BY/}  ;# ORDER BY optimized-out


# Generate another test dataset, but this time using mixed ASC/DESC indices.
#
do_test 3.0 {
  db eval {
    BEGIN;
    DROP TABLE album;
    DROP TABLE track;
    CREATE TABLE album(
      aid INTEGER PRIMARY KEY,
      title TEXT UNIQUE NOT NULL
    );
    CREATE TABLE track(
      tid INTEGER PRIMARY KEY,
      aid INTEGER NOT NULL REFERENCES album,
      tn INTEGER NOT NULL,
      name TEXT,
      UNIQUE(aid ASC, tn DESC)
    );
    INSERT INTO album VALUES(1, '1-one'), (2, '2-two'), (3, '3-three');
    INSERT INTO track VALUES
        (NULL, 1, 1, 'one-a'),
        (NULL, 2, 2, 'two-b'),
        (NULL, 3, 3, 'three-c'),
        (NULL, 1, 3, 'one-c'),
        (NULL, 2, 1, 'two-a'),
        (NULL, 3, 1, 'three-a');
    COMMIT;
  }
} {}
do_test 3.1a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}

# Verify that the ORDER BY clause is optimized out
#
do_test 3.1b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {~/ORDER BY/}  ;# ORDER BY optimized out

# The same query with ORDER BY clause optimization disabled via + operators
# should give exactly the same answer.
#
do_test 3.2a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}

# The output is sorted manually in this case.
#
do_test 3.2b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
  }
} {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms

# The same query with ORDER BY optimizations turned off via built-in test.
#
do_test 3.3a {
  optimization_control db order-by-idx-join 0
  db cache flush
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {one-c one-a two-b two-a three-c three-a}
do_test 3.3b {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
  }
} {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
optimization_control db all 1
db cache flush

# Without the mixed ASC/DESC on ORDER BY
#
do_test 3.4a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {one-a one-c two-a two-b three-a three-c}
do_test 3.4b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
  }
} {one-a one-c two-a two-b three-a three-c}  ;# verify same order after sorting
do_test 3.4c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
  }
} {/ORDER BY/}  ;# separate sorting pass due to mismatched DESC/ASC


do_test 3.5a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}
do_test 3.5b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
  }
} {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
do_test 3.5c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
  }
} {/ORDER BY/}  ;# separate sorting pass due to mismatched ASC/DESC


do_test 3.6a {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {three-a three-c two-a two-b one-a one-c}
do_test 3.6b {
  db eval {
    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn
  }
} {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
do_test 3.6c {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
  }
} {~/ORDER BY/}  ;# inverted ASC/DESC is optimized out


finish_test

  if {![info exists ::G(match)] || [string match $::G(match) $name]} {
    if {[catch {uplevel #0 "$cmd;\n"} result]} {
      puts "\nError: $result"
      fail_test $name
    } else {
      if {[regexp {^~?/.*/$} $expected]} {
        if {[string index $expected 0]=="~"} {
          set re [string map {# {[-0-9.]+}} [string range $expected 2 end-1]]
          set ok [expr {![regexp $re $result]}]
        } else {
          set re [string map {# {[-0-9.]+}} [string range $expected 1 end-1]]
          set ok [regexp $re $result]
        }
      } else {
        set ok [expr {[string compare $result $expected]==0}]
      }
      if {!$ok} {
        # if {![info exists ::testprefix] || $::testprefix eq ""} {

do_test tkt-cbd05-1.3 {
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat3 
    WHERE idx = 't1_x' 
    GROUP BY tbl,idx
  }
} {/t1 t1_x .[ ABCDEFGHI]{10}./}

do_test tkt-cbd05-2.1 {
  db eval {
    DROP TABLE t1;
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB UNIQUE NOT NULL);
    CREATE INDEX t1_x ON t1(b);
    INSERT INTO t1 VALUES(NULL, X'');

do_test tkt-cbd05-2.3 {
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat3 
    WHERE idx = 't1_x' 
    GROUP BY tbl,idx
  }
} {/t1 t1_x .[ ABCDEFGHI]{10}./}

finish_test

      SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 102}
  do_test where-5.3 {
    count {
      SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 13}
  do_test where-5.4 {
    count {
      SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 102}
  do_test where-5.5 {
    count {

  } 
} {1/4 1/1 4/4 4/1 nosort}
do_test where-14.5 {
  # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
  } 
} {/4/[14] 4/[14] 1/[14] 1/[14] sort/}
do_test where-14.6 {
  # This test case changed from "nosort" to "sort". See ticket 2a5629202f.
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
  } 
} {/4/[14] 4/[14] 1/[14] 1/[14] sort/}
do_test where-14.7 {
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b
  } 
} {4/1 4/4 1/1 1/4 sort}
do_test where-14.7.1 {
  cksort {