SQLite

  if( pDest->nCol!=pSrc->nCol ){
    return 0;   /* Number of columns must be the same in tab1 and tab2 */
  }
  if( pDest->iPKey!=pSrc->iPKey ){
    return 0;   /* Both tables must have the same INTEGER PRIMARY KEY */
  }
  for(i=0; i<pDest->nCol; i++){
    Column *pDestCol = &pDest->aCol[i];
    Column *pSrcCol = &pSrc->aCol[i];
    if( pDestCol->affinity!=pSrcCol->affinity ){
      return 0;    /* Affinity must be the same on all columns */
    }
    if( !xferCompatibleCollation(pDestCol->zColl, pSrcCol->zColl) ){
      return 0;    /* Collating sequence must be the same on all columns */
    }
    if( pDestCol->notNull && !pSrcCol->notNull ){
      return 0;    /* tab2 must be NOT NULL if tab1 is */
    }
    /* Default values for second and subsequent columns need to match. */
    if( i>0
     && ((pDestCol->zDflt==0)!=(pSrcCol->zDflt==0) 
         || (pDestCol->zDflt && strcmp(pDestCol->zDflt, pSrcCol->zDflt)!=0))
    ){
      return 0;    /* Default values must be the same for all columns */
    }
  }
  for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
    if( pDestIdx->onError!=OE_None ){
      destHasUniqueIdx = 1;
    }
    for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){

  if( p->pPrior ){
    rc = multiSelect(pParse, p, pDest);
    explainSetInteger(pParse->iSelectId, iRestoreSelectId);
    return rc;
  }
#endif













  /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and 
  ** if the select-list is the same as the ORDER BY list, then this query
  ** can be rewritten as a GROUP BY. In other words, this:
  **
  **     SELECT DISTINCT xyz FROM ... ORDER BY xyz
  **
  ** is transformed to:

                        ** one row of the input to the aggregator has been
                        ** processed */
    int iAbortFlag;     /* Mem address which causes query abort if positive */
    int groupBySort;    /* Rows come from source in GROUP BY order */
    int addrEnd;        /* End of processing for this SELECT */
    int sortPTab = 0;   /* Pseudotable used to decode sorting results */
    int sortOut = 0;    /* Output register from the sorter */
    int orderByGrp = 0; /* True if the GROUP BY and ORDER BY are the same */

    /* Remove any and all aliases between the result set and the
    ** GROUP BY clause.
    */
    if( pGroupBy ){
      int k;                        /* Loop counter */
      struct ExprList_item *pItem;  /* For looping over expression in a list */

      for(k=p->pEList->nExpr, pItem=p->pEList->a; k>0; k--, pItem++){
        pItem->u.x.iAlias = 0;
      }
      for(k=pGroupBy->nExpr, pItem=pGroupBy->a; k>0; k--, pItem++){
        pItem->u.x.iAlias = 0;
      }
      if( p->nSelectRow>100 ) p->nSelectRow = 100;
    }else{
      p->nSelectRow = 1;
    }


    /* If there is both a GROUP BY and an ORDER BY clause and they are
    ** identical, then it may be possible to disable the ORDER BY clause 
    ** on the grounds that the GROUP BY will cause elements to come out 
    ** in the correct order. It also may not - the GROUP BY may use a
    ** database index that causes rows to be grouped together as required
    ** but not actually sorted. Either way, record the fact that the
    ** ORDER BY and GROUP BY clauses are the same by setting the orderByGrp
    ** variable.  */
    if( sqlite3ExprListCompare(pGroupBy, sSort.pOrderBy, -1)==0 ){
      orderByGrp = 1;
    }
 
    /* Create a label to jump to when we want to abort the query */
    addrEnd = sqlite3VdbeMakeLabel(v);

    /* Convert TK_COLUMN nodes into TK_AGG_COLUMN and make entries in
    ** sAggInfo for all TK_AGG_FUNCTION nodes in expressions of the
    ** SELECT statement.

      /* Begin a loop that will extract all source rows in GROUP BY order.
      ** This might involve two separate loops with an OP_Sort in between, or
      ** it might be a single loop that uses an index to extract information
      ** in the right order to begin with.
      */
      sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset);
      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pGroupBy, 0,
          WHERE_GROUPBY | (orderByGrp ? WHERE_SORTBYGROUP : 0), 0
      );
      if( pWInfo==0 ) goto select_end;
      if( sqlite3WhereIsOrdered(pWInfo)==pGroupBy->nExpr ){
        /* The optimizer is able to deliver rows in group by order so
        ** we do not have to sort.  The OP_OpenEphemeral table will be
        ** cancelled later because we still need to use the pKeyInfo
        */
        groupBySort = 0;

        sAggInfo.sortingIdxPTab = sortPTab = pParse->nTab++;
        sortOut = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp3(v, OP_OpenPseudo, sortPTab, sortOut, nCol);
        sqlite3VdbeAddOp2(v, OP_SorterSort, sAggInfo.sortingIdx, addrEnd);
        VdbeComment((v, "GROUP BY sort")); VdbeCoverage(v);
        sAggInfo.useSortingIdx = 1;
        sqlite3ExprCacheClear(pParse);

      }

      /* If the index or temporary table used by the GROUP BY sort
      ** will naturally deliver rows in the order required by the ORDER BY
      ** clause, cancel the ephemeral table open coded earlier.
      **
      ** This is an optimization - the correct answer should result regardless.
      ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER to 
      ** disable this optimization for testing purposes.  */
      if( orderByGrp && OptimizationEnabled(db, SQLITE_GroupByOrder) 
       && (groupBySort || sqlite3WhereIsSorted(pWInfo))
      ){
        sSort.pOrderBy = 0;
        sqlite3VdbeChangeToNoop(v, sSort.addrSortIndex);
      }

      /* Evaluate the current GROUP BY terms and store in b0, b1, b2...
      ** (b0 is memory location iBMem+0, b1 is iBMem+1, and so forth)
      ** Then compare the current GROUP BY terms against the GROUP BY terms
      ** from the previous row currently stored in a0, a1, a2...
      */

