[sqlite] Some benchmarks

[Robert Simpson]

I was quite curious how SQLite stacked up against Firebird and Microsoft's
brand-new Sql Mobile for Windows CE, so I ran some simple
insert/iterate/update tests in C# using VS2005 beta 2 and the ADO.NET 2.0
data provider for SQLite.  SQLite pretty much smoked them, and by smoked I
mean "utter devastation" in all categories.

http://sqlite.phxsoftware.com/forums/9/ShowForum.aspx

Robert

[sqlite] DETACHing database after sqlite3_step, but before sqlite3_finalize causes crash in btree.c

[Damian Slee]

Hi,
Don't know yet if this is fixed in the latest version, but I thought I would 
bring it up anyway.

We are using 3.2.2 library on windows.

Open a database
If you then attach another database
- sqlite3_exec()

Then step a select query, combining some info from tables from both database
- sqlite3_prepare()
- sqlite3_step()
sqlite3_step()
...
But do not keep going till EOF.  Ie. stop before sqlite3_step() returns 
SQLITE_DONE.  Eg do 3 rows out of 10.

Then detach the database
- sqlite3_exec()

It will crash here in at line 2218 in btree.c:

pCur->pPrev->pNext = pCur->pNext;


Work arounds are to call sqlite3_finalize on the query (to cleanup) before 
detaching, or only selecting the top N in the first place.

I don't know the internals well enough to fix it myself..

Regards,

Damian

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.18/86 - Release Date: 31/08/2005
 

Re: [sqlite] Where are temporary tables/indices stored?

[Tito Ciuro]

Of course!!

Thanks Dennis :-)

-- Tito

On 01/09/2005, at 23:05, Dennis Cote wrote:


Tito Ciuro wrote:




If I execute a statement such as 'CREATE TEMP TABLE...' without   
specifying the database name, where is the SQL statement stored?  
I've  tried using 'temp' as the database name, but it doesn't  
return anything.




Tito,

The temp table information is stored in a second master table, just  
like the sqlite_master table, called sqlite_temp_master. Try this:


create temp table t(1,b);
select * from sqlite_temp_master;

HTH
Dennis Cote

Re: [sqlite] Where are temporary tables/indices stored?

[Dennis Cote]

Tito Ciuro wrote:



If I execute a statement such as 'CREATE TEMP TABLE...' without  
specifying the database name, where is the SQL statement stored? I've  
tried using 'temp' as the database name, but it doesn't return anything.



Tito,

The temp table information is stored in a second master table, just like 
the sqlite_master table, called sqlite_temp_master. Try this:


create temp table t(1,b);
select * from sqlite_temp_master;

HTH
Dennis Cote

RE: [sqlite] How to retrieve sqlite version

[Ned Batchelder]

SQLite version 3.2.5
Enter ".help" for instructions
sqlite> select sqlite_version(*);
sqlite_version(*)
-
3.2.5


--Ned.
http://nedbatchelder.com
 
