Quantcast

Query slow on SQLite

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Query slow on SQLite

Peter Hardman
I have a Python web application that I am converting to use SQLite3 for
local deployment instead of PostgreSQL to make it simpler to install.

Some queries run up to 50 times slower using SQLite. They are all doing
similar things - extracting the latest record from a change log table
using a query of the form:

SELECT latest.regn_no, latest.tag_prefix, latest.indiv_no, test.num_indiv_no
FROM ear_tag latest
WHERE NOT EXISTS (SELECT  'x'
                   FROM ear_tag even_later
                   WHERE  latest.regn_no = even_later.regn_no
                   AND latest.date_assigned < even_later.date_assigned)

I have a view sheep_progeny_data:
SELECT s.regn_no, s.text_dob, s.sort_dob, s.flock_book_vol,
s.breeder_person_id, s.regn_person_id,
s.originating_flock, s.registering_flock, s.sex, s.sheep_name,
s.sire_no, s.dam_no, s.register_code,
s.colour, s.horns, s.litter_size, s.registration_date, of.flock_name as
org_flock_name, i.result_code, p.allele_1, p.allele_2
FROM sheep s JOIN flock of ON s.originating_flock = of.flock_no
LEFT JOIN current_inspection i ON s.regn_no = i.regn_no
LEFT JOIN current_prp p ON s.regn_no = p.regn_no

Both the last two 'tables' are views of the form I gave above. I'm using
the same indexes for both SQLite and PostgreSQL.

The sheep_progeny_data view is used to get the data for one sheep. It
takes about 100ms to run on SQLite and 2ms on PostgreSQL when I time the
Python code.

You can see the query in action at
http://www.ppdb.org.uk/sss/sheep/progeny/020330.

The database is here: http://somborneshetlands.co.uk/things/sss-mini.zip

The Python code is identical except for the database adapter - psycopg2
and sqlite3. But I don't think it's anything to do with Python since I
get the same order of difference using PGAdmin3 to access PostgreSQL and
SQLiteman or SQLite Manager to access SQLite.

Any help welcomed.

Pete

--
Peter Hardman

'For every complex problem there is a solution that is simple,
straightforward - and wrong'
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query slow on SQLite

Black, Michael (IS)
Try increasing your cache size so your whole database fits inside.  I'm sure that's what postgres is doing.
 
pragma cache_size=100000;
 
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 

________________________________

From: [hidden email] on behalf of Peter
Sent: Wed 1/5/2011 11:42 AM
To: [hidden email]
Subject: EXTERNAL:[sqlite] Query slow on SQLite



I have a Python web application that I am converting to use SQLite3 for
local deployment instead of PostgreSQL to make it simpler to install.

Some queries run up to 50 times slower using SQLite. They are all doing
similar things - extracting the latest record from a change log table
using a query of the form:

SELECT latest.regn_no, latest.tag_prefix, latest.indiv_no, test.num_indiv_no
FROM ear_tag latest
WHERE NOT EXISTS (SELECT  'x'
                   FROM ear_tag even_later
                   WHERE  latest.regn_no = even_later.regn_no
                   AND latest.date_assigned < even_later.date_assigned)

I have a view sheep_progeny_data:
SELECT s.regn_no, s.text_dob, s.sort_dob, s.flock_book_vol,
s.breeder_person_id, s.regn_person_id,
s.originating_flock, s.registering_flock, s.sex, s.sheep_name,
s.sire_no, s.dam_no, s.register_code,
s.colour, s.horns, s.litter_size, s.registration_date, of.flock_name as
org_flock_name, i.result_code, p.allele_1, p.allele_2
FROM sheep s JOIN flock of ON s.originating_flock = of.flock_no
LEFT JOIN current_inspection i ON s.regn_no = i.regn_no
LEFT JOIN current_prp p ON s.regn_no = p.regn_no

Both the last two 'tables' are views of the form I gave above. I'm using
the same indexes for both SQLite and PostgreSQL.

The sheep_progeny_data view is used to get the data for one sheep. It
takes about 100ms to run on SQLite and 2ms on PostgreSQL when I time the
Python code.

You can see the query in action at
http://www.ppdb.org.uk/sss/sheep/progeny/020330.

The database is here: http://somborneshetlands.co.uk/things/sss-mini.zip

