Re: [sqlite] Optimization of equality comparison when NULL involved
You can use something like:
select tableA.path, tableA.value from tableA,tableB where
tableA.path=tableB.path and (tableA.value=tableB.value or
(tableA.value IS NULL AND tableB.value IS NULL));
It's possible that won't use an index, either, due to the OR, in which
case you could try a union between a select with is-null only, and
another with equality, something like:
select tableA.path, tableA.value from tableA,tableB where
tableA.path=tableB.path and tableA.value=tableB.value union
select tableA.path, tableA.value from tableA,tableB where
tableA.path=tableB.path and tableA.value IS NULL AND tableB.value IS
NULL;
If you really can treat null as '', then you might be better off
defining the column as NOT NULL DEFAULT ''.
-scott
On 6/15/07, Sean Cunningham <[EMAIL PROTECTED]> wrote:
I am hoping there is an obvious answer to this that I've overlooked.
I have two tables:
create table tableA (path TEXT, value TEXT);
create index myIndexA on tableA (path, value);
create table tableB(path TEXT, value TEXT);
create index myIndexB on tableB (path, value);
Now some simple inserts:
insert into tableA(path,value) values('alpha', '1');
insert into tableA(path,value) values('beta', '2');
insert into tableB(path,value) values('alpha', '1');
insert into tableB(path,value) values('beta', '999');
The following select statement gets what you'd expect, and it uses
the index.
sqlite> select tableA.path, tableA.value from tableA,tableB where
tableA.path=tableB.path and tableA.value=tableB.value;
alpha|1
gamma|3
However, if your data has NULL's:
insert into tableA(path,value) values('gamma', NULL);
insert into tableB(path,value) values('gamma', NULL);
The same select above neglects to report the tuple ('gamma',NULL) as
the equal. This is correct given SQL's treatment of NULL, and is
easily fixed:
sqlite> select tableA.path, tableA.value from tableA,tableB where
ifnull(tableA.path,'')=ifnull(tableB.path,'') and ifnull
(tableA.value,'')=ifnull(tableB.value,'');
alpha|1
gamma|3
gamma|
However, the above statement has the unfortunate side effect of
bypassing the index:
sqlite> explain select tableA.path, tableA.value from tableA,tableB
where ifnull(tableA.path,'')=ifnull(tableB.path,'') and ifnull
(tableA.value,'')=ifnull(tableB.value,'');
0|Goto|0|35|
1|Integer|0|0|
2|OpenRead|0|2|
3|SetNumColumns|0|2|
4|Integer|0|0|
5|OpenRead|1|4|
6|SetNumColumns|1|2|
7|Rewind|0|32|
8|Rewind|1|31|
9|Column|0|0|
10|String8|0|0|
11|CollSeq|0|0|collseq(BINARY)
12|Function|2|2|ifnull(2)
13|Column|1|0|
14|String8|0|0|
15|CollSeq|0|0|collseq(BINARY)
16|Function|2|2|ifnull(2)
17|Ne|28417|30|
18|Column|0|1|
19|String8|0|0|
20|CollSeq|0|0|collseq(BINARY)
21|Function|2|2|ifnull(2)
22|Column|1|1|
23|String8|0|0|
24|CollSeq|0|0|collseq(BINARY)
25|Function|2|2|ifnull(2)
26|Ne|28417|30|
27|Column|0|0|
28|Column|0|1|
29|Callback|2|0|
30|Next|1|9|
31|Next|0|8|
32|Close|0|0|
33|Close|1|0|
34|Halt|0|0|
35|Transaction|0|0|
36|VerifyCookie|0|4|
37|Goto|0|1|
38|Noop|0|0|
My question is:Is there another way to write such a select
statement which can solve both problems of treating NULL==NULL and
using the index.
Thanks,
Sean
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Optimization of equality comparison when NULL involved
In SQLite null is not equal to anything, including null.
I'm not sure what the best solution for your application is. With help from
the others here, I have learned to use coalesce() to convert nulls into a
value, and to not allow null in key fields.
select
tableA.path,
tableA.value
from
tableA,
tableB
where
tableA.path = tableB.path
and coalesce(tableA.value, '' ) = coalesce(tableB.value, '' );
yields
alpha|1
gamma|
And it appears to use the myIndexB index for the join.
-Clark
- Original Message
From: Sean Cunningham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, June 15, 2007 1:26:49 PM
Subject: [sqlite] Optimization of equality comparison when NULL involved
I am hoping there is an obvious answer to this that I've overlooked.
I have two tables:
create table tableA (path TEXT, value TEXT);
create index myIndexA on tableA (path, value);
create table tableB(path TEXT, value TEXT);
create index myIndexB on tableB (path, value);
Now some simple inserts:
insert into tableA(path,value) values('alpha', '1');
insert into tableA(path,value) values('beta', '2');
insert into tableB(path,value) values('alpha', '1');
insert into tableB(path,value) values('beta', '999');
The following select statement gets what you'd expect, and it uses
the index.
sqlite> select tableA.path, tableA.value from tableA,tableB where
tableA.path=tableB.path and tableA.value=tableB.value;
alpha|1
gamma|3
However, if your data has NULL's:
insert into tableA(path,value) values('gamma', NULL);
insert into tableB(path,value) values('gamma', NULL);
The same select above neglects to report the tuple ('gamma',NULL) as
the equal. This is correct given SQL's treatment of NULL, and is
easily fixed:
sqlite> select tableA.path, tableA.value from tableA,tableB where
ifnull(tableA.path,'')=ifnull(tableB.path,'') and ifnull
(tableA.value,'')=ifnull(tableB.value,'');
alpha|1
gamma|3
gamma|
However, the above statement has the unfortunate side effect of
bypassing the index:
sqlite> explain select tableA.path, tableA.value from tableA,tableB
where ifnull(tableA.path,'')=ifnull(tableB.path,'') and ifnull
(tableA.value,'')=ifnull(tableB.value,'');
0|Goto|0|35|
1|Integer|0|0|
2|OpenRead|0|2|
3|SetNumColumns|0|2|
4|Integer|0|0|
5|OpenRead|1|4|
6|SetNumColumns|1|2|
7|Rewind|0|32|
8|Rewind|1|31|
9|Column|0|0|
10|String8|0|0|
11|CollSeq|0|0|collseq(BINARY)
12|Function|2|2|ifnull(2)
13|Column|1|0|
14|String8|0|0|
15|CollSeq|0|0|collseq(BINARY)
16|Function|2|2|ifnull(2)
17|Ne|28417|30|
18|Column|0|1|
19|String8|0|0|
20|CollSeq|0|0|collseq(BINARY)
21|Function|2|2|ifnull(2)
22|Column|1|1|
23|String8|0|0|
24|CollSeq|0|0|collseq(BINARY)
25|Function|2|2|ifnull(2)
26|Ne|28417|30|
27|Column|0|0|
28|Column|0|1|
29|Callback|2|0|
30|Next|1|9|
31|Next|0|8|
32|Close|0|0|
33|Close|1|0|
34|Halt|0|0|
35|Transaction|0|0|
36|VerifyCookie|0|4|
37|Goto|0|1|
38|Noop|0|0|
My question is:Is there another way to write such a select
statement which can solve both problems of treating NULL==NULL and
using the index.
Thanks,
Sean
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Optimization of equality comparison when NULL involved
I am hoping there is an obvious answer to this that I've overlooked.
I have two tables:
create table tableA (path TEXT, value TEXT);
create index myIndexA on tableA (path, value);
create table tableB(path TEXT, value TEXT);
create index myIndexB on tableB (path, value);
Now some simple inserts:
insert into tableA(path,value) values('alpha', '1');
insert into tableA(path,value) values('beta', '2');
insert into tableB(path,value) values('alpha', '1');
insert into tableB(path,value) values('beta', '999');
The following select statement gets what you'd expect, and it uses
the index.
sqlite> select tableA.path, tableA.value from tableA,tableB where
tableA.path=tableB.path and tableA.value=tableB.value;
alpha|1
gamma|3
However, if your data has NULL's:
insert into tableA(path,value) values('gamma', NULL);
insert into tableB(path,value) values('gamma', NULL);
The same select above neglects to report the tuple ('gamma',NULL) as
the equal. This is correct given SQL's treatment of NULL, and is
easily fixed:
sqlite> select tableA.path, tableA.value from tableA,tableB where
ifnull(tableA.path,'')=ifnull(tableB.path,'') and ifnull
(tableA.value,'')=ifnull(tableB.value,'');
alpha|1
gamma|3
gamma|
However, the above statement has the unfortunate side effect of
bypassing the index:
sqlite> explain select tableA.path, tableA.value from tableA,tableB
where ifnull(tableA.path,'')=ifnull(tableB.path,'') and ifnull
(tableA.value,'')=ifnull(tableB.value,'');
0|Goto|0|35|
1|Integer|0|0|
2|OpenRead|0|2|
3|SetNumColumns|0|2|
4|Integer|0|0|
5|OpenRead|1|4|
6|SetNumColumns|1|2|
7|Rewind|0|32|
8|Rewind|1|31|
9|Column|0|0|
10|String8|0|0|
11|CollSeq|0|0|collseq(BINARY)
12|Function|2|2|ifnull(2)
13|Column|1|0|
14|String8|0|0|
15|CollSeq|0|0|collseq(BINARY)
16|Function|2|2|ifnull(2)
17|Ne|28417|30|
18|Column|0|1|
19|String8|0|0|
20|CollSeq|0|0|collseq(BINARY)
21|Function|2|2|ifnull(2)
22|Column|1|1|
23|String8|0|0|
24|CollSeq|0|0|collseq(BINARY)
25|Function|2|2|ifnull(2)
26|Ne|28417|30|
27|Column|0|0|
28|Column|0|1|
29|Callback|2|0|
30|Next|1|9|
31|Next|0|8|
32|Close|0|0|
33|Close|1|0|
34|Halt|0|0|
35|Transaction|0|0|
36|VerifyCookie|0|4|
37|Goto|0|1|
38|Noop|0|0|
My question is:Is there another way to write such a select
statement which can solve both problems of treating NULL==NULL and
using the index.
Thanks,
Sean
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Database malformed with SQLite3.3.17 on WindowsXP
I sure am glad I converted all SQL to use a BEGIN EXCLUSIVE. For some reason it struck me as odd that a SQL select could get a SQLITE_BUSY or even after you perform a single insert operation that a SQLITE_BUSY could later be thrown. Is it the delayed mechanism of the Pager Layer that provides locking upon a cache spill that causes this? I still think it should be possible to implement, multi readers and a single concurrent writer. I just don't see how though given the requirement that a reader can get sqlite_busy for a select statement. Will the new version 3.4.0 resolve this problem? Thanks Ken
[sqlite] Ticket #2415
Hello Developers, I notice that you are about to release version 3.4.0 shortly. I have just created ticked #2415 which I believe breaks existing code. Are there any chance that you find the time to look at it prior to 3.4.0? Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How SQLite may work on AVR or similar mcu?
At 14:18 13/06/2007, you wrote: I do not need it actually, but i am so inquisitive. I have heard that sqlite has been ported to AVR too. How may be solved the main storage solved on it? So for example, i have system with ATmega64 with 128KB of external SRAM. The whole ram is organized as static 60KB and 18 pages of 4KB (the last 4KB page lays in the internal RAM shadow), but it can be simlply reconfigured because the memory address logic is provided by programmable logic device (CPLD) As the firm data storage memory is there 1MB spi dataflash, but there is a possibility to replace it with 16MB chip with the same interface for an example. I am at 230th page of Mike Owen's book The definitive Guide to SQLite and with every next page, my image of SQL on a chip w/o OS disappears in haze. Is i wrote before, i do not need it actually, i use sqlite in linux environment. Thank you for let you make me a picture. Jakub Ladman 128KB RAM is very low. I have run it on powerpc 440 device with uclinux but with more ram (1GB). There is minimalist os that can be fit on a few KB, check contiki and contiki mail-list http://www.sics.se/contiki/ https://lists.sourceforge.net/lists/listinfo/contiki-developers it's used on commodore 64 and other relics. I used it before in a development card (Virtex II Pro - xupv2p). You can cut down contiki and sqlite features. The problem will be the stack/heap that sqlite needs for run. I don't know about ATmega64 but can't you add a fpga with dram controller? Perhaps using a free i/o port or changing the cpld with the fpga? HTH -- "General error, hit any user to continue." - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Database malformed with SQLite3.3.17 on WindowsXP
Hello drh and lists, Thank you for the information provided at the ticket page at http://www.sqlite.org/cvstrac/tktview?tn=2409 Now I successfully worked around the problem. -- tamagawa ryuji [EMAIL PROTECTED] : > "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: >> I've opened a new ticket 2409. >> >> http://www.sqlite.org/cvstrac/tktview?tn=2409,38 >> > > I am so far unable to reproduce the problem. Please send > me an example corrupt database and the binaries for > SQLiteCrush.exe. Tnx. > > You can send them to me by direct email if you want. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite for 16bit
Hello Christoph, Christoph Pross wrote: > Hello everybody, > > I am new to this list. We are looking for a sql > database that can run on a 16bit OS. I looked > over the sqlite C source but I found too may > 64bit integers, our OS only supports 23 bit longs. > > Has someone ported sqlite to a 16bit OS before? > > Or maybe someone knows another solution. An > open database that runs on MS DOS? > I am not sure that this will run without a 32-bit DOS extender, but you could try looking at Konstantin Knizhnik's MiniDB: http://www.garret.ru/~knizhnik/databases.html (Scroll down to the bottom.) Btw, did you mean 32 bits instead of 23 bits? Cheers, Ulrik Petersen -- Ulrik Petersen http://ulrikp.org -- Homepage http://emdros.org -- Emdros is a text database engine - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PRAGMA cache_size = 0
B V, Phanisekhar wrote: What exactly happens when I change the cache_size (both increase and decrease size)? A variable is set. It seems this term is a misnomer. What are we achieving by setting this variable? [...] Will there be any memory that will be freed up when I reduce the size of result cache? No. So whats there in this cache? I'd have thought setting the cache size was (meant to be) a one-off operation? Changing the cache size on the fly and expecting the cache to be valid must be an unusual case (well, it is to me and I've not seen anyone else asking about it) so I reckon you'd be better off looking at the source code, doing a few tests of your own and reporting back. Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite 3.X Database File Format ?
On Thu, 2007-06-14 at 15:08 -0700, Joe Wilson wrote: > Is there an SQLite 3.x equivalent document for this? > > SQLite 2.X Database File Format > http://sqlite.org/fileformat.html > > If not, is this 2.x document worth reading as a background to > the general structure of the sqlite 3.x file and page format? > Or has it changed so much that it's not useful? I just took a quick look, and it all still looks pretty relevant to me. The exact way the bytes are arranged to form row-records has changed (to accommodate manifest types), the database header is different and some of the "b trees" are now "b+ trees". And the root-page of the sqlite_master table is now on page 1 (with the header) instead of 2. But these are all really just details, the basic approach is the same. If you need the precise details, it might be easiest to read that document and then proceed to the comments in btreeInt.h. Dan. > > > > > Be a better Globetrotter. Get better travel answers from someone who knows. > Yahoo! Answers - Check it out. > http://answers.yahoo.com/dir/?link=list=396545469 > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Can the memory usage of SQLite be adjusted?
<[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > <[EMAIL PROTECTED]> wrote: > > > I completed my analysis of the SQLite database memory usage and I was > > > surprised to find that SQLite consumes so much memory. I ran my test > > > case (creates 31 tables) and found that SQLite consumed 545,231 bytes > > > of malloced memory before it started giving it back. > > > > > > > 100K? yes. 20K? no. > > > What is the set of commands to do this? > Ray > > There is no "command" to make SQLite use less memory. > Rather you have to use careful engineering to keep > the memory usage to a minimum. Start with this: > > PRAGMA default_cache_size=10; > > Then close your connection and reopen it and do this: > > PRAGMA cache_size; > > Verify that you got a "10" back so that the change took. > > When you compile, you will want to leave out as much stuff > as you do not need using SQLITE_OMIT_* flags. You will > want to take care in your use of SQL so that you do not > specify SQL statements that inherently require a lot of > memory. You'll probably want to make use of > sqlite3_soft_heap_limit() and sqlite3_release_memory(). > Stay far, far away from sqlite3_get_table(). And so > forth. Do we need to close the database connection and reopen it for the setting of cache_size to be effective? How do we reduce the memory by setting the cache_size? Will there be any freeing up of memory in case where cache_size is reduced? Regards, Phani - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite for 16bit
Hello everybody, I am new to this list. We are looking for a sql database that can run on a 16bit OS. I looked over the sqlite C source but I found too may 64bit integers, our OS only supports 23 bit longs. Has someone ported sqlite to a 16bit OS before? Or maybe someone knows another solution. An open database that runs on MS DOS? Thank you for your help Christoph - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] running testsuite
Hello. I am a newbie both to sqlite and tcl. I would like to learn how to run the test suite, so that later, when I start modifying the source code (e.g. to make a customized subset), I can verify that I have not broken anything. Is there a document somewhere that describes how to run the test suite? Or can someone describe how they have run the testsuite with the latest version on a Windows [XP] machine? Thank you! - sam - [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
