[sqlite] SQLite latest version RPM download location

[Gnanakumar]

Hi,

Our production server is running SQLite v3.3.6 on CentOS release 5.2
(Final).

We're planning to upgrade SQLite to the latest version (atleast to v3.5.0)
but couldn't able to find any equivalent RPMs greater than v3.3.6.

Can somebody point me to the RPM download link location of the same?

Regards,
Gnanam

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

Re: [sqlite] INSERT OR IGNORE with rtree virtual tables

[Dan Kennedy]

>> In the shell:
>>
>> SQLite version 3.7.3
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> CREATE VIRTUAL TABLE a_rt USING rtree( _id, min_x, max_x,
>> min_y, max_y );
>> sqlite> INSERT OR IGNORE INTO a_rt ( _id, min_x, max_x, min_y,  
>> max_y )
>> VALUES( 2, 3, 4, 5, 6 );
>> sqlite> INSERT OR IGNORE INTO a_rt ( _id, min_x, max_x, min_y,  
>> max_y )
>> VALUES( 2, 3, 4, 5, 6 );
>> Error: constraint failed
>> sqlite>
>>
>> Is this a bug, or operating as designed?

Virtual tables (like r-tree and fts3) ignore ON CONFLICT
clauses. Just the way it is unfortunately.

Dan.


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

Re: [sqlite] Compiling SQLite on Solaris 64bit and SPARC?

[Lynton Grice]

Hi Dave,

I turned to compiling it manually on Solaris for now and that latest version
of SQLite compiled perfectly.

Thanks for your comment though, I will give it a try later ;-)

Lynton

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of David Kirkby
Sent: 08 November 2010 09:35 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Compiling SQLite on Solaris 64bit and SPARC?

On 8 November 2010 16:09, Lynton Grice  wrote:
> Hi all,
>
> Anybody out there got the latest version of SQLite compiled on Solaris 10
64
> bit?
>
> Perhaps I must add some options to the "./configure"?
>
> Thanks for the help ;-)
>
> Lynton

I've built sqlite-3.6.22 on 64-bit SPARC and it installs ok. I can't
speak for the latest version. I added -m64 to both CFLAGS and
CXXFLAGS. I think the latter is unnecessary, but Sqlite is used as
part of the Sage project, and we tend to build most things like that.

Dave
___
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_step to select and update the same table

[Woody & Yuni Ho]

Sql can do it. It just takes a properly designed query.


Woody wizard at large(I'm in shape. Round is a shape)
Connected by MOTOBLUR™ on T-Mobile

-Original message-
From: cricketfan 
To: sqlite-users@sqlite.org
Sent: Mon, Nov 8, 2010 19:24:54 PST
Subject: Re: [sqlite] sqlite3_step to select and update the same table


SQL will not do the trick for me because I based on the select I have to
perform other operations(queries on other tables) and only then can I update
the table in question.

Kees Nuyt wrote:
> 
> There is no need to do this in a loop with a cursor.
> Whenever possible, use the power of SQL set operations.
> As far as I can tell, an alternative solution to
> your problem could be (pseudocode):
> 
> delSql = "UPDATE table1
>SET ghi = ?
>  WHERE def IN (
>   SELECT ref 
> FROM table1
>WHERE abc = ?
>   );";
> prepare(db, delSql, ..., stmt, ...);
> bind_int(stmt, ..., ghivalue);
> bind_text(stmt, ..., abcvalue);
> step(stmt);
> reset(stmt);
> finalize(stmt);
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30166069.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] sqlite3_step to select and update the same table

[cricketfan]

SQL will not do the trick for me because I based on the select I have to
perform other operations(queries on other tables) and only then can I update
the table in question.

Kees Nuyt wrote:
> 
> There is no need to do this in a loop with a cursor.
> Whenever possible, use the power of SQL set operations.
> As far as I can tell, an alternative solution to
> your problem could be (pseudocode):
> 
> delSql = "UPDATE table1
>SET ghi = ?
>  WHERE def IN (
>   SELECT ref 
> FROM table1
>WHERE abc = ?
>   );";
> prepare(db, delSql, ..., stmt, ...);
> bind_int(stmt, ..., ghivalue);
> bind_text(stmt, ..., abcvalue);
> step(stmt);
> reset(stmt);
> finalize(stmt);
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30166069.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

