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

2016-11-17 Thread Paul 
> 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 explained that in the original message. 'foo' is a master table. There 
are more tables like 'baz' that play role of a partial index.
Query is built dynamically from some collection of data. As a result, various 
combinations of 'foo', 'baz' and co may be in the  resulting query.
The only certain thing is that 'foo' is always in the query, hence the 'id'. 
 
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread Kevin O'Gorman 
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 the poiint where it needed to calculate more.  I get core files, but
don't know how to interpret them.

The machine is a new i7 MOBO with 32 GB RAM, 32 GB swap (mostly unused),
running Xubuntu Linux. The last run left me with a 16 GB database and a
600MB journal.

SO: I need help bifurcating this problem.  For instance, how can I tell if
the fault lies in SQLite, or in python? Or even in the hardware, given that
the time to failure is so variable?

Can anyone help me make sense of the core file?

-- 
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite as a Shell Script

2016-11-17 Thread Rowan Worth 
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 file
tcsh 6.17.00 says: /tmp/db: /tmp/db: cannot execute binary file
busybox ash 1.15.1 says: /tmp/db: /tmp/db: cannot execute binary file

When explicitly pointed at the db (ie. "zsh /tmp/db"), bash was the only
one to complain. So in general it seems there's a difference between the
treatment of an executable-file-sans-shebang and
script-named-on-command-line.

-Rowan

On 17 November 2016 at 22:31, Wout Mertens  wrote:

> 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:
> >
> > a) it will not be executable, and
> > b) the file will start with "SQLite format 3" rather than the requisite
> > "#!/bin/busybox ash"
> >
> > So the sysadmin would still have to manually execute the "script" with an
> > appropriate shell.
> > -Rowan
> >
> > On 16 November 2016 at 18:56, Wout Mertens 
> wrote:
> >
> > > well, if you have a sysadmin that has "." in their path, you could
> write
> > a
> > > database called "ls" or common misspellings of commands, and that way
> > > execute arbitrary code if the sysadmin visits the system.
> > >
> > > Wout.
> > >
> > > On Wed, Nov 16, 2016 at 11:43 AM Rowan Worth  wrote:
> > >
> > > > Interesting but doesn't seem overly practical. If you're in a
> postiion
> > to
> > > > run 'busybox ash /foo/bar' then you can easily create a shell script
> > > > through simpler means.
> > > >
> > > > Filesystem access via ATTACH DATABASE is worth bringing attention to
> > > > though, as I think a lot of developers wouldn't expect that. It can
> be
> > > > mitigated via something like sqlite3_limit(db, SQLITE_LIMIT_ATTACHED,
> > 1)
> > > if
> > > > you don't need ATTACH functionality (maybe with a limit of zero?
> > haven't
> > > > tested it).
> > > >
> > > > Of course if you are paying proper attention to security you should
> > > already
> > > > be onto the SQL injection vectors that put ATTACH in user's hands
> > anyway
> > > :)
> > > >
> > > > -Rowan
> > > >
> > > > On 16 November 2016 at 07:10, jungle Boogie  >
> > > > wrote:
> > > >
> > > > > Hi All,
> > > > >
> > > > > Pretty interesting article:
> > > > > https://www.invincealabs.com/blog/2016/11/sqlite-shell-script/
> > > > >
> > > > > This post documents how we were able to create a SQLite database
> that
> > > > > can be executed as an ash shell script purely from SQL queries.
> > > > >
> > > > >
> > > > > Found here:
> > > > >
> > > > https://www.reddit.com/r/netsec/comments/5cwb07/sqlite_
> > > as_a_shell_script/
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > ---
> > > > > inum: 883510009027723
> > > > > sip: jungleboo...@sip2sip.info
> > > > > ___
> > > > > 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
> >
> ___
> 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] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Dimitris Bil 
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