#define WHERE_OMIT_OPEN_CLOSE  0x0010 /* Table cursors are already open */
#define WHERE_FORCE_TABLE      0x0020 /* Do not use an index-only search */
#define WHERE_ONETABLE_ONLY    0x0040 /* Only code the 1st table in pTabList */
#define WHERE_AND_ONLY         0x0080 /* Don't use indices for OR terms */
#define WHERE_GROUPBY          0x0100 /* pOrderBy is really a GROUP BY */
#define WHERE_DISTINCTBY       0x0200 /* pOrderby is really a DISTINCT clause */
#define WHERE_WANT_DISTINCT    0x0400 /* All output needs to be distinct */
#define WHERE_SORTBYGROUP      0x0800 /* Support sqlite3WhereIsSorted() */

/* Allowed return values from sqlite3WhereIsDistinct()
*/
#define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */
#define WHERE_DISTINCT_UNIQUE    1  /* No duplicates */
#define WHERE_DISTINCT_ORDERED   2  /* All duplicates are adjacent */
#define WHERE_DISTINCT_UNORDERED 3  /* Duplicates are scattered */

void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqlite3WhereBegin(Parse*,SrcList*,Expr*,ExprList*,ExprList*,u16,int);
void sqlite3WhereEnd(WhereInfo*);
u64 sqlite3WhereOutputRowCount(WhereInfo*);
int sqlite3WhereIsDistinct(WhereInfo*);
int sqlite3WhereIsOrdered(WhereInfo*);
int sqlite3WhereIsSorted(WhereInfo*);
int sqlite3WhereContinueLabel(WhereInfo*);
int sqlite3WhereBreakLabel(WhereInfo*);
int sqlite3WhereOkOnePass(WhereInfo*, int*);
int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, u8);
void sqlite3ExprCodeGetColumnOfTable(Vdbe*, Table*, int, int, int);
void sqlite3ExprCodeMove(Parse*, int, int, int);
void sqlite3ExprCacheStore(Parse*, int, int, int);

