SQLite

**   "sub-select returns N columns - expected M"
*/   
void sqlite3SubselectError(Parse *pParse, int nActual, int nExpect){
  const char *zFmt = "sub-select returns %d columns - expected %d";
  sqlite3ErrorMsg(pParse, zFmt, nActual, nExpect);
}
#endif

/*
** Expression pExpr is a vector that has been used in a context where
** it is not permitted. If pExpr is a sub-select vector, this routine 
** loads the Parse object with a message of the form:
**
**   "sub-select returns N columns - expected 1"
**
** Or, if it is a regular scalar vector:
**
**   "row value misused"
*/   
void sqlite3VectorErrorMsg(Parse *pParse, Expr *pExpr){
#ifndef SQLITE_OMIT_SUBQUERY
  if( pExpr->flags & EP_xIsSelect ){
    sqlite3SubselectError(pParse, pExpr->x.pSelect->pEList->nExpr, 1);
  }else
#endif
  {
    sqlite3ErrorMsg(pParse, "row value misused");
  }
}

/*
** Generate code for scalar subqueries used as a subquery expression, EXISTS,
** or IN operators.  Examples:
**
**     (SELECT a FROM b)          -- subquery
**     EXISTS (SELECT a FROM b)   -- EXISTS subquery

  int nVector = sqlite3ExprVectorSize(pIn->pLeft);
  if( (pIn->flags & EP_xIsSelect) ){
    if( nVector!=pIn->x.pSelect->pEList->nExpr ){
      sqlite3SubselectError(pParse, pIn->x.pSelect->pEList->nExpr, nVector);
      return 1;
    }
  }else if( nVector!=1 ){



    sqlite3VectorErrorMsg(pParse, pIn->pLeft);

    return 1;
  }
  return 0;
}
#endif

#ifndef SQLITE_OMIT_SUBQUERY

  explainComposite(pParse, p->op, iSub1, iSub2, 0);
  return pParse->nErr!=0;
}
#endif

#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
/* Forward Declarations */
static void substExprList(Parse*, ExprList*, int, ExprList*);
static void substSelect(Parse*, Select *, int, ExprList*, int);