From: sqlite-users  on behalf of 
Nico Williams 
Sent: Thursday, November 17, 2016 4:32 PM
To: SQLite mailing list
Subject: 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
> --  --  --  
> 
> 0   0   1   SCAN TABLE foo
> 0   1   0   SEARCH TABLE bar USING INTEGER PRIMARY 
> KEY (rowid=?)
>
> My guess is it's because there are no sqlite_stat* tables and SQLite
> doesn't know that bar scan is more efficient.  That's why there was a
> LEFT JOIN in the first place, but as it seems, it wasn't that good
> idea.

If you want the ORDER BY to not sort (either as results are generated or
after, but either way before the application sees a single result) then
you kinda need to scan foo first.  Is there any other way?  I don't
think so, but correct me!

Nico
--
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
sqlite-users Info 
Page
mailinglists.sqlite.org
To see the collection of prior postings to the list, visit the sqlite-users 
Archives. (The current archive is only available to the list ...


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] column type impact on index usage

2016-11-17 Thread Richard Hipp 
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 bar (value integer, unique(value));
> CREATE TABLE bar1 (value, unique(value));
> COMMIT;
> sqlite>
> sqlite> explain query plan SELECT * FROM seq LEFT OUTER JOIN bar1 ON
> seq.value = bar1.value;
> 0|0|0|SCAN TABLE seq
> 0|1|1|SCAN TABLE bar1

This is so because of
https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison

The first bullet in the paragraph above is the culprit.  If a TEXT
value that looks like an integer is stored in bar1.value, then it has
to be converted into an integer before the comparison.  But that
conversion potentially changes the order (ex: "10" comes before "9")
which means that the index cannot be used for the lookup.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] column type impact on index usage

2016-11-17 Thread Mark Wagner 
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.  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.

Any thoughts on this?

Thanks.

-- Mark

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE seq (value integer);
CREATE TABLE bar (value integer, unique(value));
CREATE TABLE bar1 (value, unique(value));
COMMIT;
sqlite>
sqlite> explain query plan SELECT * FROM seq LEFT OUTER JOIN bar1 ON
seq.value = bar1.value;
0|0|0|SCAN TABLE seq
0|1|1|SCAN TABLE bar1
sqlite>
sqlite> explain query plan SELECT * FROM seq LEFT OUTER JOIN bar ON
seq.value = bar.value;
0|0|0|SCAN TABLE seq
0|1|1|SEARCH TABLE bar USING COVERING INDEX sqlite_autoindex_bar_1 (value=?)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread Drago, William @ CSG - NARDA-MITEQ 
>
> 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?

https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki#sqlite-netFx451-binary-Mono-2013

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread Nico Williams 
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
>   
> --  --  --  
> 
> 0   0   1   SCAN TABLE foo
>   
> 0   1   0   SEARCH TABLE bar USING INTEGER PRIMARY 
> KEY (rowid=?) 
> 
> My guess is it's because there are no sqlite_stat* tables and SQLite
> doesn't know that bar scan is more efficient.  That's why there was a
> LEFT JOIN in the first place, but as it seems, it wasn't that good
> idea.

If you want the ORDER BY to not sort (either as results are generated or
after, but either way before the application sees a single result) then
you kinda need to scan foo first.  Is there any other way?  I don't
think so, but correct me!

Nico
-- 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread Nico Williams 
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 preserve backwards compatibility.
> 
> WITHOUT ROWID tables were added later, and so NOT NULL is properly
> enforced on all PRIMARY KEY columns in WITHOUT ROWID tables.

I've noticed this, and I'm quite happy about it.

A [persistent] pragma to make SQLite3 adhere more strictly to the
standard would be nice.  You could slowly add more strictness.  Anyone
who turns on strictness would be getting what they asked for as
subsequent add more.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread R Smith 



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 got lots of reference errors.

With help from others at stackexchange here 
 
I was able to get the projects to compile.


Do any of you know why the two different versions of SQLite have 
different case in the method names?


Yes, we know why.

