Fossil SCM

Update to the latest stable check-in of SQLite, as a beta-test for SQLite.

drh 2010-04-15 23:53 trunk

Commit 7c37b46b12331fda2b89b3d1ba11047174ccad06

Parent 0e7b85bb92ba336…

2 files changed +301 -84 +1 -3

M src/sqlite3.c

+301 -84

		--- src/sqlite3.c
		+++ src/sqlite3.c
		@@ -628,11 +628,11 @@
628	628	** [sqlite3_libversion_number()], [sqlite3_sourceid()],
629	629	** [sqlite_version()] and [sqlite_source_id()].
630	630	*/
631	631	#define SQLITE_VERSION "3.6.23"
632	632	#define SQLITE_VERSION_NUMBER 3006023
633		-#define SQLITE_SOURCE_ID "2010-04-07 20:32:19 e388fe8be878c80ef0bfd1699a7268cdb22cb3c6"
	633	+#define SQLITE_SOURCE_ID "2010-04-15 23:24:29 f96782b389b5b97b488dc5814f7082e0393f64cd"
634	634
635	635	/*
636	636	** CAPI3REF: Run-Time Library Version Numbers
637	637	** KEYWORDS: sqlite3_version, sqlite3_sourceid
638	638	**
		@@ -4404,12 +4404,10 @@
4404	4404	** ^For the purposes of this API, a transaction is said to have been
4405	4405	** rolled back if an explicit "ROLLBACK" statement is executed, or
4406	4406	** an error or constraint causes an implicit rollback to occur.
4407	4407	** ^The rollback callback is not invoked if a transaction is
4408	4408	** automatically rolled back because the database connection is closed.
4409		-** ^The rollback callback is not invoked if a transaction is
4410		-** rolled back because a commit callback returned non-zero.
4411	4409	**
4412	4410	** See also the [sqlite3_update_hook()] interface.
4413	4411	*/
4414	4412	SQLITE_API void sqlite3_commit_hook(sqlite3, int()(void), void*);
4415	4413	SQLITE_API void sqlite3_rollback_hook(sqlite3, void()(void ), void*);
		@@ -32356,10 +32354,91 @@
32356	32354	** file simultaneously, or one process from reading the database while
32357	32355	** another is writing.
32358	32356	*/
32359	32357	#ifndef SQLITE_OMIT_DISKIO
32360	32358
	32359	+/*
	32360	+****************** NOTES ON THE DESIGN OF THE PAGER **********************
	32361	+**
	32362	+** Within this comment block, a page is deemed to have been synced
	32363	+** automatically as soon as it is written when PRAGMA synchronous=OFF.
	32364	+** Otherwise, the page is not synced until the xSync method of the VFS
	32365	+** is called successfully on the file containing the page.
	32366	+**
	32367	+** Definition: A page of the database file is said to be "overwriteable" if
	32368	+** one or more of the following are true about the page:
	32369	+**
	32370	+** (a) The original content of the page as it was at the beginning of
	32371	+** the transaction has been written into the rollback journal and
	32372	+** synced.
	32373	+**
	32374	+** (b) The page was a freelist leaf page at the start of the transaction.
	32375	+**
	32376	+** (c) The page number is greater than the largest page that existed in
	32377	+** the database file at the start of the transaction.
	32378	+**
	32379	+** (1) A page of the database file is never overwritten unless one of the
	32380	+** following are true:
	32381	+**
	32382	+** (a) The page and all other pages on the same sector are overwriteable.
	32383	+**
	32384	+** (b) The atomic page write optimization is enabled, and the entire
	32385	+** transaction other than the update of the transaction sequence
	32386	+** number consists of a single page change.
	32387	+**
	32388	+** (2) The content of a page written into the rollback journal exactly matches
	32389	+** both the content in the database when the rollback journal was written
	32390	+** and the content in the database at the beginning of the current
	32391	+** transaction.
	32392	+**
	32393	+** (3) Writes to the database file are an integer multiple of the page size
	32394	+** in length and are aligned to a page boundary.
	32395	+**
	32396	+** (4) Reads from the database file are either aligned on a page boundary and
	32397	+** an integer multiple of the page size in length or are taken from the
	32398	+** first 100 bytes of the database file.
	32399	+**
	32400	+** (5) All writes to the database file are synced prior to the rollback journal
	32401	+** being deleted, truncated, or zeroed.
	32402	+**
	32403	+** (6) If a master journal file is used, then all writes to the database file
	32404	+** are synced prior to the master journal being deleted.
	32405	+**
	32406	+** Definition: Two databases (or the same database at two points it time)
	32407	+** are said to be "logically equivalent" if they give the same answer to
	32408	+** all queries. Note in particular the the content of freelist leaf
	32409	+** pages can be changed arbitarily without effecting the logical equivalence
	32410	+** of the database.
	32411	+**
	32412	+** (7) At any time, if any subset, including the empty set and the total set,
	32413	+** of the unsynced changes to a rollback journal are removed and the
	32414	+** journal is rolled back, the resulting database file will be logical
	32415	+** equivalent to the database file at the beginning of the transaction.
	32416	+**
	32417	+** (8) When a transaction is rolled back, the xTruncate method of the VFS
	32418	+** is called to restore the database file to the same size it was at
	32419	+** the beginning of the transaction. (In some VFSes, the xTruncate
	32420	+** method is a no-op, but that does not change the fact the SQLite will
	32421	+** invoke it.)
	32422	+**
	32423	+** (9) Whenever the database file is modified, at least one bit in the range
	32424	+** of bytes from 24 through 39 inclusive will be changed prior to releasing
	32425	+** the EXCLUSIVE lock.
	32426	+**
	32427	+** (10) The pattern of bits in bytes 24 through 39 shall not repeat in less
	32428	+** than one billion transactions.
	32429	+**
	32430	+** (11) A database file is well-formed at the beginning and at the conclusion
	32431	+** of every transaction.
	32432	+**
	32433	+** (12) An EXCLUSIVE lock is held on the database file when writing to
	32434	+** the database file.
	32435	+**
	32436	+** (13) A SHARED lock is held on the database file while reading any
	32437	+** content out of the database file.
	32438	+*/
	32439	+
32361	32440	/*
32362	32441	** Macros for troubleshooting. Normally turned off
32363	32442	*/
32364	32443	#if 0
32365	32444	int sqlite3PagerTrace=1; /* True to enable tracing */
		@@ -32532,11 +32611,12 @@
32532	32611	** It is used when committing or otherwise ending a transaction. If
32533	32612	** the dbModified flag is clear then less work has to be done.
32534	32613	**
32535	32614	** journalStarted
32536	32615	**
32537		-** This flag is set whenever the the main journal is synced.
	32616	+** This flag is set whenever the the main journal is opened and
	32617	+** initialized
32538	32618	**
32539	32619	** The point of this flag is that it must be set after the
32540	32620	** first journal header in a journal file has been synced to disk.
32541	32621	** After this has happened, new pages appended to the database
32542	32622	** do not need the PGHDR_NEED_SYNC flag set, as they do not need
		@@ -32558,11 +32638,14 @@
32558	32638	** master journal name is only written to the journal file the first
32559	32639	** time CommitPhaseOne() is called.
32560	32640	**
32561	32641	** doNotSync
32562	32642	**
32563		-** This variable is set and cleared by sqlite3PagerWrite().
	32643	+** When enabled, cache spills are prohibited and the journal file cannot
	32644	+** be synced. This variable is set and cleared by sqlite3PagerWrite()
	32645	+** in order to prevent a journal sync from happening in between the
	32646	+** journalling of two pages on the same sector.
32564	32647	**
32565	32648	** needSync
32566	32649	**
32567	32650	** TODO: It might be easier to set this variable in writeJournalHdr()
32568	32651	** and writeMasterJournal() only. Change its meaning to "unsynced data
		@@ -32618,10 +32701,11 @@
32618	32701	sqlite3_file fd; / File descriptor for database */
32619	32702	sqlite3_file jfd; / File descriptor for main journal */
32620	32703	sqlite3_file sjfd; / File descriptor for sub-journal */
32621	32704	i64 journalOff; /* Current write offset in the journal file */
32622	32705	i64 journalHdr; /* Byte offset to previous journal header */
	32706	+ i64 journalSizeLimit; /* Size limit for persistent journal files */
32623	32707	PagerSavepoint aSavepoint; / Array of active savepoints */
32624	32708	int nSavepoint; /* Number of elements in aSavepoint[] */
32625	32709	char dbFileVers[16]; /* Changes whenever database file changes */
32626	32710	u32 sectorSize; /* Assumed sector size during rollback */
32627	32711
		@@ -32644,11 +32728,10 @@
32644	32728	void (xCodecSizeChng)(void,int,int); /* Notify of page size changes */
32645	32729	void (xCodecFree)(void); /* Destructor for the codec */
32646	32730	void pCodec; / First argument to xCodec... methods */
32647	32731	#endif
32648	32732	char pTmpSpace; / Pager.pageSize bytes of space for tmp use */
32649		- i64 journalSizeLimit; /* Size limit for persistent journal files */
32650	32733	PCache pPCache; / Pointer to page cache object */
32651	32734	sqlite3_backup pBackup; / Pointer to list of ongoing backup processes */
32652	32735	};
32653	32736
32654	32737	/*
		@@ -33160,10 +33243,11 @@
33160	33243	** to populate the entire journal header sector.
33161	33244	*/
33162	33245	for(nWrite=0; rc==SQLITE_OK&&nWrite<JOURNAL_HDR_SZ(pPager); nWrite+=nHeader){
33163	33246	IOTRACE(("JHDR %p %lld %d\n", pPager, pPager->journalHdr, nHeader))
33164	33247	rc = sqlite3OsWrite(pPager->jfd, zHeader, nHeader, pPager->journalOff);
	33248	+ assert( pPager->journalHdr <= pPager->journalOff );
33165	33249	pPager->journalOff += nHeader;
33166	33250	}
33167	33251
33168	33252	return rc;
33169	33253	}
		@@ -33318,10 +33402,11 @@
33318	33402	){
33319	33403	return SQLITE_OK;
33320	33404	}
33321	33405	pPager->setMaster = 1;
33322	33406	assert( isOpen(pPager->jfd) );
	33407	+ assert( pPager->journalHdr <= pPager->journalOff );
33323	33408
33324	33409	/* Calculate the length in bytes and the checksum of zMaster */
33325	33410	for(nMaster=0; zMaster[nMaster]; nMaster++){
33326	33411	cksum += zMaster[nMaster];
33327	33412	}
		@@ -33747,22 +33832,22 @@
33747	33832	** allocated by this function. If this is the case and an allocation fails,
33748	33833	** SQLITE_NOMEM is returned.
33749	33834	*/
33750	33835	static int pager_playback_one_page(
33751	33836	Pager pPager, / The pager being played back */
33752		- int isMainJrnl, /* 1 -> main journal. 0 -> sub-journal. */
33753		- int isUnsync, /* True if reading from unsynced main journal */
33754	33837	i64 pOffset, / Offset of record to playback */
33755		- int isSavepnt, /* True for a savepoint rollback */
33756		- Bitvec pDone / Bitvec of pages already played back */
	33838	+ Bitvec pDone, / Bitvec of pages already played back */
	33839	+ int isMainJrnl, /* 1 -> main journal. 0 -> sub-journal. */
	33840	+ int isSavepnt /* True for a savepoint rollback */
33757	33841	){
33758	33842	int rc;
33759	33843	PgHdr pPg; / An existing page in the cache */
33760	33844	Pgno pgno; /* The page number of a page in journal */
33761	33845	u32 cksum; /* Checksum used for sanity checking */
33762	33846	char aData; / Temporary storage for the page */
33763	33847	sqlite3_file jfd; / The file descriptor for the journal file */
	33848	+ int isSynced; /* True if journal page is synced */
33764	33849
33765	33850	assert( (isMainJrnl&~1)==0 ); /* isMainJrnl is 0 or 1 */
33766	33851	assert( (isSavepnt&~1)==0 ); /* isSavepnt is 0 or 1 */
33767	33852	assert( isMainJrnl \|\| pDone ); /* pDone always used on sub-journals */
33768	33853	assert( isSavepnt \|\| pDone==0 ); /* pDone never used on non-savepoint */
		@@ -33842,16 +33927,21 @@
33842	33927	assert( pPg \|\| !MEMDB );
33843	33928	PAGERTRACE(("PLAYBACK %d page %d hash(%08x) %s\n",
33844	33929	PAGERID(pPager), pgno, pager_datahash(pPager->pageSize, (u8*)aData),
33845	33930	(isMainJrnl?"main-journal":"sub-journal")
33846	33931	));
	33932	+ if( isMainJrnl ){
	33933	+ isSynced = pPager->noSync \|\| (*pOffset <= pPager->journalHdr);
	33934	+ }else{
	33935	+ isSynced = (pPg==0 \|\| 0==(pPg->flags & PGHDR_NEED_SYNC));
	33936	+ }
33847	33937	if( (pPager->state>=PAGER_EXCLUSIVE)
33848		- && (pPg==0 \|\| 0==(pPg->flags&PGHDR_NEED_SYNC))
33849	33938	&& isOpen(pPager->fd)
33850		- && !isUnsync
	33939	+ && isSynced
33851	33940	){
33852	33941	i64 ofst = (pgno-1)*(i64)pPager->pageSize;
	33942	+ testcase( !isSavepnt && pPg!=0 && (pPg->flags&PGHDR_NEED_SYNC)!=0 );
33853	33943	rc = sqlite3OsWrite(pPager->fd, (u8*)aData, pPager->pageSize, ofst);
33854	33944	if( pgno>pPager->dbFileSize ){
33855	33945	pPager->dbFileSize = pgno;
33856	33946	}
33857	33947	if( pPager->pBackup ){
		@@ -33896,11 +33986,12 @@
33896	33986	pPager->xReiniter(pPg);
33897	33987	if( isMainJrnl && (!isSavepnt \|\| *pOffset<=pPager->journalHdr) ){
33898	33988	/* If the contents of this page were just restored from the main
33899	33989	** journal file, then its content must be as they were when the
33900	33990	** transaction was first opened. In this case we can mark the page
33901		- ** as clean, since there will be no need to write it out to the.
	33991	+ ** as clean, since there will be no need to write it out to the
	33992	+ ** database.
33902	33993	**
33903	33994	** There is one exception to this rule. If the page is being rolled
33904	33995	** back as part of a savepoint (or statement) rollback from an
33905	33996	** unsynced portion of the main journal file, then it is not safe
33906	33997	** to mark the page as clean. This is because marking the page as
		@@ -34243,12 +34334,10 @@
34243	34334	/* This loop terminates either when a readJournalHdr() or
34244	34335	** pager_playback_one_page() call returns SQLITE_DONE or an IO error
34245	34336	** occurs.
34246	34337	*/
34247	34338	while( 1 ){
34248		- int isUnsync = 0;
34249		-
34250	34339	/* Read the next journal header from the journal file. If there are
34251	34340	** not enough bytes left in the journal file for a complete header, or
34252	34341	** it is corrupted, then a process must of failed while writing it.
34253	34342	** This indicates nothing more needs to be rolled back.
34254	34343	*/
		@@ -34285,11 +34374,10 @@
34285	34374	** should be computed based on the journal file size.
34286	34375	*/
34287	34376	if( nRec==0 && !isHot &&
34288	34377	pPager->journalHdr+JOURNAL_HDR_SZ(pPager)==pPager->journalOff ){
34289	34378	nRec = (int)((szJ - pPager->journalOff) / JOURNAL_PG_SZ(pPager));
34290		- isUnsync = 1;
34291	34379	}
34292	34380
34293	34381	/* If this is the first header read from the journal, truncate the
34294	34382	** database file back to its original size.
34295	34383	*/
		@@ -34307,11 +34395,11 @@
34307	34395	for(u=0; u<nRec; u++){
34308	34396	if( needPagerReset ){
34309	34397	pager_reset(pPager);
34310	34398	needPagerReset = 0;
34311	34399	}
34312		- rc = pager_playback_one_page(pPager,1,isUnsync,&pPager->journalOff,0,0);
	34400	+ rc = pager_playback_one_page(pPager,&pPager->journalOff,0,1,0);
34313	34401	if( rc!=SQLITE_OK ){
34314	34402	if( rc==SQLITE_DONE ){
34315	34403	rc = SQLITE_OK;
34316	34404	pPager->journalOff = szJ;
34317	34405	break;
		@@ -34460,11 +34548,11 @@
34460	34548	*/
34461	34549	if( pSavepoint ){
34462	34550	iHdrOff = pSavepoint->iHdrOffset ? pSavepoint->iHdrOffset : szJ;
34463	34551	pPager->journalOff = pSavepoint->iOffset;
34464	34552	while( rc==SQLITE_OK && pPager->journalOff<iHdrOff ){
34465		- rc = pager_playback_one_page(pPager, 1, 0, &pPager->journalOff, 1, pDone);
	34553	+ rc = pager_playback_one_page(pPager, &pPager->journalOff, pDone, 1, 1);
34466	34554	}
34467	34555	assert( rc!=SQLITE_DONE );
34468	34556	}else{
34469	34557	pPager->journalOff = 0;
34470	34558	}
		@@ -34490,11 +34578,11 @@
34490	34578	&& pPager->journalHdr+JOURNAL_HDR_SZ(pPager)==pPager->journalOff
34491	34579	){
34492	34580	nJRec = (u32)((szJ - pPager->journalOff)/JOURNAL_PG_SZ(pPager));
34493	34581	}
34494	34582	for(ii=0; rc==SQLITE_OK && ii<nJRec && pPager->journalOff<szJ; ii++){
34495		- rc = pager_playback_one_page(pPager, 1, 0, &pPager->journalOff, 1, pDone);
	34583	+ rc = pager_playback_one_page(pPager, &pPager->journalOff, pDone, 1, 1);
34496	34584	}
34497	34585	assert( rc!=SQLITE_DONE );
34498	34586	}
34499	34587	assert( rc!=SQLITE_OK \|\| pPager->journalOff==szJ );
34500	34588
		@@ -34505,11 +34593,11 @@
34505	34593	if( pSavepoint ){
34506	34594	u32 ii; /* Loop counter */
34507	34595	i64 offset = pSavepoint->iSubRec*(4+pPager->pageSize);
34508	34596	for(ii=pSavepoint->iSubRec; rc==SQLITE_OK && ii<pPager->nSubRec; ii++){
34509	34597	assert( offset==ii*(4+pPager->pageSize) );
34510		- rc = pager_playback_one_page(pPager, 0, 0, &offset, 1, pDone);
	34598	+ rc = pager_playback_one_page(pPager, &offset, pDone, 0, 1);
34511	34599	}
34512	34600	assert( rc!=SQLITE_DONE );
34513	34601	}
34514	34602
34515	34603	sqlite3BitvecDestroy(pDone);
		@@ -34942,10 +35030,35 @@
34942	35030	assert( pPager->dbSize>=nPage );
34943	35031	assert( pPager->state>=PAGER_RESERVED );
34944	35032	pPager->dbSize = nPage;
34945	35033	assertTruncateConstraint(pPager);
34946	35034	}
	35035	+
	35036	+/*
	35037	+** This function is called before attempting a hot-journal rollback. It
	35038	+** syncs the journal file to disk, then sets pPager->journalHdr to the
	35039	+** size of the journal file so that the pager_playback() routine knows
	35040	+** that the entire journal file has been synced.
	35041	+**
	35042	+** Syncing a hot-journal to disk before attempting to roll it back ensures
	35043	+** that if a power-failure occurs during the rollback, the process that
	35044	+** attempts rollback following system recovery sees the same journal
	35045	+** content as this process.
	35046	+**
	35047	+** If everything goes as planned, SQLITE_OK is returned. Otherwise,
	35048	+** an SQLite error code.
	35049	+*/
	35050	+static int pagerSyncHotJournal(Pager *pPager){
	35051	+ int rc = SQLITE_OK;
	35052	+ if( !pPager->noSync ){
	35053	+ rc = sqlite3OsSync(pPager->jfd, SQLITE_SYNC_NORMAL);
	35054	+ }
	35055	+ if( rc==SQLITE_OK ){
	35056	+ rc = sqlite3OsFileSize(pPager->jfd, &pPager->journalHdr);
	35057	+ }
	35058	+ return rc;
	35059	+}
34947	35060
34948	35061	/*
34949	35062	** Shutdown the page cache. Free all memory and close all files.
34950	35063	**
34951	35064	** If a transaction was in progress when this routine is called, that
		@@ -34972,11 +35085,13 @@
34972	35085	** call which may be made from within pagerUnlockAndRollback(). If it
34973	35086	** is not -1, then the unsynced portion of an open journal file may
34974	35087	** be played back into the database. If a power failure occurs while
34975	35088	** this is happening, the database may become corrupt.
34976	35089	*/
34977		- pPager->journalHdr = -1;
	35090	+ if( isOpen(pPager->jfd) ){
	35091	+ pPager->errCode = pagerSyncHotJournal(pPager);
	35092	+ }
34978	35093	pagerUnlockAndRollback(pPager);
34979	35094	}
34980	35095	sqlite3EndBenignMalloc();
34981	35096	enable_simulated_io_errors();
34982	35097	PAGERTRACE(("CLOSE %d\n", PAGERID(pPager)));
		@@ -35062,11 +35177,11 @@
35062	35177	/* This block deals with an obscure problem. If the last connection
35063	35178	** that wrote to this database was operating in persistent-journal
35064	35179	** mode, then the journal file may at this point actually be larger
35065	35180	** than Pager.journalOff bytes. If the next thing in the journal
35066	35181	** file happens to be a journal-header (written as part of the
35067		- ** previous connections transaction), and a crash or power-failure
	35182	+ ** previous connection's transaction), and a crash or power-failure
35068	35183	** occurs after nRec is updated but before this connection writes
35069	35184	** anything else to the journal file (or commits/rolls back its
35070	35185	** transaction), then SQLite may become confused when doing the
35071	35186	** hot-journal rollback following recovery. It may roll back all
35072	35187	** of this connections data, then proceed to rolling back the old,
		@@ -35134,10 +35249,11 @@
35134	35249	/* The journal file was just successfully synced. Set Pager.needSync
35135	35250	** to zero and clear the PGHDR_NEED_SYNC flag on all pagess.
35136	35251	*/
35137	35252	pPager->needSync = 0;
35138	35253	pPager->journalStarted = 1;
	35254	+ pPager->journalHdr = pPager->journalOff;
35139	35255	sqlite3PcacheClearSyncFlags(pPager->pPCache);
35140	35256	}
35141	35257
35142	35258	return SQLITE_OK;
35143	35259	}
		@@ -35996,23 +36112,32 @@
35996	36112	}
35997	36113	if( rc!=SQLITE_OK ){
35998	36114	goto failed;
35999	36115	}
36000	36116
36001		- /* TODO: Why are these cleared here? Is it necessary? */
	36117	+ /* Reset the journal status fields to indicates that we have no
	36118	+ ** rollback journal at this time. */
36002	36119	pPager->journalStarted = 0;
36003	36120	pPager->journalOff = 0;
36004	36121	pPager->setMaster = 0;
36005	36122	pPager->journalHdr = 0;
	36123	+
	36124	+ /* Make sure the journal file has been synced to disk. */
36006	36125
36007	36126	/* Playback and delete the journal. Drop the database write
36008	36127	** lock and reacquire the read lock. Purge the cache before
36009	36128	** playing back the hot-journal so that we don't end up with
36010		- ** an inconsistent cache.
	36129	+ ** an inconsistent cache. Sync the hot journal before playing
	36130	+ ** it back since the process that crashed and left the hot journal
	36131	+ ** probably did not sync it and we are required to always sync
	36132	+ ** the journal before playing it back.
36011	36133	*/
36012	36134	if( isOpen(pPager->jfd) ){
36013		- rc = pager_playback(pPager, 1);
	36135	+ rc = pagerSyncHotJournal(pPager);
	36136	+ if( rc==SQLITE_OK ){
	36137	+ rc = pager_playback(pPager, 1);
	36138	+ }
36014	36139	if( rc!=SQLITE_OK ){
36015	36140	rc = pager_error(pPager, rc);
36016	36141	goto failed;
36017	36142	}
36018	36143	}
		@@ -36114,11 +36239,11 @@
36114	36239	** of the page. This occurs in two seperate scenarios:
36115	36240	**
36116	36241	** a) When reading a free-list leaf page from the database, and
36117	36242	**
36118	36243	** b) When a savepoint is being rolled back and we need to load
36119		-** a new page into the cache to populate with the data read
	36244	+** a new page into the cache to be filled with the data read
