Re: [sqlite] classic update join question

[Keith Medcalf]

sqlite> create table alpha (frequency, term);
sqlite> create table beta (term, frequency);
sqlite> create index betaterm on beta(term);
sqlite> .explain
sqlite> explain query plan update alpha set frequency = (select frequency from 
beta where beta.term >= alpha.term);
sele  order  from  deta
  -    
0 0  0 SCAN TABLE alpha (~100 rows)
0 0  0 EXECUTE CORRELATED SCALAR SUBQUERY 0
0 0  0 SEARCH TABLE beta USING INDEX betaterm (term>?) 
(~25 rows)
sqlite> explain update alpha set frequency = (select frequency from beta where 
beta.term >= alpha.term);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 38000
2 Null   0 1 200
3 OpenRead   0 2 0 2  00
4 Rewind 0 8 000
5 Rowid  0 2 000
6 RowSetAdd  1 2 000
7 Next   0 5 001
8 Close  0 0 000
9 OpenWrite  0 2 0 2  00
10RowSetRead 1 36200
11NotExists  0 10200
12Null   0 3 400
13Null   0 5 000
14Integer1 6 000
15OpenRead   1 3 0 2  00
16OpenRead   2 4 0 keyinfo(1,BINARY)  00
17Column 0 1 700
18IsNull 7 28000
19SeekGe 2 287 1  00
20Column 2 0 800
21IsNull 8 27000
22IdxRowid   2 8 000
23Seek   1 8 000
24Column 1 1 900
25Move   9 5 100
26IfZero 6 28-1   00
27Next   2 20000
28Close  1 0 000
29Close  2 0 000
30SCopy  5 3 000
31Column 0 1 400
32NotExists  0 33200
33MakeRecord 3 2 8 bb 00
34Insert 0 8 2 alpha  05
35Goto   0 10000
36Close  0 0 000
37Halt   0 0 000
38Transaction0 1 000
39VerifyCookie   0 3 000
40TableLock  0 2 1 alpha  00
41TableLock  0 3 0 beta   00
42Goto   0 2 000
sqlite>

sqlite> explain query plan select alpha.term, beta.frequency from alpha, beta 
where beta.term >= alpha.term;
SELECT item[0] = {0:1}
   item[1] = {1:1}
FROM {0,*} = alpha
 {1,*} = beta
WHERE GE({1:0},{0:1})
END
sele  order  from  deta
  -    
0 0  0 SCAN TABLE alpha (~100 rows)
0 1  1 SEARCH TABLE beta USING INDEX betaterm (term>?) 
(~25 rows)
sqlite> explain select alpha.term, beta.frequency from alpha, beta where 
beta.term >= alpha.term;
SELECT item[0] = {0:1}
   item[1] = {1:1}
FROM {0,*} = alpha
 {1,*} = beta
WHERE GE({1:0},{0:1})
END
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 22000
2 OpenRead   0 2 0 2  00
3 OpenRead   1 3 0 2  00
4 OpenRead   2 4 0 keyinfo(1,BINARY)  00
5 Rewind 0 18000
6 Column 0 1 100
7 IsNull 1 17000
8 SeekGe 2 171 1  00
9 Column 2 0 200
10IsNull 2 16000
11IdxRowid   2 2 000
12Seek   1 2 000
13Column 0 1 300
14Column 1 1 400
15ResultRow  3 2 000
16Next   2 9 0

Re: [sqlite] classic update join question

[Igor Tandetnik]

On 9/5/2012 9:53 PM, Yuriy Kaminskiy wrote:

Igor Tandetnik wrote:

On 9/5/2012 12:38 PM, E. Timothy Uy wrote:

I have a column in table 'alpha' which I would like to populate with data
from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN in
sqlite, but we can

UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE
beta.term =

alpha.term)


Will the database really be doing a select in beta for
every single line in alpha?


Yes - same as when implementing a join. How do you think a join is
performed - black magic?


Subquery: O(n*log(m)), join: O(n+m). Magic!


How does O(n+m) happen, exactly? A kind of tape merge algorithm, where 
two lists are traversed in order in parallel? Last time I looked closely 
at the output of EXPLAIN (which, I admit, was a few years ago), SQLite 
never attempted anything of the sort. Does it now?


Note also that, when n is much smaller than m (note that the OP 
mentioned 100K vs several million rows), O(n*log(m)) actually beats O(n+m).

--
Igor Tandetnik

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

Re: [sqlite] classic update join question

[Simon Slavin]

On 6 Sep 2012, at 2:53am, Yuriy Kaminskiy  wrote:

> Igor Tandetnik wrote:
>> On 9/5/2012 12:38 PM, E. Timothy Uy wrote:
>>> Will the database really be doing a select in beta for
>>> every single line in alpha?
>> 
>> Yes - same as when implementing a join. How do you think a join is
>> performed - black magic?
> 
> Subquery: O(n*log(m)), join: O(n+m). Magic!
> Of course, query optimizer sometimes can rewrite subquery as join (or 
> opposite),
> but I believe (unverified!) sqlite optimizer cannot do this currently.

You can all take a look at everything mentioning JOIN in

http://www.sqlite.org/optoverview.html

and at what EXPLAIN and EXPLAIN QUERY PLAN return.  JOINs are actually 
processed as something which resembles sub-selects.  The two constructions are 
more or less equivalent and there's no great speed difference between them.

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

Re: [sqlite] classic update join question

[Yuriy Kaminskiy]

Igor Tandetnik wrote:
> On 9/5/2012 12:38 PM, E. Timothy Uy wrote:
>> I have a column in table 'alpha' which I would like to populate with data
>> from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN in
>> sqlite, but we can
>>
>> UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE
>> beta.term =
>>> alpha.term)
>>
>> Will the database really be doing a select in beta for
>> every single line in alpha?
> 
> Yes - same as when implementing a join. How do you think a join is
> performed - black magic?

Subquery: O(n*log(m)), join: O(n+m). Magic!
Of course, query optimizer sometimes can rewrite subquery as join (or opposite),
but I believe (unverified!) sqlite optimizer cannot do this currently.

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