The Python code is identical except for the database adapter - psycopg2
and sqlite3. But I don't think it's anything to do with Python since I
get the same order of difference using PGAdmin3 to access PostgreSQL and
SQLiteman or SQLite Manager to access SQLite.

Any help welcomed.

Pete

--
Peter Hardman

'For every complex problem there is a solution that is simple,
straightforward - and wrong'
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query slow on SQLite

Richard Hipp-3
In reply to this post by Peter Hardman
On Wed, Jan 5, 2011 at 12:42 PM, Peter <[hidden email]>wrote:

> I have a Python web application that I am converting to use SQLite3 for
> local deployment instead of PostgreSQL to make it simpler to install.
>
> Some queries run up to 50 times slower using SQLite.


We can probably help you better if will post your entire schema, including
all table, index, view, and trigger definitions, in addition to the query
that is giving your trouble.  Even better would be for you to run the
sqlite3_analyzer.exe utility over your database and send that result too, so
that we can get an idea of how much data is in your database and how it is
distributed.



> They are all doing
> similar things - extracting the latest record from a change log table
> using a query of the form:
>
> SELECT latest.regn_no, latest.tag_prefix, latest.indiv_no,
> test.num_indiv_no
> FROM ear_tag latest
> WHERE NOT EXISTS (SELECT  'x'
>                   FROM ear_tag even_later
>                   WHERE  latest.regn_no = even_later.regn_no
>                   AND latest.date_assigned < even_later.date_assigned)
>
> I have a view sheep_progeny_data:
> SELECT s.regn_no, s.text_dob, s.sort_dob, s.flock_book_vol,
> s.breeder_person_id, s.regn_person_id,
> s.originating_flock, s.registering_flock, s.sex, s.sheep_name,
> s.sire_no, s.dam_no, s.register_code,
> s.colour, s.horns, s.litter_size, s.registration_date, of.flock_name as
> org_flock_name, i.result_code, p.allele_1, p.allele_2
> FROM sheep s JOIN flock of ON s.originating_flock = of.flock_no
> LEFT JOIN current_inspection i ON s.regn_no = i.regn_no
> LEFT JOIN current_prp p ON s.regn_no = p.regn_no
>
> Both the last two 'tables' are views of the form I gave above. I'm using
> the same indexes for both SQLite and PostgreSQL.
>
> The sheep_progeny_data view is used to get the data for one sheep. It
> takes about 100ms to run on SQLite and 2ms on PostgreSQL when I time the
> Python code.
>
> You can see the query in action at
> http://www.ppdb.org.uk/sss/sheep/progeny/020330.
>
> The database is here: http://somborneshetlands.co.uk/things/sss-mini.zip
>
> The Python code is identical except for the database adapter - psycopg2
> and sqlite3. But I don't think it's anything to do with Python since I
> get the same order of difference using PGAdmin3 to access PostgreSQL and
> SQLiteman or SQLite Manager to access SQLite.
>
> Any help welcomed.
>
> Pete
>
> --
> Peter Hardman
>
> 'For every complex problem there is a solution that is simple,
> straightforward - and wrong'
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query slow on SQLite

Simon Slavin-3
In reply to this post by Peter Hardman

On 5 Jan 2011, at 5:42pm, Peter wrote:

> Both the last two 'tables' are views of the form I gave above. I'm using
> the same indexes for both SQLite and PostgreSQL.

PostgreSQL will make up its own indexes if it thinks they will speed up a search.  Actually, so will SQLite.  The problem is that SQLite throws its index away after the SELECT is finished, whereas PostgreSQL does not.  Consequently if you don't have good indexes for a SELECT PostgreSQL will make up the index once and cache it for future SELECTs, whereas SQLite will make up the index again every time you execute another SELECT.

So take a good look at both your SELECTS and figure out whether you have indexes ideal for every part of the SELECT: the main part and every JOIN and sub-SELECT.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query slow on SQLite

Richard Hipp-3
On Wed, Jan 5, 2011 at 5:32 PM, Simon Slavin <[hidden email]> wrote:

