Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

Yes... that's true. Since I don't know enough about how SQLite looks up a single record referenced by an index, I'm not in a position to discuss further. Naturally, there is always a trade off and, given the right percentage of matches, or position of matches, one way will be more efficient

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

On Friday, 14 July, 2017 18:20, Marc L. Allen said: > I don't know... since each match would then require a lookup in the main > table to pull all the fields. If X was a covering index for the query, I > would agree. So, if you did a table scan, you would have to

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

I don't know... since each match would then require a lookup in the main table to pull all the fields. If X was a covering index for the query, I would agree. > On Jul 14, 2017, at 7:54 PM, Keith Medcalf wrote: > > > Not necessarily. Your table may be defined as: > >

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

Not necessarily. Your table may be defined as: create table x(a int, b int, c int, d int, ... y int, z int); create index x_y on x (y); in that case a select * from x where expression(y) that scans the index (even without any skip-scan optimization) would be far more efficient than scanning

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

On 2017/07/14 8:57 PM, David Raymond wrote: "The only way to get that information from an index that includes all values of y (even and odd) is to scan." -Yes, but scan the index, not scan the raw table. Actually, scanning the table is faster, One common misconception I find in the wild is

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

Generally, but not always, WHERE elements using a column in an expression are unsuitable to using an index. For instance: WHERE y + 2 = 4 generally can't use an index, whereas WHERE y = 2 can. That's not to say a smart optimizer won't convert the former to the latter. But, what's the

Re: [sqlite] Version 3.20.0 coming soon...

Regarding the new pointer value/result pseudo-null API: void *sqlite3_value_pointer(sqlite3_value*); void sqlite3_result_pointer(sqlite3_context*, void*); Assuming sqlite3_value_type() returns SQLITE_NULL for these pseudo-nulls, and thinking ahead, an additional pointer type API pair would be

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

You can browse what gets looked at for indexes and optimizations here: http://www.sqlite.org/optoverview.html So I agree there's an "opportunity for optimization", but yes it is known. "The only way to get that information from an index that includes all values of y (even and odd) is to scan."

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

On Fri, Jul 14, 2017 at 11:55 AM, Howard Kapustein wrote: > sqlite> create table blah(id integer primary key not null, x text, y integer > not null); > sqlite> create index blahindex on blah (y); > sqlite> explain query plan select * from blah where y & ? != 0; >

Re: [sqlite] cannot compile sqlite3 with intel compiler (ICC) - log and hints for a possible solution

> On Jul 13, 2017, at 3:41 PM, Keith Medcalf wrote: > > CLang must be just catching up. > > That is better though than Intel, Microsoft, et al, who make really shoddy > compilers (buggy and suitable for amateur use only) let alone have any actual > "advanced" features.

[sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

sqlite> create table blah(id integer primary key not null, x text, y integer not null); sqlite> create index blahindex on blah (y); sqlite> explain query plan select * from blah where y & ? != 0; 0|0|0|SCAN TABLE blah But other numeric expressions do e.g. sqlite> explain query plan select *

Re: [sqlite] Return value of Tcl interface to wal_hook

On 07/14/2017 08:55 PM, Peter da Silva wrote: In wal_hook we have: ** The callback function should normally return [SQLITE_OK]. ^If an error ** code is returned, that error will propagate back up through the ** SQLite code base to cause the statement that provoked the callback ** to report an

Re: [sqlite] extension to query/set environment variables?

Nelson, Erik - 2 wrote: > Hello, I could imagine a virtual table that held all the environment > variables, or a user-defined function to get or set environment variables. > Does anyone know of anything in the wild along these lines? I know how I > would implement it, wanted to see if someone

Re: [sqlite] Version 3.20.0 coming soon...

The STMT extension has been renamed as SQLITE_STMT. Updated code is in the prerelease snapshot on https://sqlite.org/download.html and the draft documentation (https://sqlite.org/draft/index.html) has been updated. -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] Version 3.20.0 coming soon...

I wonder how SQLITE_PREPARE_PERSISTENT can be used in a jdbc driver. Do you have any idea? (seems like there is no possibility to know about it from the jdbc API) is it better to always set this flag or to never do it? ___ sqlite-users mailing list

Re: [sqlite] Version 3.20.0 coming soon...

> perhaps the devs can invent some other namespace Seconded. I would like to think the devs, themselves, would like to create some sort of namespace or isolation for the structures created by the extensions. A little more gusto in the name, rather than STMT, would always be appreciated. imo On

[sqlite] Return value of Tcl interface to wal_hook

In wal_hook we have: ** The callback function should normally return [SQLITE_OK]. ^If an error ** code is returned, that error will propagate back up through the ** SQLite code base to cause the statement that provoked the callback ** to report an error, though the commit will have still

[sqlite] extension to query/set environment variables?

Hello, I could imagine a virtual table that held all the environment variables, or a user-defined function to get or set environment variables. Does anyone know of anything in the wild along these lines? I know how I would implement it, wanted to see if someone else already had. Erik

Re: [sqlite] Suggestion for PRAGMA SYNCHRONOUS=0 documentation

Rowan Worth wrote: > I do think it's worth a mention in the PRAGMA SYNCHRONOUS=0 documentation > that sqlite3_close() doesn't propagate I/O errors so you might never hear > about write failures in this mode. When writing asynchronously, errors can be detected _after_ the close, so this is not

Re: [sqlite] Porting SQLite: Stack error

Try commenting-out the three lines and https://www.sqlite.org/src/artifact/e384cb73f?ln=77-79 and see if that helps. Let us know either way. On 7/13/17, LukasOsswald wrote: > Hello, > > I am trying to port SQLite3 to an embedded system using an ARM926 running a

Re: [sqlite] Version 3.20.0 coming soon...

On 14 Jul 2017, at 8:50am, Clemens Ladisch wrote: > There already is an "sqlite_" namespace, and it is reserved for internal > objects of SQLite itself. > > The STMT virtual table is an extension that must be explicitly enabled. > (The sqlite3 command-line shell does so by

[sqlite] Porting SQLite: Stack error

Hello, I am trying to port SQLite3 to an embedded system using an ARM926 running a real time os. The VFS is based on the example OneFile VFS created by the SQLite Developers. The following compiler flags are used: 'SQLITE_INT64_TYPE=long long',

[sqlite] Suggestion for PRAGMA SYNCHRONOUS=0 documentation

Hi guys, Ran into an interesting case today where one of our programs was writing an sqlite DB to a network file system. Just as it was finishing up there was a brief connectivity issue, and sqlite's call to close() ran into an EIO error. Sqlite3_close() doesn't return an error in this case so

[sqlite] 3.20 (beta) - secure_delete=FAST

Hello, https://sqlite.org/draft/pragma.html#pragma_secure_delete. Shouldn't/Couldn't the https://sqlite.org/draft/compile.html#secure_delete compile-time option be changed to match and allow presetting secure_delete to FAST mode? -- Best Regards, Meilleures salutations, Met vriendelijke

Re: [sqlite] Version 3.20.0 coming soon...

Eric Grange wrote: >> Could I suggest that the STMT virtual table be renamed as something like >> sqlite_statements ? >> Or anything else that starts with "sqlite_" ? > > Seconded, especially as more and more virtual tables become available (a > good thing), there is a greater risk of name

Re: [sqlite] Version 3.20.0 coming soon...

> Could I suggest that the STMT virtual table be renamed as something like sqlite_statements ? > Or anything else that starts with "sqlite_" ? Seconded, especially as more and more virtual tables become available (a good thing), there is a greater risk of name collision, so an "sqlite_" namespace

[sqlite] Extract all SQL commands from the test suite

Hello, I would like to extract all SQL commands from sqlite-src/test/*.test. For example, extract: PRAGMA cache_size=10; CREATE TABLE t1(x); INSERT INTO t1 VALUES(randomblob(2)); BEGIN; DELETE FROM t1; INSERT INTO t1 VALUES(randomblob(15000)); ... from 8_3_names.test.

[sqlite] List of subdomains/tools/drivers

Hello, Is there a web page listing all the SQLite related fossil repositories ? https://www.sqlite.org/sqlar/ http://sqlite.org/sqllogictest/ https://www.sqlite.org/android/ http://system.data.sqlite.org/ http://lua.sqlite.org/ ... Thanks. ___