-Original Message-
From: Dinsmore, Jeff [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 01 September, 2005 1:56 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to retrieve sqlite version

I can't seem to come up with how to get the version from sqlite.

The frustrating thing is that I've done it before... As I recall, it's a
select, but for the life of me, I can't remember the right syntax.

Anyone have  that info handy?

Thanks,

Jeff Dinsmore
MIS - Interfaces
Ridgeview Medical Center
[EMAIL PROTECTED]
952.442.2191 x6592



Ridgeview Medical Center Confidentiality Notice: This email message,
including any attachments, is for the sole use of the intended recipient(s)
and may contain confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not the
intended recipient, please contact the sender by reply email and destroy all
copies of the original message.

Re: [sqlite] Survey: NULLs and GROUP BY

[Martin Engelschalk]

postgresql 8.0.1 on WinXP:

a | b | sum
---+---+-
1 | 2 |   2
  | 2 |   4
1 |   |   8
  |   |  16
(4 rows)

Martin


I'm rewriting the aggregate function processing in SQLite
(so that it runs faster and uses less memory) and I want to
make sure I get it right.  In particular, I want to make
sure that SQLite handles NULLs in GROUP BY values the same
as other database engines.

Can I get some volunteers to run the SQL shown below on various
other SQL database engines and tell me what the output is?

  CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
  INSERT INTO t1 VALUES(1,2,1);
  INSERT INTO t1 VALUES(NULL,2,2);
  INSERT INTO t1 VALUES(1,NULL,4);
  INSERT INTO t1 VALUES(NULL,NULL,8);
  INSERT INTO t1 SELECT * FROM t1; 
  SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;


Thanks.
 

Re: [sqlite] Survey: NULLs and GROUP BY

[G. Roderick Singleton]

On Thu, 2005-09-01 at 14:51 -0400, D. Richard Hipp wrote:
> I'm rewriting the aggregate function processing in SQLite
> (so that it runs faster and uses less memory) and I want to
> make sure I get it right.  In particular, I want to make
> sure that SQLite handles NULLs in GROUP BY values the same
> as other database engines.
> 
> Can I get some volunteers to run the SQL shown below on various
> other SQL database engines and tell me what the output is?
> 
>CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
>INSERT INTO t1 VALUES(1,2,1);
>INSERT INTO t1 VALUES(NULL,2,2);
>INSERT INTO t1 VALUES(1,NULL,4);
>INSERT INTO t1 VALUES(NULL,NULL,8);
>INSERT INTO t1 SELECT * FROM t1; 
>SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;
> 
> Thanks.

Dumb forgot the version.

Postgresql 8.0.3

 psql template1 
PATH tech

Re: [sqlite] Survey: NULLs and GROUP BY

[D. Richard Hipp]

Thanks everybody!

All the results so far seem to be in agreement with each
other and with the current behavior of SQLite.  So I think
everything is good.  Thx for the help.

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Survey: NULLs and GROUP BY

[G. Roderick Singleton]

On Thu, 2005-09-01 at 14:51 -0400, D. Richard Hipp wrote:
> I'm rewriting the aggregate function processing in SQLite
> (so that it runs faster and uses less memory) and I want to
> make sure I get it right.  In particular, I want to make
> sure that SQLite handles NULLs in GROUP BY values the same
> as other database engines.
> 
> Can I get some volunteers to run the SQL shown below on various
> other SQL database engines and tell me what the output is?
> 
>CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
>INSERT INTO t1 VALUES(1,2,1);
>INSERT INTO t1 VALUES(NULL,2,2);
>INSERT INTO t1 VALUES(1,NULL,4);
>INSERT INTO t1 VALUES(NULL,NULL,8);
>INSERT INTO t1 SELECT * FROM t1; 
>SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;
> 
> Thanks.

Postgresql.

 psql template1 
PATH tech

Re: [sqlite] Survey: NULLs and GROUP BY

[Nemanja Corlija]

On 9/1/05, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

>CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
>INSERT INTO t1 VALUES(1,2,1);
>INSERT INTO t1 VALUES(NULL,2,2);
>INSERT INTO t1 VALUES(1,NULL,4);
>INSERT INTO t1 VALUES(NULL,NULL,8);
>INSERT INTO t1 SELECT * FROM t1;
>SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;

Postgres 8.0.3 and Firebird 1.5.2:

a   |b   |sum
1   |2   |2
NULL|2   |4
1   |NULL|8
NULL|NULL|16


-- 
Nemanja Corlija <[EMAIL PROTECTED]>

RE: [sqlite] Survey: NULLs and GROUP BY

[Thomas Briggs]

Postgres 7.4:

 a | b | sum 
---+---+-
 1 | 2 |   2
   | 2 |   4
 1 |   |   8
   |   |  16


DB2 8.2

A   B   3  
--- --- ---
  1   2   2
  -   2   4
  1   -   8
  -   -  16


   -Tom

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, September 01, 2005 2:51 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Survey: NULLs and GROUP BY
> 
> I'm rewriting the aggregate function processing in SQLite
> (so that it runs faster and uses less memory) and I want to
> make sure I get it right.  In particular, I want to make
> sure that SQLite handles NULLs in GROUP BY values the same
> as other database engines.
> 
> Can I get some volunteers to run the SQL shown below on various
> other SQL database engines and tell me what the output is?
> 
>CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
>INSERT INTO t1 VALUES(1,2,1);
>INSERT INTO t1 VALUES(NULL,2,2);
>INSERT INTO t1 VALUES(1,NULL,4);
>INSERT INTO t1 VALUES(NULL,NULL,8);
>INSERT INTO t1 SELECT * FROM t1; 
>SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;
> 
> Thanks.
> -- 
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 

Re: [sqlite] Survey: NULLs and GROUP BY

[Robert Simpson]

SQL Server 2000:

a   b   
--- --- --- 
1   2   2

NULL2   4
1   NULL8
NULLNULL16



- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, September 01, 2005 11:51 AM
Subject: [sqlite] Survey: NULLs and GROUP BY



I'm rewriting the aggregate function processing in SQLite
(so that it runs faster and uses less memory) and I want to
make sure I get it right.  In particular, I want to make
sure that SQLite handles NULLs in GROUP BY values the same
as other database engines.

Can I get some volunteers to run the SQL shown below on various
other SQL database engines and tell me what the output is?

  CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
  INSERT INTO t1 VALUES(1,2,1);
  INSERT INTO t1 VALUES(NULL,2,2);
  INSERT INTO t1 VALUES(1,NULL,4);
  INSERT INTO t1 VALUES(NULL,NULL,8);
  INSERT INTO t1 SELECT * FROM t1; 
  SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;


Thanks.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Survey: NULLs and GROUP BY

[Brass Tilde]

>CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
>INSERT INTO t1 VALUES(1,2,1);
>INSERT INTO t1 VALUES(NULL,2,2);
>INSERT INTO t1 VALUES(1,NULL,4);
>INSERT INTO t1 VALUES(NULL,NULL,8);
>INSERT INTO t1 SELECT * FROM t1; 
>SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;

Microsoft SQL Server 2000 (only difference is I used a temp table):


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(4 row(s) affected)

a   b   
--- --- --- 
  1   2   2 
NULL  2   4 
  1 NULL  8 
NULLNULL 16 

(4 row(s) affected)

Re: [sqlite] Survey: NULLs and GROUP BY

[Vladimir Zelinski]

Oracle 9i:

A   B   SUM(C)
1   2   2
[NULL]  2   4
1   [NULL]  8
[NULL]  [NULL]  16


--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:

> I'm rewriting the aggregate function processing in
> SQLite
> (so that it runs faster and uses less memory) and I
> want to
> make sure I get it right.  In particular, I want to
> make
> sure that SQLite handles NULLs in GROUP BY values
> the same
> as other database engines.
> 
> Can I get some volunteers to run the SQL shown below
> on various
> other SQL database engines and tell me what the
> output is?
> 
>CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
>INSERT INTO t1 VALUES(1,2,1);
>INSERT INTO t1 VALUES(NULL,2,2);
>INSERT INTO t1 VALUES(1,NULL,4);
>INSERT INTO t1 VALUES(NULL,NULL,8);
>INSERT INTO t1 SELECT * FROM t1; 
>SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER
> BY 3;
> 
> Thanks.
> -- 
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 

Re: [sqlite] Survey: NULLs and GROUP BY

[Puneet Kishor]

D. Richard Hipp wrote:

I'm rewriting the aggregate function processing in SQLite
(so that it runs faster and uses less memory) and I want to
make sure I get it right.  In particular, I want to make
sure that SQLite handles NULLs in GROUP BY values the same
as other database engines.

Can I get some volunteers to run the SQL shown below on various
other SQL database engines and tell me what the output is?

   CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
   INSERT INTO t1 VALUES(1,2,1);
   INSERT INTO t1 VALUES(NULL,2,2);
   INSERT INTO t1 VALUES(1,NULL,4);
   INSERT INTO t1 VALUES(NULL,NULL,8);
   INSERT INTO t1 SELECT * FROM t1; 
   SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;





Oh, what the heck!

MS Access ;-)
abExpr1002
  
