Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
On 3/09/2007 1:00 AM, RaghavendraK 70574 wrote:
Hi,
create table test (column text);
Please try pasting in SQL that actually runs. "column" is a reserved word.
create index idx on text(column);[IN uses index]
Please try pasting in SQL that actually runs. You need "test", not
"text". Use -- for comments.
insert into test values ('9');
insert into test values ('98');
insert into test values ('985');
My Query: see if u have any record
98451234 which has a similar pattern.
The literal answer to that is a boolean, not one row.
select * from test where column in
('98451234','9845123','984512','98451',
'9845','984','98','9','-1') limit 1;
even when limit 1 is provided it continues to search.
You don't want the FIRST, you want the LONGEST.
8<--- ragha.sql
create table test (acol text, guff integer);
create index idx on test(acol);
insert into test values ('9', 1);
insert into test values ('98', 42);
insert into test values ('98', 43);
insert into test values ('985', 666);
select * from test t where length(t.acol) = (
select max(length(ty.rescol)) from (
select tx.acol as rescol from test tx where tx.acol in
('98451234','9845123','984512','98451', '9845','984','98','9','')
) ty
);
8<---
Result:
sqlite> .read ragha.sql
98|42
98|43
sqlite>
Pls suggest a better way.
Get your head out of the VDBE. Your problem is nothing to do with
telling the VDBE what to do. It's not even anything to do with sqlite.
Your problem is that SQL is not suited for the type of processing that
you are trying to do. If you have to fight a language to the extent that
you are doing, you are using the wrong language. If you must experiment
with fancy indexing or fuzzy matching or whatever, use ODBC to an sqlite
database from an easy-to-use language like Python. Instead of one
humungous query, try a 3-step exercise: (query , process results with
Python, 2nd query to get result rows).
HTH,
John
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
On 9/2/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> select * from test where column in
> ('98451234','9845123','984512','98451',
> '9845','984','98','9','-1') limit 1;
>
> even when limit 1 is provided it continues to search.
Continues to search in what way? What exactly are you looking at?
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
Hi,
create table test (column text);
create index idx on text(column);[IN uses index]
insert into test values ('9');
insert into test values ('98');
insert into test values ('985');
My Query: see if u have any record
98451234 which has a similar pattern.
select * from test where column in
('98451234','9845123','984512','98451',
'9845','984','98','9','-1') limit 1;
even when limit 1 is provided it continues to search.
Pls suggest a better way.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Christian Smith <[EMAIL PROTECTED]>
Date: Sunday, September 2, 2007 8:15 pm
Subject: Re: [sqlite] Difference between OR and IN
[http://www.sqlite.org/php2004/page-052.html]
> Once you get your first row back (corresponding to (a==1), simply
> halt
> there and sqlite3_finalize() or sqlite3_reset the statement. You
> control
> the execution and how many rows you want back.
>
>
> RaghavendraK 70574 uttered:
>
> > Hi,
> >
> > Ok.
> >
> > Is there any way to tell the VDBE to stop execution moment it
> gets a record
> > from the IN list rather than continue to query for all the
> parameters?>
> > I mean can it work like the C if clause
> > a = 1;
> > b = 100;
> > if( a == 1 or b == 10)
> > {
> > }
> >
> > in the above case a is evauated but not b. Is this possible in
> SQL or SQLite?
> > Pls suggest.
> >
> > regards
> > ragha
> >
> >
> >
> **>
> This email and its attachments contain confidential information from HUAWEI,
> which is intended only for the person or entity whose address is listed
> above. Any use of the information contained herein in any way (including, but
> not limited to, total or partial disclosure, reproduction, or dissemination)
> by persons other than the intended recipient(s) is prohibited. If you receive
> this e-mail in error, please notify the sender by phone or email immediately
> and delete it!
> >
> *****************************>
> > ----- Original Message -
> > From: Trevor Talbot <[EMAIL PROTECTED]>
> > Date: Sunday, September 2, 2007 1:03 am
> > Subject: Re: [sqlite] Difference between OR and IN
> [http://www.sqlite.org/php2004/page-052.html]
> >
> >> On 9/1/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> >>
> >>> I want to know why
> >>> prepareStatement: select * from xxx where IN (?);
> >>> stmt.bind("abc,xyz,123"); is not supported for multiple
> >>> values.
> >>
> >> It's not supported because it doesn't make sense. The parametric
> >> binding mechanism is for single values; it's not a macro-like text
> >> replacement system. With your syntax, how do I bind a set of
> >> integers? Strings? Blobs?
> >>
> >> One common use for parametric binding (besides convenience) is to
> >> avoid SQL injection attacks. The example you posted doesn't do
> that;>> you have to manually escape each individual value to make
> sure it's
> >> valid syntax for the IN() group in text form. Why even use
> parameters>> in that case? It's the same amount of work whether
> you build the
> >> entire SQL statement or not.
> >>
> >> All common databases I'm aware of work exactly the same way.
> >>
> >> -
> --
> >> --
> >> To unsubscribe, send email to [EMAIL PROTECTED]
> >> -
> --
> >> --
> >>
> >>
> >
> > --
> ---
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > --
> ---
> >
>
> --
> /"\
> \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
> X - AGAINST MS ATTACHMENTS
> / \
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
Once you get your first row back (corresponding to (a==1), simply halt
there and sqlite3_finalize() or sqlite3_reset the statement. You control
the execution and how many rows you want back.
RaghavendraK 70574 uttered:
Hi,
Ok.
Is there any way to tell the VDBE to stop execution moment it gets a record
from the IN list rather than continue to query for all the parameters?
I mean can it work like the C if clause
a = 1;
b = 100;
if( a == 1 or b == 10)
{
}
in the above case a is evauated but not b. Is this possible in SQL or SQLite?
Pls suggest.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Trevor Talbot <[EMAIL PROTECTED]>
Date: Sunday, September 2, 2007 1:03 am
Subject: Re: [sqlite] Difference between OR and IN
[http://www.sqlite.org/php2004/page-052.html]
On 9/1/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
I want to know why
prepareStatement: select * from xxx where IN (?);
stmt.bind("abc,xyz,123"); is not supported for multiple
values.
It's not supported because it doesn't make sense. The parametric
binding mechanism is for single values; it's not a macro-like text
replacement system. With your syntax, how do I bind a set of
integers? Strings? Blobs?
One common use for parametric binding (besides convenience) is to
avoid SQL injection attacks. The example you posted doesn't do that;
you have to manually escape each individual value to make sure it's
valid syntax for the IN() group in text form. Why even use parameters
in that case? It's the same amount of work whether you build the
entire SQL statement or not.
All common databases I'm aware of work exactly the same way.
---
--
To unsubscribe, send email to [EMAIL PROTECTED]
---
--
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Fwd: Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
Hi,
Ok.
Is there any way to tell the VDBE to stop execution moment it gets a record
from the IN list rather than continue to query for all the parameters?
I mean can it work like the C if clause
a = 1;
b = 100;
if( a == 1 or b == 10)
{
}
in the above case a is evauated but not b. Is this possible in SQL or SQLite?
Pls suggest.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
--- Begin Message ---
Hi,
Ok.
Is there any way to tell the VDBE to stop execution moment it gets a record
from the IN list rather than continue to query for all the parameters?
I mean can it work like the C if clause
a = 1;
b = 100;
if( a == 1 or b == 10)
{
}
in the above case a is evauated but not b. Is this possible in SQL or SQLite?
Pls suggest.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Trevor Talbot <[EMAIL PROTECTED]>
Date: Sunday, September 2, 2007 1:03 am
Subject: Re: [sqlite] Difference between OR and IN
[http://www.sqlite.org/php2004/page-052.html]
> On 9/1/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
>
> > I want to know why
> > prepareStatement: select * from xxx where IN (?);
> > stmt.bind("abc,xyz,123"); is not supported for multiple
> > values.
>
> It's not supported because it doesn't make sense. The parametric
> binding mechanism is for single values; it's not a macro-like text
> replacement system. With your syntax, how do I bind a set of
> integers? Strings? Blobs?
>
> One common use for parametric binding (besides convenience) is to
> avoid SQL injection attacks. The example you posted doesn't do that;
> you have to manually escape each individual value to make sure it's
> valid syntax for the IN() group in text form. Why even use parameters
> in that case? It's the same amount of work whether you build the
> entire SQL statement or not.
>
> All common databases I'm aware of work exactly the same way.
>
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--- End Message ---
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
Hi,
Ok.
Is there any way to tell the VDBE to stop execution moment it gets a record
from the IN list rather than continue to query for all the parameters?
I mean can it work like the C if clause
a = 1;
b = 100;
if( a == 1 or b == 10)
{
}
in the above case a is evauated but not b. Is this possible in SQL or SQLite?
Pls suggest.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Trevor Talbot <[EMAIL PROTECTED]>
Date: Sunday, September 2, 2007 1:03 am
Subject: Re: [sqlite] Difference between OR and IN
[http://www.sqlite.org/php2004/page-052.html]
> On 9/1/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
>
> > I want to know why
> > prepareStatement: select * from xxx where IN (?);
> > stmt.bind("abc,xyz,123"); is not supported for multiple
> > values.
>
> It's not supported because it doesn't make sense. The parametric
> binding mechanism is for single values; it's not a macro-like text
> replacement system. With your syntax, how do I bind a set of
> integers? Strings? Blobs?
>
> One common use for parametric binding (besides convenience) is to
> avoid SQL injection attacks. The example you posted doesn't do that;
> you have to manually escape each individual value to make sure it's
> valid syntax for the IN() group in text form. Why even use parameters
> in that case? It's the same amount of work whether you build the
> entire SQL statement or not.
>
> All common databases I'm aware of work exactly the same way.
>
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
On 9/1/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> I want to know why
> prepareStatement: select * from xxx where IN (?);
> stmt.bind("abc,xyz,123"); is not supported for multiple
> values.
It's not supported because it doesn't make sense. The parametric
binding mechanism is for single values; it's not a macro-like text
replacement system. With your syntax, how do I bind a set of
integers? Strings? Blobs?
One common use for parametric binding (besides convenience) is to
avoid SQL injection attacks. The example you posted doesn't do that;
you have to manually escape each individual value to make sure it's
valid syntax for the IN() group in text form. Why even use parameters
in that case? It's the same amount of work whether you build the
entire SQL statement or not.
All common databases I'm aware of work exactly the same way.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
Thx.
I want to know why
prepareStatement: select * from xxx where IN (?);
stmt.bind("abc,xyz,123"); is not supported for multiple
values.
Instead sqlite expect use to declare the statement var before hand,
is it due to limitation of Virtual Machine impl?
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Dan Kennedy <[EMAIL PROTECTED]>
Date: Saturday, September 1, 2007 11:59 am
Subject: Re: [sqlite] Difference between OR and IN
[http://www.sqlite.org/php2004/page-052.html]
> On Sat, 2007-09-01 at 11:58 +0800, RaghavendraK 70574 wrote:
> > Hi,
> >
> > In one of the slides "http://www.sqlite.org/php2004/page-052.html;
> > it is stated "OR will make DBEngine not to use Indexes"
> and IN will make DBEngine use
> > indexes"
> >
> > I could not understand the rationale abt this. Can u explain?
>
> The point is (was) that if you have a table like this:
>
> CREATE TABLE abc(a PRIMARY KEY, b, c);
>
> Then the first query uses the index on a, the second does (did) not:
>
> SELECT * FROM abc WHERE a IN ('hello', 'world');
> SELECT * FROM abc WHERE a = 'hello' OR a = 'world';
>
> However, since those slides were written SQLite has been updated
> so that
> queries of the second form are internally transformed to the
> first, and
> therefore may use an index.
>
> Dan.
>
> >
> > regards
> > ragha
> >
> >
> **>
> This email and its attachments contain confidential information from
> HUAWEI, which is intended only for the person or entity whose address is
> listed above. Any use of the information contained herein in any way
> (including, but not limited to, total or partial disclosure, reproduction, or
> dissemination) by persons other than the intended recipient(s) is prohibited.
> If you receive this e-mail in error, please notify the sender by phone or
> email immediately and delete it!
> >
> *>
>
> > -
>
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
>
> >
>
>
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
On Sat, 2007-09-01 at 11:58 +0800, RaghavendraK 70574 wrote:
> Hi,
>
> In one of the slides "http://www.sqlite.org/php2004/page-052.html;
> it is stated "OR will make DBEngine not to use Indexes" and IN will
> make DBEngine use
> indexes"
>
> I could not understand the rationale abt this. Can u explain?
The point is (was) that if you have a table like this:
CREATE TABLE abc(a PRIMARY KEY, b, c);
Then the first query uses the index on a, the second does (did) not:
SELECT * FROM abc WHERE a IN ('hello', 'world');
SELECT * FROM abc WHERE a = 'hello' OR a = 'world';
However, since those slides were written SQLite has been updated so that
queries of the second form are internally transformed to the first, and
therefore may use an index.
Dan.
>
> regards
> ragha
>
> **
> This email and its attachments contain confidential information from HUAWEI,
> which is intended only for the person or entity whose address is listed
> above. Any use of the information contained herein in any way (including, but
> not limited to, total or partial disclosure, reproduction, or dissemination)
> by persons other than the intended recipient(s) is prohibited. If you receive
> this e-mail in error, please notify the sender by phone or email immediately
> and delete it!
>
> *
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
