aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--ext/fts5/fts5_index.c31
-rw-r--r--ext/fts5/test/fts5corrupt3.test5
-rw-r--r--ext/fts5/test/fts5integrity.test6
-rw-r--r--manifest50
-rw-r--r--manifest.uuid2
-rw-r--r--src/btree.c24
-rw-r--r--src/btree.h1
-rw-r--r--src/build.c14
-rw-r--r--src/resolve.c5
-rw-r--r--src/select.c100
-rw-r--r--src/shell.c.in1
-rw-r--r--src/sqliteInt.h4
-rw-r--r--src/vdbe.c26
-rw-r--r--src/where.c11
-rw-r--r--src/wherecode.c6
-rw-r--r--src/whereexpr.c2
-rw-r--r--test/eqp.test3
-rw-r--r--test/existsexpr.test426
-rw-r--r--test/existsexpr2.test96
-rw-r--r--test/existsfault.test49
-rw-r--r--test/incrblob4.test15
-rw-r--r--test/json101.test8
-rw-r--r--test/notnull2.test2
23 files changed, 828 insertions, 59 deletions
diff --git a/ext/fts5/fts5_index.c b/ext/fts5/fts5_index.c
index 5a5063748..7036e57f5 100644
--- a/ext/fts5/fts5_index.c
+++ b/ext/fts5/fts5_index.c
@@ -8286,19 +8286,27 @@ static int fts5TestUtf8(const char *z, int n){
/*
** This function is also purely an internal test. It does not contribute to
** FTS functionality, or even the integrity-check, in any way.
+**
+** This function sets output variable (*pbFail) to true if the test fails. Or
+** leaves it unchanged if the test succeeds.
*/
static void fts5TestTerm(
Fts5Index *p,
Fts5Buffer *pPrev, /* Previous term */
const char *z, int n, /* Possibly new term to test */
u64 expected,
- u64 *pCksum
+ u64 *pCksum,
+ int *pbFail
){
int rc = p->rc;
if( pPrev->n==0 ){
fts5BufferSet(&rc, pPrev, n, (const u8*)z);
}else
- if( rc==SQLITE_OK && (pPrev->n!=n || memcmp(pPrev->p, z, n)) ){
+ if( *pbFail==0
+ && rc==SQLITE_OK
+ && (pPrev->n!=n || memcmp(pPrev->p, z, n))
+ && (p->pHash==0 || p->pHash->nEntry==0)
+ ){
u64 cksum3 = *pCksum;
const char *zTerm = (const char*)&pPrev->p[1]; /* term sans prefix-byte */
int nTerm = pPrev->n-1; /* Size of zTerm in bytes */
@@ -8348,7 +8356,7 @@ static void fts5TestTerm(
fts5BufferSet(&rc, pPrev, n, (const u8*)z);
if( rc==SQLITE_OK && cksum3!=expected ){
- rc = FTS5_CORRUPT;
+ *pbFail = 1;
}
*pCksum = cksum3;
}
@@ -8357,7 +8365,7 @@ static void fts5TestTerm(
#else
# define fts5TestDlidxReverse(x,y,z)
-# define fts5TestTerm(u,v,w,x,y,z)
+# define fts5TestTerm(t,u,v,w,x,y,z)
#endif
/*
@@ -8615,6 +8623,7 @@ int sqlite3Fts5IndexIntegrityCheck(Fts5Index *p, u64 cksum, int bUseCksum){
/* Used by extra internal tests only run if NDEBUG is not defined */
u64 cksum3 = 0; /* Checksum based on contents of indexes */
Fts5Buffer term = {0,0,0}; /* Buffer used to hold most recent term */
+ int bTestFail = 0;
#endif
const int flags = FTS5INDEX_QUERY_NOOUTPUT;
@@ -8657,7 +8666,7 @@ int sqlite3Fts5IndexIntegrityCheck(Fts5Index *p, u64 cksum, int bUseCksum){
char *z = (char*)fts5MultiIterTerm(pIter, &n);
/* If this is a new term, query for it. Update cksum3 with the results. */
- fts5TestTerm(p, &term, z, n, cksum2, &cksum3);
+ fts5TestTerm(p, &term, z, n, cksum2, &cksum3, &bTestFail);
if( p->rc ) break;
if( eDetail==FTS5_DETAIL_NONE ){
@@ -8675,7 +8684,7 @@ int sqlite3Fts5IndexIntegrityCheck(Fts5Index *p, u64 cksum, int bUseCksum){
}
}
}
- fts5TestTerm(p, &term, 0, 0, cksum2, &cksum3);
+ fts5TestTerm(p, &term, 0, 0, cksum2, &cksum3, &bTestFail);
fts5MultiIterFree(pIter);
if( p->rc==SQLITE_OK && bUseCksum && cksum!=cksum2 ){
@@ -8684,11 +8693,17 @@ int sqlite3Fts5IndexIntegrityCheck(Fts5Index *p, u64 cksum, int bUseCksum){
"fts5: checksum mismatch for table \"%s\"", p->pConfig->zName
);
}
-
- fts5StructureRelease(pStruct);
#ifdef SQLITE_DEBUG
+ /* In SQLITE_DEBUG builds, expensive extra checks were run as part of
+ ** the integrity-check above. If no other errors were detected, but one
+ ** of these tests failed, set the result to SQLITE_CORRUPT_VTAB here. */
+ if( p->rc==SQLITE_OK && bTestFail ){
+ p->rc = FTS5_CORRUPT;
+ }
fts5BufferFree(&term);
#endif
+
+ fts5StructureRelease(pStruct);
fts5BufferFree(&poslist);
return fts5IndexReturn(p);
}
diff --git a/ext/fts5/test/fts5corrupt3.test b/ext/fts5/test/fts5corrupt3.test
index 66acf07ee..eab4c3c91 100644
--- a/ext/fts5/test/fts5corrupt3.test
+++ b/ext/fts5/test/fts5corrupt3.test
@@ -6644,7 +6644,7 @@ do_test 48.0 {
do_catchsql_test 48.1 {
INSERT INTO t1(t1) VALUES('integrity-check');
-} {1 {database disk image is malformed}}
+} {1 {fts5: corruption on page 1, segment 1, table "t1"}}
#--------------------------------------------------------------------------
reset_db
@@ -10106,7 +10106,7 @@ do_test 68.0 {
do_catchsql_test 68.1 {
PRAGMA reverse_unordered_selects=ON;
INSERT INTO t1(t1) SELECT x FROM t2;
-} {1 {database disk image is malformed}}
+} {1 {fts5: corruption on page 1, segment 1, table "t1"}}
#-------------------------------------------------------------------------
reset_db
@@ -16126,4 +16126,3 @@ do_catchsql_test 83.1 {
sqlite3_fts5_may_be_corrupt 0
finish_test
-
diff --git a/ext/fts5/test/fts5integrity.test b/ext/fts5/test/fts5integrity.test
index 5c4002180..4bf120c44 100644
--- a/ext/fts5/test/fts5integrity.test
+++ b/ext/fts5/test/fts5integrity.test
@@ -37,6 +37,12 @@ do_execsql_test 2.1 {
INSERT INTO yy(yy) VALUES('integrity-check');
}
+db close
+sqlite3 db test.db
+do_execsql_test 2.1 {
+ INSERT INTO yy(yy) VALUES('integrity-check');
+}
+
#--------------------------------------------------------------------
#
do_execsql_test 3.0 {
diff --git a/manifest b/manifest
index 48b10eebb..58cfe3114 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Rework\sthe\sfix\sto\sthe\sproblem\sdescribed\sby\n[forum:/forumpost/b9647a113b465950|forum\spost\sb9647a113b]\sso\sthat\sit\nprovides\sa\smore\scomplete\sfix\sthat\scovers\scases\sthat\swere\snot\sresolved\sby\nthe\soriginal\sfix,\sand\sso\sthat\sit\sdoes\snot\scause\sperformance\sregressions.
-D 2025-07-07T16:19:44.166
+C Fix\sa\spossible\suser-after\sfree\sfollowing\sOOM\sin\sthe\sEXISTS-to-JOIN\noptimization.
+D 2025-07-08T22:11:39.260
F .fossil-settings/binary-glob 61195414528fb3ea9693577e1980230d78a1f8b0a54c78cf1b9b24d0a409ed6a x
F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
@@ -113,7 +113,7 @@ F ext/fts5/fts5_buffer.c f1e6d0324d7c55329d340673befc26681a372a4d36086caa8d1ec7d
F ext/fts5/fts5_config.c e7d8dd062b44a66cd77e5a0f74f23a2354cd1f3f8575afb967b2773c3384f7f8
F ext/fts5/fts5_expr.c be9e5f7f11d87e7bd3680832c93c13050fe351994b5052b0215c2ef40312c23a
F ext/fts5/fts5_hash.c a6266cedd801ab7964fa9e74ebcdda6d30ec6a96107fa24148ec6b7b5b80f6e0
-F ext/fts5/fts5_index.c ed562b75c87efaa80c40e55f6c1102904728e35baa293c371f96b1c8e151399d
+F ext/fts5/fts5_index.c 8321944bcabdb3f3cac1c44867758ff9a9baaee5532debed2721d4d64d3c615d
F ext/fts5/fts5_main.c e558225168845dc708abeb2ad10415696e5a3249bcba1810ba3c7ef80764962e
F ext/fts5/fts5_storage.c 19bc7c4cbe1e6a2dd9849ef7d84b5ca1fcbf194cefc3e386b901e00e08bf05c2
F ext/fts5/fts5_tcl.c 7fb5a3d3404099075aaa2457307cb459bbc257c0de3dbd52b1e80a5b503e0329
@@ -162,7 +162,7 @@ F ext/fts5/test/fts5contentless4.test ec34dc69ef474ca9997dae6d91e072906e0e9a5a4b
F ext/fts5/test/fts5contentless5.test 38cd0392c730dc7090c550321ce3c24ba4c392bc97308b51a4180e9959dca7b5
F ext/fts5/test/fts5corrupt.test 237fce1c3261bb3a5bec333b0f0dbf5b105ec32627ef14cccbda3cfe13833193
F ext/fts5/test/fts5corrupt2.test 4a03a158c2cb617c9f76d26b35c1ef2534124bc0bbddcea38dfd5b170ebea27b
-F ext/fts5/test/fts5corrupt3.test 03a6118a8fe5a7c217c28c92b6b25ba04643640a4ac0a1d2b8d10de8191dc5f4
+F ext/fts5/test/fts5corrupt3.test 43d6a836892d79ab738ab89b3b6f4ae46c07ee966193e4b357bbb14e7f81d5da
F ext/fts5/test/fts5corrupt4.test dc08d19f5b8943e95a7778a7d8da592042504faf18dd93f68f7d7a0d7d7dd733
F ext/fts5/test/fts5corrupt5.test 73985d4fe6d8f0d5d5c7bcf79ae7c6522c376cd6ad710a0ff2f26e0c2e222abe
F ext/fts5/test/fts5corrupt6.test 2d72db743db7b5d9c9a6d0cfef24d799ed1aa5e8192b66c40e871a37ed9eed06
@@ -197,7 +197,7 @@ F ext/fts5/test/fts5first.test bfd685b96905bf541d99d8644e0a7219d1d833455a08ab64e
F ext/fts5/test/fts5full.test 97d263c1072f4a560929cca31e70f65d2ae232610e17e6affcf7e979df59547b
F ext/fts5/test/fts5fuzz1.test 238d8c45f3b81342aa384de3e581ff2fa330bf922a7b69e484bbc06051a1080e
F ext/fts5/test/fts5hash.test fd3e0367fbf0b0944d6936fdb22696350f57b9871069c6766251578a103e8a14
-F ext/fts5/test/fts5integrity.test 646796671205dae46af5bb12a49b5696483cfe8e12d71d21454940b13ace95ab
+F ext/fts5/test/fts5integrity.test c423ce16fd1ccadcac7fc22f794226b2bb00f5a187c0ab1d9f8502521b1bae05
F ext/fts5/test/fts5integrity2.test 4c3636615c0201232c44a8105d5cb14fd5499fd0ee3014d7ffd7e83aac76ece8
F ext/fts5/test/fts5interrupt.test 20d04204d3e341b104c0c24a41596b6393a3a81eba1044c168db0e106f9ac92c
F ext/fts5/test/fts5lastrowid.test f36298a1fb9f988bde060a274a7ce638faa9c38a31400f8d2d27ea9373e0c4a1
@@ -726,10 +726,10 @@ F src/auth.c 54ab9c6c5803b47c0d45b76ce27eff22a03b4b1f767c5945a3a4eb13aa4c78dc
F src/backup.c 5c97e8023aab1ce14a42387eb3ae00ba5a0644569e3476f38661fa6f824c3523
F src/bitvec.c e242d4496774dfc88fa278177dd23b607dce369ccafb3f61b41638eea2c9b399
F src/btmutex.c 30dada73a819a1ef5b7583786370dce1842e12e1ad941e4d05ac29695528daea
-F src/btree.c 783f9999f9ca56846619ba902f5970e181d897c23cc923c915fef225af6dda8a
-F src/btree.h 18e5e7b2124c23426a283523e5f31a4bff029131b795bb82391f9d2f3136fc50
+F src/btree.c cb5b8ceb9baa02a63a2f83dec09c4153e1cfbdf9c2adef5c62c26d2160eeb067
+F src/btree.h e823c46d87f63d904d735a24b76146d19f51f04445ea561f71cc3382fd1307f0
F src/btreeInt.h 9c0f9ea5c9b5f4dcaea18111d43efe95f2ac276cd86d770dce10fd99ccc93886
-F src/build.c 67159d31bfc565e5f23a7be8159325bae599bddd19fc584ac2511b947cf341d3
+F src/build.c cc4f287348790bbb7219f7e8dee13b1c345c3377fcdd98eca866e7457ecd07e7
F src/callback.c acae8c8dddda41ee85cfdf19b926eefe830f371069f8aadca3aa39adf5b1c859
F src/complete.c a3634ab1e687055cd002e11b8f43eb75c17da23e
F src/date.c 9db4d604e699a73e10b8e85a44db074a1f04c0591a77e2abfd77703f50dce1e9
@@ -783,14 +783,14 @@ F src/pragma.c 30b535d0a66348df844ee36f890617b4cf45e9a22dcbc47ec3ca92909c50aaf1
F src/prepare.c 1832be043fce7d489959aae6f994c452d023914714c4d5457beaed51c0f3d126
F src/printf.c 71b6d3a0093bf23f473e25480ca0024e8962681506c75f4ffd3d343a3f0ab113
F src/random.c 606b00941a1d7dd09c381d3279a058d771f406c5213c9932bbd93d5587be4b9c
-F src/resolve.c d40fe18d7c2fd0339f5846ffcf7d6809866e380acdf14c76fb2af87e9fe13f64
+F src/resolve.c d3ee7ed308d46f4ee6d3bb6316d8d6f87158f93a7fd616732138cc953cf364f0
F src/rowset.c 8432130e6c344b3401a8874c3cb49fefe6873fec593294de077afea2dce5ec97
-F src/select.c 700e98061a61bf8e8b0f2707ed22ffc44c7a7b660dbf7c569430e04d2f95d8a5
-F src/shell.c.in 4f14a1f5196b6006abc8e73cc8fd6c1a62cf940396f8ba909d6711f35f074bb6
+F src/select.c 33a46f68191ac6cb00409417593adb03be68c8078d36ebe079a3a0914b220d93
+F src/shell.c.in 73c0eeb7c265d59b99219d5aa055f412f07842088d8036b6d259927d85dd1bbf
F src/sqlite.h.in 5c54f2461a1ea529bab8499148a2b238e2d4bb571d59e8ea5322d0c190abb693
F src/sqlite3.rc 015537e6ac1eec6c7050e17b616c2ffe6f70fca241835a84a4f0d5937383c479
F src/sqlite3ext.h 0bfd049bb2088cc44c2ad54f2079d1c6e43091a4e1ce8868779b75f6c1484f1e
-F src/sqliteInt.h 72bf74887e551d8adf0140bf20fbc321fda7f3cef2c6dc0c5aeefa584cd713a1
+F src/sqliteInt.h 9c99d7565a839ad342cdda504c4b7921bb1a24c07227b8f50b7b131245a20693
F src/sqliteLimit.h 6d817c28a8f19af95e6f4921933b7fbbca48a962bce0eb0ec81e8bb3ef38e68b
F src/status.c 0e72e4f6be6ccfde2488eb63210297e75f569f3ce9920f6c3d77590ec6ce5ffd
F src/table.c 0f141b58a16de7e2fbe81c308379e7279f4c6b50eb08efeec5892794a0ba30d1
@@ -852,7 +852,7 @@ F src/upsert.c 215328c3f91623c520ec8672c44323553f12caeb4f01b1090ebdca99fdf7b4f1
F src/utf.c 7267c3fb9e2467020507601af3354c2446c61f444387e094c779dccd5ca62165
F src/util.c 36fb1150062957280777655976f3f9a75db236cb8207a0770ceae8d5ec17fcd3
F src/vacuum.c 1bacdd0a81d2b5dc1c508fbf0d938c89fa78dd8d5b46ec92686d44030d4f4789
-F src/vdbe.c d2c13c0001f5ec40ec1f010a7f9badcff3ce09bbd7a78528682ad49d8566df54
+F src/vdbe.c e505b8b879a330e8dafbe3ed9582eae2fc671b44a64748d1b58c07e4e0f527da
F src/vdbe.h 93761ed7c6b8bc19524912fd9b9b587d41bf4f1d0ade650a00dadc10518d8958
F src/vdbeInt.h 0bc581a9763be385e3af715e8c0a503ba8422c2b7074922faf4bb0d6ae31b15e
F src/vdbeapi.c f9a4881a9674fec3fa13da35044a1484d3c4b95f9ec891cc8ffb02ef2b7a41df
@@ -867,10 +867,10 @@ F src/vxworks.h d2988f4e5a61a4dfe82c6524dd3d6e4f2ce3cdb9
F src/wal.c 20be6f0a25a80b7897cf2a5369bfd37ef198e6f0b6cdef16d83eee856056b159
F src/wal.h ba252daaa94f889f4b2c17c027e823d9be47ce39da1d3799886bbd51f0490452
F src/walker.c d5006d6b005e4ea7302ad390957a8d41ed83faa177e412f89bc5600a7462a014
-F src/where.c 21b768d47fe28aafc7607a97228c261d7ccdca5cd2bff2221418566ca608e2c5
+F src/where.c 6a9266dd1a559d48d8c7ca670a3e80143c7913153f7d1ceb0a4eca1087318951
F src/whereInt.h 8d94cb116c9e06205c3d5ac87af065fc044f8cf08bfdccd94b6ea1c1308e65da
-F src/wherecode.c 8e375f7c2191e2a797a89ad34fca687509dc10a012877bc07f464b3cf5f21eb7
-F src/whereexpr.c d007dc41364de5902181739632380afd671e14f0c5cc9978e64a2c6df8f28c6c
+F src/wherecode.c 2a2d2993fd98c46f525f71b3bfd330fde73d8613aa0ff3e20402dd1fc63470af
+F src/whereexpr.c 0a7fe115adad30def38aeab6ac1d35fb67782cee92a43df7448136240accd4dd
F src/window.c d01227141f622f24fbe36ca105fbe6ef023f9fd98f1ccd65da95f88886565db5
F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2
F test/affinity2.test 4d7a34d328e58ca2a2d78fd76c27614a41ca7ddf4312ded9c68c04f430b3b47d
@@ -1131,7 +1131,7 @@ F test/enc.test b5503a87b31cea8a5084c6e447383f9ca08933bd2f29d97b6b6201081b2343eb
F test/enc2.test 872afe58db772e7dfa1ad8e0759f8cc820e9efc8172d460fae83023101c2e435
F test/enc3.test 55ef64416d72975c66167310a51dc9fc544ba3ae4858b8d5ab22f4cb6500b087
F test/enc4.test c8f1ce3618508fd0909945beb8b8831feef2c020
-F test/eqp.test 82f221e8cd588434d7f3bba9a0f4c78cbe7a541615a41632e12f50608bfb4a99
+F test/eqp.test 746db9fe11629a0d00328e1721cc2a2e4726d574b677ab14de35fd914f54cc82
F test/eqp2.test 6e8996148de88f0e7670491e92e712a2920a369b4406f21a27c3c9b6a46b68dd
F test/errmsg.test eae9f091eb39ce7e20305de45d8e5d115b68fa856fba4ea6757b6ca3705ff7f9
F test/errofst1.test 6da78363739ba8991f498396ab331b5d64e7ab5c4172c12b5884683ef523ac53
@@ -1140,6 +1140,9 @@ F test/exclusive.test 7ff63be7503990921838d5c9f77f6e33e68e48ed1a9d48cd28745bf650
F test/exclusive2.test cd70b1d9c6fffd336f9795b711dcc5d9ceba133ad3f7001da3fda63615bdc91e
F test/exec.test e949714dc127eaa5ecc7d723efec1ec27118fdd7
F test/exists.test 79a75323c78f02bbe9c251ea502a092f9ef63dac
+F test/existsexpr.test 40ddd9500109579dd949cd15bbb4e3a88f79f905d1f31905b9493651f60aacf6
+F test/existsexpr2.test dc23e76389eff3d29f6488ff733012a3560cd67ec8cfaecbecd52cced5d5af11
+F test/existsfault.test ff41c11f3052c1bbd4f8dd557802310026253d67d7c4e3a180c16d2f0862973e
F test/expr.test 4ada8eb822c45ef27a36851a258004d43c1e95e7c82585a1217e732084e4482c
F test/expr2.test c27327ae9c017a7ff6280123f67aff496f912da74d78c888926d68b46ec75fd8
F test/exprfault.test da33606d799718e2f8e34efd0e5858884a1ad87f608774c552a7f5517cc27181
@@ -1315,7 +1318,7 @@ F test/in7.test d9efdee00b074a60c6343993b2eda78bc369ab080dad864513c73f8aca89d566
F test/incrblob.test c9b96afc292aeff43d6687bcb09b0280aa599822
F test/incrblob2.test a494c9e848560039a23974b9119cfc2cf3ad3bd15cc2694ee6367ae537ef8f1f
F test/incrblob3.test 67621a04b3084113bf38ce03797d70eca012d9d8f948193b8f655df577b0da6f
-F test/incrblob4.test 10f4537febe1774c02f8d490d393322b4a50898d4027868b67055e9c3adc22db
+F test/incrblob4.test a8d6b5ff04055fcec747a50b78485ebf4fcd80074e0b3cedbe952bde346da54a
F test/incrblob_err.test 89372a28f1d98254f03fed705f9efcd34ef61a674df16d2dbb4726944a2de5e9
F test/incrblobfault.test de274b1e329169c2c3438f9528994807ea8201ebf38ae9f157d34bf3ec0cc549
F test/incrcorrupt.test 6c567fbf870aa9e91866fe52ce6f200cd548939a
@@ -1387,7 +1390,7 @@ F test/json/json-generator.tcl dc0dd0f393800c98658fc4c47eaa6af29d4e17527380cd286
F test/json/json-q1.txt 65f9d1cdcc4cffa9823fb73ed936aae5658700cd001fde448f68bfb91c807307
F test/json/json-speed-check.sh 7d5898808ce7542762318306ae6075a30f5e7ee115c4a409f487e123afe91d88 x
F test/json/jsonb-q1.txt 1e180fe6491efab307e318b22879e3a736ac9a96539bbde7911a13ee5b33abc7
-F test/json101.test 8237a484c256965eab1678fd950a32ac56325bb7d0dadbd095a46b0ddd95d62b
+F test/json101.test cf53254f0f0c1399a01b21fc58fee0e63a12a556be91b9ee9faccdb8b82c083c
F test/json102.test 9b2e5ada10845ff84853b3feaae2ce51ce7145ae458f74c6a6cecc6ef6ee3ae1
F test/json103.test 355746a6b66aa438f214b4fae454b13068fad2444b5f693e0d538ad1c059b264
F test/json104.test 1b844a70cddcfa2e4cd81a5db0657b2e61e7f00868310f24f56a9ba0114348c1
@@ -1494,7 +1497,7 @@ F test/notify1.test 669b2b743618efdc18ca4b02f45423d5d2304abf
F test/notify2.test 2ecabaa1305083856b7c39cf32816b612740c161
F test/notify3.test 796c7b7157f55c93b4e672b724e9c923a6fc6aa72ac419379a623e2350472e22
F test/notnull.test a37b663d5bb728d66fc182016613fb8e4a0a4bbf3d75b8876a7527f7d4ed3f18
-F test/notnull2.test 2ac7b4e04917148c7a1a9ed36df20150175ce942f07f5714375b29acbaca7106
+F test/notnull2.test 5b7dd6e82c409b2d011ad6acf19ae4bf0816a9c69ccf600b529d7405d7c49874
F test/notnullfault.test fc4bb7845582a2b3db376001ef49118393b1b11abe0d24adb03db057ee2b73d5
F test/null.test b7ff206a1c60fe01aa2abd33ef9ea83c93727d993ca8a613de86e925c9f2bc6f
F test/nulls1.test 7a5e4346ee4285034100b4cd20e6784f16a9d6c927e44ecdf10034086bbee9c9
@@ -2208,9 +2211,8 @@ F tool/version-info.c 3b36468a90faf1bbd59c65fd0eb66522d9f941eedd364fabccd7227350
F tool/warnings-clang.sh bbf6a1e685e534c92ec2bfba5b1745f34fb6f0bc2a362850723a9ee87c1b31a7
F tool/warnings.sh 1ad0169b022b280bcaaf94a7fa231591be96b514230ab5c98fbf15cd7df842dd
F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f
-P c385475b250f3364507a95c5832137098a9bb7c7fc12ab3bb116e1fad7bb7645 d2adf61f21a3ce901a2b08199ad0de191e88ef16e097c5f7a75c95ad958eff12
-R 82e9c3e3f384036ea2f7751db27154bc
-T +closed d2adf61f21a3ce901a2b08199ad0de191e88ef16e097c5f7a75c95ad958eff12
+P 720387f8604f7cd997f1850ed62ce6ab32608155d7f02a89c695041caafc4067
+R 06654310fbfa0a3e107f5e62dfa928dd
U drh
-Z 1a6c8c788ec8f2b16f1cc6f88559d1db
+Z d3919178e62d3c6ffc666ec27b2d4fc8
# Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index b06de7908..bedccbe89 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-28db0d152d90fb5e62d03ea5caceabe8901be98522aef3dc2b54564fbc35355d
+498ee8d514e64cdc93a8d68e1971b6326c6132daf25067936bec921c42494caa
diff --git a/src/btree.c b/src/btree.c
index 00cdc9760..a931b0d12 100644
--- a/src/btree.c
+++ b/src/btree.c
@@ -5667,6 +5667,30 @@ int sqlite3BtreeFirst(BtCursor *pCur, int *pRes){
return rc;
}
+/* Set *pRes to 1 (true) if the BTree pointed to by cursor pCur contains zero
+** rows of content. Set *pRes to 0 (false) if the table contains content.
+** Return SQLITE_OK on success or some error code (ex: SQLITE_NOMEM) if
+** something goes wrong.
+*/
+int sqlite3BtreeIsEmpty(BtCursor *pCur, int *pRes){
+ int rc;
+
+ assert( cursorOwnsBtShared(pCur) );
+ assert( sqlite3_mutex_held(pCur->pBtree->db->mutex) );
+ if( pCur->eState==CURSOR_VALID ){
+ *pRes = 0;
+ return SQLITE_OK;
+ }
+ rc = moveToRoot(pCur);
+ if( rc==SQLITE_EMPTY ){
+ *pRes = 1;
+ rc = SQLITE_OK;
+ }else{
+ *pRes = 0;
+ }
+ return rc;
+}
+
#ifdef SQLITE_DEBUG
/* The cursors is CURSOR_VALID and has BTCF_AtLast set. Verify that
** this flags are true for a consistent database.
diff --git a/src/btree.h b/src/btree.h
index 241261dc6..96f4c4c60 100644
--- a/src/btree.h
+++ b/src/btree.h
@@ -317,6 +317,7 @@ struct BtreePayload {
int sqlite3BtreeInsert(BtCursor*, const BtreePayload *pPayload,
int flags, int seekResult);
int sqlite3BtreeFirst(BtCursor*, int *pRes);
+int sqlite3BtreeIsEmpty(BtCursor *pCur, int *pRes);
int sqlite3BtreeLast(BtCursor*, int *pRes);
int sqlite3BtreeNext(BtCursor*, int flags);
int sqlite3BtreeEof(BtCursor*);
diff --git a/src/build.c b/src/build.c
index 27d7b499d..5495cef18 100644
--- a/src/build.c
+++ b/src/build.c
@@ -5137,16 +5137,22 @@ void sqlite3SrcListIndexedBy(Parse *pParse, SrcList *p, Token *pIndexedBy){
** are deleted by this function.
*/
SrcList *sqlite3SrcListAppendList(Parse *pParse, SrcList *p1, SrcList *p2){
- assert( p1 && p1->nSrc==1 );
+ assert( p1 );
+ assert( p2 || pParse->nErr );
+ assert( p2==0 || p2->nSrc>=1 );
+ testcase( p1->nSrc==0 );
if( p2 ){
- SrcList *pNew = sqlite3SrcListEnlarge(pParse, p1, p2->nSrc, 1);
+ int nOld = p1->nSrc;
+ SrcList *pNew = sqlite3SrcListEnlarge(pParse, p1, p2->nSrc, nOld);
if( pNew==0 ){
sqlite3SrcListDelete(pParse->db, p2);
}else{
p1 = pNew;
- memcpy(&p1->a[1], p2->a, p2->nSrc*sizeof(SrcItem));
+ memcpy(&p1->a[nOld], p2->a, p2->nSrc*sizeof(SrcItem));
+ assert( nOld==1 || (p2->a[0].fg.jointype & JT_LTORJ)==0 );
+ assert( p1->nSrc>=1 );
+ p1->a[0].fg.jointype |= (JT_LTORJ & p2->a[0].fg.jointype);
sqlite3DbFree(pParse->db, p2);
- p1->a[0].fg.jointype |= (JT_LTORJ & p1->a[1].fg.jointype);
}
}
return p1;
diff --git a/src/resolve.c b/src/resolve.c
index 3961a2009..57ccd0c07 100644
--- a/src/resolve.c
+++ b/src/resolve.c
@@ -1358,11 +1358,13 @@ static int resolveExprStep(Walker *pWalker, Expr *pExpr){
return WRC_Prune;
}
#ifndef SQLITE_OMIT_SUBQUERY
+ case TK_EXISTS:
case TK_SELECT:
- case TK_EXISTS: testcase( pExpr->op==TK_EXISTS );
#endif
case TK_IN: {
testcase( pExpr->op==TK_IN );
+ testcase( pExpr->op==TK_EXISTS );
+ testcase( pExpr->op==TK_SELECT );
if( ExprUseXSelect(pExpr) ){
int nRef = pNC->nRef;
testcase( pNC->ncFlags & NC_IsCheck );
@@ -1370,6 +1372,7 @@ static int resolveExprStep(Walker *pWalker, Expr *pExpr){
testcase( pNC->ncFlags & NC_IdxExpr );
testcase( pNC->ncFlags & NC_GenCol );
assert( pExpr->x.pSelect );
+ if( pExpr->op==TK_EXISTS ) pParse->bHasExists = 1;
if( pNC->ncFlags & NC_SelfRef ){
notValidImpl(pParse, pNC, "subqueries", pExpr, pExpr);
}else{
diff --git a/src/select.c b/src/select.c
index 1e54747fc..99b05c76a 100644
--- a/src/select.c
+++ b/src/select.c
@@ -384,7 +384,7 @@ static int tableAndColumnIndex(
int iEnd, /* Last member of pSrc->a[] to check */
const char *zCol, /* Name of the column we are looking for */
int *piTab, /* Write index of pSrc->a[] here */
- int *piCol, /* Write index of pSrc->a[*piTab].pTab->aCol[] here */
+ int *piCol, /* Write index of pSrc->a[*piTab].pSTab->aCol[] here */
int bIgnoreHidden /* Ignore hidden columns */
){
int i; /* For looping over tables in pSrc */
@@ -3036,7 +3036,9 @@ static int multiSelect(
int priorOp; /* The SRT_ operation to apply to prior selects */
Expr *pLimit; /* Saved values of p->nLimit */
int addr;
+ int emptyBypass = 0; /* IfEmpty opcode to bypass RHS */
SelectDest uniondest;
+
testcase( p->op==TK_EXCEPT );
testcase( p->op==TK_UNION );
@@ -3075,6 +3077,8 @@ static int multiSelect(
*/
if( p->op==TK_EXCEPT ){
op = SRT_Except;
+ emptyBypass = sqlite3VdbeAddOp1(v, OP_IfEmpty, unionTab);
+ VdbeCoverage(v);
}else{
assert( p->op==TK_UNION );
op = SRT_Union;
@@ -3095,6 +3099,7 @@ static int multiSelect(
if( p->op==TK_UNION ){
p->nSelectRow = sqlite3LogEstAdd(p->nSelectRow, pPrior->nSelectRow);
}
+ if( emptyBypass ) sqlite3VdbeJumpHere(v, emptyBypass);
sqlite3ExprDelete(db, p->pLimit);
p->pLimit = pLimit;
p->iLimit = 0;
@@ -3128,6 +3133,7 @@ static int multiSelect(
int addr;
SelectDest intersectdest;
int r1;
+ int emptyBypass;
/* INTERSECT is different from the others since it requires
** two temporary tables. Hence it has its own case. Begin
@@ -3151,6 +3157,7 @@ static int multiSelect(
if( rc ){
goto multi_select_end;
}
+ emptyBypass = sqlite3VdbeAddOp1(v, OP_IfEmpty, tab1); VdbeCoverage(v);
/* Code the current SELECT into temporary table "tab2"
*/
@@ -3182,7 +3189,7 @@ static int multiSelect(
iBreak = sqlite3VdbeMakeLabel(pParse);
iCont = sqlite3VdbeMakeLabel(pParse);
computeLimitRegisters(pParse, p, iBreak);
- sqlite3VdbeAddOp2(v, OP_Rewind, tab1, iBreak); VdbeCoverage(v);
+ sqlite3VdbeAddOp1(v, OP_Rewind, tab1);
r1 = sqlite3GetTempReg(pParse);
iStart = sqlite3VdbeAddOp2(v, OP_RowData, tab1, r1);
sqlite3VdbeAddOp4Int(v, OP_NotFound, tab2, iCont, r1, 0);
@@ -3194,6 +3201,7 @@ static int multiSelect(
sqlite3VdbeAddOp2(v, OP_Next, tab1, iStart); VdbeCoverage(v);
sqlite3VdbeResolveLabel(v, iBreak);
sqlite3VdbeAddOp2(v, OP_Close, tab2, 0);
+ sqlite3VdbeJumpHere(v, emptyBypass);
sqlite3VdbeAddOp2(v, OP_Close, tab1, 0);
break;
}
@@ -4650,7 +4658,7 @@ static int flattenSubquery(
** complete, since there may still exist Expr.pTab entries that
** refer to the subquery even after flattening. Ticket #3346.
**
- ** pSubitem->pTab is always non-NULL by test restrictions and tests above.
+ ** pSubitem->pSTab is always non-NULL by test restrictions and tests above.
*/
if( ALWAYS(pSubitem->pSTab!=0) ){
Table *pTabToDel = pSubitem->pSTab;
@@ -5764,7 +5772,7 @@ With *sqlite3WithPush(Parse *pParse, With *pWith, u8 bFree){
** CTE expression, through routine checks to see if the reference is
** a recursive reference to the CTE.
**
-** If pFrom matches a CTE according to either of these two above, pFrom->pTab
+** If pFrom matches a CTE according to either of these two above, pFrom->pSTab
** and other fields are populated accordingly.
**
** Return 0 if no match is found.
@@ -7392,6 +7400,83 @@ static int fromClauseTermCanBeCoroutine(
}
/*
+** Argument pWhere is the WHERE clause belonging to SELECT statement p. This
+** function attempts to transform expressions of the form:
+**
+** EXISTS (SELECT ...)
+**
+** into joins. For example, given
+**
+** CREATE TABLE sailors(sid INTEGER PRIMARY KEY, name TEXT);
+** CREATE TABLE reserves(sid INT, day DATE, PRIMARY KEY(sid, day));
+**
+** SELECT name FROM sailors AS S WHERE EXISTS (
+** SELECT * FROM reserves AS R WHERE S.sid = R.sid AND R.day = '2022-10-25'
+** );
+**
+** the SELECT statement may be transformed as follows:
+**
+** SELECT name FROM sailors AS S, reserves AS R
+** WHERE S.sid = R.sid AND R.day = '2022-10-25';
+**
+** **Approximately**. Really, we have to ensure that the FROM-clause term
+** that was formerly inside the EXISTS is only executed once. This is handled
+** by setting the SrcItem.fg.fromExists flag, which then causes code in
+** the where.c file to exit the corresponding loop after the first successful
+** match (if any).
+*/
+static SQLITE_NOINLINE void existsToJoin(
+ Parse *pParse, /* Parsing context */
+ Select *p, /* The SELECT statement being optimized */
+ Expr *pWhere /* part of the WHERE clause currently being examined */
+){
+ if( pParse->nErr==0
+ && pWhere!=0
+ && !ExprHasProperty(pWhere, EP_OuterON|EP_InnerON)
+ && ALWAYS(p->pSrc!=0)
+ && p->pSrc->nSrc<BMS
+ ){
+ if( pWhere->op==TK_AND ){
+ Expr *pRight = pWhere->pRight;
+ existsToJoin(pParse, p, pWhere->pLeft);
+ existsToJoin(pParse, p, pRight);
+ }
+ else if( pWhere->op==TK_EXISTS ){
+ Select *pSub = pWhere->x.pSelect;
+ Expr *pSubWhere = pSub->pWhere;
+ if( pSub->pSrc->nSrc==1
+ && (pSub->selFlags & SF_Aggregate)==0
+ && !pSub->pSrc->a[0].fg.isSubquery
+ ){
+ memset(pWhere, 0, sizeof(*pWhere));
+ pWhere->op = TK_INTEGER;
+ pWhere->u.iValue = 1;
+ ExprSetProperty(pWhere, EP_IntValue);
+
+ assert( p->pWhere!=0 );
+ pSub->pSrc->a[0].fg.fromExists = 1;
+ pSub->pSrc->a[0].fg.jointype |= JT_CROSS;
+ p->pSrc = sqlite3SrcListAppendList(pParse, p->pSrc, pSub->pSrc);
+ if( pSubWhere ){
+ p->pWhere = sqlite3PExpr(pParse, TK_AND, p->pWhere, pSubWhere);
+ pSub->pWhere = 0;
+ }
+ pSub->pSrc = 0;
+ sqlite3ParserAddCleanup(pParse, sqlite3SelectDeleteGeneric, pSub);
+#if TREETRACE_ENABLED
+ if( sqlite3TreeTrace & 0x100000 ){
+ TREETRACE(0x100000,pParse,p,
+ ("After EXISTS-to-JOIN optimization:\n"));
+ sqlite3TreeViewSelect(0, p, 0);
+ }
+#endif
+ existsToJoin(pParse, p, pSubWhere);
+ }
+ }
+ }
+}
+
+/*
** Generate byte-code for the SELECT statement given in the p argument.
**
** The results are returned according to the SelectDest structure.
@@ -7759,6 +7844,13 @@ int sqlite3Select(
}
#endif
+ /* If there may be an "EXISTS (SELECT ...)" in the WHERE clause, attempt
+ ** to change it into a join. */
+ if( pParse->bHasExists && OptimizationEnabled(db,SQLITE_ExistsToJoin) ){
+ existsToJoin(pParse, p, p->pWhere);
+ pTabList = p->pSrc;
+ }
+
/* Do the WHERE-clause constant propagation optimization if this is
** a join. No need to spend time on this operation for non-join queries
** as the equivalent optimization will be handled by query planner in
diff --git a/src/shell.c.in b/src/shell.c.in
index 33dd30697..5cda6a1a1 100644
--- a/src/shell.c.in
+++ b/src/shell.c.in
@@ -11710,6 +11710,7 @@ static int do_meta_command(char *zLine, ShellState *p){
{ 0x08000000, 1, "OnePass" },
{ 0x10000000, 1, "OrderBySubq" },
{ 0x20000000, 1, "StarQuery" },
+ { 0x40000000, 1, "ExistsToJoin" },
{ 0xffffffff, 0, "All" },
};
unsigned int curOpt;
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index a05cf75ad..7b914d958 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -1154,6 +1154,7 @@ extern u32 sqlite3TreeTrace;
** 0x00040000 SELECT tree dump after all code has been generated
** 0x00080000 NOT NULL strength reduction
** 0x00100000 Pointers are all shown as zero
+** 0x00200000 EXISTS-to-JOIN optimization
*/
/*
@@ -1926,6 +1927,7 @@ struct sqlite3 {
#define SQLITE_OnePass 0x08000000 /* Single-pass DELETE and UPDATE */
#define SQLITE_OrderBySubq 0x10000000 /* ORDER BY in subquery helps outer */
#define SQLITE_StarQuery 0x20000000 /* Heurists for star queries */
+#define SQLITE_ExistsToJoin 0x40000000 /* The EXISTS-to-JOIN optimization */
#define SQLITE_AllOpts 0xffffffff /* All optimizations */
/*
@@ -3369,6 +3371,7 @@ struct SrcItem {
unsigned rowidUsed :1; /* The ROWID of this table is referenced */
unsigned fixedSchema :1; /* Uses u4.pSchema, not u4.zDatabase */
unsigned hadSchema :1; /* Had u4.zDatabase before u4.pSchema */
+ unsigned fromExists :1; /* Comes from WHERE EXISTS(...) */
} fg;
int iCursor; /* The VDBE cursor number used to access this table */
Bitmask colUsed; /* Bit N set if column N used. Details above for N>62 */
@@ -3899,6 +3902,7 @@ struct Parse {
u8 disableLookaside; /* Number of times lookaside has been disabled */
u8 prepFlags; /* SQLITE_PREPARE_* flags */
u8 withinRJSubrtn; /* Nesting level for RIGHT JOIN body subroutines */
+ u8 bHasExists; /* Has a correlated "EXISTS (SELECT ....)" expression */
u8 mSubrtnSig; /* mini Bloom filter on available SubrtnSig.selId */
u8 eTriggerOp; /* TK_UPDATE, TK_INSERT or TK_DELETE */
u8 bReturning; /* Coding a RETURNING trigger */
diff --git a/src/vdbe.c b/src/vdbe.c
index 0020b52bf..9e456a1cd 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -6398,6 +6398,32 @@ case OP_Rewind: { /* jump0, ncycle */
break;
}
+/* Opcode: IfEmpty P1 P2 * * *
+** Synopsis: if( empty(P1) ) goto P2
+**
+** Check to see if the b-tree table that cursor P1 references is empty
+** and jump to P2 if it is.
+*/
+case OP_IfEmpty: { /* jump */
+ VdbeCursor *pC;
+ BtCursor *pCrsr;
+ int res;
+
+ assert( pOp->p1>=0 && pOp->p1<p->nCursor );
+ assert( pOp->p2>=0 && pOp->p2<p->nOp );
+
+ pC = p->apCsr[pOp->p1];
+ assert( pC!=0 );
+ assert( pC->eCurType==CURTYPE_BTREE );
+ pCrsr = pC->uc.pCursor;
+ assert( pCrsr );
+ rc = sqlite3BtreeIsEmpty(pCrsr, &res);
+ if( rc ) goto abort_due_to_error;
+ VdbeBranchTaken(res!=0,2);
+ if( res ) goto jump_to_p2;
+ break;
+}
+
/* Opcode: Next P1 P2 P3 * P5
**
** Advance cursor P1 so that it points to the next key/data pair in its
diff --git a/src/where.c b/src/where.c
index 5d80dd3d6..ab1b419a2 100644
--- a/src/where.c
+++ b/src/where.c
@@ -3532,6 +3532,7 @@ static int whereLoopAddBtreeIndex(
&& pProbe->hasStat1!=0
&& OptimizationEnabled(db, SQLITE_SkipScan)
&& pProbe->aiRowLogEst[saved_nEq+1]>=42 /* TUNING: Minimum for skip-scan */
+ && pSrc->fg.fromExists==0
&& (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
){
LogEst nIter;
@@ -7148,6 +7149,13 @@ WhereInfo *sqlite3WhereBegin(
sqlite3VdbeAddOp4Dup8(v, OP_ColumnsUsed, pTabItem->iCursor, 0, 0,
(const u8*)&pTabItem->colUsed, P4_INT64);
#endif
+ if( ii>=2
+ && (pTabItem[0].fg.jointype & (JT_LTORJ|JT_LEFT))==0
+ && pLevel->addrHalt==pWInfo->a[0].addrHalt
+ ){
+ sqlite3VdbeAddOp2(v, OP_IfEmpty, pTabItem->iCursor, pWInfo->iBreak);
+ VdbeCoverage(v);
+ }
}else{
sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
}
@@ -7404,6 +7412,9 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){
sqlite3VdbeAddOp2(v, OP_Goto, 1, pLevel->p2);
}
#endif /* SQLITE_DISABLE_SKIPAHEAD_DISTINCT */
+ if( pTabList->a[pLevel->iFrom].fg.fromExists ){
+ sqlite3VdbeAddOp2(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+2);
+ }
/* The common case: Advance to the next row */
if( pLevel->addrCont ) sqlite3VdbeResolveLabel(v, pLevel->addrCont);
sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
diff --git a/src/wherecode.c b/src/wherecode.c
index 839304c11..43a669d81 100644
--- a/src/wherecode.c
+++ b/src/wherecode.c
@@ -126,7 +126,6 @@ void sqlite3WhereAddExplainText(
#endif
{
VdbeOp *pOp = sqlite3VdbeGetOp(pParse->pVdbe, addr);
-
SrcItem *pItem = &pTabList->a[pLevel->iFrom];
sqlite3 *db = pParse->db; /* Database handle */
int isSearch; /* True for a SEARCH. False for SCAN. */
@@ -149,7 +148,10 @@ void sqlite3WhereAddExplainText(
sqlite3StrAccumInit(&str, db, zBuf, sizeof(zBuf), SQLITE_MAX_LENGTH);
str.printfFlags = SQLITE_PRINTF_INTERNAL;
- sqlite3_str_appendf(&str, "%s %S", isSearch ? "SEARCH" : "SCAN", pItem);
+ sqlite3_str_appendf(&str, "%s %S%s",
+ isSearch ? "SEARCH" : "SCAN",
+ pItem,
+ pItem->fg.fromExists ? " EXISTS" : "");
if( (flags & (WHERE_IPK|WHERE_VIRTUALTABLE))==0 ){
const char *zFmt = 0;
Index *pIdx;
diff --git a/src/whereexpr.c b/src/whereexpr.c
index e4be8d9d6..e9fa4a143 100644
--- a/src/whereexpr.c
+++ b/src/whereexpr.c
@@ -948,7 +948,7 @@ static int termIsEquivalence(Parse *pParse, Expr *pExpr, SrcList *pSrc){
if( ExprHasProperty(pExpr, EP_OuterON) ) return 0; /* (3) */
assert( pSrc!=0 );
if( pExpr->op==TK_IS
- && pSrc->nSrc
+ && pSrc->nSrc>=2
&& (pSrc->a[0].fg.jointype & JT_LTORJ)!=0
){
return 0; /* (4) */
diff --git a/test/eqp.test b/test/eqp.test
index 5d2659be7..147b5ceaf 100644
--- a/test/eqp.test
+++ b/test/eqp.test
@@ -338,8 +338,7 @@ det 3.3.3 {
} {
QUERY PLAN
|--SCAN t1
- `--CORRELATED SCALAR SUBQUERY xxxxxx
- `--SCAN t2
+ `--SCAN t2 EXISTS
}
#-------------------------------------------------------------------------
diff --git a/test/existsexpr.test b/test/existsexpr.test
new file mode 100644
index 000000000..2bf2e8223
--- /dev/null
+++ b/test/existsexpr.test
@@ -0,0 +1,426 @@
+# 2024 May 25
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+source $testdir/lock_common.tcl
+set testprefix existsexpr
+
+
+do_execsql_test 1.0 {
+ CREATE TABLE x1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
+ INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6);
+ CREATE INDEX x1b ON x1(b);
+
+ CREATE TABLE x2(x, y);
+ INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6);
+}
+
+do_execsql_test 1.1 {
+ SELECT 1 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=5)
+} {1}
+
+do_execsql_test 1.2 {
+ SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)
+} {1 2 3 4 5 6}
+
+# With "a=x", the UNIQUE index means the EXIST can be transformed to a join.
+# So no "SUBQUERY". With "b=x", the index is not UNIQUE and so there is a
+# "SUBQUERY".
+do_execsql_test 1.3.1 {
+ EXPLAIN QUERY PLAN
+ SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)
+} {~/SUBQUERY/}
+do_execsql_test 1.3.2 {
+ EXPLAIN QUERY PLAN
+ SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE b=x)
+} {~/SUBQUERY/}
+
+do_execsql_test 1.4.1 {
+ EXPLAIN QUERY PLAN
+ SELECT * FROM x2 WHERE x=1 AND EXISTS (SELECT 1 FROM x1 WHERE a=x)
+} {~/SUBQUERY/}
+do_execsql_test 1.4.2 {
+ EXPLAIN QUERY PLAN
+ SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x) AND y=2
+} {~/SUBQUERY/}
+
+do_execsql_test 1.5 {
+ SELECT count(*) FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)
+} {3}
+
+#-------------------------------------------------------------------------
+do_execsql_test 2.0 {
+ CREATE TABLE t1(a, b);
+ WITH s(i) AS (
+ SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000
+ ) INSERT INTO t1 SELECT i, i FROM s;
+
+ CREATE TABLE t2(c, d);
+ WITH s(i) AS (
+ SELECT 10 UNION ALL SELECT i+10 FROM s WHERE i<1000
+ ) INSERT INTO t2 SELECT i, i FROM s;
+}
+
+do_execsql_test 2.1 {
+ SELECT count(*) FROM t1;
+ SELECT count(*) FROM t2;
+} {1000 100}
+
+do_execsql_test 2.2 {
+ SELECT count(*) FROM t1, t2 WHERE a=c;
+} {100}
+
+do_execsql_test 2.3 {
+ SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a)
+} {100}
+do_eqp_test 2.4 {
+ SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a)
+} {SCAN t1}
+
+do_execsql_test 2.4.0 {
+ CREATE UNIQUE INDEX t2c ON t2(c);
+ CREATE UNIQUE INDEX t1a ON t1(a);
+}
+
+do_eqp_test 2.4.1 {
+ SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);
+} {SCAN t1*t2 EXISTS}
+do_execsql_test 2.4.2 {
+ ANALYZE;
+}
+do_eqp_test 2.4.3 {
+ SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);
+} {SCAN t1*t2 EXISTS}
+do_execsql_test 2.4.4 {
+ SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);
+} {100}
+
+do_execsql_test 2.5.1 {
+ EXPLAIN QUERY PLAN
+ SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.rowid=a);
+} {~/SUBQUERY/}
+
+#-------------------------------------------------------------------------
+proc do_subquery_test {tn bSub sql res} {
+ set r1(0) ~/SUBQUERY/
+ set r1(1) /SUBQUERY/
+ do_execsql_test $tn.1 "explain query plan $sql" $r1($bSub)
+ do_execsql_test $tn.2 $sql $res
+}
+
+do_execsql_test 3.0 {
+ CREATE TABLE y1(a, b, c);
+ CREATE TABLE y2(x, y, z);
+ CREATE UNIQUE INDEX y2zy ON y2(z, y);
+
+ INSERT INTO y1 VALUES(1, 1, 1);
+ INSERT INTO y1 VALUES(2, 2, 2);
+ INSERT INTO y1 VALUES(3, 3, 3);
+ INSERT INTO y1 VALUES(4, 4, 4);
+
+ INSERT INTO y2 VALUES(1, 1, 1);
+ INSERT INTO y2 VALUES(3, 3, 3);
+}
+
+do_subquery_test 3.1 0 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT 1 FROM y2 WHERE z=a AND y=b AND x=z
+ )
+} {
+ 1 1 1 3 3 3
+}
+
+do_subquery_test 3.2 0 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT 1 FROM y2 WHERE z=max(a,b) AND y=min(b,a) AND x=z
+ )
+} {
+ 1 1 1 3 3 3
+}
+
+do_subquery_test 3.3 0 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT 1 FROM y2 WHERE z=max(a,b) AND y=min(b,a) AND c!=3
+ )
+} {
+ 1 1 1
+}
+
+do_subquery_test 3.4 0 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT 1 FROM y2 WHERE z=max(a,b) AND b=3
+ )
+} {
+ 3 3 3
+}
+
+do_subquery_test 3.5 0 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT 1 FROM y2 WHERE z=a-1 AND y=a-1
+ )
+} {
+ 2 2 2
+ 4 4 4
+}
+
+do_subquery_test 3.6 0 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT 1 FROM y2 WHERE z=a-1 AND y+1=a
+ )
+} {
+ 2 2 2
+ 4 4 4
+}
+
+do_subquery_test 3.7 1 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT count(*) FROM y2 WHERE z=a-1 AND y=a-1
+ )
+} {
+ 1 1 1
+ 2 2 2
+ 3 3 3
+ 4 4 4
+}
+
+do_subquery_test 3.8 0 {
+ SELECT * FROM y1 WHERE EXISTS ( SELECT a+1 FROM y2 )
+} {
+ 1 1 1
+ 2 2 2
+ 3 3 3
+ 4 4 4
+}
+
+do_subquery_test 3.9 1 {
+ SELECT * FROM y1 WHERE EXISTS (
+ SELECT 1 FROM y2 one, y2 two WHERE one.z=a-1 AND one.y=a-1
+ )
+} {
+ 2 2 2
+ 4 4 4
+}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 4.0 {
+ CREATE TABLE tx1(a TEXT COLLATE nocase, b TEXT);
+ CREATE UNIQUE INDEX tx1ab ON tx1(a, b);
+
+ INSERT INTO tx1 VALUES('a', 'a');
+ INSERT INTO tx1 VALUES('B', 'b');
+ INSERT INTO tx1 VALUES('c', 'c');
+ INSERT INTO tx1 VALUES('D', 'd');
+ INSERT INTO tx1 VALUES('e', 'e');
+
+ CREATE TABLE tx2(x, y);
+ INSERT INTO tx2 VALUES('A', 'a');
+ INSERT INTO tx2 VALUES('b', 'b');
+ INSERT INTO tx2 VALUES('C', 'c');
+ INSERT INTO tx2 VALUES('D', 'd');
+}
+
+do_subquery_test 4.1 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE a=x AND b=y
+ )
+} {
+ A a
+ b b
+ C c
+ D d
+}
+
+do_subquery_test 4.1.1 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE (a COLLATE nocase)=x AND b=y
+ )
+} {
+ A a b b C c D d
+}
+do_subquery_test 4.1.2 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE a=x AND (b COLLATE binary)=y
+ )
+} {
+ A a b b C c D d
+}
+do_subquery_test 4.1.1 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE x=(a COLLATE nocase) AND b=y
+ )
+} {
+ A a b b C c D d
+}
+do_subquery_test 4.1.2 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE a=x AND y=(b COLLATE binary)
+ )
+} {
+ A a b b C c D d
+}
+
+do_subquery_test 4.2 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE a=x AND b=y COLLATE nocase
+ )
+} {
+ A a
+ b b
+ C c
+ D d
+}
+
+do_execsql_test 4.3 {
+ DROP INDEX tx1ab;
+ CREATE UNIQUE INDEX tx1ab ON tx1(a COLLATE binary, b);
+}
+
+do_subquery_test 4.4 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE a=x AND b=y
+ )
+} {
+ A a
+ b b
+ C c
+ D d
+}
+
+do_subquery_test 4.4 0 {
+ SELECT * FROM tx2 WHERE EXISTS (
+ SELECT 1 FROM tx1 WHERE a=x COLLATE binary AND b=y
+ )
+} {
+ D d
+}
+
+do_subquery_test 4.4 1 {
+ SELECT EXISTS ( SELECT x FROM tx1 ) FROM tx2
+} {
+ 1 1 1 1
+}
+
+do_subquery_test 4.4 1 {
+ SELECT (SELECT EXISTS ( SELECT x FROM tx1 ) WHERE 1) FROM tx2
+} {
+ 1 1 1 1
+}
+
+#-------------------------------------------------------------------------
+proc cols {s f} {
+ set lCols [list]
+ for {set i $s} {$i<=$f} {incr i} {
+ lappend lCols [format "c%02d" $i]
+ }
+ join $lCols ", "
+}
+proc vals {n val} {
+ set lVal [list]
+ for {set i 0} {$i<$n} {incr i} {
+ lappend lVal $val
+ }
+ join $lVal ", "
+}
+proc exprs {s f} {
+ set lExpr [list]
+ for {set i $s} {$i<=$f} {incr i} {
+ lappend lExpr [format "c%02d = o" $i]
+ }
+ join $lExpr " AND "
+}
+
+
+do_execsql_test 5.0 "
+ CREATE TABLE a1( [cols 0 99] );
+"
+do_execsql_test 5.1 "
+ -- 63 column index
+ CREATE UNIQUE INDEX a1idx1 ON a1( [cols 0 62] );
+"
+do_execsql_test 5.2 "
+ -- 64 column index
+ CREATE UNIQUE INDEX a1idx2 ON a1( [cols 10 73] );
+"
+do_execsql_test 5.2 "
+ -- 65 column index
+ CREATE UNIQUE INDEX a1idx3 ON a1( [cols 20 84] );
+"
+
+do_test 5.3 {
+ foreach v {1 2 3 4 5 6} {
+ execsql "INSERT INTO a1 VALUES( [vals 100 $v] )"
+ }
+} {}
+
+do_execsql_test 5.4 {
+ CREATE TABLE a2(o);
+ INSERT INTO a2 VALUES(2), (5);
+}
+
+do_subquery_test 5.5 0 "
+ SELECT o FROM a2 WHERE EXISTS (
+ SELECT 1 FROM a1 WHERE [exprs 0 62]
+ )
+" {
+ 2 5
+}
+
+do_subquery_test 5.6 0 "
+ SELECT o FROM a2 WHERE EXISTS (
+ SELECT 1 FROM a1 WHERE [exprs 10 73]
+ )
+" {
+ 2 5
+}
+
+do_subquery_test 5.7 0 "
+ SELECT o FROM a2 WHERE EXISTS (
+ SELECT 1 FROM a1 WHERE [exprs 20 84]
+ )
+" {
+ 2 5
+}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 6.0 {
+ CREATE TABLE t1(a, b UNIQUE, c UNIQUE);
+ CREATE TABLE t2(a INfEGER PRIMARY KEY, b);
+ CREATE UNIQUE INDEX t2b ON t2(b);
+}
+
+do_catchsql_test 6.1 {
+ SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c COLLATE f = a)
+} {1 {no such collation sequence: f}}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test 7.0 {
+ CREATE TABLE t1(x);
+ CREATE TABLE t2(y UNIQUE);
+
+ INSERT INTO t1 VALUES(1), (2);
+ INSERT INTO t2 VALUES(1), (3);
+
+ SELECT * FROM t1 one LEFT JOIN t1 two ON (one.x=two.x AND EXISTS (
+ SELECT 1 FROM t2 WHERE y=one.x
+ ));
+} {
+ 1 1
+ 2 {}
+}
+
+
+
+finish_test
diff --git a/test/existsexpr2.test b/test/existsexpr2.test
new file mode 100644
index 000000000..f7644bf80
--- /dev/null
+++ b/test/existsexpr2.test
@@ -0,0 +1,96 @@
+# 2024 June 14
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+source $testdir/lock_common.tcl
+set testprefix existsexpr2
+
+
+do_execsql_test 1.0 {
+ CREATE TABLE x1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
+ INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6);
+ CREATE INDEX x1b ON x1(b);
+
+ CREATE TABLE x2(x, y);
+ INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6);
+}
+
+do_execsql_test 1.1 {
+ SELECT * FROM x1 WHERE EXISTS (SELECT 1 FROM x2 WHERE a!=123)
+} {1 2 3 4 5 6}
+
+do_execsql_test 1.2 {
+ CREATE TABLE x3(u, v);
+ CREATE INDEX x3u ON x3(u);
+ INSERT INTO x3 VALUES
+ (1, 1), (1, 2), (1, 3),
+ (2, 1), (2, 2), (2, 3);
+}
+
+do_execsql_test 1.3 {
+ SELECT * FROM x1 WHERE EXISTS (
+ SELECT 1 FROM x3 WHERE u IN (1, 2, 3, 4) AND v=b
+ );
+} {
+ 1 2
+}
+
+#-------------------------------------------------------------------------
+#
+reset_db
+do_execsql_test 2.0 {
+ CREATE TABLE t1(a, b, c);
+ CREATE INDEX t1ab ON t1(a,b);
+
+ INSERT INTO t1 VALUES
+ ('abc', 1, 1),
+ ('abc', 2, 2),
+ ('abc', 2, 3),
+
+ ('def', 1, 1),
+ ('def', 2, 2),
+ ('def', 2, 3);
+
+ CREATE TABLE t2(x, y);
+ INSERT INTO t2 VALUES(1, 1), (2, 2), (3, 3);
+
+ ANALYZE;
+ DELETE FROM sqlite_stat1;
+ INSERT INTO sqlite_stat1 VALUES('t1','t1ab','10000 5000 2');
+ ANALYZE sqlite_master;
+}
+
+
+do_execsql_test 2.1 {
+ SELECT a,b,c FROM t1 WHERE b=2 ORDER BY a
+} {
+ abc 2 2
+ abc 2 3
+ def 2 2
+ def 2 3
+}
+
+do_execsql_test 2.2 {
+ SELECT x, y FROM t2 WHERE EXISTS (
+ SELECT 1 FROM t1 WHERE b=x
+ )
+} {
+ 1 1
+ 2 2
+}
+
+
+
+finish_test
+
+
diff --git a/test/existsfault.test b/test/existsfault.test
new file mode 100644
index 000000000..4b335d84c
--- /dev/null
+++ b/test/existsfault.test
@@ -0,0 +1,49 @@
+# 2024 May 25
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+source $testdir/lock_common.tcl
+source $testdir/malloc_common.tcl
+set testprefix existsfault
+
+db close
+sqlite3_shutdown
+sqlite3_config_lookaside 0 0
+sqlite3_initialize
+autoinstall_test_functions
+sqlite3 db test.db
+
+do_execsql_test 1.0 {
+ CREATE TABLE x1(a, b);
+ INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6);
+ CREATE UNIQUE INDEX x1a ON x1(a);
+ CREATE INDEX x1b ON x1(b);
+
+ CREATE TABLE x2(x, y);
+ INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6);
+}
+
+do_faultsim_test 1 -faults oom* -prep {
+ sqlite3 db test.db
+ execsql { SELECT * FROM sqlite_schema }
+} -body {
+ execsql {
+ SELECT count(*) FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x) AND y!=11
+ }
+} -test {
+ faultsim_test_result {0 3}
+}
+
+finish_test
+
+
diff --git a/test/incrblob4.test b/test/incrblob4.test
index 31040e91b..c9bcee8a3 100644
--- a/test/incrblob4.test
+++ b/test/incrblob4.test
@@ -108,6 +108,8 @@ close $blob
#-------------------------------------------------------------------------
+ifcapable preupdate {
+
reset_db
do_execsql_test 5.1 {
CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
@@ -127,7 +129,8 @@ do_test 5.2.1 {
do_test 5.2.2 {
puts -nonewline $blob "world"
- list [catch { flush $blob } msg] $msg
+ set rc [catch { flush $blob } msg]
+ list $rc [regsub {input/output} $msg {I/O}]
} "1 {error flushing \"$blob\": I/O error}"
catch { close $blob }
@@ -147,7 +150,8 @@ do_test 5.3.1 {
do_test 5.3.2 {
puts -nonewline $blob "world"
- list [catch { flush $blob } msg] $msg
+ set rc [catch { flush $blob } msg]
+ list $rc [regsub {input/output} $msg {I/O}]
} "1 {error flushing \"$blob\": I/O error}"
catch { close $blob }
@@ -188,13 +192,16 @@ do_test 5.4.3 {
do_test 5.4.4 {
puts -nonewline $blob "world"
- list [catch { flush $blob } msg] $msg
+ set rc [catch { flush $blob } msg]
+ list $rc [regsub {input/output} $msg {I/O}]
} "1 {error flushing \"$blob\": I/O error}"
catch { close $blob }
catchsql { ROLLBACK }
-do_test 5.3.3 {
+do_test 5.4.5 {
set ::preupdate_count
} {2}
+}
+
finish_test
diff --git a/test/json101.test b/test/json101.test
index e22902f86..7582d14a6 100644
--- a/test/json101.test
+++ b/test/json101.test
@@ -892,15 +892,15 @@ do_execsql_test json101-13.100 {
INSERT INTO t2(id,json) VALUES(4,'{"value":4}');
INSERT INTO t2(id,json) VALUES(5,'{"value":5}');
INSERT INTO t2(id,json) VALUES(6,'{"value":6}');
- SELECT * FROM t1 CROSS JOIN t2
+ SELECT *, 'NL' FROM t1 CROSS JOIN t2
WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z
WHERE Z.value==t2.id);
-} {1 {{"items":[3,5]}} 3 {{"value":3}} 1 {{"items":[3,5]}} 5 {{"value":5}}}
+} {1 {{"items":[3,5]}} 3 {{"value":3}} NL 1 {{"items":[3,5]}} 5 {{"value":5}} NL}
do_execsql_test json101-13.110 {
- SELECT * FROM t2 CROSS JOIN t1
+ SELECT *, 'NL' FROM t2 CROSS JOIN t1
WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z
WHERE Z.value==t2.id);
-} {3 {{"value":3}} 1 {{"items":[3,5]}} 5 {{"value":5}} 1 {{"items":[3,5]}}}
+} {3 {{"value":3}} 1 {{"items":[3,5]}} NL 5 {{"value":5}} 1 {{"items":[3,5]}} NL}
# 2018-05-16
# Incorrect fullkey output from json_each()
diff --git a/test/notnull2.test b/test/notnull2.test
index 09161efbd..67d7c26a8 100644
--- a/test/notnull2.test
+++ b/test/notnull2.test
@@ -66,7 +66,7 @@ do_vmstep_test 1.5.2 {
SELECT count(*) FROM t2 WHERE EXISTS(
SELECT 1 FROM t1 WHERE t1.a=450 AND t2.c IS NULL
)
-} +8000 {0}
+} 4000 {0}
#-------------------------------------------------------------------------
reset_db
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-09 17:31:40 +0000