** readability.  From this point on down, the normal indentation rules are
** restored.
*****************************************************************************/
    }

#ifdef VDBE_PROFILE
    {
      u64 endTime = sqlite3Hwtime();
      if( endTime>start ) pOp->cycles += endTime - start;
      pOp->cnt++;
    }
#endif

    /* The following code adds nothing to the actual functionality
    ** of the program.  It is only here for testing and debugging.
    ** On the other hand, it does burn CPU cycles every time through

      whereLoopDelete(db, p);
    }
    sqlite3DbFree(db, pWInfo);
  }
}

/*
** Return TRUE if both of the following are true:
**
**   (1)  X has the same or lower cost that Y
**   (2)  X is a proper subset of Y
**
** By "proper subset" we mean that X uses fewer WHERE clause terms
** than Y and that every WHERE clause term used by X is also used
** by Y.
**
** If X is a proper subset of Y then Y is a better choice and ought
** to have a lower cost.  This routine returns TRUE when that cost 
** relationship is inverted and needs to be adjusted.
*/
static int whereLoopCheaperProperSubset(
  const WhereLoop *pX,       /* First WhereLoop to compare */
  const WhereLoop *pY        /* Compare against this WhereLoop */
){
  int i, j;
  if( pX->nLTerm >= pY->nLTerm ) return 0; /* X is not a subset of Y */
  if( pX->rRun >= pY->rRun ){
    if( pX->rRun > pY->rRun ) return 0;    /* X costs more than Y */
    if( pX->nOut > pY->nOut ) return 0;    /* X costs more than Y */
  }
  for(j=0, i=pX->nLTerm-1; i>=0; i--){
    for(j=pY->nLTerm-1; j>=0; j--){
      if( pY->aLTerm[j]==pX->aLTerm[i] ) break;
    }
    if( j<0 ) return 0;  /* X not a subset of Y since term X[i] not used by Y */
  }
  return 1;  /* All conditions meet */
}

/*
** Try to adjust the cost of WhereLoop pTemplate upwards or downwards so
** that:
**
**   (1) pTemplate costs less than any other WhereLoops that are a proper

** also used by Y.
*/
static void whereLoopAdjustCost(const WhereLoop *p, WhereLoop *pTemplate){
  if( (pTemplate->wsFlags & WHERE_INDEXED)==0 ) return;
  for(; p; p=p->pNextLoop){
    if( p->iTab!=pTemplate->iTab ) continue;
    if( (p->wsFlags & WHERE_INDEXED)==0 ) continue;



    if( whereLoopCheaperProperSubset(p, pTemplate) ){

      /* Adjust pTemplate cost downward so that it is cheaper than its 
      ** subset p */
      pTemplate->rRun = p->rRun;
      pTemplate->nOut = p->nOut - 1;
    }else if( whereLoopCheaperProperSubset(pTemplate, p) ){
      /* Adjust pTemplate cost upward so that it is costlier than p since

      ** pTemplate is a proper subset of p */


      pTemplate->rRun = p->rRun;
      pTemplate->nOut = p->nOut + 1;
    }
  }
}