It's because it's not SQLite's code directly, it is the code of 
intermediate libraries on top of SQLite made by different programmers 
whom obviously took different views of what the acronym casing should be.


So now you know WHY, but I'm afraid it's not very helpful knowing why, 
and sadly nothing we can do to alleviate it - and I guess these 
programmers have had their adaptations used a lot by now, so changing it 
now will break too much for others


I guess Search-Replace is your only salvation here.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread Jim Henderson 
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 others at stackexchange here 
 
I was able to get the projects to compile.


Do any of you know why the two different versions of SQLite have 
different case in the method names?


Thanks,
Jim

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread Paul 


> 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 query
> >> > planner in SQLite will not reorder a CROSS JOIN.
> >>
> >> But, as it turns out, if you use CROSS JOIN it goes back to sorting.
> >> Bummer.
> >
> > Yes... unfortunately.
> >
> >>
> >> Ok, a new optimization opportunity.
> >
> > So, in the upcoming releases, some of these queries may get optimised?
> > The ones with the LEFT or wit the CROSS JOIN?
> >
> 
> CROSS JOIN.  I don't think the LEFT JOIN case works, but I will confirm that.
> 

BTW, bar has a FK from foo. When FKs are on I believe SQLite can assume that 
constraints are not violated.
Hence the right part of the LEFT JOIN can never be NULL, specifically the id.
 
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread Marc L. Allen 
  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 
Subject: 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, which can never be NULL.

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 
preserve backwards compatibility.

WITHOUT ROWID tables were added later, and so NOT NULL is properly enforced on 
all PRIMARY KEY columns in WITHOUT ROWID tables.

>
> -Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of 
> Richard Hipp
> Sent: Thursday, November 17, 2016 9:32 AM
> To: SQLite mailing list 
> Subject: 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 join fails.
>>
>> Yeah.. I'm probably missing something.
>
> No, you are correct.  Since the ID column is on the RHS of a LEFT 
> JOIN, it cannot use an index for sorting.
>
> The foreign key constraint could, in theory, be used by the query 
> planner to simplify the LEFT JOIN into an ordinary INNER JOIN.  But 
> the query planner in SQLite does not currently know that trick.
>
> So, one solution is to remove the LEFT keyword from the query in the 
> application
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
>


--
D. Richard Hipp
d...@sqlite.org
___
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] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Richard Hipp 
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 query
>> > planner in SQLite will not reorder a CROSS JOIN.
>>
>> But, as it turns out, if you use CROSS JOIN it goes back to sorting.
>> Bummer.
>
> Yes... unfortunately.
>
>>
>> Ok, a new optimization opportunity.
>
> So, in the upcoming releases, some of these queries may get optimised?
> The ones with the LEFT or wit the CROSS JOIN?
>

CROSS JOIN.  I don't think the LEFT JOIN case works, but I will confirm that.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread Paul 
 
> 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 JOIN.
> 
> But, as it turns out, if you use CROSS JOIN it goes back to sorting.  Bummer.

Yes... unfortunately.

> 
> Ok, a new optimization opportunity.

So, in the upcoming releases, some of these queries may get optimised? 
The ones with the LEFT or wit the CROSS JOIN?

> 
> It gets the correct answer, though, so this is not considered a bug.
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Richard Hipp 
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 JOIN.

But, as it turns out, if you use CROSS JOIN it goes back to sorting.  Bummer.

Ok, a new optimization opportunity.

It gets the correct answer, though, so this is not considered a bug.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread Richard Hipp 
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread Richard Hipp 
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, which can never be NULL.

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 preserve backwards compatibility.

WITHOUT ROWID tables were added later, and so NOT NULL is properly
enforced on all PRIMARY KEY columns in WITHOUT ROWID tables.

