Re: [sqlite] [EXTERNAL] virtual tables, and theTrue meaning of pIdxInfo->estimatedCost, pIdxInfo->estimatedRows, and pIdxInfo->idxFlags...
I can provide some info coming from our experience with SQLite 3.7.14: Since most SQl processing is IO bound, the "estimated cost" should be the number of disk IO operations required to retrieve the rows. The later addition of "estimated rows" reflects to the fact that some virtual table implementations might use non-disk storage (e.g. process or shared memory), where the number of IO operations is determined by the resident set and the cost of paging/swapping. Lets say you have 1 records of 200 bytes with 50 bytes of key overhead stored in some kind of ISAM file, and a page size of 4k. Performing a full table scan will take an estimated 1 * 200 / 4096 ~= 489 disk accesses, whereas looking up a single record will take about 3 (50 bytes per key in a 4096 byte page gives an estimated fan out of over 100, resulting in 2 pages to read from the index and 1 for the record itself). Performing a partial index scan that returns 100 records will take 2 acesses to locate the first record, 1 more if a second index page is required and anywhere between 5 (if the records are contiguous) and 100 (if each is from a separate page) accesses to retrieve the records themselves. Regarding the UNIQUE flag, this is quite different from the number of estimated rows, which may be 0 or 1 due to rounding errors on a non-unique index (e.g. the initials of a set of 100 people has a cardinality of 26*26=676, giving an average number of 0,1479 records per index entry, but there may still be duplicates). -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von dave Gesendet: Donnerstag, 19. Oktober 2017 18:43 An: 'SQLite mailing list' Betreff: [EXTERNAL] [sqlite] virtual tables, and theTrue meaning of pIdxInfo->estimatedCost, pIdxInfo->estimatedRows, and pIdxInfo->idxFlags... Hi folks, I am trying to fully understand the impact and correct use of a few subtle features related to virtual tables' the xBestIndex mechanism, and their correct use. Here are my current beliefs: * pIdxInfo->estimatedCost obviously the cost of the proposed plan; a metric of the 'viscosity' of the table when traversing through xNext relative to other tables and especially to filesystem access * pIdxInfo->estimatedRows obviously the approximate number of rows that a proposed plan will return. But less obvious to me is how this materially affects the query plan, especially relative to pIdxInfo->estimatedCost and a little bit with respect to: * pIdxInfo->idxFlags when the SQLITE_INDEX_SCAN_UNIQUE is set. Isn't setting pIdxInfo->estimatedRows to 0 or 1 enough to communicate this same information? Anyway, I am dutifully setting both estimatedRows and idxFlags in cases where I have a 0-or-1-result table (I have several of these), and I am also estimatedRows to LLONG_MAX along with estimatedCost to DBL_MAX in cases where a plan can never be executed (btw I would respectfully suggest perhaps using a bit in idxFlags to communicate 'never use this plan, it will never work'). I haven't had any ill effects doing the above, but wonder if that is 'correct'. Also, it would be interesting just to know what the material effect of estimatedRows and idxFlags is, so that I can maybe use them more effectively. Any thoughts or corrections to my thinking? Thanks in advance; cheers! -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [BUG] shell: .import: no special characters in table name
Simon Slavin wrote: > On 19 Oct 2017, at 7:38pm, Clemens Ladisch wrote: >> the .import command does not work (and can show misleading error messages) >> if the table name contains special characters: >> >> sqlite> .import test.csv temp.t > > What format is the file "temp.t" in ? UTF-8 ? ASCII ? Some sort of code > page ? The contents of the file do not matter; just use ".once test.csv", "select 'x';". Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [BUG] shell: .import: no special characters in table name
On 19 Oct 2017, at 7:38pm, Clemens Ladisch wrote: > the .import command does not work (and can show misleading error messages) > if the table name contains special characters: > > sqlite> .import test.csv temp.t What format is the file "temp.t" in ? UTF-8 ? ASCII ? Some sort of code page ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [BUG] shell: .import: no special characters in table name
Hi, the .import command does not work (and can show misleading error messages) if the table name contains special characters: sqlite> .import test.csv temp.t Error: no such table: temp.t sqlite> select * from temp.t; sqlite> This appears to be caused by inconsistent quoting of the table name: zSql = sqlite3_mprintf("SELECT * FROM %s", zTable); char *zCreate = sqlite3_mprintf("CREATE TABLE %s", zTable); sqlite3_snprintf(nByte+20, zSql, "INSERT INTO \"%w\" VALUES(?", zTable); Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] virtual tables, and theTrue meaning of pIdxInfo->estimatedCost, pIdxInfo->estimatedRows, and pIdxInfo->idxFlags...
Hi folks, I am trying to fully understand the impact and correct use of a few subtle features related to virtual tables' the xBestIndex mechanism, and their correct use. Here are my current beliefs: * pIdxInfo->estimatedCost obviously the cost of the proposed plan; a metric of the 'viscosity' of the table when traversing through xNext relative to other tables and especially to filesystem access * pIdxInfo->estimatedRows obviously the approximate number of rows that a proposed plan will return. But less obvious to me is how this materially affects the query plan, especially relative to pIdxInfo->estimatedCost and a little bit with respect to: * pIdxInfo->idxFlags when the SQLITE_INDEX_SCAN_UNIQUE is set. Isn't setting pIdxInfo->estimatedRows to 0 or 1 enough to communicate this same information? Anyway, I am dutifully setting both estimatedRows and idxFlags in cases where I have a 0-or-1-result table (I have several of these), and I am also estimatedRows to LLONG_MAX along with estimatedCost to DBL_MAX in cases where a plan can never be executed (btw I would respectfully suggest perhaps using a bit in idxFlags to communicate 'never use this plan, it will never work'). I haven't had any ill effects doing the above, but wonder if that is 'correct'. Also, it would be interesting just to know what the material effect of estimatedRows and idxFlags is, so that I can maybe use them more effectively. Any thoughts or corrections to my thinking? Thanks in advance; cheers! -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Inconsistency between sha1 and sha3 extensions
Hello ! I've tried to use both ext/misc/sha1.c and ext/misc/shathree.c static linked with sqlite3 and noticed that instead of using an unique prefix for hash_step_vformat like (sha1_step_vformat / sha3_step_vformat) giving an error "duplicated symbol" at link time. Also the return of sha1 is converted to hexadecimal but the sha3 is not. Another problem with sha3 is that it clashes when compiled for ARM Android whit a CPU register B0 and I did a dirty patch to allow it compile see bellow: /* ** A single step of the Keccak mixing function for a 1600-bit state */ static void KeccakF1600Step(SHA3Context *p){ int i; +#ifdef B0 +#undef B0 +#endif u64 B0, B1, B2, B3, B4; u64 C0, C1, C2, C3, C4; u64 D0, D1, D2, D3, D4; static const u64 RC[] = { 0x0001ULL, 0x8082ULL, Cheers ! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems compiling FTS5 extension
Thanks for the tips! Don't know about the segfault too but can ignore it for now — this same file works well with the GUI app and with my Node.js program. On Thu, Oct 19, 2017 at 6:38 PM Dan Kennedy wrote: > On 10/19/2017 08:40 PM, Eugene Mirotin wrote: > > Hm, I think that's because the extension is already preloaded with my > > version if sqlite. Which means I didn't even have to build it :) > > But now is the question how would I reliably load / not load it in > > production env if the target user may or may not have sqlite precompiled > > with it? > > Right, it's an error to try to register a new version of a module - > "fts5" in this case - that has already been registered. > > You could just assume that if the attempt to load fts5 dynamically fails > it is already registered. Or you could try preparing the statement > "SELECT fts5_source_id()" before loading the extension. If preparing the > statement succeeds, fts5 is already present and you don't need to load it. > > Don't know why you might be getting the segfault. > > Dan. > > > > > > On Thu, Oct 19, 2017 at 3:30 PM Eugene Mirotin > wrote: > > > >> Well, now I have troubles loading this extension. > >> > >> I've built another one before, fts5stemmer. > >> > >> When using the CLI sqlite3 (version 3.20.1 from MacPorts): > >> > >> ❯ sqlite3 > >> SQLite version 3.20.1 2017-08-24 16:21:36 > >> Enter ".help" for usage hints. > >> Connected to a transient in-memory database. > >> Use ".open FILENAME" to reopen on a persistent database. > >> sqlite> .load './fts5.dylib' > >> Error: error during initialization: > >> sqlite> .load './fts5stemmer.dylib' > >> [1]35952 segmentation fault sqlite3 > >> > >> When using the GUI DB Browser for SQLite: > >> fts5 extension reports the same error "Error: error during > initialization:" > >> fst5stemmer loads fine (or at least reports to) > >> > >> On Thu, Oct 19, 2017 at 3:09 PM Eugene Mirotin > wrote: > >> > >>> Thanks a lot Dan, that worked! > >>> I'm fine with the trunk version for now but hope to see this in stable > >>> eventually as I'll have to later build this extension for various > platforms > >>> for the release of my app. > >>> Thanks again for the quick fix > >>> > >>> On Wed, Oct 18, 2017 at 6:06 PM Dan Kennedy > >>> wrote: > >>> > On 10/18/2017 06:32 PM, Eugene Mirotin wrote: > > In short the error I get is > > fts5_storage.c:305:9: error: 'sqlite3_api_routines' has no member > named > > '__builtin___snprintf_chk' > > > > More details in SO question here: > > > > https://stackoverflow.com/questions/46793988/sqlite-trouble-building-fts5-loadable-extension > , > > please let me know if I should paste everything in my email. > > > > I've seen a similar problem reported before and somehow related to > XCode, > > but that issue was reported to be fixed. > > > > Would be thankful for any tips, I haven't used C for years and have > no > idea > > where to start. > Please try with the latest trunk checkin: > > http://www.sqlite.org/src/info/cd0471ca9f75e7c8 > > (click the "ZIP archive" link to download if you're not using fossil) > > To generate the sqlite3ext.h and sqlite3.h files required when > compiling > fts5.c, run [make sqlite3.h sqlite3ext.h]. So, altogether, something > like: > > ./configure > make fts5.c sqlite3.h sqlite3ext.h > gcc -O2 -fPIC -shared fts5.c -o fts5.dylib > > Or, if you want to use a released version, after [make sqlite3ext.h] > replace the two instances of "snprintf" in the sqlite3ext.h with > "xsnprintf". > > Dan. > > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems compiling FTS5 extension
On 10/19/2017 08:40 PM, Eugene Mirotin wrote: Hm, I think that's because the extension is already preloaded with my version if sqlite. Which means I didn't even have to build it :) But now is the question how would I reliably load / not load it in production env if the target user may or may not have sqlite precompiled with it? Right, it's an error to try to register a new version of a module - "fts5" in this case - that has already been registered. You could just assume that if the attempt to load fts5 dynamically fails it is already registered. Or you could try preparing the statement "SELECT fts5_source_id()" before loading the extension. If preparing the statement succeeds, fts5 is already present and you don't need to load it. Don't know why you might be getting the segfault. Dan. On Thu, Oct 19, 2017 at 3:30 PM Eugene Mirotin wrote: Well, now I have troubles loading this extension. I've built another one before, fts5stemmer. When using the CLI sqlite3 (version 3.20.1 from MacPorts): ❯ sqlite3 SQLite version 3.20.1 2017-08-24 16:21:36 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .load './fts5.dylib' Error: error during initialization: sqlite> .load './fts5stemmer.dylib' [1]35952 segmentation fault sqlite3 When using the GUI DB Browser for SQLite: fts5 extension reports the same error "Error: error during initialization:" fst5stemmer loads fine (or at least reports to) On Thu, Oct 19, 2017 at 3:09 PM Eugene Mirotin wrote: Thanks a lot Dan, that worked! I'm fine with the trunk version for now but hope to see this in stable eventually as I'll have to later build this extension for various platforms for the release of my app. Thanks again for the quick fix On Wed, Oct 18, 2017 at 6:06 PM Dan Kennedy wrote: On 10/18/2017 06:32 PM, Eugene Mirotin wrote: In short the error I get is fts5_storage.c:305:9: error: 'sqlite3_api_routines' has no member named '__builtin___snprintf_chk' More details in SO question here: https://stackoverflow.com/questions/46793988/sqlite-trouble-building-fts5-loadable-extension , please let me know if I should paste everything in my email. I've seen a similar problem reported before and somehow related to XCode, but that issue was reported to be fixed. Would be thankful for any tips, I haven't used C for years and have no idea where to start. Please try with the latest trunk checkin: http://www.sqlite.org/src/info/cd0471ca9f75e7c8 (click the "ZIP archive" link to download if you're not using fossil) To generate the sqlite3ext.h and sqlite3.h files required when compiling fts5.c, run [make sqlite3.h sqlite3ext.h]. So, altogether, something like: ./configure make fts5.c sqlite3.h sqlite3ext.h gcc -O2 -fPIC -shared fts5.c -o fts5.dylib Or, if you want to use a released version, after [make sqlite3ext.h] replace the two instances of "snprintf" in the sqlite3ext.h with "xsnprintf". Dan. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems compiling FTS5 extension
Hm, I think that's because the extension is already preloaded with my version if sqlite. Which means I didn't even have to build it :) But now is the question how would I reliably load / not load it in production env if the target user may or may not have sqlite precompiled with it? On Thu, Oct 19, 2017 at 3:30 PM Eugene Mirotin wrote: > Well, now I have troubles loading this extension. > > I've built another one before, fts5stemmer. > > When using the CLI sqlite3 (version 3.20.1 from MacPorts): > > ❯ sqlite3 > SQLite version 3.20.1 2017-08-24 16:21:36 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> .load './fts5.dylib' > Error: error during initialization: > sqlite> .load './fts5stemmer.dylib' > [1]35952 segmentation fault sqlite3 > > When using the GUI DB Browser for SQLite: > fts5 extension reports the same error "Error: error during initialization:" > fst5stemmer loads fine (or at least reports to) > > On Thu, Oct 19, 2017 at 3:09 PM Eugene Mirotin wrote: > >> Thanks a lot Dan, that worked! >> I'm fine with the trunk version for now but hope to see this in stable >> eventually as I'll have to later build this extension for various platforms >> for the release of my app. >> Thanks again for the quick fix >> >> On Wed, Oct 18, 2017 at 6:06 PM Dan Kennedy >> wrote: >> >>> On 10/18/2017 06:32 PM, Eugene Mirotin wrote: >>> > In short the error I get is >>> > fts5_storage.c:305:9: error: 'sqlite3_api_routines' has no member named >>> > '__builtin___snprintf_chk' >>> > >>> > More details in SO question here: >>> > >>> https://stackoverflow.com/questions/46793988/sqlite-trouble-building-fts5-loadable-extension >>> , >>> > please let me know if I should paste everything in my email. >>> > >>> > I've seen a similar problem reported before and somehow related to >>> XCode, >>> > but that issue was reported to be fixed. >>> > >>> > Would be thankful for any tips, I haven't used C for years and have no >>> idea >>> > where to start. >>> >>> Please try with the latest trunk checkin: >>> >>>http://www.sqlite.org/src/info/cd0471ca9f75e7c8 >>> >>> (click the "ZIP archive" link to download if you're not using fossil) >>> >>> To generate the sqlite3ext.h and sqlite3.h files required when compiling >>> fts5.c, run [make sqlite3.h sqlite3ext.h]. So, altogether, something >>> like: >>> >>>./configure >>>make fts5.c sqlite3.h sqlite3ext.h >>>gcc -O2 -fPIC -shared fts5.c -o fts5.dylib >>> >>> Or, if you want to use a released version, after [make sqlite3ext.h] >>> replace the two instances of "snprintf" in the sqlite3ext.h with >>> "xsnprintf". >>> >>> Dan. >>> >>> >>> >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems compiling FTS5 extension
Well, now I have troubles loading this extension. I've built another one before, fts5stemmer. When using the CLI sqlite3 (version 3.20.1 from MacPorts): ❯ sqlite3 SQLite version 3.20.1 2017-08-24 16:21:36 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .load './fts5.dylib' Error: error during initialization: sqlite> .load './fts5stemmer.dylib' [1]35952 segmentation fault sqlite3 When using the GUI DB Browser for SQLite: fts5 extension reports the same error "Error: error during initialization:" fst5stemmer loads fine (or at least reports to) On Thu, Oct 19, 2017 at 3:09 PM Eugene Mirotin wrote: > Thanks a lot Dan, that worked! > I'm fine with the trunk version for now but hope to see this in stable > eventually as I'll have to later build this extension for various platforms > for the release of my app. > Thanks again for the quick fix > > On Wed, Oct 18, 2017 at 6:06 PM Dan Kennedy wrote: > >> On 10/18/2017 06:32 PM, Eugene Mirotin wrote: >> > In short the error I get is >> > fts5_storage.c:305:9: error: 'sqlite3_api_routines' has no member named >> > '__builtin___snprintf_chk' >> > >> > More details in SO question here: >> > >> https://stackoverflow.com/questions/46793988/sqlite-trouble-building-fts5-loadable-extension >> , >> > please let me know if I should paste everything in my email. >> > >> > I've seen a similar problem reported before and somehow related to >> XCode, >> > but that issue was reported to be fixed. >> > >> > Would be thankful for any tips, I haven't used C for years and have no >> idea >> > where to start. >> >> Please try with the latest trunk checkin: >> >>http://www.sqlite.org/src/info/cd0471ca9f75e7c8 >> >> (click the "ZIP archive" link to download if you're not using fossil) >> >> To generate the sqlite3ext.h and sqlite3.h files required when compiling >> fts5.c, run [make sqlite3.h sqlite3ext.h]. So, altogether, something like: >> >>./configure >>make fts5.c sqlite3.h sqlite3ext.h >>gcc -O2 -fPIC -shared fts5.c -o fts5.dylib >> >> Or, if you want to use a released version, after [make sqlite3ext.h] >> replace the two instances of "snprintf" in the sqlite3ext.h with >> "xsnprintf". >> >> Dan. >> >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems compiling FTS5 extension
Thanks a lot Dan, that worked! I'm fine with the trunk version for now but hope to see this in stable eventually as I'll have to later build this extension for various platforms for the release of my app. Thanks again for the quick fix On Wed, Oct 18, 2017 at 6:06 PM Dan Kennedy wrote: > On 10/18/2017 06:32 PM, Eugene Mirotin wrote: > > In short the error I get is > > fts5_storage.c:305:9: error: 'sqlite3_api_routines' has no member named > > '__builtin___snprintf_chk' > > > > More details in SO question here: > > > https://stackoverflow.com/questions/46793988/sqlite-trouble-building-fts5-loadable-extension > , > > please let me know if I should paste everything in my email. > > > > I've seen a similar problem reported before and somehow related to XCode, > > but that issue was reported to be fixed. > > > > Would be thankful for any tips, I haven't used C for years and have no > idea > > where to start. > > Please try with the latest trunk checkin: > >http://www.sqlite.org/src/info/cd0471ca9f75e7c8 > > (click the "ZIP archive" link to download if you're not using fossil) > > To generate the sqlite3ext.h and sqlite3.h files required when compiling > fts5.c, run [make sqlite3.h sqlite3ext.h]. So, altogether, something like: > >./configure >make fts5.c sqlite3.h sqlite3ext.h >gcc -O2 -fPIC -shared fts5.c -o fts5.dylib > > Or, if you want to use a released version, after [make sqlite3ext.h] > replace the two instances of "snprintf" in the sqlite3ext.h with > "xsnprintf". > > Dan. > > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Wrong Column Name in "create table as select"
Hello, with SQLite 3.20.1 2017-08-24 16:21:36 8d3a7ea6c5690d6b7c3767558f4f01b511c55463e3f9e64506801fe9b74dce34 I am observing the following: sqlite> create table q as select service from (select v as service from (select 1 as v)); sqlite> .schema CREATE TABLE q(v); I would expect the column name to be "service", not "v", and I think the behaviour changed recently. For comparison, with SQLite version 3.11.0 2016-02-15 17:29:24 I get: sqlite> create table q as select service from (select v as service from (select 1 as v)); sqlite> .schema CREATE TABLE q(service); This is a simplified example of a similar more complex case, that exhibits the same phenomenon. Sincerely, Thomas Rohwer ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users