Fossil SCM

Update the build-in SQLite to the latest 3.21.0 beta that includes the enhance use of co-routines for subqueries.

drh 2017-10-02 03:46 trunk

Commit 75fffb49ce9e23a2aabc2ea75c123d95a6aede3d6f8032823ac8e03237218e81

Parent c8b46764b6a913f…

2 files changed +276 -211 +1 -1

M src/sqlite3.c

+276 -211

		--- src/sqlite3.c
		+++ src/sqlite3.c
		@@ -1147,11 +1147,11 @@
1147	1147	** [sqlite3_libversion_number()], [sqlite3_sourceid()],
1148	1148	** [sqlite_version()] and [sqlite_source_id()].
1149	1149	*/
1150	1150	#define SQLITE_VERSION "3.21.0"
1151	1151	#define SQLITE_VERSION_NUMBER 3021000
1152		-#define SQLITE_SOURCE_ID "2017-09-21 20:43:48 5d03c738e93d36815248991d9ed3d62297ba1bb966e602e7874410076c144f43"
	1152	+#define SQLITE_SOURCE_ID "2017-10-02 02:52:54 c9104b59c7ed360291f7f6fc8caae938e9840c77620d598e4096f78183bf807a"
1153	1153
1154	1154	/*
1155	1155	** CAPI3REF: Run-Time Library Version Numbers
1156	1156	** KEYWORDS: sqlite3_version sqlite3_sourceid
1157	1157	**
		@@ -15279,20 +15279,19 @@
15279	15279	*/
15280	15280	#define SQLITE_QueryFlattener 0x0001 /* Query flattening */
15281	15281	#define SQLITE_ColumnCache 0x0002 /* Column cache */
15282	15282	#define SQLITE_GroupByOrder 0x0004 /* GROUPBY cover of ORDERBY */
15283	15283	#define SQLITE_FactorOutConst 0x0008 /* Constant factoring */
15284		-/* not used 0x0010 // Was: SQLITE_IdxRealAsInt */
15285		-#define SQLITE_DistinctOpt 0x0020 /* DISTINCT using indexes */
15286		-#define SQLITE_CoverIdxScan 0x0040 /* Covering index scans */
15287		-#define SQLITE_OrderByIdxJoin 0x0080 /* ORDER BY of joins via index */
15288		-#define SQLITE_SubqCoroutine 0x0100 /* Evaluate subqueries as coroutines */
15289		-#define SQLITE_Transitive 0x0200 /* Transitive constraints */
15290		-#define SQLITE_OmitNoopJoin 0x0400 /* Omit unused tables in joins */
	15284	+#define SQLITE_DistinctOpt 0x0010 /* DISTINCT using indexes */
	15285	+#define SQLITE_CoverIdxScan 0x0020 /* Covering index scans */
	15286	+#define SQLITE_OrderByIdxJoin 0x0040 /* ORDER BY of joins via index */
	15287	+#define SQLITE_Transitive 0x0080 /* Transitive constraints */
	15288	+#define SQLITE_OmitNoopJoin 0x0100 /* Omit unused tables in joins */
	15289	+#define SQLITE_CountOfView 0x0200 /* The count-of-view optimization */
	15290	+#define SQLITE_CursorHints 0x0400 /* Add OP_CursorHint opcodes */
15291	15291	#define SQLITE_Stat34 0x0800 /* Use STAT3 or STAT4 data */
15292		-#define SQLITE_CountOfView 0x1000 /* The count-of-view optimization */
15293		-#define SQLITE_CursorHints 0x2000 /* Add OP_CursorHint opcodes */
	15292	+ /* TH3 expects the Stat34 ^^^^^^ value to be 0x0800. Don't change it */
15294	15293	#define SQLITE_AllOpts 0xffff /* All optimizations */
15295	15294
15296	15295	/*
15297	15296	** Macros for testing whether or not optimizations are enabled or disabled.
15298	15297	*/
		@@ -17777,10 +17776,12 @@
17777	17776	SQLITE_PRIVATE const char *sqlite3ErrStr(int);
17778	17777	SQLITE_PRIVATE int sqlite3ReadSchema(Parse *pParse);
17779	17778	SQLITE_PRIVATE CollSeq sqlite3FindCollSeq(sqlite3,u8 enc, const char*,int);
17780	17779	SQLITE_PRIVATE CollSeq sqlite3LocateCollSeq(Parse pParse, const char*zName);
17781	17780	SQLITE_PRIVATE CollSeq sqlite3ExprCollSeq(Parse pParse, Expr *pExpr);
	17781	+SQLITE_PRIVATE CollSeq sqlite3ExprNNCollSeq(Parse pParse, Expr *pExpr);
	17782	+SQLITE_PRIVATE int sqlite3ExprCollSeqMatch(Parse,Expr,Expr*);
17782	17783	SQLITE_PRIVATE Expr sqlite3ExprAddCollateToken(Parse pParse, Expr, const Token, int);
17783	17784	SQLITE_PRIVATE Expr sqlite3ExprAddCollateString(Parse,Expr,const char);
17784	17785	SQLITE_PRIVATE Expr sqlite3ExprSkipCollate(Expr);
17785	17786	SQLITE_PRIVATE int sqlite3CheckCollSeq(Parse , CollSeq );
17786	17787	SQLITE_PRIVATE int sqlite3CheckObjectName(Parse , const char );
		@@ -43282,18 +43283,23 @@
43282	43283	dwFlagsAndAttributes & FILE_ATTRIBUTE_MASK;
43283	43284	extendedParameters.dwFileFlags = dwFlagsAndAttributes & FILE_FLAG_MASK;
43284	43285	extendedParameters.dwSecurityQosFlags = SECURITY_ANONYMOUS;
43285	43286	extendedParameters.lpSecurityAttributes = NULL;
43286	43287	extendedParameters.hTemplateFile = NULL;
43287		- while( (h = osCreateFile2((LPCWSTR)zConverted,
43288		- dwDesiredAccess,
43289		- dwShareMode,
43290		- dwCreationDisposition,
43291		- &extendedParameters))==INVALID_HANDLE_VALUE &&
43292		- winRetryIoerr(&cnt, &lastErrno) ){
43293		- /* Noop */
43294		- }
	43288	+ do{
	43289	+ h = osCreateFile2((LPCWSTR)zConverted,
	43290	+ dwDesiredAccess,
	43291	+ dwShareMode,
	43292	+ dwCreationDisposition,
	43293	+ &extendedParameters);
	43294	+ if( h!=INVALID_HANDLE_VALUE ) break;
	43295	+ if( isReadWrite ){
	43296	+ int isRO = 0;
	43297	+ int rc2 = winAccess(pVfs, zName, SQLITE_ACCESS_READ, &isRO);
	43298	+ if( rc2==SQLITE_OK && isRO ) break;
	43299	+ }
	43300	+ }while( winRetryIoerr(&cnt, &lastErrno) );