>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Richard Hipp
> Sent: Thursday, November 17, 2016 9:32 AM
> To: SQLite mailing list 
> Subject: 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 join fails.
>>
>> Yeah.. I'm probably missing something.
>
> No, you are correct.  Since the ID column is on the RHS of a LEFT JOIN, it
> cannot use an index for sorting.
>
> The foreign key constraint could, in theory, be used by the query planner to
> simplify the LEFT JOIN into an ordinary INNER JOIN.  But the query planner
> in SQLite does not currently know that trick.
>
> So, one solution is to remove the LEFT keyword from the query in the
> application
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread Paul 
 
> 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 join fails.
> >
> > Yeah.. I'm probably missing something.
> 
> No, you are correct.  Since the ID column is on the RHS of a LEFT
> JOIN, it cannot use an index for sorting.

Now I see... Thanks for making it clear.

> 
> The foreign key constraint could, in theory, be used by the query
> planner to simplify the LEFT JOIN into an ordinary INNER JOIN.  But
> the query planner in SQLite does not currently know that trick.
> 
> So, one solution is to remove the LEFT keyword from the query in the
> application

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  

--  --  --  

0   0   1   SCAN TABLE foo  

0   1   0   SEARCH TABLE bar USING INTEGER PRIMARY KEY 
(rowid=?) 

My guess is it's because there are no sqlite_stat* tables and SQLite doesn't 
know that bar scan is more efficient.
That's why there was a LEFT JOIN in the first place, but as it seems, it wasn't 
that good idea.


> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Marc L. Allen 
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 Richard Hipp
Sent: Thursday, November 17, 2016 9:32 AM
To: SQLite mailing list 
Subject: 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 join fails.
>
> Yeah.. I'm probably missing something.

No, you are correct.  Since the ID column is on the RHS of a LEFT JOIN, it 
cannot use an index for sorting.

The foreign key constraint could, in theory, be used by the query planner to 
simplify the LEFT JOIN into an ordinary INNER JOIN.  But the query planner in 
SQLite does not currently know that trick.

So, one solution is to remove the LEFT keyword from the query in the 
application
--
D. Richard Hipp
d...@sqlite.org
___
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] SQLite as a Shell Script

2016-11-17 Thread Wout Mertens 
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:
>
> a) it will not be executable, and
> b) the file will start with "SQLite format 3" rather than the requisite
> "#!/bin/busybox ash"
>
> So the sysadmin would still have to manually execute the "script" with an
> appropriate shell.
> -Rowan
>
> On 16 November 2016 at 18:56, Wout Mertens  wrote:
>
> > well, if you have a sysadmin that has "." in their path, you could write
> a
> > database called "ls" or common misspellings of commands, and that way
> > execute arbitrary code if the sysadmin visits the system.
> >
> > Wout.
> >
> > On Wed, Nov 16, 2016 at 11:43 AM Rowan Worth  wrote:
> >
> > > Interesting but doesn't seem overly practical. If you're in a postiion
> to
> > > run 'busybox ash /foo/bar' then you can easily create a shell script
> > > through simpler means.
> > >
> > > Filesystem access via ATTACH DATABASE is worth bringing attention to
> > > though, as I think a lot of developers wouldn't expect that. It can be
> > > mitigated via something like sqlite3_limit(db, SQLITE_LIMIT_ATTACHED,
> 1)
> > if
> > > you don't need ATTACH functionality (maybe with a limit of zero?
> haven't
> > > tested it).
> > >
> > > Of course if you are paying proper attention to security you should
> > already
> > > be onto the SQL injection vectors that put ATTACH in user's hands
> anyway
> > :)
> > >
> > > -Rowan
> > >
> > > On 16 November 2016 at 07:10, jungle Boogie 
> > > wrote:
> > >
> > > > Hi All,
> > > >
> > > > Pretty interesting article:
> > > > https://www.invincealabs.com/blog/2016/11/sqlite-shell-script/
> > > >
> > > > This post documents how we were able to create a SQLite database that
> > > > can be executed as an ash shell script purely from SQL queries.
> > > >
> > > >
> > > > Found here:
> > > >
> > > https://www.reddit.com/r/netsec/comments/5cwb07/sqlite_
> > as_a_shell_script/
> > > >
> > > >
> > > >
> > > > --
> > > > ---
> > > > inum: 883510009027723
> > > > sip: jungleboo...@sip2sip.info
> > > > ___
> > > > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread Richard Hipp 
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 join fails.
>
> Yeah.. I'm probably missing something.