Re: [sqlite] classic update join question

[Keith Medcalf]

I like replying to myself, obviously ...

Update alpha
   Set alpha.value = (select beta.value from beta where beta.key = alpha.key)
 Where exists (select * from beta where beta.key = alpha.key)

Could be more efficiently:

Update alpha
   Set alpha.value = coalesce((select beta.value from beta where beta.key = 
alpha.key), alpha.value)

Which is probably what query re-write would actually execute ...

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: Keith Medcalf [mailto:kmedc...@dessus.com]
> Sent: Wednesday, 05 September, 2012 18:18
> To: 'General Discussion of SQLite Database'
> Subject: RE: [sqlite] classic update join question
> 
> > Update alpha
> >Set alpha.value = beta.value
> >   From alpha, beta
> >  Where alpha.key = beta.key
> >
> > (which is how you would express an update of a join table in other DBMS's)
> >
> > Is equivalent to (and will be executed as)
> >
> > Update alpha
> >Set alpha.value = (select beta.value from beta where beta.key =
> alpha.key)
>Where exists (select * from beta where beta.key = alpha.key)
> 
> Is the correct translation.  Without it, alpha.value will be set to null
> where there is no matching beta row ...
> 
> Mutatis mutandis the second query also for the same reason.
> 
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
> 
> 
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Keith Medcalf
> > Sent: Wednesday, 05 September, 2012 18:09
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] classic update join question
> >
> >
> > I think what you are asking is whether an update through a cursor:
> >
> > Update alpha
> >Set alpha.value = beta.value
> >   From alpha, beta
> >  Where alpha.key = beta.key
> >
> > (which is how you would express an update of a join table in other DBMS's)
> >
> > Is equivalent to (and will be executed as)
> >
> > Update alpha
> >Set alpha.value = (select beta.value from beta where beta.key =
> alpha.key)
> >
> > And the answer is yes, if and only if beta.key is constrained unique.  Not
> > only are they equivalent, they are executed in exactly the same fashion.
> If
> > beta.key is not constrained unique (or the correlation condition is not an
> > equality) then the results are entirely different depending on whether the
> > first joined update is executed by a cursor or by query-rewrite to the
> > correlated form.  If it is executed through a cursor, then the alpha.value
> > will be the last match.  If it is executed as a correlated subquery, the
> > value will be the first-match.
> >
> > If beta.key is not constrained unique, then both forms are algebraically
> > indeterminate.
> >
> >
> >
> > So for example while if you did something like:
> >
> > Update alpha
> >Set alpha.value = beta.value
> >   From alpha, beta
> >  Where beta.key => alpha.key
> >
> > will not return (have the same effect) as
> >
> > update alpha
> >set alpha.value = (select beta.value from beta where beta.key =>
> > alpha.key)
> >
> > because the first update will set the alpha.value to the beta.value
> > corresponding to the "largest" beta.key => alpha.key (the last match
> > scanned), while the second will set alpha.value to the beta.value
> > corresponding to the "smallest" beta.key => alpha.key (ie, the first one
> > found).
> >
> > For the former form, the value of alpha.value will be updated for each
> > beta.value match.  For the latter correlated scaler subquery, each
> > alpha.value will only be updated once.
> >
> >
> >
> > If beta.key is not indexed performance will be "crappy" to say the least
> > because rather than doing an index seek to find the beta.value, it will do
> a
> > table scan for each row in alpha...
> >
> >
> > ---
> > ()  ascii ribbon campaign against html e-mail
> > /\  www.asciiribbon.org
> >
> > > -Original Message-
> > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > > boun...@sqlite.org] On Behalf Of E. Timothy Uy
> > > Sent: Wednesday, 05 September, 2012 14:11
> > > To: General Discussion of SQLite Database
> > > Subject: Re: [sqlite] classic update join question
> > >
> > > Dear Igor,
> > >
> > > Are you saying that
> > >
> > > UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term
> > > = alpha.term)
> > >
> > > is just as efficient as it gets and equivalent to an update using join
> (in
> > > other dbs)? That would helpful to know. I do kind of imagine some kind of
> > > black magic... ;)
> > >
> > >
> > > On Wed, Sep 5, 2012 at 12:24 PM, Igor Tandetnik 
> > wrote:
> > >
> > > > On 9/5/2012 12:38 PM, E. Timothy Uy wrote:
> > > >
> > > >> I have a column in table 'alpha' which I would like to populate with
> > data
> > > >> from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN
> > in
> > > >> sqlite, but we can
> > > >>
> > > >> 

Re: [sqlite] classic update join question

[Keith Medcalf]