/*

**   N>0:   N terms of the ORDER BY clause are satisfied
**   N==0:  No terms of the ORDER BY clause are satisfied
**   N<0:   Unknown yet how many terms of ORDER BY might be satisfied.   
**
** Note that processing for WHERE_GROUPBY and WHERE_DISTINCTBY is not as
** strict.  With GROUP BY and DISTINCT the only requirement is that
** equivalent rows appear immediately adjacent to one another.  GROUP BY
** and DISTINCT do not require rows to appear in any particular order as long
** as equivelent rows are grouped together.  Thus for GROUP BY and DISTINCT
** the pOrderBy terms can be matched in any order.  With ORDER BY, the 
** pOrderBy terms must be matched in strict left-to-right order.
*/
static i8 wherePathSatisfiesOrderBy(
  WhereInfo *pWInfo,    /* The WHERE clause */
  ExprList *pOrderBy,   /* ORDER BY or GROUP BY or DISTINCT clause to check */

  **
  ** The rowid for a table is always UNIQUE and NOT NULL so whenever the
  ** rowid appears in the ORDER BY clause, the corresponding WhereLoop is
  ** automatically order-distinct.
  */

  assert( pOrderBy!=0 );








  if( nLoop && OptimizationDisabled(db, SQLITE_OrderByIdxJoin) ) return 0;

  nOrderBy = pOrderBy->nExpr;
  testcase( nOrderBy==BMS-1 );
  if( nOrderBy>BMS-1 ) return 0;  /* Cannot optimize overly large ORDER BYs */
  isOrderDistinct = 1;
  obDone = MASKBIT(nOrderBy)-1;
  orderDistinctMask = 0;
  ready = 0;
  for(iLoop=0; isOrderDistinct && obSat<obDone && iLoop<=nLoop; iLoop++){
    if( iLoop>0 ) ready |= pLoop->maskSelf;
    pLoop = iLoop<nLoop ? pPath->aLoop[iLoop] : pLast;
    if( pLoop->wsFlags & WHERE_VIRTUALTABLE ){
      if( pLoop->u.vtab.isOrdered ) obSat = obDone;
      break;
    }
    iCur = pWInfo->pTabList->a[pLoop->iTab].iCursor;

    /* Mark off any ORDER BY term X that is a column in the table of
    ** the current loop for which there is term in the WHERE
    ** clause of the form X IS NULL or X=? that reference only outer
    ** loops.
    */

         && j>=pLoop->u.btree.nEq
         && pIndex->pTable->aCol[iColumn].notNull==0
        ){
          isOrderDistinct = 0;
        }

        /* Find the ORDER BY term that corresponds to the j-th column
        ** of the index and mark that ORDER BY term off 
        */
        bOnce = 1;
        isMatch = 0;
        for(i=0; bOnce && i<nOrderBy; i++){
          if( MASKBIT(i) & obSat ) continue;
          pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
          testcase( wctrlFlags & WHERE_GROUPBY );

      if( (obSat&m)==m ) return i;
    }
    return 0;
  }
  return -1;
}


/*
** If the WHERE_GROUPBY flag is set in the mask passed to sqlite3WhereBegin(),
** the planner assumes that the specified pOrderBy list is actually a GROUP
** BY clause - and so any order that groups rows as required satisfies the
** request.
**
** Normally, in this case it is not possible for the caller to determine
** whether or not the rows are really being delivered in sorted order, or
** just in some other order that provides the required grouping. However,
** if the WHERE_SORTBYGROUP flag is also passed to sqlite3WhereBegin(), then
** this function may be called on the returned WhereInfo object. It returns
** true if the rows really will be sorted in the specified order, or false
** otherwise.
**
** For example, assuming:
**
**   CREATE INDEX i1 ON t1(x, Y);
**
** then
**
**   SELECT * FROM t1 GROUP BY x,y ORDER BY x,y;   -- IsSorted()==1
**   SELECT * FROM t1 GROUP BY y,x ORDER BY y,x;   -- IsSorted()==0
*/
int sqlite3WhereIsSorted(WhereInfo *pWInfo){
  assert( pWInfo->wctrlFlags & WHERE_GROUPBY );
  assert( pWInfo->wctrlFlags & WHERE_SORTBYGROUP );
  return pWInfo->sorted;
}

#ifdef WHERETRACE_ENABLED
/* For debugging use only: */
static const char *wherePathName(WherePath *pPath, int nLoop, WhereLoop *pLast){
  static char zName[65];
  int i;
  for(i=0; i<nLoop; i++){ zName[i] = pPath->aLoop[i]->cId; }
  if( pLast ) zName[i++] = pLast->cId;
  zName[i] = 0;
  return zName;
}
#endif