No, you are correct.  Since the ID column is on the RHS of a LEFT
JOIN, it cannot use an index for sorting.

The foreign key constraint could, in theory, be used by the query
planner to simplify the LEFT JOIN into an ordinary INNER JOIN.  But
the query planner in SQLite does not currently know that trick.

So, one solution is to remove the LEFT keyword from the query in the
application
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread Marc L. Allen 
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.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter
Sent: Thursday, November 17, 2016 8:53 AM
To: 'SQLite mailing list' 
Subject: 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-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Paul
Gesendet: Donnerstag, 17. November 2016 13:58
An: General Discussion of SQLite Database 
Betreff: [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 bar.foo = 
foo.id ORDER BY id LIMIT 10, 10;

selectidorder   fromdetail
--  --  --  
---
0   0   0   SCAN TABLE bar
0   1   1   SEARCH TABLE foo USING INTEGER PRIMARY KEY 
(rowid=?)
0   0   0   USE TEMP B-TREE FOR ORDER BY

'bar.foo = foo.id' gives a strong hint that primary key index of 'bar' can be 
used for 'ORDER BY id' ordering.
But it does not happen, hence the plan includes full scan of 'bar' and  TEMP 
B-TREE construction.


The reason behind ordering being 'ORDER BY id' instead of 'ORDER BY bar.foo'  
is that bar pays a role of partial index.
Database was created just a year before partial indexes were released. Bar is 
not a single such table, there are more.
The query is being constructed dynamically and in the end  'ORDER BY id' is 
appended to ensure that  query is correct no matter how many 
'partial-index-tables'  the foo is LEFT JOIN-ed with.


___
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
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
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] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Paul 
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 the other words: there is no need to do extra searches in 
'partial-index-tables' like bar. The idea is that those tables are orders 
of magnitude  smaller than foo, hence the LEFT JOIN to make it verbose and 
force query planner to scan 'bar' and co first.

 
> 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-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Paul
> Gesendet: Donnerstag, 17. November 2016 13:58
> An: General Discussion of SQLite Database 
> Betreff: [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 bar.foo = 
> foo.id ORDER BY id LIMIT 10, 10;
> 
> selectidorder   fromdetail
> --  --  --  
> ---
> 0   0   0   SCAN TABLE bar
> 0   1   1   SEARCH TABLE foo USING INTEGER PRIMARY 
> KEY (rowid=?)
> 0   0   0   USE TEMP B-TREE FOR ORDER BY
> 
> 'bar.foo = foo.id' gives a strong hint that primary key index of 'bar' can be 
> used for 'ORDER BY id' ordering.
> But it does not happen, hence the plan includes full scan of 'bar' and  TEMP 
> B-TREE construction.
> 
> 
> The reason behind ordering being 'ORDER BY id' instead of 'ORDER BY bar.foo'  
> is that bar pays a role of partial index.
> Database was created just a year before partial indexes were released. Bar is 
> not a single such table, there are more.
> The query is being constructed dynamically and in the end  'ORDER BY id' is 
> appended to ensure that  query is correct no matter how many 
> 'partial-index-tables'  the foo is LEFT JOIN-ed with.
> 
> 
> ___
> 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
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
> 
> This communication (including any attachments) is intended for the use of the 
> intended recipient(s) only and may contain information that is confidential, 
> privileged or legally protected. Any unauthorized use or dissemination of 
> this communication is strictly prohibited. If you have received this 
> communication in error, please immediately notify the sender by return e-mail 
> message and delete all copies of the original communication. Thank you for 
> your cooperation.
> 
> 
> ___
> 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] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Hick Gunter 
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-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Paul
Gesendet: Donnerstag, 17. November 2016 13:58
An: General Discussion of SQLite Database 
Betreff: [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 bar.foo = 
foo.id ORDER BY id LIMIT 10, 10;

selectidorder   fromdetail
--  --  --  
---
0   0   0   SCAN TABLE bar
0   1   1   SEARCH TABLE foo USING INTEGER PRIMARY KEY 
(rowid=?)
0   0   0   USE TEMP B-TREE FOR ORDER BY

'bar.foo = foo.id' gives a strong hint that primary key index of 'bar' can be 
used for 'ORDER BY id' ordering.
But it does not happen, hence the plan includes full scan of 'bar' and  TEMP 
B-TREE construction.


The reason behind ordering being 'ORDER BY id' instead of 'ORDER BY bar.foo'  
is that bar pays a role of partial index.
Database was created just a year before partial indexes were released. Bar is 
not a single such table, there are more.
The query is being constructed dynamically and in the end  'ORDER BY id' is 
appended to ensure that  query is correct no matter how many 
'partial-index-tables'  the foo is LEFT JOIN-ed with.


___
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
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread Paul 
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 bar.foo = 
foo.id ORDER BY id LIMIT 10, 10;

selectidorder   fromdetail  

--  --  --  
---
0   0   0   SCAN TABLE bar  

0   1   1   SEARCH TABLE foo USING INTEGER PRIMARY KEY 
(rowid=?)
0   0   0   USE TEMP B-TREE FOR ORDER BY 

'bar.foo = foo.id' gives a strong hint that primary key index of 'bar' can be 
used for 'ORDER BY id' ordering.
But it does not happen, hence the plan includes full scan of 'bar' and  TEMP 
B-TREE construction.


The reason behind ordering being 'ORDER BY id' instead of 'ORDER BY bar.foo'  
is that bar pays a role of partial index.
Database was created just a year before partial indexes were released. Bar is 
not a single such table, there are more.
The query is being constructed dynamically and in the end  'ORDER BY id' is 
appended to ensure that  query is correct
no matter how many 'partial-index-tables'  the foo is LEFT JOIN-ed with.
 
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changing ID's to UUID

2016-11-17 Thread Richard Damon 

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 about 50%.


All correct, but sounding misleading... Your phrase tends to sound 
like "Only after 1 billion UUIDs / sec for 100 years do I have a 50% 
chance of getting a single duplicate...".
I think you miss the math. After 100 Years of generating a Billion 
UUIDs/sec, we have generated a rough order of 3x10^18 UUIDs, or about 
2^62 UUIDs. We have 2^128 possible UUIDs, so we are no where near 50% 
chance of a duplicate for each one, and the 50% chance quoted was the 
chance that in that 3x10^18 UUIDs created, there is one pair of 
duplicates. (I haven't done the math to verify that number, but it is 
about right, we have used about 1/2 the bits, which about the point you 
expect the first duplicate)



--
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread ???? 
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 = 
'customChinese', content, tokenize = 'unicode61 ')。


