Re: [sqlite] BestMatch and SqliteStatment Clash
Yes,i understand it is no problem,my mistake.
One another thing is how does reversing of value and column work,
i mean the internals of it. Does it create any temp table ...
And i see the sqlite documentation of like, which say if the first char is not
a wild char then index
will be helpful.
create table test(t text);
insert into test values ('9');
insert into test values('98');
insert into test values('983');
insert into test values('9854');
select * from test where '982' like t || '%' order by t desc limit 1;
output: 98 [correct]
regard
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: Dennis Cote <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 8:45 pm
Subject: Re: [sqlite] BestMatch and SqliteStatment Clash
> RaghavendraK 70574 wrote:
> > Hi,
> >
> > There are 2 testcases one works and other fails
> > Hope am clear.
> >
> > SqliteVersion: 3.4.0
> >
> > TestCase 1: works
> >
> > create table test(t text);
> >
> > insert into test values ('9');
> > insert into test values('98');
> > insert into test values('983');
> > insert into test values('9854');
> >
> > select * from test where '982' like t || '%' order by t desc
> limit 1;
> >
> > output: 98 [correct]
> >
> > TestCase 2: does not work
> > create table 'tbl.7'(ver integer,
> > column1 text not NULL,
> > column2 text not NULL,
> > column3 text not NULL,
> > column4 text not NULL,
> > column5 text not NULL,
> >column6 text not NULL,
> > column7 text not NULL,
> > column8 text not NULL,
> > column9 text not NULL,
> > column10 text not NULL,
> > primary
> key(ver,column1,column2,column3,column4,column5));>
> > insert into 'tbl.7'
> > values
> > (7, '9845002655', '1', '1', '1', '1','x','x','x',
> > 'x','x');
> >
> > insert into 'tbl.7'
> > values
> > (7, '9855002655', '1', '1', '1', '1','x','x','x',
> > 'x','x');
> >
> > --Best match for 985 shd be 9855002655
> > select * from 'tbl.7' where '985' like column1 || '%' order by
> column1 desc limit 1;
> >
> > output: none //this is a bug.
> >
> >
> >> '9854002656%' is not a match for '982', so seems not to be a bug
> >>
> >>
> >
> As has been pointed out several times already your expectations are
> wrong.
> You need additional code to implement your best match criterion.
> What
> you might want to match is the substring of the column up to the
> length
> of the target string, not the entire column.
>
> select * from tab
> where :target like substr(column1,1,length(:target)) || '%'
> order by column1 desc limit 1;
>
> I'm not sure why you think the first such match is the best match,
> but
> that is another issue for you to look at.
>
> Another point, you should be using double quotes around your table
> name,
> not single quotes.
>
> HTH
> Dennis Cote
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] BestMatch and SqliteStatment Clash
RaghavendraK 70574 wrote:
Hi,
There are 2 testcases one works and other fails
Hope am clear.
SqliteVersion: 3.4.0
TestCase 1: works
create table test(t text);
insert into test values ('9');
insert into test values('98');
insert into test values('983');
insert into test values('9854');
select * from test where '982' like t || '%' order by t desc limit 1;
output: 98 [correct]
TestCase 2: does not work
create table 'tbl.7'(ver integer,
column1 text not NULL,
column2 text not NULL,
column3 text not NULL,
column4 text not NULL,
column5 text not NULL,
column6 text not NULL,
column7 text not NULL,
column8 text not NULL,
column9 text not NULL,
column10 text not NULL,
primary key(ver,column1,column2,column3,column4,column5));
insert into 'tbl.7'
values
(7, '9845002655', '1', '1', '1', '1','x','x','x',
'x','x');
insert into 'tbl.7'
values
(7, '9855002655', '1', '1', '1', '1','x','x','x',
'x','x');
--Best match for 985 shd be 9855002655
select * from 'tbl.7' where '985' like column1 || '%' order by column1 desc
limit 1;
output: none //this is a bug.
'9854002656%' is not a match for '982', so seems not to be a bug
As has been pointed out several times already your expectations are wrong.
You need additional code to implement your best match criterion. What
you might want to match is the substring of the column up to the length
of the target string, not the entire column.
select * from tab
where :target like substr(column1,1,length(:target)) || '%'
order by column1 desc limit 1;
I'm not sure why you think the first such match is the best match, but
that is another issue for you to look at.
Another point, you should be using double quotes around your table name,
not single quotes.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] BestMatch and SqliteStatment Clash
hi ragha,
On 29/08/2007, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> Hi,
>
> There are 2 testcases one works and other fails
> Hope am clear.
>
It is only clear that you are misunderstanding the operation of the
'like' operator.
See Igor's post for explanation...
Rgds,
Simon
On 29/08/2007, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> Hi,
>
> There are 2 testcases one works and other fails
> Hope am clear.
>
> SqliteVersion: 3.4.0
>
> TestCase 1: works
>
> create table test(t text);
>
> insert into test values ('9');
> insert into test values('98');
> insert into test values('983');
> insert into test values('9854');
>
> select * from test where '982' like t || '%' order by t desc limit 1;
>
> output: 98 [correct]
>
> TestCase 2: does not work
> create table 'tbl.7'(ver integer,
> column1 text not NULL,
> column2 text not NULL,
> column3 text not NULL,
> column4 text not NULL,
> column5 text not NULL,
> column6 text not NULL,
> column7 text not NULL,
> column8 text not NULL,
> column9 text not NULL,
> column10 text not NULL,
> primary
> key(ver,column1,column2,column3,column4,column5));
>
> insert into 'tbl.7'
> values
> (7, '9845002655', '1', '1', '1', '1','x','x','x',
> 'x','x');
>
> insert into 'tbl.7'
> values
> (7, '9855002655', '1', '1', '1', '1','x','x','x',
> 'x','x');
>
> --Best match for 985 shd be 9855002655
> select * from 'tbl.7' where '985' like column1 || '%' order by column1 desc
> limit 1;
>
> output: none //this is a bug.
>
> **
> 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: Simon Davies <[EMAIL PROTECTED]>
> Date: Wednesday, August 29, 2007 7:39 pm
> Subject: Re: [sqlite] BestMatch and SqliteStatment Clash
>
> > On 29/08/2007, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> > >
> > > select * from test where '982' like t || '%' order by t desc
> > limit 1;
> > >
> > > This works but will not work for earlier data(9854002656).So
> > seems to be bug.
> > >
> > > regards
> > > ragha
> >
> > '9854002656%' is not a match for '982', so seems not to be a bug
> >
> > Rgds,
> > Simon
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] BestMatch and SqliteStatment Clash
Hi,
There are 2 testcases one works and other fails
Hope am clear.
SqliteVersion: 3.4.0
TestCase 1: works
create table test(t text);
insert into test values ('9');
insert into test values('98');
insert into test values('983');
insert into test values('9854');
select * from test where '982' like t || '%' order by t desc limit 1;
output: 98 [correct]
TestCase 2: does not work
create table 'tbl.7'(ver integer,
column1 text not NULL,
column2 text not NULL,
column3 text not NULL,
column4 text not NULL,
column5 text not NULL,
column6 text not NULL,
column7 text not NULL,
column8 text not NULL,
column9 text not NULL,
column10 text not NULL,
primary key(ver,column1,column2,column3,column4,column5));
insert into 'tbl.7'
values
(7, '9845002655', '1', '1', '1', '1','x','x','x',
'x','x');
insert into 'tbl.7'
values
(7, '9855002655', '1', '1', '1', '1','x','x','x',
'x','x');
--Best match for 985 shd be 9855002655
select * from 'tbl.7' where '985' like column1 || '%' order by column1 desc
limit 1;
output: none //this is a bug.
**
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: Simon Davies <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 7:39 pm
Subject: Re: [sqlite] BestMatch and SqliteStatment Clash
> On 29/08/2007, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
> >
> > select * from test where '982' like t || '%' order by t desc
> limit 1;
> >
> > This works but will not work for earlier data(9854002656).So
> seems to be bug.
> >
> > regards
> > ragha
>
> '9854002656%' is not a match for '982', so seems not to be a bug
>
> Rgds,
> Simon
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] BestMatch and SqliteStatment Clash
On 29/08/2007, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: > > select * from test where '982' like t || '%' order by t desc limit 1; > > This works but will not work for earlier data(9854002656).So seems to be bug. > > regards > ragha '9854002656%' is not a match for '982', so seems not to be a bug Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] BestMatch and SqliteStatment Clash
Hi,
Best match is "9854002656" among the 2 records.
Pls try this simple one.It will make it clear,
create table test(t text);
insert into test values ('9');
insert into test values('98');
insert into test values('983');
insert into test values('9854');
select * from test where '982' like t || '%' order by t desc limit 1;
above sql tries to model a DST(digit search tree).
Expected output: 98
This works but will not work for earlier data(9854002656).So seems to be bug.
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: John Machin <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 6:22 pm
Subject: Re: [sqlite] BestMatch and SqliteStatment Clash
> On 29/08/2007 10:37 PM, RaghavendraK 70574 wrote:
> > Thx. I have modifed it to ?, but
> > Sqlite fails to get records for the below query. When debug it
> retuns
> > SQLITE_DONE. Pls help.
> >
> > select * from 'tbl.7' where ? like column1 || '%' order by
> column1 desc limit 1;
> >
> > Data is as below:
> > Version: 3.4.0
> > Re-confirm the problem in sqlite and not in my code,
> > I tried using sqlite3 commandLine tool and found the same problem.
> >
> > create table 'tbl.7'(ver integer,
> > column1 text not NULL,
> > column2 text not NULL,
> > column3 text not NULL,
> > column4 text not NULL,
> > column5 text not NULL,
> > column6 text not NULL,
> > column7 text not NULL,
> > column8 text not NULL,
> > column9 text not NULL,
> > column10 text not NULL,
> > primary
> key(ver,column1,column2,column3,column4,column5));>
> >
> > insert into 'tbl.7'
> > values
> > (7,
> > '9845002655',
> > '9845002655',
> > '9845002655',
> > '9845002655',
> > '9845002655',
> >
> 'CO',>
>
> 'CO',
> >
> 'CO',>
>
> 'CO',
> >
> 'CO');>
>
> > insert into 'tbl.7'
> > values
> > (7,
> > '9854002656',
> > '9845002655',
> > '9845002655',
> > '9845002655',
> > '9845002655',
> >
> 'CO',>
>
> 'CO',
> >
> 'CO',>
>
> 'CO',
> >
> 'CO');>
>
> > --Best match for 985
> > select * from 'tbl.7' where '985' like column1 || '%' order by
> column1 desc limit 1;
>
> Can you leave out the incredibly annoying 'COL...' stuff? It's
> nothing to do with your problem!
>
> I don't see how *ANY* of your rows will match. Which rows do you
> expect
> to match? Why?
>
> If column1 was '98' (for example), then you would have
> '985' like '98' || '%'
> which is
> '985' like '98%'
> which is true.
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] BestMatch and SqliteStatment Clash
Show us your program.
RaghavendraK 70574 wrote:
Thx. I have modifed it to ?, but
Sqlite fails to get records for the below query. When debug it retuns
SQLITE_DONE. Pls help.
select * from 'tbl.7' where ? like column1 || '%' order by column1 desc limit 1;
Data is as below:
Version: 3.4.0
Re-confirm the problem in sqlite and not in my code,
I tried using sqlite3 commandLine tool and found the same problem.
create table 'tbl.7'(ver integer,
column1 text not NULL,
column2 text not NULL,
column3 text not NULL,
column4 text not NULL,
column5 text not NULL,
column6 text not NULL,
column7 text not NULL,
column8 text not NULL,
column9 text not NULL,
column10 text not NULL,
primary key(ver,column1,column2,column3,column4,column5));
insert into 'tbl.7'
values
(7,
'9845002655',
'9845002655',
'9845002655',
'9845002655',
'9845002655',
'CO',
'CO',
'CO',
'CO',
'CO');
insert into 'tbl.7'
values
(7,
'9854002656',
'9845002655',
'9845002655',
'9845002655',
'9845002655',
'CO',
'CO',
'CO',
'CO',
'CO');
--Best match for 985
select * from 'tbl.7' where '985' like column1 || '%' order by column1 desc
limit 1;
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: Wednesday, August 29, 2007 7:07 pm
Subject: Re: [sqlite] BestMatch and SqliteStatment Clash
On Wed, 2007-08-29 at 18:37 +0800, RaghavendraK 70574 wrote:
Hi,
Am using sqlite 3.4.0
stmt= sqlite_prepareV2("select * from test where '?' like t ||
'%' order by t desc);
You need to remove the ' quotes around the question mark.
At the moment the expression is a literal string value,
not an sql variable.
---
--
To unsubscribe, send email to [EMAIL PROTECTED]
---
--
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] BestMatch and SqliteStatment Clash
On 29/08/2007 10:37 PM, RaghavendraK 70574 wrote: Thx. I have modifed it to ?, but Sqlite fails to get records for the below query. When debug it retuns SQLITE_DONE. Pls help. select * from 'tbl.7' where ? like column1 || '%' order by column1 desc limit 1; Data is as below: Version: 3.4.0 Re-confirm the problem in sqlite and not in my code, I tried using sqlite3 commandLine tool and found the same problem. create table 'tbl.7'(ver integer, column1 text not NULL, column2 text not NULL, column3 text not NULL, column4 text not NULL, column5 text not NULL, column6 text not NULL, column7 text not NULL, column8 text not NULL, column9 text not NULL, column10 text not NULL, primary key(ver,column1,column2,column3,column4,column5)); insert into 'tbl.7' values (7, '9845002655', '9845002655', '9845002655', '9845002655', '9845002655', 'CO', 'CO', 'CO', 'CO', 'CO'); insert into 'tbl.7' values (7, '9854002656', '9845002655', '9845002655', '9845002655', '9845002655', 'CO', 'CO', 'CO', 'CO', 'CO'); --Best match for 985 select * from 'tbl.7' where '985' like column1 || '%' order by column1 desc limit 1; Can you leave out the incredibly annoying 'COL...' stuff? It's nothing to do with your problem! I don't see how *ANY* of your rows will match. Which rows do you expect to match? Why? If column1 was '98' (for example), then you would have '985' like '98' || '%' which is '985' like '98%' which is true. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] BestMatch and SqliteStatment Clash
Thx. I have modifed it to ?, but
Sqlite fails to get records for the below query. When debug it retuns
SQLITE_DONE. Pls help.
select * from 'tbl.7' where ? like column1 || '%' order by column1 desc limit 1;
Data is as below:
Version: 3.4.0
Re-confirm the problem in sqlite and not in my code,
I tried using sqlite3 commandLine tool and found the same problem.
create table 'tbl.7'(ver integer,
column1 text not NULL,
column2 text not NULL,
column3 text not NULL,
column4 text not NULL,
column5 text not NULL,
column6 text not NULL,
column7 text not NULL,
column8 text not NULL,
column9 text not NULL,
column10 text not NULL,
primary key(ver,column1,column2,column3,column4,column5));
insert into 'tbl.7'
values
(7,
'9845002655',
'9845002655',
'9845002655',
'9845002655',
'9845002655',
'CO',
'CO',
'CO',
'CO',
'CO');
insert into 'tbl.7'
values
(7,
'9854002656',
'9845002655',
'9845002655',
'9845002655',
'9845002655',
'CO',
'CO',
'CO',
'CO',
'CO');
--Best match for 985
select * from 'tbl.7' where '985' like column1 || '%' order by column1 desc
limit 1;
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: Wednesday, August 29, 2007 7:07 pm
Subject: Re: [sqlite] BestMatch and SqliteStatment Clash
> On Wed, 2007-08-29 at 18:37 +0800, RaghavendraK 70574 wrote:
> > Hi,
> >
> > Am using sqlite 3.4.0
> >
> > stmt= sqlite_prepareV2("select * from test where '?' like t ||
> '%' order by t desc);
>
> You need to remove the ' quotes around the question mark.
> At the moment the expression is a literal string value,
> not an sql variable.
>
>
>
>
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] BestMatch and SqliteStatment Clash
On 29/08/2007 8:37 PM, RaghavendraK 70574 wrote:
Hi,
Am using sqlite 3.4.0
stmt= sqlite_prepareV2("select * from test where '?' like t || '%' order by t desc);
? is the sql variable.
No it isn't; it's the contents of a string constant.
Try this:
select * from test where ? like t || '%' order by t desc
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] BestMatch and SqliteStatment Clash
On Wed, 2007-08-29 at 16:43 +0800, RaghavendraK 70574 wrote:
> Hi,
>
> create table test (t text);
>
> insert into test values ('9');
> insert into test values ('98');
> insert into test values ('986');
> insert into test values ('9867');
>
> select * from test where '98555' like t || '%' order by t desc limit 1;
There are no SQL variables to bind to in that statement. Syntax
for SQL variables is here:
http://www.sqlite.org/lang_expr.html
Dan.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] BestMatch and SqliteStatment Clash
Hi,
create table test (t text);
insert into test values ('9');
insert into test values ('98');
insert into test values ('986');
insert into test values ('9867');
select * from test where '98555' like t || '%' order by t desc limit 1;
When we try to compile the above sql as a statement,we get Success but
when we bind it gives a error "SQLITE_RANGE".
After inspection we find
"sParse.nVar" = 0 [which represent nr of "?"]
Can u pls help to correct this error.
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: Daniel Önnerby <[EMAIL PROTECTED]>
Date: Wednesday, August 29, 2007 4:20 pm
Subject: Re: [sqlite] Towards SQLite version 3.5.0
> Hi!
>
> The new multithread-features will be great.
> Do you think that it will be better to share one connection
> between all
> theads in an application or is better to have each thread open a
> new
> connection and use the sqlite3_enable_shared_cache?
>
> Best regards
> Daniel
>
> [EMAIL PROTECTED] wrote:
> > The transition from 3.4.2 to 3.5.0 will perhaps be the
> > largest single change to SQLite since 2.8->3.0. There
> > will not be that many visible changes, but a lot is
> > changing behind the scenes. Some less frequently used
> > interfaces will be changing in slightly incompatible
> > ways. Users who have build customized OS intereface layers
> > or backends for SQLite will find that they are going to
> > need to do some rework.
> >
> > SQLite version 3.5.0 is not close to being ready yet.
> > But it is to the point where the source code will
> > compile and pass many tests. And so I would like to
> > take this opportunity to encourage people in the
> > community to download the CVS HEAD and give it
> > a whirl in their applications. Please let me know
> > about any serious issues you run across.
> >
> > I have *started* to prepare documentation describing
> > the changes in 3.5.0. This is draft documentation.
> > But for those who are interested, please visit
> >
> >http://www.sqlite.org/34to35.html
> >http://www.sqlite.org/capi350ref.html
> >
> > In particular, if your application uses a customized
> > OS interface for SQLite, you should read the 34to35.html
> > document to see exactly what will be involved in porting
> > your application to run with version 3.5.0.
> >
> > The SQLite code currently in CVS HEAD is not ready for
> > production use. We know that. We know what many of the
> > problems are and Dan and I are working long hours to fix
> > them. It's the problems that we *do not* know about that
> > are scary. So that is why I am inviting the larger
> > community to have an early look and perhaps bring our
> > attention to issues sooner rather than later.
> >
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> >
> >
> > -
>
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
>
> >
> >
>
>
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
