[sqlite] Some benchmarks
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
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?
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?
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] Survey: NULLs and GROUP BY
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. test=# select version(); version - PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) test=# 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; CREATE test=#INSERT INTO t1 VALUES(1,2,1); INSERT 1229984 1 test=#INSERT INTO t1 VALUES(NULL,2,2); INSERT 1229985 1 test=#INSERT INTO t1 VALUES(1,NULL,4); INSERT 1229986 1 test=#INSERT INTO t1 VALUES(NULL,NULL,8); INSERT 1229987 1 test=#INSERT INTO t1 SELECT * FROM t1; INSERT 0 4 test=#SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3; a | b | sum ---+---+- 1 | 2 | 2 | 2 | 4 1 | | 8 | | 16 (4 rows) reid
RE: [sqlite] How to retrieve sqlite version
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
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
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
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
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
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
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
Postgresql 7.4.7 stats=>SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3; a | b | sum ---+---+- 1 | 2 | 2 | 2 | 4 1 | | 8 | | 16 (4 rows) Rgds, Mark. On Thu, 1 Sep 2005, 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. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > Regards, Mark
Re: [sqlite] Survey: NULLs and GROUP BY
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
>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
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
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
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
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
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
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
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
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
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
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
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?
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
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