Because I need the title to support Chinese Pinyin。 So I rewrote a Chinese 
customChinese to support chinese pinyin。
But the content not need to use Chinese Pinyin. So the conetent use unicode61 
tokenizer。


In this case, should I build two virtual table？
If create two virtual table, the efficiency of the query will be relatively 
lower. 
Because there may be a third columns use different  tokenizers .


Can I just create one virtual table, and use different tokenizers  in different 
columns。
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2016-11-17 Thread ???? 
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 = 
'customChinese', content, tokenize = 'unicode61 ')。


Because I need the title to support Chinese Pinyin。 So I rewrote a Chinese 
customChinese to support chinese pinyin。
But the content not need to use Chinese Pinyin. So the conetent use unicode61 
tokenizer。


In this case, should I build two virtual table？
If create two virtual table, the efficiency of the query will be relatively 
lower. 
Because there may be a third columns use different  tokenizers .


Can I just create one virtual table, and use different tokenizers  in different 
columns。
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changing ID's to UUID

2016-11-17 Thread R Smith 


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 misleading... Your phrase tends to sound like 
"Only after 1 billion UUIDs / sec for 100 years do I have a 50% chance 
of getting a single duplicate...".


The real story is: "After creating that amount of UUIDs, I have a 50% 
for every next UUID I make to be a duplicate..." - Which basically 
means, Uniqueness expectation is destroyed by that time, and in fact, 
long before that time.