43295	43301	#else
43296	43302	do{
43297	43303	h = osCreateFileW((LPCWSTR)zConverted,
43298	43304	dwDesiredAccess,
43299	43305	dwShareMode, NULL,
		@@ -43309,19 +43315,24 @@
43309	43315	}while( winRetryIoerr(&cnt, &lastErrno) );
43310	43316	#endif
43311	43317	}
43312	43318	#ifdef SQLITE_WIN32_HAS_ANSI
43313	43319	else{
43314		- while( (h = osCreateFileA((LPCSTR)zConverted,
43315		- dwDesiredAccess,
43316		- dwShareMode, NULL,
43317		- dwCreationDisposition,
43318		- dwFlagsAndAttributes,
43319		- NULL))==INVALID_HANDLE_VALUE &&
43320		- winRetryIoerr(&cnt, &lastErrno) ){
43321		- /* Noop */
43322		- }
	43320	+ do{
	43321	+ h = osCreateFileA((LPCSTR)zConverted,
	43322	+ dwDesiredAccess,
	43323	+ dwShareMode, NULL,
	43324	+ dwCreationDisposition,
	43325	+ dwFlagsAndAttributes,
	43326	+ NULL);
	43327	+ if( h!=INVALID_HANDLE_VALUE ) break;
	43328	+ if( isReadWrite ){
	43329	+ int isRO = 0;
	43330	+ int rc2 = winAccess(pVfs, zName, SQLITE_ACCESS_READ, &isRO);
	43331	+ if( rc2==SQLITE_OK && isRO ) break;
	43332	+ }
	43333	+ }while( winRetryIoerr(&cnt, &lastErrno) );
43323	43334	}
43324	43335	#endif
43325	43336	winLogIoerr(cnt, __LINE__);
43326	43337
43327	43338	OSTRACE(("OPEN file=%p, name=%s, access=%lx, rc=%s\n", h, zUtf8Name,
		@@ -61031,10 +61042,13 @@
61031	61042	u8 pEnd = &data[cellOffset + nCell2];
61032	61043	u8 *pAddr;
61033	61044	int sz2 = 0;
61034	61045	int sz = get2byte(&data[iFree+2]);
61035	61046	int top = get2byte(&data[hdr+5]);
	61047	+ if( top>=iFree ){
	61048	+ return SQLITE_CORRUPT_PGNO(pPage->pgno);
	61049	+ }
61036	61050	if( iFree2 ){
61037	61051	assert( iFree+sz<=iFree2 ); /* Verified by pageFindSlot() */
61038	61052	sz2 = get2byte(&data[iFree2+2]);
61039	61053	assert( iFree+sz+sz2+iFree2-(iFree+sz) <= usableSize );
61040	61054	memmove(&data[iFree+sz+sz2], &data[iFree+sz], iFree2-(iFree+sz));
		@@ -71383,13 +71397,14 @@
71383	71397	u32 amt, /* Number of bytes to return. */
71384	71398	Mem pMem / OUT: Return data in this Mem structure. */
71385	71399	){
71386	71400	int rc;
71387	71401	pMem->flags = MEM_Null;
71388		- if( SQLITE_OK==(rc = sqlite3VdbeMemClearAndResize(pMem, amt)) ){
	71402	+ if( SQLITE_OK==(rc = sqlite3VdbeMemClearAndResize(pMem, amt+1)) ){
71389	71403	rc = sqlite3BtreePayload(pCur, offset, amt, pMem->z);
71390	71404	if( rc==SQLITE_OK ){
	71405	+ pMem->z[amt] = 0; /* Overrun area used when reading malformed records */
71391	71406	pMem->flags = MEM_Blob;
71392	71407	pMem->n = (int)amt;
71393	71408	}else{
71394	71409	sqlite3VdbeMemRelease(pMem);
71395	71410	}
		@@ -91882,10 +91897,15 @@
91882	91897
91883	91898	/*
91884	91899	** Return the collation sequence for the expression pExpr. If
91885	91900	** there is no defined collating sequence, return NULL.
91886	91901	**
	91902	+** See also: sqlite3ExprNNCollSeq()
	91903	+**
	91904	+** The sqlite3ExprNNCollSeq() works the same exact that it returns the
	91905	+** default collation if pExpr has no defined collation.
	91906	+**
91887	91907	** The collating sequence might be determined by a COLLATE operator
91888	91908	** or by the presence of a column with a defined collating sequence.
91889	91909	** COLLATE operators take first precedence. Left operands take
91890	91910	** precedence over right operands.
91891	91911	*/
		@@ -91945,10 +91965,36 @@
91945	91965	if( sqlite3CheckCollSeq(pParse, pColl) ){
91946	91966	pColl = 0;
91947	91967	}
91948	91968	return pColl;
91949	91969	}
	91970	+
	91971	+/*
	91972	+** Return the collation sequence for the expression pExpr. If
	91973	+** there is no defined collating sequence, return a pointer to the
	91974	+** defautl collation sequence.
	91975	+**
	91976	+** See also: sqlite3ExprCollSeq()
	91977	+**
	91978	+** The sqlite3ExprCollSeq() routine works the same except that it
	91979	+** returns NULL if there is no defined collation.
	91980	+*/
	91981	+SQLITE_PRIVATE CollSeq sqlite3ExprNNCollSeq(Parse pParse, Expr *pExpr){
	91982	+ CollSeq *p = sqlite3ExprCollSeq(pParse, pExpr);
	91983	+ if( p==0 ) p = pParse->db->pDfltColl;
	91984	+ assert( p!=0 );
	91985	+ return p;
	91986	+}
	91987	+
	91988	+/*
	91989	+** Return TRUE if the two expressions have equivalent collating sequences.
	91990	+*/
	91991	+SQLITE_PRIVATE int sqlite3ExprCollSeqMatch(Parse pParse, Expr pE1, Expr *pE2){
	91992	+ CollSeq *pColl1 = sqlite3ExprNNCollSeq(pParse, pE1);
	91993	+ CollSeq *pColl2 = sqlite3ExprNNCollSeq(pParse, pE2);
	91994	+ return sqlite3StrICmp(pColl1->zName, pColl2->zName)==0;
	91995	+}
91950	91996
91951	91997	/*
91952	91998	** pExpr is an operand of a comparison operator. aff2 is the
91953	91999	** type affinity of the other operand. This routine returns the
91954	92000	** type affinity that should be used for the comparison operator.
		@@ -93439,16 +93485,15 @@
93439	93485	** ExprList.
93440	93486	*/
93441	93487	SQLITE_PRIVATE u32 sqlite3ExprListFlags(const ExprList *pList){
93442	93488	int i;
93443	93489	u32 m = 0;
93444		- if( pList ){
93445		- for(i=0; i<pList->nExpr; i++){
93446		- Expr *pExpr = pList->a[i].pExpr;
93447		- assert( pExpr!=0 );
93448		- m \|= pExpr->flags;
93449		- }
	93490	+ assert( pList!=0 );
	93491	+ for(i=0; i<pList->nExpr; i++){
	93492	+ Expr *pExpr = pList->a[i].pExpr;
	93493	+ assert( pExpr!=0 );
	93494	+ m \|= pExpr->flags;
93450	93495	}
93451	93496	return m;
93452	93497	}
93453	93498
93454	93499	/*
		@@ -93601,12 +93646,12 @@
93601	93646	/* Check if pExpr is identical to any GROUP BY term. If so, consider
93602	93647	** it constant. */
93603	93648	for(i=0; i<pGroupBy->nExpr; i++){
93604	93649	Expr *p = pGroupBy->a[i].pExpr;
93605	93650	if( sqlite3ExprCompare(0, pExpr, p, -1)<2 ){
93606		- CollSeq *pColl = sqlite3ExprCollSeq(pWalker->pParse, p);
93607		- if( pColl==0 \|\| sqlite3_stricmp("BINARY", pColl->zName)==0 ){
	93651	+ CollSeq *pColl = sqlite3ExprNNCollSeq(pWalker->pParse, p);
	93652	+ if( sqlite3_stricmp("BINARY", pColl->zName)==0 ){
93608	93653	return WRC_Prune;
93609	93654	}
93610	93655	}
93611	93656	}
93612	93657
		@@ -117746,11 +117791,12 @@
117746	117791	if( pNew==0 ){
117747	117792	assert( pParse->db->mallocFailed );
117748	117793	pNew = &standin;
117749	117794	}
117750	117795	if( pEList==0 ){
117751		- pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(pParse->db,TK_ASTERISK,0));
	117796	+ pEList = sqlite3ExprListAppend(pParse, 0,
	117797	+ sqlite3Expr(pParse->db,TK_ASTERISK,0));
117752	117798	}
117753	117799	pNew->pEList = pEList;
117754	117800	pNew->op = TK_SELECT;
117755	117801	pNew->selFlags = selFlags;
117756	117802	pNew->iLimit = 0;
		@@ -117770,11 +117816,12 @@
117770	117816	pNew->pPrior = 0;
117771	117817	pNew->pNext = 0;
117772	117818	pNew->pLimit = pLimit;
117773	117819	pNew->pOffset = pOffset;
117774	117820	pNew->pWith = 0;
117775		- assert( pOffset==0 \|\| pLimit!=0 \|\| pParse->nErr>0 \|\| pParse->db->mallocFailed!=0 );
	117821	+ assert( pOffset==0 \|\| pLimit!=0 \|\| pParse->nErr>0
	117822	+ \|\| pParse->db->mallocFailed!=0 );
117776	117823	if( pParse->db->mallocFailed ) {
117777	117824	clearSelect(pParse->db, pNew, pNew!=&standin);
117778	117825	pNew = 0;
117779	117826	}else{
117780	117827	assert( pNew->pSrc!=0 \|\| pParse->nErr>0 );
		@@ -118387,11 +118434,12 @@
118387	118434	}
118388	118435	regOrig = 0;
118389	118436	assert( eDest==SRT_Set \|\| eDest==SRT_Mem
118390	118437	\|\| eDest==SRT_Coroutine \|\| eDest==SRT_Output );
118391	118438	}
118392		- nResultCol = sqlite3ExprCodeExprList(pParse,p->pEList,regResult,0,ecelFlags);
	118439	+ nResultCol = sqlite3ExprCodeExprList(pParse,p->pEList,regResult,
	118440	+ 0,ecelFlags);
118393	118441	}
118394	118442
118395	118443	/* If the DISTINCT keyword was present on the SELECT statement
118396	118444	** and this row has been seen before, then do not make this row
118397	118445	** part of the result.
		@@ -118737,14 +118785,11 @@
118737	118785	nExpr = pList->nExpr;
118738	118786	pInfo = sqlite3KeyInfoAlloc(db, nExpr-iStart, nExtra+1);
118739	118787	if( pInfo ){
118740	118788	assert( sqlite3KeyInfoIsWriteable(pInfo) );
118741	118789	for(i=iStart, pItem=pList->a+iStart; i<nExpr; i++, pItem++){
118742		- CollSeq *pColl;
118743		- pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr);
118744		- if( !pColl ) pColl = db->pDfltColl;
118745		- pInfo->aColl[i-iStart] = pColl;
	118790	+ pInfo->aColl[i-iStart] = sqlite3ExprNNCollSeq(pParse, pItem->pExpr);
118746	118791	pInfo->aSortOrder[i-iStart] = pItem->sortOrder;
118747	118792	}
118748	118793	}
118749	118794	return pInfo;
118750	118795	}
		@@ -119201,13 +119246,13 @@
119201	119246	** short=OFF, full=OFF: Column name is the text of the expression has it
119202	119247	** originally appears in the SELECT statement. In
119203	119248	** other words, the zSpan of the result expression.
119204	119249	**
119205	119250	** short=ON, full=OFF: (This is the default setting). If the result
119206		-** refers directly to a table column, then the result
119207		-** column name is just the table column name: COLUMN.
119208		-** Otherwise use zSpan.
	119251	+** refers directly to a table column, then the
	119252	+** result column name is just the table column
	119253	+** name: COLUMN. Otherwise use zSpan.
119209	119254	**
119210	119255	** full=ON, short=ANY: If the result refers directly to a table column,
119211	119256	** then the result column name with the table name
119212	119257	** prefix, ex: TABLE.COLUMN. Otherwise use zSpan.
119213	119258	*/
		@@ -119245,11 +119290,11 @@
119245	119290	for(i=0; i<pEList->nExpr; i++){
119246	119291	Expr *p = pEList->a[i].pExpr;
119247	119292
119248	119293	assert( p!=0 );
119249	119294	assert( p->op!=TK_AGG_COLUMN ); /* Agg processing has not run yet */
119250		- assert( p->op!=TK_COLUMN \|\| p->pTab!=0 ); /* Covering indexes not yet coded */
	119295	+ assert( p->op!=TK_COLUMN \|\| p->pTab!=0 ); /* Covering idx not yet coded */
119251	119296	if( pEList->a[i].zName ){
119252	119297	/* An AS clause always takes first priority */
119253	119298	char *zName = pEList->a[i].zName;
119254	119299	sqlite3VdbeSetColName(v, i, COLNAME_NAME, zName, SQLITE_TRANSIENT);
119255	119300	}else if( srcName && p->op==TK_COLUMN ){
		@@ -120809,11 +120854,13 @@
120809	120854	static Expr *substExpr(
120810	120855	SubstContext pSubst, / Description of the substitution */
120811	120856	Expr pExpr / Expr in which substitution occurs */
120812	120857	){
120813	120858	if( pExpr==0 ) return 0;
120814		- if( ExprHasProperty(pExpr, EP_FromJoin) && pExpr->iRightJoinTable==pSubst->iTable ){
	120859	+ if( ExprHasProperty(pExpr, EP_FromJoin)
	120860	+ && pExpr->iRightJoinTable==pSubst->iTable
	120861	+ ){
120815	120862	pExpr->iRightJoinTable = pSubst->iNewTable;
120816	120863	}
120817	120864	if( pExpr->op==TK_COLUMN && pExpr->iTable==pSubst->iTable ){
120818	120865	if( pExpr->iColumn<0 ){
120819	120866	pExpr->op = TK_NULL;
		@@ -120922,72 +120969,78 @@
120922	120969	** The code generated for this simplification gives the same result
120923	120970	** but only has to scan the data once. And because indices might
120924	120971	** exist on the table t1, a complete scan of the data might be
120925	120972	** avoided.
120926	120973	**
120927		-** Flattening is only attempted if all of the following are true:
120928		-**
120929		-** (1) The subquery and the outer query do not both use aggregates.
120930		-**
120931		-** (2) The subquery is not an aggregate or (2a) the outer query is not a join
120932		-** and (2b) the outer query does not use subqueries other than the one
120933		-** FROM-clause subquery that is a candidate for flattening. (2b is
120934		-** due to ticket [2f7170d73bf9abf80] from 2015-02-09.)
120935		-**
120936		-** (3) The subquery is not the right operand of a LEFT JOIN
120937		-** or (a) the subquery is not itself a join and (b) the FROM clause
120938		-** of the subquery does not contain a virtual table and (c) the
120939		-** outer query is not an aggregate.
120940		-**
120941		-** (4) The subquery is not DISTINCT.
	120974	+** Flattening is subject to the following constraints:
	120975	+**
	120976	+ () We no longer attempt to flatten aggregate subqueries. Was:
	120977	+** The subquery and the outer query cannot both be aggregates.
	120978	+**
	120979	+ () We no longer attempt to flatten aggregate subqueries. Was:
	120980	+** (2) If the subquery is an aggregate then
	120981	+** (2a) the outer query must not be a join and
	120982	+** (2b) the outer query must not use subqueries
	120983	+** other than the one FROM-clause subquery that is a candidate
	120984	+** for flattening. (This is due to ticket [2f7170d73bf9abf80]
	120985	+** from 2015-02-09.)
	120986	+**
	120987	+** (3) If the subquery is the right operand of a LEFT JOIN then
	120988	+** (3a) the subquery may not be a join and
	120989	+** (3b) the FROM clause of the subquery may not contain a virtual
	120990	+** table and
	120991	+** (3c) the outer query may not be an aggregate.
	120992	+**
	120993	+** (4) The subquery can not be DISTINCT.
120942	120994	**
120943	120995	() At one point restrictions (4) and (5) defined a subset of DISTINCT
120944	120996	** sub-queries that were excluded from this optimization. Restriction
120945	120997	** (4) has since been expanded to exclude all DISTINCT subqueries.
120946	120998	**
120947		-** (6) The subquery does not use aggregates or the outer query is not
120948		-** DISTINCT.
	120999	+ () We no longer attempt to flatten aggregate subqueries. Was:
	121000	+** If the subquery is aggregate, the outer query may not be DISTINCT.
120949	121001	**
120950		-** (7) The subquery has a FROM clause. TODO: For subqueries without
	121002	+** (7) The subquery must have a FROM clause. TODO: For subqueries without
120951	121003	** A FROM clause, consider adding a FROM clause with the special
120952	121004	** table sqlite_once that consists of a single row containing a
120953	121005	** single NULL.
120954	121006	**
120955		-** (8) The subquery does not use LIMIT or the outer query is not a join.
	121007	+** (8) If the subquery uses LIMIT then the outer query may not be a join.
120956	121008	**
120957		-** (9) The subquery does not use LIMIT or the outer query does not use
120958		-** aggregates.
	121009	+** (9) If the subquery uses LIMIT then the outer query may not be aggregate.
120959	121010	**
120960	121011	() Restriction (10) was removed from the code on 2005-02-05 but we
120961	121012	** accidently carried the comment forward until 2014-09-15. Original
120962		-** text: "The subquery does not use aggregates or the outer query
120963		-** does not use LIMIT."
	121013	+** constraint: "If the subquery is aggregate then the outer query
	121014	+** may not use LIMIT."
120964	121015	**
120965		-** (11) The subquery and the outer query do not both have ORDER BY clauses.
	121016	+** (11) The subquery and the outer query may not both have ORDER BY clauses.
120966	121017	**
120967	121018	() Not implemented. Subsumed into restriction (3). Was previously
120968	121019	** a separate restriction deriving from ticket #350.
120969	121020	**
120970		-** (13) The subquery and outer query do not both use LIMIT.
	121021	+** (13) The subquery and outer query may not both use LIMIT.
120971	121022	**
120972		-** (14) The subquery does not use OFFSET.
	121023	+** (14) The subquery may not use OFFSET.
120973	121024	**
120974		-** (15) The outer query is not part of a compound select or the
120975		-** subquery does not have a LIMIT clause.
	121025	+** (15) If the outer query is part of a compound select, then the
	121026	+** subquery may not use LIMIT.
120976	121027	** (See ticket #2339 and ticket [02a8e81d44]).
120977	121028	**
120978		-** (16) The outer query is not an aggregate or the subquery does
120979		-** not contain ORDER BY. (Ticket #2942) This used to not matter
	121029	+** (16) If the outer query is aggregate, then the subquery may not
	121030	+** use ORDER BY. (Ticket #2942) This used to not matter
120980	121031	** until we introduced the group_concat() function.
120981	121032	**
120982		-** (17) The sub-query is not a compound select, or it is a UNION ALL
120983		-** compound clause made up entirely of non-aggregate queries, and
120984		-** the parent query:
120985		-**
120986		-** * is not itself part of a compound select,
120987		-** * is not an aggregate or DISTINCT query, and
120988		-** * is not a join
	121033	+** (17) If the subquery is a compound select, then
	121034	+** (17a) all compound operators must be a UNION ALL, and
	121035	+** (17b) no terms within the subquery compound may be aggregate
	121036	+** or DISTINT, and
	121037	+** (17c) every term within the subquery compound must have a FROM clause
	121038	+** (17d) the outer query may not be
	121039	+** (17d1) aggregate, or
	121040	+** (17d2) DISTINCT, or
	121041	+** (17d3) a join.
120989	121042	**
120990	121043	** The parent and sub-query may contain WHERE clauses. Subject to
120991	121044	** rules (11), (13) and (14), they may also contain ORDER BY,
120992	121045	** LIMIT and OFFSET clauses. The subquery cannot use any compound
120993	121046	** operator other than UNION ALL because all the other compound
		@@ -120999,41 +121052,42 @@
120999	121052	** SELECT statement, but all the code here does is make sure that no
121000	121053	** such (illegal) sub-query is flattened. The caller will detect the
121001	121054	** syntax error and return a detailed message.
121002	121055	**
121003	121056	** (18) If the sub-query is a compound select, then all terms of the
121004		-** ORDER by clause of the parent must be simple references to
	121057	+** ORDER BY clause of the parent must be simple references to
121005	121058	** columns of the sub-query.
121006	121059	**
121007		-** (19) The subquery does not use LIMIT or the outer query does not
	121060	+** (19) If the subquery uses LIMIT then the outer query may not
121008	121061	** have a WHERE clause.
121009	121062	**
121010	121063	** (20) If the sub-query is a compound select, then it must not use
121011	121064	** an ORDER BY clause. Ticket #3773. We could relax this constraint
121012	121065	** somewhat by saying that the terms of the ORDER BY clause must
121013	121066	** appear as unmodified result columns in the outer query. But we
121014	121067	** have other optimizations in mind to deal with that case.
121015	121068	**
121016		-** (21) The subquery does not use LIMIT or the outer query is not
	121069	+** (21) If the subquery uses LIMIT then the outer query may not be
121017	121070	** DISTINCT. (See ticket [752e1646fc]).
121018	121071	**
121019		-** (22) The subquery is not a recursive CTE.
	121072	+** (22) The subquery may not be a recursive CTE.
121020	121073	**
121021		-** (23) The parent is not a recursive CTE, or the sub-query is not a
121022		-** compound query. This restriction is because transforming the
	121074	+** (23) If the outer query is a recursive CTE, then the sub-query may not be
	121075	+** a compound query. This restriction is because transforming the
121023	121076	** parent to a compound query confuses the code that handles
121024	121077	** recursive queries in multiSelect().
121025	121078	**
121026		-** (24) The subquery is not an aggregate that uses the built-in min() or
	121079	+ () We no longer attempt to flatten aggregate subqueries. Was:
	121080	+** The subquery may not be an aggregate that uses the built-in min() or
121027	121081	** or max() functions. (Without this restriction, a query like:
121028	121082	** "SELECT x FROM (SELECT max(y), x FROM t1)" would not necessarily
121029	121083	** return the value X for which Y was maximal.)
121030	121084	**
121031	121085	**
121032	121086	** In this routine, the "p" parameter is a pointer to the outer query.
121033	121087	** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query
121034		-** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
	121088	+** uses aggregates.
121035	121089	**
121036	121090	** If flattening is not attempted, this routine is a no-op and returns 0.
121037	121091	** If flattening is attempted this routine returns 1.
121038	121092	**
121039	121093	** All of the expression analysis must occur on both the outer query and
		@@ -121041,12 +121095,11 @@
121041	121095	*/
121042	121096	static int flattenSubquery(
121043	121097	Parse pParse, / Parsing context */
121044	121098	Select p, / The parent or outer SELECT statement */
121045	121099	int iFrom, /* Index in p->pSrc->a[] of the inner subquery */
121046		- int isAgg, /* True if outer SELECT uses aggregate functions */
121047		- int subqueryIsAgg /* True if the subquery uses aggregate functions */
	121100	+ int isAgg /* True if outer SELECT uses aggregate functions */
121048	121101	){
121049	121102	const char *zSavedAuthContext = pParse->zAuthContext;
121050	121103	Select pParent; / Current UNION ALL term of the other query */
121051	121104	Select pSub; / The inner query or "subquery" */
121052	121105	Select pSub1; / Pointer to the rightmost select in sub-query */
		@@ -121061,28 +121114,18 @@
121061	121114	sqlite3 *db = pParse->db;
121062	121115
121063	121116	/* Check to see if flattening is permitted. Return 0 if not.
121064	121117	*/
121065	121118	assert( p!=0 );
121066		- assert( p->pPrior==0 ); /* Unable to flatten compound queries */
	121119	+ assert( p->pPrior==0 );
121067	121120	if( OptimizationDisabled(db, SQLITE_QueryFlattener) ) return 0;
121068	121121	pSrc = p->pSrc;
121069	121122	assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
121070	121123	pSubitem = &pSrc->a[iFrom];
121071	121124	iParent = pSubitem->iCursor;
121072	121125	pSub = pSubitem->pSelect;
121073	121126	assert( pSub!=0 );
121074		- if( subqueryIsAgg ){
121075		- if( isAgg ) return 0; /* Restriction (1) */
121076		- if( pSrc->nSrc>1 ) return 0; /* Restriction (2a) */
121077		- if( (p->pWhere && ExprHasProperty(p->pWhere,EP_Subquery))
121078		- \|\| (sqlite3ExprListFlags(p->pEList) & EP_Subquery)!=0
121079		- \|\| (sqlite3ExprListFlags(p->pOrderBy) & EP_Subquery)!=0
121080		- ){
121081		- return 0; /* Restriction (2b) */
121082		- }
121083		- }
121084	121127
121085	121128	pSubSrc = pSub->pSrc;
121086	121129	assert( pSubSrc );
121087	121130	/* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
121088	121131	** not arbitrary expressions, we allowed some combining of LIMIT and OFFSET
		@@ -121093,37 +121136,33 @@
121093	121136	if( pSub->pOffset ) return 0; /* Restriction (14) */
121094	121137	if( (p->selFlags & SF_Compound)!=0 && pSub->pLimit ){
121095	121138	return 0; /* Restriction (15) */
121096	121139	}
121097	121140	if( pSubSrc->nSrc==0 ) return 0; /* Restriction (7) */
121098		- if( pSub->selFlags & SF_Distinct ) return 0; /* Restriction (5) */
	121141	+ if( pSub->selFlags & SF_Distinct ) return 0; /* Restriction (4) */
121099	121142	if( pSub->pLimit && (pSrc->nSrc>1 \|\| isAgg) ){
121100	121143	return 0; /* Restrictions (8)(9) */
121101	121144	}
121102		- if( (p->selFlags & SF_Distinct)!=0 && subqueryIsAgg ){
121103		- return 0; /* Restriction (6) */
121104		- }
121105	121145	if( p->pOrderBy && pSub->pOrderBy ){
121106	121146	return 0; /* Restriction (11) */
121107	121147	}
121108	121148	if( isAgg && pSub->pOrderBy ) return 0; /* Restriction (16) */
121109	121149	if( pSub->pLimit && p->pWhere ) return 0; /* Restriction (19) */
121110	121150	if( pSub->pLimit && (p->selFlags & SF_Distinct)!=0 ){
121111	121151	return 0; /* Restriction (21) */
121112	121152	}
121113		- testcase( pSub->selFlags & SF_Recursive );
121114		- testcase( pSub->selFlags & SF_MinMaxAgg );
121115		- if( pSub->selFlags & (SF_Recursive\|SF_MinMaxAgg) ){
121116		- return 0; /* Restrictions (22) and (24) */
	121153	+ if( pSub->selFlags & (SF_Recursive) ){
	121154	+ return 0; /* Restrictions (22) */
121117	121155	}
121118	121156	if( (p->selFlags & SF_Recursive) && pSub->pPrior ){
121119	121157	return 0; /* Restriction (23) */
121120	121158	}
121121	121159
121122	121160	/*
121123	121161	** If the subquery is the right operand of a LEFT JOIN, then the
121124		- ** subquery may not be a join itself. Example of why this is not allowed:
	121162	+ ** subquery may not be a join itself (3a). Example of why this is not
	121163	+ ** allowed:
121125	121164	**
121126	121165	** t1 LEFT OUTER JOIN (t2 JOIN t3)
121127	121166	**
121128	121167	** If we flatten the above, we would get
121129	121168	**
		@@ -121130,58 +121169,60 @@
121130	121169	** (t1 LEFT OUTER JOIN t2) JOIN t3
121131	121170	**
121132	121171	** which is not at all the same thing.
121133	121172	**
121134	121173	** If the subquery is the right operand of a LEFT JOIN, then the outer
121135		- ** query cannot be an aggregate. This is an artifact of the way aggregates
121136		- ** are processed - there is no mechanism to determine if the LEFT JOIN
121137		- ** table should be all-NULL.
	121174	+ ** query cannot be an aggregate. (3c) This is an artifact of the way
	121175	+ ** aggregates are processed - there is no mechanism to determine if
	121176	+ ** the LEFT JOIN table should be all-NULL.
121138	121177	**
121139	121178	** See also tickets #306, #350, and #3300.
121140	121179	*/
121141	121180	if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){
121142	121181	isLeftJoin = 1;
121143	121182	if( pSubSrc->nSrc>1 \|\| isAgg \|\| IsVirtual(pSubSrc->a[0].pTab) ){
121144		- return 0; /* Restriction (3) */
	121183	+ /* (3a) (3c) (3b) */
	121184	+ return 0;
121145	121185	}
121146	121186	}
121147	121187	#ifdef SQLITE_EXTRA_IFNULLROW
121148	121188	else if( iFrom>0 && !isAgg ){
121149	121189	/* Setting isLeftJoin to -1 causes OP_IfNullRow opcodes to be generated for
121150		- ** every reference to any result column from subquery in a join, even though
121151		- ** they are not necessary. This will stress-test the OP_IfNullRow opcode. */
	121190	+ ** every reference to any result column from subquery in a join, even
	121191	+ ** though they are not necessary. This will stress-test the OP_IfNullRow
	121192	+ ** opcode. */
121152	121193	isLeftJoin = -1;
121153	121194	}
121154	121195	#endif
121155	121196
121156		- /* Restriction 17: If the sub-query is a compound SELECT, then it must
	121197	+ /* Restriction (17): If the sub-query is a compound SELECT, then it must
121157	121198	** use only the UNION ALL operator. And none of the simple select queries
121158	121199	** that make up the compound SELECT are allowed to be aggregate or distinct
121159	121200	** queries.
121160	121201	*/
121161	121202	if( pSub->pPrior ){
121162	121203	if( pSub->pOrderBy ){
121163		- return 0; /* Restriction 20 */
	121204	+ return 0; /* Restriction (20) */
121164	121205	}
121165	121206	if( isAgg \|\| (p->selFlags & SF_Distinct)!=0 \|\| pSrc->nSrc!=1 ){
121166		- return 0;
	121207	+ return 0; /* (17d1), (17d2), or (17d3) */
121167	121208	}
121168	121209	for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){
121169	121210	testcase( (pSub1->selFlags & (SF_Distinct\|SF_Aggregate))==SF_Distinct );
121170	121211	testcase( (pSub1->selFlags & (SF_Distinct\|SF_Aggregate))==SF_Aggregate );
121171	121212	assert( pSub->pSrc!=0 );
121172	121213	assert( pSub->pEList->nExpr==pSub1->pEList->nExpr );
121173		- if( (pSub1->selFlags & (SF_Distinct\|SF_Aggregate))!=0
121174		- \|\| (pSub1->pPrior && pSub1->op!=TK_ALL)
121175		- \|\| pSub1->pSrc->nSrc<1
	121214	+ if( (pSub1->selFlags & (SF_Distinct\|SF_Aggregate))!=0 /* (17b) */
	121215	+ \|\| (pSub1->pPrior && pSub1->op!=TK_ALL) /* (17a) */
	121216	+ \|\| pSub1->pSrc->nSrc<1 /* (17c) */
121176	121217	){
121177	121218	return 0;
121178	121219	}
121179	121220	testcase( pSub1->pSrc->nSrc>1 );
121180	121221	}
121181	121222
121182		- /* Restriction 18. */
	121223	+ /* Restriction (18). */
121183	121224	if( p->pOrderBy ){
121184	121225	int ii;
121185	121226	for(ii=0; ii<p->pOrderBy->nExpr; ii++){
121186	121227	if( p->pOrderBy->a[ii].u.x.iOrderByCol==0 ) return 0;
121187	121228	}
		@@ -121398,22 +121439,11 @@
121398	121439	}
121399	121440	pWhere = sqlite3ExprDup(db, pSub->pWhere, 0);
121400	121441	if( isLeftJoin>0 ){
121401	121442	setJoinExpr(pWhere, iNewParent);
121402	121443	}
121403		- if( subqueryIsAgg ){
121404		- assert( pParent->pHaving==0 );
121405		- pParent->pHaving = pParent->pWhere;
121406		- pParent->pWhere = pWhere;
121407		- pParent->pHaving = sqlite3ExprAnd(db,
121408		- sqlite3ExprDup(db, pSub->pHaving, 0), pParent->pHaving
121409		- );
121410		- assert( pParent->pGroupBy==0 );
121411		- pParent->pGroupBy = sqlite3ExprListDup(db, pSub->pGroupBy, 0);
121412		- }else{
121413		- pParent->pWhere = sqlite3ExprAnd(db, pWhere, pParent->pWhere);
121414		- }
	121444	+ pParent->pWhere = sqlite3ExprAnd(db, pWhere, pParent->pWhere);
121415	121445	if( db->mallocFailed==0 ){
121416	121446	SubstContext x;
121417	121447	x.pParse = pParse;
121418	121448	x.iTable = iParent;
121419	121449	x.iNewTable = iNewParent;
		@@ -121472,13 +121502,17 @@
121472	121502	** The hope is that the terms added to the inner query will make it more
121473	121503	** efficient.
121474	121504	**
121475	121505	** Do not attempt this optimization if:
121476	121506	**
121477		-** (1) The inner query is an aggregate. (In that case, we'd really want
121478		-** to copy the outer WHERE-clause terms onto the HAVING clause of the
121479		-** inner query. But they probably won't help there so do not bother.)
	121507	+ (1) ( This restriction was removed on 2017-09-29. We used to
	121508	+** disallow this optimization for aggregate subqueries, but now
	121509	+** it is allowed by putting the extra terms on the HAVING clause.
	121510	+** The added HAVING clause is pointless if the subquery lacks
	121511	+** a GROUP BY clause. But such a HAVING clause is also harmless
	121512	+** so there does not appear to be any reason to add extra logic
	121513	+ to suppress it. )
121480	121514	**
121481	121515	** (2) The inner query is the recursive part of a common table expression.
121482	121516	**
121483	121517	** (3) The inner query has a LIMIT clause (since the changes to the WHERE
121484	121518	** close would change the meaning of the LIMIT).
		@@ -121499,28 +121533,34 @@
121499	121533	Expr pWhere, / The WHERE clause of the outer query */
121500	121534	int iCursor /* Cursor number of the subquery */
121501	121535	){
121502	121536	Expr *pNew;
121503	121537	int nChng = 0;
121504		- Select pX; / For looping over compound SELECTs in pSubq */
121505	121538	if( pWhere==0 ) return 0;
121506		- for(pX=pSubq; pX; pX=pX->pPrior){
121507		- if( (pX->selFlags & (SF_Aggregate\|SF_Recursive))!=0 ){
121508		- testcase( pX->selFlags & SF_Aggregate );
121509		- testcase( pX->selFlags & SF_Recursive );
121510		- testcase( pX!=pSubq );
121511		- return 0; /* restrictions (1) and (2) */
	121539	+ if( pSubq->selFlags & SF_Recursive ) return 0; /* restriction (2) */
	121540	+
	121541	+#ifdef SQLITE_DEBUG
	121542	+ /* Only the first term of a compound can have a WITH clause. But make
	121543	+ ** sure no other terms are marked SF_Recursive in case something changes
	121544	+ ** in the future.
	121545	+ */
	121546	+ {
	121547	+ Select *pX;
	121548	+ for(pX=pSubq; pX; pX=pX->pPrior){
	121549	+ assert( (pX->selFlags & (SF_Recursive))==0 );
121512	121550	}
121513	121551	}
	121552	+#endif
	121553	+
121514	121554	if( pSubq->pLimit!=0 ){
121515	121555	return 0; /* restriction (3) */
121516	121556	}
121517	121557	while( pWhere->op==TK_AND ){
121518	121558	nChng += pushDownWhereTerms(pParse, pSubq, pWhere->pRight, iCursor);
121519	121559	pWhere = pWhere->pLeft;
121520	121560	}
121521		- if( ExprHasProperty(pWhere,EP_FromJoin) ) return 0; /* restriction 5 */
	121561	+ if( ExprHasProperty(pWhere,EP_FromJoin) ) return 0; /* restriction (5) */
121522	121562	if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){
121523	121563	nChng++;
121524	121564	while( pSubq ){
121525	121565	SubstContext x;
121526	121566	pNew = sqlite3ExprDup(pParse->db, pWhere, 0);
		@@ -121528,11 +121568,15 @@
121528	121568	x.iTable = iCursor;
121529	121569	x.iNewTable = iCursor;
121530	121570	x.isLeftJoin = 0;
121531	121571	x.pEList = pSubq->pEList;
121532	121572	pNew = substExpr(&x, pNew);
121533		- pSubq->pWhere = sqlite3ExprAnd(pParse->db, pSubq->pWhere, pNew);
	121573	+ if( pSubq->selFlags & SF_Aggregate ){
	121574	+ pSubq->pHaving = sqlite3ExprAnd(pParse->db, pSubq->pHaving, pNew);
	121575	+ }else{
	121576	+ pSubq->pWhere = sqlite3ExprAnd(pParse->db, pSubq->pWhere, pNew);
	121577	+ }
121534	121578	pSubq = pSubq->pPrior;
121535	121579	}
121536	121580	}
121537	121581	return nChng;
121538	121582	}
		@@ -121856,11 +121900,12 @@
121856	121900	sqlite3ErrorMsg(
121857	121901	pParse, "multiple references to recursive table: %s", pCte->zName
121858	121902	);
121859	121903	return SQLITE_ERROR;
121860	121904	}
121861		- assert( pTab->nTabRef==1 \|\| ((pSel->selFlags&SF_Recursive) && pTab->nTabRef==2 ));
	121905	+ assert( pTab->nTabRef==1 \|\|
	121906	+ ((pSel->selFlags&SF_Recursive) && pTab->nTabRef==2 ));
121862	121907
121863	121908	pCte->zCteErr = "circular reference: %s";
121864	121909	pSavedWith = pParse->pWith;
121865	121910	pParse->pWith = pWith;
121866	121911	if( bMayRecursive ){
		@@ -122000,11 +122045,15 @@
122000	122045	assert( pFrom->pTab==0 );
122001	122046	if( sqlite3WalkSelect(pWalker, pSel) ) return WRC_Abort;
122002	122047	pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
122003	122048	if( pTab==0 ) return WRC_Abort;
122004	122049	pTab->nTabRef = 1;
122005		- pTab->zName = sqlite3MPrintf(db, "sqlite_sq_%p", (void*)pTab);
	122050	+ if( pFrom->zAlias ){
	122051	+ pTab->zName = sqlite3DbStrDup(db, pFrom->zAlias);
	122052	+ }else{
	122053	+ pTab->zName = sqlite3MPrintf(db, "subquery_%p", (void*)pTab);
	122054	+ }
122006	122055	while( pSel->pPrior ){ pSel = pSel->pPrior; }
122007	122056	sqlite3ColumnsFromExprList(pParse, pSel->pEList,&pTab->nCol,&pTab->aCol);
122008	122057	pTab->iPKey = -1;
122009	122058	pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) );
122010	122059	pTab->tabFlags \|= TF_Ephemeral;
		@@ -122660,28 +122709,28 @@
122660	122709	static int countOfViewOptimization(Parse pParse, Select p){
122661	122710	Select pSub, pPrior;
122662	122711	Expr *pExpr;
122663	122712	Expr *pCount;
122664	122713	sqlite3 *db;
122665		- if( (p->selFlags & SF_Aggregate)==0 ) return 0; /* This is an aggregate query */
	122714	+ if( (p->selFlags & SF_Aggregate)==0 ) return 0; /* This is an aggregate */
122666	122715	if( p->pEList->nExpr!=1 ) return 0; /* Single result column */
122667	122716	pExpr = p->pEList->a[0].pExpr;
122668	122717	if( pExpr->op!=TK_AGG_FUNCTION ) return 0; /* Result is an aggregate */
122669		- if( sqlite3_stricmp(pExpr->u.zToken,"count") ) return 0; /* Must be count() */
	122718	+ if( sqlite3_stricmp(pExpr->u.zToken,"count") ) return 0; /* Is count() */
122670	122719	if( pExpr->x.pList!=0 ) return 0; /* Must be count() /
122671		- if( p->pSrc->nSrc!=1 ) return 0; /* One table in the FROM clause */
	122720	+ if( p->pSrc->nSrc!=1 ) return 0; /* One table in FROM */
122672	122721	pSub = p->pSrc->a[0].pSelect;
122673	122722	if( pSub==0 ) return 0; /* The FROM is a subquery */
122674		- if( pSub->pPrior==0 ) return 0; /* Must be a compound subquery */
	122723	+ if( pSub->pPrior==0 ) return 0; /* Must be a compound ry */
122675	122724	do{
122676	122725	if( pSub->op!=TK_ALL && pSub->pPrior ) return 0; /* Must be UNION ALL */
122677	122726	if( pSub->pWhere ) return 0; /* No WHERE clause */
122678	122727	if( pSub->selFlags & SF_Aggregate ) return 0; /* Not an aggregate */
122679		- pSub = pSub->pPrior; /* Repeat over compound terms */
	122728	+ pSub = pSub->pPrior; /* Repeat over compound */
122680	122729	}while( pSub );
122681	122730
122682		- /* If we reach this point, that means it is OK to perform the transformation */
	122731	+ /* If we reach this point then it is OK to perform the transformation */
122683	122732
122684	122733	db = pParse->db;
122685	122734	pCount = pExpr;
122686	122735	pExpr = 0;
122687	122736	pSub = p->pSrc->a[0].pSelect;
		@@ -122817,11 +122866,10 @@
122817	122866	*/
122818	122867	#if !defined(SQLITE_OMIT_SUBQUERY) \|\| !defined(SQLITE_OMIT_VIEW)
122819	122868	for(i=0; !p->pPrior && i<pTabList->nSrc; i++){
122820	122869	struct SrcList_item *pItem = &pTabList->a[i];
122821	122870	Select *pSub = pItem->pSelect;
122822		- int isAggSub;
122823	122871	Table *pTab = pItem->pTab;
122824	122872	if( pSub==0 ) continue;
122825	122873
122826	122874	/* Catch mismatch in the declared columns of a view and the number of
122827	122875	** columns in the SELECT on the RHS */
		@@ -122829,17 +122877,40 @@
122829	122877	sqlite3ErrorMsg(pParse, "expected %d columns for '%s' but got %d",
122830	122878	pTab->nCol, pTab->zName, pSub->pEList->nExpr);
122831	122879	goto select_end;
122832	122880	}
122833	122881
122834		- isAggSub = (pSub->selFlags & SF_Aggregate)!=0;
122835		- if( flattenSubquery(pParse, p, i, isAgg, isAggSub) ){
	122882	+ /* Do not try to flatten an aggregate subquery.
	122883	+ **
	122884	+ ** Flattening an aggregate subquery is only possible if the outer query
	122885	+ ** is not a join. But if the outer query is not a join, then the subquery
	122886	+ ** will be implemented as a co-routine and there is no advantage to
	122887	+ ** flattening in that case.
	122888	+ */
	122889	+ if( (pSub->selFlags & SF_Aggregate)!=0 ) continue;
	122890	+ assert( pSub->pGroupBy==0 );
	122891	+
	122892	+ /* If the subquery contains an ORDER BY clause and if
	122893	+ ** it will be implemented as a co-routine, then do not flatten. This
	122894	+ ** restriction allows SQL constructs like this:
	122895	+ **
	122896	+ ** SELECT expensive_function(x)
	122897	+ ** FROM (SELECT x FROM tab ORDER BY y LIMIT 10);
	122898	+ **
	122899	+ ** The expensive_function() is only computed on the 10 rows that
	122900	+ ** are output, rather than every row of the table.
	122901	+ */
	122902	+ if( pSub->pOrderBy!=0
	122903	+ && i==0
	122904	+ && (pTabList->nSrc==1
	122905	+ \|\| (pTabList->a[1].fg.jointype&(JT_LEFT\|JT_CROSS))!=0)
	122906	+ ){
	122907	+ continue;
	122908	+ }
	122909	+
	122910	+ if( flattenSubquery(pParse, p, i, isAgg) ){
122836	122911	/* This subquery can be absorbed into its parent. */
122837		- if( isAggSub ){
122838		- isAgg = 1;
122839		- p->selFlags \|= SF_Aggregate;
122840		- }
122841	122912	i = -1;
122842	122913	}
122843	122914	pTabList = p->pSrc;
122844	122915	if( db->mallocFailed ) goto select_end;
122845	122916	if( !IgnorableOrderby(pDest) ){
		@@ -122869,25 +122940,29 @@
122869	122940	*/
122870	122941	for(i=0; i<pTabList->nSrc; i++){
122871	122942	struct SrcList_item *pItem = &pTabList->a[i];
122872	122943	SelectDest dest;
122873	122944	Select *pSub;
	122945	+#if !defined(SQLITE_OMIT_SUBQUERY) \|\| !defined(SQLITE_OMIT_VIEW)
	122946	+ const char *zSavedAuthContext;
	122947	+#endif
122874	122948
122875		- /* Issue SQLITE_READ authorizations with a fake column name for any tables that
122876		- ** are referenced but from which no values are extracted. Examples of where these
122877		- ** kinds of null SQLITE_READ authorizations would occur:
	122949	+ /* Issue SQLITE_READ authorizations with a fake column name for any
	122950	+ ** tables that are referenced but from which no values are extracted.
	122951	+ ** Examples of where these kinds of null SQLITE_READ authorizations
	122952	+ ** would occur:
122878	122953	**
122879	122954	** SELECT count(*) FROM t1; -- SQLITE_READ t1.""
122880	122955	** SELECT t1.* FROM t1, t2; -- SQLITE_READ t2.""
122881	122956	**
122882	122957	** The fake column name is an empty string. It is possible for a table to
122883	122958	** have a column named by the empty string, in which case there is no way to
122884	122959	** distinguish between an unreferenced table and an actual reference to the
122885		- ** "" column. The original design was for the fake column name to be a NULL,
	122960	+ ** "" column. The original design was for the fake column name to be a NULL,
122886	122961	** which would be unambiguous. But legacy authorization callbacks might
122887		- ** assume the column name is non-NULL and segfault. The use of an empty string
122888		- ** for the fake column name seems safer.
	122962	+ ** assume the column name is non-NULL and segfault. The use of an empty
	122963	+ ** string for the fake column name seems safer.
122889	122964	*/
122890	122965	if( pItem->colUsed==0 ){
122891	122966	sqlite3AuthCheck(pParse, SQLITE_READ, pItem->zName, "", pItem->zDatabase);
122892	122967	}
122893	122968
		@@ -122934,35 +123009,32 @@
122934	123009	SELECTTRACE(0x100,pParse,p,("After WHERE-clause push-down:\n"));
122935	123010	sqlite3TreeViewSelect(0, p, 0);
122936	123011	}
122937	123012	#endif
122938	123013	}
	123014	+
	123015	+ zSavedAuthContext = pParse->zAuthContext;
	123016	+ pParse->zAuthContext = pItem->zName;
122939	123017
122940	123018	/* Generate code to implement the subquery
122941	123019	**
122942		- ** The subquery is implemented as a co-routine if all of these are true:
122943		- ** (1) The subquery is guaranteed to be the outer loop (so that it
122944		- ** does not need to be computed more than once)
122945		- ** (2) The ALL keyword after SELECT is omitted. (Applications are
122946		- ** allowed to say "SELECT ALL" instead of just "SELECT" to disable
122947		- ** the use of co-routines.)
122948		- ** (3) Co-routines are not disabled using sqlite3_test_control()
122949		- ** with SQLITE_TESTCTRL_OPTIMIZATIONS.
	123020	+ ** The subquery is implemented as a co-routine if the subquery is
	123021	+ ** guaranteed to be the outer loop (so that it does not need to be
	123022	+ ** computed more than once)
122950	123023	**
122951	123024	** TODO: Are there other reasons beside (1) to use a co-routine
122952	123025	** implementation?
122953	123026	*/
122954	123027	if( i==0
122955	123028	&& (pTabList->nSrc==1
122956	123029	\|\| (pTabList->a[1].fg.jointype&(JT_LEFT\|JT_CROSS))!=0) /* (1) */
122957		- && (p->selFlags & SF_All)==0 /* (2) */
122958		- && OptimizationEnabled(db, SQLITE_SubqCoroutine) /* (3) */
122959	123030	){
122960	123031	/* Implement a co-routine that will return a single row of the result
122961	123032	** set on each invocation.
122962	123033	*/
122963	123034	int addrTop = sqlite3VdbeCurrentAddr(v)+1;
	123035	+
122964	123036	pItem->regReturn = ++pParse->nMem;
122965	123037	sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop);
122966	123038	VdbeComment((v, "%s", pItem->pTab->zName));
122967	123039	pItem->addrFillSub = addrTop;
122968	123040	sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn);
		@@ -123016,10 +123088,11 @@
123016	123088	sqlite3VdbeChangeP1(v, topAddr, retAddr);
123017	123089	sqlite3ClearTempRegCache(pParse);
123018	123090	}
123019	123091	if( db->mallocFailed ) goto select_end;
123020	123092	pParse->nHeight -= sqlite3SelectExprHeight(p);
	123093	+ pParse->zAuthContext = zSavedAuthContext;
123021	123094	#endif
123022	123095	}
123023	123096
123024	123097	/* Various elements of the SELECT copied into local variables for
123025	123098	** convenience */
		@@ -131021,11 +131094,10 @@
131021	131094	** returned when it should not be, then incorrect answers might result.
131022	131095	*/
131023	131096	static int termIsEquivalence(Parse pParse, Expr pExpr){
131024	131097	char aff1, aff2;
131025	131098	CollSeq *pColl;
131026		- const char zColl1, zColl2;
131027	131099	if( !OptimizationEnabled(pParse->db, SQLITE_Transitive) ) return 0;
131028	131100	if( pExpr->op!=TK_EQ && pExpr->op!=TK_IS ) return 0;
131029	131101	if( ExprHasProperty(pExpr, EP_FromJoin) ) return 0;
131030	131102	aff1 = sqlite3ExprAffinity(pExpr->pLeft);
131031	131103	aff2 = sqlite3ExprAffinity(pExpr->pRight);
		@@ -131034,15 +131106,11 @@
131034	131106	){
131035	131107	return 0;
131036	131108	}
131037	131109	pColl = sqlite3BinaryCompareCollSeq(pParse, pExpr->pLeft, pExpr->pRight);
131038	131110	if( pColl==0 \|\| sqlite3StrICmp(pColl->zName, "BINARY")==0 ) return 1;
131039		- pColl = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
131040		- zColl1 = pColl ? pColl->zName : 0;
131041		- pColl = sqlite3ExprCollSeq(pParse, pExpr->pRight);
131042		- zColl2 = pColl ? pColl->zName : 0;
131043		- return sqlite3_stricmp(zColl1, zColl2)==0;
	131111	+ return sqlite3ExprCollSeqMatch(pParse, pExpr->pLeft, pExpr->pRight);
131044	131112	}
131045	131113
131046	131114	/*
131047	131115	** Recursively walk the expressions of a SELECT statement and generate
131048	131116	** a bitmask indicating which tables are used in that expression
		@@ -132119,12 +132187,12 @@
132119	132187	Expr *p = sqlite3ExprSkipCollate(pList->a[i].pExpr);
132120	132188	if( p->op==TK_COLUMN
132121	132189	&& p->iColumn==pIdx->aiColumn[iCol]
132122	132190	&& p->iTable==iBase
132123	132191	){
132124		- CollSeq *pColl = sqlite3ExprCollSeq(pParse, pList->a[i].pExpr);
132125		- if( pColl && 0==sqlite3StrICmp(pColl->zName, zColl) ){
	132192	+ CollSeq *pColl = sqlite3ExprNNCollSeq(pParse, pList->a[i].pExpr);
	132193	+ if( 0==sqlite3StrICmp(pColl->zName, zColl) ){
132126	132194	return i;
132127	132195	}
132128	132196	}
132129	132197	}
132130	132198
		@@ -134385,11 +134453,11 @@
134385	134453	if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1;
134386	134454	}
134387	134455	}else if( (aColExpr = pIndex->aColExpr)!=0 ){
134388	134456	for(jj=0; jj<pIndex->nKeyCol; jj++){
134389	134457	if( pIndex->aiColumn[jj]!=XN_EXPR ) continue;
134390		- if( sqlite3ExprCompare(0, pExpr,aColExpr->a[jj].pExpr,iCursor)==0 ){
	134458	+ if( sqlite3ExprCompareSkip(pExpr,aColExpr->a[jj].pExpr,iCursor)==0 ){
134391	134459	return 1;
134392	134460	}
134393	134461	}
134394	134462	}
134395	134463	}
		@@ -135295,18 +135363,14 @@
135295	135363	assert( wctrlFlags & WHERE_ORDERBY_LIMIT );
135296	135364	for(j=0; j<pLoop->nLTerm && pTerm!=pLoop->aLTerm[j]; j++){}
135297	135365	if( j>=pLoop->nLTerm ) continue;
135298	135366	}
135299	135367	if( (pTerm->eOperator&(WO_EQ\|WO_IS))!=0 && pOBExpr->iColumn>=0 ){
135300		- const char z1, z2;
135301		- pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
135302		- if( !pColl ) pColl = db->pDfltColl;
135303		- z1 = pColl->zName;
135304		- pColl = sqlite3ExprCollSeq(pWInfo->pParse, pTerm->pExpr);
135305		- if( !pColl ) pColl = db->pDfltColl;
135306		- z2 = pColl->zName;
135307		- if( sqlite3StrICmp(z1, z2)!=0 ) continue;
	135368	+ if( sqlite3ExprCollSeqMatch(pWInfo->pParse,
	135369	+ pOrderBy->a[i].pExpr, pTerm->pExpr)==0 ){
	135370	+ continue;
	135371	+ }
135308	135372	testcase( pTerm->pExpr->op==TK_IS );
135309	135373	}
135310	135374	obSat \|= MASKBIT(i);
135311	135375	}
135312	135376
		@@ -135374,11 +135438,11 @@
135374	135438	** (revIdx) for the j-th column of the index.
135375	135439	*/
135376	135440	if( pIndex ){
135377	135441	iColumn = pIndex->aiColumn[j];
135378	135442	revIdx = pIndex->aSortOrder[j];
135379		- if( iColumn==pIndex->pTable->iPKey ) iColumn = -1;
	135443	+ if( iColumn==pIndex->pTable->iPKey ) iColumn = XN_ROWID;
135380	135444	}else{
135381	135445	iColumn = XN_ROWID;
135382	135446	revIdx = 0;
135383	135447	}
135384	135448
		@@ -135401,23 +135465,22 @@
135401	135465	if( MASKBIT(i) & obSat ) continue;
135402	135466	pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
135403	135467	testcase( wctrlFlags & WHERE_GROUPBY );
135404	135468	testcase( wctrlFlags & WHERE_DISTINCTBY );
135405	135469	if( (wctrlFlags & (WHERE_GROUPBY\|WHERE_DISTINCTBY))==0 ) bOnce = 0;
135406		- if( iColumn>=(-1) ){
	135470	+ if( iColumn>=XN_ROWID ){
135407	135471	if( pOBExpr->op!=TK_COLUMN ) continue;
135408	135472	if( pOBExpr->iTable!=iCur ) continue;
135409	135473	if( pOBExpr->iColumn!=iColumn ) continue;
135410	135474	}else{
135411		- if( sqlite3ExprCompare(0,
135412		- pOBExpr,pIndex->aColExpr->a[j].pExpr,iCur) ){
	135475	+ Expr *pIdxExpr = pIndex->aColExpr->a[j].pExpr;
	135476	+ if( sqlite3ExprCompareSkip(pOBExpr, pIdxExpr, iCur) ){
135413	135477	continue;
135414	135478	}
135415	135479	}
135416	135480	if( iColumn!=XN_ROWID ){
135417		- pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
135418		- if( !pColl ) pColl = db->pDfltColl;
	135481	+ pColl = sqlite3ExprNNCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
135419	135482	if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
135420	135483	}
135421	135484	pLoop->u.btree.nIdxCol = j+1;
135422	135485	isMatch = 1;
135423	135486	break;
		@@ -137095,11 +137158,12 @@
137095	137158	** YYNSTATE the combined number of states.
137096	137159	** YYNRULE the number of rules in the grammar
137097	137160	** YY_MAX_SHIFT Maximum value for shift actions
137098	137161	** YY_MIN_SHIFTREDUCE Minimum value for shift-reduce actions
137099	137162	** YY_MAX_SHIFTREDUCE Maximum value for shift-reduce actions
137100		-** YY_MIN_REDUCE Maximum value for reduce actions
	137163	+** YY_MIN_REDUCE Minimum value for reduce actions
	137164	+** YY_MAX_REDUCE Maximum value for reduce actions
137101	137165	** YY_ERROR_ACTION The yy_action[] code for syntax error
137102	137166	** YY_ACCEPT_ACTION The yy_action[] code for accept
137103	137167	** YY_NO_ACTION The yy_action[] code for no-op
137104	137168	*/
137105	137169	#ifndef INTERFACE
		@@ -184892,11 +184956,12 @@
184892	184956	** fts5YYNSTATE the combined number of states.
184893	184957	** fts5YYNRULE the number of rules in the grammar
184894	184958	** fts5YY_MAX_SHIFT Maximum value for shift actions
184895	184959	** fts5YY_MIN_SHIFTREDUCE Minimum value for shift-reduce actions
184896	184960	** fts5YY_MAX_SHIFTREDUCE Maximum value for shift-reduce actions
184897		-** fts5YY_MIN_REDUCE Maximum value for reduce actions
	184961	+** fts5YY_MIN_REDUCE Minimum value for reduce actions
	184962	+** fts5YY_MAX_REDUCE Maximum value for reduce actions
184898	184963	** fts5YY_ERROR_ACTION The fts5yy_action[] code for syntax error
184899	184964	** fts5YY_ACCEPT_ACTION The fts5yy_action[] code for accept
184900	184965	** fts5YY_NO_ACTION The fts5yy_action[] code for no-op
184901	184966	*/
184902	184967	#ifndef INTERFACE
		@@ -200632,11 +200697,11 @@
200632	200697	int nArg, /* Number of args */
200633	200698	sqlite3_value *apUnused / Function arguments */
200634	200699	){
200635	200700	assert( nArg==0 );
200636	200701	UNUSED_PARAM2(nArg, apUnused);
200637		- sqlite3_result_text(pCtx, "fts5: 2017-09-21 20:43:48 5d03c738e93d36815248991d9ed3d62297ba1bb966e602e7874410076c144f43", -1, SQLITE_TRANSIENT);
	200702	+ sqlite3_result_text(pCtx, "fts5: 2017-09-29 16:07:56 0840f9f824c16212ce3fd6c859e501176eb0a58924ea1728a54d5bdfd0c25c86", -1, SQLITE_TRANSIENT);
200638	200703	}
200639	200704
200640	200705	static int fts5Init(sqlite3 *db){
200641	200706	static const sqlite3_module fts5Mod = {
200642	200707	/* iVersion */ 2,
		@@ -204901,12 +204966,12 @@
204901	204966	}
204902	204967	#endif /* SQLITE_CORE */
204903	204968	#endif /* !defined(SQLITE_CORE) \|\| defined(SQLITE_ENABLE_STMTVTAB) */
204904	204969
204905	204970	/************ End of stmt.c **********************************************/
204906		-#if __LINE__!=204906
	204971	+#if __LINE__!=204971
204907	204972	#undef SQLITE_SOURCE_ID
204908		-#define SQLITE_SOURCE_ID "2017-09-21 20:43:48 5d03c738e93d36815248991d9ed3d62297ba1bb966e602e7874410076c14alt2"
	204973	+#define SQLITE_SOURCE_ID "2017-10-02 02:52:54 c9104b59c7ed360291f7f6fc8caae938e9840c77620d598e4096f78183bfalt2"
204909	204974	#endif
204910	204975	/* Return the source-id for this library */
204911	204976	SQLITE_API const char *sqlite3_sourceid(void){ return SQLITE_SOURCE_ID; }
204912	204977	/************************ End of sqlite3.c ****************************/
204913	204978

	--- src/sqlite3.c
	+++ src/sqlite3.c
	@@ -1147,11 +1147,11 @@
1147	** [sqlite3_libversion_number()], [sqlite3_sourceid()],
1148	** [sqlite_version()] and [sqlite_source_id()].
1149	*/
1150	#define SQLITE_VERSION "3.21.0"
1151	#define SQLITE_VERSION_NUMBER 3021000
1152	#define SQLITE_SOURCE_ID "2017-09-21 20:43:48 5d03c738e93d36815248991d9ed3d62297ba1bb966e602e7874410076c144f43"
1153
1154	/*
1155	** CAPI3REF: Run-Time Library Version Numbers
1156	** KEYWORDS: sqlite3_version sqlite3_sourceid
1157	**
	@@ -15279,20 +15279,19 @@
15279	*/
15280	#define SQLITE_QueryFlattener 0x0001 /* Query flattening */
15281	#define SQLITE_ColumnCache 0x0002 /* Column cache */
15282	#define SQLITE_GroupByOrder 0x0004 /* GROUPBY cover of ORDERBY */
15283	#define SQLITE_FactorOutConst 0x0008 /* Constant factoring */
15284	/* not used 0x0010 // Was: SQLITE_IdxRealAsInt */
15285	#define SQLITE_DistinctOpt 0x0020 /* DISTINCT using indexes */
15286	#define SQLITE_CoverIdxScan 0x0040 /* Covering index scans */
15287	#define SQLITE_OrderByIdxJoin 0x0080 /* ORDER BY of joins via index */
15288	#define SQLITE_SubqCoroutine 0x0100 /* Evaluate subqueries as coroutines */
15289	#define SQLITE_Transitive 0x0200 /* Transitive constraints */
15290	#define SQLITE_OmitNoopJoin 0x0400 /* Omit unused tables in joins */
15291	#define SQLITE_Stat34 0x0800 /* Use STAT3 or STAT4 data */
15292	#define SQLITE_CountOfView 0x1000 /* The count-of-view optimization */
15293	#define SQLITE_CursorHints 0x2000 /* Add OP_CursorHint opcodes */
15294	#define SQLITE_AllOpts 0xffff /* All optimizations */
15295
15296	/*
15297	** Macros for testing whether or not optimizations are enabled or disabled.
15298	*/
	@@ -17777,10 +17776,12 @@
17777	SQLITE_PRIVATE const char *sqlite3ErrStr(int);
17778	SQLITE_PRIVATE int sqlite3ReadSchema(Parse *pParse);
17779	SQLITE_PRIVATE CollSeq sqlite3FindCollSeq(sqlite3,u8 enc, const char*,int);
17780	SQLITE_PRIVATE CollSeq sqlite3LocateCollSeq(Parse pParse, const char*zName);
17781	SQLITE_PRIVATE CollSeq sqlite3ExprCollSeq(Parse pParse, Expr *pExpr);


17782	SQLITE_PRIVATE Expr sqlite3ExprAddCollateToken(Parse pParse, Expr, const Token, int);
17783	SQLITE_PRIVATE Expr sqlite3ExprAddCollateString(Parse,Expr,const char);
17784	SQLITE_PRIVATE Expr sqlite3ExprSkipCollate(Expr);
17785	SQLITE_PRIVATE int sqlite3CheckCollSeq(Parse , CollSeq );
17786	SQLITE_PRIVATE int sqlite3CheckObjectName(Parse , const char );
	@@ -43282,18 +43283,23 @@
43282	dwFlagsAndAttributes & FILE_ATTRIBUTE_MASK;
43283	extendedParameters.dwFileFlags = dwFlagsAndAttributes & FILE_FLAG_MASK;
43284	extendedParameters.dwSecurityQosFlags = SECURITY_ANONYMOUS;
43285	extendedParameters.lpSecurityAttributes = NULL;
43286	extendedParameters.hTemplateFile = NULL;
43287	while( (h = osCreateFile2((LPCWSTR)zConverted,
43288	dwDesiredAccess,
43289	dwShareMode,
43290	dwCreationDisposition,
43291	&extendedParameters))==INVALID_HANDLE_VALUE &&
43292	winRetryIoerr(&cnt, &lastErrno) ){
43293	/* Noop */
43294	}





43295	#else
43296	do{
43297	h = osCreateFileW((LPCWSTR)zConverted,
43298	dwDesiredAccess,
43299	dwShareMode, NULL,
	@@ -43309,19 +43315,24 @@
43309	}while( winRetryIoerr(&cnt, &lastErrno) );
43310	#endif
43311	}
43312	#ifdef SQLITE_WIN32_HAS_ANSI
43313	else{
43314	while( (h = osCreateFileA((LPCSTR)zConverted,
43315	dwDesiredAccess,
43316	dwShareMode, NULL,
43317	dwCreationDisposition,
43318	dwFlagsAndAttributes,
43319	NULL))==INVALID_HANDLE_VALUE &&
43320	winRetryIoerr(&cnt, &lastErrno) ){
43321	/* Noop */
43322	}





43323	}
43324	#endif
43325	winLogIoerr(cnt, __LINE__);
43326
43327	OSTRACE(("OPEN file=%p, name=%s, access=%lx, rc=%s\n", h, zUtf8Name,
	@@ -61031,10 +61042,13 @@
61031	u8 pEnd = &data[cellOffset + nCell2];
61032	u8 *pAddr;
61033	int sz2 = 0;
61034	int sz = get2byte(&data[iFree+2]);
61035	int top = get2byte(&data[hdr+5]);



61036	if( iFree2 ){
61037	assert( iFree+sz<=iFree2 ); /* Verified by pageFindSlot() */
61038	sz2 = get2byte(&data[iFree2+2]);
61039	assert( iFree+sz+sz2+iFree2-(iFree+sz) <= usableSize );
61040	memmove(&data[iFree+sz+sz2], &data[iFree+sz], iFree2-(iFree+sz));
	@@ -71383,13 +71397,14 @@
71383	u32 amt, /* Number of bytes to return. */
71384	Mem pMem / OUT: Return data in this Mem structure. */
71385	){
71386	int rc;
71387	pMem->flags = MEM_Null;
71388	if( SQLITE_OK==(rc = sqlite3VdbeMemClearAndResize(pMem, amt)) ){
71389	rc = sqlite3BtreePayload(pCur, offset, amt, pMem->z);
71390	if( rc==SQLITE_OK ){

71391	pMem->flags = MEM_Blob;
71392	pMem->n = (int)amt;
71393	}else{
71394	sqlite3VdbeMemRelease(pMem);
71395	}
	@@ -91882,10 +91897,15 @@
91882
91883	/*
91884	** Return the collation sequence for the expression pExpr. If
91885	** there is no defined collating sequence, return NULL.
91886	**





91887	** The collating sequence might be determined by a COLLATE operator
91888	** or by the presence of a column with a defined collating sequence.
91889	** COLLATE operators take first precedence. Left operands take
91890	** precedence over right operands.
91891	*/
	@@ -91945,10 +91965,36 @@
91945	if( sqlite3CheckCollSeq(pParse, pColl) ){
91946	pColl = 0;
91947	}
91948	return pColl;
91949	}


























91950
91951	/*
91952	** pExpr is an operand of a comparison operator. aff2 is the
91953	** type affinity of the other operand. This routine returns the
91954	** type affinity that should be used for the comparison operator.
	@@ -93439,16 +93485,15 @@
93439	** ExprList.
93440	*/
93441	SQLITE_PRIVATE u32 sqlite3ExprListFlags(const ExprList *pList){
93442	int i;
93443	u32 m = 0;
93444	if( pList ){
93445	for(i=0; i<pList->nExpr; i++){
93446	Expr *pExpr = pList->a[i].pExpr;
93447	assert( pExpr!=0 );
93448	m \|= pExpr->flags;
93449	}
93450	}
93451	return m;
93452	}
93453
93454	/*
	@@ -93601,12 +93646,12 @@
93601	/* Check if pExpr is identical to any GROUP BY term. If so, consider
93602	** it constant. */
93603	for(i=0; i<pGroupBy->nExpr; i++){
93604	Expr *p = pGroupBy->a[i].pExpr;
93605	if( sqlite3ExprCompare(0, pExpr, p, -1)<2 ){
93606	CollSeq *pColl = sqlite3ExprCollSeq(pWalker->pParse, p);
93607	if( pColl==0 \|\| sqlite3_stricmp("BINARY", pColl->zName)==0 ){
93608	return WRC_Prune;
93609	}
93610	}
93611	}
93612
	@@ -117746,11 +117791,12 @@
117746	if( pNew==0 ){
117747	assert( pParse->db->mallocFailed );
117748	pNew = &standin;
117749	}
117750	if( pEList==0 ){
117751	pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(pParse->db,TK_ASTERISK,0));

117752	}
117753	pNew->pEList = pEList;
117754	pNew->op = TK_SELECT;
117755	pNew->selFlags = selFlags;
117756	pNew->iLimit = 0;
	@@ -117770,11 +117816,12 @@
117770	pNew->pPrior = 0;
117771	pNew->pNext = 0;
117772	pNew->pLimit = pLimit;
117773	pNew->pOffset = pOffset;
117774	pNew->pWith = 0;
117775	assert( pOffset==0 \|\| pLimit!=0 \|\| pParse->nErr>0 \|\| pParse->db->mallocFailed!=0 );

117776	if( pParse->db->mallocFailed ) {
117777	clearSelect(pParse->db, pNew, pNew!=&standin);
117778	pNew = 0;
117779	}else{
117780	assert( pNew->pSrc!=0 \|\| pParse->nErr>0 );
	@@ -118387,11 +118434,12 @@
118387	}
118388	regOrig = 0;
118389	assert( eDest==SRT_Set \|\| eDest==SRT_Mem
118390	\|\| eDest==SRT_Coroutine \|\| eDest==SRT_Output );
118391	}
118392	nResultCol = sqlite3ExprCodeExprList(pParse,p->pEList,regResult,0,ecelFlags);

118393	}
118394
118395	/* If the DISTINCT keyword was present on the SELECT statement
118396	** and this row has been seen before, then do not make this row
118397	** part of the result.
	@@ -118737,14 +118785,11 @@
118737	nExpr = pList->nExpr;
118738	pInfo = sqlite3KeyInfoAlloc(db, nExpr-iStart, nExtra+1);
118739	if( pInfo ){
118740	assert( sqlite3KeyInfoIsWriteable(pInfo) );
118741	for(i=iStart, pItem=pList->a+iStart; i<nExpr; i++, pItem++){
118742	CollSeq *pColl;
118743	pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr);
118744	if( !pColl ) pColl = db->pDfltColl;
118745	pInfo->aColl[i-iStart] = pColl;
118746	pInfo->aSortOrder[i-iStart] = pItem->sortOrder;
118747	}
118748	}
118749	return pInfo;
118750	}
	@@ -119201,13 +119246,13 @@
119201	** short=OFF, full=OFF: Column name is the text of the expression has it
119202	** originally appears in the SELECT statement. In
119203	** other words, the zSpan of the result expression.
119204	**
119205	** short=ON, full=OFF: (This is the default setting). If the result
119206	** refers directly to a table column, then the result
119207	** column name is just the table column name: COLUMN.
119208	** Otherwise use zSpan.
119209	**
119210	** full=ON, short=ANY: If the result refers directly to a table column,
119211	** then the result column name with the table name
119212	** prefix, ex: TABLE.COLUMN. Otherwise use zSpan.
119213	*/
	@@ -119245,11 +119290,11 @@
119245	for(i=0; i<pEList->nExpr; i++){
119246	Expr *p = pEList->a[i].pExpr;
119247
119248	assert( p!=0 );
119249	assert( p->op!=TK_AGG_COLUMN ); /* Agg processing has not run yet */
119250	assert( p->op!=TK_COLUMN \|\| p->pTab!=0 ); /* Covering indexes not yet coded */
119251	if( pEList->a[i].zName ){
119252	/* An AS clause always takes first priority */
119253	char *zName = pEList->a[i].zName;
119254	sqlite3VdbeSetColName(v, i, COLNAME_NAME, zName, SQLITE_TRANSIENT);
119255	}else if( srcName && p->op==TK_COLUMN ){
	@@ -120809,11 +120854,13 @@
120809	static Expr *substExpr(
120810	SubstContext pSubst, / Description of the substitution */
120811	Expr pExpr / Expr in which substitution occurs */
120812	){
120813	if( pExpr==0 ) return 0;
120814	if( ExprHasProperty(pExpr, EP_FromJoin) && pExpr->iRightJoinTable==pSubst->iTable ){


120815	pExpr->iRightJoinTable = pSubst->iNewTable;
120816	}
120817	if( pExpr->op==TK_COLUMN && pExpr->iTable==pSubst->iTable ){
120818	if( pExpr->iColumn<0 ){
120819	pExpr->op = TK_NULL;
	@@ -120922,72 +120969,78 @@
120922	** The code generated for this simplification gives the same result
120923	** but only has to scan the data once. And because indices might
120924	** exist on the table t1, a complete scan of the data might be
120925	** avoided.
120926	**
120927	** Flattening is only attempted if all of the following are true:
120928	**
120929	** (1) The subquery and the outer query do not both use aggregates.
120930	**
120931	** (2) The subquery is not an aggregate or (2a) the outer query is not a join
120932	** and (2b) the outer query does not use subqueries other than the one
120933	** FROM-clause subquery that is a candidate for flattening. (2b is
120934	** due to ticket [2f7170d73bf9abf80] from 2015-02-09.)
120935	**
120936	** (3) The subquery is not the right operand of a LEFT JOIN
120937	** or (a) the subquery is not itself a join and (b) the FROM clause
120938	** of the subquery does not contain a virtual table and (c) the
120939	** outer query is not an aggregate.
120940	**
120941	** (4) The subquery is not DISTINCT.





120942	**
120943	() At one point restrictions (4) and (5) defined a subset of DISTINCT
120944	** sub-queries that were excluded from this optimization. Restriction
120945	** (4) has since been expanded to exclude all DISTINCT subqueries.
120946	**
120947	** (6) The subquery does not use aggregates or the outer query is not
120948	** DISTINCT.
120949	**
120950	** (7) The subquery has a FROM clause. TODO: For subqueries without
120951	** A FROM clause, consider adding a FROM clause with the special
120952	** table sqlite_once that consists of a single row containing a
120953	** single NULL.
120954	**
120955	** (8) The subquery does not use LIMIT or the outer query is not a join.
120956	**
120957	** (9) The subquery does not use LIMIT or the outer query does not use
120958	** aggregates.
120959	**
120960	() Restriction (10) was removed from the code on 2005-02-05 but we
120961	** accidently carried the comment forward until 2014-09-15. Original
120962	** text: "The subquery does not use aggregates or the outer query
120963	** does not use LIMIT."
120964	**
120965	** (11) The subquery and the outer query do not both have ORDER BY clauses.
120966	**
120967	() Not implemented. Subsumed into restriction (3). Was previously
120968	** a separate restriction deriving from ticket #350.
120969	**
120970	** (13) The subquery and outer query do not both use LIMIT.
120971	**
120972	** (14) The subquery does not use OFFSET.
120973	**
120974	** (15) The outer query is not part of a compound select or the
120975	** subquery does not have a LIMIT clause.
120976	** (See ticket #2339 and ticket [02a8e81d44]).
120977	**
120978	** (16) The outer query is not an aggregate or the subquery does
120979	** not contain ORDER BY. (Ticket #2942) This used to not matter
120980	** until we introduced the group_concat() function.
120981	**
120982	** (17) The sub-query is not a compound select, or it is a UNION ALL
120983	** compound clause made up entirely of non-aggregate queries, and
120984	** the parent query:
120985	**
120986	** * is not itself part of a compound select,
120987	** * is not an aggregate or DISTINCT query, and
120988	** * is not a join


120989	**
120990	** The parent and sub-query may contain WHERE clauses. Subject to
120991	** rules (11), (13) and (14), they may also contain ORDER BY,
120992	** LIMIT and OFFSET clauses. The subquery cannot use any compound
120993	** operator other than UNION ALL because all the other compound
	@@ -120999,41 +121052,42 @@
120999	** SELECT statement, but all the code here does is make sure that no
121000	** such (illegal) sub-query is flattened. The caller will detect the
121001	** syntax error and return a detailed message.
121002	**
121003	** (18) If the sub-query is a compound select, then all terms of the
121004	** ORDER by clause of the parent must be simple references to
121005	** columns of the sub-query.
121006	**
121007	** (19) The subquery does not use LIMIT or the outer query does not
121008	** have a WHERE clause.
121009	**
121010	** (20) If the sub-query is a compound select, then it must not use
121011	** an ORDER BY clause. Ticket #3773. We could relax this constraint
121012	** somewhat by saying that the terms of the ORDER BY clause must
121013	** appear as unmodified result columns in the outer query. But we
121014	** have other optimizations in mind to deal with that case.
121015	**
121016	** (21) The subquery does not use LIMIT or the outer query is not
121017	** DISTINCT. (See ticket [752e1646fc]).
121018	**
121019	** (22) The subquery is not a recursive CTE.
121020	**
121021	** (23) The parent is not a recursive CTE, or the sub-query is not a
121022	** compound query. This restriction is because transforming the
121023	** parent to a compound query confuses the code that handles
121024	** recursive queries in multiSelect().
121025	**
121026	** (24) The subquery is not an aggregate that uses the built-in min() or

121027	** or max() functions. (Without this restriction, a query like:
121028	** "SELECT x FROM (SELECT max(y), x FROM t1)" would not necessarily
121029	** return the value X for which Y was maximal.)
121030	**
121031	**
121032	** In this routine, the "p" parameter is a pointer to the outer query.
121033	** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query
121034	** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
121035	**
121036	** If flattening is not attempted, this routine is a no-op and returns 0.
121037	** If flattening is attempted this routine returns 1.
121038	**
121039	** All of the expression analysis must occur on both the outer query and
	@@ -121041,12 +121095,11 @@
121041	*/
121042	static int flattenSubquery(
121043	Parse pParse, / Parsing context */
121044	Select p, / The parent or outer SELECT statement */
121045	int iFrom, /* Index in p->pSrc->a[] of the inner subquery */
121046	int isAgg, /* True if outer SELECT uses aggregate functions */
121047	int subqueryIsAgg /* True if the subquery uses aggregate functions */
121048	){
121049	const char *zSavedAuthContext = pParse->zAuthContext;
121050	Select pParent; / Current UNION ALL term of the other query */
121051	Select pSub; / The inner query or "subquery" */
121052	Select pSub1; / Pointer to the rightmost select in sub-query */
	@@ -121061,28 +121114,18 @@
121061	sqlite3 *db = pParse->db;
121062
121063	/* Check to see if flattening is permitted. Return 0 if not.
121064	*/
121065	assert( p!=0 );
121066	assert( p->pPrior==0 ); /* Unable to flatten compound queries */
121067	if( OptimizationDisabled(db, SQLITE_QueryFlattener) ) return 0;
121068	pSrc = p->pSrc;
121069	assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
121070	pSubitem = &pSrc->a[iFrom];
121071	iParent = pSubitem->iCursor;
121072	pSub = pSubitem->pSelect;
121073	assert( pSub!=0 );
121074	if( subqueryIsAgg ){
121075	if( isAgg ) return 0; /* Restriction (1) */
121076	if( pSrc->nSrc>1 ) return 0; /* Restriction (2a) */
121077	if( (p->pWhere && ExprHasProperty(p->pWhere,EP_Subquery))
121078	\|\| (sqlite3ExprListFlags(p->pEList) & EP_Subquery)!=0
121079	\|\| (sqlite3ExprListFlags(p->pOrderBy) & EP_Subquery)!=0
121080	){
121081	return 0; /* Restriction (2b) */
121082	}
121083	}
121084
121085	pSubSrc = pSub->pSrc;
121086	assert( pSubSrc );
121087	/* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
121088	** not arbitrary expressions, we allowed some combining of LIMIT and OFFSET
	@@ -121093,37 +121136,33 @@
121093	if( pSub->pOffset ) return 0; /* Restriction (14) */
121094	if( (p->selFlags & SF_Compound)!=0 && pSub->pLimit ){
121095	return 0; /* Restriction (15) */
121096	}
121097	if( pSubSrc->nSrc==0 ) return 0; /* Restriction (7) */
121098	if( pSub->selFlags & SF_Distinct ) return 0; /* Restriction (5) */
121099	if( pSub->pLimit && (pSrc->nSrc>1 \|\| isAgg) ){
121100	return 0; /* Restrictions (8)(9) */
121101	}
121102	if( (p->selFlags & SF_Distinct)!=0 && subqueryIsAgg ){
121103	return 0; /* Restriction (6) */
121104	}
121105	if( p->pOrderBy && pSub->pOrderBy ){
121106	return 0; /* Restriction (11) */
121107	}
121108	if( isAgg && pSub->pOrderBy ) return 0; /* Restriction (16) */
121109	if( pSub->pLimit && p->pWhere ) return 0; /* Restriction (19) */
121110	if( pSub->pLimit && (p->selFlags & SF_Distinct)!=0 ){
121111	return 0; /* Restriction (21) */
121112	}
121113	testcase( pSub->selFlags & SF_Recursive );
121114	testcase( pSub->selFlags & SF_MinMaxAgg );
121115	if( pSub->selFlags & (SF_Recursive\|SF_MinMaxAgg) ){
121116	return 0; /* Restrictions (22) and (24) */
121117	}
121118	if( (p->selFlags & SF_Recursive) && pSub->pPrior ){
121119	return 0; /* Restriction (23) */
121120	}
121121
121122	/*
121123	** If the subquery is the right operand of a LEFT JOIN, then the
121124	** subquery may not be a join itself. Example of why this is not allowed:

121125	**
121126	** t1 LEFT OUTER JOIN (t2 JOIN t3)
121127	**
121128	** If we flatten the above, we would get
121129	**
	@@ -121130,58 +121169,60 @@
121130	** (t1 LEFT OUTER JOIN t2) JOIN t3
121131	**
121132	** which is not at all the same thing.
121133	**
121134	** If the subquery is the right operand of a LEFT JOIN, then the outer
121135	** query cannot be an aggregate. This is an artifact of the way aggregates
121136	** are processed - there is no mechanism to determine if the LEFT JOIN
121137	** table should be all-NULL.
121138	**
121139	** See also tickets #306, #350, and #3300.
121140	*/
121141	if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){
121142	isLeftJoin = 1;
121143	if( pSubSrc->nSrc>1 \|\| isAgg \|\| IsVirtual(pSubSrc->a[0].pTab) ){
121144	return 0; /* Restriction (3) */

121145	}
121146	}
121147	#ifdef SQLITE_EXTRA_IFNULLROW
121148	else if( iFrom>0 && !isAgg ){
121149	/* Setting isLeftJoin to -1 causes OP_IfNullRow opcodes to be generated for
121150	** every reference to any result column from subquery in a join, even though
121151	** they are not necessary. This will stress-test the OP_IfNullRow opcode. */

121152	isLeftJoin = -1;
121153	}
121154	#endif
121155
121156	/* Restriction 17: If the sub-query is a compound SELECT, then it must
121157	** use only the UNION ALL operator. And none of the simple select queries
121158	** that make up the compound SELECT are allowed to be aggregate or distinct
121159	** queries.
121160	*/
121161	if( pSub->pPrior ){
121162	if( pSub->pOrderBy ){
121163	return 0; /* Restriction 20 */
121164	}
121165	if( isAgg \|\| (p->selFlags & SF_Distinct)!=0 \|\| pSrc->nSrc!=1 ){
121166	return 0;
121167	}
121168	for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){
121169	testcase( (pSub1->selFlags & (SF_Distinct\|SF_Aggregate))==SF_Distinct );
121170	testcase( (pSub1->selFlags & (SF_Distinct\|SF_Aggregate))==SF_Aggregate );
121171	assert( pSub->pSrc!=0 );
121172	assert( pSub->pEList->nExpr==pSub1->pEList->nExpr );
121173	if( (pSub1->selFlags & (SF_Distinct\|SF_Aggregate))!=0
121174	\|\| (pSub1->pPrior && pSub1->op!=TK_ALL)
121175	\|\| pSub1->pSrc->nSrc<1
121176	){
121177	return 0;
121178	}
121179	testcase( pSub1->pSrc->nSrc>1 );
121180	}
121181
121182	/* Restriction 18. */
121183	if( p->pOrderBy ){
121184	int ii;
121185	for(ii=0; ii<p->pOrderBy->nExpr; ii++){
121186	if( p->pOrderBy->a[ii].u.x.iOrderByCol==0 ) return 0;
121187	}
	@@ -121398,22 +121439,11 @@
121398	}
121399	pWhere = sqlite3ExprDup(db, pSub->pWhere, 0);
121400	if( isLeftJoin>0 ){
121401	setJoinExpr(pWhere, iNewParent);
121402	}
121403	if( subqueryIsAgg ){
121404	assert( pParent->pHaving==0 );
121405	pParent->pHaving = pParent->pWhere;
121406	pParent->pWhere = pWhere;
121407	pParent->pHaving = sqlite3ExprAnd(db,
121408	sqlite3ExprDup(db, pSub->pHaving, 0), pParent->pHaving
121409	);
121410	assert( pParent->pGroupBy==0 );
121411	pParent->pGroupBy = sqlite3ExprListDup(db, pSub->pGroupBy, 0);
121412	}else{
121413	pParent->pWhere = sqlite3ExprAnd(db, pWhere, pParent->pWhere);
121414	}
121415	if( db->mallocFailed==0 ){
121416	SubstContext x;
121417	x.pParse = pParse;
121418	x.iTable = iParent;
121419	x.iNewTable = iNewParent;
	@@ -121472,13 +121502,17 @@
121472	** The hope is that the terms added to the inner query will make it more
121473	** efficient.
121474	**
121475	** Do not attempt this optimization if:
121476	**
121477	** (1) The inner query is an aggregate. (In that case, we'd really want
121478	** to copy the outer WHERE-clause terms onto the HAVING clause of the
121479	** inner query. But they probably won't help there so do not bother.)




121480	**
121481	** (2) The inner query is the recursive part of a common table expression.
121482	**
121483	** (3) The inner query has a LIMIT clause (since the changes to the WHERE
121484	** close would change the meaning of the LIMIT).
	@@ -121499,28 +121533,34 @@
121499	Expr pWhere, / The WHERE clause of the outer query */
121500	int iCursor /* Cursor number of the subquery */
121501	){
121502	Expr *pNew;
121503	int nChng = 0;
121504	Select pX; / For looping over compound SELECTs in pSubq */
121505	if( pWhere==0 ) return 0;
121506	for(pX=pSubq; pX; pX=pX->pPrior){
121507	if( (pX->selFlags & (SF_Aggregate\|SF_Recursive))!=0 ){
121508	testcase( pX->selFlags & SF_Aggregate );
121509	testcase( pX->selFlags & SF_Recursive );
121510	testcase( pX!=pSubq );
121511	return 0; /* restrictions (1) and (2) */





121512	}
121513	}


121514	if( pSubq->pLimit!=0 ){
121515	return 0; /* restriction (3) */
121516	}
121517	while( pWhere->op==TK_AND ){
121518	nChng += pushDownWhereTerms(pParse, pSubq, pWhere->pRight, iCursor);
121519	pWhere = pWhere->pLeft;
121520	}
121521	if( ExprHasProperty(pWhere,EP_FromJoin) ) return 0; /* restriction 5 */
121522	if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){
121523	nChng++;
121524	while( pSubq ){
121525	SubstContext x;
121526	pNew = sqlite3ExprDup(pParse->db, pWhere, 0);
	@@ -121528,11 +121568,15 @@
121528	x.iTable = iCursor;
121529	x.iNewTable = iCursor;
121530	x.isLeftJoin = 0;
121531	x.pEList = pSubq->pEList;
121532	pNew = substExpr(&x, pNew);
121533	pSubq->pWhere = sqlite3ExprAnd(pParse->db, pSubq->pWhere, pNew);




121534	pSubq = pSubq->pPrior;
121535	}
121536	}
121537	return nChng;
121538	}
	@@ -121856,11 +121900,12 @@
121856	sqlite3ErrorMsg(
121857	pParse, "multiple references to recursive table: %s", pCte->zName
121858	);
121859	return SQLITE_ERROR;
121860	}
121861	assert( pTab->nTabRef==1 \|\| ((pSel->selFlags&SF_Recursive) && pTab->nTabRef==2 ));

121862
121863	pCte->zCteErr = "circular reference: %s";
121864	pSavedWith = pParse->pWith;
121865	pParse->pWith = pWith;
121866	if( bMayRecursive ){
	@@ -122000,11 +122045,15 @@
122000	assert( pFrom->pTab==0 );
122001	if( sqlite3WalkSelect(pWalker, pSel) ) return WRC_Abort;
122002	pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
122003	if( pTab==0 ) return WRC_Abort;
122004	pTab->nTabRef = 1;
122005	pTab->zName = sqlite3MPrintf(db, "sqlite_sq_%p", (void*)pTab);




122006	while( pSel->pPrior ){ pSel = pSel->pPrior; }
122007	sqlite3ColumnsFromExprList(pParse, pSel->pEList,&pTab->nCol,&pTab->aCol);
122008	pTab->iPKey = -1;
122009	pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) );
122010	pTab->tabFlags \|= TF_Ephemeral;
	@@ -122660,28 +122709,28 @@
122660	static int countOfViewOptimization(Parse pParse, Select p){
122661	Select pSub, pPrior;
122662	Expr *pExpr;
122663	Expr *pCount;
122664	sqlite3 *db;
122665	if( (p->selFlags & SF_Aggregate)==0 ) return 0; /* This is an aggregate query */
122666	if( p->pEList->nExpr!=1 ) return 0; /* Single result column */
122667	pExpr = p->pEList->a[0].pExpr;
122668	if( pExpr->op!=TK_AGG_FUNCTION ) return 0; /* Result is an aggregate */
122669	if( sqlite3_stricmp(pExpr->u.zToken,"count") ) return 0; /* Must be count() */
122670	if( pExpr->x.pList!=0 ) return 0; /* Must be count() /
122671	if( p->pSrc->nSrc!=1 ) return 0; /* One table in the FROM clause */
122672	pSub = p->pSrc->a[0].pSelect;
122673	if( pSub==0 ) return 0; /* The FROM is a subquery */
122674	if( pSub->pPrior==0 ) return 0; /* Must be a compound subquery */
122675	do{
122676	if( pSub->op!=TK_ALL && pSub->pPrior ) return 0; /* Must be UNION ALL */
122677	if( pSub->pWhere ) return 0; /* No WHERE clause */
122678	if( pSub->selFlags & SF_Aggregate ) return 0; /* Not an aggregate */
122679	pSub = pSub->pPrior; /* Repeat over compound terms */
122680	}while( pSub );
122681
122682	/* If we reach this point, that means it is OK to perform the transformation */
122683
122684	db = pParse->db;
122685	pCount = pExpr;
122686	pExpr = 0;
122687	pSub = p->pSrc->a[0].pSelect;
	@@ -122817,11 +122866,10 @@
122817	*/
122818	#if !defined(SQLITE_OMIT_SUBQUERY) \|\| !defined(SQLITE_OMIT_VIEW)
122819	for(i=0; !p->pPrior && i<pTabList->nSrc; i++){
122820	struct SrcList_item *pItem = &pTabList->a[i];
122821	Select *pSub = pItem->pSelect;
122822	int isAggSub;
122823	Table *pTab = pItem->pTab;
122824	if( pSub==0 ) continue;
122825
122826	/* Catch mismatch in the declared columns of a view and the number of
122827	** columns in the SELECT on the RHS */
	@@ -122829,17 +122877,40 @@
122829	sqlite3ErrorMsg(pParse, "expected %d columns for '%s' but got %d",
122830	pTab->nCol, pTab->zName, pSub->pEList->nExpr);
122831	goto select_end;
122832	}
122833
122834	isAggSub = (pSub->selFlags & SF_Aggregate)!=0;
122835	if( flattenSubquery(pParse, p, i, isAgg, isAggSub) ){



























122836	/* This subquery can be absorbed into its parent. */
122837	if( isAggSub ){
122838	isAgg = 1;
122839	p->selFlags \|= SF_Aggregate;
122840	}
122841	i = -1;
122842	}
122843	pTabList = p->pSrc;
122844	if( db->mallocFailed ) goto select_end;
122845	if( !IgnorableOrderby(pDest) ){
	@@ -122869,25 +122940,29 @@
122869	*/
122870	for(i=0; i<pTabList->nSrc; i++){
122871	struct SrcList_item *pItem = &pTabList->a[i];
122872	SelectDest dest;
122873	Select *pSub;



122874
122875	/* Issue SQLITE_READ authorizations with a fake column name for any tables that
122876	** are referenced but from which no values are extracted. Examples of where these
122877	** kinds of null SQLITE_READ authorizations would occur:

122878	**
122879	** SELECT count(*) FROM t1; -- SQLITE_READ t1.""
122880	** SELECT t1.* FROM t1, t2; -- SQLITE_READ t2.""
122881	**
122882	** The fake column name is an empty string. It is possible for a table to
122883	** have a column named by the empty string, in which case there is no way to
122884	** distinguish between an unreferenced table and an actual reference to the
122885	** "" column. The original design was for the fake column name to be a NULL,
122886	** which would be unambiguous. But legacy authorization callbacks might
122887	** assume the column name is non-NULL and segfault. The use of an empty string
122888	** for the fake column name seems safer.
122889	*/
122890	if( pItem->colUsed==0 ){
122891	sqlite3AuthCheck(pParse, SQLITE_READ, pItem->zName, "", pItem->zDatabase);
122892	}
122893
	@@ -122934,35 +123009,32 @@
122934	SELECTTRACE(0x100,pParse,p,("After WHERE-clause push-down:\n"));
122935	sqlite3TreeViewSelect(0, p, 0);
122936	}
122937	#endif
122938	}



122939
122940	/* Generate code to implement the subquery
122941	**
122942	** The subquery is implemented as a co-routine if all of these are true:
122943	** (1) The subquery is guaranteed to be the outer loop (so that it
122944	** does not need to be computed more than once)
122945	** (2) The ALL keyword after SELECT is omitted. (Applications are
122946	** allowed to say "SELECT ALL" instead of just "SELECT" to disable
122947	** the use of co-routines.)
122948	** (3) Co-routines are not disabled using sqlite3_test_control()
122949	** with SQLITE_TESTCTRL_OPTIMIZATIONS.
122950	**
122951	** TODO: Are there other reasons beside (1) to use a co-routine
122952	** implementation?
122953	*/
122954	if( i==0
122955	&& (pTabList->nSrc==1
122956	\|\| (pTabList->a[1].fg.jointype&(JT_LEFT\|JT_CROSS))!=0) /* (1) */
122957	&& (p->selFlags & SF_All)==0 /* (2) */
122958	&& OptimizationEnabled(db, SQLITE_SubqCoroutine) /* (3) */
122959	){
122960	/* Implement a co-routine that will return a single row of the result
122961	** set on each invocation.
122962	*/
122963	int addrTop = sqlite3VdbeCurrentAddr(v)+1;

122964	pItem->regReturn = ++pParse->nMem;
122965	sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop);
122966	VdbeComment((v, "%s", pItem->pTab->zName));
122967	pItem->addrFillSub = addrTop;
122968	sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn);
	@@ -123016,10 +123088,11 @@
123016	sqlite3VdbeChangeP1(v, topAddr, retAddr);
123017	sqlite3ClearTempRegCache(pParse);
123018	}
123019	if( db->mallocFailed ) goto select_end;
123020	pParse->nHeight -= sqlite3SelectExprHeight(p);

123021	#endif
123022	}
123023
123024	/* Various elements of the SELECT copied into local variables for
123025	** convenience */
	@@ -131021,11 +131094,10 @@
131021	** returned when it should not be, then incorrect answers might result.
131022	*/
131023	static int termIsEquivalence(Parse pParse, Expr pExpr){
131024	char aff1, aff2;
131025	CollSeq *pColl;
131026	const char zColl1, zColl2;
131027	if( !OptimizationEnabled(pParse->db, SQLITE_Transitive) ) return 0;
131028	if( pExpr->op!=TK_EQ && pExpr->op!=TK_IS ) return 0;
131029	if( ExprHasProperty(pExpr, EP_FromJoin) ) return 0;
131030	aff1 = sqlite3ExprAffinity(pExpr->pLeft);
131031	aff2 = sqlite3ExprAffinity(pExpr->pRight);
	@@ -131034,15 +131106,11 @@
131034	){
131035	return 0;
131036	}
131037	pColl = sqlite3BinaryCompareCollSeq(pParse, pExpr->pLeft, pExpr->pRight);
131038	if( pColl==0 \|\| sqlite3StrICmp(pColl->zName, "BINARY")==0 ) return 1;
131039	pColl = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
131040	zColl1 = pColl ? pColl->zName : 0;
131041	pColl = sqlite3ExprCollSeq(pParse, pExpr->pRight);
131042	zColl2 = pColl ? pColl->zName : 0;
131043	return sqlite3_stricmp(zColl1, zColl2)==0;
131044	}
131045
131046	/*
131047	** Recursively walk the expressions of a SELECT statement and generate
131048	** a bitmask indicating which tables are used in that expression
	@@ -132119,12 +132187,12 @@
132119	Expr *p = sqlite3ExprSkipCollate(pList->a[i].pExpr);
132120	if( p->op==TK_COLUMN
132121	&& p->iColumn==pIdx->aiColumn[iCol]
132122	&& p->iTable==iBase
132123	){
132124	CollSeq *pColl = sqlite3ExprCollSeq(pParse, pList->a[i].pExpr);
132125	if( pColl && 0==sqlite3StrICmp(pColl->zName, zColl) ){
132126	return i;
132127	}
132128	}
132129	}
132130
	@@ -134385,11 +134453,11 @@
134385	if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1;
134386	}
134387	}else if( (aColExpr = pIndex->aColExpr)!=0 ){
134388	for(jj=0; jj<pIndex->nKeyCol; jj++){
134389	if( pIndex->aiColumn[jj]!=XN_EXPR ) continue;
134390	if( sqlite3ExprCompare(0, pExpr,aColExpr->a[jj].pExpr,iCursor)==0 ){
134391	return 1;
134392	}
134393	}
134394	}
134395	}
	@@ -135295,18 +135363,14 @@
135295	assert( wctrlFlags & WHERE_ORDERBY_LIMIT );
135296	for(j=0; j<pLoop->nLTerm && pTerm!=pLoop->aLTerm[j]; j++){}
135297	if( j>=pLoop->nLTerm ) continue;
135298	}
135299	if( (pTerm->eOperator&(WO_EQ\|WO_IS))!=0 && pOBExpr->iColumn>=0 ){
135300	const char z1, z2;
135301	pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
135302	if( !pColl ) pColl = db->pDfltColl;
135303	z1 = pColl->zName;
135304	pColl = sqlite3ExprCollSeq(pWInfo->pParse, pTerm->pExpr);
135305	if( !pColl ) pColl = db->pDfltColl;
135306	z2 = pColl->zName;
135307	if( sqlite3StrICmp(z1, z2)!=0 ) continue;
135308	testcase( pTerm->pExpr->op==TK_IS );
135309	}
135310	obSat \|= MASKBIT(i);
135311	}
135312
	@@ -135374,11 +135438,11 @@
135374	** (revIdx) for the j-th column of the index.
135375	*/
135376	if( pIndex ){
135377	iColumn = pIndex->aiColumn[j];
135378	revIdx = pIndex->aSortOrder[j];
135379	if( iColumn==pIndex->pTable->iPKey ) iColumn = -1;
135380	}else{
135381	iColumn = XN_ROWID;
135382	revIdx = 0;
135383	}
135384
	@@ -135401,23 +135465,22 @@
135401	if( MASKBIT(i) & obSat ) continue;
135402	pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
135403	testcase( wctrlFlags & WHERE_GROUPBY );
135404	testcase( wctrlFlags & WHERE_DISTINCTBY );
135405	if( (wctrlFlags & (WHERE_GROUPBY\|WHERE_DISTINCTBY))==0 ) bOnce = 0;
135406	if( iColumn>=(-1) ){
135407	if( pOBExpr->op!=TK_COLUMN ) continue;
135408	if( pOBExpr->iTable!=iCur ) continue;
135409	if( pOBExpr->iColumn!=iColumn ) continue;
135410	}else{
135411	if( sqlite3ExprCompare(0,
135412	pOBExpr,pIndex->aColExpr->a[j].pExpr,iCur) ){
135413	continue;
135414	}
135415	}
135416	if( iColumn!=XN_ROWID ){
135417	pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
135418	if( !pColl ) pColl = db->pDfltColl;
135419	if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
135420	}
135421	pLoop->u.btree.nIdxCol = j+1;
135422	isMatch = 1;
135423	break;
	@@ -137095,11 +137158,12 @@
137095	** YYNSTATE the combined number of states.
137096	** YYNRULE the number of rules in the grammar
137097	** YY_MAX_SHIFT Maximum value for shift actions
137098	** YY_MIN_SHIFTREDUCE Minimum value for shift-reduce actions
137099	** YY_MAX_SHIFTREDUCE Maximum value for shift-reduce actions
137100	** YY_MIN_REDUCE Maximum value for reduce actions

137101	** YY_ERROR_ACTION The yy_action[] code for syntax error
137102	** YY_ACCEPT_ACTION The yy_action[] code for accept
137103	** YY_NO_ACTION The yy_action[] code for no-op
137104	*/
137105	#ifndef INTERFACE
	@@ -184892,11 +184956,12 @@
184892	** fts5YYNSTATE the combined number of states.
184893	** fts5YYNRULE the number of rules in the grammar
184894	** fts5YY_MAX_SHIFT Maximum value for shift actions
184895	** fts5YY_MIN_SHIFTREDUCE Minimum value for shift-reduce actions
184896	** fts5YY_MAX_SHIFTREDUCE Maximum value for shift-reduce actions
184897	** fts5YY_MIN_REDUCE Maximum value for reduce actions

184898	** fts5YY_ERROR_ACTION The fts5yy_action[] code for syntax error
184899	** fts5YY_ACCEPT_ACTION The fts5yy_action[] code for accept
184900	** fts5YY_NO_ACTION The fts5yy_action[] code for no-op
184901	*/
184902	#ifndef INTERFACE
	@@ -200632,11 +200697,11 @@
200632	int nArg, /* Number of args */
200633	sqlite3_value *apUnused / Function arguments */
200634	){
200635	assert( nArg==0 );
200636	UNUSED_PARAM2(nArg, apUnused);
200637	sqlite3_result_text(pCtx, "fts5: 2017-09-21 20:43:48 5d03c738e93d36815248991d9ed3d62297ba1bb966e602e7874410076c144f43", -1, SQLITE_TRANSIENT);
200638	}
200639
200640	static int fts5Init(sqlite3 *db){
200641	static const sqlite3_module fts5Mod = {
200642	/* iVersion */ 2,
	@@ -204901,12 +204966,12 @@
204901	}
204902	#endif /* SQLITE_CORE */
204903	#endif /* !defined(SQLITE_CORE) \|\| defined(SQLITE_ENABLE_STMTVTAB) */
204904
204905	/************ End of stmt.c **********************************************/
204906	#if __LINE__!=204906
204907	#undef SQLITE_SOURCE_ID
204908	#define SQLITE_SOURCE_ID "2017-09-21 20:43:48 5d03c738e93d36815248991d9ed3d62297ba1bb966e602e7874410076c14alt2"
204909	#endif
204910	/* Return the source-id for this library */
204911	SQLITE_API const char *sqlite3_sourceid(void){ return SQLITE_SOURCE_ID; }
204912	/************************ End of sqlite3.c ****************************/
204913

	--- src/sqlite3.c
	+++ src/sqlite3.c
	@@ -1147,11 +1147,11 @@
1147	** [sqlite3_libversion_number()], [sqlite3_sourceid()],
1148	** [sqlite_version()] and [sqlite_source_id()].
1149	*/
1150	#define SQLITE_VERSION "3.21.0"
1151	#define SQLITE_VERSION_NUMBER 3021000
1152	#define SQLITE_SOURCE_ID "2017-10-02 02:52:54 c9104b59c7ed360291f7f6fc8caae938e9840c77620d598e4096f78183bf807a"
1153
1154	/*
1155	** CAPI3REF: Run-Time Library Version Numbers
1156	** KEYWORDS: sqlite3_version sqlite3_sourceid
1157	**
	@@ -15279,20 +15279,19 @@
15279	*/
15280	#define SQLITE_QueryFlattener 0x0001 /* Query flattening */
15281	#define SQLITE_ColumnCache 0x0002 /* Column cache */
15282	#define SQLITE_GroupByOrder 0x0004 /* GROUPBY cover of ORDERBY */
15283	#define SQLITE_FactorOutConst 0x0008 /* Constant factoring */
15284	#define SQLITE_DistinctOpt 0x0010 /* DISTINCT using indexes */
15285	#define SQLITE_CoverIdxScan 0x0020 /* Covering index scans */
15286	#define SQLITE_OrderByIdxJoin 0x0040 /* ORDER BY of joins via index */
15287	#define SQLITE_Transitive 0x0080 /* Transitive constraints */
15288	#define SQLITE_OmitNoopJoin 0x0100 /* Omit unused tables in joins */
15289	#define SQLITE_CountOfView 0x0200 /* The count-of-view optimization */
15290	#define SQLITE_CursorHints 0x0400 /* Add OP_CursorHint opcodes */
15291	#define SQLITE_Stat34 0x0800 /* Use STAT3 or STAT4 data */
15292	/* TH3 expects the Stat34 ^^^^^^ value to be 0x0800. Don't change it */

15293	#define SQLITE_AllOpts 0xffff /* All optimizations */
15294
15295	/*
15296	** Macros for testing whether or not optimizations are enabled or disabled.
15297	*/
	@@ -17777,10 +17776,12 @@
17776	SQLITE_PRIVATE const char *sqlite3ErrStr(int);
17777	SQLITE_PRIVATE int sqlite3ReadSchema(Parse *pParse);
17778	SQLITE_PRIVATE CollSeq sqlite3FindCollSeq(sqlite3,u8 enc, const char*,int);
17779	SQLITE_PRIVATE CollSeq sqlite3LocateCollSeq(Parse pParse, const char*zName);
17780	SQLITE_PRIVATE CollSeq sqlite3ExprCollSeq(Parse pParse, Expr *pExpr);
17781	SQLITE_PRIVATE CollSeq sqlite3ExprNNCollSeq(Parse pParse, Expr *pExpr);
17782	SQLITE_PRIVATE int sqlite3ExprCollSeqMatch(Parse,Expr,Expr*);
17783	SQLITE_PRIVATE Expr sqlite3ExprAddCollateToken(Parse pParse, Expr, const Token, int);
17784	SQLITE_PRIVATE Expr sqlite3ExprAddCollateString(Parse,Expr,const char);
17785	SQLITE_PRIVATE Expr sqlite3ExprSkipCollate(Expr);
17786	SQLITE_PRIVATE int sqlite3CheckCollSeq(Parse , CollSeq );
17787	SQLITE_PRIVATE int sqlite3CheckObjectName(Parse , const char );
	@@ -43282,18 +43283,23 @@
43283	dwFlagsAndAttributes & FILE_ATTRIBUTE_MASK;
43284	extendedParameters.dwFileFlags = dwFlagsAndAttributes & FILE_FLAG_MASK;
43285	extendedParameters.dwSecurityQosFlags = SECURITY_ANONYMOUS;
43286	extendedParameters.lpSecurityAttributes = NULL;
43287	extendedParameters.hTemplateFile = NULL;
43288	do{
43289	h = osCreateFile2((LPCWSTR)zConverted,
43290	dwDesiredAccess,
43291	dwShareMode,
43292	dwCreationDisposition,
43293	&extendedParameters);
43294	if( h!=INVALID_HANDLE_VALUE ) break;
43295	if( isReadWrite ){
43296	int isRO = 0;
43297	int rc2 = winAccess(pVfs, zName, SQLITE_ACCESS_READ, &isRO);
43298	if( rc2==SQLITE_OK && isRO ) break;
43299	}
43300	}while( winRetryIoerr(&cnt, &lastErrno) );
43301	#else
43302	do{
43303	h = osCreateFileW((LPCWSTR)zConverted,
43304	dwDesiredAccess,
43305	dwShareMode, NULL,
	@@ -43309,19 +43315,24 @@
43315	}while( winRetryIoerr(&cnt, &lastErrno) );
43316	#endif
43317	}
43318	#ifdef SQLITE_WIN32_HAS_ANSI
43319	else{
43320	do{
43321	h = osCreateFileA((LPCSTR)zConverted,
43322	dwDesiredAccess,
43323	dwShareMode, NULL,
43324	dwCreationDisposition,
43325	dwFlagsAndAttributes,
43326	NULL);
43327	if( h!=INVALID_HANDLE_VALUE ) break;
43328	if( isReadWrite ){
43329	int isRO = 0;
43330	int rc2 = winAccess(pVfs, zName, SQLITE_ACCESS_READ, &isRO);
43331	if( rc2==SQLITE_OK && isRO ) break;
43332	}
43333	}while( winRetryIoerr(&cnt, &lastErrno) );
43334	}
43335	#endif
43336	winLogIoerr(cnt, __LINE__);
43337
43338	OSTRACE(("OPEN file=%p, name=%s, access=%lx, rc=%s\n", h, zUtf8Name,
	@@ -61031,10 +61042,13 @@
61042	u8 pEnd = &data[cellOffset + nCell2];
61043	u8 *pAddr;
61044	int sz2 = 0;
61045	int sz = get2byte(&data[iFree+2]);
61046	int top = get2byte(&data[hdr+5]);
61047	if( top>=iFree ){
61048	return SQLITE_CORRUPT_PGNO(pPage->pgno);
61049	}
61050	if( iFree2 ){
61051	assert( iFree+sz<=iFree2 ); /* Verified by pageFindSlot() */
61052	sz2 = get2byte(&data[iFree2+2]);
61053	assert( iFree+sz+sz2+iFree2-(iFree+sz) <= usableSize );
61054	memmove(&data[iFree+sz+sz2], &data[iFree+sz], iFree2-(iFree+sz));
	@@ -71383,13 +71397,14 @@
71397	u32 amt, /* Number of bytes to return. */
71398	Mem pMem / OUT: Return data in this Mem structure. */
71399	){
71400	int rc;
71401	pMem->flags = MEM_Null;
71402	if( SQLITE_OK==(rc = sqlite3VdbeMemClearAndResize(pMem, amt+1)) ){
71403	rc = sqlite3BtreePayload(pCur, offset, amt, pMem->z);
71404	if( rc==SQLITE_OK ){
71405	pMem->z[amt] = 0; /* Overrun area used when reading malformed records */
71406	pMem->flags = MEM_Blob;
71407	pMem->n = (int)amt;
71408	}else{
71409	sqlite3VdbeMemRelease(pMem);
71410	}
	@@ -91882,10 +91897,15 @@
91897
91898	/*
91899	** Return the collation sequence for the expression pExpr. If
91900	** there is no defined collating sequence, return NULL.
91901	**
91902	** See also: sqlite3ExprNNCollSeq()
91903	**
91904	** The sqlite3ExprNNCollSeq() works the same exact that it returns the
91905	** default collation if pExpr has no defined collation.
91906	**
91907	** The collating sequence might be determined by a COLLATE operator
91908	** or by the presence of a column with a defined collating sequence.
91909	** COLLATE operators take first precedence. Left operands take
91910	** precedence over right operands.
91911	*/
	@@ -91945,10 +91965,36 @@
91965	if( sqlite3CheckCollSeq(pParse, pColl) ){
91966	pColl = 0;
91967	}
91968	return pColl;
91969	}
91970
91971	/*
91972	** Return the collation sequence for the expression pExpr. If
91973	** there is no defined collating sequence, return a pointer to the
91974	** defautl collation sequence.
91975	**
91976	** See also: sqlite3ExprCollSeq()
91977	**
91978	** The sqlite3ExprCollSeq() routine works the same except that it
91979	** returns NULL if there is no defined collation.
91980	*/
91981	SQLITE_PRIVATE CollSeq sqlite3ExprNNCollSeq(Parse pParse, Expr *pExpr){
91982	CollSeq *p = sqlite3ExprCollSeq(pParse, pExpr);
91983	if( p==0 ) p = pParse->db->pDfltColl;
91984	assert( p!=0 );
91985	return p;
91986	}
91987
91988	/*
91989	** Return TRUE if the two expressions have equivalent collating sequences.
91990	*/
91991	SQLITE_PRIVATE int sqlite3ExprCollSeqMatch(Parse pParse, Expr pE1, Expr *pE2){
91992	CollSeq *pColl1 = sqlite3ExprNNCollSeq(pParse, pE1);
91993	CollSeq *pColl2 = sqlite3ExprNNCollSeq(pParse, pE2);
91994	return sqlite3StrICmp(pColl1->zName, pColl2->zName)==0;
91995	}
91996
91997	/*
91998	** pExpr is an operand of a comparison operator. aff2 is the
91999	** type affinity of the other operand. This routine returns the
92000	** type affinity that should be used for the comparison operator.
	@@ -93439,16 +93485,15 @@
93485	** ExprList.
93486	*/
93487	SQLITE_PRIVATE u32 sqlite3ExprListFlags(const ExprList *pList){
93488	int i;
93489	u32 m = 0;
93490	assert( pList!=0 );
93491	for(i=0; i<pList->nExpr; i++){
93492	Expr *pExpr = pList->a[i].pExpr;
93493	assert( pExpr!=0 );
93494	m \|= pExpr->flags;

93495	}
93496	return m;
93497	}
93498
93499	/*
	@@ -93601,12 +93646,12 @@
93646	/* Check if pExpr is identical to any GROUP BY term. If so, consider
93647	** it constant. */
93648	for(i=0; i<pGroupBy->nExpr; i++){
93649	Expr *p = pGroupBy->a[i].pExpr;
93650	if( sqlite3ExprCompare(0, pExpr, p, -1)<2 ){
93651	CollSeq *pColl = sqlite3ExprNNCollSeq(pWalker->pParse, p);
93652	if( sqlite3_stricmp("BINARY", pColl->zName)==0 ){
93653	return WRC_Prune;
93654	}
93655	}
93656	}
93657
	@@ -117746,11 +117791,12 @@
117791	if( pNew==0 ){
117792	assert( pParse->db->mallocFailed );
117793	pNew = &standin;
117794	}
117795	if( pEList==0 ){
117796	pEList = sqlite3ExprListAppend(pParse, 0,
117797	sqlite3Expr(pParse->db,TK_ASTERISK,0));
117798	}
117799	pNew->pEList = pEList;
117800	pNew->op = TK_SELECT;
117801	pNew->selFlags = selFlags;
117802	pNew->iLimit = 0;
	@@ -117770,11 +117816,12 @@
117816	pNew->pPrior = 0;
117817	pNew->pNext = 0;
117818	pNew->pLimit = pLimit;
117819	pNew->pOffset = pOffset;
117820	pNew->pWith = 0;
117821	assert( pOffset==0 \|\| pLimit!=0 \|\| pParse->nErr>0
117822	\|\| pParse->db->mallocFailed!=0 );
117823	if( pParse->db->mallocFailed ) {
117824	clearSelect(pParse->db, pNew, pNew!=&standin);
117825	pNew = 0;
117826	}else{
117827	assert( pNew->pSrc!=0 \|\| pParse->nErr>0 );
	@@ -118387,11 +118434,12 @@
118434	}
118435	regOrig = 0;
118436	assert( eDest==SRT_Set \|\| eDest==SRT_Mem
118437	\|\| eDest==SRT_Coroutine \|\| eDest==SRT_Output );
118438	}
118439	nResultCol = sqlite3ExprCodeExprList(pParse,p->pEList,regResult,
118440	0,ecelFlags);
118441	}
118442
118443	/* If the DISTINCT keyword was present on the SELECT statement
118444	** and this row has been seen before, then do not make this row
118445	** part of the result.
	@@ -118737,14 +118785,11 @@
118785	nExpr = pList->nExpr;
118786	pInfo = sqlite3KeyInfoAlloc(db, nExpr-iStart, nExtra+1);
118787	if( pInfo ){
118788	assert( sqlite3KeyInfoIsWriteable(pInfo) );
118789	for(i=iStart, pItem=pList->a+iStart; i<nExpr; i++, pItem++){
118790	pInfo->aColl[i-iStart] = sqlite3ExprNNCollSeq(pParse, pItem->pExpr);



118791	pInfo->aSortOrder[i-iStart] = pItem->sortOrder;
118792	}
118793	}
118794	return pInfo;
118795	}
	@@ -119201,13 +119246,13 @@
119246	** short=OFF, full=OFF: Column name is the text of the expression has it
119247	** originally appears in the SELECT statement. In
119248	** other words, the zSpan of the result expression.
119249	**
119250	** short=ON, full=OFF: (This is the default setting). If the result
119251	** refers directly to a table column, then the
119252	** result column name is just the table column
119253	** name: COLUMN. Otherwise use zSpan.
119254	**
119255	** full=ON, short=ANY: If the result refers directly to a table column,
119256	** then the result column name with the table name
119257	** prefix, ex: TABLE.COLUMN. Otherwise use zSpan.
119258	*/
	@@ -119245,11 +119290,11 @@
119290	for(i=0; i<pEList->nExpr; i++){
119291	Expr *p = pEList->a[i].pExpr;
119292
119293	assert( p!=0 );
119294	assert( p->op!=TK_AGG_COLUMN ); /* Agg processing has not run yet */
119295	assert( p->op!=TK_COLUMN \|\| p->pTab!=0 ); /* Covering idx not yet coded */
119296	if( pEList->a[i].zName ){
119297	/* An AS clause always takes first priority */
119298	char *zName = pEList->a[i].zName;
119299	sqlite3VdbeSetColName(v, i, COLNAME_NAME, zName, SQLITE_TRANSIENT);
119300	}else if( srcName && p->op==TK_COLUMN ){
	@@ -120809,11 +120854,13 @@
120854	static Expr *substExpr(
120855	SubstContext pSubst, / Description of the substitution */
120856	Expr pExpr / Expr in which substitution occurs */
120857	){
120858	if( pExpr==0 ) return 0;
120859	if( ExprHasProperty(pExpr, EP_FromJoin)
120860	&& pExpr->iRightJoinTable==pSubst->iTable
120861	){
120862	pExpr->iRightJoinTable = pSubst->iNewTable;
120863	}
120864	if( pExpr->op==TK_COLUMN && pExpr->iTable==pSubst->iTable ){
120865	if( pExpr->iColumn<0 ){
120866	pExpr->op = TK_NULL;
	@@ -120922,72 +120969,78 @@
120969	** The code generated for this simplification gives the same result
120970	** but only has to scan the data once. And because indices might
120971	** exist on the table t1, a complete scan of the data might be
120972	** avoided.
120973	**
120974	** Flattening is subject to the following constraints:
120975	**
120976	() We no longer attempt to flatten aggregate subqueries. Was:
120977	** The subquery and the outer query cannot both be aggregates.
120978	**
120979	() We no longer attempt to flatten aggregate subqueries. Was:
120980	** (2) If the subquery is an aggregate then
120981	** (2a) the outer query must not be a join and
120982	** (2b) the outer query must not use subqueries
120983	** other than the one FROM-clause subquery that is a candidate
120984	** for flattening. (This is due to ticket [2f7170d73bf9abf80]
120985	** from 2015-02-09.)
120986	**
120987	** (3) If the subquery is the right operand of a LEFT JOIN then
120988	** (3a) the subquery may not be a join and
120989	** (3b) the FROM clause of the subquery may not contain a virtual
120990	** table and
120991	** (3c) the outer query may not be an aggregate.
120992	**
120993	** (4) The subquery can not be DISTINCT.
120994	**
120995	() At one point restrictions (4) and (5) defined a subset of DISTINCT
120996	** sub-queries that were excluded from this optimization. Restriction
120997	** (4) has since been expanded to exclude all DISTINCT subqueries.
120998	**
120999	() We no longer attempt to flatten aggregate subqueries. Was:
121000	** If the subquery is aggregate, the outer query may not be DISTINCT.
121001	**
121002	** (7) The subquery must have a FROM clause. TODO: For subqueries without
121003	** A FROM clause, consider adding a FROM clause with the special
121004	** table sqlite_once that consists of a single row containing a
121005	** single NULL.
121006	**
121007	** (8) If the subquery uses LIMIT then the outer query may not be a join.
121008	**
121009	** (9) If the subquery uses LIMIT then the outer query may not be aggregate.

121010	**
121011	() Restriction (10) was removed from the code on 2005-02-05 but we
121012	** accidently carried the comment forward until 2014-09-15. Original
121013	** constraint: "If the subquery is aggregate then the outer query
121014	** may not use LIMIT."
121015	**
121016	** (11) The subquery and the outer query may not both have ORDER BY clauses.
121017	**
121018	() Not implemented. Subsumed into restriction (3). Was previously
121019	** a separate restriction deriving from ticket #350.
121020	**
121021	** (13) The subquery and outer query may not both use LIMIT.
121022	**
121023	** (14) The subquery may not use OFFSET.
121024	**
121025	** (15) If the outer query is part of a compound select, then the
121026	** subquery may not use LIMIT.
121027	** (See ticket #2339 and ticket [02a8e81d44]).
121028	**
121029	** (16) If the outer query is aggregate, then the subquery may not
121030	** use ORDER BY. (Ticket #2942) This used to not matter
121031	** until we introduced the group_concat() function.
121032	**
121033	** (17) If the subquery is a compound select, then
121034	** (17a) all compound operators must be a UNION ALL, and
121035	** (17b) no terms within the subquery compound may be aggregate
121036	** or DISTINT, and
121037	** (17c) every term within the subquery compound must have a FROM clause
121038	** (17d) the outer query may not be
121039	** (17d1) aggregate, or
121040	** (17d2) DISTINCT, or
121041	** (17d3) a join.
121042	**
121043	** The parent and sub-query may contain WHERE clauses. Subject to
121044	** rules (11), (13) and (14), they may also contain ORDER BY,
121045	** LIMIT and OFFSET clauses. The subquery cannot use any compound
121046	** operator other than UNION ALL because all the other compound
	@@ -120999,41 +121052,42 @@
121052	** SELECT statement, but all the code here does is make sure that no
121053	** such (illegal) sub-query is flattened. The caller will detect the
121054	** syntax error and return a detailed message.
121055	**
121056	** (18) If the sub-query is a compound select, then all terms of the
121057	** ORDER BY clause of the parent must be simple references to
121058	** columns of the sub-query.
121059	**
121060	** (19) If the subquery uses LIMIT then the outer query may not
121061	** have a WHERE clause.
121062	**
121063	** (20) If the sub-query is a compound select, then it must not use
121064	** an ORDER BY clause. Ticket #3773. We could relax this constraint
121065	** somewhat by saying that the terms of the ORDER BY clause must
121066	** appear as unmodified result columns in the outer query. But we
121067	** have other optimizations in mind to deal with that case.
121068	**
121069	** (21) If the subquery uses LIMIT then the outer query may not be
121070	** DISTINCT. (See ticket [752e1646fc]).
121071	**
121072	** (22) The subquery may not be a recursive CTE.
121073	**
121074	** (23) If the outer query is a recursive CTE, then the sub-query may not be
121075	** a compound query. This restriction is because transforming the
121076	** parent to a compound query confuses the code that handles
121077	** recursive queries in multiSelect().
121078	**
121079	() We no longer attempt to flatten aggregate subqueries. Was:
121080	** The subquery may not be an aggregate that uses the built-in min() or
121081	** or max() functions. (Without this restriction, a query like:
121082	** "SELECT x FROM (SELECT max(y), x FROM t1)" would not necessarily
121083	** return the value X for which Y was maximal.)
121084	**
121085	**
121086	** In this routine, the "p" parameter is a pointer to the outer query.
121087	** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query
121088	** uses aggregates.
121089	**
121090	** If flattening is not attempted, this routine is a no-op and returns 0.
121091	** If flattening is attempted this routine returns 1.
121092	**
121093	** All of the expression analysis must occur on both the outer query and
	@@ -121041,12 +121095,11 @@
121095	*/
121096	static int flattenSubquery(
121097	Parse pParse, / Parsing context */
121098	Select p, / The parent or outer SELECT statement */
121099	int iFrom, /* Index in p->pSrc->a[] of the inner subquery */
121100	int isAgg /* True if outer SELECT uses aggregate functions */

121101	){
121102	const char *zSavedAuthContext = pParse->zAuthContext;
121103	Select pParent; / Current UNION ALL term of the other query */
121104	Select pSub; / The inner query or "subquery" */
121105	Select pSub1; / Pointer to the rightmost select in sub-query */
	@@ -121061,28 +121114,18 @@
121114	sqlite3 *db = pParse->db;
121115
121116	/* Check to see if flattening is permitted. Return 0 if not.
121117	*/
121118	assert( p!=0 );
121119	assert( p->pPrior==0 );
121120	if( OptimizationDisabled(db, SQLITE_QueryFlattener) ) return 0;
121121	pSrc = p->pSrc;
121122	assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc );
121123	pSubitem = &pSrc->a[iFrom];
121124	iParent = pSubitem->iCursor;
121125	pSub = pSubitem->pSelect;
121126	assert( pSub!=0 );










121127
121128	pSubSrc = pSub->pSrc;
121129	assert( pSubSrc );
121130	/* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
121131	** not arbitrary expressions, we allowed some combining of LIMIT and OFFSET
	@@ -121093,37 +121136,33 @@
121136	if( pSub->pOffset ) return 0; /* Restriction (14) */
121137	if( (p->selFlags & SF_Compound)!=0 && pSub->pLimit ){
121138	return 0; /* Restriction (15) */
121139	}
121140	if( pSubSrc->nSrc==0 ) return 0; /* Restriction (7) */
121141	if( pSub->selFlags & SF_Distinct ) return 0; /* Restriction (4) */
121142	if( pSub->pLimit && (pSrc->nSrc>1 \|\| isAgg) ){
121143	return 0; /* Restrictions (8)(9) */
121144	}



121145	if( p->pOrderBy && pSub->pOrderBy ){
121146	return 0; /* Restriction (11) */
121147	}
121148	if( isAgg && pSub->pOrderBy ) return 0; /* Restriction (16) */
121149	if( pSub->pLimit && p->pWhere ) return 0; /* Restriction (19) */
121150	if( pSub->pLimit && (p->selFlags & SF_Distinct)!=0 ){
121151	return 0; /* Restriction (21) */
121152	}
121153	if( pSub->selFlags & (SF_Recursive) ){
121154	return 0; /* Restrictions (22) */


121155	}
121156	if( (p->selFlags & SF_Recursive) && pSub->pPrior ){
121157	return 0; /* Restriction (23) */
121158	}
121159
121160	/*
121161	** If the subquery is the right operand of a LEFT JOIN, then the
121162	** subquery may not be a join itself (3a). Example of why this is not
121163	** allowed:
121164	**
121165	** t1 LEFT OUTER JOIN (t2 JOIN t3)
121166	**
121167	** If we flatten the above, we would get
121168	**
	@@ -121130,58 +121169,60 @@
121169	** (t1 LEFT OUTER JOIN t2) JOIN t3
121170	**
121171	** which is not at all the same thing.
121172	**
121173	** If the subquery is the right operand of a LEFT JOIN, then the outer
121174	** query cannot be an aggregate. (3c) This is an artifact of the way
121175	** aggregates are processed - there is no mechanism to determine if
121176	** the LEFT JOIN table should be all-NULL.
121177	**
121178	** See also tickets #306, #350, and #3300.
121179	*/
121180	if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){
121181	isLeftJoin = 1;
121182	if( pSubSrc->nSrc>1 \|\| isAgg \|\| IsVirtual(pSubSrc->a[0].pTab) ){
121183	/* (3a) (3c) (3b) */
121184	return 0;
121185	}
121186	}
121187	#ifdef SQLITE_EXTRA_IFNULLROW
121188	else if( iFrom>0 && !isAgg ){
121189	/* Setting isLeftJoin to -1 causes OP_IfNullRow opcodes to be generated for
121190	** every reference to any result column from subquery in a join, even
121191	** though they are not necessary. This will stress-test the OP_IfNullRow
121192	** opcode. */
121193	isLeftJoin = -1;
121194	}
121195	#endif
121196
121197	/* Restriction (17): If the sub-query is a compound SELECT, then it must
121198	** use only the UNION ALL operator. And none of the simple select queries
121199	** that make up the compound SELECT are allowed to be aggregate or distinct
121200	** queries.
121201	*/
121202	if( pSub->pPrior ){
121203	if( pSub->pOrderBy ){
121204	return 0; /* Restriction (20) */
121205	}
121206	if( isAgg \|\| (p->selFlags & SF_Distinct)!=0 \|\| pSrc->nSrc!=1 ){
121207	return 0; /* (17d1), (17d2), or (17d3) */
121208	}
121209	for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){
121210	testcase( (pSub1->selFlags & (SF_Distinct\|SF_Aggregate))==SF_Distinct );
121211	testcase( (pSub1->selFlags & (SF_Distinct\|SF_Aggregate))==SF_Aggregate );
121212	assert( pSub->pSrc!=0 );
121213	assert( pSub->pEList->nExpr==pSub1->pEList->nExpr );
121214	if( (pSub1->selFlags & (SF_Distinct\|SF_Aggregate))!=0 /* (17b) */
121215	\|\| (pSub1->pPrior && pSub1->op!=TK_ALL) /* (17a) */
121216	\|\| pSub1->pSrc->nSrc<1 /* (17c) */
121217	){
121218	return 0;
121219	}
121220	testcase( pSub1->pSrc->nSrc>1 );
121221	}
121222
121223	/* Restriction (18). */
121224	if( p->pOrderBy ){
121225	int ii;
121226	for(ii=0; ii<p->pOrderBy->nExpr; ii++){
121227	if( p->pOrderBy->a[ii].u.x.iOrderByCol==0 ) return 0;
121228	}
	@@ -121398,22 +121439,11 @@
121439	}
121440	pWhere = sqlite3ExprDup(db, pSub->pWhere, 0);
121441	if( isLeftJoin>0 ){
121442	setJoinExpr(pWhere, iNewParent);
121443	}
121444	pParent->pWhere = sqlite3ExprAnd(db, pWhere, pParent->pWhere);











121445	if( db->mallocFailed==0 ){
121446	SubstContext x;
121447	x.pParse = pParse;
121448	x.iTable = iParent;
121449	x.iNewTable = iNewParent;
	@@ -121472,13 +121502,17 @@
121502	** The hope is that the terms added to the inner query will make it more
121503	** efficient.
121504	**
121505	** Do not attempt this optimization if:
121506	**
121507	(1) ( This restriction was removed on 2017-09-29. We used to
121508	** disallow this optimization for aggregate subqueries, but now
121509	** it is allowed by putting the extra terms on the HAVING clause.
121510	** The added HAVING clause is pointless if the subquery lacks
121511	** a GROUP BY clause. But such a HAVING clause is also harmless
121512	** so there does not appear to be any reason to add extra logic
121513	to suppress it. )
121514	**
121515	** (2) The inner query is the recursive part of a common table expression.
121516	**
121517	** (3) The inner query has a LIMIT clause (since the changes to the WHERE
121518	** close would change the meaning of the LIMIT).
	@@ -121499,28 +121533,34 @@
121533	Expr pWhere, / The WHERE clause of the outer query */
121534	int iCursor /* Cursor number of the subquery */
121535	){
121536	Expr *pNew;
121537	int nChng = 0;

121538	if( pWhere==0 ) return 0;
121539	if( pSubq->selFlags & SF_Recursive ) return 0; /* restriction (2) */
121540
121541	#ifdef SQLITE_DEBUG
121542	/* Only the first term of a compound can have a WITH clause. But make
121543	** sure no other terms are marked SF_Recursive in case something changes
121544	** in the future.
121545	*/
121546	{
121547	Select *pX;
121548	for(pX=pSubq; pX; pX=pX->pPrior){
121549	assert( (pX->selFlags & (SF_Recursive))==0 );
121550	}
121551	}
121552	#endif
121553
121554	if( pSubq->pLimit!=0 ){
121555	return 0; /* restriction (3) */
121556	}
121557	while( pWhere->op==TK_AND ){
121558	nChng += pushDownWhereTerms(pParse, pSubq, pWhere->pRight, iCursor);
121559	pWhere = pWhere->pLeft;
121560	}
121561	if( ExprHasProperty(pWhere,EP_FromJoin) ) return 0; /* restriction (5) */
121562	if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){
121563	nChng++;
121564	while( pSubq ){
121565	SubstContext x;
121566	pNew = sqlite3ExprDup(pParse->db, pWhere, 0);
	@@ -121528,11 +121568,15 @@
121568	x.iTable = iCursor;
121569	x.iNewTable = iCursor;
121570	x.isLeftJoin = 0;
121571	x.pEList = pSubq->pEList;
121572	pNew = substExpr(&x, pNew);
121573	if( pSubq->selFlags & SF_Aggregate ){
121574	pSubq->pHaving = sqlite3ExprAnd(pParse->db, pSubq->pHaving, pNew);
121575	}else{
121576	pSubq->pWhere = sqlite3ExprAnd(pParse->db, pSubq->pWhere, pNew);
121577	}
121578	pSubq = pSubq->pPrior;
121579	}
121580	}
121581	return nChng;
121582	}
	@@ -121856,11 +121900,12 @@
121900	sqlite3ErrorMsg(
121901	pParse, "multiple references to recursive table: %s", pCte->zName
121902	);
121903	return SQLITE_ERROR;
121904	}
121905	assert( pTab->nTabRef==1 \|\|
121906	((pSel->selFlags&SF_Recursive) && pTab->nTabRef==2 ));
121907
121908	pCte->zCteErr = "circular reference: %s";
121909	pSavedWith = pParse->pWith;
121910	pParse->pWith = pWith;
121911	if( bMayRecursive ){
	@@ -122000,11 +122045,15 @@
122045	assert( pFrom->pTab==0 );
122046	if( sqlite3WalkSelect(pWalker, pSel) ) return WRC_Abort;
122047	pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
122048	if( pTab==0 ) return WRC_Abort;
122049	pTab->nTabRef = 1;
122050	if( pFrom->zAlias ){
122051	pTab->zName = sqlite3DbStrDup(db, pFrom->zAlias);
122052	}else{
122053	pTab->zName = sqlite3MPrintf(db, "subquery_%p", (void*)pTab);
122054	}
122055	while( pSel->pPrior ){ pSel = pSel->pPrior; }
122056	sqlite3ColumnsFromExprList(pParse, pSel->pEList,&pTab->nCol,&pTab->aCol);
122057	pTab->iPKey = -1;
122058	pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) );
122059	pTab->tabFlags \|= TF_Ephemeral;
	@@ -122660,28 +122709,28 @@
122709	static int countOfViewOptimization(Parse pParse, Select p){
122710	Select pSub, pPrior;
122711	Expr *pExpr;
122712	Expr *pCount;
122713	sqlite3 *db;
122714	if( (p->selFlags & SF_Aggregate)==0 ) return 0; /* This is an aggregate */
122715	if( p->pEList->nExpr!=1 ) return 0; /* Single result column */
122716	pExpr = p->pEList->a[0].pExpr;
122717	if( pExpr->op!=TK_AGG_FUNCTION ) return 0; /* Result is an aggregate */
122718	if( sqlite3_stricmp(pExpr->u.zToken,"count") ) return 0; /* Is count() */
122719	if( pExpr->x.pList!=0 ) return 0; /* Must be count() /
122720	if( p->pSrc->nSrc!=1 ) return 0; /* One table in FROM */
122721	pSub = p->pSrc->a[0].pSelect;
122722	if( pSub==0 ) return 0; /* The FROM is a subquery */
122723	if( pSub->pPrior==0 ) return 0; /* Must be a compound ry */
122724	do{
122725	if( pSub->op!=TK_ALL && pSub->pPrior ) return 0; /* Must be UNION ALL */
122726	if( pSub->pWhere ) return 0; /* No WHERE clause */
122727	if( pSub->selFlags & SF_Aggregate ) return 0; /* Not an aggregate */
122728	pSub = pSub->pPrior; /* Repeat over compound */
122729	}while( pSub );
122730
122731	/* If we reach this point then it is OK to perform the transformation */
122732
122733	db = pParse->db;
122734	pCount = pExpr;
122735	pExpr = 0;
122736	pSub = p->pSrc->a[0].pSelect;
	@@ -122817,11 +122866,10 @@
122866	*/
122867	#if !defined(SQLITE_OMIT_SUBQUERY) \|\| !defined(SQLITE_OMIT_VIEW)
122868	for(i=0; !p->pPrior && i<pTabList->nSrc; i++){
122869	struct SrcList_item *pItem = &pTabList->a[i];
122870	Select *pSub = pItem->pSelect;

122871	Table *pTab = pItem->pTab;
122872	if( pSub==0 ) continue;
122873
122874	/* Catch mismatch in the declared columns of a view and the number of
122875	** columns in the SELECT on the RHS */
	@@ -122829,17 +122877,40 @@
122877	sqlite3ErrorMsg(pParse, "expected %d columns for '%s' but got %d",
122878	pTab->nCol, pTab->zName, pSub->pEList->nExpr);
122879	goto select_end;
122880	}
122881
122882	/* Do not try to flatten an aggregate subquery.
122883	**
122884	** Flattening an aggregate subquery is only possible if the outer query
122885	** is not a join. But if the outer query is not a join, then the subquery
122886	** will be implemented as a co-routine and there is no advantage to
122887	** flattening in that case.
122888	*/
122889	if( (pSub->selFlags & SF_Aggregate)!=0 ) continue;
122890	assert( pSub->pGroupBy==0 );
122891
122892	/* If the subquery contains an ORDER BY clause and if
122893	** it will be implemented as a co-routine, then do not flatten. This
122894	** restriction allows SQL constructs like this:
122895	**
122896	** SELECT expensive_function(x)
122897	** FROM (SELECT x FROM tab ORDER BY y LIMIT 10);
122898	**
122899	** The expensive_function() is only computed on the 10 rows that
122900	** are output, rather than every row of the table.
122901	*/
122902	if( pSub->pOrderBy!=0
122903	&& i==0
122904	&& (pTabList->nSrc==1
122905	\|\| (pTabList->a[1].fg.jointype&(JT_LEFT\|JT_CROSS))!=0)
122906	){
122907	continue;
122908	}
122909
122910	if( flattenSubquery(pParse, p, i, isAgg) ){
122911	/* This subquery can be absorbed into its parent. */




122912	i = -1;
122913	}
122914	pTabList = p->pSrc;
122915	if( db->mallocFailed ) goto select_end;
122916	if( !IgnorableOrderby(pDest) ){
	@@ -122869,25 +122940,29 @@
122940	*/
122941	for(i=0; i<pTabList->nSrc; i++){
122942	struct SrcList_item *pItem = &pTabList->a[i];
122943	SelectDest dest;
122944	Select *pSub;
122945	#if !defined(SQLITE_OMIT_SUBQUERY) \|\| !defined(SQLITE_OMIT_VIEW)
122946	const char *zSavedAuthContext;
122947	#endif
122948
122949	/* Issue SQLITE_READ authorizations with a fake column name for any
122950	** tables that are referenced but from which no values are extracted.
122951	** Examples of where these kinds of null SQLITE_READ authorizations
122952	** would occur:
122953	**
122954	** SELECT count(*) FROM t1; -- SQLITE_READ t1.""
122955	** SELECT t1.* FROM t1, t2; -- SQLITE_READ t2.""
122956	**
122957	** The fake column name is an empty string. It is possible for a table to
122958	** have a column named by the empty string, in which case there is no way to
122959	** distinguish between an unreferenced table and an actual reference to the
122960	** "" column. The original design was for the fake column name to be a NULL,
122961	** which would be unambiguous. But legacy authorization callbacks might
122962	** assume the column name is non-NULL and segfault. The use of an empty
122963	** string for the fake column name seems safer.
122964	*/
122965	if( pItem->colUsed==0 ){
122966	sqlite3AuthCheck(pParse, SQLITE_READ, pItem->zName, "", pItem->zDatabase);
122967	}
122968
	@@ -122934,35 +123009,32 @@
123009	SELECTTRACE(0x100,pParse,p,("After WHERE-clause push-down:\n"));
123010	sqlite3TreeViewSelect(0, p, 0);
123011	}
123012	#endif
123013	}
123014
123015	zSavedAuthContext = pParse->zAuthContext;
123016	pParse->zAuthContext = pItem->zName;
123017
123018	/* Generate code to implement the subquery
123019	**
123020	** The subquery is implemented as a co-routine if the subquery is
123021	** guaranteed to be the outer loop (so that it does not need to be
123022	** computed more than once)





123023	**
123024	** TODO: Are there other reasons beside (1) to use a co-routine
123025	** implementation?
123026	*/
123027	if( i==0
123028	&& (pTabList->nSrc==1
123029	\|\| (pTabList->a[1].fg.jointype&(JT_LEFT\|JT_CROSS))!=0) /* (1) */


123030	){
123031	/* Implement a co-routine that will return a single row of the result
123032	** set on each invocation.
123033	*/
123034	int addrTop = sqlite3VdbeCurrentAddr(v)+1;
123035
123036	pItem->regReturn = ++pParse->nMem;
123037	sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop);
123038	VdbeComment((v, "%s", pItem->pTab->zName));
123039	pItem->addrFillSub = addrTop;
123040	sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn);
	@@ -123016,10 +123088,11 @@
123088	sqlite3VdbeChangeP1(v, topAddr, retAddr);
123089	sqlite3ClearTempRegCache(pParse);
123090	}
123091	if( db->mallocFailed ) goto select_end;
123092	pParse->nHeight -= sqlite3SelectExprHeight(p);
123093	pParse->zAuthContext = zSavedAuthContext;
123094	#endif
123095	}
123096
123097	/* Various elements of the SELECT copied into local variables for
123098	** convenience */
	@@ -131021,11 +131094,10 @@
131094	** returned when it should not be, then incorrect answers might result.
131095	*/
131096	static int termIsEquivalence(Parse pParse, Expr pExpr){
131097	char aff1, aff2;
131098	CollSeq *pColl;

131099	if( !OptimizationEnabled(pParse->db, SQLITE_Transitive) ) return 0;
131100	if( pExpr->op!=TK_EQ && pExpr->op!=TK_IS ) return 0;
131101	if( ExprHasProperty(pExpr, EP_FromJoin) ) return 0;
131102	aff1 = sqlite3ExprAffinity(pExpr->pLeft);
131103	aff2 = sqlite3ExprAffinity(pExpr->pRight);
	@@ -131034,15 +131106,11 @@
131106	){
131107	return 0;
131108	}
131109	pColl = sqlite3BinaryCompareCollSeq(pParse, pExpr->pLeft, pExpr->pRight);
131110	if( pColl==0 \|\| sqlite3StrICmp(pColl->zName, "BINARY")==0 ) return 1;
131111	return sqlite3ExprCollSeqMatch(pParse, pExpr->pLeft, pExpr->pRight);




131112	}
131113
131114	/*
131115	** Recursively walk the expressions of a SELECT statement and generate
131116	** a bitmask indicating which tables are used in that expression
	@@ -132119,12 +132187,12 @@
132187	Expr *p = sqlite3ExprSkipCollate(pList->a[i].pExpr);
132188	if( p->op==TK_COLUMN
132189	&& p->iColumn==pIdx->aiColumn[iCol]
132190	&& p->iTable==iBase
132191	){
132192	CollSeq *pColl = sqlite3ExprNNCollSeq(pParse, pList->a[i].pExpr);
132193	if( 0==sqlite3StrICmp(pColl->zName, zColl) ){
132194	return i;
132195	}
132196	}
132197	}
132198
	@@ -134385,11 +134453,11 @@
134453	if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1;
134454	}
134455	}else if( (aColExpr = pIndex->aColExpr)!=0 ){
134456	for(jj=0; jj<pIndex->nKeyCol; jj++){
134457	if( pIndex->aiColumn[jj]!=XN_EXPR ) continue;
134458	if( sqlite3ExprCompareSkip(pExpr,aColExpr->a[jj].pExpr,iCursor)==0 ){
134459	return 1;
134460	}
134461	}
134462	}
134463	}
	@@ -135295,18 +135363,14 @@
135363	assert( wctrlFlags & WHERE_ORDERBY_LIMIT );
135364	for(j=0; j<pLoop->nLTerm && pTerm!=pLoop->aLTerm[j]; j++){}
135365	if( j>=pLoop->nLTerm ) continue;
135366	}
135367	if( (pTerm->eOperator&(WO_EQ\|WO_IS))!=0 && pOBExpr->iColumn>=0 ){
135368	if( sqlite3ExprCollSeqMatch(pWInfo->pParse,
135369	pOrderBy->a[i].pExpr, pTerm->pExpr)==0 ){
135370	continue;
135371	}




135372	testcase( pTerm->pExpr->op==TK_IS );
135373	}
135374	obSat \|= MASKBIT(i);
135375	}
135376
	@@ -135374,11 +135438,11 @@
135438	** (revIdx) for the j-th column of the index.
135439	*/
135440	if( pIndex ){
135441	iColumn = pIndex->aiColumn[j];
135442	revIdx = pIndex->aSortOrder[j];
135443	if( iColumn==pIndex->pTable->iPKey ) iColumn = XN_ROWID;
135444	}else{
135445	iColumn = XN_ROWID;
135446	revIdx = 0;
135447	}
135448
	@@ -135401,23 +135465,22 @@
135465	if( MASKBIT(i) & obSat ) continue;
135466	pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
135467	testcase( wctrlFlags & WHERE_GROUPBY );
135468	testcase( wctrlFlags & WHERE_DISTINCTBY );
135469	if( (wctrlFlags & (WHERE_GROUPBY\|WHERE_DISTINCTBY))==0 ) bOnce = 0;
135470	if( iColumn>=XN_ROWID ){
135471	if( pOBExpr->op!=TK_COLUMN ) continue;
135472	if( pOBExpr->iTable!=iCur ) continue;
135473	if( pOBExpr->iColumn!=iColumn ) continue;
135474	}else{
135475	Expr *pIdxExpr = pIndex->aColExpr->a[j].pExpr;
135476	if( sqlite3ExprCompareSkip(pOBExpr, pIdxExpr, iCur) ){
135477	continue;
135478	}
135479	}
135480	if( iColumn!=XN_ROWID ){
135481	pColl = sqlite3ExprNNCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);

135482	if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
135483	}
135484	pLoop->u.btree.nIdxCol = j+1;
135485	isMatch = 1;
135486	break;
	@@ -137095,11 +137158,12 @@
137158	** YYNSTATE the combined number of states.
137159	** YYNRULE the number of rules in the grammar
137160	** YY_MAX_SHIFT Maximum value for shift actions
137161	** YY_MIN_SHIFTREDUCE Minimum value for shift-reduce actions
137162	** YY_MAX_SHIFTREDUCE Maximum value for shift-reduce actions
137163	** YY_MIN_REDUCE Minimum value for reduce actions
137164	** YY_MAX_REDUCE Maximum value for reduce actions
137165	** YY_ERROR_ACTION The yy_action[] code for syntax error
137166	** YY_ACCEPT_ACTION The yy_action[] code for accept
137167	** YY_NO_ACTION The yy_action[] code for no-op
137168	*/
137169	#ifndef INTERFACE
	@@ -184892,11 +184956,12 @@
184956	** fts5YYNSTATE the combined number of states.
184957	** fts5YYNRULE the number of rules in the grammar
184958	** fts5YY_MAX_SHIFT Maximum value for shift actions
184959	** fts5YY_MIN_SHIFTREDUCE Minimum value for shift-reduce actions
184960	** fts5YY_MAX_SHIFTREDUCE Maximum value for shift-reduce actions
184961	** fts5YY_MIN_REDUCE Minimum value for reduce actions
184962	** fts5YY_MAX_REDUCE Maximum value for reduce actions
184963	** fts5YY_ERROR_ACTION The fts5yy_action[] code for syntax error
184964	** fts5YY_ACCEPT_ACTION The fts5yy_action[] code for accept
184965	** fts5YY_NO_ACTION The fts5yy_action[] code for no-op
184966	*/
184967	#ifndef INTERFACE
	@@ -200632,11 +200697,11 @@
200697	int nArg, /* Number of args */
200698	sqlite3_value *apUnused / Function arguments */
200699	){
200700	assert( nArg==0 );
200701	UNUSED_PARAM2(nArg, apUnused);
200702	sqlite3_result_text(pCtx, "fts5: 2017-09-29 16:07:56 0840f9f824c16212ce3fd6c859e501176eb0a58924ea1728a54d5bdfd0c25c86", -1, SQLITE_TRANSIENT);
200703	}
200704
200705	static int fts5Init(sqlite3 *db){
200706	static const sqlite3_module fts5Mod = {
200707	/* iVersion */ 2,
	@@ -204901,12 +204966,12 @@
204966	}
204967	#endif /* SQLITE_CORE */
204968	#endif /* !defined(SQLITE_CORE) \|\| defined(SQLITE_ENABLE_STMTVTAB) */
204969
204970	/************ End of stmt.c **********************************************/
204971	#if __LINE__!=204971
204972	#undef SQLITE_SOURCE_ID
204973	#define SQLITE_SOURCE_ID "2017-10-02 02:52:54 c9104b59c7ed360291f7f6fc8caae938e9840c77620d598e4096f78183bfalt2"
204974	#endif
204975	/* Return the source-id for this library */
204976	SQLITE_API const char *sqlite3_sourceid(void){ return SQLITE_SOURCE_ID; }
204977	/************************ End of sqlite3.c ****************************/
204978

M src/sqlite3.h

+1 -1

		--- src/sqlite3.h
		+++ src/sqlite3.h
		@@ -123,11 +123,11 @@
123	123	** [sqlite3_libversion_number()], [sqlite3_sourceid()],
124	124	** [sqlite_version()] and [sqlite_source_id()].
125	125	*/
126	126	#define SQLITE_VERSION "3.21.0"
127	127	#define SQLITE_VERSION_NUMBER 3021000
128		-#define SQLITE_SOURCE_ID "2017-09-21 20:43:48 5d03c738e93d36815248991d9ed3d62297ba1bb966e602e7874410076c144f43"
	128	+#define SQLITE_SOURCE_ID "2017-10-02 02:52:54 c9104b59c7ed360291f7f6fc8caae938e9840c77620d598e4096f78183bf807a"
129	129
130	130	/*
131	131	** CAPI3REF: Run-Time Library Version Numbers
132	132	** KEYWORDS: sqlite3_version sqlite3_sourceid
133	133	**
134	134

	--- src/sqlite3.h
	+++ src/sqlite3.h
	@@ -123,11 +123,11 @@
123	** [sqlite3_libversion_number()], [sqlite3_sourceid()],
124	** [sqlite_version()] and [sqlite_source_id()].
125	*/
126	#define SQLITE_VERSION "3.21.0"
127	#define SQLITE_VERSION_NUMBER 3021000
128	#define SQLITE_SOURCE_ID "2017-09-21 20:43:48 5d03c738e93d36815248991d9ed3d62297ba1bb966e602e7874410076c144f43"
129
130	/*
131	** CAPI3REF: Run-Time Library Version Numbers
132	** KEYWORDS: sqlite3_version sqlite3_sourceid
133	**
134

	--- src/sqlite3.h
	+++ src/sqlite3.h
	@@ -123,11 +123,11 @@
123	** [sqlite3_libversion_number()], [sqlite3_sourceid()],
124	** [sqlite_version()] and [sqlite_source_id()].
125	*/
126	#define SQLITE_VERSION "3.21.0"
127	#define SQLITE_VERSION_NUMBER 3021000
128	#define SQLITE_SOURCE_ID "2017-10-02 02:52:54 c9104b59c7ed360291f7f6fc8caae938e9840c77620d598e4096f78183bf807a"
129
130	/*
131	** CAPI3REF: Run-Time Library Version Numbers
132	** KEYWORDS: sqlite3_version sqlite3_sourceid
133	**
134

Fossil SCM

Keyboard Shortcuts