Re: [sqlite] sqlite3_step to select and update the same table

[Simon Slavin]

On 8 Nov 2010, at 9:27pm, Stephen Chrzanowski wrote:

> I don't know how the code works, but logically speaking, if I'm at row B,
> and I update row B to E, row B physically remains B but has its data content
> changed to E.  From there on in, it should go on to C then D then F, etc.

And naturally, since you are reading the row and it was retrieved in 'B' order, 
it is vital that the contents of the field is 'B'.  Otherwise your code will 
not find the data in the order it has asked for it.

> Since the full rowset results already exist somewhere, it shouldn't show up
> anywhere else down the line, simply because the updated or even new
> recordset isn't part of the compiled result set list.
> 
> In other words, when I make a SELECT statement, the results that come back
> would point to the physical locations of where the raw data exists, or,
> return the data and stores it in memory, or however the mechanism works.
> Updating should not affect what rows have been called up.

So you require two copies of the data: one which is the data as it was when you 
started the SELECT command and another will all the updates that have taken 
place since then, until _finalize() is called.  This means that the SELECT 
command must reserve enough memory for the entire results of the SELECT 
command, and copy all the data into it to process the initial SELECT command.

So if I had a table which took 5 Gig of disk space and did a "SELECT * FROM 
myTable" my application would suddenly need to reserve 5 Gig of memory to store 
the results.  This would make every SELECT very slow and use a lot of memory, 
useless on platforms which need to respond to button-presses in realtime or 
have limited memory.  So I can understand why SQLite doesn't allow it.

You can do this yourself, of course: perform the entire SELECT and store the 
results in one or more variables, then use the contents of those variables to 
decide which UPDATE commands to do.  Or you can just use the UPDATE command 
with a WHERE clause, which does both commands in one go and is far more 
efficient.

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

Re: [sqlite] sqlite3_step to select and update the same table

[Kees Nuyt]

On Sun, 7 Nov 2010 10:14:29 -0800 (PST), cricketfan
 wrote:

>Just to make things clearer
>the value being fetched into ref from the database, is also the value being
>changed(ghi) in the update statement. When I change my query (just to debug)
>to update some other column in the table the whole thing runs fine and runs
>only once!
>Can someone throw some light on this?

It is still unclear to me why you insist using a cursor.
A single UPDATE statement with a proper WHERE clause should
do the job. I already suggested code for that.

Since your first example wasn't exact, and the second is not
complete I'm not really tempted to look into it.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step to select and update the same table

[Stephen Chrzanowski]

I don't know how the code works, but logically speaking, if I'm at row B,
and I update row B to E, row B physically remains B but has its data content
changed to E.  From there on in, it should go on to C then D then F, etc.

Since the full rowset results already exist somewhere, it shouldn't show up
anywhere else down the line, simply because the updated or even new
recordset isn't part of the compiled result set list.

In other words, when I make a SELECT statement, the results that come back
would point to the physical locations of where the raw data exists, or,
return the data and stores it in memory, or however the mechanism works.
Updating should not affect what rows have been called up.

On Sun, Nov 7, 2010 at 9:16 PM, Simon Slavin  wrote:

>
> On 8 Nov 2010, at 2:02am, cricketfan wrote:
>
> > Simon, As per my understanding I am getting the result set and trying to
> > change values in the table based on what I read from the result set up to
> > that point. I see no reason why I should be stopped from updating the row
> I
> > have already read in the result set.
>
> Your update might change whether the row is in the result set.  Or it might
> change /where/ the row falls in the result set.  For instance, suppose you
> have the following rows:
>
> A
> B
> C
> D
> F
>
> You read as far as B, then decide to change the B to an E.  Now what should
> happen ?  Should that record appear again when you get past the D ?  But it
> might be a problem to include the same record twice.  How should SQL know
> what it should do ?  So SQL prevents you from making changes which effect an
> open SELECT.
>
> Simon.
> ___
> 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] sqlite-3.7.3-tea help configure tcl bindings

