Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> Why don't you just explicitly sort by bar.foo? > > > sqlite> EXPLAIN QUERY PLAN SELECT bar.foo as id, foo.baz FROM bar CROSS JOIN > foo ON bar.foo = foo.id ORDER BY bar.foo LIMIT 10, 10; > 0|0|0|SCAN TABLE bar > 0|1|1|SEARCH TABLE foo USING INTEGER PRIMARY KEY (rowid=?) > sqlite> I have

[sqlite] I keep getting seg faults building my database using python sqlite3

I ran this thing 3 times with identical inputs, it is deterministic, but it failed after 66, 128 and 96 minutes respectively. Each run started with no database at all, and gets a single input from which the rest is calculated. The calculations are cached (in flat files), so and it never got to

Re: [sqlite] SQLite as a Shell Script

Ah, I stand corrected. Thanks for pointing that out! Although if you try running a "db" script in an interactive shell: bash 4.1.2 says: bash: /tmp/db: cannot execute binary file zsh 4.3.10 says: zsh: /tmp/db: cannot execute binary file dash 0.5.5.1 says: /tmp/db: /tmp/db: cannot execute binary

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

Why don't you just explicitly sort by bar.foo? sqlite> EXPLAIN QUERY PLAN SELECT bar.foo as id, foo.baz FROM bar CROSS JOIN foo ON bar.foo = foo.id ORDER BY bar.foo LIMIT 10, 10; 0|0|0|SCAN TABLE bar 0|1|1|SEARCH TABLE foo USING INTEGER PRIMARY KEY (rowid=?) sqlite> Dimitris

Re: [sqlite] column type impact on index usage

On 11/17/16, Mark Wagner wrote: > > Here's my sample code. Note that in the case when the columns are both > integer an index is used and when one is integer and one is not specified > no index is used. > > BEGIN TRANSACTION; > CREATE TABLE seq (value integer); > CREATE TABLE

[sqlite] column type impact on index usage

I thought I understood that column types were effectively a hint to sqlite and didn't really have an effect on the semantics of queries. But I ran into this case wherein the column types of columns in tables being joined seems to determine whether an index is used or not. Here's my sample code.

Re: [sqlite] Anybody know why mono SQLite uses SqliteConnection but Windows uses SQLiteConnection?

> > Do any of you know why the two different versions of SQLite have different > case in the method names? > > Thanks, > Jim > Is there a reason why you can't use this one which should have the correct case?

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On Thu, Nov 17, 2016 at 04:48:20PM +0200, Paul wrote: > Replacing JOIN does not help either: > > sqlite> EXPLAIN QUERY PLAN SELECT foo.id FROM bar JOIN foo ON bar.foo = > foo.id ORDER BY id DESC LIMIT 0, 40; > selectidorder fromdetail >

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On Thu, Nov 17, 2016 at 09:54:01AM -0500, Richard Hipp wrote: > Standard SQL requires an implied NOT NULL on all PRIMARY KEY columns. > But due to a coding error, early versions of SQLite did not enforce > that, and so we have taken care not to enforce it on all subsequent > versions of SQLite to

Re: [sqlite] Anybody know why mono SQLite uses SqliteConnection but Windows uses SQLiteConnection?

On 2016/11/17 6:07 PM, Jim Henderson wrote: I am adapting a large open-source Windows program to Linux, using MonoDevelop. I find that the SQLite method names in Windows look like this: SQLiteConnection but in Mono.Data.Sqlite they look like this: SqliteConnection with different case, so I

[sqlite] Anybody know why mono SQLite uses SqliteConnection but Windows uses SQLiteConnection?

I am adapting a large open-source Windows program to Linux, using MonoDevelop. I find that the SQLite method names in Windows look like this: SQLiteConnection but in Mono.Data.Sqlite they look like this: SqliteConnection with different case, so I got lots of reference errors. With help from

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> On 11/17/16, Paul wrote: > > > >> On 11/17/16, Richard Hipp wrote: > >> > On 11/17/16, Paul wrote: > >> >> That's why there was a LEFT JOIN in the first place, but as it seems, > >> >> it > >> >> wasn't that good idea. > >> > > >> > Try using CROSS JOIN instead of just JOIN or LEFT JOIN.

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