/*
** Given the list of WhereLoop objects at pWInfo->pLoops, this routine
** attempts to find the lowest cost path that visits each WhereLoop
** once.  This path is then loaded into the pWInfo->a[].pWLoop fields.
**
** Assume that the total number of output rows that will need to be sorted

        pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
      }
    }else{
      pWInfo->nOBSat = pFrom->isOrdered;
      if( pWInfo->nOBSat<0 ) pWInfo->nOBSat = 0;
      pWInfo->revMask = pFrom->revLoop;
    }
    if( (pWInfo->wctrlFlags & WHERE_SORTBYGROUP)
        && pWInfo->nOBSat==pWInfo->pOrderBy->nExpr
    ){
      Bitmask notUsed = 0;
      int nOrder = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, 
          pFrom, 0, nLoop-1, pFrom->aLoop[nLoop-1], &notUsed
      );
      assert( pWInfo->sorted==0 );
      pWInfo->sorted = (nOrder==pWInfo->pOrderBy->nExpr);
    }
  }


  pWInfo->nRowOut = pFrom->nRow;

  /* Free temporary memory and return success */
  sqlite3DbFree(db, pSpace);
  return SQLITE_OK;
}

  ExprList *pOrderBy;       /* The ORDER BY clause or NULL */
  ExprList *pResultSet;     /* Result set. DISTINCT operates on these */
  WhereLoop *pLoops;        /* List of all WhereLoop objects */
  Bitmask revMask;          /* Mask of ORDER BY terms that need reversing */
  LogEst nRowOut;           /* Estimated number of output rows */
  u16 wctrlFlags;           /* Flags originally passed to sqlite3WhereBegin() */
  i8 nOBSat;                /* Number of ORDER BY terms satisfied by indices */
  u8 sorted;                /* True if really sorted (not just grouped) */
  u8 okOnePass;             /* Ok to use one-pass algorithm for UPDATE/DELETE */
  u8 untestedTerms;         /* Not all WHERE terms resolved by outer loop */
  u8 eDistinct;             /* One of the WHERE_DISTINCT_* values below */
  u8 nLevel;                /* Number of nested loop */
  int iTop;                 /* The very beginning of the WHERE loop */
  int iContinue;            /* Jump here to continue with next record */
  int iBreak;               /* Jump here to break out of the loop */

    h DEFAULT ( substr('abcd', 0, 2) || 'cd' ),
    i DEFAULT CURRENT_TIME,
    j DEFAULT CURRENT_DATE,
    k DEFAULT CURRENT_TIMESTAMP
  );
} {}

# EVIDENCE-OF: R-36381-62919 For the purposes of the DEFAULT clause, an
# expression is considered constant provided that it does not contain
# any sub-queries, column or table references, or string literals
# enclosed in double-quotes instead of single-quotes.
#
do_createtable_tests 3.4.1 -error {
  default value of column [x] is not constant
} {
  1   {CREATE TABLE t5(x DEFAULT ( (SELECT 1) ))}  {}
  2   {CREATE TABLE t5(x DEFAULT ( "abc" ))}  {}
  3   {CREATE TABLE t5(x DEFAULT ( 1 IN (SELECT 1) ))}  {}

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-07280-60510 Assuming the library is compiled with
# foreign key constraints enabled, it must still be enabled by the
# application at runtime, using the PRAGMA foreign_keys command.
#
# This also tests that foreign key constraints are disabled by default.
#
# EVIDENCE-OF: R-44261-39702 Foreign key constraints are disabled by
# default (for backwards compatibility), so must be enabled separately
# for each database connection.
#
drop_all_tables
do_test e_fkey-4.1 {
  execsql {
    CREATE TABLE p(i PRIMARY KEY);
    CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
    INSERT INTO p VALUES('hello');

    INSERT INTO c VALUES('hello');
    UPDATE p SET i = 'world';
    SELECT * FROM c;
  } 
} {world}

#-------------------------------------------------------------------------
# EVIDENCE-OF: R-08013-37737 The application can also use a PRAGMA
# foreign_keys statement to determine if foreign keys are currently
# enabled.

#
# This also tests the example code in section 2 of foreignkeys.in.
#
# EVIDENCE-OF: R-11255-19907
# 
reset_db
do_test e_fkey-5.1 {

  } {5}
}