> Update alpha
>Set alpha.value = beta.value
>   From alpha, beta
>  Where alpha.key = beta.key
> 
> (which is how you would express an update of a join table in other DBMS's)
> 
> Is equivalent to (and will be executed as)
> 
> Update alpha
>Set alpha.value = (select beta.value from beta where beta.key = alpha.key)
   Where exists (select * from beta where beta.key = alpha.key)

Is the correct translation.  Without it, alpha.value will be set to null where 
there is no matching beta row ... 

Mutatis mutandis the second query also for the same reason.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Keith Medcalf
> Sent: Wednesday, 05 September, 2012 18:09
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] classic update join question
> 
> 
> I think what you are asking is whether an update through a cursor:
> 
> Update alpha
>Set alpha.value = beta.value
>   From alpha, beta
>  Where alpha.key = beta.key
> 
> (which is how you would express an update of a join table in other DBMS's)
> 
> Is equivalent to (and will be executed as)
> 
> Update alpha
>Set alpha.value = (select beta.value from beta where beta.key = alpha.key)
> 
> And the answer is yes, if and only if beta.key is constrained unique.  Not
> only are they equivalent, they are executed in exactly the same fashion.  If
> beta.key is not constrained unique (or the correlation condition is not an
> equality) then the results are entirely different depending on whether the
> first joined update is executed by a cursor or by query-rewrite to the
> correlated form.  If it is executed through a cursor, then the alpha.value
> will be the last match.  If it is executed as a correlated subquery, the
> value will be the first-match.
> 
> If beta.key is not constrained unique, then both forms are algebraically
> indeterminate.
> 
> 
> 
> So for example while if you did something like:
> 
> Update alpha
>Set alpha.value = beta.value
>   From alpha, beta
>  Where beta.key => alpha.key
> 
> will not return (have the same effect) as
> 
> update alpha
>set alpha.value = (select beta.value from beta where beta.key =>
> alpha.key)
> 
> because the first update will set the alpha.value to the beta.value
> corresponding to the "largest" beta.key => alpha.key (the last match
> scanned), while the second will set alpha.value to the beta.value
> corresponding to the "smallest" beta.key => alpha.key (ie, the first one
> found).
> 
> For the former form, the value of alpha.value will be updated for each
> beta.value match.  For the latter correlated scaler subquery, each
> alpha.value will only be updated once.
> 
> 
> 
> If beta.key is not indexed performance will be "crappy" to say the least
> because rather than doing an index seek to find the beta.value, it will do a
> table scan for each row in alpha...
> 
> 
> ---
> ()  ascii ribbon campaign against html e-mail
> /\  www.asciiribbon.org
> 
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of E. Timothy Uy
> > Sent: Wednesday, 05 September, 2012 14:11
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] classic update join question
> >
> > Dear Igor,
> >
> > Are you saying that
> >
> > UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term
> > = alpha.term)
> >
> > is just as efficient as it gets and equivalent to an update using join (in
> > other dbs)? That would helpful to know. I do kind of imagine some kind of
> > black magic... ;)
> >
> >
> > On Wed, Sep 5, 2012 at 12:24 PM, Igor Tandetnik 
> wrote:
> >
> > > On 9/5/2012 12:38 PM, E. Timothy Uy wrote:
> > >
> > >> I have a column in table 'alpha' which I would like to populate with
> data
> > >> from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN
> in
> > >> sqlite, but we can
> > >>
> > >> UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term
> =
> > >>
> > >>> alpha.term)
> > >>>
> > >>
> > >> Will the database really be doing a select in beta for
> > >> every single line in alpha?
> > >>
> > >
> > > Yes - same as when implementing a join. How do you think a join is
> > > performed - black magic?
> > > --
> > > Igor Tandetnik
> > >
> > > __**_
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-
> > **users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> 

Re: [sqlite] classic update join question

[Keith Medcalf]

I think what you are asking is whether an update through a cursor:

Update alpha
   Set alpha.value = beta.value
  From alpha, beta
 Where alpha.key = beta.key

(which is how you would express an update of a join table in other DBMS's)

Is equivalent to (and will be executed as)

Update alpha
   Set alpha.value = (select beta.value from beta where beta.key = alpha.key)

And the answer is yes, if and only if beta.key is constrained unique.  Not only 
are they equivalent, they are executed in exactly the same fashion.  If 
beta.key is not constrained unique (or the correlation condition is not an 
equality) then the results are entirely different depending on whether the 
first joined update is executed by a cursor or by query-rewrite to the 
correlated form.  If it is executed through a cursor, then the alpha.value will 
be the last match.  If it is executed as a correlated subquery, the value will 
be the first-match.

If beta.key is not constrained unique, then both forms are algebraically 
indeterminate.



So for example while if you did something like:

Update alpha
   Set alpha.value = beta.value
  From alpha, beta
 Where beta.key => alpha.key

will not return (have the same effect) as

update alpha
   set alpha.value = (select beta.value from beta where beta.key => alpha.key)

because the first update will set the alpha.value to the beta.value 
corresponding to the "largest" beta.key => alpha.key (the last match scanned), 
while the second will set alpha.value to the beta.value corresponding to the 
"smallest" beta.key => alpha.key (ie, the first one found).

For the former form, the value of alpha.value will be updated for each 
beta.value match.  For the latter correlated scaler subquery, each alpha.value 
will only be updated once.



If beta.key is not indexed performance will be "crappy" to say the least 
because rather than doing an index seek to find the beta.value, it will do a 
table scan for each row in alpha...


---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of E. Timothy Uy
> Sent: Wednesday, 05 September, 2012 14:11
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] classic update join question
> 
> Dear Igor,
> 
> Are you saying that
> 
> UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term
> = alpha.term)
> 
> is just as efficient as it gets and equivalent to an update using join (in
> other dbs)? That would helpful to know. I do kind of imagine some kind of
> black magic... ;)
> 
> 
> On Wed, Sep 5, 2012 at 12:24 PM, Igor Tandetnik  wrote:
> 
> > On 9/5/2012 12:38 PM, E. Timothy Uy wrote:
> >
> >> I have a column in table 'alpha' which I would like to populate with data
> >> from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN in
> >> sqlite, but we can
> >>
> >> UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term =
> >>
> >>> alpha.term)
> >>>
> >>
> >> Will the database really be doing a select in beta for
> >> every single line in alpha?
> >>
> >
> > Yes - same as when implementing a join. How do you think a join is
> > performed - black magic?
> > --
> > Igor Tandetnik
> >
> > __**_
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-
> **users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Petite Abeille]

On Sep 5, 2012, at 4:57 PM, Richard Hipp  wrote:

> I think I'll stick with SQLite's magical processing of min())

"I think I'll stick with SQLite's magical processing of mine" -- there, I fixed 
it for you :P

> the mtime value is a floating point number, and we all know the hazards of 
> comparing floating point numbers for equality, right?

As pointed out by Igor & Olaf already… why would that ever be an issue in that 
specific scenario?

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

Re: [sqlite] classic update join question

[Petite Abeille]

On Sep 5, 2012, at 11:00 PM, Igor Tandetnik  wrote:

>> And such statement should raise an exception if the scalar sub-query returns 
>> multiple rows, no?
> 
> Definitely not in SQLite. I don't believe it would do that in other DBMS 
> either, but won't bet on it. SQLite would take the value from the first row, 
> or NULL if there are no rows.

Oracle, for example, will make sure that a scalar query stays, hmmm, scalar. If 
not, it will raise an error [1]. Ditto for Postgres [2]. After all, what's the 
"first row" of a scalar sub-query without an explicit ordering? 'Random' is 
usually how it's called.