Yeah.. I know that. Missed the PRIMARY KEY. :( Back to lurking. ;) -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, November 17, 2016 9:54 AM To: SQLite mailing list

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On 11/17/16, Paul wrote: > >> On 11/17/16, Richard Hipp wrote: >> > On 11/17/16, Paul wrote: >> >> That's why there was a LEFT JOIN in the first place, but as it seems, >> >> it >> >> wasn't that good idea. >> > >> > Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> On 11/17/16, Richard Hipp wrote: > > On 11/17/16, Paul wrote: > >> That's why there was a LEFT JOIN in the first place, but as it seems, it > >> wasn't that good idea. > > > > Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The query > > planner in SQLite will not reorder a CROSS

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On 11/17/16, Richard Hipp wrote: > On 11/17/16, Paul wrote: >> That's why there was a LEFT JOIN in the first place, but as it seems, it >> wasn't that good idea. > > Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The query > planner in SQLite will not

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On 11/17/16, Paul wrote: > That's why there was a LEFT JOIN in the first place, but as it seems, it > wasn't that good idea. Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The query planner in SQLite will not reorder a CROSS JOIN. -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On 11/17/16, Marc L. Allen wrote: > I'm not sure that's a valid trick, as bar.foo can be NULL, in which case the > LEFT join still returns it, but an INNER join does not. Unless sqlite > infers a NOT NULL on bar.foo? The bar.foo column is an INTEGER PRIMARY KEY,

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> On 11/17/16, Marc L. Allen wrote: > > Maybe I'm missing something, but... > > > > ORDER BY id > > > > Is ordering by the ID the right-hand side of a LEFT join. As such, it > > depends on how NULL factors into an ORDER BY. If NULL comes first, it has > > to find enough records where the LEFT

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

I'm not sure that's a valid trick, as bar.foo can be NULL, in which case the LEFT join still returns it, but an INNER join does not. Unless sqlite infers a NOT NULL on bar.foo? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of

Re: [sqlite] SQLite as a Shell Script

actually, if a file is executable and missing a proper shebang, it is just interpreted by the shell. But getting that executable flag is indeed not trivial. On Wed, Nov 16, 2016 at 1:17 PM Rowan Worth wrote: > Not true. You can go ahead and create a database called "ls", but: >

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On 11/17/16, Marc L. Allen wrote: > Maybe I'm missing something, but... > > ORDER BY id > > Is ordering by the ID the right-hand side of a LEFT join. As such, it > depends on how NULL factors into an ORDER BY. If NULL comes first, it has > to find enough records

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

Maybe I'm missing something, but... ORDER BY id Is ordering by the ID the right-hand side of a LEFT join. As such, it depends on how NULL factors into an ORDER BY. If NULL comes first, it has to find enough records where the LEFT join fails. Yeah.. I'm probably missing something.

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

It's not a solution, because in your example, foo will be scanned until limit is reached. This may take considerable amount of time, if results are parse. Also, this solution is totally useless. It's a partial index on 'foo', meaning that I can know true or false having only rows of 'foo'. In

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

Maybe you are looking for SELECT id,baz from foo where exists( select 1 from bar where foo = id) ... which has the effect of easily extending to an arbitrary number of bar tables via additional exists subqueries that may be connected by logical operators -Ursprüngliche Nachricht- Von:

[sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

These are the queries: CREATE TABLE foo( idINTEGER, baz INTEGER, PRIMARY KEY(id) ); CREATE TABLE bar( foo INTEGER, PRIMARY KEY(foo), FOREIGN KEY(foo) REFERENCES foo(id) ON DELETE CASCADE ); EXPLAIN QUERY PLAN SELECT foo.id, baz FROM bar LEFT JOIN foo ON

Re: [sqlite] Changing ID's to UUID

On 11/17/16 6:10 AM, R Smith wrote: On 2016/11/17 10:48 AM, J Decker wrote: https://en.wikipedia.org/wiki/Birthday_problem /.../ one duplicate. In other words, only after generating 1 billion UUIDs every second for the next 100 years, the probability of creating just one duplicate would be

[sqlite] Can different tokenizers be used in different columns in fts5?

I use fts5 create virtual table like this "CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5 (title, content)" I want the title can use my custom tokenizer, and the content column use unicode61 tokenizer。 such as CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5 (title, tokenize =

[sqlite] Can different tokenizers be used in different columns in fts5?

I use fts5 create virtual table like this "CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5 (title, content)" I want the title can use my custom tokenizer, and the content column use unicode61 tokenizer。 such as CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5 (title, tokenize =

Re: [sqlite] Changing ID's to UUID

On 2016/11/17 10:48 AM, J Decker wrote: https://en.wikipedia.org/wiki/Birthday_problem /.../ one duplicate. In other words, only after generating 1 billion UUIDs every second for the next 100 years, the probability of creating just one duplicate would be about 50%. All correct, but sounding

Re: [sqlite] Changing ID's to UUID

Re Integer shorness... UUID does seem wasteful - to have to ccompare such a long value. But the string compare will often fail within the first 4 bytes, or less, making many comparisons less 'work' than an integer of the same... since sqlite stores everything as text if you are in 123,000,000..

Re: [sqlite] Changing ID's to UUID

https://en.wikipedia.org/wiki/Birthday_problem In probability theory , the *birthday problem* or *birthday paradox *[1] concerns the probability