#-------------------------------------------------------------------------
# The setting of the recursive_triggers pragma does not affect foreign
# key actions.
#
# EVIDENCE-OF: R-44355-00270 The PRAGMA recursive_triggers setting does
# not affect the operation of foreign key actions.
#
foreach recursive_triggers_setting [list 0 1 ON OFF] {
  drop_all_tables
  execsql "PRAGMA recursive_triggers = $recursive_triggers_setting"

  do_test e_fkey-64.$recursive_triggers_setting.1 {
    execsql {

# 2014-04-25
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing ORDER BY optimizations on joins
# that involve virtual tables.
#


set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix orderby7

ifcapable !fts3 {
  finish_test
  return
}

do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE fts USING fts3(content TEXT);
  INSERT INTO fts(rowid,content)
     VALUES(1,'this is a test of the fts3 virtual'),
           (2,'table used as part of a join together'),
           (3,'with the DISTINCT keyword.  There was'),
           (4,'a bug at one time (2013-06 through 2014-04)'),
           (5,'that prevented this from working correctly.'),
           (11,'a row that occurs twice'),
           (12,'a row that occurs twice');
 
  CREATE TABLE t1(x TEXT PRIMARY KEY, y);
  INSERT OR IGNORE INTO t1 SELECT content, rowid+100 FROM fts;
} {}
do_execsql_test 1.1 {
  SELECT DISTINCT fts.rowid, t1.y
    FROM fts, t1
   WHERE fts MATCH 'that twice'
     AND content=x
   ORDER BY y;
} {11 111 12 111}
do_execsql_test 1.2 {
  SELECT DISTINCT fts.rowid, t1.x
    FROM fts, t1
   WHERE fts MATCH 'that twice'
     AND content=x
   ORDER BY 1;
} {11 {a row that occurs twice} 12 {a row that occurs twice}}
do_execsql_test 1.3 {
  SELECT DISTINCT t1.x
    FROM fts, t1
   WHERE fts MATCH 'that twice'
     AND content=x
   ORDER BY 1;
} {{a row that occurs twice}}
do_execsql_test 1.4 {
  SELECT t1.x
    FROM fts, t1
   WHERE fts MATCH 'that twice'
     AND content=x
   ORDER BY 1;
} {{a row that occurs twice} {a row that occurs twice}}
do_execsql_test 1.5 {
  SELECT DISTINCT t1.x
    FROM fts, t1
   WHERE fts MATCH 'that twice'
     AND content=x;
} {{a row that occurs twice}}
do_execsql_test 1.6 {
  SELECT t1.x
    FROM fts, t1
   WHERE fts MATCH 'that twice'
     AND content=x;
} {{a row that occurs twice} {a row that occurs twice}}

do_execsql_test 2.1 {
  SELECT DISTINCT t1.x
    FROM fts, t1
   WHERE fts.rowid=11
     AND content=x
   ORDER BY fts.rowid;
} {{a row that occurs twice}}
do_execsql_test 2.2 {
  SELECT DISTINCT t1.*
    FROM fts, t1
   WHERE fts.rowid=11
     AND content=x
   ORDER BY fts.rowid;
} {{a row that occurs twice} 111}
do_execsql_test 2.3 {
  SELECT DISTINCT t1.*
    FROM fts, t1
   WHERE fts.rowid=11
     AND content=x
   ORDER BY t1.y
} {{a row that occurs twice} 111}




finish_test

# 2014-04-21
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
#
# Test that ticket [b75a9ca6b0] has been fixed.
#
# Ticket [b75a9ca6b0] concerns queries that have both a GROUP BY
# and an ORDER BY.  This code verifies that SQLite is able to
# optimize out the ORDER BY in some circumstances, but retains the
# ORDER BY when necessary.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix tkt-b75a9ca6b0

do_execsql_test 1 {
  CREATE TABLE t1 (x, y);
  INSERT INTO t1 VALUES (1, 3); 
  INSERT INTO t1 VALUES (2, 2);
  INSERT INTO t1 VALUES (3, 1);
}

do_execsql_test 1.1 {
  CREATE INDEX i1 ON t1(x, y);
} 

set idxscan {0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}}
set tblscan {0 0 0 {SCAN TABLE t1}}
set grpsort {0 0 0 {USE TEMP B-TREE FOR GROUP BY}}
set sort    {0 0 0 {USE TEMP B-TREE FOR ORDER BY}}

foreach {tn q res eqp} [subst -nocommands {
  1 "SELECT * FROM t1 GROUP BY x, y ORDER BY x,y"
  {1 3  2 2  3 1} {$idxscan}

  2 "SELECT * FROM t1 GROUP BY x, y ORDER BY x"
  {1 3  2 2  3 1} {$idxscan $sort}

  3 "SELECT * FROM t1 GROUP BY y, x ORDER BY y, x"
  {3 1  2 2  1 3} {$idxscan $sort}
  
  4 "SELECT * FROM t1 GROUP BY x ORDER BY x"
  {1 3  2 2  3 1} {$idxscan}

  5 "SELECT * FROM t1 GROUP BY y ORDER BY y"
  {3 1  2 2  1 3} {$tblscan $grpsort}

  6 "SELECT * FROM t1 GROUP BY y ORDER BY x"
  {1 3  2 2  3 1} {$tblscan $grpsort $sort}

  7 "SELECT * FROM t1 GROUP BY x, y ORDER BY x, y DESC"
  {1 3  2 2  3 1} {$idxscan $sort}

  8 "SELECT * FROM t1 GROUP BY x, y ORDER BY x DESC, y DESC"
  {3 1  2 2  1 3} {$idxscan $sort}

  9 "SELECT * FROM t1 GROUP BY x, y ORDER BY x ASC, y ASC"
  {1 3  2 2  3 1} {$idxscan}

  10 "SELECT * FROM t1 GROUP BY x, y ORDER BY x COLLATE nocase, y"
  {1 3  2 2  3 1} {$idxscan $sort}

}] {
  do_execsql_test 1.$tn.1 $q $res
  do_eqp_test     1.$tn.2 $q $eqp
}


finish_test

# 2014 April 26
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# Test that ticket f67b41381a has been resolved.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix tkt-f67b41381a

do_execsql_test 1.0 {
  CREATE TABLE t1(a);
  INSERT INTO t1 VALUES(1);
  ALTER TABLE t1 ADD COLUMN b DEFAULT 2;
  CREATE TABLE t2(a, b);
  INSERT INTO t2 SELECT * FROM t1;
  SELECT * FROM t2;
} {1 2}

db cache size 0
foreach {tn tbls xfer} {
  1 { CREATE TABLE t1(a, b); CREATE TABLE t2(a, b)             }             1
  2 { CREATE TABLE t1(a, b DEFAULT 'x'); CREATE TABLE t2(a, b) }             0
  3 { CREATE TABLE t1(a, b DEFAULT 'x'); CREATE TABLE t2(a, b DEFAULT 'x') } 1
  4 { CREATE TABLE t1(a, b DEFAULT NULL); CREATE TABLE t2(a, b) }            0
  5 { CREATE TABLE t1(a DEFAULT 2, b); CREATE TABLE t2(a DEFAULT 1, b) }     1
  6 { CREATE TABLE t1(a DEFAULT 1, b); CREATE TABLE t2(a DEFAULT 1, b) }     1
  7 { CREATE TABLE t1(a DEFAULT 1, b DEFAULT 1);
      CREATE TABLE t2(a DEFAULT 3, b DEFAULT 1) }                            1
  8 { CREATE TABLE t1(a DEFAULT 1, b DEFAULT 1);
      CREATE TABLE t2(a DEFAULT 3, b DEFAULT 3) }                            0

} {

  execsql { DROP TABLE t1; DROP TABLE t2 }
  execsql $tbls

  set res 1
  db eval { EXPLAIN INSERT INTO t1 SELECT * FROM t2 } {
    if {$opcode == "Column"} { set res 0 }
  }

  do_test 2.$tn [list set res] $xfer
}

finish_test