In my opinion, this is yet again one of these rather, hmmm, "weird" behaviors 
of SQLite. A bit along the line of that "unique" 'group by' behavior discussed 
in another thread somewhere else.

One person feature is another person bug.

[1] http://docs.oracle.com/cd/B28359_01/server.111/b28286/expressions013.htm
[2] http://www.postgresql.org/docs/9.1/static/sql-expressions.html

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

Re: [sqlite] classic update join question

[Igor Tandetnik]

On 9/5/2012 4:54 PM, Petite Abeille wrote:


On Sep 5, 2012, at 10:38 PM, Igor Tandetnik  wrote:


A select statement that would most closely resemble your update statement would 
look like this:

select frequency, (select frequency from beta where beta.term = alpha.term)
from alpha;

This statement will always return exactly as many rows as there are rows in 
alpha table.


And such statement should raise an exception if the scalar sub-query returns 
multiple rows, no?


Definitely not in SQLite. I don't believe it would do that in other DBMS 
either, but won't bet on it. SQLite would take the value from the first 
row, or NULL if there are no rows.

--
Igor Tandetnik

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

Re: [sqlite] classic update join question

[Petite Abeille]

On Sep 5, 2012, at 10:38 PM, Igor Tandetnik  wrote:

> A select statement that would most closely resemble your update statement 
> would look like this:
> 
> select frequency, (select frequency from beta where beta.term = alpha.term)
> from alpha;
> 
> This statement will always return exactly as many rows as there are rows in 
> alpha table.

And such statement should raise an exception if the scalar sub-query returns 
multiple rows, no?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Igor Tandetnik]

On 9/5/2012 4:35 PM, Igor Tandetnik wrote:

On 9/5/2012 4:32 PM, E. Timothy Uy wrote:

Igor, what happens if there are multiple hits for

SELECT frequency FROM beta WHERE beta.term
= alpha.term


There can't be multiple hits, or even a single hit. This statement will
not run, as it's not syntactically valid.


A select statement that would most closely resemble your update 
statement would look like this:


select frequency, (select frequency from beta where beta.term = alpha.term)
from alpha;

This statement will always return exactly as many rows as there are rows 
in alpha table.

--
Igor Tandetnik

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

Re: [sqlite] classic update join question

[Igor Tandetnik]

On 9/5/2012 4:32 PM, E. Timothy Uy wrote:

Igor, what happens if there are multiple hits for

SELECT frequency FROM beta WHERE beta.term
= alpha.term


There can't be multiple hits, or even a single hit. This statement will 
not run, as it's not syntactically valid.

--
Igor Tandetnik

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

Re: [sqlite] classic update join question

[E. Timothy Uy]

Igor, what happens if there are multiple hits for

SELECT frequency FROM beta WHERE beta.term
= alpha.term

(there aren't but, it is possible for there to be).  Rob could be right in
a sense.

On Wed, Sep 5, 2012 at 1:28 PM, Igor Tandetnik  wrote:

> On 9/5/2012 4:20 PM, Rob Richardson wrote:
>
>> Well, I think you want a where clause on your main UPDATE query.  What
>> you wrote will set the frequency of every record in the alpha table to the
>> value from the beta table, for every record in the beta table that matches
>> an alpha record.  (It's late, I'm tired and that's incoherent.  I hope you
>> followed it.)  If you have 1000 records in the two tables that match each
>> other, every record in the alpha table will be updated 1000 times.
>>
>
> No, that's not true. Every record in alpha will be updated exactly once.
> --
> Igor Tandetnik
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Igor Tandetnik]

On 9/5/2012 4:20 PM, Rob Richardson wrote:

Well, I think you want a where clause on your main UPDATE query.  What you 
wrote will set the frequency of every record in the alpha table to the value 
from the beta table, for every record in the beta table that matches an alpha 
record.  (It's late, I'm tired and that's incoherent.  I hope you followed it.) 
 If you have 1000 records in the two tables that match each other, every record 
in the alpha table will be updated 1000 times.


No, that's not true. Every record in alpha will be updated exactly once.
--
Igor Tandetnik

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

Re: [sqlite] classic update join question

[Rob Richardson]

Well, I think you want a where clause on your main UPDATE query.  What you 
wrote will set the frequency of every record in the alpha table to the value 
from the beta table, for every record in the beta table that matches an alpha 
record.  (It's late, I'm tired and that's incoherent.  I hope you followed it.) 
 If you have 1000 records in the two tables that match each other, every record 
in the alpha table will be updated 1000 times.

Or am I merely demonstrating my ignorance?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of E. Timothy Uy
Sent: Wednesday, September 05, 2012 4:11 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] classic update join question

Dear Igor,

Are you saying that

UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term = 
alpha.term)

is just as efficient as it gets and equivalent to an update using join (in 
other dbs)? That would helpful to know. I do kind of imagine some kind of black 
magic... ;)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Igor Tandetnik]

On 9/5/2012 4:11 PM, E. Timothy Uy wrote:

Are you saying that

UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term
= alpha.term)

is just as efficient as it gets and equivalent to an update using join (in
other dbs)?


I don't know what other DBMS are doing. In SQLite, this update statement 
would have a query plan very similar to the one for


select alpha.frequency, beta.frequency from alpha, beta
where beta.term = alpha.term;

--
Igor Tandetnik

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

Re: [sqlite] classic update join question

[E. Timothy Uy]

Dear Igor,

Are you saying that

UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term
= alpha.term)

is just as efficient as it gets and equivalent to an update using join (in
other dbs)? That would helpful to know. I do kind of imagine some kind of
black magic... ;)


On Wed, Sep 5, 2012 at 12:24 PM, Igor Tandetnik  wrote:

> On 9/5/2012 12:38 PM, E. Timothy Uy wrote:
>
>> I have a column in table 'alpha' which I would like to populate with data
>> from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN in
>> sqlite, but we can
>>
>> UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term =
>>
>>> alpha.term)
>>>
>>
>> Will the database really be doing a select in beta for
>> every single line in alpha?
>>
>
> Yes - same as when implementing a join. How do you think a join is
> performed - black magic?
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXTERNAL: Re: sqlite3_trace() threadsafe