122
 24
1 8
 16

Re: [sqlite] Survey: NULLs and GROUP BY

[Cory Nelson]

SQL Server 2005:

a   b
--- --- ---
  1   2   2
   NULL   2   4
  1NULL   8
   NULLNULL  16

On 9/1/05, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> I'm rewriting the aggregate function processing in SQLite
> (so that it runs faster and uses less memory) and I want to
> make sure I get it right.  In particular, I want to make
> sure that SQLite handles NULLs in GROUP BY values the same
> as other database engines.
> 
> Can I get some volunteers to run the SQL shown below on various
> other SQL database engines and tell me what the output is?
> 
>CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
>INSERT INTO t1 VALUES(1,2,1);
>INSERT INTO t1 VALUES(NULL,2,2);
>INSERT INTO t1 VALUES(1,NULL,4);
>INSERT INTO t1 VALUES(NULL,NULL,8);
>INSERT INTO t1 SELECT * FROM t1;
>SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;
> 
> Thanks.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 


-- 
Cory Nelson
http://www.int64.org

RE: [sqlite] Survey: NULLs and GROUP BY

[Bob Dankert]

MySQL 4.1.1-NT

+--+--++
| a| b| sum(c) |
+--+--++
|1 |2 |  2 |
| NULL |2 |  4 |
|1 | NULL |  8 |
| NULL | NULL | 16 |
+--+--++

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780
 