[linux fan]

Hi,

Installed tcl8.5.8 - OK
Insalled sqlite-amalgamation-3.7.3 - OK

Installed sqlite-3.7.3-tea with:
./configure --prefix=/usr --with-system-sqlite

It installed:
/usr/lib/sqlite3.7.3/libsqlite3.7.3.so
/usr/lib/sqlite3.7.3/pkgIndex.tcl

It can work in tclsh if I do:
% load /usr/lib/sqlite3.7.3/libsqlite3.7.3.so Sqlite3

Please advise a better configure.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling SQLite on Solaris 64bit and SPARC?

[David Kirkby]

On 8 November 2010 16:09, Lynton Grice  wrote:
> Hi all,
>
> Anybody out there got the latest version of SQLite compiled on Solaris 10 64
> bit?
>
> Perhaps I must add some options to the "./configure"?
>
> Thanks for the help ;-)
>
> Lynton

I've built sqlite-3.6.22 on 64-bit SPARC and it installs ok. I can't
speak for the latest version. I added -m64 to both CFLAGS and
CXXFLAGS. I think the latter is unnecessary, but Sqlite is used as
part of the Sage project, and we tend to build most things like that.

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

Re: [sqlite] Best way to represent UUID

[Samuel Adam]

On Mon, 08 Nov 2010 12:24:22 -0500, George  wrote:

> Hi there. I'm wondering what the best way to represent a 16 byte UUID in
> sqlite might be (I didn't see any specific data type for UUIDs in the
> documentation, but forgive me if there is).
>
> I could just stringify or blob it, but I might have to index a large  
> number
> of items by this identifier (thousands) , so speed is a consideration  
> more
> than space would be.

BLOBs are always compared using memcmp().  Hopefully that is an intrinsic  
in your compiler.

TEXT strings may be compared case-sensitively, case-insensitively, or with  
a user-defined collating function.  Note that you can bind and insert any  
arbitrary bytes as a TEXT value; SQLite simply assumes you are inserting  
valid UTF-8/UTF-16 (depending on database encoding).  I therefore presume  
(but do not know for certain) that in the unlikely event you somehow have  
something faster than memcmp(), you could stuff your 16-byte UUIDs in as  
TEXT and apply your user collation.  Yes, that would be a very ugly hack;  
and there may (or may not) be additional user-function overhead which  
would overwhelm the speed advantage of a hypothetical faster-than-memcmp()  
function.

Those are the only two SQLite3 datatypes capable of holding a 16-byte  
value.

Samuel Adam 
763 Montgomery Road
Hillsborough, NJ  08844-1304
United States
http://certifound.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite] Best way to represent UUID

[Black, Michael (IS)]

Thousands doesn't seem like it will be problem...so I'd keep it simple and put 
it in string form.
 
You'll cut the bytes in half for a blob but it doesn't sound like you need it 
for just thousands unless you're really memory starved.
 
 
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of George
Sent: Mon 11/8/2010 11:24 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] Best way to represent UUID



Hi there. I'm wondering what the best way to represent a 16 byte UUID in
sqlite might be (I didn't see any specific data type for UUIDs in the
documentation, but forgive me if there is).

I could just stringify or blob it, but I might have to index a large number
of items by this identifier (thousands) , so speed is a consideration more
than space would be.
___
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] Compiling SQLite on Solaris 64bit and SPARC?

[Lynton Grice]

Hi all,

Anybody out there got the latest version of SQLite compiled on Solaris 10 64
bit?

Perhaps I must add some options to the "./configure"?

Thanks for the help ;-)

Lynton

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Lynton Grice
Sent: 07 November 2010 07:01 PM
To: 'General Discussion of SQLite Database'
Subject: [sqlite] Compiling SQLite on Solaris 64bit and SPARC?
Importance: High

Hi there,

 

I have just downloaded the latest version of SQLite
(sqlite-amalgamation-3.7.3.tar.gz) and am trying to compile it on my Solaris
10 64bit machine, but get an error for some reason (see below).

 