[Sum, Eric B]

" It is not mentioned by the docs that your trace callback will be called 
encapsulated by a mutex - In other words: Are you sure that your trace callback 
is threadsafe ?"

Thanks for the suggestion Marcus.  I have tried encapsulating the callback with 
a mutex to make it threadsafe.  However, that yielded the same behavior.  I 
appreciate your suggestion.

-Eric

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marcus Grimm
Sent: Wednesday, September 05, 2012 3:44 PM
To: General Discussion of SQLite Database
Subject: EXTERNAL: Re: [sqlite] sqlite3_trace() threadsafe

Am 2012-09-05 18:53, schrieb esum:
> Thank you so much for all help.  I really appreciate it.
>
> /"All the same, I'm guessing that
> http://www.sqlite.org/src/info/39f763bfc0will fix your problem."/
>
> I gave this new src code from the src tree a shot, but I seem to be
> getting the same behavior.
>
> Interestingly though, I tried enforcing my own serialization of the
> execution of the insert operations being called from the different
> threads with a mutex such that the code that performs the inserts is
> called sequentially from each thread, and this seemed to fix things.
> I did not see any duplicate sql statements.  (This is with the sqlite
> src provided in the link compiled with the --enable-threadsafe and
> --enable-cross-thread-connections which I assume sets the threading
> mode to serialized).

It is not mentioned by the docs that your trace callback will be called 
encapsulated by a mutex - In other words: Are you sure that your trace callback 
is threadsafe ? I can recall strange effects when doing "fprintf(stderr, ..."  
from different threads concurrently.
Just a guess.. though.

Marcus



>
> This leads me to believe that it has to do with contention/blocking
> within sqlite as Simon explains:
>
> /"What I am guessing is happening is that sometimes sqlite3_trace() is
> called, but is eventually held up because of access contention, a
> situation where it can't go ahead because the database is locked.
> Eventually the database becomes free again and this thread can go
> ahead, at which point
> _trace() gets called again."/
>
> I also tried using the sqlite_profile( ) function, but this seems to
> not keep the bound parameter values in the const char* sql statement
> text, and this also replicated statements (I could tell because an
> incorrect number of INSERTS were printed).
>
> I greatly appreciate any other thoughts on what my problem could be.
>
>
>
> --
> View this message in context:
>
> http://sqlite.1065341.n5.nabble.com/sqlite3-trace-threadsafe-tp64004p6
> 4030.html Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

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

Re: [sqlite] sqlite3_trace() threadsafe

[Marcus Grimm]

Am 2012-09-05 18:53, schrieb esum:

Thank you so much for all help.  I really appreciate it.

/"All the same, I'm guessing that
http://www.sqlite.org/src/info/39f763bfc0will fix your problem."/

I gave this new src code from the src tree a shot, but I seem to be 
getting

the same behavior.

Interestingly though, I tried enforcing my own serialization of the
execution of the insert operations being called from the different 
threads

with a mutex such that the code that performs the inserts is called
sequentially from each thread, and this seemed to fix things.  I did 
not see
any duplicate sql statements.  (This is with the sqlite src provided 
in the

link compiled with the --enable-threadsafe and
--enable-cross-thread-connections which I assume sets the threading 
mode to

serialized).


It is not mentioned by the docs that your trace callback will be called
encapsulated by a mutex - In other words: Are you sure that your
trace callback is threadsafe ? I can recall strange effects when doing
"fprintf(stderr, ..."  from different threads concurrently.
Just a guess.. though.

Marcus





This leads me to believe that it has to do with contention/blocking 
within

sqlite as Simon explains:

/"What I am guessing is happening is that sometimes sqlite3_trace() 
is
called, but is eventually held up because of access contention, a 
situation
where it can't go ahead because the database is locked.  Eventually 
the
database becomes free again and this thread can go ahead, at which 
point

_trace() gets called again."/

I also tried using the sqlite_profile( ) function, but this seems to 
not
keep the bound parameter values in the const char* sql statement 
text, and
this also replicated statements (I could tell because an incorrect 
number of

INSERTS were printed).

I greatly appreciate any other thoughts on what my problem could be.



--
View this message in context:

http://sqlite.1065341.n5.nabble.com/sqlite3-trace-threadsafe-tp64004p64030.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Petite Abeille]

On Sep 5, 2012, at 3:03 PM, Richard Hipp  wrote:

>SELECT id, a, b, ..., min(mtime)
>FROM tab
>GROUP BY id
>ORDER BY min(mtime) DESC;

Perhaps something along these lines, i.e. a simple self-join:

selectfoo.*
from  foo
join  (
selectid,
  min( mtime ) as mtime
from  foo
  
group by  id
  )
asMinFoo
onMinFoo.id = foo.id
and   MinFoo.mtime = foo.mtime

order by  foo.mtime desc


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

Re: [sqlite] classic update join question

[Igor Tandetnik]

On 9/5/2012 12:38 PM, E. Timothy Uy wrote:

I have a column in table 'alpha' which I would like to populate with data
from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN in
sqlite, but we can

UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term =

alpha.term)


Will the database really be doing a select in beta for
every single line in alpha?


Yes - same as when implementing a join. How do you think a join is 
performed - black magic?

--
Igor Tandetnik

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

Re: [sqlite] (no subject)

[Rui Maciel]

On 09/05/2012 06:36 PM, Arbol One wrote:

That would be the C++ Standard Template Library
http://en.wikipedia.org/wiki/Standard_Template_Library

I am learning the library and as a student I always have questions.


The STL isn't necessarily a project, as it is defined in the C++ 
standard and therefore is a part of the C++ programming language.


If you are looking for public forums where the STL is discussed, you 
could give Usenet's comp.lang.c++ a try.  If you don't have a usenet 
client around, here's a google news link:


https://groups.google.com/forum/?fromgroups=#!forum/comp.lang.c++


Hope this helps,
Rui Maciel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_trace() threadsafe

[esum]

Thank you so much for all help.  I really appreciate it.