-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 01, 2005 1:51 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Survey: NULLs and GROUP BY

I'm rewriting the aggregate function processing in SQLite
(so that it runs faster and uses less memory) and I want to
make sure I get it right.  In particular, I want to make
sure that SQLite handles NULLs in GROUP BY values the same
as other database engines.

Can I get some volunteers to run the SQL shown below on various
other SQL database engines and tell me what the output is?

   CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
   INSERT INTO t1 VALUES(1,2,1);
   INSERT INTO t1 VALUES(NULL,2,2);
   INSERT INTO t1 VALUES(1,NULL,4);
   INSERT INTO t1 VALUES(NULL,NULL,8);
   INSERT INTO t1 SELECT * FROM t1; 
   SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;

Thanks.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Survey: NULLs and GROUP BY

[Robert Simpson]

MS Jet:

a  bExpr1002
1  22
NULL   24
1  NULL 8
NULL   NULL 16



- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, September 01, 2005 11:51 AM
Subject: [sqlite] Survey: NULLs and GROUP BY



I'm rewriting the aggregate function processing in SQLite
(so that it runs faster and uses less memory) and I want to
make sure I get it right.  In particular, I want to make
sure that SQLite handles NULLs in GROUP BY values the same
as other database engines.

Can I get some volunteers to run the SQL shown below on various
other SQL database engines and tell me what the output is?

  CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
  INSERT INTO t1 VALUES(1,2,1);
  INSERT INTO t1 VALUES(NULL,2,2);
  INSERT INTO t1 VALUES(1,NULL,4);
  INSERT INTO t1 VALUES(NULL,NULL,8);
  INSERT INTO t1 SELECT * FROM t1; 
  SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;


Thanks.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Survey: NULLs and GROUP BY

[Andrew Piskorski]

On Thu, Sep 01, 2005 at 02:51:21PM -0400, D. Richard Hipp wrote:

Oracle9i Enterprise Edition Release 9.2.0.4.0:

SQL> SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3; 
 
 A  B SUM(C) 
-- -- -- 
 1  2  2 
2  4 
 1 8 
  16 

> Can I get some volunteers to run the SQL shown below on various
> other SQL database engines and tell me what the output is?
> 
>CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
>INSERT INTO t1 VALUES(1,2,1);
>INSERT INTO t1 VALUES(NULL,2,2);
>INSERT INTO t1 VALUES(1,NULL,4);
>INSERT INTO t1 VALUES(NULL,NULL,8);
>INSERT INTO t1 SELECT * FROM t1; 
>SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;

-- 
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/

Re: [sqlite] Survey: NULLs and GROUP BY

[Puneet Kishor]

D. Richard Hipp wrote:

I'm rewriting the aggregate function processing in SQLite
(so that it runs faster and uses less memory) and I want to
make sure I get it right.  In particular, I want to make
sure that SQLite handles NULLs in GROUP BY values the same
as other database engines.

Can I get some volunteers to run the SQL shown below on various
other SQL database engines and tell me what the output is?

   CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
   INSERT INTO t1 VALUES(1,2,1);
   INSERT INTO t1 VALUES(NULL,2,2);
   INSERT INTO t1 VALUES(1,NULL,4);
   INSERT INTO t1 VALUES(NULL,NULL,8);
   INSERT INTO t1 SELECT * FROM t1; 
   SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;





SQL Server 2000
a   b   (no column name)
-   --- ---
1   2   2
NULL2   4
1   NULL8
NULLNULL16

Oracle

   A   B   SUM(C)
- ---  ---
1  22
   24
1   8
   16

[sqlite] Survey: NULLs and GROUP BY

[D. Richard Hipp]

I'm rewriting the aggregate function processing in SQLite
(so that it runs faster and uses less memory) and I want to
make sure I get it right.  In particular, I want to make
sure that SQLite handles NULLs in GROUP BY values the same
as other database engines.