I ran a "./configure" (with no parameters) and it seemed to be fine, but
then the moment I tried to run "make install" it gave the following error
below (not too many details):

 


# make install

/bin/bash ./libtool --tag=CC --mode=link gcc -DSQLITE_THREADSAFE=1
-DSQLITE_ENA

BLE_FTS3 -DSQLITE_ENABLE_RTREE -g -O2   -o libsqlite3.la -rpath
/usr/local/lib -

no-undefined -version-info 8:6:8 sqlite3.lo

rm -fr  .libs/libsqlite3.so .libs/libsqlite3.so.0 .libs/libsqlite3.so.0.8.6

gcc -shared -Wl,-h -Wl,libsqlite3.so.0 -o .libs/libsqlite3.so.0.8.6
.libs/sqlit

e3.o  -lc

(cd .libs && rm -f libsqlite3.so.0 && ln -s libsqlite3.so.0.8.6
libsqlite3.so.0)

(cd .libs && rm -f libsqlite3.so && ln -s libsqlite3.so.0.8.6 libsqlite3.so)

false cru .libs/libsqlite3.a  sqlite3.o

make: *** [libsqlite3.la] Error 1

 

I really need to get SQLite compiled very soon on Solaris, does anyone have
any advise on how I can compile SQLite on my Solaris machine? Any help will
be greatly appreciated ;-)

 

Thanks

 

Lynton

___
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] SQLite Memory Usage

[Black, Michael (IS)]

My guess is that you have a memory leak...
 
Care to post your code of how you're "creating" the database?  You're probably 
not freeing the SQL, or the statement handle.
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Sachin.2.Gupta
Sent: Mon 11/8/2010 4:47 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] SQLite Memory Usage



Hi,

We are trying to Integrate SQLite in our Application and are trying to populate 
as a Cache. We are planning to use it as a In Memory Database. Using it for the 
first time. Our Application is C++ based.

Our Application interacts with the Master Database to fetch data and performs 
numerous operations. These Operations are generally concerned with one Table 
which is quite huge in size.
We replicated this Table in SQLite and following are the observations:

Number of Fields: 60
Number of Records: 1,00,000

As the data population starts, the memory of the Application, shoots up 
drastically to ~1.4 GB from 120MB. At this time our application is in idle 
state and not doing any major operations. But normally, once the Operations 
start, the Memory Utilization shoots up. Now with SQLite as in Memory DB and 
this high memory usage, we don't think we will be able to support these many 
records.

Q. Is there a way to find the size of the database when it is in memory?

When I create the DB on Disk, the DB size sums to ~40MB. But still the Memory 
Usage of the Application remains very high.
Q. Is there a reason for this high usage. All buffers have been cleared and as 
said before the DB is not in memory?

Any help would be deeply appreciated.

Thanks and Regards
Sachin

___
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] SQLite Memory Usage

[Sachin . 2 . Gupta]

Hi,

We are trying to Integrate SQLite in our Application and are trying to populate 
as a Cache. We are planning to use it as a In Memory Database. Using it for the 
first time. Our Application is C++ based.

Our Application interacts with the Master Database to fetch data and performs 
numerous operations. These Operations are generally concerned with one Table 
which is quite huge in size.
We replicated this Table in SQLite and following are the observations:

Number of Fields: 60
Number of Records: 1,00,000

As the data population starts, the memory of the Application, shoots up 
drastically to ~1.4 GB from 120MB. At this time our application is in idle 
state and not doing any major operations. But normally, once the Operations 
start, the Memory Utilization shoots up. Now with SQLite as in Memory DB and 
this high memory usage, we don't think we will be able to support these many 
records.

Q. Is there a way to find the size of the database when it is in memory?

When I create the DB on Disk, the DB size sums to ~40MB. But still the Memory 
Usage of the Application remains very high.
Q. Is there a reason for this high usage. All buffers have been cleared and as 
said before the DB is not in memory?

Any help would be deeply appreciated.

Thanks and Regards
Sachin

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

[sqlite] Why no Right/Full Join?