>
> On 5 Jan 2011, at 5:42pm, Peter wrote:
>
> > Both the last two 'tables' are views of the form I gave above. I'm using
> > the same indexes for both SQLite and PostgreSQL.
>
> PostgreSQL will make up its own indexes if it thinks they will speed up a
> search.  Actually, so will SQLite.  The problem is that SQLite throws its
> index away after the SELECT is finished, whereas PostgreSQL does not.
>  Consequently if you don't have good indexes for a SELECT PostgreSQL will
> make up the index once and cache it for future SELECTs, whereas SQLite will
> make up the index again every time you execute another SELECT.
>
> So take a good look at both your SELECTS and figure out whether you have
> indexes ideal for every part of the SELECT: the main part and every JOIN and
> sub-SELECT.
>

In this particular case the query is of the form:

     SELECT ... FROM table LEFT JOIN view LEFT JOIN view;

Each of the views is a self-join.  What SQLite is doing is first
materializing both views, then constructing transient automatic indices for
each materialized view, then running the query, then discarding both
materialized views and their indices.  It takes a little time to create the
indices.  But, that is still much faster than trying to do the 3-way join
without indices, which is what SQLite did prior to version 3.7.0.  So SQLite
3.7.4 might not be as fast as PostgreSQL on this query, but it is way faster
than SQLite 3.6.23.

I don't know what PostgreSQL is doing with this query.  But I suspect that
it is caching the materialized views and their indices and reusing them.

Perhaps a reasonable workaround would be to add a boolean "is_latest" column
to each of the tables and keep that column up-to-date using triggers:

CREATE TRIGGER xyz_update AFTER UPDATE ON xyz
BEGIN
    UPDATE xyz
       SET is_latest = NOT EXISTS
           (SELECT 1 FROM xyz AS later
             WHERE later.id=id AND later.time>time)
     WHERE xyz.id=NEW.id;
END;
CREATE TRIGGER xyz_insert AFTER INSERT ON xyz
BEGIN
    UPDATE xyz
       SET is_latest = NOT EXISTS
           (SELECT 1 FROM xyz AS later
             WHERE later.id=id AND later.time>time)
     WHERE xyz.id=NEW.id;
END;

CREATE TRIGGER xyz_delete AFTER DELETE ON xyz
BEGIN
    UPDATE xyz
       SET is_latest = NOT EXISTS
           (SELECT 1 FROM xyz AS later
             WHERE later.id=id AND later.time>time)
     WHERE xyz.id=OLD.id;
END;


Then instead of querying against the view, just query against the table but
add an "ON is_latest" restriction.


> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: EXTERNAL:Re: Query slow on SQLite

Black, Michael (IS)
In reply to this post by Simon Slavin-3
Hmmm...do I hear a new pragma that would either remember such indexes, or be verbose about creating them?
 
Could just be from the shell I suppose for verbosity.
 
Would really be nice to be able to turn that on for testing.
 
Or does the explain tell you when it will create one?
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 

________________________________

From: [hidden email] on behalf of Simon Slavin
Sent: Wed 1/5/2011 4:32 PM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] Query slow on SQLite




On 5 Jan 2011, at 5:42pm, Peter wrote:

> Both the last two 'tables' are views of the form I gave above. I'm using
> the same indexes for both SQLite and PostgreSQL.

PostgreSQL will make up its own indexes if it thinks they will speed up a search.  Actually, so will SQLite.  The problem is that SQLite throws its index away after the SELECT is finished, whereas PostgreSQL does not.  Consequently if you don't have good indexes for a SELECT PostgreSQL will make up the index once and cache it for future SELECTs, whereas SQLite will make up the index again every time you execute another SELECT.

So take a good look at both your SELECTS and figure out whether you have indexes ideal for every part of the SELECT: the main part and every JOIN and sub-SELECT.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Maintaining temporary indexes

Simon Slavin-3

On 6 Jan 2011, at 12:10pm, Black, Michael (IS) wrote:

> Hmmm...do I hear a new pragma that would either remember such indexes,

Just for context, we're talking about SQLite keeping the indexes it makes up temporarily to speed up a search.

I expect that the devs will need to talk to one-another about this before deciding if it's practical.  Two ways occur to me to do it:

A) Hold the index either in the journal file or in the database file, with some sort of timestamp.  Any indexes which haven't been used for say, an hour, can be thrown away.  All indexes will be thrown away when the journal file is deleted (i.e. all connections closed).

