Re: [sqlite] ICU collation

[Sylvain Pointeau]

Igor,

you didn't understand my sentence, I said that I would be stupid to not use
ICU
as it is already used by sqlite...
means I want to use ICU and nothing else, because it is used in sqlite.

BUT

I find that the ICU extensions should provide a function to normalize a
string,
it would have me to not use ICU directly...

anyway, I will do it, that's not a problem :-)

relax, that's holidays soon :-P

Best regards,
Sylvain

On Tue, Dec 22, 2009 at 11:00 PM, Igor Tandetnik wrote:

> Sylvain Pointeau
>  wrote:
> > by the way, I will not use other thing than ICU to implement it
> > because I
> > already use it with sqlite then I would be really stupid to change
> > again of
> > API... don't you agree?
>
> No, I don't understand this point at all. SQLite uses fopen to open files
> and malloc to allocate memory - does it mean it's stupid to ever use fopen
> or malloc in a program that uses SQLite? Should one wait until SQLite
> provides an API for opening a file or allocating memory?
>
> Just because SQLite uses ICU doesn't mean you can't also use ICU for some
> additional functionality you need.
>
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] lemon: error handling

[D. Richard Hipp]

On Dec 22, 2009, at 6:06 PM, Benjamin Peterson wrote:

> I'm using lemon to write a parser for a little language I'm writing.
> I'm wondering how I indicate to lemon that an error has occurred in
> processing and an exit is needed. For example, if I have:
>
> stmt(A) ::= NAME(B). { A = malloc(sizeof(stmt)); A->name = B; }
>
> If malloc returns NULL, what should I do to escape the parser?

Configure the %extra_argument to be a pointer to a structure that  
holds the state of your parser.

  %extra_argument {Parser *pParser}

The pParser pointer is directly accessible from within your actions.   
So set a flag in that structure that tell the tokenizer to stop  
sending tokens and abort with an error.

 stmt(A) ::= NAME(B).  {
A = malloc(sizeof(stmt));
if( A==0 ){
   pParser->errFlag = 1;
}else{
  A->name = B;
   }
}

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

D. Richard Hipp
d...@hwaci.com



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

[sqlite] lemon: error handling

[Benjamin Peterson]

I'm using lemon to write a parser for a little language I'm writing.
I'm wondering how I indicate to lemon that an error has occurred in
processing and an exit is needed. For example, if I have:

stmt(A) ::= NAME(B). { A = malloc(sizeof(stmt)); A->name = B; }

If malloc returns NULL, what should I do to escape the parser?

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

Re: [sqlite] db corruption with zero bytes

[Max Vlasov]

On Tue, Dec 22, 2009 at 1:22 PM, Evilsmile  wrote:

> Hello,
>
> My sqlite version is 3.5.1 and there are a lot of db corruption in my
> system.
>
>

Please, let us know more about your language/platform
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ICU collation

[Igor Tandetnik]

Sylvain Pointeau
 wrote: 
> by the way, I will not use other thing than ICU to implement it
> because I 
> already use it with sqlite then I would be really stupid to change
> again of 
> API... don't you agree?

No, I don't understand this point at all. SQLite uses fopen to open files and 
malloc to allocate memory - does it mean it's stupid to ever use fopen or 
malloc in a program that uses SQLite? Should one wait until SQLite provides an 
API for opening a file or allocating memory?

Just because SQLite uses ICU doesn't mean you can't also use ICU for some 
additional functionality you need.

Igor Tandetnik

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

Re: [sqlite] ICU collation

[Sylvain Pointeau]

you probably think that I criticize sqlite, that's may why you answer in
this way.

... but I am not, I would just like that when ICU is compiled with,
to provide one or more function to use the rules of collation from ICU (you
know the ö=oe etc). In fact I am asking nothing to sqlite but to the ICU
extension, right?

by the way, I will not use other thing than ICU to implement it because I
already use it with sqlite then I would be really stupid to change again of
API... don't you agree?

for all, thank you very much for your help & explainations,
it really helped me to see the next steps for my case.

Many thanks
Sylvain


On Tue, Dec 22, 2009 at 8:46 PM, Igor Tandetnik  wrote:

> Sylvain Pointeau
>  wrote:
> > yes this is what I meant in my previous email,
> > but still another api to use ...
>
> Well, your choice: use another API that already exists, or wait for another
> API that currently doesn't.
>
> > It would have been easier if everything came from sqlite.
>
> If _everything_ came from SQLite, it wouldn't be very "lite", would it?
>
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ICU collation

[Igor Tandetnik]

Sylvain Pointeau
 wrote: 
> yes this is what I meant in my previous email,
> but still another api to use ...