Can I get some volunteers to run the SQL shown below on various
other SQL database engines and tell me what the output is?

   CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER);
   INSERT INTO t1 VALUES(1,2,1);
   INSERT INTO t1 VALUES(NULL,2,2);
   INSERT INTO t1 VALUES(1,NULL,4);
   INSERT INTO t1 VALUES(NULL,NULL,8);
   INSERT INTO t1 SELECT * FROM t1; 
   SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3;

Thanks.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] How to retrieve sqlite version

[Tito Ciuro]

Hi Jeff,

Just call sqlite3_libversion()

Regards,

-- Tito

On 01/09/2005, at 19:55, Dinsmore, Jeff wrote:


I can't seem to come up with how to get the version from sqlite.

The frustrating thing is that I've done it before... As I recall,  
it's a

select, but for the life of me, I can't remember the right syntax.

Anyone have  that info handy?

Thanks,

Jeff Dinsmore
MIS - Interfaces
Ridgeview Medical Center
[EMAIL PROTECTED]
952.442.2191 x6592



Ridgeview Medical Center Confidentiality Notice: This email  
message, including any attachments, is for the sole use of the  
intended recipient(s) and may contain confidential and privileged  
information. Any unauthorized review, use, disclosure or  
distribution is prohibited. If you are not the intended recipient,  
please contact the sender by reply email and destroy all copies of  
the original message.

[sqlite] How to retrieve sqlite version

[Dinsmore, Jeff]

I can't seem to come up with how to get the version from sqlite.

The frustrating thing is that I've done it before... As I recall, it's a
select, but for the life of me, I can't remember the right syntax.

Anyone have  that info handy?

Thanks,

Jeff Dinsmore
MIS - Interfaces
Ridgeview Medical Center
[EMAIL PROTECTED]
952.442.2191 x6592



Ridgeview Medical Center Confidentiality Notice: This email message, including 
any attachments, is for the sole use of the intended recipient(s) and may 
contain confidential and privileged information. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply email and destroy all copies of 
the original message.

Re: [sqlite] BUG? "order by" does not sort correctly

[D. Richard Hipp]

On Thu, 2005-09-01 at 13:04 -0400, Igor Tandetnik wrote:
> SQLite 3.2.5, freshly downloaded Windows binaries.

The bug appears to have been introduced in version 3.1.0.

> -- now for the bug
> select a.id, b.id, b.text from a join b on (a.id = b.aId) order by a.id,
> b.text;
> 1|1|zzz
> 1|2|xxx
> 1|3|yyy
> 

Fix is at http://www.sqlite.org/cvstrac/chngview/cn=2655.

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] Where are temporary tables/indices stored?

[Tito Ciuro]

Hello,

Regarding the TEMP keyword, the documentation states:


If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and  
"TABLE" then the table that is created is only visible within that  
same database connection and is automatically deleted when the  
database connection is closed. Any indices created on a temporary  
table are also temporary. Temporary tables and indices are stored  
in a separate file distinct from the main database file.


If a  is specified, then the table is created in the  
named database. It is an error to specify both a   
and the TEMP keyword, unless the  is "temp". If no  
database name is specified, and the TEMP keyword is not present,  
the table is created in the main database.


If I execute a statement such as 'CREATE TEMP TABLE...' without  
specifying the database name, where is the SQL statement stored? I've  
tried using 'temp' as the database name, but it doesn't return anything.




Thanks,



-- Tito

[sqlite] BUG? "order by" does not sort correctly

[Igor Tandetnik]

SQLite 3.2.5, freshly downloaded Windows binaries.

I start with an empty DB and execute the following commands in sqlite3
console:

create table a (id integer primary key);
create table b (id integer primary key, aId integer, text);

insert into a values (1);

insert into b values (1, 1, 'zzz');
insert into b values (2, 1, 'xxx');
insert into b values (3, 1, 'yyy');

-- sanity check
select * from a;
   1
select * from b;
   1|1|zzz
   2|1|xxx
   3|1|yyy

-- now for the bug
select a.id, b.id, b.text from a join b on (a.id = b.aId) order by a.id,
b.text;
   1|1|zzz
   1|2|xxx
   1|3|yyy

The result of the last query is supposed to be sorted by b.text (the
last column), but it is obviously not. The engine seems to be confused
by a.id and b.id columns having the same names (sans table name prefix).
Slightly modifying the query yields the correct result:

select a.id mainId, b.id, b.text from a join b on (a.id = b.aId) order
by mainId, b.text;
   1|2|xxx
   1|3|yyy
   1|1|zzz

Is this a bug, or am I missing something obvious?

Igor Tandetnik