[Jun Fang]

Anyone can tell me why no right/full in sqlite3?
because they can be replaced by other join/where/etc clause or other tech
issue?

Thanks in advance.

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

Re: [sqlite] RESOLVED: unable to open database file/Disc I/O error

[Serena Lien]

Sqlite version 3.6.15
Client running Windows Vista SP2
Server running Windows Small Business Server 2008
Written in C++
All databases are on the same share, mounted as a drive letter

I'm sorry have not had time yet to write a smaller test app for you, but I'm
not sure it would be reproducible for you anyway, as we have several vista
machines running our application accessing the same networked share and only
one of our machines seems to reliably show the problem.

thanks Serena


On Sat, Nov 6, 2010 at 10:53 AM, Black, Michael (IS)  wrote:

> #1 What version of Sqlite and Windows client/server are you using?
> #2 What language is your application written in?
> #3 Are all your databases on the same share mount point?
> #4 Is your share mounted as a drive letter?
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Serena Lien
> Sent: Sat 11/6/2010 3:08 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] RESOLVED: unable to open database file/Disc
> I/O error
>
>
>
> No, I didn't explain correctly. My application is not open overnight, only
> the computer.
> So I do not have any database connections open at all, they are not going
> stale.
>
> In the morning, I start the application, and it opens some databases in
> readwrite mode and executes statements without errors.
> It opened some databases in readonly mode and executes statements, these
> return SQLITE_BUSY errors, nothing else is using the databases.
>
> Therefore there is nothing wrong with the network connection as it has
> already accessed networked databases before my errors occur.
>
> I can only conclude it must be a bug with locking using the readonly flag -
> either sqlite or something vista OS specific going wrong.
>
> cheers Serena.
>
>
>
>
> On Fri, Nov 5, 2010 at 10:52 PM, H. Phil Duby  >wrote:
>
> > On Fri, Nov 5, 2010 at 3:25 AM, Serena Lien 
> wrote:
> > >
> > > I would like to inform the group that I managed to resolve the issue
> with
> > a
> > > workaround. I am not sure if this is a bug in vista or whether there
> > might
> > > in fact be some slight issue with sqlite...
> > >
> > > The problem seems to be hardware and software related, and occurs for
> me
> > > when my application is running on Windows Vista and is opening a
> database
> > on
> > > a networked drive (running Windows Small Business Server 2008) with the
> > > read-only flag. Running sql queries on the database after this will
> > > return SQLITE_BUSY when  no one is using the database. Once the
> > application
> > > is closed and restarted the error does not re-occur. This is highly
> > > reproducible once per day (in the morning after the machine has powered
> > down
> > > the screen only, it doesn't go to sleep).
> >
> > Given that sequence / timing, I *suspect* this is actually caused by
> > the network connection 'timing out'.  Your application thinks it has
> > the database file open, but SBS has _decided_ that the connection is
> > stale, and closed it.  I do not know why your symptoms would only show
> > up for read only connections.  You might be able to continue after the
> > error, by having the application close and reopen / reconnect to the
> > database file.  You might get an error doing a normal close as well,
> > because SBS thinks it is already closed.
> >
> > When your Vista system powers down the screen, it may *also* be
> > powering down the [wired or wireless] network card, which could
> > trigger SBS to close the read only connection.
> >
> > > Removing that flag and making sure I only open databases in read-write
> > mode
> > > has resolved the problem. I have tested this thoroughly over a period
> of
> > > many days.
> > >
> > > As I said I'm not sure if this is an sqlite bug or not (and I'm sure
> very
> > > difficult to reproduce) but I'm writing this in case anyone else has
> the
> > > same problem in the hope it helps them.
> > >
> > > cheers Serena
> > --
> > Phil
> > ___
> > 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
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query planner bug on "distinct" clause

[Dan Kennedy]

On Nov 8, 2010, at 5:12 AM, Alexey Pechnikov wrote:

> sqlite> .s object_record
> CREATE TABLE object_record
> (
>  record_id INTEGER PRIMARY KEY,
>  ts INTEGER NOT NULL DEFAULT (strftime('%s','now')),
>  object_id INTEGER NOT NULL
> );
> CREATE INDEX object_id_ts_idx on object_record(object_id,ts);
> CREATE INDEX object_ts_idx on object_record(ts);
>
> The planner bug with "distinct":
> sqlite> explain query plan select distinct object_id from (select
> ts,object_id from object_record where rowid>1581369-1) as x;
> 0|0|TABLE object_record WITH INDEX object_id_ts_idx ORDER BY
> CPU Time: user 0.00 sys 0.00
> sqlite> select distinct object_id from (select ts,object_id from
> object_record where rowid>1581369-1) as x;
> 31596
> CPU Time: user 0.224014 sys 0.00
>
> The plan without "distinct" is correct:
> sqlite> explain query plan select object_id from (select  
> ts,object_id from
> object_record where rowid>1581369-1) as x;
> 0|0|TABLE object_record USING PRIMARY KEY
> CPU Time: user 0.00 sys 0.00
> sqlite> select object_id from (select ts,object_id from  
> object_record where
> rowid>1581369-1) as x;
> 31596
> CPU Time: user 0.00 sys 0.00

The concept of "correct" is tricky here. When planning a query,
SQLite normally assumes that the (rowid>?) clause identifies
1/3 of the rows in table object_record. Using this assumption,
it goes on to assume that scanning via the index (so that
duplicate object_id values can excluded without using any
additional data structure) is going to be faster than using the
rowid index and a temporary b-tree to filter out duplicate
entries.

If the (rowid>?) clause identifies a small number of rows,
then the last assumption will be incorrect - using the rowid
index would be much faster. But SQLite doesn't know this when
formulating a query plan.

Regards,
Dan.





> The correct plan may be:
> sqlite> drop  INDEX object_id_ts_idx;
> CPU Time: user 0.024001 sys 0.00
> sqlite> explain query plan select distinct object_id from (select
> ts,object_id from object_record where rowid>1581369-1) as x;
> 0|0|TABLE object_record USING PRIMARY KEY
> CPU Time: user 0.00 sys 0.00
> sqlite> select distinct object_id from (select ts,object_id from
> object_record where rowid>1581369-1) as x;
> 31596
> CPU Time: user 0.00 sys 0.00
>
> And bug again:
> sqlite> CREATE INDEX object_id_idx on object_record(object_id);
> CPU Time: user 4.540283 sys 0.016001
> sqlite> explain query plan select distinct object_id from (select
> ts,object_id from object_record where rowid>1581369-1) as x;
> 0|0|TABLE object_record WITH INDEX object_id_idx ORDER BY
> CPU Time: user 0.00 sys 0.00
> sqlite> select distinct object_id from (select ts,object_id from
> object_record where rowid>1581369-1) as x;
> 31596
> CPU Time: user 0.236015 sys 0.00
>
>
> -- 
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] Idea for one of the testing suites

[Max Vlasov]

On Mon, Nov 8, 2010 at 2:44 AM, Simon Slavin  wrote:

> Several bugs I've been reading about here seem to be cases where the query
> optimizer works differently between one version of SQLite and the next.  So
> I wondered whether it would be possible for the testing suite to log the
> amount of time taken for each operation, leading to a long series of
> timings.  Then, once the build has passed all the tests, the timings for the
> new version could be compared to the timings for the previous version.  Any
> discrepancies unexpected in amount or direction could then be explored.
>
>
Simon, also thought about something like this when all those timing issues
appeared in the list. I'm sure the sqlite developers will find the way how
it is best to implement this, just my random thoughts.

This test suite at least for some of the problems posted can be just a
record of a table with the following fields:
- the queries to create the tables and indexes.
- the minumum number of "insert .. default values" queries for every table
created (this will save from saving the actual user data). I suppose for
many joins if the planner failed to optimize effectively, the difference
should be noticeable even with all those Nulls coming from "insert ..
default values".
- the query in question.

So the test suite can just step through all the records from the table
above. For one record it just  creates the db twice, measuring the timings
for one version and another. if the difference is significant, then stop,
otherwise go further.

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