Well, your choice: use another API that already exists, or wait for another API 
that currently doesn't.

> It would have been easier if everything came from sqlite.

If _everything_ came from SQLite, it wouldn't be very "lite", would it?

Igor Tandetnik


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

[sqlite] Bug in test file fuzz3.test

[Noah Hart]

This test file contains the tests

 

do_test fuzz2-3.0 {

  fuzzcatch {CREATE TRIGGER ...

do_test fuzz2-3.1 {

  fuzzcatch {CREATE TRIGGER ...

do_test fuzz2-3.2 {

  fuzzcatch {CREATE TEMP TRIGGER ...

 

TRIGGERS are unavailable when compiled with SQLITE_OMIT_TRIGGER

 

Test needs to be bracketed with 

 

ifcapable {trigger} {  # Only do the following tests if triggers are
enable

 

Regards,

 

Noah Hart

 




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


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

Re: [sqlite] ICU collation

[Sylvain Pointeau]

yes this is what I meant in my previous email,
but still another api to use ...
It would have been easier if everything came from sqlite.
(a new feature then? :-))

Best regards,
Sylvain

On Tue, Dec 22, 2009 at 7:54 PM, Igor Tandetnik  wrote:

> Sylvain Pointeau
>  wrote:
> > ok then to do it on the application side but why sqlite does not
> > provide simple functions along with ICU to normalize a string
> > following a specified locale? Then I will be able implement it on the
> > application layer.
>
> You can just use ICU directly in your application layer, can't you?
>
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug in test file tkt-3fe897352e.test

[Noah Hart]

This test file has a call to hex_to_utf16be 

which is unavailable when compiled with SQLITE_OMIT_UTF16

 

Test needs to be bracketed with ifcapable {utf16} 

 

Regards,

 

Noah Hart

 




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


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

Re: [sqlite] ICU collation

[Igor Tandetnik]

Sylvain Pointeau
 wrote: 
> ok then to do it on the application side but why sqlite does not
> provide simple functions along with ICU to normalize a string
> following a specified locale? Then I will be able implement it on the
> application layer. 

You can just use ICU directly in your application layer, can't you?

Igor Tandetnik

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

Re: [sqlite] ICU collation

[Sylvain Pointeau]

ok then to do it on the application side but why sqlite does not provide
simple functions along with ICU to normalize a string following a specified
locale? Then I will be able implement it on the application layer.

I think that trying to re-implement myself the rules ä=ae ü=ue is waste of
time, at worst I would use ICU behind ... I want to rely on something well
tested :-)

may I can ask for having some functions inside SQLite to provide the
normalization etc?
That would be really great :-)

Best regards,
Sylvain

On Tue, Dec 22, 2009 at 5:44 PM, Nicolas Williams
wrote:

> On Tue, Dec 22, 2009 at 07:49:24AM -0500, Tim Romano wrote:
> > On 12/22/2009 5:31 AM, Sylvain Pointeau wrote:
> > > It cannot be done in the application layer...
> > >
> > You are wrong about that. I have written a full-text search application
> > to go against ancient Germanic texts where, for example, there were two
> > dozen ways to spell the word for modern English 'sister' --spelling had
> > not yet been regularized but reflected regional dialect pronunciations
> > and regional scriptorium conventions.  There is no way an ICU collation
> > could handle that crazy quilt and it had to be done in the application
> > layer.
> >
> > It is done in the application layer  by *normalizing* the data on its
> > way into the database and then, of course, you must also normalize the
> > search terms as the user supplies them.  So, for example, if you are
> > importing a-umlaut you store 'ae' and if you are given a-umlaut as a
> > search term by the user, you search for 'ae'.  Normalization of
> > graphemes is analogous to Unicode decomposition of composite characters.
>
> Indeed.
>
> > However, if SQLite can flip an ICU German collation into Full
> > Normalization mode this could be done in the database.
>
> Sure, but you lose control in the process.  Suppose you eventually need
> to add support for non-German locales yet you've been using a collation
> that performs these conversions -- oops.  Or suppose there are
> well-known words of foreign origin to which this sort of normalization
> must not be applied: a generic toolkit like ICU, that works
> character-by-character or codepoint-by-codepoint, will not know about
> them -- why should it?  And so on.
>
> > P.S. I recently asked for a lightweight raw "reverse-string"
> > (codepoint-by-codepoint) function to be added to the SQLite core
> > (because I don't have access to its UDF mechanism in Adobe
> > Flex/FlashBuilder) and do agree that there are often good reasons for
> > wanting something to be done in the database layer, provided it does not
> > slow the database down for everyone else.
>
> Yes.  I believe that databases need to support Unicode normalization-
> insensitive/preserving behavior, at least as an option (most input
> methods produce pre-composed output, so often one can get away with not
> normalizing at all).
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ICU collation

[Nicolas Williams]

On Tue, Dec 22, 2009 at 07:49:24AM -0500, Tim Romano wrote:
> On 12/22/2009 5:31 AM, Sylvain Pointeau wrote:
> > It cannot be done in the application layer...
> >
> You are wrong about that. I have written a full-text search application 
> to go against ancient Germanic texts where, for example, there were two 
> dozen ways to spell the word for modern English 'sister' --spelling had 
> not yet been regularized but reflected regional dialect pronunciations 
> and regional scriptorium conventions.  There is no way an ICU collation 
> could handle that crazy quilt and it had to be done in the application 
> layer.
> 
> It is done in the application layer  by *normalizing* the data on its 
> way into the database and then, of course, you must also normalize the 
> search terms as the user supplies them.  So, for example, if you are 
> importing a-umlaut you store 'ae' and if you are given a-umlaut as a 
> search term by the user, you search for 'ae'.  Normalization of 
> graphemes is analogous to Unicode decomposition of composite characters.

Indeed.

> However, if SQLite can flip an ICU German collation into Full 
> Normalization mode this could be done in the database.

Sure, but you lose control in the process.  Suppose you eventually need
to add support for non-German locales yet you've been using a collation
that performs these conversions -- oops.  Or suppose there are
well-known words of foreign origin to which this sort of normalization
must not be applied: a generic toolkit like ICU, that works
character-by-character or codepoint-by-codepoint, will not know about
them -- why should it?  And so on.

> P.S. I recently asked for a lightweight raw "reverse-string" 
> (codepoint-by-codepoint) function to be added to the SQLite core 
> (because I don't have access to its UDF mechanism in Adobe 
> Flex/FlashBuilder) and do agree that there are often good reasons for 
> wanting something to be done in the database layer, provided it does not 
> slow the database down for everyone else.

Yes.  I believe that databases need to support Unicode normalization-
insensitive/preserving behavior, at least as an option (most input
methods produce pre-composed output, so often one can get away with not
normalizing at all).

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

[sqlite] C++Builder 3.0 - A beginners problem

[peter.wallmander]

Hi,
when I compile sqlite3.c the compiler generates 3 error messages. The first 
one occurs on line 15474 and reads "Type mismatch in parameter 'Exchange' in 
call to 'InterlockedCompareExchange'." The next error is on line 15492: "Type 
mismatch in parameter 'Comperand' in call to 'InterlockedCompareExchange'." 
Since there are many warnings too, the compiler ends with the third error 
message "Too many error or warning messages".
I would very much appreciate being saved from this puzzlement. 
Kind regards
Peter

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

Re: [sqlite] ICU collation

[Tim Romano]

Sylvain,
Here is a link I provided earlier, showing how normalization can be done 
in the application layer:

http://php.net/manual/de/normalizer.normalize.php

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

Re: [sqlite] ICU collation

[Tim Romano]

On 12/22/2009 5:31 AM, Sylvain Pointeau wrote:
> It cannot be done in the application layer...
>
You are wrong about that. I have written a full-text search application 
to go against ancient Germanic texts where, for example, there were two 
dozen ways to spell the word for modern English 'sister' --spelling had 
not yet been regularized but reflected regional dialect pronunciations 
and regional scriptorium conventions.  There is no way an ICU collation 
could handle that crazy quilt and it had to be done in the application 
layer.

It is done in the application layer  by *normalizing* the data on its 
way into the database and then, of course, you must also normalize the 
search terms as the user supplies them.  So, for example, if you are 
importing a-umlaut you store 'ae' and if you are given a-umlaut as a 
search term by the user, you search for 'ae'.  Normalization of 
graphemes is analogous to Unicode decomposition of composite characters.

However, if SQLite can flip an ICU German collation into Full 
Normalization mode this could be done in the database.

P.S. I recently asked for a lightweight raw "reverse-string" 
(codepoint-by-codepoint) function to be added to the SQLite core 
(because I don't have access to its UDF mechanism in Adobe 
Flex/FlashBuilder) and do agree that there are often good reasons for 
wanting something to be done in the database layer, provided it does not 
slow the database down for everyone else.

Regards
Tim Romano



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

[sqlite] db corruption with zero bytes

[Evilsmile]

Hello,

My sqlite version is 3.5.1 and there are a lot of db corruption in my
system.
Given the following pragma integrity_check output:
> sqlite> pragma integrity_check;
> *** in database main ***
> Page 236054 is never used
> Page 236055 is never used
> rowid 0 missing from index sqlite_autoindex_aggr_log_1
> rowid 17 missing from index sqlite_autoindex_aggr_log_1
> rowid 21 missing from index sqlite_autoindex_aggr_log_1

I try to use UltraEdit to open the these corrupted databases with
hexadecimal mode, and find there are two situations.
1) There are 8 zero bytes in the record.
2) There are 8 zero bytes in the record header.

When using "select * from table" command, it will read data from table
until the zero bytes. And the sqlite of version 3.5.1 can read more
data from corrupted db than version 3.6.20. (When using ".dump"
command, the sqlite of version 3.5.1 will dump almost data from db and
version will dump a little.)
PS: the data is created by sqlite of version 3.5.

I have no idea about what happened. Is there any fix after version
3.5.1 about this situation? or what can I do?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE the order of INT fileds

[Simon Slavin]

On 22 Dec 2009, at 11:48am, Andrea Galeazzi wrote:

> Probably it won't work because ID must be UNIQUE and Name is not.

Ah.  Okay, pick an arbitrary number, perhaps 999, to use temporarily for the 
entry that's being moved.

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

Re: [sqlite] UPDATE the order of INT fileds

[Andrea Galeazzi]

Simon Slavin ha scritto:
> On 22 Dec 2009, at 10:25am, Andrea Galeazzi wrote:
>
>   
>> ID INTEGER,
>> Name TEXT
>>
>> So, for instance, I can have:
>>
>> 1 Julia
>> 2 Eric
>> 3 Kevin
>> 4 Sarah
>> 5 John
>>
>> Now I wanna move Eric from 2 to 4 in order to yield (by performing a 
>> series of UPDATE of ID field):
>>
>> 1 Julia
>> 2 Kevin
>> 3 Sarah
>> 4 Eric
>> 5 John
>>
>> What's the correct sequence of SQL statements to accomplish a such kind 
>> of task?
>> 
>
> Something like
>
> UPDATE myTable SET id=id-1 WHERE id>oldplace AND id<=newplace
> UPDATE myTable SET id=newplace WHERE name='Eric'
>
> You will need to detect whether you're moving the single record up or down 
> and change the UPDATE accordingly (or you could use ABS but that would be 
> even more confusing).
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> __ Informazioni da ESET NOD32 Antivirus, versione del database delle 
> firme digitali 4708 (20091222) __
>
> Il messaggio è stato controllato da ESET NOD32 Antivirus.
>
> www.nod32.it
>
>
>
>
>   
Probably it won't work because ID must be UNIQUE and Name is not.
Other ideas?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE the order of INT fileds

[Simon Slavin]

On 22 Dec 2009, at 10:25am, Andrea Galeazzi wrote:

> ID INTEGER,
> Name TEXT
> 
> So, for instance, I can have:
> 
> 1 Julia
> 2 Eric
> 3 Kevin
> 4 Sarah
> 5 John
> 
> Now I wanna move Eric from 2 to 4 in order to yield (by performing a 
> series of UPDATE of ID field):
> 
> 1 Julia
> 2 Kevin
> 3 Sarah
> 4 Eric
> 5 John
> 
> What's the correct sequence of SQL statements to accomplish a such kind 
> of task?

Something like

UPDATE myTable SET id=id-1 WHERE id>oldplace AND id<=newplace
UPDATE myTable SET id=newplace WHERE name='Eric'

You will need to detect whether you're moving the single record up or down and 
change the UPDATE accordingly (or you could use ABS but that would be even more 
confusing).

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

Re: [sqlite] ICU collation

[Simon Slavin]

On 22 Dec 2009, at 10:55am, Jean-Denis Muys wrote:

> On 12/22/09 11:31 , "Sylvain Pointeau"  wrote:
> 
>> Imagine that I want to query my database for a certain type of word,
>> the user could enter ü or ue and I will display the corresponding items...
>> 
>> It cannot be done in the application layer...
>> 
> 
> Or maybe it can... You could for example maintain in your application,
> possibly in a SQLite table for that purpose, all equivalence classes of all
> characters.
> 
> The obvious optimisation is to omit an equivalence class when it's a
> singleton.
> 
> So you are left with a few equivalence classes that may look like this:
> 
> ü, ue
> ä, ae, æ
> e, é, è, ê, ë
> 
> (or whatever).

But at that distance you might as well get rid of vowels entirely.  That is 
what SOUNDEX and Metaphone are for:



Not only do they make up for minor spelling errors, but they also compensate 
for different character sets.  You can see some details of German equivalents 
here:




As you can see, these things should definitely be done in the application 
layer.  Store both forms of your text: the original text and the encoded form.

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

[sqlite] UPDATE the order of INT fileds

[Andrea Galeazzi]

Hi,
I'm trying to solve this problem:
I've got the following table, made up of two fields:

ID INTEGER,
Name TEXT

So, for instance, I can have:

1 Julia

2 Eric

3 Kevin

4 Sarah

5 John

Now I wanna move Eric from 2 to 4 in order to yield (by performing a 
series of UPDATE of ID field):

1 Julia

2 Kevin

3 Sarah

4 Eric

5 John


What's the correct sequence of SQL statements to accomplish a such kind 
of task?
Obviously I could also want to move the records in the opposite order (3 
to 1)

Cheers


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

Re: [sqlite] usage of indexes - query performance

[Jens Miltner]

Am 18.12.2009 um 15:58 schrieb Pavel Ivanov:

> As you said because of your LEFT JOIN SQLite (or any other DBMS in its
> place) is forced to use t2 as a base table. You have no conditions on
> t2, so SQLite will make full scan on it and for each row it will need
> to pick up a corresponding rows from t1 which it does using primary
> index.
>
> And FYI, by using condition "where t1.uid = 'x'" you're killing all
> purpose of LEFT JOIN - you're forcing for the row in t1 to exist and
> thus you can with the same success use INNER JOIN for this query. But
> if you do use it I believe SQLite will be smart enough to select rows
> from t1 using t1_idx and then for each row pick up corresponding row
> from t2 using t2_idx.

Thanks, you're right, of course... sometimes, it's hard to think  
outside one's box :(

And yes - using an INNER JOIN will actually make SQLite use the proper  
indexes.

Thanks a lot!





>
> On Fri, Dec 18, 2009 at 3:52 AM, Jens Miltner  wrote:
>> Hi everybody,
>>
>> consider the following database schema:
>>
>> CREATE TABLE t1 (
>>id INTEGER PRIMARY KEY,
>>uid,
>>x
>> );
>> CREATE INDEX t1_idx ON t1(uid);
>> CREATE TABLE t2 (
>>id INTEGER PRIMARY KEY,
>> t1_id INTEGER,
>>y
>> );
>> CREATE INDEX t2_idx ON t2(t1_id);
>>
>>
>> Shouldn't the following query attempt to use both indexes, instead of
>> not using an index on table t2:
>>
>> sqlite> EXPLAIN QUERY PLAN SELECT * FROM t2 LEFT JOIN t1 ON
>> t1.id=t2.t1_id WHERE t1.uid='x';
>> 0|0|TABLE t2
>> 1|1|TABLE t1 USING PRIMARY KEY
>>
>> even when explicitely forcing to use t1_idx, no index on t2 will be
>> used:
>>
>> sqlite> EXPLAIN QUERY PLAN SELECT * FROM t2 LEFT JOIN t1 ON
>> +t1.id=t2.t1_id WHERE t1.uid='x';
>> 0|0|TABLE t2
>> 1|1|TABLE t1 WITH INDEX t1_idx
>>
>>
>> I would have expected the query optimizer to use t1_idx and t2_idx?
>> I understand that using the LEFT JOIN in the way I did above will  
>> have
>> to use t2 as the base table, but shouldn't this also be possible when
>> filtering on t1 first (by uid) and then applying the JOIN to records
>> from t2 satisfying the JOIN condition?
>>
>> It definitely would make sense in the case I have in mind where t2
>> contains much, much more records than t1 and only very few of them
>> actually satisfy the JOIN condition AND the uid condition...
>> I found that I can rewrite the query as
>>
>> sqlite> EXPLAIN QUERY PLAN SELECT * FROM t2 LEFT JOIN t1 ON
>> t1.id=t2.t1_id WHERE t2.t1_id=(SELECT id FROM t1 WHERE uid='x');
>> 0|0|TABLE t2 WITH INDEX t2_idx
>> 1|1|TABLE t1 USING PRIMARY KEY
>> 0|0|TABLE t1 WITH INDEX t1_idx
>>
>> but this is somewhat awkward and hard to do in our case, since the
>> queries are generated dynamically...
>> Wouldn't/Shouldn't the query optimizer be able to do this kind of
>> optimization without me explicitely rewriting the query (especially
>> when I have run ANALYZE, so the optimizer knows the data  
>> distribution)?
>>
>> Don't get me wrong - I'm not trying to complain, I'd just like to
>> understand whether there is potential for the query optimizer to
>> transparently optimize queries as the above or whether I'm completely
>> off with my idea :)
>>
>>
>> Thanks,
>> 
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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