/"All the same, I'm guessing that
http://www.sqlite.org/src/info/39f763bfc0will fix your problem."/

I gave this new src code from the src tree a shot, but I seem to be getting
the same behavior.

Interestingly though, I tried enforcing my own serialization of the
execution of the insert operations being called from the different threads
with a mutex such that the code that performs the inserts is called
sequentially from each thread, and this seemed to fix things.  I did not see
any duplicate sql statements.  (This is with the sqlite src provided in the
link compiled with the --enable-threadsafe and
--enable-cross-thread-connections which I assume sets the threading mode to
serialized).

This leads me to believe that it has to do with contention/blocking within
sqlite as Simon explains:

/"What I am guessing is happening is that sometimes sqlite3_trace() is
called, but is eventually held up because of access contention, a situation
where it can't go ahead because the database is locked.  Eventually the
database becomes free again and this thread can go ahead, at which point
_trace() gets called again."/

I also tried using the sqlite_profile( ) function, but this seems to not
keep the bound parameter values in the const char* sql statement text, and
this also replicated statements (I could tell because an incorrect number of
INSERTS were printed).

I greatly appreciate any other thoughts on what my problem could be.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/sqlite3-trace-threadsafe-tp64004p64030.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] classic update join question

[E. Timothy Uy]

Hi sqlite-users,

I have a column in table 'alpha' which I would like to populate with data
from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN in
sqlite, but we can

UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term =
> alpha.term)


or we can create a temp table with the joined data and then drop and
replace alpha. How inefficient is the former method? Is it "ok"?  In this
case, alpha is about 100k rows and beta might be several million. The
'term' is indexed. Will the database really be doing a select in beta for
every single line in alpha?

Respectfully,
Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Olaf Schmidt]

Am 05.09.2012 16:57, schrieb Richard Hipp:

On Wed, Sep 5, 2012 at 10:53 AM, Olaf Schmidt  wrote:


Am 05.09.2012 15:58, schrieb Igor Tandetnik:


  Well, you could do something like this:


SELECT id, a, b, ..., mtime FROM tab t1
where mtime = (select min(mtime) from tab t2 where t2.id=t1.id)
ORDER BY mtime DESC;



Ah, nice ... this solves the problem of the potential
"non-uniqueness" of mtime... (was fiddeling along with
something like that too, but so far without a result).

Just for completeness (in case mtime *is* unique):


SELECT id, a, b, ..., mtime FROM tab
WHERE mtime IN (SELECT Min(mtime) FROM tab GROUP BY id)
ORDER BY mtime DESC



In the actual use case that inspired this question (and for which, after
seeing the alternatives, I think I'll stick with SQLite's magical
processing of min())


Sure, I've always seen this special Grouping-behaviour of SQLite
as a feature (when no "SQL-portability" needed to be considered).


the mtime value is a floating point number, and we all
know the hazards of comparing floating point numbers for equality, right?


Hmm, out of interest, considering that in this case - where the
query doesn't contain any Float/String-conversion and no dynamic
calculations (just plain Byte-by-Byte comparison of the stored
SQLite.Values?) - would it really be that risky?


Olaf

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

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Igor Tandetnik]

Richard Hipp  wrote:
> In the actual use case that inspired this question (and for which, after
> seeing the alternatives, I think I'll stick with SQLite's magical
> processing of min()) the mtime value is a floating point number, and we all
> know the hazards of comparing floating point numbers for equality, right?

This applies when the floating point number is the result of calculations. If 
you just read a number twice from the same source, the two copies should 
compare equal just fine.
-- 
Igor Tandetnik

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

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Richard Hipp]

On Wed, Sep 5, 2012 at 10:53 AM, Olaf Schmidt  wrote:

> Am 05.09.2012 15:58, schrieb Igor Tandetnik:
>
>
>  Well, you could do something like this:
>>
>> SELECT id, a, b, ..., mtime FROM tab t1
>> where mtime = (select min(mtime) from tab t2 where t2.id=t1.id)
>> ORDER BY mtime DESC;
>>
>
> Ah, nice ... this solves the problem of the potential
> "non-uniqueness" of mtime... (was fiddeling along with
> something like that too, but so far without a result).
>
> Just for completeness (in case mtime *is* unique):
>
>
> SELECT id, a, b, ..., mtime FROM tab
> WHERE mtime IN (SELECT Min(mtime) FROM tab GROUP BY id)
> ORDER BY mtime DESC
>

In the actual use case that inspired this question (and for which, after
seeing the alternatives, I think I'll stick with SQLite's magical
processing of min()) the mtime value is a floating point number, and we all
know the hazards of comparing floating point numbers for equality, right?


>
> Olaf
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



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

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Olaf Schmidt]

Am 05.09.2012 15:58, schrieb Igor Tandetnik:


Well, you could do something like this:

SELECT id, a, b, ..., mtime FROM tab t1
where mtime = (select min(mtime) from tab t2 where t2.id=t1.id)
ORDER BY mtime DESC;


Ah, nice ... this solves the problem of the potential
"non-uniqueness" of mtime... (was fiddeling along with
something like that too, but so far without a result).

Just for completeness (in case mtime *is* unique):

SELECT id, a, b, ..., mtime FROM tab
WHERE mtime IN (SELECT Min(mtime) FROM tab GROUP BY id)
ORDER BY mtime DESC

Olaf

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

Re: [sqlite] (no subject)

[Igor Tandetnik]

Arbol One  wrote:
> Does anyone know if there is a mailing list for the STL project?

Which of these do you mean by STL?

http://en.wikipedia.org/wiki/Standard_Template_Library
http://en.wikipedia.org/wiki/State_logic
http://en.wikipedia.org/wiki/Subtitle_(captioning)
http://en.wikipedia.org/wiki/STL_(file_format)
http://en.wikipedia.org/wiki/Statement_List

Also, how is your question related to SQLite?
-- 
Igor Tandetnik

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

[sqlite] (no subject)

[Arbol One]

Does anyone know if there is a mailing list for the STL project?

 