What further is misleading, is that Loong before then, you will have 
a decent chance of getting duplicate UUIDs, 1%, or even 0.0001% chance 
happens billions of iterations sooner (which is already horribly bad for 
the standard programming project, meaning you'd have to actively test 
uniqueness long before this).


And even if the chance is 0.001% - there is no guarantee that any 
next UUID is unique - in fact, it is guaranteed that by a 1 in 100 
million chance, it won't be unique (so if you make 100 million UUIDs, 
not an uncommon amount, it will hit a duplicate).  It's like the Lotto 
analogy: There is an extremely small chance for you to win the lotto 
(IIRC 1 in 14 mil or so), yet every week or so, someone wins it...  
Don't confuse low probability with likelihood of an event - the 
population size dictates it.


Reminds me of this satire exchange between a probability theory 
professor and a student (I am doing this from memory, so might get it 
wrong, feel free to post the original if known):
Student: So, if I toss this coin ten times, It will land heads up five 
times?

Professor: Probably.
Student: But it will definitely get heads at least once right?
Professor: Probably.
Student:  But surely it won't land tails every time?
Professor: Probably not.
etc.

https://www.noisebridge.net/images/b/be/Conditional_risk.png

http://withfriendship.com/images/e/21237/probability-theory.gif

and...
http://xkcd.com/552/

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changing ID's to UUID

2016-11-17 Thread J Decker 
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.. 123,000,001
 takes a lot of comparison to find difference.

yes it's 'inconveneitnt' I can't remember the code for item X is 99 so
just select where id=99 - easy to type, instead I have to expect my
database browser to support copy and paste.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changing ID's to UUID

2016-11-17 Thread J Decker 
https://en.wikipedia.org/wiki/Birthday_problem

In probability theory ,
the *birthday problem* or *birthday paradox
*[1]
 concerns the
probability  that, in a set
of {\displaystyle
n}[image: n] randomly  chosen people,
some pair of them will have the same birthday
. By the pigeonhole principle
, the probability
reaches 100% when the number of people reaches 367 (since there are only
366 possible birthdays, including February 29
). However, 99.9% probability is
reached with just 70 people, and 50% probability with 23 people. These
conclusions are based on the assumption that each day of the year (except
February 29) is equally probable for a birthday.

UUID https://en.wikipedia.org/wiki/Universally_unique_identifier
Out of a total of 128 bits, Type 4 UUIDs have 6 reserved bits (4 for the
version and 2 other reserved bits), so randomly generated UUIDs have 122
random bits. The chance of two such UUIDs having the same value can be
calculated using probability theory (birthday problem
). Using the approximation

To put these numbers into perspective, the annual risk of a given person
being hit by a meteorite is estimated to be one chance in 17 billion,[5]
 which
means the probability is about 0.006 (6 × 10−11), equivalent to the
odds of creating a* few tens of trillions *of UUIDs in a year and having
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%.

-
Generating a recordset on client side - you'll have that ID in one place,
if it needs to change (on insert fails) you generate a new one and insert
again - everything else is likely related to that first field in the first
place.
If it's a case of merging datasets - how many of a type of record are you
really generating? 4? 5?

if it's a generation of millions of opints - than probably there should be
a different unique ID associated that increases them to a full unique uuid
space each.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users