Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Merge trunk enhancements into the exists-to-join branch. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | exists-to-join |
Files: | files | file ages | folders |
SHA3-256: |
5f25a9518a675efbd0525cc2f5595ee7 |
User & Date: | drh 2024-06-11 17:37:36 |
Context
2024-06-11
| ||
18:22 | Provide the ability to disable the exists-to-join optimization using SQLITE_TESTCTRL_OPTIMIZATIONS. Provide tree-trace output for the optimization. (check-in: 33a3f327 user: drh tags: exists-to-join) | |
17:37 | Merge trunk enhancements into the exists-to-join branch. (check-in: 5f25a951 user: drh tags: exists-to-join) | |
17:04 | Remove some dead JS code and update some JS docs. (check-in: 6935ac71 user: stephan tags: trunk) | |
2024-06-10
| ||
19:45 | Add missing calls to sqlite3exprSkipCollateAndLikely() to the enchancement on this branch. (check-in: 078537d0 user: dan tags: exists-to-join) | |
Changes
Changes to ext/wasm/api/sqlite3-api-worker1.js.
︙ | ︙ | |||
567 568 569 570 571 572 573 | [ 'bigIntEnabled' ].forEach(function(k){ if(Object.getOwnPropertyDescriptor(src, k)) rc[k] = src[k]; }); rc.version = sqlite3.version; rc.vfsList = sqlite3.capi.sqlite3_js_vfs_list(); | < | 567 568 569 570 571 572 573 574 575 576 577 578 579 580 | [ 'bigIntEnabled' ].forEach(function(k){ if(Object.getOwnPropertyDescriptor(src, k)) rc[k] = src[k]; }); rc.version = sqlite3.version; rc.vfsList = sqlite3.capi.sqlite3_js_vfs_list(); return rc; }, /** Exports the database to a byte array, as per sqlite3_serialize(). Response is an object: |
︙ | ︙ | |||
594 595 596 597 598 599 600 | }; wState.xfer.push(response.byteArray.buffer); return response; }/*export()*/, toss: function(ev){ toss("Testing worker exception"); | < < < < < < | 593 594 595 596 597 598 599 600 601 602 603 604 605 606 | }; wState.xfer.push(response.byteArray.buffer); return response; }/*export()*/, toss: function(ev){ toss("Testing worker exception"); } }/*wMsgHandler*/; globalThis.onmessage = async function(ev){ ev = ev.data; let result, dbId = ev.dbId, evType = ev.type; const arrivalTime = performance.now(); |
︙ | ︙ |
Changes to ext/wasm/api/sqlite3-vfs-opfs.c-pp.js.
︙ | ︙ | |||
34 35 36 37 38 39 40 | - The counterpart Worker (see below) could not be loaded. - The environment does not support OPFS. That includes when this function is called from the main window thread. Significant notes and limitations: | < < < < | | > | | | > | 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | - The counterpart Worker (see below) could not be loaded. - The environment does not support OPFS. That includes when this function is called from the main window thread. Significant notes and limitations: - The OPFS features used here are only available in dedicated Worker threads. This file tries to detect that case, resulting in a rejected Promise if those features do not seem to be available. - It requires the SharedArrayBuffer and Atomics classes, and the former is only available if the HTTP server emits the so-called COOP and COEP response headers. These features are required for proxying OPFS's synchronous API via the synchronous interface required by the sqlite3_vfs API. - This function may only be called a single time. When called, this function removes itself from the sqlite3 object. All arguments to this function are for internal/development purposes only. They do not constitute a public API and may change at any time. The argument may optionally be a plain object with the following configuration options: - proxyUri: name of the async proxy JS file. - verbose (=2): an integer 0-3. 0 disables all logging, 1 enables logging of errors. 2 enables logging of warnings and errors. 3 additionally enables debugging info. Logging is performed via the sqlite3.config.{log|warn|error}() functions. - sanityChecks (=false): if true, some basic sanity tests are run on the OPFS VFS API after it's initialized, before the returned Promise resolves. This is only intended for testing and development of the VFS, not client-side use. On success, the Promise resolves to the top-most sqlite3 namespace object and that object gets a new object installed in its `opfs` property, containing several OPFS-specific utilities. */ const installOpfsVfs = function callee(options){ if(!globalThis.SharedArrayBuffer |
︙ | ︙ | |||
990 991 992 993 994 995 996 | }catch(e){ //sqlite3.config.warn("mkdir(",absDirName,") failed:",e); return false; } }; /** Checks whether the given OPFS filesystem entry exists, | | > > | 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 | }catch(e){ //sqlite3.config.warn("mkdir(",absDirName,") failed:",e); return false; } }; /** Checks whether the given OPFS filesystem entry exists, returning true if it does, false if it doesn't or if an exception is intercepted while trying to make the determination. */ opfsUtil.entryExists = async function(fsEntryName){ try { const [dh, fn] = await opfsUtil.getDirForFilename(fsEntryName); await dh.getFileHandle(fn); return true; }catch(e){ |
︙ | ︙ | |||
1106 1107 1108 1109 1110 1111 1112 | }); } return false; } }; /** | | | | | 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 | }); } return false; } }; /** Traverses the OPFS filesystem, calling a callback for each entry. The argument may be either a callback function or an options object with any of the following properties: - `callback`: function which gets called for each filesystem entry. It gets passed 3 arguments: 1) the FileSystemFileHandle or FileSystemDirectoryHandle of each entry (noting that both are instanceof FileSystemHandle). 2) the FileSystemDirectoryHandle of the parent directory. 3) the current depth level, with 0 being at the top of the tree |
︙ | ︙ | |||
1138 1139 1140 1141 1142 1143 1144 | If this function is passed a function, it is assumed to be the callback. Returns a promise because it has to (by virtue of being async) but that promise has no specific meaning: the traversal it performs is synchronous. The promise must be used to catch any exceptions propagated by the callback, however. | < < < < < | 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 | If this function is passed a function, it is assumed to be the callback. Returns a promise because it has to (by virtue of being async) but that promise has no specific meaning: the traversal it performs is synchronous. The promise must be used to catch any exceptions propagated by the callback, however. */ opfsUtil.traverse = async function(opt){ const defaultOpt = { recursive: true, directory: opfsUtil.rootDirectory }; if('function'===typeof opt){ |
︙ | ︙ |
Changes to src/date.c.
︙ | ︙ | |||
663 664 665 666 667 668 669 | float rLimit; /* Maximum NNN value for this transform */ float rXform; /* Constant used for this transform */ } aXformType[] = { /* 0 */ { 6, "second", 4.6427e+14, 1.0 }, /* 1 */ { 6, "minute", 7.7379e+12, 60.0 }, /* 2 */ { 4, "hour", 1.2897e+11, 3600.0 }, /* 3 */ { 3, "day", 5373485.0, 86400.0 }, | | | | 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 | float rLimit; /* Maximum NNN value for this transform */ float rXform; /* Constant used for this transform */ } aXformType[] = { /* 0 */ { 6, "second", 4.6427e+14, 1.0 }, /* 1 */ { 6, "minute", 7.7379e+12, 60.0 }, /* 2 */ { 4, "hour", 1.2897e+11, 3600.0 }, /* 3 */ { 3, "day", 5373485.0, 86400.0 }, /* 4 */ { 5, "month", 176546.0, 2592000.0 }, /* 5 */ { 4, "year", 14713.0, 31536000.0 }, }; /* ** If the DateTime p is raw number, try to figure out if it is ** a julian day number of a unix timestamp. Set the p value ** appropriately. */ |
︙ | ︙ |
Changes to src/func.c.
︙ | ︙ | |||
2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 | int y, z; char zBuf[100]; UNUSED_PARAMETER(argc); assert( argc==3 ); x = sqlite3_value_double(argv[0]); y = sqlite3_value_int(argv[1]); z = sqlite3_value_int(argv[2]); sqlite3FpDecode(&s, x, y, z); if( s.isSpecial==2 ){ sqlite3_snprintf(sizeof(zBuf), zBuf, "NaN"); }else{ sqlite3_snprintf(sizeof(zBuf), zBuf, "%c%.*s/%d", s.sign, s.n, s.z, s.iDP); } sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); | > | 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 2557 | int y, z; char zBuf[100]; UNUSED_PARAMETER(argc); assert( argc==3 ); x = sqlite3_value_double(argv[0]); y = sqlite3_value_int(argv[1]); z = sqlite3_value_int(argv[2]); if( z<=0 ) z = 1; sqlite3FpDecode(&s, x, y, z); if( s.isSpecial==2 ){ sqlite3_snprintf(sizeof(zBuf), zBuf, "NaN"); }else{ sqlite3_snprintf(sizeof(zBuf), zBuf, "%c%.*s/%d", s.sign, s.n, s.z, s.iDP); } sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); |
︙ | ︙ |
Changes to src/shell.c.in.
︙ | ︙ | |||
12856 12857 12858 12859 12860 12861 12862 | memset(&data, 0, sizeof(data)); #ifdef SQLITE_DEBUG if( sqlite3_memory_used()>mem_main_enter ){ eputf("Memory leaked: %u bytes\n", (unsigned int)(sqlite3_memory_used()-mem_main_enter)); } #endif | | > > | 12856 12857 12858 12859 12860 12861 12862 12863 12864 12865 12866 12867 12868 12869 12870 12871 12872 | memset(&data, 0, sizeof(data)); #ifdef SQLITE_DEBUG if( sqlite3_memory_used()>mem_main_enter ){ eputf("Memory leaked: %u bytes\n", (unsigned int)(sqlite3_memory_used()-mem_main_enter)); } #endif #else /* SQLITE_SHELL_FIDDLE... */ shell_main_exit: #endif return rc; } #ifdef SQLITE_SHELL_FIDDLE /* Only for emcc experimentation purposes. */ int fiddle_experiment(int a,int b){ |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
3384 3385 3386 3387 3388 3389 3390 | #define WHERE_GROUPBY 0x0040 /* pOrderBy is really a GROUP BY */ #define WHERE_DISTINCTBY 0x0080 /* pOrderby is really a DISTINCT clause */ #define WHERE_WANT_DISTINCT 0x0100 /* All output needs to be distinct */ #define WHERE_SORTBYGROUP 0x0200 /* Support sqlite3WhereIsSorted() */ #define WHERE_AGG_DISTINCT 0x0400 /* Query is "SELECT agg(DISTINCT ...)" */ #define WHERE_ORDERBY_LIMIT 0x0800 /* ORDERBY+LIMIT on the inner loop */ #define WHERE_RIGHT_JOIN 0x1000 /* Processing a RIGHT JOIN */ | | | 3384 3385 3386 3387 3388 3389 3390 3391 3392 3393 3394 3395 3396 3397 3398 | #define WHERE_GROUPBY 0x0040 /* pOrderBy is really a GROUP BY */ #define WHERE_DISTINCTBY 0x0080 /* pOrderby is really a DISTINCT clause */ #define WHERE_WANT_DISTINCT 0x0100 /* All output needs to be distinct */ #define WHERE_SORTBYGROUP 0x0200 /* Support sqlite3WhereIsSorted() */ #define WHERE_AGG_DISTINCT 0x0400 /* Query is "SELECT agg(DISTINCT ...)" */ #define WHERE_ORDERBY_LIMIT 0x0800 /* ORDERBY+LIMIT on the inner loop */ #define WHERE_RIGHT_JOIN 0x1000 /* Processing a RIGHT JOIN */ #define WHERE_KEEP_ALL_JOINS 0x2000 /* Do not do the omit-noop-join opt */ #define WHERE_USE_LIMIT 0x4000 /* Use the LIMIT in cost estimates */ /* 0x8000 not currently used */ /* Allowed return values from sqlite3WhereIsDistinct() */ #define WHERE_DISTINCT_NOOP 0 /* DISTINCT keyword not used */ #define WHERE_DISTINCT_UNIQUE 1 /* No duplicates */ |
︙ | ︙ |
Changes to src/util.c.
︙ | ︙ | |||
1011 1012 1013 1014 1015 1016 1017 | return x; } /* ** Decode a floating-point value into an approximate decimal ** representation. ** | | > | < | > > > > > | 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 | return x; } /* ** Decode a floating-point value into an approximate decimal ** representation. ** ** If iRound<=0 then round to -iRound significant digits to the ** the left of the decimal point, or to a maximum of mxRound total ** significant digits. ** ** If iRound>0 round to min(iRound,mxRound) significant digits total. ** ** mxRound must be positive. ** ** The significant digits of the decimal representation are ** stored in p->z[] which is a often (but not always) a pointer ** into the middle of p->zBuf[]. There are p->n significant digits. ** The p->z[] array is *not* zero-terminated. */ void sqlite3FpDecode(FpDecode *p, double r, int iRound, int mxRound){ int i; u64 v; int e, exp = 0; p->isSpecial = 0; p->z = p->zBuf; assert( mxRound>0 ); /* Convert negative numbers to positive. Deal with Infinity, 0.0, and ** NaN. */ if( r<0.0 ){ p->sign = '-'; r = -r; }else if( r==0.0 ){ |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
6014 6015 6016 6017 6018 6019 6020 6021 6022 6023 6024 6025 6026 6027 | ** 5) The table must not have an inner-join ON or USING clause if there is ** a RIGHT JOIN anywhere in the query. Otherwise the ON/USING clause ** might move from the right side to the left side of the RIGHT JOIN. ** Note: Due to (2), this condition can only arise if the table is ** the right-most table of a subquery that was flattened into the ** main query and that subquery was the right-hand operand of an ** inner join that held an ON or USING clause. ** ** For example, given: ** ** CREATE TABLE t1(ipk INTEGER PRIMARY KEY, v1); ** CREATE TABLE t2(ipk INTEGER PRIMARY KEY, v2); ** CREATE TABLE t3(ipk INTEGER PRIMARY KEY, v3); ** | > > > > | 6014 6015 6016 6017 6018 6019 6020 6021 6022 6023 6024 6025 6026 6027 6028 6029 6030 6031 | ** 5) The table must not have an inner-join ON or USING clause if there is ** a RIGHT JOIN anywhere in the query. Otherwise the ON/USING clause ** might move from the right side to the left side of the RIGHT JOIN. ** Note: Due to (2), this condition can only arise if the table is ** the right-most table of a subquery that was flattened into the ** main query and that subquery was the right-hand operand of an ** inner join that held an ON or USING clause. ** 6) The ORDER BY clause has 63 or fewer terms ** 7) The omit-noop-join optimization is enabled. ** ** Items (1), (6), and (7) are checked by the caller. ** ** For example, given: ** ** CREATE TABLE t1(ipk INTEGER PRIMARY KEY, v1); ** CREATE TABLE t2(ipk INTEGER PRIMARY KEY, v2); ** CREATE TABLE t3(ipk INTEGER PRIMARY KEY, v3); ** |
︙ | ︙ | |||
6428 6429 6430 6431 6432 6433 6434 6435 6436 6437 6438 6439 6440 6441 | memset(&sWLB, 0, sizeof(sWLB)); /* An ORDER/GROUP BY clause of more than 63 terms cannot be optimized */ testcase( pOrderBy && pOrderBy->nExpr==BMS-1 ); if( pOrderBy && pOrderBy->nExpr>=BMS ){ pOrderBy = 0; wctrlFlags &= ~WHERE_WANT_DISTINCT; } /* The number of tables in the FROM clause is limited by the number of ** bits in a Bitmask */ testcase( pTabList->nSrc==BMS ); if( pTabList->nSrc>BMS ){ | > | 6432 6433 6434 6435 6436 6437 6438 6439 6440 6441 6442 6443 6444 6445 6446 | memset(&sWLB, 0, sizeof(sWLB)); /* An ORDER/GROUP BY clause of more than 63 terms cannot be optimized */ testcase( pOrderBy && pOrderBy->nExpr==BMS-1 ); if( pOrderBy && pOrderBy->nExpr>=BMS ){ pOrderBy = 0; wctrlFlags &= ~WHERE_WANT_DISTINCT; wctrlFlags |= WHERE_KEEP_ALL_JOINS; /* Disable omit-noop-join opt */ } /* The number of tables in the FROM clause is limited by the number of ** bits in a Bitmask */ testcase( pTabList->nSrc==BMS ); if( pTabList->nSrc>BMS ){ |
︙ | ︙ | |||
6728 6729 6730 6731 6732 6733 6734 | ** procedure to keep the sqlite3WhereBegin() procedure from becoming ** too large. If sqlite3WhereBegin() becomes too large, that prevents ** some C-compiler optimizers from in-lining the ** sqlite3WhereCodeOneLoopStart() procedure, and it is important to ** in-line sqlite3WhereCodeOneLoopStart() for performance reasons. */ notReady = ~(Bitmask)0; | | | | | | 6733 6734 6735 6736 6737 6738 6739 6740 6741 6742 6743 6744 6745 6746 6747 6748 6749 6750 | ** procedure to keep the sqlite3WhereBegin() procedure from becoming ** too large. If sqlite3WhereBegin() becomes too large, that prevents ** some C-compiler optimizers from in-lining the ** sqlite3WhereCodeOneLoopStart() procedure, and it is important to ** in-line sqlite3WhereCodeOneLoopStart() for performance reasons. */ notReady = ~(Bitmask)0; if( pWInfo->nLevel>=2 /* Must be a join, or this opt8n is pointless */ && pResultSet!=0 /* Condition (1) */ && 0==(wctrlFlags & (WHERE_AGG_DISTINCT|WHERE_KEEP_ALL_JOINS)) /* (1),(6) */ && OptimizationEnabled(db, SQLITE_OmitNoopJoin) /* (7) */ ){ notReady = whereOmitNoopJoin(pWInfo, notReady); nTabList = pWInfo->nLevel; assert( nTabList>0 ); } /* Check to see if there are any SEARCH loops that might benefit from |
︙ | ︙ |