Fossil SCM
Fix a bug in recursive CTE used to help implement from=,bt= which which might result in an incorrect answer. The original query was put in place prior to the "subquery-order-by-optimization" patch to SQLite ([src:/info/20240816185146|2024-08-16]) version 3.47.0. Without that optimization, the query runs slowly. Also: a comment typo fix.
Commit
29f946923eb9f739ac5622b72520a295ca0899213a745ca589a41a11b81f3638
Parent
2229b2f6f1bfe5a…
2 files changed
+1
-1
+4
-2
+1
-1
| --- src/name.c | ||
| +++ src/name.c | ||
| @@ -231,11 +231,11 @@ | ||
| 231 | 231 | ** This is a tricky query to do efficiently. |
| 232 | 232 | ** If the tag is very common (ex: "trunk") then |
| 233 | 233 | ** we want to use the query identified below as Q1 - which searches |
| 234 | 234 | ** the most recent EVENT table entries for the most recent with the tag. |
| 235 | 235 | ** But if the tag is relatively scarce (anything other than "trunk", basically) |
| 236 | -** then we want to do the indexed search show below as Q2. | |
| 236 | +** then we want to do the indexed search shown below as Q2. | |
| 237 | 237 | */ |
| 238 | 238 | static int most_recent_event_with_tag(const char *zTag, const char *zType){ |
| 239 | 239 | return db_int(0, |
| 240 | 240 | "SELECT objid FROM (" |
| 241 | 241 | /* Q1: Begin by looking for the tag in the 30 most recent events */ |
| 242 | 242 |
| --- src/name.c | |
| +++ src/name.c | |
| @@ -231,11 +231,11 @@ | |
| 231 | ** This is a tricky query to do efficiently. |
| 232 | ** If the tag is very common (ex: "trunk") then |
| 233 | ** we want to use the query identified below as Q1 - which searches |
| 234 | ** the most recent EVENT table entries for the most recent with the tag. |
| 235 | ** But if the tag is relatively scarce (anything other than "trunk", basically) |
| 236 | ** then we want to do the indexed search show below as Q2. |
| 237 | */ |
| 238 | static int most_recent_event_with_tag(const char *zTag, const char *zType){ |
| 239 | return db_int(0, |
| 240 | "SELECT objid FROM (" |
| 241 | /* Q1: Begin by looking for the tag in the 30 most recent events */ |
| 242 |
| --- src/name.c | |
| +++ src/name.c | |
| @@ -231,11 +231,11 @@ | |
| 231 | ** This is a tricky query to do efficiently. |
| 232 | ** If the tag is very common (ex: "trunk") then |
| 233 | ** we want to use the query identified below as Q1 - which searches |
| 234 | ** the most recent EVENT table entries for the most recent with the tag. |
| 235 | ** But if the tag is relatively scarce (anything other than "trunk", basically) |
| 236 | ** then we want to do the indexed search shown below as Q2. |
| 237 | */ |
| 238 | static int most_recent_event_with_tag(const char *zTag, const char *zType){ |
| 239 | return db_int(0, |
| 240 | "SELECT objid FROM (" |
| 241 | /* Q1: Begin by looking for the tag in the 30 most recent events */ |
| 242 |
+4
-2
| --- src/timeline.c | ||
| +++ src/timeline.c | ||
| @@ -1408,11 +1408,12 @@ | ||
| 1408 | 1408 | " AND plink.mtime<=(SELECT max(event.mtime) FROM tagxref, event" |
| 1409 | 1409 | " WHERE tagxref.tagid=%d AND tagxref.tagtype>0" |
| 1410 | 1410 | " AND event.objid=tagxref.rid)" |
| 1411 | 1411 | " ORDER BY plink.mtime)" |
| 1412 | 1412 | "SELECT id FROM dx, tagxref" |
| 1413 | - " WHERE tagid=%d AND tagtype>0 AND rid=id LIMIT 1", | |
| 1413 | + " WHERE tagid=%d AND tagtype>0 AND rid=id" | |
| 1414 | + " ORDER BY dx.mtime LIMIT 1", | |
| 1414 | 1415 | iFrom, iFrom, tagId, tagId |
| 1415 | 1416 | ); |
| 1416 | 1417 | }else{ |
| 1417 | 1418 | db_prepare(&q, |
| 1418 | 1419 | "WITH RECURSIVE dx(id,mtime) AS (" |
| @@ -1439,11 +1440,12 @@ | ||
| 1439 | 1440 | " AND event.mtime>=(SELECT min(event.mtime) FROM tagxref, event" |
| 1440 | 1441 | " WHERE tagxref.tagid=%d AND tagxref.tagtype>0" |
| 1441 | 1442 | " AND event.objid=tagxref.rid)" |
| 1442 | 1443 | " ORDER BY event.mtime DESC)" |
| 1443 | 1444 | "SELECT id FROM dx, tagxref" |
| 1444 | - " WHERE tagid=%d AND tagtype>0 AND rid=id LIMIT 1", | |
| 1445 | + " WHERE tagid=%d AND tagtype>0 AND rid=id" | |
| 1446 | + " ORDER BY dx.mtime DESC LIMIT 1", | |
| 1445 | 1447 | iFrom, iFrom, tagId, tagId |
| 1446 | 1448 | ); |
| 1447 | 1449 | }else{ |
| 1448 | 1450 | db_prepare(&q, |
| 1449 | 1451 | "WITH RECURSIVE dx(id,mtime) AS (" |
| 1450 | 1452 |
| --- src/timeline.c | |
| +++ src/timeline.c | |
| @@ -1408,11 +1408,12 @@ | |
| 1408 | " AND plink.mtime<=(SELECT max(event.mtime) FROM tagxref, event" |
| 1409 | " WHERE tagxref.tagid=%d AND tagxref.tagtype>0" |
| 1410 | " AND event.objid=tagxref.rid)" |
| 1411 | " ORDER BY plink.mtime)" |
| 1412 | "SELECT id FROM dx, tagxref" |
| 1413 | " WHERE tagid=%d AND tagtype>0 AND rid=id LIMIT 1", |
| 1414 | iFrom, iFrom, tagId, tagId |
| 1415 | ); |
| 1416 | }else{ |
| 1417 | db_prepare(&q, |
| 1418 | "WITH RECURSIVE dx(id,mtime) AS (" |
| @@ -1439,11 +1440,12 @@ | |
| 1439 | " AND event.mtime>=(SELECT min(event.mtime) FROM tagxref, event" |
| 1440 | " WHERE tagxref.tagid=%d AND tagxref.tagtype>0" |
| 1441 | " AND event.objid=tagxref.rid)" |
| 1442 | " ORDER BY event.mtime DESC)" |
| 1443 | "SELECT id FROM dx, tagxref" |
| 1444 | " WHERE tagid=%d AND tagtype>0 AND rid=id LIMIT 1", |
| 1445 | iFrom, iFrom, tagId, tagId |
| 1446 | ); |
| 1447 | }else{ |
| 1448 | db_prepare(&q, |
| 1449 | "WITH RECURSIVE dx(id,mtime) AS (" |
| 1450 |
| --- src/timeline.c | |
| +++ src/timeline.c | |
| @@ -1408,11 +1408,12 @@ | |
| 1408 | " AND plink.mtime<=(SELECT max(event.mtime) FROM tagxref, event" |
| 1409 | " WHERE tagxref.tagid=%d AND tagxref.tagtype>0" |
| 1410 | " AND event.objid=tagxref.rid)" |
| 1411 | " ORDER BY plink.mtime)" |
| 1412 | "SELECT id FROM dx, tagxref" |
| 1413 | " WHERE tagid=%d AND tagtype>0 AND rid=id" |
| 1414 | " ORDER BY dx.mtime LIMIT 1", |
| 1415 | iFrom, iFrom, tagId, tagId |
| 1416 | ); |
| 1417 | }else{ |
| 1418 | db_prepare(&q, |
| 1419 | "WITH RECURSIVE dx(id,mtime) AS (" |
| @@ -1439,11 +1440,12 @@ | |
| 1440 | " AND event.mtime>=(SELECT min(event.mtime) FROM tagxref, event" |
| 1441 | " WHERE tagxref.tagid=%d AND tagxref.tagtype>0" |
| 1442 | " AND event.objid=tagxref.rid)" |
| 1443 | " ORDER BY event.mtime DESC)" |
| 1444 | "SELECT id FROM dx, tagxref" |
| 1445 | " WHERE tagid=%d AND tagtype>0 AND rid=id" |
| 1446 | " ORDER BY dx.mtime DESC LIMIT 1", |
| 1447 | iFrom, iFrom, tagId, tagId |
| 1448 | ); |
| 1449 | }else{ |
| 1450 | db_prepare(&q, |
| 1451 | "WITH RECURSIVE dx(id,mtime) AS (" |
| 1452 |