B) Hold the index in memory, as part of the storage used for the database connection.  This means that the file won't get bigger unexpectedly.  Indexes might or might not be thrown away after a certain time has expired.  However, there would have to be a mechanism for throwing the index away (or marking it for update) if another connection (from either the same or a different thread/process/application/computer) updates the table.

In terms of PRAGMAs, systems that do this often have some way of reporting which indexes are currently being held.  By consulting this at the same time as an application is being run, it's possible to create a log of which ones were created and destroyed at what times.  With this log, a developer can begin a project making no indexes at all, then during testing just create whatever indexes the SQL engine decided would be useful.

This is a very big advantage for users who don't really understand how SQL works.  And it's the sort of thing professional programmers hate, because it cheapens the effort they put into learning database theory and design.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Maintaining temporary indexes

Peter Hardman
On 06/01/11 12:44, Simon Slavin wrote:

>
> On 6 Jan 2011, at 12:10pm, Black, Michael (IS) wrote:
>
>> Hmmm...do I hear a new pragma that would either remember such
>> indexes,
>
> Just for context, we're talking about SQLite keeping the indexes it
> makes up temporarily to speed up a search.
>
> I expect that the devs will need to talk to one-another about this
> before deciding if it's practical.  Two ways occur to me to do it:
>
> A) Hold the index either in the journal file or in the database file,
> with some sort of timestamp.  Any indexes which haven't been used for
> say, an hour, can be thrown away.  All indexes will be thrown away
> when the journal file is deleted (i.e. all connections closed).
>
> B) Hold the index in memory, as part of the storage used for the
> database connection.  This means that the file won't get bigger
> unexpectedly.  Indexes might or might not be thrown away after a
> certain time has expired.  However, there would have to be a
> mechanism for throwing the index away (or marking it for update) if
> another connection (from either the same or a different
> thread/process/application/computer) updates the table.
>
> In terms of PRAGMAs, systems that do this often have some way of
> reporting which indexes are currently being held.  By consulting this
> at the same time as an application is being run, it's possible to
> create a log of which ones were created and destroyed at what times.
> With this log, a developer can begin a project making no indexes at
> all, then during testing just create whatever indexes the SQL engine
> decided would be useful.
>
> This is a very big advantage for users who don't really understand
> how SQL works.  And it's the sort of thing professional programmers
> hate, because it cheapens the effort they put into learning database
> theory and design.
>
> Simon. _______________________________________________ sqlite-users
> mailing list [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>

The last paragraph there has the key to my particular problem. I needed
to write the query paying attention to the way SQLite runs a query as
documented.

The solution is to write the query so that I select only one record in
each of the left joins, thus avoiding the need for an index. I tried
three ways, all of which run as fast as each other, and faster than
PostgreSQL. One of them allows me to keep my inner views by wrapping
them in a (select * from view where ) clause.

Now I've bothered to look at the PostgreSQL 'Explain analyze' output
this is exactly what the PostgreSQL query planner does - applies the
final 'where' clause to each of the joined tables thus avoiding the need
for on-the-fly indexes and covering up for my poorly written query in
the process.


Pete
--
Peter Hardman

'For every complex problem there is a solution that is simple,
straightforward - and wrong'
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Maintaining temporary indexes

Nicolas Williams-2
In reply to this post by Simon Slavin-3
On Thu, Jan 06, 2011 at 12:44:51PM +0000, Simon Slavin wrote:
> This is a very big advantage for users who don't really understand how
> SQL works.  And it's the sort of thing professional programmers hate,
> because it cheapens the effort they put into learning database theory
> and design.

Agreed.  It'd be better to have tools that help users optimize their DBs
for their queries.  Constructing such tools is not exactly easy.  But it
should be feasible to build a tool that takes a DB and a SELECT
statement and outputs a set of sets of CREATE INDEX statements that
would result in possibly better query plans -- the user would still have
to evaluate which query plan (and therefore which set of additional
indexes) is best.  For that it'd be nice to have stable, parseable
EXPLAIN QUERY PLAN output.

Nico
--
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Maintaining temporary indexes

Richard Hipp-3
On Thu, Jan 6, 2011 at 5:31 PM, Nicolas Williams <
[hidden email]> wrote:

> On Thu, Jan 06, 2011 at 12:44:51PM +0000, Simon Slavin wrote:
> > This is a very big advantage for users who don't really understand how
> > SQL works.  And it's the sort of thing professional programmers hate,
> > because it cheapens the effort they put into learning database theory
> > and design.
>
> Agreed.  It'd be better to have tools that help users optimize their DBs
> for their queries.  Constructing such tools is not exactly easy.  But it
> should be feasible to build a tool that takes a DB and a SELECT
> statement and outputs a set of sets of CREATE INDEX statements that
> would result in possibly better query plans -- the user would still have
> to evaluate which query plan (and therefore which set of additional
> indexes) is best.  For that it'd be nice to have stable, parseable
> EXPLAIN QUERY PLAN output.
>

Enhanced for version 3.7.4.  See http://www.sqlite.org/eqp.html for details.


>
> Nico
> --
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Maintaining temporary indexes

Simon Slavin-3

On 6 Jan 2011, at 10:46pm, Richard Hipp wrote:

> On Thu, Jan 6, 2011 at 5:31 PM, Nicolas Williams <
> [hidden email]> wrote:
>
>> it'd be nice to have stable, parseable
>> EXPLAIN QUERY PLAN output.
>
> Enhanced for version 3.7.4.  See http://www.sqlite.org/eqp.html for details.

The new output as described on that page is very nice.  Would it be possible to extend the 'USE TEMP B-TREE FOR ...' and 'COMPOUND SUBQUERIES ... USING TEMP B-TREE' lines so that the end of the line lists an index that would mean the TEMP B-TREE was not needed ?  For example it could produce something like

USE TEMP B-TREE FOR GROUP BY (a, b DESC, c)

Of course this won't describe the best suggestion for an index in every case, but it may help.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Result set column names

Peter Hardman
In reply to this post by Richard Hipp-3
In the C interface documentation we are told:

"The name of a result column is the value of the "AS" clause for that
column, if there is an AS clause. If there is no AS clause then the name
of the column is unspecified and may change from one release of SQLite
to the next."

What exactly is meant by 'unspecified'? Does it mean that in the absence
of an "AS" clause I will get either TABLE.COLUMN or COLUMN, or are
there/may there be other alternatives?

I've just discovered I can get COLUMN for some names and TABLE.COLUMN
for others depending (apparently) on how many tables I have JOIN'ed in
the query.

Pete
--
Peter Hardman

'For every complex problem there is a solution that is simple,
straightforward - and wrong'
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Result set column names

Igor Tandetnik
On 1/7/2011 1:31 PM, Peter wrote:
> In the C interface documentation we are told:
>
> "The name of a result column is the value of the "AS" clause for that
> column, if there is an AS clause. If there is no AS clause then the name
> of the column is unspecified and may change from one release of SQLite
> to the next."
>
> What exactly is meant by 'unspecified'?

Generated by internal logic that is not precisely documented, and is
subject to change without notice; should be treated as unpredictable,
and not relied on for anything of substance.

> Does it mean that in the absence
> of an "AS" clause I will get either TABLE.COLUMN or COLUMN, or are
> there/may there be other alternatives?

There may potentially be other alternatives, now or in the future.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Result set column names

Artur Reilin-2
Isn't it also depending if there are columns which have the same name or not?

> On 1/7/2011 1:31 PM, Peter wrote:
>> In the C interface documentation we are told:
>>
>> "The name of a result column is the value of the "AS" clause for that
>> column, if there is an AS clause. If there is no AS clause then the name
>> of the column is unspecified and may change from one release of SQLite
>> to the next."
>>
>> What exactly is meant by 'unspecified'?
>
> Generated by internal logic that is not precisely documented, and is
> subject to change without notice; should be treated as unpredictable,
> and not relied on for anything of substance.
>
>> Does it mean that in the absence
>> of an "AS" clause I will get either TABLE.COLUMN or COLUMN, or are
>> there/may there be other alternatives?
>
> There may potentially be other alternatives, now or in the future.
> --
> Igor Tandetnik
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


Artur Reilin
sqlite.yuedream.de
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Result set column names

Peter Hardman
In reply to this post by Igor Tandetnik
On 07/01/11 18:41, Igor Tandetnik wrote:

> On 1/7/2011 1:31 PM, Peter wrote:
>> In the C interface documentation we are told:
>>
>> "The name of a result column is the value of the "AS" clause for that
>> column, if there is an AS clause. If there is no AS clause then the name
>> of the column is unspecified and may change from one release of SQLite
>> to the next."
>>
>> What exactly is meant by 'unspecified'?
>
> Generated by internal logic that is not precisely documented, and is
> subject to change without notice; should be treated as unpredictable,
> and not relied on for anything of substance.
>
>> Does it mean that in the absence
>> of an "AS" clause I will get either TABLE.COLUMN or COLUMN, or are
>> there/may there be other alternatives?
>
> There may potentially be other alternatives, now or in the future.

So the 'column name' may be some internal representation/magic number
and bear no direct relationship to the name in the projector as supplied
in the SELECT?

Not a very useful function then.

--
Peter Hardman

'For every complex problem there is a solution that is simple,
straightforward - and wrong'
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Result set column names

Simon Slavin-3

On 8 Jan 2011, at 9:09am, Peter wrote:

> So the 'column name' may be some internal representation/magic number
> and bear no direct relationship to the name in the projector as supplied
> in the SELECT?

For columns which are exact copies of columns in a table, the name is always a copy of the table's column name.  You can rely on that.  For columns which are the results of a calculation, there is no documentation for the name produced by SQLite.  Different versions of SQLite may produce different names.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Result set column names

Peter Hardman
On 08/01/11 09:12, Simon Slavin wrote:

>
> On 8 Jan 2011, at 9:09am, Peter wrote:
>
>> So the 'column name' may be some internal representation/magic number
>> and bear no direct relationship to the name in the projector as supplied
>> in the SELECT?
>
> For columns which are exact copies of columns in a table, the name is always a copy of the table's column name.  You can rely on that.  For columns which are the results of a calculation, there is no documentation for the name produced by SQLite.  Different versions of SQLite may produce different names.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>

That sounds better. I just have to always use an AS clause on calculated
fields.

Maybe the documentation should be clarified?

Pete

--
Peter Hardman

'For every complex problem there is a solution that is simple,
straightforward - and wrong'
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Result set column names

Jay A. Kreibich
In reply to this post by Simon Slavin-3
On Sat, Jan 08, 2011 at 09:12:36AM +0000, Simon Slavin scratched on the wall:
>
> On 8 Jan 2011, at 9:09am, Peter wrote:
>
> > So the 'column name' may be some internal representation/magic number
> > and bear no direct relationship to the name in the projector as supplied
> > in the SELECT?
>
> For columns which are exact copies of columns in a table, the name
> is always a copy of the table's column name.  You can rely on that.

  Unless the name is prefixed with a table name.

  Or unless the name is prefixed with a table alias (AS in a FROM clause).

  I wouldn't even be surprised to find version that prefix database names,
  when appropriate.



  You can rely on "AS" names, and that's pretty much it-- just as the
  docs warn.

   -j

--
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Result set column names

Peter Hardman
Jay A. Kreibich wrote, On 08/01/11 14:07:

> On Sat, Jan 08, 2011 at 09:12:36AM +0000, Simon Slavin scratched on the wall:
>>
>> On 8 Jan 2011, at 9:09am, Peter wrote:
>>
>>> So the 'column name' may be some internal representation/magic number
>>> and bear no direct relationship to the name in the projector as supplied
>>> in the SELECT?
>>
>> For columns which are exact copies of columns in a table, the name
>> is always a copy of the table's column name.  You can rely on that.
>
>    Unless the name is prefixed with a table name.
>
>    Or unless the name is prefixed with a table alias (AS in a FROM clause).
>
>    I wouldn't even be surprised to find version that prefix database names,
>    when appropriate.
>
>
>
>    You can rely on "AS" names, and that's pretty much it-- just as the
>    docs warn.
>
>     -j
>
Well so long as it _is_ a column name, just that it's not specified
whether it will include tablename or alias then that's all that matters.

I interpreted Igor's reply to mean that unless the column name was
specified by an AS in the projectors then that function could return
anything, including garbage.


--
Peter Hardman

'For every complex problem there is a solution that is simple,
straightforward - and wrong'
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Loading...