This e-mail is for the sole use of the intended recipient and may contain
confidential or privileged information. Unauthorized use of its contents is
prohibited. If you have received this e-mail in error, please notify sender
immediately via return e-mail and then delete the original e-mail.

 

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

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Igor Tandetnik]

Richard Hipp  wrote:
> Suppose you have a table like this:
> 
>CREATE TABLE tab(ID, A, B, C, ..., MTIME);
> 
> The ID entries are not unique.  For each ID occurrence, the values for A,
> B, C, and so forth might (or might not) be different.  We do queries like
> this:
> 
>SELECT id, a, b, ..., mtime
>FROM tab
>ORDER BY mtime DESC;
> 
> *The problem to solve* is this:  We want to restrict the output of the
> query above to show only the entry with the smallest MTIME for each
> distinct ID.
> 
> *Question*:  Is there a (better) standard SQL way to do the same thing?

Well, you could do something like this:

SELECT id, a, b, ..., mtime FROM tab t1
where mtime = (select min(mtime) from tab t2 where t2.id=t1.id)
ORDER BY mtime DESC;

or

SELECT id, a, b, ..., mtime FROM tab t1
where not exists (select 1 from tab t2 where t2.id = t1.id and t2.mtime > 
t1.mtime)
ORDER BY mtime DESC;

I believe these queries are standard, but are likely to be much slower than the 
original.
-- 
Igor Tandetnik

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

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Baruch Burstein]

Temporary table? Then you will have rowids.

On Wed, Sep 5, 2012 at 4:16 PM, Richard Hipp  wrote:

> On Wed, Sep 5, 2012 at 9:12 AM, Baruch Burstein  >wrote:
>
> >  SELECT id, a, b, ..., mtime
> > FROM tab
> > WHERE rowid IN (
> > SELECT id, min(mtime)
> > FROM tab
> > GROUP BY id
> > )
> > ORDER BY mtime DESC;
> >
>
> In the actual application, "tab" is not a real table but is a join:
>
>  http://www.fossil-scm.org/fossil/artifact/5dfd626877f?ln=271-273
>
> So there isn't a ROWID available to select from.
>
>
> >
> > On Wed, Sep 5, 2012 at 4:03 PM, Richard Hipp  wrote:
> >
> > >
> > > SELECT id, a, b, ..., min(mtime)
> > > FROM tab
> > > GROUP BY id
> > > ORDER BY min(mtime) DESC;
> >
> >
> >
> >
> > --
> > ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Simon Slavin]

On 5 Sep 2012, at 2:03pm, Richard Hipp  wrote:

> *The problem to solve* is this:  We want to restrict the output of the
> query above to show only the entry with the smallest MTIME for each
> distinct ID.
> 
> As of SQLite version 3.7.11
> the problem can be
> solved like this:
> 
>SELECT id, a, b, ..., min(mtime)
>FROM tab
>GROUP BY id
>ORDER BY min(mtime) DESC;
> 
> SQLite, unique among SQL database engines as far as I know, will choose for
> A, B, C, ... the values of those columns that correspond to the row that
> satisfies the min(mtime) aggregate.  And so this actually works.  On SQLite
> versions prior to 3.7.11, the values of A, B, C, .. would be from any
> arbitrary row having the corresponding ID.  And on more strictly conforming
> SQL implementations (ex: PostgreSQL) the query will not even parse because
> the GROUP BY clause does not contain every column that is not within an
> aggregate function.  But the query does work, and works efficiently and
> well, in SQLite and seems very convenient.
> 
> *Question*:  Is there a (better) standard SQL way to do the same thing?

For the sake of completeness, I supply an answer you may reject: do it in two 
SELECTs.  The first one just identifies something unique about the row which 
has the entry with the smallest mtime -- perhaps the mtime value itself if 
that's unique and indexed, otherwise rowid.  The second one gets the values of 
a, b, c, etc. from that row.  Will work for all SQL engines, and is not 
dependent on obscure (even if documented) behaviour of GROUP BY ORDER BY.

I've noticed that the majority of people posting here seem to prefer one long 
complicated query which requires detailed knowledge of SQL syntax to two simple 
queries.

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

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Richard Hipp]

On Wed, Sep 5, 2012 at 9:12 AM, Baruch Burstein wrote:

>  SELECT id, a, b, ..., mtime
> FROM tab
> WHERE rowid IN (
> SELECT id, min(mtime)
> FROM tab
> GROUP BY id
> )
> ORDER BY mtime DESC;
>

In the actual application, "tab" is not a real table but is a join:

 http://www.fossil-scm.org/fossil/artifact/5dfd626877f?ln=271-273

So there isn't a ROWID available to select from.


>
> On Wed, Sep 5, 2012 at 4:03 PM, Richard Hipp  wrote:
>
> >
> > SELECT id, a, b, ..., min(mtime)
> > FROM tab
> > GROUP BY id
> > ORDER BY min(mtime) DESC;
>
>
>
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Baruch Burstein]

Sorry, that won't work.

On Wed, Sep 5, 2012 at 4:12 PM, Baruch Burstein wrote:

> SELECT id, a, b, ..., mtime
> FROM tab
> WHERE rowid IN (
> SELECT id, min(mtime)
>
> FROM tab
> GROUP BY id
> )
> ORDER BY mtime DESC;
>
>
> On Wed, Sep 5, 2012 at 4:03 PM, Richard Hipp  wrote:
>
>>
>> SELECT id, a, b, ..., min(mtime)
>> FROM tab
>> GROUP BY id
>> ORDER BY min(mtime) DESC;
>
>
>
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>
>


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Finding rows with MIN(MTIME) for all IDs

[Baruch Burstein]

 SELECT id, a, b, ..., mtime
FROM tab
WHERE rowid IN (
SELECT id, min(mtime)
FROM tab
GROUP BY id
)
ORDER BY mtime DESC;

On Wed, Sep 5, 2012 at 4:03 PM, Richard Hipp  wrote:

>
> SELECT id, a, b, ..., min(mtime)
> FROM tab
> GROUP BY id
> ORDER BY min(mtime) DESC;




-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Finding rows with MIN(MTIME) for all IDs

[Richard Hipp]

Suppose you have a table like this:

CREATE TABLE tab(ID, A, B, C, ..., MTIME);

The ID entries are not unique.  For each ID occurrence, the values for A,
B, C, and so forth might (or might not) be different.  We do queries like
this:

SELECT id, a, b, ..., mtime
FROM tab
ORDER BY mtime DESC;

*The problem to solve* is this:  We want to restrict the output of the
query above to show only the entry with the smallest MTIME for each
distinct ID.

As of SQLite version 3.7.11
the problem can be
solved like this:

SELECT id, a, b, ..., min(mtime)
FROM tab
GROUP BY id
ORDER BY min(mtime) DESC;

SQLite, unique among SQL database engines as far as I know, will choose for
A, B, C, ... the values of those columns that correspond to the row that
satisfies the min(mtime) aggregate.  And so this actually works.  On SQLite
versions prior to 3.7.11, the values of A, B, C, .. would be from any
arbitrary row having the corresponding ID.  And on more strictly conforming
SQL implementations (ex: PostgreSQL) the query will not even parse because
the GROUP BY clause does not contain every column that is not within an
aggregate function.  But the query does work, and works efficiently and
well, in SQLite and seems very convenient.

*Question*:  Is there a (better) standard SQL way to do the same thing?



*Context*:  The actual application here is Fossil, and the display of all
changes to a particular file in a repository.  Queries like the above are
used to generate, for example, a complete history of the changes to the
date/time function logic in SQLite:

 http://www.sqlite.org/src/finfo?name=src/date.c

In this example, the "ID" column corresponds to a particular version (SHA1
hash) of a file.  That same version might appear in various branches and so
there are duplicate entries in the graph for the same file, which can be
confusing.  (Note, the actual
queryto
render the "finfo" page is slightly more complex; "tab" is really a
join
of two tables, and the "a, b, c, .." columns are subqueries.  But these
complications do not bare upon the question of this email.)  To work around
the confused output, we'd like to only see a single row for each ID,
specifically the row with the smallest value of mtime, which means the
first time that a particular version of a file appears in the repository.
For example:

http://www.sqlite.org/src/finfo?name=src/date.c=1

The recent change to Fossil to add the "fco" query parameter to the "finfo"
page is the first time I have actually needed SQLite's min()/max() query
behavior myself, and I now find it *very* convenient.  But, I am slightly
troubled by depending on SQL behavior that is only found in SQLite in not
in other database engines.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Query Optimization (using Limit and Offset)

[Igor Tandetnik]

sattu  wrote:
> select * from myTable LIMIT 100 OFFSET 0   //Execution Time is less than
> 1sec
> select * from myTable LIMIT 100 OFFSET 95000   //Execution Time is almost
> 15secs

http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor

-- 
Igor Tandetnik

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

Re: [sqlite] Sqlite Query Optimization (using Limit and Offset)

[Simon Slavin]

On 4 Sep 2012, at 3:56pm, sattu  wrote:

> What I observed is, if the offset is very high like say 9, then it takes
> more time for the query to execute. Following is the time difference between
> 2 queries with different offsets:
> 
> 
> select * from myTable LIMIT 100 OFFSET 0   //Execution Time is less than
> 1sec
> select * from myTable LIMIT 100 OFFSET 95000   //Execution Time is almost
> 15secs

These queries are not useful, since you have not specified an ORDER BY clause 
in your SELECT.  If you don't specify an ORDER BY clause, then SQL is allowed 
to sort the records two different ways for two different SELECT commands, which 
would mean that some rows might appear in both queries, or and some not in 
either.  If you intend to use OFFSET then you should be using ORDER BY too.

> Can anyone suggest me how to optimize this query? I mean, the Query
> Execution Time should be same and fast for any number of records I wish to
> retrieve from any OFFSET.

The problem is that use of OFFSET 95000 requires SQLite to do a search which 
/does/ include the first 95000 rows, then quickly run through 95000 rows and 
discard them before returning the first result to your program.  Discarding the 
95000 results you don't want is what's taking the time.  I don't know how your 
program is written but it might be possible to use a key column to allow SQLite 
to find the right rows immediately.

For example, suppose you were using a key column called 'rowid' for your SELECT 
command:

SELECT * FROM myTable ORDER BY rowid LIMIT 100

To have this SELECT run quickly, you would make sure you had an index on your 
'rowid' column.  If your 'rowid' column is the real INTEGER PRIMARY KEY for the 
table, then SQLite will automatically create such an index for you without you 
having to ask for it.

Then instead of using OFFSET to make SQLite discard 95000 results, you use 
WHERE to specify the records you're interested in.  In your software you keep 
track of the last value for rowid returned and use that when writing the next 
command.  So your first SELECT might be

SELECT * FROM myTable WHERE rowid > -1 ORDER BY rowid LIMIT 100

and perhaps the last row returned by this has a rowid of 104.  Then your next 
SELECT should be

SELECT * FROM myTable WHERE rowid > 104 ORDER BY rowid LIMIT 100

and perhaps the last row returned by this has a rowid of 205.  Then your next 
SELECT should be

SELECT * FROM myTable WHERE rowid > 205 ORDER BY rowid LIMIT 100

If you use SELECT commands like this, SQLite can use the index on the rowid 
column to zoom straight to the first row it should return.  It could do this in 
far less than a second, no matter how big your value for rowid is.

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

[sqlite] Sqlite Query Optimization (using Limit and Offset)

[sattu]

Following is the query that I use for getting a fixed number of records from
a database with millions of records:-

select * from myTable LIMIT 100 OFFSET 0


What I observed is, if the offset is very high like say 9, then it takes
more time for the query to execute. Following is the time difference between
2 queries with different offsets:


select * from myTable LIMIT 100 OFFSET 0   //Execution Time is less than
1sec
select * from myTable LIMIT 100 OFFSET 95000   //Execution Time is almost
15secs


Can anyone suggest me how to optimize this query? I mean, the Query
Execution Time should be same and fast for any number of records I wish to
retrieve from any OFFSET.


-Thanks in advance



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Sqlite-Query-Optimization-using-Limit-and-Offset-tp64000.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users