36120	36245	** from the savepoint journal.
36121	36246	**
36122	36247	** If noContent is true, then the data returned is zeroed instead of
36123	36248	** being read from the database. Additionally, the bits corresponding
36124	36249	** to pgno in Pager.pInJournal (bitvec of pages already written to the
		@@ -36546,10 +36671,12 @@
36546	36671
36547	36672	/* We should never write to the journal file the page that
36548	36673	** contains the database locks. The following assert verifies
36549	36674	** that we do not. */
36550	36675	assert( pPg->pgno!=PAGER_MJ_PGNO(pPager) );
	36676	+
	36677	+ assert( pPager->journalHdr <= pPager->journalOff );
36551	36678	CODEC2(pPager, pData, pPg->pgno, 7, return SQLITE_NOMEM, pData2);
36552	36679	cksum = pager_cksum(pPager, (u8*)pData2);
36553	36680	rc = write32bits(pPager->jfd, pPager->journalOff, pPg->pgno);
36554	36681	if( rc==SQLITE_OK ){
36555	36682	rc = sqlite3OsWrite(pPager->jfd, pData2, pPager->pageSize,
		@@ -60151,15 +60278,14 @@
60151	60278	** Syncing an in-memory journal is a no-op. And, in fact, this routine
60152	60279	** is never called in a working implementation. This implementation
60153	60280	** exists purely as a contingency, in case some malfunction in some other
60154	60281	** part of SQLite causes Sync to be called by mistake.
60155	60282	*/
60156		-static int memjrnlSync(sqlite3_file NotUsed, int NotUsed2){ /NO_TEST*/
60157		- UNUSED_PARAMETER2(NotUsed, NotUsed2); /NO_TEST/
60158		- assert( 0 ); /NO_TEST/
60159		- return SQLITE_OK; /NO_TEST/
60160		-} /NO_TEST/
	60283	+static int memjrnlSync(sqlite3_file *NotUsed, int NotUsed2){
	60284	+ UNUSED_PARAMETER2(NotUsed, NotUsed2);
	60285	+ return SQLITE_OK;
	60286	+}
60161	60287
60162	60288	/*
60163	60289	** Query the size of the file in bytes.
60164	60290	*/
60165	60291	static int memjrnlFileSize(sqlite3_file pJfd, sqlite_int64 pSize){
		@@ -60751,11 +60877,11 @@
60751	60877	}
60752	60878	}
60753	60879
60754	60880	/*
60755	60881	** Allocate and return a pointer to an expression to load the column iCol
60756		-** from datasource iSrc datasource in SrcList pSrc.
	60882	+** from datasource iSrc in SrcList pSrc.
60757	60883	*/
60758	60884	SQLITE_PRIVATE Expr sqlite3CreateColumnExpr(sqlite3 db, SrcList *pSrc, int iSrc, int iCol){
60759	60885	Expr *p = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
60760	60886	if( p ){
60761	60887	struct SrcList_item *pItem = &pSrc->a[iSrc];
		@@ -60763,10 +60889,12 @@
60763	60889	p->iTable = pItem->iCursor;
60764	60890	if( p->pTab->iPKey==iCol ){
60765	60891	p->iColumn = -1;
60766	60892	}else{
60767	60893	p->iColumn = (ynVar)iCol;
	60894	+ testcase( iCol==BMS );
	60895	+ testcase( iCol==BMS-1 );
60768	60896	pItem->colUsed \|= ((Bitmask)1)<<(iCol>=BMS ? BMS-1 : iCol);
60769	60897	}
60770	60898	ExprSetProperty(p, EP_Resolved);
60771	60899	}
60772	60900	return p;
		@@ -79646,32 +79774,32 @@
79646	79774	/* Call the parser to process a CREATE TABLE, INDEX or VIEW.
79647	79775	** But because db->init.busy is set to 1, no VDBE code is generated
79648	79776	** or executed. All the parser does is build the internal data
79649	79777	** structures that describe the table, index, or view.
79650	79778	*/
79651		- char *zErr;
79652	79779	int rc;
	79780	+ sqlite3_stmt *pStmt;
	79781	+
79653	79782	assert( db->init.busy );
79654	79783	db->init.iDb = iDb;
79655	79784	db->init.newTnum = atoi(argv[1]);
79656	79785	db->init.orphanTrigger = 0;
79657		- rc = sqlite3_exec(db, argv[2], 0, 0, &zErr);
	79786	+ rc = sqlite3_prepare(db, argv[2], -1, &pStmt, 0);
79658	79787	db->init.iDb = 0;
79659		- assert( rc!=SQLITE_OK \|\| zErr==0 );
79660	79788	if( SQLITE_OK!=rc ){
79661	79789	if( db->init.orphanTrigger ){
79662	79790	assert( iDb==1 );
79663	79791	}else{
79664	79792	pData->rc = rc;
79665	79793	if( rc==SQLITE_NOMEM ){
79666	79794	db->mallocFailed = 1;
79667	79795	}else if( rc!=SQLITE_INTERRUPT && rc!=SQLITE_LOCKED ){
79668		- corruptSchema(pData, argv[0], zErr);
	79796	+ corruptSchema(pData, argv[0], sqlite3_errmsg(db));
79669	79797	}
79670	79798	}
79671		- sqlite3DbFree(db, zErr);
79672	79799	}
	79800	+ sqlite3_finalize(pStmt);
79673	79801	}else if( argv[0]==0 ){
79674	79802	corruptSchema(pData, 0, 0);
79675	79803	}else{
79676	79804	/* If the SQL column is blank it means this is an index that
79677	79805	** was created to be the PRIMARY KEY or to fulfill a UNIQUE
		@@ -82959,12 +83087,12 @@
82959	83087	** (13) The subquery and outer query do not both use LIMIT
82960	83088	**
82961	83089	** (14) The subquery does not use OFFSET
82962	83090	**
82963	83091	** (15) The outer query is not part of a compound select or the
82964		-** subquery does not have both an ORDER BY and a LIMIT clause.
82965		-** (See ticket #2339)
	83092	+** subquery does not have a LIMIT clause.
	83093	+** (See ticket #2339 and ticket [02a8e81d44]).
82966	83094	**
82967	83095	** (16) The outer query is not an aggregate or the subquery does
82968	83096	** not contain ORDER BY. (Ticket #2942) This used to not matter
82969	83097	** until we introduced the group_concat() function.
82970	83098	**
		@@ -83043,11 +83171,11 @@
83043	83171	** because they could be computed at compile-time. But when LIMIT and OFFSET
83044	83172	** became arbitrary expressions, we were forced to add restrictions (13)
83045	83173	** and (14). */
83046	83174	if( pSub->pLimit && p->pLimit ) return 0; /* Restriction (13) */
83047	83175	if( pSub->pOffset ) return 0; /* Restriction (14) */
83048		- if( p->pRightmost && pSub->pLimit && pSub->pOrderBy ){
	83176	+ if( p->pRightmost && pSub->pLimit ){
83049	83177	return 0; /* Restriction (15) */
83050	83178	}
83051	83179	if( pSubSrc->nSrc==0 ) return 0; /* Restriction (7) */
83052	83180	if( ((pSub->selFlags & SF_Distinct)!=0 \|\| pSub->pLimit)
83053	83181	&& (pSrc->nSrc>1 \|\| isAgg) ){ /* Restrictions (4)(5)(8)(9) */
		@@ -89537,10 +89665,15 @@
89537	89665	#ifndef SQLITE_OMIT_OR_OPTIMIZATION
89538	89666	const int iCur = pSrc->iCursor; /* The cursor of the table to be accessed */
89539	89667	const Bitmask maskSrc = getMask(pWC->pMaskSet, iCur); /* Bitmask for pSrc */
89540	89668	WhereTerm * const pWCEnd = &pWC->a[pWC->nTerm]; /* End of pWC->a[] */
89541	89669	WhereTerm pTerm; / A single term of the WHERE clause */
	89670	+
	89671	+ /* No OR-clause optimization allowed if the NOT INDEXED clause is used */
	89672	+ if( pSrc->notIndexed ){
	89673	+ return;
	89674	+ }
89542	89675
89543	89676	/* Search the WHERE clause terms for a usable WO_OR term. */
89544	89677	for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
89545	89678	if( pTerm->eOperator==WO_OR
89546	89679	&& ((pTerm->prereqAll & ~maskSrc) & notReady)==0
		@@ -89580,12 +89713,13 @@
89580	89713	}
89581	89714
89582	89715	/* If there is an ORDER BY clause, increase the scan cost to account
89583	89716	** for the cost of the sort. */
89584	89717	if( pOrderBy!=0 ){
	89718	+ WHERETRACE(("... sorting increases OR cost %.9g to %.9g\n",
	89719	+ rTotal, rTotal+nRow*estLog(nRow)));
89585	89720	rTotal += nRow*estLog(nRow);
89586		- WHERETRACE(("... sorting increases OR cost to %.9g\n", rTotal));
89587	89721	}
89588	89722
89589	89723	/* If the cost of scanning using this OR term for optimization is
89590	89724	** less than the current cost stored in pCost, replace the contents
89591	89725	** of pCost. */
		@@ -89658,21 +89792,21 @@
89658	89792	/* The NOT INDEXED clause appears in the SQL. */
89659	89793	return;
89660	89794	}
89661	89795
89662	89796	assert( pParse->nQueryLoop >= (double)1 );
89663		- nTableRow = pSrc->pIndex ? pSrc->pIndex->aiRowEst[0] : 1000000;
	89797	+ pTable = pSrc->pTab;
	89798	+ nTableRow = pTable->pIndex ? pTable->pIndex->aiRowEst[0] : 1000000;
89664	89799	logN = estLog(nTableRow);
89665	89800	costTempIdx = 2logN(nTableRow/pParse->nQueryLoop + 1);
89666	89801	if( costTempIdx>=pCost->rCost ){
89667	89802	/* The cost of creating the transient table would be greater than
89668	89803	** doing the full table scan */
89669	89804	return;
89670	89805	}
89671	89806
89672	89807	/* Search for any equality comparison term */
89673		- pTable = pSrc->pTab;
89674	89808	pWCEnd = &pWC->a[pWC->nTerm];
89675	89809	for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
89676	89810	if( termCanDriveIndex(pTerm, pSrc, notReady) ){
89677	89811	WHERETRACE(("auto-index reduces cost from %.2f to %.2f\n",
89678	89812	pCost->rCost, costTempIdx));
		@@ -89736,12 +89870,17 @@
89736	89870	pWCEnd = &pWC->a[pWC->nTerm];
89737	89871	idxCols = 0;
89738	89872	for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
89739	89873	if( termCanDriveIndex(pTerm, pSrc, notReady) ){
89740	89874	int iCol = pTerm->u.leftColumn;
89741		- if( iCol<BMS && iCol>=0 ) idxCols \|= 1<<iCol;
89742		- nColumn++;
	89875	+ Bitmask cMask = iCol>=BMS ? ((Bitmask)1)<<(BMS-1) : ((Bitmask)1)<<iCol;
	89876	+ testcase( iCol==BMS );
	89877	+ testcase( iCol==BMS-1 );
	89878	+ if( (idxCols & cMask)==0 ){
	89879	+ nColumn++;
	89880	+ idxCols \|= cMask;
	89881	+ }
89743	89882	}
89744	89883	}
89745	89884	assert( nColumn>0 );
89746	89885	pLevel->plan.nEq = nColumn;
89747	89886
		@@ -89751,14 +89890,16 @@
89751	89890	** original table never needs to be accessed. Automatic indices must
89752	89891	** be a covering index because the index will not be updated if the
89753	89892	** original table changes and the index and table cannot both be used
89754	89893	** if they go out of sync.
89755	89894	*/
89756		- extraCols = pSrc->colUsed & ~idxCols;
	89895	+ extraCols = pSrc->colUsed & (~idxCols \| (((Bitmask)1)<<(BMS-1)));
89757	89896	mxBitCol = (pTable->nCol >= BMS-1) ? BMS-1 : pTable->nCol;
	89897	+ testcase( pTable->nCol==BMS-1 );
	89898	+ testcase( pTable->nCol==BMS-2 );
89758	89899	for(i=0; i<mxBitCol; i++){
89759		- if( extraCols & (1<<i) ) nColumn++;
	89900	+ if( extraCols & (((Bitmask)1)<<i) ) nColumn++;
89760	89901	}
89761	89902	if( pSrc->colUsed & (((Bitmask)1)<<(BMS-1)) ){
89762	89903	nColumn += pTable->nCol - BMS + 1;
89763	89904	}
89764	89905	pLevel->plan.wsFlags \|= WHERE_COLUMN_EQ \| WHERE_IDX_ONLY \| WO_EQ;
		@@ -89776,25 +89917,31 @@
89776	89917	pIdx->aSortOrder = (u8*)&pIdx->aiColumn[nColumn];
89777	89918	pIdx->zName = "auto-index";
89778	89919	pIdx->nColumn = nColumn;
89779	89920	pIdx->pTable = pTable;
89780	89921	n = 0;
	89922	+ idxCols = 0;
89781	89923	for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
89782	89924	if( termCanDriveIndex(pTerm, pSrc, notReady) ){
89783		- Expr *pX = pTerm->pExpr;
89784		- pIdx->aiColumn[n] = pTerm->u.leftColumn;
89785		- pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
89786		- pIdx->azColl[n] = pColl->zName;
89787		- n++;
	89925	+ int iCol = pTerm->u.leftColumn;
	89926	+ Bitmask cMask = iCol>=BMS ? ((Bitmask)1)<<(BMS-1) : ((Bitmask)1)<<iCol;
	89927	+ if( (idxCols & cMask)==0 ){
	89928	+ Expr *pX = pTerm->pExpr;
	89929	+ idxCols \|= cMask;
	89930	+ pIdx->aiColumn[n] = pTerm->u.leftColumn;
	89931	+ pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
	89932	+ pIdx->azColl[n] = pColl->zName;
	89933	+ n++;
	89934	+ }
89788	89935	}
89789	89936	}
89790	89937	assert( n==pLevel->plan.nEq );
89791	89938
89792	89939	/* Add additional columns needed to make the automatic index into
89793	89940	** a covering index */
89794	89941	for(i=0; i<mxBitCol; i++){
89795		- if( extraCols & (1<<i) ){
	89942	+ if( extraCols & (((Bitmask)1)<<i) ){
89796	89943	pIdx->aiColumn[n] = i;
89797	89944	pIdx->azColl[n] = "BINARY";
89798	89945	n++;
89799	89946	}
89800	89947	}
		@@ -90519,18 +90666,18 @@
90519	90666	**
90520	90667	** bInEst:
90521	90668	** Set to true if there was at least one "x IN (SELECT ...)" term used
90522	90669	** in determining the value of nInMul.
90523	90670	**
90524		- ** nBound:
	90671	+ ** estBound:
90525	90672	** An estimate on the amount of the table that must be searched. A
90526	90673	** value of 100 means the entire table is searched. Range constraints
90527	90674	** might reduce this to a value less than 100 to indicate that only
90528	90675	** a fraction of the table needs searching. In the absence of
90529	90676	** sqlite_stat2 ANALYZE data, a single inequality reduces the search
90530	90677	** space to 1/3rd its original size. So an x>? constraint reduces
90531		- ** nBound to 33. Two constraints (x>? AND x<?) reduce nBound to 11.
	90678	+ ** estBound to 33. Two constraints (x>? AND x<?) reduce estBound to 11.
90532	90679	**
90533	90680	** bSort:
90534	90681	** Boolean. True if there is an ORDER BY clause that will require an
90535	90682	** external sort (i.e. scanning the index being evaluated will not
90536	90683	** correctly order records).
		@@ -90548,17 +90695,18 @@
90548	90695	** SELECT a, b, c FROM tbl WHERE a = 1;
90549	90696	*/
90550	90697	int nEq;
90551	90698	int bInEst = 0;
90552	90699	int nInMul = 1;
90553		- int nBound = 100;
	90700	+ int estBound = 100;
	90701	+ int nBound = 0; /* Number of range constraints seen */
90554	90702	int bSort = 0;
90555	90703	int bLookup = 0;
	90704	+ WhereTerm pTerm; / A single term of the WHERE clause */
90556	90705
90557	90706	/* Determine the values of nEq and nInMul */
90558	90707	for(nEq=0; nEq<pProbe->nColumn; nEq++){
90559		- WhereTerm pTerm; / A single term of the WHERE clause */
90560	90708	int j = pProbe->aiColumn[nEq];
90561	90709	pTerm = findTerm(pWC, iCur, j, notReady, eqTermMask, pIdx);
90562	90710	if( pTerm==0 ) break;
90563	90711	wsFlags \|= (WHERE_COLUMN_EQ\|WHERE_ROWID_EQ);
90564	90712	if( pTerm->eOperator & WO_IN ){
		@@ -90574,22 +90722,24 @@
90574	90722	wsFlags \|= WHERE_COLUMN_NULL;
90575	90723	}
90576	90724	used \|= pTerm->prereqRight;
90577	90725	}
90578	90726
90579		- /* Determine the value of nBound. */
	90727	+ /* Determine the value of estBound. */
90580	90728	if( nEq<pProbe->nColumn ){
90581	90729	int j = pProbe->aiColumn[nEq];
90582	90730	if( findTerm(pWC, iCur, j, notReady, WO_LT\|WO_LE\|WO_GT\|WO_GE, pIdx) ){
90583	90731	WhereTerm *pTop = findTerm(pWC, iCur, j, notReady, WO_LT\|WO_LE, pIdx);
90584	90732	WhereTerm *pBtm = findTerm(pWC, iCur, j, notReady, WO_GT\|WO_GE, pIdx);
90585		- whereRangeScanEst(pParse, pProbe, nEq, pBtm, pTop, &nBound);
	90733	+ whereRangeScanEst(pParse, pProbe, nEq, pBtm, pTop, &estBound);
90586	90734	if( pTop ){
	90735	+ nBound = 1;
90587	90736	wsFlags \|= WHERE_TOP_LIMIT;
90588	90737	used \|= pTop->prereqRight;
90589	90738	}
90590	90739	if( pBtm ){
	90740	+ nBound++;
90591	90741	wsFlags \|= WHERE_BTM_LIMIT;
90592	90742	used \|= pBtm->prereqRight;
90593	90743	}
90594	90744	wsFlags \|= (WHERE_COLUMN_RANGE\|WHERE_ROWID_RANGE);
90595	90745	}
		@@ -90635,12 +90785,11 @@
90635	90785	}else{
90636	90786	bLookup = 1;
90637	90787	}
90638	90788	}
90639	90789
90640		- /**** Begin adding up the cost of using this index (Needs improvements)
90641		- **
	90790	+ /*
90642	90791	** Estimate the number of rows of output. For an IN operator,
90643	90792	** do not let the estimate exceed half the rows in the table.
90644	90793	*/
90645	90794	nRow = (double)(aiRowEst[nEq] * nInMul);
90646	90795	if( bInEst && nRow*2>aiRowEst[0] ){
		@@ -90655,12 +90804,12 @@
90655	90804	cost = nRow + nInMul*estLog(aiRowEst[0]);
90656	90805
90657	90806	/* Adjust the number of rows and the cost downward to reflect rows
90658	90807	** that are excluded by range constraints.
90659	90808	*/
90660		- nRow = (nRow * (double)nBound) / (double)100;
90661		- cost = (cost * (double)nBound) / (double)100;
	90809	+ nRow = (nRow * (double)estBound) / (double)100;
	90810	+ cost = (cost * (double)estBound) / (double)100;
90662	90811
90663	90812	/* Add in the estimated cost of sorting the result
90664	90813	*/
90665	90814	if( bSort ){
90666	90815	cost += cost*estLog(cost);
		@@ -90672,22 +90821,80 @@
90672	90821	*/
90673	90822	if( pIdx && bLookup==0 ){
90674	90823	cost /= (double)2;
90675	90824	}
90676	90825	/** Cost of using this index has now been computed **/
	90826	+
	90827	+ /* If there are additional constraints on this table that cannot
	90828	+ ** be used with the current index, but which might lower the number
	90829	+ ** of output rows, adjust the nRow value accordingly. This only
	90830	+ ** matters if the current index is the least costly, so do not bother
	90831	+ ** with this step if we already know this index will not be chosen.
	90832	+ ** Also, never reduce the output row count below 2 using this step.
	90833	+ **
	90834	+ ** Do not reduce the output row count if pSrc is the only table that
	90835	+ ** is notReady; if notReady is a power of two. This will be the case
	90836	+ ** when the main sqlite3WhereBegin() loop is scanning for a table with
	90837	+ ** and "optimal" index, and on such a scan the output row count
	90838	+ ** reduction is not valid because it does not update the "pCost->used"
	90839	+ ** bitmap. The notReady bitmap will also be a power of two when we
	90840	+ ** are scanning for the last table in a 64-way join. We are willing
	90841	+ ** to bypass this optimization in that corner case.
	90842	+ */
	90843	+ if( nRow>2 && cost<=pCost->rCost && (notReady & (notReady-1))!=0 ){
	90844	+ int k; /* Loop counter */
	90845	+ int nSkipEq = nEq; /* Number of == constraints to skip */
	90846	+ int nSkipRange = nBound; /* Number of < constraints to skip */
	90847	+ Bitmask thisTab; /* Bitmap for pSrc */
	90848	+
	90849	+ thisTab = getMask(pWC->pMaskSet, iCur);
	90850	+ for(pTerm=pWC->a, k=pWC->nTerm; nRow>2 && k; k--, pTerm++){
	90851	+ if( pTerm->wtFlags & TERM_VIRTUAL ) continue;
	90852	+ if( (pTerm->prereqAll & notReady)!=thisTab ) continue;
	90853	+ if( pTerm->eOperator & (WO_EQ\|WO_IN\|WO_ISNULL) ){
	90854	+ if( nSkipEq ){
	90855	+ /* Ignore the first nEq equality matches since the index
	90856	+ ** has already accounted for these */
	90857	+ nSkipEq--;
	90858	+ }else{
	90859	+ /* Assume each additional equality match reduces the result
	90860	+ ** set size by a factor of 10 */
	90861	+ nRow /= 10;
	90862	+ }
	90863	+ }else if( pTerm->eOperator & (WO_LT\|WO_LE\|WO_GT\|WO_GE) ){
	90864	+ if( nSkipRange ){
	90865	+ /* Ignore the first nBound range constraints since the index
	90866	+ ** has already accounted for these */
	90867	+ nSkipRange--;
	90868	+ }else{
	90869	+ /* Assume each additional range constraint reduces the result
	90870	+ ** set size by a factor of 3 */
	90871	+ nRow /= 3;
	90872	+ }
	90873	+ }else{
	90874	+ /* Any other expression lowers the output row count by half */
	90875	+ nRow /= 2;
	90876	+ }
	90877	+ }
	90878	+ if( nRow<2 ) nRow = 2;
	90879	+ }
	90880	+
90677	90881
90678	90882	WHERETRACE((
90679		- "%s(%s): nEq=%d nInMul=%d nBound=%d bSort=%d bLookup=%d wsFlags=0x%x\n"
	90883	+ "%s(%s): nEq=%d nInMul=%d estBound=%d bSort=%d bLookup=%d wsFlags=0x%x\n"
90680	90884	" notReady=0x%llx nRow=%.2f cost=%.2f used=0x%llx\n",
90681	90885	pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk"),
90682		- nEq, nInMul, nBound, bSort, bLookup, wsFlags, notReady, nRow, cost, used
	90886	+ nEq, nInMul, estBound, bSort, bLookup, wsFlags,
	90887	+ notReady, nRow, cost, used
90683	90888	));
90684	90889
90685	90890	/* If this index is the best we have seen so far, then record this
90686	90891	** index and its cost in the pCost structure.
90687	90892	*/
90688		- if( (!pIdx \|\| wsFlags) && cost<pCost->rCost ){
	90893	+ if( (!pIdx \|\| wsFlags)
	90894	+ && (cost<pCost->rCost \|\| (cost<=pCost->rCost && nRow<pCost->nRow))
	90895	+ ){
90689	90896	pCost->rCost = cost;
90690	90897	pCost->nRow = nRow;
90691	90898	pCost->used = used;
90692	90899	pCost->plan.wsFlags = (wsFlags&wsFlagMask);
90693	90900	pCost->plan.nEq = nEq;
		@@ -90718,11 +90925,12 @@
90718	90925	\|\| pCost->plan.u.pIdx==0
90719	90926	\|\| pCost->plan.u.pIdx==pSrc->pIndex
90720	90927	);
90721	90928
90722	90929	WHERETRACE(("best index is: %s\n",
90723		- (pCost->plan.u.pIdx ? pCost->plan.u.pIdx->zName : "ipk")
	90930	+ ((pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)==0 ? "none" :
	90931	+ pCost->plan.u.pIdx ? pCost->plan.u.pIdx->zName : "ipk")
90724	90932	));
90725	90933
90726	90934	bestOrClauseIndex(pParse, pWC, pSrc, notReady, pOrderBy, pCost);
90727	90935	bestAutomaticIndex(pParse, pWC, pSrc, notReady, pCost);
90728	90936	pCost->plan.wsFlags \|= eqTermMask;
		@@ -91654,11 +91862,11 @@
91654	91862
91655	91863	/*
91656	91864	** Free a WhereInfo structure
91657	91865	*/
91658	91866	static void whereInfoFree(sqlite3 db, WhereInfo pWInfo){
91659		- if( pWInfo ){
	91867	+ if( ALWAYS(pWInfo) ){
91660	91868	int i;
91661	91869	for(i=0; i<pWInfo->nLevel; i++){
91662	91870	sqlite3_index_info *pInfo = pWInfo->a[i].pIdxInfo;
91663	91871	if( pInfo ){
91664	91872	/* assert( pInfo->needToFreeIdxStr==0 \|\| db->mallocFailed ); */
		@@ -91791,10 +91999,11 @@
91791	91999	sqlite3 db; / Database connection */
91792	92000
91793	92001	/* The number of tables in the FROM clause is limited by the number of
91794	92002	** bits in a Bitmask
91795	92003	*/
	92004	+ testcase( pTabList->nSrc==BMS );
91796	92005	if( pTabList->nSrc>BMS ){
91797	92006	sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
91798	92007	return 0;
91799	92008	}
91800	92009
		@@ -91930,24 +92139,29 @@
91930	92139
91931	92140	memset(&bestPlan, 0, sizeof(bestPlan));
91932	92141	bestPlan.rCost = SQLITE_BIG_DBL;
91933	92142
91934	92143	/* Loop through the remaining entries in the FROM clause to find the
91935		- ** next nested loop. The FROM clause entries may be iterated through
	92144	+ ** next nested loop. The loop tests all FROM clause entries
91936	92145	** either once or twice.
91937	92146	**
91938		- ** The first iteration, which is always performed, searches for the
91939		- ** FROM clause entry that permits the lowest-cost, "optimal" scan. In
	92147	+ ** The first test is always performed if there are two or more entries
	92148	+ ** remaining and never performed if there is only one FROM clause entry
	92149	+ ** to choose from. The first test looks for an "optimal" scan. In
91940	92150	** this context an optimal scan is one that uses the same strategy
91941	92151	** for the given FROM clause entry as would be selected if the entry
91942	92152	** were used as the innermost nested loop. In other words, a table
91943	92153	** is chosen such that the cost of running that table cannot be reduced
91944		- ** by waiting for other tables to run first.
	92154	+ ** by waiting for other tables to run first. This "optimal" test works
	92155	+ ** by first assuming that the FROM clause is on the inner loop and finding
	92156	+ ** its query plan, then checking to see if that query plan uses any
	92157	+ ** other FROM clause terms that are notReady. If no notReady terms are
	92158	+ ** used then the "optimal" query plan works.
91945	92159	**
91946		- ** The second iteration is only performed if no optimal scan strategies
91947		- ** were found by the first. This iteration is used to search for the
91948		- ** lowest cost scan overall.
	92160	+ ** The second loop iteration is only performed if no optimal scan
	92161	+ ** strategies were found by the first loop. This 2nd iteration is used to
	92162	+ ** search for the lowest cost scan overall.
91949	92163	**
91950	92164	** Previous versions of SQLite performed only the second iteration -
91951	92165	** the next outermost loop was always that with the lowest overall
91952	92166	** cost. However, this meant that SQLite could select the wrong plan
91953	92167	** for scripts such as the following:
		@@ -91961,13 +92175,12 @@
91961	92175	** However, since the cost of a linear scan through table t2 is the same
91962	92176	** as the cost of a linear scan through table t1, a simple greedy
91963	92177	** algorithm may choose to use t2 for the outer loop, which is a much
91964	92178	** costlier approach.
91965	92179	*/
91966		- for(isOptimal=1; isOptimal>=0 && bestJ<0; isOptimal--){
91967		- Bitmask mask = (isOptimal ? 0 : notReady);
91968		- assert( (nTabList-iFrom)>1 \|\| isOptimal );
	92180	+ for(isOptimal=(iFrom<nTabList-1); isOptimal>=0; isOptimal--){
	92181	+ Bitmask mask; /* Mask of tables not yet ready */
91969	92182	for(j=iFrom, pTabItem=&pTabList->a[j]; j<nTabList; j++, pTabItem++){
91970	92183	int doNotReorder; /* True if this table should not be reordered */
91971	92184	WhereCost sCost; /* Cost information from best[Virtual]Index() */
91972	92185	ExprList pOrderBy; / ORDER BY clause for index to optimize */
91973	92186
		@@ -91976,10 +92189,11 @@
91976	92189	m = getMask(pMaskSet, pTabItem->iCursor);
91977	92190	if( (m & notReady)==0 ){
91978	92191	if( j==iFrom ) iFrom++;
91979	92192	continue;
91980	92193	}
	92194	+ mask = (isOptimal ? m : notReady);
91981	92195	pOrderBy = ((i==0 && ppOrderBy )?*ppOrderBy:0);
91982	92196
91983	92197	assert( pTabItem->pTab );
91984	92198	#ifndef SQLITE_OMIT_VIRTUALTABLE
91985	92199	if( IsVirtual(pTabItem->pTab) ){
		@@ -91991,12 +92205,15 @@
91991	92205	bestBtreeIndex(pParse, pWC, pTabItem, mask, pOrderBy, &sCost);
91992	92206	}
91993	92207	assert( isOptimal \|\| (sCost.used&notReady)==0 );
91994	92208
91995	92209	if( (sCost.used&notReady)==0
91996		- && (j==iFrom \|\| sCost.rCost<bestPlan.rCost)
	92210	+ && (bestJ<0 \|\| sCost.rCost<bestPlan.rCost
	92211	+ \|\| (sCost.rCost<=bestPlan.rCost && sCost.nRow<bestPlan.nRow))
91997	92212	){
	92213	+ WHERETRACE(("... best so far with cost=%g and nRow=%g\n",
	92214	+ sCost.rCost, sCost.nRow));
91998	92215	bestPlan = sCost;
91999	92216	bestJ = j;
92000	92217	}
92001	92218	if( doNotReorder ) break;
92002	92219	}
		@@ -92118,10 +92335,12 @@
92118	92335	#endif
92119	92336	if( (pLevel->plan.wsFlags & WHERE_IDX_ONLY)==0
92120	92337	&& (wctrlFlags & WHERE_OMIT_OPEN)==0 ){
92121	92338	int op = pWInfo->okOnePass ? OP_OpenWrite : OP_OpenRead;
92122	92339	sqlite3OpenTable(pParse, pTabItem->iCursor, iDb, pTab, op);
	92340	+ testcase( pTab->nCol==BMS-1 );
	92341	+ testcase( pTab->nCol==BMS );
92123	92342	if( !pWInfo->okOnePass && pTab->nCol<BMS ){
92124	92343	Bitmask b = pTabItem->colUsed;
92125	92344	int n = 0;
92126	92345	for(; b; b=b>>1, n++){}
92127	92346	sqlite3VdbeChangeP4(v, sqlite3VdbeCurrentAddr(v)-1,
		@@ -92297,11 +92516,11 @@
92297	92516	){
92298	92517	int ws = pLevel->plan.wsFlags;
92299	92518	if( !pWInfo->okOnePass && (ws & WHERE_IDX_ONLY)==0 ){
92300	92519	sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor);
92301	92520	}
92302		- if( (ws & (WHERE_INDEXED\|WHERE_TEMP_INDEX)) == WHERE_INDEXED ){
	92521	+ if( (ws & WHERE_INDEXED)!=0 && (ws & WHERE_TEMP_INDEX)==0 ){
92303	92522	sqlite3VdbeAddOp1(v, OP_Close, pLevel->iIdxCur);
92304	92523	}
92305	92524	}
92306	92525
92307	92526	/* If this scan uses an index, make code substitutions to read data
		@@ -92345,14 +92564,12 @@
92345	92564	}
92346	92565	}
92347	92566
92348	92567	/* Final cleanup
92349	92568	*/
92350		- if( pWInfo ){
92351		- pParse->nQueryLoop = pWInfo->savedNQueryLoop;
92352		- whereInfoFree(db, pWInfo);
92353		- }
	92569	+ pParse->nQueryLoop = pWInfo->savedNQueryLoop;
	92570	+ whereInfoFree(db, pWInfo);
92354	92571	return;
92355	92572	}
92356	92573
92357	92574	/************ End of where.c *********************************************/
92358	92575	/************ Begin file parse.c *****************************************/
92359	92576

	--- src/sqlite3.c
	+++ src/sqlite3.c
	@@ -628,11 +628,11 @@
628	** [sqlite3_libversion_number()], [sqlite3_sourceid()],
629	** [sqlite_version()] and [sqlite_source_id()].
630	*/
631	#define SQLITE_VERSION "3.6.23"
632	#define SQLITE_VERSION_NUMBER 3006023
633	#define SQLITE_SOURCE_ID "2010-04-07 20:32:19 e388fe8be878c80ef0bfd1699a7268cdb22cb3c6"
634
635	/*
636	** CAPI3REF: Run-Time Library Version Numbers
637	** KEYWORDS: sqlite3_version, sqlite3_sourceid
638	**
	@@ -4404,12 +4404,10 @@
4404	** ^For the purposes of this API, a transaction is said to have been
4405	** rolled back if an explicit "ROLLBACK" statement is executed, or
4406	** an error or constraint causes an implicit rollback to occur.
4407	** ^The rollback callback is not invoked if a transaction is
4408	** automatically rolled back because the database connection is closed.
4409	** ^The rollback callback is not invoked if a transaction is
4410	** rolled back because a commit callback returned non-zero.
4411	**
4412	** See also the [sqlite3_update_hook()] interface.
4413	*/
4414	SQLITE_API void sqlite3_commit_hook(sqlite3, int()(void), void*);
4415	SQLITE_API void sqlite3_rollback_hook(sqlite3, void()(void ), void*);
	@@ -32356,10 +32354,91 @@
32356	** file simultaneously, or one process from reading the database while
32357	** another is writing.
32358	*/
32359	#ifndef SQLITE_OMIT_DISKIO
32360

















































































32361	/*
32362	** Macros for troubleshooting. Normally turned off
32363	*/
32364	#if 0
32365	int sqlite3PagerTrace=1; /* True to enable tracing */
	@@ -32532,11 +32611,12 @@
32532	** It is used when committing or otherwise ending a transaction. If
32533	** the dbModified flag is clear then less work has to be done.
32534	**
32535	** journalStarted
32536	**
32537	** This flag is set whenever the the main journal is synced.

32538	**
32539	** The point of this flag is that it must be set after the
32540	** first journal header in a journal file has been synced to disk.
32541	** After this has happened, new pages appended to the database
32542	** do not need the PGHDR_NEED_SYNC flag set, as they do not need
	@@ -32558,11 +32638,14 @@
32558	** master journal name is only written to the journal file the first
32559	** time CommitPhaseOne() is called.
32560	**
32561	** doNotSync
32562	**
32563	** This variable is set and cleared by sqlite3PagerWrite().



32564	**
32565	** needSync
32566	**
32567	** TODO: It might be easier to set this variable in writeJournalHdr()
32568	** and writeMasterJournal() only. Change its meaning to "unsynced data
	@@ -32618,10 +32701,11 @@
32618	sqlite3_file fd; / File descriptor for database */
32619	sqlite3_file jfd; / File descriptor for main journal */
32620	sqlite3_file sjfd; / File descriptor for sub-journal */
32621	i64 journalOff; /* Current write offset in the journal file */
32622	i64 journalHdr; /* Byte offset to previous journal header */

32623	PagerSavepoint aSavepoint; / Array of active savepoints */
32624	int nSavepoint; /* Number of elements in aSavepoint[] */
32625	char dbFileVers[16]; /* Changes whenever database file changes */
32626	u32 sectorSize; /* Assumed sector size during rollback */
32627
	@@ -32644,11 +32728,10 @@
32644	void (xCodecSizeChng)(void,int,int); /* Notify of page size changes */
32645	void (xCodecFree)(void); /* Destructor for the codec */
32646	void pCodec; / First argument to xCodec... methods */
32647	#endif
32648	char pTmpSpace; / Pager.pageSize bytes of space for tmp use */
32649	i64 journalSizeLimit; /* Size limit for persistent journal files */
32650	PCache pPCache; / Pointer to page cache object */
32651	sqlite3_backup pBackup; / Pointer to list of ongoing backup processes */
32652	};
32653
32654	/*
	@@ -33160,10 +33243,11 @@
33160	** to populate the entire journal header sector.
33161	*/
33162	for(nWrite=0; rc==SQLITE_OK&&nWrite<JOURNAL_HDR_SZ(pPager); nWrite+=nHeader){
33163	IOTRACE(("JHDR %p %lld %d\n", pPager, pPager->journalHdr, nHeader))
33164	rc = sqlite3OsWrite(pPager->jfd, zHeader, nHeader, pPager->journalOff);

33165	pPager->journalOff += nHeader;
33166	}
33167
33168	return rc;
33169	}
	@@ -33318,10 +33402,11 @@
33318	){
33319	return SQLITE_OK;
33320	}
33321	pPager->setMaster = 1;
33322	assert( isOpen(pPager->jfd) );

33323
33324	/* Calculate the length in bytes and the checksum of zMaster */
33325	for(nMaster=0; zMaster[nMaster]; nMaster++){
33326	cksum += zMaster[nMaster];
33327	}
	@@ -33747,22 +33832,22 @@
33747	** allocated by this function. If this is the case and an allocation fails,
33748	** SQLITE_NOMEM is returned.
33749	*/
33750	static int pager_playback_one_page(
33751	Pager pPager, / The pager being played back */
33752	int isMainJrnl, /* 1 -> main journal. 0 -> sub-journal. */
33753	int isUnsync, /* True if reading from unsynced main journal */
33754	i64 pOffset, / Offset of record to playback */
33755	int isSavepnt, /* True for a savepoint rollback */
33756	Bitvec pDone / Bitvec of pages already played back */

33757	){
33758	int rc;
33759	PgHdr pPg; / An existing page in the cache */
33760	Pgno pgno; /* The page number of a page in journal */
33761	u32 cksum; /* Checksum used for sanity checking */
33762	char aData; / Temporary storage for the page */
33763	sqlite3_file jfd; / The file descriptor for the journal file */

33764
33765	assert( (isMainJrnl&~1)==0 ); /* isMainJrnl is 0 or 1 */
33766	assert( (isSavepnt&~1)==0 ); /* isSavepnt is 0 or 1 */
33767	assert( isMainJrnl \|\| pDone ); /* pDone always used on sub-journals */
33768	assert( isSavepnt \|\| pDone==0 ); /* pDone never used on non-savepoint */
	@@ -33842,16 +33927,21 @@
33842	assert( pPg \|\| !MEMDB );
33843	PAGERTRACE(("PLAYBACK %d page %d hash(%08x) %s\n",
33844	PAGERID(pPager), pgno, pager_datahash(pPager->pageSize, (u8*)aData),
33845	(isMainJrnl?"main-journal":"sub-journal")
33846	));





33847	if( (pPager->state>=PAGER_EXCLUSIVE)
33848	&& (pPg==0 \|\| 0==(pPg->flags&PGHDR_NEED_SYNC))
33849	&& isOpen(pPager->fd)
33850	&& !isUnsync
33851	){
33852	i64 ofst = (pgno-1)*(i64)pPager->pageSize;

33853	rc = sqlite3OsWrite(pPager->fd, (u8*)aData, pPager->pageSize, ofst);
33854	if( pgno>pPager->dbFileSize ){
33855	pPager->dbFileSize = pgno;
33856	}
33857	if( pPager->pBackup ){
	@@ -33896,11 +33986,12 @@
33896	pPager->xReiniter(pPg);
33897	if( isMainJrnl && (!isSavepnt \|\| *pOffset<=pPager->journalHdr) ){
33898	/* If the contents of this page were just restored from the main
33899	** journal file, then its content must be as they were when the
33900	** transaction was first opened. In this case we can mark the page
33901	** as clean, since there will be no need to write it out to the.

33902	**
33903	** There is one exception to this rule. If the page is being rolled
33904	** back as part of a savepoint (or statement) rollback from an
33905	** unsynced portion of the main journal file, then it is not safe
33906	** to mark the page as clean. This is because marking the page as
	@@ -34243,12 +34334,10 @@
34243	/* This loop terminates either when a readJournalHdr() or
34244	** pager_playback_one_page() call returns SQLITE_DONE or an IO error
34245	** occurs.
34246	*/
34247	while( 1 ){
34248	int isUnsync = 0;
34249
34250	/* Read the next journal header from the journal file. If there are
34251	** not enough bytes left in the journal file for a complete header, or
34252	** it is corrupted, then a process must of failed while writing it.
34253	** This indicates nothing more needs to be rolled back.
34254	*/
	@@ -34285,11 +34374,10 @@
34285	** should be computed based on the journal file size.
34286	*/
34287	if( nRec==0 && !isHot &&
34288	pPager->journalHdr+JOURNAL_HDR_SZ(pPager)==pPager->journalOff ){
34289	nRec = (int)((szJ - pPager->journalOff) / JOURNAL_PG_SZ(pPager));
34290	isUnsync = 1;
34291	}
34292
34293	/* If this is the first header read from the journal, truncate the
34294	** database file back to its original size.
34295	*/
	@@ -34307,11 +34395,11 @@
34307	for(u=0; u<nRec; u++){
34308	if( needPagerReset ){
34309	pager_reset(pPager);
34310	needPagerReset = 0;
34311	}
34312	rc = pager_playback_one_page(pPager,1,isUnsync,&pPager->journalOff,0,0);
34313	if( rc!=SQLITE_OK ){
34314	if( rc==SQLITE_DONE ){
34315	rc = SQLITE_OK;
34316	pPager->journalOff = szJ;
34317	break;
	@@ -34460,11 +34548,11 @@
34460	*/
34461	if( pSavepoint ){
34462	iHdrOff = pSavepoint->iHdrOffset ? pSavepoint->iHdrOffset : szJ;
34463	pPager->journalOff = pSavepoint->iOffset;
34464	while( rc==SQLITE_OK && pPager->journalOff<iHdrOff ){
34465	rc = pager_playback_one_page(pPager, 1, 0, &pPager->journalOff, 1, pDone);
34466	}
34467	assert( rc!=SQLITE_DONE );
34468	}else{
34469	pPager->journalOff = 0;
34470	}
	@@ -34490,11 +34578,11 @@
34490	&& pPager->journalHdr+JOURNAL_HDR_SZ(pPager)==pPager->journalOff
34491	){
34492	nJRec = (u32)((szJ - pPager->journalOff)/JOURNAL_PG_SZ(pPager));
34493	}
34494	for(ii=0; rc==SQLITE_OK && ii<nJRec && pPager->journalOff<szJ; ii++){
34495	rc = pager_playback_one_page(pPager, 1, 0, &pPager->journalOff, 1, pDone);
34496	}
34497	assert( rc!=SQLITE_DONE );
34498	}
34499	assert( rc!=SQLITE_OK \|\| pPager->journalOff==szJ );
34500
	@@ -34505,11 +34593,11 @@
34505	if( pSavepoint ){
34506	u32 ii; /* Loop counter */
34507	i64 offset = pSavepoint->iSubRec*(4+pPager->pageSize);
34508	for(ii=pSavepoint->iSubRec; rc==SQLITE_OK && ii<pPager->nSubRec; ii++){
34509	assert( offset==ii*(4+pPager->pageSize) );
34510	rc = pager_playback_one_page(pPager, 0, 0, &offset, 1, pDone);
34511	}
34512	assert( rc!=SQLITE_DONE );
34513	}
34514
34515	sqlite3BitvecDestroy(pDone);
	@@ -34942,10 +35030,35 @@
34942	assert( pPager->dbSize>=nPage );
34943	assert( pPager->state>=PAGER_RESERVED );
34944	pPager->dbSize = nPage;
34945	assertTruncateConstraint(pPager);
34946	}

























34947
34948	/*
34949	** Shutdown the page cache. Free all memory and close all files.
34950	**
34951	** If a transaction was in progress when this routine is called, that
	@@ -34972,11 +35085,13 @@
34972	** call which may be made from within pagerUnlockAndRollback(). If it
34973	** is not -1, then the unsynced portion of an open journal file may
34974	** be played back into the database. If a power failure occurs while
34975	** this is happening, the database may become corrupt.
34976	*/
34977	pPager->journalHdr = -1;


34978	pagerUnlockAndRollback(pPager);
34979	}
34980	sqlite3EndBenignMalloc();
34981	enable_simulated_io_errors();
34982	PAGERTRACE(("CLOSE %d\n", PAGERID(pPager)));
	@@ -35062,11 +35177,11 @@
35062	/* This block deals with an obscure problem. If the last connection
35063	** that wrote to this database was operating in persistent-journal
35064	** mode, then the journal file may at this point actually be larger
35065	** than Pager.journalOff bytes. If the next thing in the journal
35066	** file happens to be a journal-header (written as part of the
35067	** previous connections transaction), and a crash or power-failure
35068	** occurs after nRec is updated but before this connection writes
35069	** anything else to the journal file (or commits/rolls back its
35070	** transaction), then SQLite may become confused when doing the
35071	** hot-journal rollback following recovery. It may roll back all
35072	** of this connections data, then proceed to rolling back the old,
	@@ -35134,10 +35249,11 @@
35134	/* The journal file was just successfully synced. Set Pager.needSync
35135	** to zero and clear the PGHDR_NEED_SYNC flag on all pagess.
35136	*/
35137	pPager->needSync = 0;
35138	pPager->journalStarted = 1;

35139	sqlite3PcacheClearSyncFlags(pPager->pPCache);
35140	}
35141
35142	return SQLITE_OK;
35143	}
	@@ -35996,23 +36112,32 @@
35996	}
35997	if( rc!=SQLITE_OK ){
35998	goto failed;
35999	}
36000
36001	/* TODO: Why are these cleared here? Is it necessary? */

36002	pPager->journalStarted = 0;
36003	pPager->journalOff = 0;
36004	pPager->setMaster = 0;
36005	pPager->journalHdr = 0;


36006
36007	/* Playback and delete the journal. Drop the database write
36008	** lock and reacquire the read lock. Purge the cache before
36009	** playing back the hot-journal so that we don't end up with
36010	** an inconsistent cache.



36011	*/
36012	if( isOpen(pPager->jfd) ){
36013	rc = pager_playback(pPager, 1);



36014	if( rc!=SQLITE_OK ){
36015	rc = pager_error(pPager, rc);
36016	goto failed;
36017	}
36018	}
	@@ -36114,11 +36239,11 @@
36114	** of the page. This occurs in two seperate scenarios:
36115	**
36116	** a) When reading a free-list leaf page from the database, and
36117	**
36118	** b) When a savepoint is being rolled back and we need to load
36119	** a new page into the cache to populate with the data read
36120	** from the savepoint journal.
36121	**
36122	** If noContent is true, then the data returned is zeroed instead of
36123	** being read from the database. Additionally, the bits corresponding
36124	** to pgno in Pager.pInJournal (bitvec of pages already written to the
	@@ -36546,10 +36671,12 @@
36546
36547	/* We should never write to the journal file the page that
36548	** contains the database locks. The following assert verifies
36549	** that we do not. */
36550	assert( pPg->pgno!=PAGER_MJ_PGNO(pPager) );


36551	CODEC2(pPager, pData, pPg->pgno, 7, return SQLITE_NOMEM, pData2);
36552	cksum = pager_cksum(pPager, (u8*)pData2);
36553	rc = write32bits(pPager->jfd, pPager->journalOff, pPg->pgno);
36554	if( rc==SQLITE_OK ){
36555	rc = sqlite3OsWrite(pPager->jfd, pData2, pPager->pageSize,
	@@ -60151,15 +60278,14 @@
60151	** Syncing an in-memory journal is a no-op. And, in fact, this routine
60152	** is never called in a working implementation. This implementation
60153	** exists purely as a contingency, in case some malfunction in some other
60154	** part of SQLite causes Sync to be called by mistake.
60155	*/
60156	static int memjrnlSync(sqlite3_file NotUsed, int NotUsed2){ /NO_TEST*/
60157	UNUSED_PARAMETER2(NotUsed, NotUsed2); /NO_TEST/
60158	assert( 0 ); /NO_TEST/
60159	return SQLITE_OK; /NO_TEST/
60160	} /NO_TEST/
60161
60162	/*
60163	** Query the size of the file in bytes.
60164	*/
60165	static int memjrnlFileSize(sqlite3_file pJfd, sqlite_int64 pSize){
	@@ -60751,11 +60877,11 @@
60751	}
60752	}
60753
60754	/*
60755	** Allocate and return a pointer to an expression to load the column iCol
60756	** from datasource iSrc datasource in SrcList pSrc.
60757	*/
60758	SQLITE_PRIVATE Expr sqlite3CreateColumnExpr(sqlite3 db, SrcList *pSrc, int iSrc, int iCol){
60759	Expr *p = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
60760	if( p ){
60761	struct SrcList_item *pItem = &pSrc->a[iSrc];
	@@ -60763,10 +60889,12 @@
60763	p->iTable = pItem->iCursor;
60764	if( p->pTab->iPKey==iCol ){
60765	p->iColumn = -1;
60766	}else{
60767	p->iColumn = (ynVar)iCol;


60768	pItem->colUsed \|= ((Bitmask)1)<<(iCol>=BMS ? BMS-1 : iCol);
60769	}
60770	ExprSetProperty(p, EP_Resolved);
60771	}
60772	return p;
	@@ -79646,32 +79774,32 @@
79646	/* Call the parser to process a CREATE TABLE, INDEX or VIEW.
79647	** But because db->init.busy is set to 1, no VDBE code is generated
79648	** or executed. All the parser does is build the internal data
79649	** structures that describe the table, index, or view.
79650	*/
79651	char *zErr;
79652	int rc;


79653	assert( db->init.busy );
79654	db->init.iDb = iDb;
79655	db->init.newTnum = atoi(argv[1]);
79656	db->init.orphanTrigger = 0;
79657	rc = sqlite3_exec(db, argv[2], 0, 0, &zErr);
79658	db->init.iDb = 0;
79659	assert( rc!=SQLITE_OK \|\| zErr==0 );
79660	if( SQLITE_OK!=rc ){
79661	if( db->init.orphanTrigger ){
79662	assert( iDb==1 );
79663	}else{
79664	pData->rc = rc;
79665	if( rc==SQLITE_NOMEM ){
79666	db->mallocFailed = 1;
79667	}else if( rc!=SQLITE_INTERRUPT && rc!=SQLITE_LOCKED ){
79668	corruptSchema(pData, argv[0], zErr);
79669	}
79670	}
79671	sqlite3DbFree(db, zErr);
79672	}

79673	}else if( argv[0]==0 ){
79674	corruptSchema(pData, 0, 0);
79675	}else{
79676	/* If the SQL column is blank it means this is an index that
79677	** was created to be the PRIMARY KEY or to fulfill a UNIQUE
	@@ -82959,12 +83087,12 @@
82959	** (13) The subquery and outer query do not both use LIMIT
82960	**
82961	** (14) The subquery does not use OFFSET
82962	**
82963	** (15) The outer query is not part of a compound select or the
82964	** subquery does not have both an ORDER BY and a LIMIT clause.
82965	** (See ticket #2339)
82966	**
82967	** (16) The outer query is not an aggregate or the subquery does
82968	** not contain ORDER BY. (Ticket #2942) This used to not matter
82969	** until we introduced the group_concat() function.
82970	**
	@@ -83043,11 +83171,11 @@
83043	** because they could be computed at compile-time. But when LIMIT and OFFSET
83044	** became arbitrary expressions, we were forced to add restrictions (13)
83045	** and (14). */
83046	if( pSub->pLimit && p->pLimit ) return 0; /* Restriction (13) */
83047	if( pSub->pOffset ) return 0; /* Restriction (14) */
83048	if( p->pRightmost && pSub->pLimit && pSub->pOrderBy ){
83049	return 0; /* Restriction (15) */
83050	}
83051	if( pSubSrc->nSrc==0 ) return 0; /* Restriction (7) */
83052	if( ((pSub->selFlags & SF_Distinct)!=0 \|\| pSub->pLimit)
83053	&& (pSrc->nSrc>1 \|\| isAgg) ){ /* Restrictions (4)(5)(8)(9) */
	@@ -89537,10 +89665,15 @@
89537	#ifndef SQLITE_OMIT_OR_OPTIMIZATION
89538	const int iCur = pSrc->iCursor; /* The cursor of the table to be accessed */
89539	const Bitmask maskSrc = getMask(pWC->pMaskSet, iCur); /* Bitmask for pSrc */
89540	WhereTerm * const pWCEnd = &pWC->a[pWC->nTerm]; /* End of pWC->a[] */
89541	WhereTerm pTerm; / A single term of the WHERE clause */





89542
89543	/* Search the WHERE clause terms for a usable WO_OR term. */
89544	for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
89545	if( pTerm->eOperator==WO_OR
89546	&& ((pTerm->prereqAll & ~maskSrc) & notReady)==0
	@@ -89580,12 +89713,13 @@
89580	}
89581
89582	/* If there is an ORDER BY clause, increase the scan cost to account
89583	** for the cost of the sort. */
89584	if( pOrderBy!=0 ){


89585	rTotal += nRow*estLog(nRow);
89586	WHERETRACE(("... sorting increases OR cost to %.9g\n", rTotal));
89587	}
89588
89589	/* If the cost of scanning using this OR term for optimization is
89590	** less than the current cost stored in pCost, replace the contents
89591	** of pCost. */
	@@ -89658,21 +89792,21 @@
89658	/* The NOT INDEXED clause appears in the SQL. */
89659	return;
89660	}
89661
89662	assert( pParse->nQueryLoop >= (double)1 );
89663	nTableRow = pSrc->pIndex ? pSrc->pIndex->aiRowEst[0] : 1000000;

89664	logN = estLog(nTableRow);
89665	costTempIdx = 2logN(nTableRow/pParse->nQueryLoop + 1);
89666	if( costTempIdx>=pCost->rCost ){
89667	/* The cost of creating the transient table would be greater than
89668	** doing the full table scan */
89669	return;
89670	}
89671
89672	/* Search for any equality comparison term */
89673	pTable = pSrc->pTab;
89674	pWCEnd = &pWC->a[pWC->nTerm];
89675	for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
89676	if( termCanDriveIndex(pTerm, pSrc, notReady) ){
89677	WHERETRACE(("auto-index reduces cost from %.2f to %.2f\n",
89678	pCost->rCost, costTempIdx));
	@@ -89736,12 +89870,17 @@
89736	pWCEnd = &pWC->a[pWC->nTerm];
89737	idxCols = 0;
89738	for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
89739	if( termCanDriveIndex(pTerm, pSrc, notReady) ){
89740	int iCol = pTerm->u.leftColumn;
89741	if( iCol<BMS && iCol>=0 ) idxCols \|= 1<<iCol;
89742	nColumn++;





89743	}
89744	}
89745	assert( nColumn>0 );
89746	pLevel->plan.nEq = nColumn;
89747
	@@ -89751,14 +89890,16 @@
89751	** original table never needs to be accessed. Automatic indices must
89752	** be a covering index because the index will not be updated if the
89753	** original table changes and the index and table cannot both be used
89754	** if they go out of sync.
89755	*/
89756	extraCols = pSrc->colUsed & ~idxCols;
89757	mxBitCol = (pTable->nCol >= BMS-1) ? BMS-1 : pTable->nCol;


89758	for(i=0; i<mxBitCol; i++){
89759	if( extraCols & (1<<i) ) nColumn++;
89760	}
89761	if( pSrc->colUsed & (((Bitmask)1)<<(BMS-1)) ){
89762	nColumn += pTable->nCol - BMS + 1;
89763	}
89764	pLevel->plan.wsFlags \|= WHERE_COLUMN_EQ \| WHERE_IDX_ONLY \| WO_EQ;
	@@ -89776,25 +89917,31 @@
89776	pIdx->aSortOrder = (u8*)&pIdx->aiColumn[nColumn];
89777	pIdx->zName = "auto-index";
89778	pIdx->nColumn = nColumn;
89779	pIdx->pTable = pTable;
89780	n = 0;

89781	for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
89782	if( termCanDriveIndex(pTerm, pSrc, notReady) ){
89783	Expr *pX = pTerm->pExpr;
89784	pIdx->aiColumn[n] = pTerm->u.leftColumn;
89785	pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
89786	pIdx->azColl[n] = pColl->zName;
89787	n++;





89788	}
89789	}
89790	assert( n==pLevel->plan.nEq );
89791
89792	/* Add additional columns needed to make the automatic index into
89793	** a covering index */
89794	for(i=0; i<mxBitCol; i++){
89795	if( extraCols & (1<<i) ){
89796	pIdx->aiColumn[n] = i;
89797	pIdx->azColl[n] = "BINARY";
89798	n++;
89799	}
89800	}
	@@ -90519,18 +90666,18 @@
90519	**
90520	** bInEst:
90521	** Set to true if there was at least one "x IN (SELECT ...)" term used
90522	** in determining the value of nInMul.
90523	**
90524	** nBound:
90525	** An estimate on the amount of the table that must be searched. A
90526	** value of 100 means the entire table is searched. Range constraints
90527	** might reduce this to a value less than 100 to indicate that only
90528	** a fraction of the table needs searching. In the absence of
90529	** sqlite_stat2 ANALYZE data, a single inequality reduces the search
90530	** space to 1/3rd its original size. So an x>? constraint reduces
90531	** nBound to 33. Two constraints (x>? AND x<?) reduce nBound to 11.
90532	**
90533	** bSort:
90534	** Boolean. True if there is an ORDER BY clause that will require an
90535	** external sort (i.e. scanning the index being evaluated will not
90536	** correctly order records).
	@@ -90548,17 +90695,18 @@
90548	** SELECT a, b, c FROM tbl WHERE a = 1;
90549	*/
90550	int nEq;
90551	int bInEst = 0;
90552	int nInMul = 1;
90553	int nBound = 100;

90554	int bSort = 0;
90555	int bLookup = 0;

90556
90557	/* Determine the values of nEq and nInMul */
90558	for(nEq=0; nEq<pProbe->nColumn; nEq++){
90559	WhereTerm pTerm; / A single term of the WHERE clause */
90560	int j = pProbe->aiColumn[nEq];
90561	pTerm = findTerm(pWC, iCur, j, notReady, eqTermMask, pIdx);
90562	if( pTerm==0 ) break;
90563	wsFlags \|= (WHERE_COLUMN_EQ\|WHERE_ROWID_EQ);
90564	if( pTerm->eOperator & WO_IN ){
	@@ -90574,22 +90722,24 @@
90574	wsFlags \|= WHERE_COLUMN_NULL;
90575	}
90576	used \|= pTerm->prereqRight;
90577	}
90578
90579	/* Determine the value of nBound. */
90580	if( nEq<pProbe->nColumn ){
90581	int j = pProbe->aiColumn[nEq];
90582	if( findTerm(pWC, iCur, j, notReady, WO_LT\|WO_LE\|WO_GT\|WO_GE, pIdx) ){
90583	WhereTerm *pTop = findTerm(pWC, iCur, j, notReady, WO_LT\|WO_LE, pIdx);
90584	WhereTerm *pBtm = findTerm(pWC, iCur, j, notReady, WO_GT\|WO_GE, pIdx);
90585	whereRangeScanEst(pParse, pProbe, nEq, pBtm, pTop, &nBound);
90586	if( pTop ){

90587	wsFlags \|= WHERE_TOP_LIMIT;
90588	used \|= pTop->prereqRight;
90589	}
90590	if( pBtm ){

90591	wsFlags \|= WHERE_BTM_LIMIT;
90592	used \|= pBtm->prereqRight;
90593	}
90594	wsFlags \|= (WHERE_COLUMN_RANGE\|WHERE_ROWID_RANGE);
90595	}
	@@ -90635,12 +90785,11 @@
90635	}else{
90636	bLookup = 1;
90637	}
90638	}
90639
90640	/**** Begin adding up the cost of using this index (Needs improvements)
90641	**
90642	** Estimate the number of rows of output. For an IN operator,
90643	** do not let the estimate exceed half the rows in the table.
90644	*/
90645	nRow = (double)(aiRowEst[nEq] * nInMul);
90646	if( bInEst && nRow*2>aiRowEst[0] ){
	@@ -90655,12 +90804,12 @@
90655	cost = nRow + nInMul*estLog(aiRowEst[0]);
90656
90657	/* Adjust the number of rows and the cost downward to reflect rows
90658	** that are excluded by range constraints.
90659	*/
90660	nRow = (nRow * (double)nBound) / (double)100;
90661	cost = (cost * (double)nBound) / (double)100;
90662
90663	/* Add in the estimated cost of sorting the result
90664	*/
90665	if( bSort ){
90666	cost += cost*estLog(cost);
	@@ -90672,22 +90821,80 @@
90672	*/
90673	if( pIdx && bLookup==0 ){
90674	cost /= (double)2;
90675	}
90676	/** Cost of using this index has now been computed **/























































90677
90678	WHERETRACE((
90679	"%s(%s): nEq=%d nInMul=%d nBound=%d bSort=%d bLookup=%d wsFlags=0x%x\n"
90680	" notReady=0x%llx nRow=%.2f cost=%.2f used=0x%llx\n",
90681	pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk"),
90682	nEq, nInMul, nBound, bSort, bLookup, wsFlags, notReady, nRow, cost, used

90683	));
90684
90685	/* If this index is the best we have seen so far, then record this
90686	** index and its cost in the pCost structure.
90687	*/
90688	if( (!pIdx \|\| wsFlags) && cost<pCost->rCost ){


90689	pCost->rCost = cost;
90690	pCost->nRow = nRow;
90691	pCost->used = used;
90692	pCost->plan.wsFlags = (wsFlags&wsFlagMask);
90693	pCost->plan.nEq = nEq;
	@@ -90718,11 +90925,12 @@
90718	\|\| pCost->plan.u.pIdx==0
90719	\|\| pCost->plan.u.pIdx==pSrc->pIndex
90720	);
90721
90722	WHERETRACE(("best index is: %s\n",
90723	(pCost->plan.u.pIdx ? pCost->plan.u.pIdx->zName : "ipk")

90724	));
90725
90726	bestOrClauseIndex(pParse, pWC, pSrc, notReady, pOrderBy, pCost);
90727	bestAutomaticIndex(pParse, pWC, pSrc, notReady, pCost);
90728	pCost->plan.wsFlags \|= eqTermMask;
	@@ -91654,11 +91862,11 @@
91654
91655	/*
91656	** Free a WhereInfo structure
91657	*/
91658	static void whereInfoFree(sqlite3 db, WhereInfo pWInfo){
91659	if( pWInfo ){
91660	int i;
91661	for(i=0; i<pWInfo->nLevel; i++){
91662	sqlite3_index_info *pInfo = pWInfo->a[i].pIdxInfo;
91663	if( pInfo ){
91664	/* assert( pInfo->needToFreeIdxStr==0 \|\| db->mallocFailed ); */
	@@ -91791,10 +91999,11 @@
91791	sqlite3 db; / Database connection */
91792
91793	/* The number of tables in the FROM clause is limited by the number of
91794	** bits in a Bitmask
91795	*/

91796	if( pTabList->nSrc>BMS ){
91797	sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
91798	return 0;
91799	}
91800
	@@ -91930,24 +92139,29 @@
91930
91931	memset(&bestPlan, 0, sizeof(bestPlan));
91932	bestPlan.rCost = SQLITE_BIG_DBL;
91933
91934	/* Loop through the remaining entries in the FROM clause to find the
91935	** next nested loop. The FROM clause entries may be iterated through
91936	** either once or twice.
91937	**
91938	** The first iteration, which is always performed, searches for the
91939	** FROM clause entry that permits the lowest-cost, "optimal" scan. In

91940	** this context an optimal scan is one that uses the same strategy
91941	** for the given FROM clause entry as would be selected if the entry
91942	** were used as the innermost nested loop. In other words, a table
91943	** is chosen such that the cost of running that table cannot be reduced
91944	** by waiting for other tables to run first.




91945	**
91946	** The second iteration is only performed if no optimal scan strategies
91947	** were found by the first. This iteration is used to search for the
91948	** lowest cost scan overall.
91949	**
91950	** Previous versions of SQLite performed only the second iteration -
91951	** the next outermost loop was always that with the lowest overall
91952	** cost. However, this meant that SQLite could select the wrong plan
91953	** for scripts such as the following:
	@@ -91961,13 +92175,12 @@
91961	** However, since the cost of a linear scan through table t2 is the same
91962	** as the cost of a linear scan through table t1, a simple greedy
91963	** algorithm may choose to use t2 for the outer loop, which is a much
91964	** costlier approach.
91965	*/
91966	for(isOptimal=1; isOptimal>=0 && bestJ<0; isOptimal--){
91967	Bitmask mask = (isOptimal ? 0 : notReady);
91968	assert( (nTabList-iFrom)>1 \|\| isOptimal );
91969	for(j=iFrom, pTabItem=&pTabList->a[j]; j<nTabList; j++, pTabItem++){
91970	int doNotReorder; /* True if this table should not be reordered */
91971	WhereCost sCost; /* Cost information from best[Virtual]Index() */
91972	ExprList pOrderBy; / ORDER BY clause for index to optimize */
91973
	@@ -91976,10 +92189,11 @@
91976	m = getMask(pMaskSet, pTabItem->iCursor);
91977	if( (m & notReady)==0 ){
91978	if( j==iFrom ) iFrom++;
91979	continue;
91980	}

91981	pOrderBy = ((i==0 && ppOrderBy )?*ppOrderBy:0);
91982
91983	assert( pTabItem->pTab );
91984	#ifndef SQLITE_OMIT_VIRTUALTABLE
91985	if( IsVirtual(pTabItem->pTab) ){
	@@ -91991,12 +92205,15 @@
91991	bestBtreeIndex(pParse, pWC, pTabItem, mask, pOrderBy, &sCost);
91992	}
91993	assert( isOptimal \|\| (sCost.used&notReady)==0 );
91994
91995	if( (sCost.used&notReady)==0
91996	&& (j==iFrom \|\| sCost.rCost<bestPlan.rCost)

91997	){


91998	bestPlan = sCost;
91999	bestJ = j;
92000	}
92001	if( doNotReorder ) break;
92002	}
	@@ -92118,10 +92335,12 @@
92118	#endif
92119	if( (pLevel->plan.wsFlags & WHERE_IDX_ONLY)==0
92120	&& (wctrlFlags & WHERE_OMIT_OPEN)==0 ){
92121	int op = pWInfo->okOnePass ? OP_OpenWrite : OP_OpenRead;
92122	sqlite3OpenTable(pParse, pTabItem->iCursor, iDb, pTab, op);


92123	if( !pWInfo->okOnePass && pTab->nCol<BMS ){
92124	Bitmask b = pTabItem->colUsed;
92125	int n = 0;
92126	for(; b; b=b>>1, n++){}
92127	sqlite3VdbeChangeP4(v, sqlite3VdbeCurrentAddr(v)-1,
	@@ -92297,11 +92516,11 @@
92297	){
92298	int ws = pLevel->plan.wsFlags;
92299	if( !pWInfo->okOnePass && (ws & WHERE_IDX_ONLY)==0 ){
92300	sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor);
92301	}
92302	if( (ws & (WHERE_INDEXED\|WHERE_TEMP_INDEX)) == WHERE_INDEXED ){
92303	sqlite3VdbeAddOp1(v, OP_Close, pLevel->iIdxCur);
92304	}
92305	}
92306
92307	/* If this scan uses an index, make code substitutions to read data
	@@ -92345,14 +92564,12 @@
92345	}
92346	}
92347
92348	/* Final cleanup
92349	*/
92350	if( pWInfo ){
92351	pParse->nQueryLoop = pWInfo->savedNQueryLoop;
92352	whereInfoFree(db, pWInfo);
92353	}
92354	return;
92355	}
92356
92357	/************ End of where.c *********************************************/
92358	/************ Begin file parse.c *****************************************/
92359

	--- src/sqlite3.c
	+++ src/sqlite3.c
	@@ -628,11 +628,11 @@
628	** [sqlite3_libversion_number()], [sqlite3_sourceid()],
629	** [sqlite_version()] and [sqlite_source_id()].
630	*/
631	#define SQLITE_VERSION "3.6.23"
632	#define SQLITE_VERSION_NUMBER 3006023
633	#define SQLITE_SOURCE_ID "2010-04-15 23:24:29 f96782b389b5b97b488dc5814f7082e0393f64cd"
634
635	/*
636	** CAPI3REF: Run-Time Library Version Numbers
637	** KEYWORDS: sqlite3_version, sqlite3_sourceid
638	**
	@@ -4404,12 +4404,10 @@
4404	** ^For the purposes of this API, a transaction is said to have been
4405	** rolled back if an explicit "ROLLBACK" statement is executed, or
4406	** an error or constraint causes an implicit rollback to occur.
4407	** ^The rollback callback is not invoked if a transaction is
4408	** automatically rolled back because the database connection is closed.


4409	**
4410	** See also the [sqlite3_update_hook()] interface.
4411	*/
4412	SQLITE_API void sqlite3_commit_hook(sqlite3, int()(void), void*);
4413	SQLITE_API void sqlite3_rollback_hook(sqlite3, void()(void ), void*);
	@@ -32356,10 +32354,91 @@
32354	** file simultaneously, or one process from reading the database while
32355	** another is writing.
32356	*/
32357	#ifndef SQLITE_OMIT_DISKIO
32358
32359	/*
32360	****************** NOTES ON THE DESIGN OF THE PAGER **********************
32361	**
32362	** Within this comment block, a page is deemed to have been synced
32363	** automatically as soon as it is written when PRAGMA synchronous=OFF.
32364	** Otherwise, the page is not synced until the xSync method of the VFS
32365	** is called successfully on the file containing the page.
32366	**
32367	** Definition: A page of the database file is said to be "overwriteable" if
32368	** one or more of the following are true about the page:
32369	**
32370	** (a) The original content of the page as it was at the beginning of
32371	** the transaction has been written into the rollback journal and
32372	** synced.
32373	**
32374	** (b) The page was a freelist leaf page at the start of the transaction.
32375	**
32376	** (c) The page number is greater than the largest page that existed in
32377	** the database file at the start of the transaction.
32378	**
32379	** (1) A page of the database file is never overwritten unless one of the
32380	** following are true:
32381	**
32382	** (a) The page and all other pages on the same sector are overwriteable.
32383	**
32384	** (b) The atomic page write optimization is enabled, and the entire
32385	** transaction other than the update of the transaction sequence
32386	** number consists of a single page change.
32387	**
32388	** (2) The content of a page written into the rollback journal exactly matches
32389	** both the content in the database when the rollback journal was written
32390	** and the content in the database at the beginning of the current
32391	** transaction.
32392	**
32393	** (3) Writes to the database file are an integer multiple of the page size
32394	** in length and are aligned to a page boundary.
32395	**
32396	** (4) Reads from the database file are either aligned on a page boundary and
32397	** an integer multiple of the page size in length or are taken from the
32398	** first 100 bytes of the database file.
32399	**
32400	** (5) All writes to the database file are synced prior to the rollback journal
32401	** being deleted, truncated, or zeroed.
32402	**
32403	** (6) If a master journal file is used, then all writes to the database file
32404	** are synced prior to the master journal being deleted.
32405	**
32406	** Definition: Two databases (or the same database at two points it time)
32407	** are said to be "logically equivalent" if they give the same answer to
32408	** all queries. Note in particular the the content of freelist leaf
32409	** pages can be changed arbitarily without effecting the logical equivalence
32410	** of the database.
32411	**
32412	** (7) At any time, if any subset, including the empty set and the total set,
32413	** of the unsynced changes to a rollback journal are removed and the
32414	** journal is rolled back, the resulting database file will be logical
32415	** equivalent to the database file at the beginning of the transaction.
32416	**
32417	** (8) When a transaction is rolled back, the xTruncate method of the VFS
32418	** is called to restore the database file to the same size it was at
32419	** the beginning of the transaction. (In some VFSes, the xTruncate
32420	** method is a no-op, but that does not change the fact the SQLite will
32421	** invoke it.)
32422	**
32423	** (9) Whenever the database file is modified, at least one bit in the range
32424	** of bytes from 24 through 39 inclusive will be changed prior to releasing
32425	** the EXCLUSIVE lock.
32426	**
32427	** (10) The pattern of bits in bytes 24 through 39 shall not repeat in less
32428	** than one billion transactions.
32429	**
32430	** (11) A database file is well-formed at the beginning and at the conclusion
32431	** of every transaction.
32432	**
32433	** (12) An EXCLUSIVE lock is held on the database file when writing to
32434	** the database file.
32435	**
32436	** (13) A SHARED lock is held on the database file while reading any
32437	** content out of the database file.
32438	*/
32439
32440	/*
32441	** Macros for troubleshooting. Normally turned off
32442	*/
32443	#if 0
32444	int sqlite3PagerTrace=1; /* True to enable tracing */
	@@ -32532,11 +32611,12 @@
32611	** It is used when committing or otherwise ending a transaction. If
32612	** the dbModified flag is clear then less work has to be done.
32613	**
32614	** journalStarted
32615	**
32616	** This flag is set whenever the the main journal is opened and
32617	** initialized
32618	**
32619	** The point of this flag is that it must be set after the
32620	** first journal header in a journal file has been synced to disk.
32621	** After this has happened, new pages appended to the database
32622	** do not need the PGHDR_NEED_SYNC flag set, as they do not need
	@@ -32558,11 +32638,14 @@
32638	** master journal name is only written to the journal file the first
32639	** time CommitPhaseOne() is called.
32640	**
32641	** doNotSync
32642	**
32643	** When enabled, cache spills are prohibited and the journal file cannot
32644	** be synced. This variable is set and cleared by sqlite3PagerWrite()
32645	** in order to prevent a journal sync from happening in between the
32646	** journalling of two pages on the same sector.
32647	**
32648	** needSync
32649	**
32650	** TODO: It might be easier to set this variable in writeJournalHdr()
32651	** and writeMasterJournal() only. Change its meaning to "unsynced data
	@@ -32618,10 +32701,11 @@
32701	sqlite3_file fd; / File descriptor for database */
32702	sqlite3_file jfd; / File descriptor for main journal */
32703	sqlite3_file sjfd; / File descriptor for sub-journal */
32704	i64 journalOff; /* Current write offset in the journal file */
32705	i64 journalHdr; /* Byte offset to previous journal header */
32706	i64 journalSizeLimit; /* Size limit for persistent journal files */
32707	PagerSavepoint aSavepoint; / Array of active savepoints */
32708	int nSavepoint; /* Number of elements in aSavepoint[] */
32709	char dbFileVers[16]; /* Changes whenever database file changes */
32710	u32 sectorSize; /* Assumed sector size during rollback */
32711
	@@ -32644,11 +32728,10 @@
32728	void (xCodecSizeChng)(void,int,int); /* Notify of page size changes */
32729	void (xCodecFree)(void); /* Destructor for the codec */
32730	void pCodec; / First argument to xCodec... methods */
32731	#endif
32732	char pTmpSpace; / Pager.pageSize bytes of space for tmp use */

32733	PCache pPCache; / Pointer to page cache object */
32734	sqlite3_backup pBackup; / Pointer to list of ongoing backup processes */
32735	};
32736
32737	/*
	@@ -33160,10 +33243,11 @@
33243	** to populate the entire journal header sector.
33244	*/
33245	for(nWrite=0; rc==SQLITE_OK&&nWrite<JOURNAL_HDR_SZ(pPager); nWrite+=nHeader){
33246	IOTRACE(("JHDR %p %lld %d\n", pPager, pPager->journalHdr, nHeader))
33247	rc = sqlite3OsWrite(pPager->jfd, zHeader, nHeader, pPager->journalOff);
33248	assert( pPager->journalHdr <= pPager->journalOff );
33249	pPager->journalOff += nHeader;
33250	}
33251
33252	return rc;
33253	}
	@@ -33318,10 +33402,11 @@
33402	){
33403	return SQLITE_OK;
33404	}
33405	pPager->setMaster = 1;
33406	assert( isOpen(pPager->jfd) );
33407	assert( pPager->journalHdr <= pPager->journalOff );
33408
33409	/* Calculate the length in bytes and the checksum of zMaster */
33410	for(nMaster=0; zMaster[nMaster]; nMaster++){
33411	cksum += zMaster[nMaster];
33412	}
	@@ -33747,22 +33832,22 @@
33832	** allocated by this function. If this is the case and an allocation fails,
33833	** SQLITE_NOMEM is returned.
33834	*/
33835	static int pager_playback_one_page(
33836	Pager pPager, / The pager being played back */


33837	i64 pOffset, / Offset of record to playback */
33838	Bitvec pDone, / Bitvec of pages already played back */
33839	int isMainJrnl, /* 1 -> main journal. 0 -> sub-journal. */
33840	int isSavepnt /* True for a savepoint rollback */
33841	){
33842	int rc;
33843	PgHdr pPg; / An existing page in the cache */
33844	Pgno pgno; /* The page number of a page in journal */
33845	u32 cksum; /* Checksum used for sanity checking */
33846	char aData; / Temporary storage for the page */
33847	sqlite3_file jfd; / The file descriptor for the journal file */
33848	int isSynced; /* True if journal page is synced */
33849
33850	assert( (isMainJrnl&~1)==0 ); /* isMainJrnl is 0 or 1 */
33851	assert( (isSavepnt&~1)==0 ); /* isSavepnt is 0 or 1 */
33852	assert( isMainJrnl \|\| pDone ); /* pDone always used on sub-journals */
33853	assert( isSavepnt \|\| pDone==0 ); /* pDone never used on non-savepoint */
	@@ -33842,16 +33927,21 @@
33927	assert( pPg \|\| !MEMDB );
33928	PAGERTRACE(("PLAYBACK %d page %d hash(%08x) %s\n",
33929	PAGERID(pPager), pgno, pager_datahash(pPager->pageSize, (u8*)aData),
33930	(isMainJrnl?"main-journal":"sub-journal")
33931	));
33932	if( isMainJrnl ){
33933	isSynced = pPager->noSync \|\| (*pOffset <= pPager->journalHdr);
33934	}else{
33935	isSynced = (pPg==0 \|\| 0==(pPg->flags & PGHDR_NEED_SYNC));
33936	}
33937	if( (pPager->state>=PAGER_EXCLUSIVE)

33938	&& isOpen(pPager->fd)
33939	&& isSynced
33940	){
33941	i64 ofst = (pgno-1)*(i64)pPager->pageSize;
33942	testcase( !isSavepnt && pPg!=0 && (pPg->flags&PGHDR_NEED_SYNC)!=0 );
33943	rc = sqlite3OsWrite(pPager->fd, (u8*)aData, pPager->pageSize, ofst);
33944	if( pgno>pPager->dbFileSize ){
33945	pPager->dbFileSize = pgno;
33946	}
33947	if( pPager->pBackup ){
	@@ -33896,11 +33986,12 @@
33986	pPager->xReiniter(pPg);
33987	if( isMainJrnl && (!isSavepnt \|\| *pOffset<=pPager->journalHdr) ){
33988	/* If the contents of this page were just restored from the main
33989	** journal file, then its content must be as they were when the
33990	** transaction was first opened. In this case we can mark the page
33991	** as clean, since there will be no need to write it out to the
33992	** database.
33993	**
33994	** There is one exception to this rule. If the page is being rolled
33995	** back as part of a savepoint (or statement) rollback from an
33996	** unsynced portion of the main journal file, then it is not safe
33997	** to mark the page as clean. This is because marking the page as
	@@ -34243,12 +34334,10 @@
34334	/* This loop terminates either when a readJournalHdr() or
34335	** pager_playback_one_page() call returns SQLITE_DONE or an IO error
34336	** occurs.
34337	*/
34338	while( 1 ){


34339	/* Read the next journal header from the journal file. If there are
34340	** not enough bytes left in the journal file for a complete header, or
34341	** it is corrupted, then a process must of failed while writing it.
34342	** This indicates nothing more needs to be rolled back.
34343	*/
	@@ -34285,11 +34374,10 @@
34374	** should be computed based on the journal file size.
34375	*/
34376	if( nRec==0 && !isHot &&
34377	pPager->journalHdr+JOURNAL_HDR_SZ(pPager)==pPager->journalOff ){
34378	nRec = (int)((szJ - pPager->journalOff) / JOURNAL_PG_SZ(pPager));

34379	}
34380
34381	/* If this is the first header read from the journal, truncate the
34382	** database file back to its original size.
34383	*/
	@@ -34307,11 +34395,11 @@
34395	for(u=0; u<nRec; u++){
34396	if( needPagerReset ){
34397	pager_reset(pPager);
34398	needPagerReset = 0;
34399	}
34400	rc = pager_playback_one_page(pPager,&pPager->journalOff,0,1,0);
34401	if( rc!=SQLITE_OK ){
34402	if( rc==SQLITE_DONE ){
34403	rc = SQLITE_OK;
34404	pPager->journalOff = szJ;
34405	break;
	@@ -34460,11 +34548,11 @@
34548	*/
34549	if( pSavepoint ){
34550	iHdrOff = pSavepoint->iHdrOffset ? pSavepoint->iHdrOffset : szJ;
34551	pPager->journalOff = pSavepoint->iOffset;
34552	while( rc==SQLITE_OK && pPager->journalOff<iHdrOff ){
34553	rc = pager_playback_one_page(pPager, &pPager->journalOff, pDone, 1, 1);
34554	}
34555	assert( rc!=SQLITE_DONE );
34556	}else{
34557	pPager->journalOff = 0;
34558	}
	@@ -34490,11 +34578,11 @@
34578	&& pPager->journalHdr+JOURNAL_HDR_SZ(pPager)==pPager->journalOff
34579	){
34580	nJRec = (u32)((szJ - pPager->journalOff)/JOURNAL_PG_SZ(pPager));
34581	}
34582	for(ii=0; rc==SQLITE_OK && ii<nJRec && pPager->journalOff<szJ; ii++){
34583	rc = pager_playback_one_page(pPager, &pPager->journalOff, pDone, 1, 1);
34584	}
34585	assert( rc!=SQLITE_DONE );
34586	}
34587	assert( rc!=SQLITE_OK \|\| pPager->journalOff==szJ );
34588
	@@ -34505,11 +34593,11 @@
34593	if( pSavepoint ){
34594	u32 ii; /* Loop counter */
34595	i64 offset = pSavepoint->iSubRec*(4+pPager->pageSize);
34596	for(ii=pSavepoint->iSubRec; rc==SQLITE_OK && ii<pPager->nSubRec; ii++){
34597	assert( offset==ii*(4+pPager->pageSize) );
34598	rc = pager_playback_one_page(pPager, &offset, pDone, 0, 1);
34599	}
34600	assert( rc!=SQLITE_DONE );
34601	}
34602
34603	sqlite3BitvecDestroy(pDone);
	@@ -34942,10 +35030,35 @@
35030	assert( pPager->dbSize>=nPage );
35031	assert( pPager->state>=PAGER_RESERVED );
35032	pPager->dbSize = nPage;
35033	assertTruncateConstraint(pPager);
35034	}
35035
35036	/*
35037	** This function is called before attempting a hot-journal rollback. It
35038	** syncs the journal file to disk, then sets pPager->journalHdr to the
35039	** size of the journal file so that the pager_playback() routine knows
35040	** that the entire journal file has been synced.
35041	**
35042	** Syncing a hot-journal to disk before attempting to roll it back ensures
35043	** that if a power-failure occurs during the rollback, the process that
35044	** attempts rollback following system recovery sees the same journal
35045	** content as this process.
35046	**
35047	** If everything goes as planned, SQLITE_OK is returned. Otherwise,
35048	** an SQLite error code.
35049	*/
35050	static int pagerSyncHotJournal(Pager *pPager){
35051	int rc = SQLITE_OK;
35052	if( !pPager->noSync ){
35053	rc = sqlite3OsSync(pPager->jfd, SQLITE_SYNC_NORMAL);
35054	}
35055	if( rc==SQLITE_OK ){
35056	rc = sqlite3OsFileSize(pPager->jfd, &pPager->journalHdr);
35057	}
35058	return rc;
35059	}
35060
35061	/*
35062	** Shutdown the page cache. Free all memory and close all files.
35063	**
35064	** If a transaction was in progress when this routine is called, that
	@@ -34972,11 +35085,13 @@
35085	** call which may be made from within pagerUnlockAndRollback(). If it
35086	** is not -1, then the unsynced portion of an open journal file may
35087	** be played back into the database. If a power failure occurs while
35088	** this is happening, the database may become corrupt.
35089	*/
35090	if( isOpen(pPager->jfd) ){
35091	pPager->errCode = pagerSyncHotJournal(pPager);
35092	}
35093	pagerUnlockAndRollback(pPager);
35094	}
35095	sqlite3EndBenignMalloc();
35096	enable_simulated_io_errors();
35097	PAGERTRACE(("CLOSE %d\n", PAGERID(pPager)));
	@@ -35062,11 +35177,11 @@
35177	/* This block deals with an obscure problem. If the last connection
35178	** that wrote to this database was operating in persistent-journal
35179	** mode, then the journal file may at this point actually be larger
35180	** than Pager.journalOff bytes. If the next thing in the journal
35181	** file happens to be a journal-header (written as part of the
35182	** previous connection's transaction), and a crash or power-failure
35183	** occurs after nRec is updated but before this connection writes
35184	** anything else to the journal file (or commits/rolls back its
35185	** transaction), then SQLite may become confused when doing the
35186	** hot-journal rollback following recovery. It may roll back all
35187	** of this connections data, then proceed to rolling back the old,
	@@ -35134,10 +35249,11 @@
35249	/* The journal file was just successfully synced. Set Pager.needSync
35250	** to zero and clear the PGHDR_NEED_SYNC flag on all pagess.
35251	*/
35252	pPager->needSync = 0;
35253	pPager->journalStarted = 1;
35254	pPager->journalHdr = pPager->journalOff;
35255	sqlite3PcacheClearSyncFlags(pPager->pPCache);
35256	}
35257
35258	return SQLITE_OK;
35259	}
	@@ -35996,23 +36112,32 @@
36112	}
36113	if( rc!=SQLITE_OK ){
36114	goto failed;
36115	}
36116
36117	/* Reset the journal status fields to indicates that we have no
36118	** rollback journal at this time. */
36119	pPager->journalStarted = 0;
36120	pPager->journalOff = 0;
36121	pPager->setMaster = 0;
36122	pPager->journalHdr = 0;
36123
36124	/* Make sure the journal file has been synced to disk. */
36125
36126	/* Playback and delete the journal. Drop the database write
36127	** lock and reacquire the read lock. Purge the cache before
36128	** playing back the hot-journal so that we don't end up with
36129	** an inconsistent cache. Sync the hot journal before playing
36130	** it back since the process that crashed and left the hot journal
36131	** probably did not sync it and we are required to always sync
36132	** the journal before playing it back.
36133	*/
36134	if( isOpen(pPager->jfd) ){
36135	rc = pagerSyncHotJournal(pPager);
36136	if( rc==SQLITE_OK ){
36137	rc = pager_playback(pPager, 1);
36138	}
36139	if( rc!=SQLITE_OK ){
36140	rc = pager_error(pPager, rc);
36141	goto failed;
36142	}
36143	}
	@@ -36114,11 +36239,11 @@
36239	** of the page. This occurs in two seperate scenarios:
36240	**
36241	** a) When reading a free-list leaf page from the database, and
36242	**
36243	** b) When a savepoint is being rolled back and we need to load
36244	** a new page into the cache to be filled with the data read
36245	** from the savepoint journal.
36246	**
36247	** If noContent is true, then the data returned is zeroed instead of
36248	** being read from the database. Additionally, the bits corresponding
36249	** to pgno in Pager.pInJournal (bitvec of pages already written to the
	@@ -36546,10 +36671,12 @@
36671
36672	/* We should never write to the journal file the page that
36673	** contains the database locks. The following assert verifies
36674	** that we do not. */
36675	assert( pPg->pgno!=PAGER_MJ_PGNO(pPager) );
36676
36677	assert( pPager->journalHdr <= pPager->journalOff );
36678	CODEC2(pPager, pData, pPg->pgno, 7, return SQLITE_NOMEM, pData2);
36679	cksum = pager_cksum(pPager, (u8*)pData2);
36680	rc = write32bits(pPager->jfd, pPager->journalOff, pPg->pgno);
36681	if( rc==SQLITE_OK ){
36682	rc = sqlite3OsWrite(pPager->jfd, pData2, pPager->pageSize,
	@@ -60151,15 +60278,14 @@
60278	** Syncing an in-memory journal is a no-op. And, in fact, this routine
60279	** is never called in a working implementation. This implementation
60280	** exists purely as a contingency, in case some malfunction in some other
60281	** part of SQLite causes Sync to be called by mistake.
60282	*/
60283	static int memjrnlSync(sqlite3_file *NotUsed, int NotUsed2){
60284	UNUSED_PARAMETER2(NotUsed, NotUsed2);
60285	return SQLITE_OK;
60286	}

60287
60288	/*
60289	** Query the size of the file in bytes.
60290	*/
60291	static int memjrnlFileSize(sqlite3_file pJfd, sqlite_int64 pSize){
	@@ -60751,11 +60877,11 @@
60877	}
60878	}
60879
60880	/*
60881	** Allocate and return a pointer to an expression to load the column iCol
60882	** from datasource iSrc in SrcList pSrc.
60883	*/
60884	SQLITE_PRIVATE Expr sqlite3CreateColumnExpr(sqlite3 db, SrcList *pSrc, int iSrc, int iCol){
60885	Expr *p = sqlite3ExprAlloc(db, TK_COLUMN, 0, 0);
60886	if( p ){
60887	struct SrcList_item *pItem = &pSrc->a[iSrc];
	@@ -60763,10 +60889,12 @@
60889	p->iTable = pItem->iCursor;
60890	if( p->pTab->iPKey==iCol ){
60891	p->iColumn = -1;
60892	}else{
60893	p->iColumn = (ynVar)iCol;
60894	testcase( iCol==BMS );
60895	testcase( iCol==BMS-1 );
60896	pItem->colUsed \|= ((Bitmask)1)<<(iCol>=BMS ? BMS-1 : iCol);
60897	}
60898	ExprSetProperty(p, EP_Resolved);
60899	}
60900	return p;
	@@ -79646,32 +79774,32 @@
79774	/* Call the parser to process a CREATE TABLE, INDEX or VIEW.
79775	** But because db->init.busy is set to 1, no VDBE code is generated
79776	** or executed. All the parser does is build the internal data
79777	** structures that describe the table, index, or view.
79778	*/

79779	int rc;
79780	sqlite3_stmt *pStmt;
79781
79782	assert( db->init.busy );
79783	db->init.iDb = iDb;
79784	db->init.newTnum = atoi(argv[1]);
79785	db->init.orphanTrigger = 0;
79786	rc = sqlite3_prepare(db, argv[2], -1, &pStmt, 0);
79787	db->init.iDb = 0;

79788	if( SQLITE_OK!=rc ){
79789	if( db->init.orphanTrigger ){
79790	assert( iDb==1 );
79791	}else{
79792	pData->rc = rc;
79793	if( rc==SQLITE_NOMEM ){
79794	db->mallocFailed = 1;
79795	}else if( rc!=SQLITE_INTERRUPT && rc!=SQLITE_LOCKED ){
79796	corruptSchema(pData, argv[0], sqlite3_errmsg(db));
79797	}
79798	}

79799	}
79800	sqlite3_finalize(pStmt);
79801	}else if( argv[0]==0 ){
79802	corruptSchema(pData, 0, 0);
79803	}else{
79804	/* If the SQL column is blank it means this is an index that
79805	** was created to be the PRIMARY KEY or to fulfill a UNIQUE
	@@ -82959,12 +83087,12 @@
83087	** (13) The subquery and outer query do not both use LIMIT
83088	**
83089	** (14) The subquery does not use OFFSET
83090	**
83091	** (15) The outer query is not part of a compound select or the
83092	** subquery does not have a LIMIT clause.
83093	** (See ticket #2339 and ticket [02a8e81d44]).
83094	**
83095	** (16) The outer query is not an aggregate or the subquery does
83096	** not contain ORDER BY. (Ticket #2942) This used to not matter
83097	** until we introduced the group_concat() function.
83098	**
	@@ -83043,11 +83171,11 @@
83171	** because they could be computed at compile-time. But when LIMIT and OFFSET
83172	** became arbitrary expressions, we were forced to add restrictions (13)
83173	** and (14). */
83174	if( pSub->pLimit && p->pLimit ) return 0; /* Restriction (13) */
83175	if( pSub->pOffset ) return 0; /* Restriction (14) */
83176	if( p->pRightmost && pSub->pLimit ){
83177	return 0; /* Restriction (15) */
83178	}
83179	if( pSubSrc->nSrc==0 ) return 0; /* Restriction (7) */
83180	if( ((pSub->selFlags & SF_Distinct)!=0 \|\| pSub->pLimit)
83181	&& (pSrc->nSrc>1 \|\| isAgg) ){ /* Restrictions (4)(5)(8)(9) */
	@@ -89537,10 +89665,15 @@
89665	#ifndef SQLITE_OMIT_OR_OPTIMIZATION
89666	const int iCur = pSrc->iCursor; /* The cursor of the table to be accessed */
89667	const Bitmask maskSrc = getMask(pWC->pMaskSet, iCur); /* Bitmask for pSrc */
89668	WhereTerm * const pWCEnd = &pWC->a[pWC->nTerm]; /* End of pWC->a[] */
89669	WhereTerm pTerm; / A single term of the WHERE clause */
89670
89671	/* No OR-clause optimization allowed if the NOT INDEXED clause is used */
89672	if( pSrc->notIndexed ){
89673	return;
89674	}
89675
89676	/* Search the WHERE clause terms for a usable WO_OR term. */
89677	for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
89678	if( pTerm->eOperator==WO_OR
89679	&& ((pTerm->prereqAll & ~maskSrc) & notReady)==0
	@@ -89580,12 +89713,13 @@
89713	}
89714
89715	/* If there is an ORDER BY clause, increase the scan cost to account
89716	** for the cost of the sort. */
89717	if( pOrderBy!=0 ){
89718	WHERETRACE(("... sorting increases OR cost %.9g to %.9g\n",
89719	rTotal, rTotal+nRow*estLog(nRow)));
89720	rTotal += nRow*estLog(nRow);

89721	}
89722
89723	/* If the cost of scanning using this OR term for optimization is
89724	** less than the current cost stored in pCost, replace the contents
89725	** of pCost. */
	@@ -89658,21 +89792,21 @@
89792	/* The NOT INDEXED clause appears in the SQL. */
89793	return;
89794	}
89795
89796	assert( pParse->nQueryLoop >= (double)1 );
89797	pTable = pSrc->pTab;
89798	nTableRow = pTable->pIndex ? pTable->pIndex->aiRowEst[0] : 1000000;
89799	logN = estLog(nTableRow);
89800	costTempIdx = 2logN(nTableRow/pParse->nQueryLoop + 1);
89801	if( costTempIdx>=pCost->rCost ){
89802	/* The cost of creating the transient table would be greater than
89803	** doing the full table scan */
89804	return;
89805	}
89806
89807	/* Search for any equality comparison term */

89808	pWCEnd = &pWC->a[pWC->nTerm];
89809	for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
89810	if( termCanDriveIndex(pTerm, pSrc, notReady) ){
89811	WHERETRACE(("auto-index reduces cost from %.2f to %.2f\n",
89812	pCost->rCost, costTempIdx));
	@@ -89736,12 +89870,17 @@
89870	pWCEnd = &pWC->a[pWC->nTerm];
89871	idxCols = 0;
89872	for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
89873	if( termCanDriveIndex(pTerm, pSrc, notReady) ){
89874	int iCol = pTerm->u.leftColumn;
89875	Bitmask cMask = iCol>=BMS ? ((Bitmask)1)<<(BMS-1) : ((Bitmask)1)<<iCol;
89876	testcase( iCol==BMS );
89877	testcase( iCol==BMS-1 );
89878	if( (idxCols & cMask)==0 ){
89879	nColumn++;
89880	idxCols \|= cMask;
89881	}
89882	}
89883	}
89884	assert( nColumn>0 );
89885	pLevel->plan.nEq = nColumn;
89886
	@@ -89751,14 +89890,16 @@
89890	** original table never needs to be accessed. Automatic indices must
89891	** be a covering index because the index will not be updated if the
89892	** original table changes and the index and table cannot both be used
89893	** if they go out of sync.
89894	*/
89895	extraCols = pSrc->colUsed & (~idxCols \| (((Bitmask)1)<<(BMS-1)));
89896	mxBitCol = (pTable->nCol >= BMS-1) ? BMS-1 : pTable->nCol;
89897	testcase( pTable->nCol==BMS-1 );
89898	testcase( pTable->nCol==BMS-2 );
89899	for(i=0; i<mxBitCol; i++){
89900	if( extraCols & (((Bitmask)1)<<i) ) nColumn++;
89901	}
89902	if( pSrc->colUsed & (((Bitmask)1)<<(BMS-1)) ){
89903	nColumn += pTable->nCol - BMS + 1;
89904	}
89905	pLevel->plan.wsFlags \|= WHERE_COLUMN_EQ \| WHERE_IDX_ONLY \| WO_EQ;
	@@ -89776,25 +89917,31 @@
89917	pIdx->aSortOrder = (u8*)&pIdx->aiColumn[nColumn];
89918	pIdx->zName = "auto-index";
89919	pIdx->nColumn = nColumn;
89920	pIdx->pTable = pTable;
89921	n = 0;
89922	idxCols = 0;
89923	for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
89924	if( termCanDriveIndex(pTerm, pSrc, notReady) ){
89925	int iCol = pTerm->u.leftColumn;
89926	Bitmask cMask = iCol>=BMS ? ((Bitmask)1)<<(BMS-1) : ((Bitmask)1)<<iCol;
89927	if( (idxCols & cMask)==0 ){
89928	Expr *pX = pTerm->pExpr;
89929	idxCols \|= cMask;
89930	pIdx->aiColumn[n] = pTerm->u.leftColumn;
89931	pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
89932	pIdx->azColl[n] = pColl->zName;
89933	n++;
89934	}
89935	}
89936	}
89937	assert( n==pLevel->plan.nEq );
89938
89939	/* Add additional columns needed to make the automatic index into
89940	** a covering index */
89941	for(i=0; i<mxBitCol; i++){
89942	if( extraCols & (((Bitmask)1)<<i) ){
89943	pIdx->aiColumn[n] = i;
89944	pIdx->azColl[n] = "BINARY";
89945	n++;
89946	}
89947	}
	@@ -90519,18 +90666,18 @@
90666	**
90667	** bInEst:
90668	** Set to true if there was at least one "x IN (SELECT ...)" term used
90669	** in determining the value of nInMul.
90670	**
90671	** estBound:
90672	** An estimate on the amount of the table that must be searched. A
90673	** value of 100 means the entire table is searched. Range constraints
90674	** might reduce this to a value less than 100 to indicate that only
90675	** a fraction of the table needs searching. In the absence of
90676	** sqlite_stat2 ANALYZE data, a single inequality reduces the search
90677	** space to 1/3rd its original size. So an x>? constraint reduces
90678	** estBound to 33. Two constraints (x>? AND x<?) reduce estBound to 11.
90679	**
90680	** bSort:
90681	** Boolean. True if there is an ORDER BY clause that will require an
90682	** external sort (i.e. scanning the index being evaluated will not
90683	** correctly order records).
	@@ -90548,17 +90695,18 @@
90695	** SELECT a, b, c FROM tbl WHERE a = 1;
90696	*/
90697	int nEq;
90698	int bInEst = 0;
90699	int nInMul = 1;
90700	int estBound = 100;
90701	int nBound = 0; /* Number of range constraints seen */
90702	int bSort = 0;
90703	int bLookup = 0;
90704	WhereTerm pTerm; / A single term of the WHERE clause */
90705
90706	/* Determine the values of nEq and nInMul */
90707	for(nEq=0; nEq<pProbe->nColumn; nEq++){

90708	int j = pProbe->aiColumn[nEq];
90709	pTerm = findTerm(pWC, iCur, j, notReady, eqTermMask, pIdx);
90710	if( pTerm==0 ) break;
90711	wsFlags \|= (WHERE_COLUMN_EQ\|WHERE_ROWID_EQ);
90712	if( pTerm->eOperator & WO_IN ){
	@@ -90574,22 +90722,24 @@
90722	wsFlags \|= WHERE_COLUMN_NULL;
90723	}
90724	used \|= pTerm->prereqRight;
90725	}
90726
90727	/* Determine the value of estBound. */
90728	if( nEq<pProbe->nColumn ){
90729	int j = pProbe->aiColumn[nEq];
90730	if( findTerm(pWC, iCur, j, notReady, WO_LT\|WO_LE\|WO_GT\|WO_GE, pIdx) ){
90731	WhereTerm *pTop = findTerm(pWC, iCur, j, notReady, WO_LT\|WO_LE, pIdx);
90732	WhereTerm *pBtm = findTerm(pWC, iCur, j, notReady, WO_GT\|WO_GE, pIdx);
90733	whereRangeScanEst(pParse, pProbe, nEq, pBtm, pTop, &estBound);
90734	if( pTop ){
90735	nBound = 1;
90736	wsFlags \|= WHERE_TOP_LIMIT;
90737	used \|= pTop->prereqRight;
90738	}
90739	if( pBtm ){
90740	nBound++;
90741	wsFlags \|= WHERE_BTM_LIMIT;
90742	used \|= pBtm->prereqRight;
90743	}
90744	wsFlags \|= (WHERE_COLUMN_RANGE\|WHERE_ROWID_RANGE);
90745	}
	@@ -90635,12 +90785,11 @@
90785	}else{
90786	bLookup = 1;
90787	}
90788	}
90789
90790	/*

90791	** Estimate the number of rows of output. For an IN operator,
90792	** do not let the estimate exceed half the rows in the table.
90793	*/
90794	nRow = (double)(aiRowEst[nEq] * nInMul);
90795	if( bInEst && nRow*2>aiRowEst[0] ){
	@@ -90655,12 +90804,12 @@
90804	cost = nRow + nInMul*estLog(aiRowEst[0]);
90805
90806	/* Adjust the number of rows and the cost downward to reflect rows
90807	** that are excluded by range constraints.
90808	*/
90809	nRow = (nRow * (double)estBound) / (double)100;
90810	cost = (cost * (double)estBound) / (double)100;
90811
90812	/* Add in the estimated cost of sorting the result
90813	*/
90814	if( bSort ){
90815	cost += cost*estLog(cost);
	@@ -90672,22 +90821,80 @@
90821	*/
90822	if( pIdx && bLookup==0 ){
90823	cost /= (double)2;
90824	}
90825	/** Cost of using this index has now been computed **/
90826
90827	/* If there are additional constraints on this table that cannot
90828	** be used with the current index, but which might lower the number
90829	** of output rows, adjust the nRow value accordingly. This only
90830	** matters if the current index is the least costly, so do not bother
90831	** with this step if we already know this index will not be chosen.
90832	** Also, never reduce the output row count below 2 using this step.
90833	**
90834	** Do not reduce the output row count if pSrc is the only table that
90835	** is notReady; if notReady is a power of two. This will be the case
90836	** when the main sqlite3WhereBegin() loop is scanning for a table with
90837	** and "optimal" index, and on such a scan the output row count
90838	** reduction is not valid because it does not update the "pCost->used"
90839	** bitmap. The notReady bitmap will also be a power of two when we
90840	** are scanning for the last table in a 64-way join. We are willing
90841	** to bypass this optimization in that corner case.
90842	*/
90843	if( nRow>2 && cost<=pCost->rCost && (notReady & (notReady-1))!=0 ){
90844	int k; /* Loop counter */
90845	int nSkipEq = nEq; /* Number of == constraints to skip */
90846	int nSkipRange = nBound; /* Number of < constraints to skip */
90847	Bitmask thisTab; /* Bitmap for pSrc */
90848
90849	thisTab = getMask(pWC->pMaskSet, iCur);
90850	for(pTerm=pWC->a, k=pWC->nTerm; nRow>2 && k; k--, pTerm++){
90851	if( pTerm->wtFlags & TERM_VIRTUAL ) continue;
90852	if( (pTerm->prereqAll & notReady)!=thisTab ) continue;
90853	if( pTerm->eOperator & (WO_EQ\|WO_IN\|WO_ISNULL) ){
90854	if( nSkipEq ){
90855	/* Ignore the first nEq equality matches since the index
90856	** has already accounted for these */
90857	nSkipEq--;
90858	}else{
90859	/* Assume each additional equality match reduces the result
90860	** set size by a factor of 10 */
90861	nRow /= 10;
90862	}
90863	}else if( pTerm->eOperator & (WO_LT\|WO_LE\|WO_GT\|WO_GE) ){
90864	if( nSkipRange ){
90865	/* Ignore the first nBound range constraints since the index
90866	** has already accounted for these */
90867	nSkipRange--;
90868	}else{
90869	/* Assume each additional range constraint reduces the result
90870	** set size by a factor of 3 */
90871	nRow /= 3;
90872	}
90873	}else{
90874	/* Any other expression lowers the output row count by half */
90875	nRow /= 2;
90876	}
90877	}
90878	if( nRow<2 ) nRow = 2;
90879	}
90880
90881
90882	WHERETRACE((
90883	"%s(%s): nEq=%d nInMul=%d estBound=%d bSort=%d bLookup=%d wsFlags=0x%x\n"
90884	" notReady=0x%llx nRow=%.2f cost=%.2f used=0x%llx\n",
90885	pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk"),
90886	nEq, nInMul, estBound, bSort, bLookup, wsFlags,
90887	notReady, nRow, cost, used
90888	));
90889
90890	/* If this index is the best we have seen so far, then record this
90891	** index and its cost in the pCost structure.
90892	*/
90893	if( (!pIdx \|\| wsFlags)
90894	&& (cost<pCost->rCost \|\| (cost<=pCost->rCost && nRow<pCost->nRow))
90895	){
90896	pCost->rCost = cost;
90897	pCost->nRow = nRow;
90898	pCost->used = used;
90899	pCost->plan.wsFlags = (wsFlags&wsFlagMask);
90900	pCost->plan.nEq = nEq;
	@@ -90718,11 +90925,12 @@
90925	\|\| pCost->plan.u.pIdx==0
90926	\|\| pCost->plan.u.pIdx==pSrc->pIndex
90927	);
90928
90929	WHERETRACE(("best index is: %s\n",
90930	((pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)==0 ? "none" :
90931	pCost->plan.u.pIdx ? pCost->plan.u.pIdx->zName : "ipk")
90932	));
90933
90934	bestOrClauseIndex(pParse, pWC, pSrc, notReady, pOrderBy, pCost);
90935	bestAutomaticIndex(pParse, pWC, pSrc, notReady, pCost);
90936	pCost->plan.wsFlags \|= eqTermMask;
	@@ -91654,11 +91862,11 @@
91862
91863	/*
91864	** Free a WhereInfo structure
91865	*/
91866	static void whereInfoFree(sqlite3 db, WhereInfo pWInfo){
91867	if( ALWAYS(pWInfo) ){
91868	int i;
91869	for(i=0; i<pWInfo->nLevel; i++){
91870	sqlite3_index_info *pInfo = pWInfo->a[i].pIdxInfo;
91871	if( pInfo ){
91872	/* assert( pInfo->needToFreeIdxStr==0 \|\| db->mallocFailed ); */
	@@ -91791,10 +91999,11 @@
91999	sqlite3 db; / Database connection */
92000
92001	/* The number of tables in the FROM clause is limited by the number of
92002	** bits in a Bitmask
92003	*/
92004	testcase( pTabList->nSrc==BMS );
92005	if( pTabList->nSrc>BMS ){
92006	sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
92007	return 0;
92008	}
92009
	@@ -91930,24 +92139,29 @@
92139
92140	memset(&bestPlan, 0, sizeof(bestPlan));
92141	bestPlan.rCost = SQLITE_BIG_DBL;
92142
92143	/* Loop through the remaining entries in the FROM clause to find the
92144	** next nested loop. The loop tests all FROM clause entries
92145	** either once or twice.
92146	**
92147	** The first test is always performed if there are two or more entries
92148	** remaining and never performed if there is only one FROM clause entry
92149	** to choose from. The first test looks for an "optimal" scan. In
92150	** this context an optimal scan is one that uses the same strategy
92151	** for the given FROM clause entry as would be selected if the entry
92152	** were used as the innermost nested loop. In other words, a table
92153	** is chosen such that the cost of running that table cannot be reduced
92154	** by waiting for other tables to run first. This "optimal" test works
92155	** by first assuming that the FROM clause is on the inner loop and finding
92156	** its query plan, then checking to see if that query plan uses any
92157	** other FROM clause terms that are notReady. If no notReady terms are
92158	** used then the "optimal" query plan works.
92159	**
92160	** The second loop iteration is only performed if no optimal scan
92161	** strategies were found by the first loop. This 2nd iteration is used to
92162	** search for the lowest cost scan overall.
92163	**
92164	** Previous versions of SQLite performed only the second iteration -
92165	** the next outermost loop was always that with the lowest overall
92166	** cost. However, this meant that SQLite could select the wrong plan
92167	** for scripts such as the following:
	@@ -91961,13 +92175,12 @@
92175	** However, since the cost of a linear scan through table t2 is the same
92176	** as the cost of a linear scan through table t1, a simple greedy
92177	** algorithm may choose to use t2 for the outer loop, which is a much
92178	** costlier approach.
92179	*/
92180	for(isOptimal=(iFrom<nTabList-1); isOptimal>=0; isOptimal--){
92181	Bitmask mask; /* Mask of tables not yet ready */

92182	for(j=iFrom, pTabItem=&pTabList->a[j]; j<nTabList; j++, pTabItem++){
92183	int doNotReorder; /* True if this table should not be reordered */
92184	WhereCost sCost; /* Cost information from best[Virtual]Index() */
92185	ExprList pOrderBy; / ORDER BY clause for index to optimize */
92186
	@@ -91976,10 +92189,11 @@
92189	m = getMask(pMaskSet, pTabItem->iCursor);
92190	if( (m & notReady)==0 ){
92191	if( j==iFrom ) iFrom++;
92192	continue;
92193	}
92194	mask = (isOptimal ? m : notReady);
92195	pOrderBy = ((i==0 && ppOrderBy )?*ppOrderBy:0);
92196
92197	assert( pTabItem->pTab );
92198	#ifndef SQLITE_OMIT_VIRTUALTABLE
92199	if( IsVirtual(pTabItem->pTab) ){
	@@ -91991,12 +92205,15 @@
92205	bestBtreeIndex(pParse, pWC, pTabItem, mask, pOrderBy, &sCost);
92206	}
92207	assert( isOptimal \|\| (sCost.used&notReady)==0 );
92208
92209	if( (sCost.used&notReady)==0
92210	&& (bestJ<0 \|\| sCost.rCost<bestPlan.rCost
92211	\|\| (sCost.rCost<=bestPlan.rCost && sCost.nRow<bestPlan.nRow))
92212	){
92213	WHERETRACE(("... best so far with cost=%g and nRow=%g\n",
92214	sCost.rCost, sCost.nRow));
92215	bestPlan = sCost;
92216	bestJ = j;
92217	}
92218	if( doNotReorder ) break;
92219	}
	@@ -92118,10 +92335,12 @@
92335	#endif
92336	if( (pLevel->plan.wsFlags & WHERE_IDX_ONLY)==0
92337	&& (wctrlFlags & WHERE_OMIT_OPEN)==0 ){
92338	int op = pWInfo->okOnePass ? OP_OpenWrite : OP_OpenRead;
92339	sqlite3OpenTable(pParse, pTabItem->iCursor, iDb, pTab, op);
92340	testcase( pTab->nCol==BMS-1 );
92341	testcase( pTab->nCol==BMS );
92342	if( !pWInfo->okOnePass && pTab->nCol<BMS ){
92343	Bitmask b = pTabItem->colUsed;
92344	int n = 0;
92345	for(; b; b=b>>1, n++){}
92346	sqlite3VdbeChangeP4(v, sqlite3VdbeCurrentAddr(v)-1,
	@@ -92297,11 +92516,11 @@
92516	){
92517	int ws = pLevel->plan.wsFlags;
92518	if( !pWInfo->okOnePass && (ws & WHERE_IDX_ONLY)==0 ){
92519	sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor);
92520	}
92521	if( (ws & WHERE_INDEXED)!=0 && (ws & WHERE_TEMP_INDEX)==0 ){
92522	sqlite3VdbeAddOp1(v, OP_Close, pLevel->iIdxCur);
92523	}
92524	}
92525
92526	/* If this scan uses an index, make code substitutions to read data
	@@ -92345,14 +92564,12 @@
92564	}
92565	}
92566
92567	/* Final cleanup
92568	*/
92569	pParse->nQueryLoop = pWInfo->savedNQueryLoop;
92570	whereInfoFree(db, pWInfo);


92571	return;
92572	}
92573
92574	/************ End of where.c *********************************************/
92575	/************ Begin file parse.c *****************************************/
92576

M src/sqlite3.h

+1 -3

		--- src/sqlite3.h
		+++ src/sqlite3.h
		@@ -107,11 +107,11 @@
107	107	** [sqlite3_libversion_number()], [sqlite3_sourceid()],
108	108	** [sqlite_version()] and [sqlite_source_id()].
109	109	*/
110	110	#define SQLITE_VERSION "3.6.23"
111	111	#define SQLITE_VERSION_NUMBER 3006023
112		-#define SQLITE_SOURCE_ID "2010-04-07 20:32:19 e388fe8be878c80ef0bfd1699a7268cdb22cb3c6"
	112	+#define SQLITE_SOURCE_ID "2010-04-15 23:24:29 f96782b389b5b97b488dc5814f7082e0393f64cd"
113	113
114	114	/*
115	115	** CAPI3REF: Run-Time Library Version Numbers
116	116	** KEYWORDS: sqlite3_version, sqlite3_sourceid
117	117	**
		@@ -3883,12 +3883,10 @@
3883	3883	** ^For the purposes of this API, a transaction is said to have been
3884	3884	** rolled back if an explicit "ROLLBACK" statement is executed, or
3885	3885	** an error or constraint causes an implicit rollback to occur.
3886	3886	** ^The rollback callback is not invoked if a transaction is
3887	3887	** automatically rolled back because the database connection is closed.
3888		-** ^The rollback callback is not invoked if a transaction is
3889		-** rolled back because a commit callback returned non-zero.
3890	3888	**
3891	3889	** See also the [sqlite3_update_hook()] interface.
3892	3890	*/
3893	3891	SQLITE_API void sqlite3_commit_hook(sqlite3, int()(void), void*);
3894	3892	SQLITE_API void sqlite3_rollback_hook(sqlite3, void()(void ), void*);
3895	3893

	--- src/sqlite3.h
	+++ src/sqlite3.h
	@@ -107,11 +107,11 @@
107	** [sqlite3_libversion_number()], [sqlite3_sourceid()],
108	** [sqlite_version()] and [sqlite_source_id()].
109	*/
110	#define SQLITE_VERSION "3.6.23"
111	#define SQLITE_VERSION_NUMBER 3006023
112	#define SQLITE_SOURCE_ID "2010-04-07 20:32:19 e388fe8be878c80ef0bfd1699a7268cdb22cb3c6"
113
114	/*
115	** CAPI3REF: Run-Time Library Version Numbers
116	** KEYWORDS: sqlite3_version, sqlite3_sourceid
117	**
	@@ -3883,12 +3883,10 @@
3883	** ^For the purposes of this API, a transaction is said to have been
3884	** rolled back if an explicit "ROLLBACK" statement is executed, or
3885	** an error or constraint causes an implicit rollback to occur.
3886	** ^The rollback callback is not invoked if a transaction is
3887	** automatically rolled back because the database connection is closed.
3888	** ^The rollback callback is not invoked if a transaction is
3889	** rolled back because a commit callback returned non-zero.
3890	**
3891	** See also the [sqlite3_update_hook()] interface.
3892	*/
3893	SQLITE_API void sqlite3_commit_hook(sqlite3, int()(void), void*);
3894	SQLITE_API void sqlite3_rollback_hook(sqlite3, void()(void ), void*);
3895

	--- src/sqlite3.h
	+++ src/sqlite3.h
	@@ -107,11 +107,11 @@
107	** [sqlite3_libversion_number()], [sqlite3_sourceid()],
108	** [sqlite_version()] and [sqlite_source_id()].
109	*/
110	#define SQLITE_VERSION "3.6.23"
111	#define SQLITE_VERSION_NUMBER 3006023
112	#define SQLITE_SOURCE_ID "2010-04-15 23:24:29 f96782b389b5b97b488dc5814f7082e0393f64cd"
113
114	/*
115	** CAPI3REF: Run-Time Library Version Numbers
116	** KEYWORDS: sqlite3_version, sqlite3_sourceid
117	**
	@@ -3883,12 +3883,10 @@
3883	** ^For the purposes of this API, a transaction is said to have been
3884	** rolled back if an explicit "ROLLBACK" statement is executed, or
3885	** an error or constraint causes an implicit rollback to occur.
3886	** ^The rollback callback is not invoked if a transaction is
3887	** automatically rolled back because the database connection is closed.


3888	**
3889	** See also the [sqlite3_update_hook()] interface.
3890	*/
3891	SQLITE_API void sqlite3_commit_hook(sqlite3, int()(void), void*);
3892	SQLITE_API void sqlite3_rollback_hook(sqlite3, void()(void ), void*);
3893

Fossil SCM

Keyboard Shortcuts