/*
** Scan through the expression pExpr.  Replace every reference to
** a column in table number iTable with a copy of the iColumn-th
** entry in pEList.  (But leave references to the ROWID column 
** unchanged.)
**
** This routine is part of the flattening procedure.  A subquery
** whose result set is defined by pEList appears as entry in the
** FROM clause of a SELECT such that the VDBE cursor assigned to that
** FORM clause entry is iTable.  This routine make the necessary 
** changes to pExpr so that it refers directly to the source table
** of the subquery rather the result set of the subquery.
*/
static Expr *substExpr(
  Parse *pParse,      /* Report errors here */
  Expr *pExpr,        /* Expr in which substitution occurs */
  int iTable,         /* Table to be substituted */
  ExprList *pEList    /* Substitute expressions */
){
  sqlite3 *db = pParse->db;
  if( pExpr==0 ) return 0;
  if( pExpr->op==TK_COLUMN && pExpr->iTable==iTable ){
    if( pExpr->iColumn<0 ){
      pExpr->op = TK_NULL;
    }else{
      Expr *pNew;
      Expr *pCopy = pEList->a[pExpr->iColumn].pExpr;
      assert( pEList!=0 && pExpr->iColumn<pEList->nExpr );
      assert( pExpr->pLeft==0 && pExpr->pRight==0 );
      if( sqlite3ExprIsVector(pCopy) ){
        sqlite3VectorErrorMsg(pParse, pCopy);
      }else{
        pNew = sqlite3ExprDup(db, pCopy, 0);
        if( pNew && (pExpr->flags & EP_FromJoin) ){
          pNew->iRightJoinTable = pExpr->iRightJoinTable;
          pNew->flags |= EP_FromJoin;
        }
        sqlite3ExprDelete(db, pExpr);
        pExpr = pNew;
      }
    }
  }else{
    pExpr->pLeft = substExpr(pParse, pExpr->pLeft, iTable, pEList);
    pExpr->pRight = substExpr(pParse, pExpr->pRight, iTable, pEList);
    if( ExprHasProperty(pExpr, EP_xIsSelect) ){
      substSelect(pParse, pExpr->x.pSelect, iTable, pEList, 1);
    }else{
      substExprList(pParse, pExpr->x.pList, iTable, pEList);
    }
  }
  return pExpr;
}
static void substExprList(
  Parse *pParse,       /* Report errors here */
  ExprList *pList,     /* List to scan and in which to make substitutes */
  int iTable,          /* Table to be substituted */
  ExprList *pEList     /* Substitute values */
){
  int i;
  if( pList==0 ) return;
  for(i=0; i<pList->nExpr; i++){
    pList->a[i].pExpr = substExpr(pParse, pList->a[i].pExpr, iTable, pEList);
  }
}
static void substSelect(
  Parse *pParse,       /* Report errors here */
  Select *p,           /* SELECT statement in which to make substitutions */
  int iTable,          /* Table to be replaced */
  ExprList *pEList,    /* Substitute values */
  int doPrior          /* Do substitutes on p->pPrior too */
){
  SrcList *pSrc;
  struct SrcList_item *pItem;
  int i;
  if( !p ) return;
  do{
    substExprList(pParse, p->pEList, iTable, pEList);
    substExprList(pParse, p->pGroupBy, iTable, pEList);
    substExprList(pParse, p->pOrderBy, iTable, pEList);
    p->pHaving = substExpr(pParse, p->pHaving, iTable, pEList);
    p->pWhere = substExpr(pParse, p->pWhere, iTable, pEList);
    pSrc = p->pSrc;
    assert( pSrc!=0 );
    for(i=pSrc->nSrc, pItem=pSrc->a; i>0; i--, pItem++){
      substSelect(pParse, pItem->pSelect, iTable, pEList, 1);
      if( pItem->fg.isTabFunc ){
        substExprList(pParse, pItem->u1.pFuncArg, iTable, pEList);
      }
    }
  }while( doPrior && (p = p->pPrior)!=0 );
}
#endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */

#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)

          sqlite3ExprDup(db, pSub->pHaving, 0), pParent->pHaving
      );
      assert( pParent->pGroupBy==0 );
      pParent->pGroupBy = sqlite3ExprListDup(db, pSub->pGroupBy, 0);
    }else{
      pParent->pWhere = sqlite3ExprAnd(db, pWhere, pParent->pWhere);
    }
    substSelect(pParse, pParent, iParent, pSub->pEList, 0);
  
    /* The flattened query is distinct if either the inner or the
    ** outer query is distinct. 
    */
    pParent->selFlags |= pSub->selFlags & SF_Distinct;
  
    /*

**   (5) The WHERE clause expression originates in the ON or USING clause
**       of a LEFT JOIN.
**
** Return 0 if no changes are made and non-zero if one or more WHERE clause
** terms are duplicated into the subquery.
*/
static int pushDownWhereTerms(
  Parse *pParse,        /* Parse context (for malloc() and error reporting) */
  Select *pSubq,        /* The subquery whose WHERE clause is to be augmented */
  Expr *pWhere,         /* The WHERE clause of the outer query */
  int iCursor           /* Cursor number of the subquery */
){
  Expr *pNew;
  int nChng = 0;
  Select *pX;           /* For looping over compound SELECTs in pSubq */
  if( pWhere==0 ) return 0;
  for(pX=pSubq; pX; pX=pX->pPrior){
    if( (pX->selFlags & (SF_Aggregate|SF_Recursive))!=0 ){
      testcase( pX->selFlags & SF_Aggregate );
      testcase( pX->selFlags & SF_Recursive );
      testcase( pX!=pSubq );
      return 0; /* restrictions (1) and (2) */
    }
  }
  if( pSubq->pLimit!=0 ){
    return 0; /* restriction (3) */
  }
  while( pWhere->op==TK_AND ){
    nChng += pushDownWhereTerms(pParse, pSubq, pWhere->pRight, iCursor);
    pWhere = pWhere->pLeft;
  }
  if( ExprHasProperty(pWhere,EP_FromJoin) ) return 0; /* restriction 5 */
  if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){
    nChng++;
    while( pSubq ){
      pNew = sqlite3ExprDup(pParse->db, pWhere, 0);
      pNew = substExpr(pParse, pNew, iCursor, pSubq->pEList);
      pSubq->pWhere = sqlite3ExprAnd(pParse->db, pSubq->pWhere, pNew);
      pSubq = pSubq->pPrior;
    }
  }
  return nChng;
}
#endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */

    */
    pParse->nHeight += sqlite3SelectExprHeight(p);

    /* Make copies of constant WHERE-clause terms in the outer query down
    ** inside the subquery.  This can help the subquery to run more efficiently.
    */
    if( (pItem->fg.jointype & JT_OUTER)==0
     && pushDownWhereTerms(pParse, pSub, p->pWhere, pItem->iCursor)
    ){
#if SELECTTRACE_ENABLED
      if( sqlite3SelectTrace & 0x100 ){
        SELECTTRACE(0x100,pParse,p,("After WHERE-clause push-down:\n"));
        sqlite3TreeViewSelect(0, p, 0);
      }
#endif

int sqlite3DbstatRegister(sqlite3*);
#endif

int sqlite3ExprVectorSize(Expr *pExpr);
int sqlite3ExprIsVector(Expr *pExpr);
Expr *sqlite3VectorFieldSubexpr(Expr*, int);
Expr *sqlite3ExprForVectorField(Parse*,Expr*,int);
void sqlite3VectorErrorMsg(Parse*, Expr*);

#endif /* SQLITEINT_H */

do_execsql_test 14.2 "SELECT CASE (2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1
do_execsql_test 14.3 "SELECT CASE (SELECT 2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1
do_execsql_test 14.4 "SELECT 1 WHERE (SELECT 2,2) BETWEEN (1,1) AND (3,3)" 1
do_execsql_test 14.5 "SELECT 1 FROM t12 WHERE (x,1) BETWEEN (1,1) AND (3,3)" 1
do_execsql_test 14.6 {
  SELECT 1 FROM t12 WHERE (1,x) BETWEEN (1,1) AND (3,3)
} {1 1}

#-------------------------------------------------------------------------
# Test that errors are not concealed by the SELECT flattening or
# WHERE-clause push-down optimizations.
do_execsql_test 14.1 {
  CREATE TABLE x1(a PRIMARY KEY, b);
  CREATE TABLE x2(a INTEGER PRIMARY KEY, b);
}

foreach {tn n sql} {
  1 0 "SELECT * FROM (SELECT (1, 1) AS c FROM x1) WHERE c=1"
  2 2 "SELECT * FROM (SELECT 1 AS x, (SELECT 8,9) AS y) WHERE y<1"
  3 3 "SELECT * FROM (SELECT 1 AS x, (SELECT 8,9,10) AS y) WHERE y<1"
  4 0 "SELECT * FROM (SELECT (a, b) AS c FROM x1), x2 WHERE c=a"
  5 0 "SELECT * FROM (SELECT a AS c, (1, 2, 3) FROM x1), x2 WHERE c=a"
  6 0 "SELECT * FROM (SELECT 1 AS c, (1, 2, 3) FROM x1) WHERE c=1"
} {
  if {$n==0} {
    set err "row value misused"
  } else {
    set err "sub-select returns $n columns - expected 1"
  }
  do_catchsql_test 14.2.$tn $sql [list 1 $err]
}


finish_test