Re: [sqlite] The LIKE operator and Swift

[J. King]

On September 26, 2019 9:26:23 a.m. EDT, Daniel Odom  wrote:
>I am just now getting around to learning Swift and XCode. I am having a
>
>problem with 'LIKE'. When I do this:
>
>let queryString = "select name, phone, street, city, state from phone 
>where name like '%?%'"

As others have said, '?' is a literal character, whereas ? is a parameter. 
Concatenating with || is one solution, though I would personally recommend 
instead preparing your whole pattern in your application logic and passing just 
the parameter to SQLite. Don't forget to escape any literal % and _ characters 
in your input before fencing with %, unless your input is itself a LIKE 
pattern. 

<https://sqlite.org/lang_expr.html#like>
-- 
J. King
_______
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The LIKE operator and Swift

[Gwendal Roué]

You can build your pattern in Swift, and use a single parameter:

let queryString = "select name, phone, street, city, state from phone
where name like '?'"
let pattern = "%\(name)%"
if sqlite3_bind_text(stmt, 1, pattern, -1, SQLITE_TRANSIENT) !=
SQLITE_OK {do whatever}

For a more detailed explanation of the reasons why you get an error with
this LIKE query see this dedicated FAQ:
https://github.com/groue/GRDB.swift/blob/master/README.md#sqlite-error-21-wrong-number-of-statement-arguments-with-like-queries

Gwendal Roué

On Thu, Sep 26, 2019 at 3:26 PM Daniel Odom  wrote:

> I am just now getting around to learning Swift and XCode. I am having a
> problem with 'LIKE'. When I do this:
>
> let queryString = "select name, phone, street, city, state from phone
> where name like '%?%'"
>
> And then this: if sqlite3_bind_text(stmt, 1, name, -1, SQLITE_TRANSIENT)
> != SQLITE_OK {do whatever}
>
> I get an error "column index out of range". The rest of the code is
> fine. When I do this:
>
> let queryString = "select name, phone, street, city, state from phone
> where name = ?"
>
> everything works just fine. What am I missing?
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] The LIKE operator and Swift

[Daniel Odom]

I am just now getting around to learning Swift and XCode. I am having a 
problem with 'LIKE'. When I do this:


let queryString = "select name, phone, street, city, state from phone 
where name like '%?%'"


And then this: if sqlite3_bind_text(stmt, 1, name, -1, SQLITE_TRANSIENT) 
!= SQLITE_OK {do whatever}


I get an error "column index out of range". The rest of the code is 
fine. When I do this:


let queryString = "select name, phone, street, city, state from phone 
where name = ?"


everything works just fine. What am I missing?

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

Re: [sqlite] database like file archive

[Rowan Worth]

On Tue, 27 Aug 2019 at 21:57, Peng Yu  wrote:

> I haven't found an archive format that allows in-place delete (I know
> that .zip, .7z and .tar don't). This means that whenever delete is
> needed, the original archive must be copied first. This can be
> problematic when the archive is large and the file to delete is small.
>
> Something along the line of the ability of sqlite3 to perform in-place
> delete might be a useful feature for archives. But I haven't found any
> such archive format. Does anybody know one? Thanks.
>

Note that you wouldn't actually reclaim any disk space by deleting a file
in-place from an archive backed by sqlite -- unless you issue a VACUUM,
which rewrites the whole DB. Without a VACUUM, the free pages within the DB
would only be taken advantage of by future additions to the archive.

That may be an acceptable compromise for the
delete-small-file-from-large-archive use case you're presenting, but the
fact that archive formats are generally designed to minimise storage
requirements goes a long way towards explaining why the scenario is not
well-catered for. It also raises the question of whether an archive is the
correct tool for the job!

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

Re: [sqlite] database like file archive

[Richard Hipp]

On 8/27/19, Jens Alfke  wrote:
> is [SQLite] engineered with the
> assumption that a database file may be malicious, or is the assumption
> "garbage in, garbage out"?

https://www.sqlite.org/security.html
https://www.sqlite.org/testing.html

Our intent is that SQLite database files are secure in the sense that
reading a maliciously corrupted database file is harmless.  I spend
most of my time testing for this sort of thing, and dreaming up new
defenses against yet undiscovered attacks.

Research out of the University of Buffalo shows that every Android
phone has about 200 SQLite database files and about 14% of those are
used for transfer purposes only.  In other words, content is
downloaded from the cloud as an SQLite database then becomes read-only
on the phone.  That's about 70 billion databases used as containers.
So lots of people are using SQLite as a container.  And those numbers
are for Android only.  Indications are the iOS is even bigger.

We work very hard to ensure that those billions and billions of data
containers in circulation are not a security risk.

That said, Jens remarks made me realize that the SQLite archive code
has not been updated recently to implement the best practices outlined
in the first document above.  The existing code is safe.  But I'll get
busy and add the extra layers of defense to make it even safer.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database like file archive

[Keith Medcalf]

On Tuesday, 27 August, 2019 14:40, Jens Alfke  wrote:

>> On Aug 27, 2019, at 12:21 PM, Keith Medcalf  wrote:

>> Everything that has been touched by a third-party is inherently
>> untrustworthy.  Thus it is and thus it has always been.

> Yes. I have a lot of experience with network coding and security, so
> I'm aware of this, thanks. My question was simply whether SQLite
> itself is considered safe when operating on an untrusted database
> file.

Considered by whom -- and what is the trustworthiness of that "whom" to be 
making that assessment?

Personally speaking I find the developers of SQLite to be trustworthy and 
generally trust that SQLite3 will operate in accordance with its design.  
Furthermore, I trust that any issues where SQLite does not operate in 
accordance with its design will be fixed by said developers.  

I also trust that when presented with a trustworthy database file the library 
will operate in a trustworthy fashion.  The necessary inference therefore is 
that if presented with an untrustworthy database file, then the whole shebang 
is untrustworthy.

This inference would stand no matter what trustworthy party may claim that 
SQLite3 is trustworthy in the face of untrustworthy input.  (And this applies 
to everything not just SQLite3).  To assume otherwise is fraught with peril and 
is the cause of much security problems in the world today.

>> Even ZIP files have a database schema that can be manipulated as
>> does everything else.

> The layout of a Zip file is vastly simpler than a SQLite database.
> A Zip codec does not include an interpreter for a sophisticated
> programming language.
> Zip files do not contain program code that runs when the file is
> read; SQLite databases can.

In fact, if one forgets about ZIP (presumably meaning something trustworthy 
like Info-ZIP) then there are in fact a great many things that are capable of 
executing arbitrary code contained within what is ostensibly data, often by 
design.  Can one trust that any given program when presented with untrustworthy 
input will not execute arbitrary code?  No.  One should expect that any given 
program when presented with untrustworthy input may operate in an untrustworthy 
manner, unless it is manifestly impossible for this to occur.  Since 
determination of "manifestly impossible" is huge in scope, it is only done in 
very rare instances.

Also, I do not think that SQLite, by design, executes any code that is stored 
within the database, assuming a trustworthy database file.  However, if you are 
asking if one can craft an (untrustworthy) database file that may execute code 
contained within the database when it is opened, then this again devolves into 
the fact that the database file is itself untrustworthy, and as such all such 
bets are off.

>> And how is this in anyway different from a zip process, or a rar
>> processess or an uncompress process or any or a number of possibly
>> trustworthy programs processing data coming from an untrustworthy
>> source?  (which includes things like Web Browsers, Video Players, and
>> on and on)

> Codecs used in such apps are considered attack surfaces and are
> screened for vulnerabilities. (For example, Google found and fixed
> some security holes in the TrueType font renderer when they added
> web-font support to Chrome.) This is precisely what I'm asking about
> SQLite — is it engineered with the assumption that a database file
> may be malicious, or is the assumption "garbage in, garbage out"?

And this is a known security problem (untrustworthy font files) that has 
existed for as long as I can recall (at least since GOOEYs were invented).  The 
solution is, of course, to not permit the processing of files (including font 
files) that originate from untrustworthy sources.  Just because one such hole 
has been found and fixed does not mean that others do not exist.  In fact, it 
means that it is more likely that such holes exist but merely have not been 
published in the local tabloid yet.  Nonetheless, it is again a matter of 
assigning trust and trustworthiness and the boundaries that apply to this trust.

So, directly to your question "SQLite — is it engineered with the assumption 
that a database file may be malicious, or is the assumption "garbage in, 
garbage out"?  The answer of course is a matter of trust.  Yes, SQLite is 
engineered to operate in a trustworthy fashion even when presented with 
untrustworthy input and in fact the developers endeavour to fix any such issues 
found/reported.  This however does not preclude the possible existence of 
situations where this is not the case or have not otherwise yet been reported 
and fixed.

So again, we come back to the fact that no matter how trustworthy something is 
when presented with trusted inputs, its behaviour when presented with 
untrustworthy input cannot be guaranteed.  That is to say that for all 
pipelines A -> B -> C where either of A or B is untrustworthy, then C must be 
untrustworthy 

Re: [sqlite] database like file archive

[Jens Alfke]

> On Aug 27, 2019, at 12:21 PM, Keith Medcalf  wrote:
> 
> Everything that has been touched by a third-party is inherently 
> untrustworthy.  Thus it is and thus it has always been.  

Yes. I have a lot of experience with network coding and security, so I'm aware 
of this, thanks. My question was simply whether SQLite itself is considered 
safe when operating on an untrusted database file.

> Even ZIP files have a database schema that can be manipulated as does 
> everything else.

The layout of a Zip file is vastly simpler than a SQLite database.
A Zip codec does not include an interpreter for a sophisticated programming 
language.
Zip files do not contain program code that runs when the file is read; SQLite 
databases can.

> And how is this in anyway different from a zip process, or a rar processess 
> or an uncompress process or any or a number of possibly trustworthy programs 
> processing data coming from an untrustworthy source?  (which includes things 
> like Web Browsers, Video Players, and on and on)

Codecs used in such apps are considered attack surfaces and are screened for 
vulnerabilities. (For example, Google found and fixed some security holes in 
the TrueType font renderer when they added web-font support to Chrome.) This is 
precisely what I'm asking about SQLite — is it engineered with the assumption 
that a database file may be malicious, or is the assumption "garbage in, 
garbage out"?

> Chrome is a Google product.  Google's only revenue source is selling 
> information that they have obtained from third-
[anti-Google ranting removed]

This is not only off-topic and inaccurate (Google has many other revenue 
sources), it's the sort of scenery-chewing conspiracy theorizing that's beneath 
someone with your level of expertise. Check yo'self.

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

Re: [sqlite] database like file archive

[Keith Medcalf]

On Tuesday, 27 August, 2019 12:47, Jens Alfke  wrote:

>Archive files often get transferred between people. Using this format
>for that purpose would involve opening and reading untrusted SQLite
>database files. Is that safe? Could maliciously corrupting the schema
>or other metadata of a database cause security problems for the
>client accessing the database?

Everything that has been touched by a third-party is inherently untrustworthy.  
Thus it is and thus it has always been.  Even ZIP files have a database schema 
that can be manipulated as does everything else.  There is no difference other 
than the misplaced assumption of trust.

>(I'm thinking not just of a separate `sqlite3` process accessing the
>archive, but also of the archiving code running inside some other
>process — consider a web browser or file manager extracting a sqlar
>archive.)

And how is this in anyway different from a zip process, or a rar processess or 
an uncompress process or any or a number of possibly trustworthy programs 
processing data coming from an untrustworthy source?  (which includes things 
like Web Browsers, Video Players, and on and on)

>There were some security issues that came up recently involving the
>Chrome browser allowing untrusted JS code to run SQLite queries on
>local database files. But the scenario I'm thinking of is kind of the
>reverse — the queries are trusted but the database itself isn't.

Chrome is a Google product.  Google's only revenue source is selling 
information that they have obtained from third-parties by clandestine means.  
As such, nothing which bears a Google (or Alphabet) name can be considered in 
any way trustworthy.  One must assign trust having an eye to this fact and 
evaluate all statements made in light of this truth.  So Google Chrome 
permitting untrusted JS to run SQLite queries on local database files should be 
expected and is not a security problem.  How else would Google make money?

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] database like file archive

[Simon Slavin]

On 27 Aug 2019, at 7:47pm, Jens Alfke  wrote:

> Archive files often get transferred between people. Using this format for 
> that purpose would involve opening and reading untrusted SQLite database 
> files. Is that safe? Could maliciously corrupting the schema or other 
> metadata of a database cause security problems for the client accessing the 
> database?

You're thinking of an exploit like a ZIP bomb.  This is a small, 
maliciously-constructed ZIP file which expands into a huge amount of contents.  
A well-known example is a 42 kilobyte zip file which unzips into 4.5 petabytes 
of contents.

Other problems include overwriting in-archive filenames with illegal characters 
like a colon and a slash, then relying on oversights in OS routines to do nasty 
things to your file structure.

I'm going to let the devs handle this one.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database like file archive

[Jens Alfke]

> On Aug 27, 2019, at 7:06 AM, Philip Bennefall  wrote:
> 
> There is the sqlar archive format, which you can test using the official 
> sqlite3 command line shell. There is also a library for it as part of the 
> Sqlite3 repository.

"An SQLite Archive is an ordinary SQLite database file that contains the 
following table as part of its schema…"

Archive files often get transferred between people. Using this format for that 
purpose would involve opening and reading untrusted SQLite database files. Is 
that safe? Could maliciously corrupting the schema or other metadata of a 
database cause security problems for the client accessing the database?

(I'm thinking not just of a separate `sqlite3` process accessing the archive, 
but also of the archiving code running inside some other process — consider a 
web browser or file manager extracting a sqlar archive.)

There were some security issues that came up recently involving the Chrome 
browser allowing untrusted JS code to run SQLite queries on local database 
files. But the scenario I'm thinking of is kind of the reverse — the queries 
are trusted but the database itself isn't.

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

Re: [sqlite] database like file archive

[Peng Yu]

> The standard "sqlite3" command-line tool will read and write SQLite
> archive files.  See the documentation at
> https://www.sqlite.org/sqlar.html#managing_an_sqlite_archive_from_the_command_line

OK. So there is basically no need to install the sqlar command since
all features from the sqlar command is accessible via the sqlite3
command? Thanks.

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

Re: [sqlite] database like file archive

[Philip Bennefall]

The earliest version of the shell which ships with the archive support 
is 3.22.0, according to the page I linked to. If you have an earlier 
version you could simply grab the Mac OSX precompiled binaries from the 
download page on sqlite.org and you'll be good to go.



Kind regards,


Philip Bennefall


On 8/27/2019 4:18 PM, Peng Yu wrote:

There is the sqlar archive format, which you can test using the official
sqlite3 command line shell. There is also a library for it as part of
the Sqlite3 repository.

https://www.sqlite.org/sqlar.html
https://sqlite.org/sqlar/doc/trunk/README.md

This is good to know.

How to install it? In homebrew's sqlite package, I don't find sqlar. I
use Mac OS X.



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

Re: [sqlite] database like file archive

[Richard Hipp]

On 8/27/19, Peng Yu  wrote:
>
> How to install it? In homebrew's sqlite package, I don't find sqlar. I
> use Mac OS X.
>

The standard "sqlite3" command-line tool will read and write SQLite
archive files.  See the documentation at
https://www.sqlite.org/sqlar.html#managing_an_sqlite_archive_from_the_command_line
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database like file archive

[Peng Yu]

> There is the sqlar archive format, which you can test using the official
> sqlite3 command line shell. There is also a library for it as part of
> the Sqlite3 repository.
>
> https://www.sqlite.org/sqlar.html

> https://sqlite.org/sqlar/doc/trunk/README.md

This is good to know.

How to install it? In homebrew's sqlite package, I don't find sqlar. I
use Mac OS X.

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

Re: [sqlite] database like file archive

[Dominique Devienne]

https://sqlite.org/sqlar/doc/trunk/README.md

On Tue, Aug 27, 2019 at 3:57 PM Peng Yu  wrote:

> Hi,
>
> I haven't found an archive format that allows in-place delete (I know
> that .zip, .7z and .tar don't). This means that whenever delete is
> needed, the original archive must be copied first. This can be
> problematic when the archive is large and the file to delete is small.
>
> Something along the line of the ability of sqlite3 to perform in-place
> delete might be a useful feature for archives. But I haven't found any
> such archive format. Does anybody know one? Thanks.
>
> --
> Regards,
> Peng
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database like file archive

[Philip Bennefall]

There is the sqlar archive format, which you can test using the official 
sqlite3 command line shell. There is also a library for it as part of 
the Sqlite3 repository.



https://www.sqlite.org/sqlar.html

Kind regards,

Philip Bennefall
On 8/27/2019 3:56 PM, Peng Yu wrote:

Hi,

I haven't found an archive format that allows in-place delete (I know
that .zip, .7z and .tar don't). This means that whenever delete is
needed, the original archive must be copied first. This can be
problematic when the archive is large and the file to delete is small.

Something along the line of the ability of sqlite3 to perform in-place
delete might be a useful feature for archives. But I haven't found any
such archive format. Does anybody know one? Thanks.



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

[sqlite] database like file archive

[Peng Yu]

Hi,

I haven't found an archive format that allows in-place delete (I know
that .zip, .7z and .tar don't). This means that whenever delete is
needed, the original archive must be copied first. This can be
problematic when the archive is large and the file to delete is small.

Something along the line of the ability of sqlite3 to perform in-place
delete might be a useful feature for archives. But I haven't found any
such archive format. Does anybody know one? Thanks.

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

Re: [sqlite] UTF8 LIKE stranges

[Clemens Ladisch]

Vlczech - Tomáš Volf wrote:
> CREATE TABLE people (
>   firstname  TEXT,
>   surname TEXT
> );
> INSERT INTO people('Tomáš', 'Surname');
>
> "SELECT * FROM people WHERE firstname LIKE ?"
> For binding I use: sqlite3_bind_text(stmt, 1, name.c_str(), -1, 
> SQLITE_STATIC);

SQLITE_STATIC works only if the name variable is not modified or destroyed
before the query is finalized.  Otherwise, use SQLITE_TRANSIENT.

> The problem is, that row with name "Tomáš" is selected also by following 
> strings (with CZ national chars) in variable name used in sqlite3_bind_text:
> * "Tomě", but no longer with "Toměš"

I cannot reproduce this.

Are you sure that you have used the UTF-8 encoding correctly?


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

[sqlite] UTF8 LIKE stranges

[Vlczech - Tomáš Volf]

Hello,
I have some strange behaviout in LIKE query in SQLite. Letš see some very 
simplified example:
 
Let's have a table
CREATE TABLE people (
  firstname  TEXT,
  surname TEXT
);
and in it following data:
INSERT INTO people('Tomáš', 'Surname');
created by sqlite3_exec() function.
 
 
Then I use sqlite3_prepare_v2() function with for example this SQL query string: 
"SELECT * FROM people WHERE firstname LIKE ?".
For binding I use: sqlite3_bind_text(stmt, 1, name.c_str(), -1, SQLITE_STATIC); where name is some 
string for bellow, with "%" char at the end (for example name = "Tom%").
 
Then I use sqlite3_exec() function with callback, where sql string is 
sqlite3_expanded_sql(stmt) from previous rows.
 
The problem is, that row with name "Tomáš" is selected also by following 
strings (with CZ national chars) in variable name used in sqlite3_bind_text:
"Tomě", but no longer with "Toměš""Tomč", but no longer with "Tomčš""Tomř" as well as "Tomřš""Tomý" as well as "Tomýš""Tomí", but no longer with "Tomíš""Tomé", but no longer with 
"Toméš""Tomů" as well as "Tomůš""Tomú", but no longer with "Tomúš""Tomď", but no longer with "Tomďš""Tomň", but no longer with "Tomňš""Tomáž""Tomáť " (without space - it is written purposely cause hook above t 
char is not so visible without the space)+ all of combination where "Tom*š" is successfull and "Tomá*" is successfull:
á = ř: Tomřž, Tomřťá = ý: Tomýž, Tomýťá = ů: Tomůž, Tomůťá = ï (see bellow): Tomïž, 
TomïťFollowing CZ national chars do not lead to select row with name "Tomáš", 
which is correct behavior:
"Tomš""Tomž""Tomť " (without space - it is written purposely cause hook above t char is 
not so visible without the space)For example the problem with other national chars from german language which also 
select row with name "Tomáš":
"Tomä", but no longer with "Tomäš""Tomë", but no longer with "Tomëš""Tomö", but no longer with 
"Tomöš""Tomü" as well as "Tomüš"Followingnational chars do not lead to select row name "Tomáš", which is correct behavior:
"Tomï" 
Or it is only some misuse of sqlite3_*() functions?
 
Thank you, with best regards Tomáš Volf.

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

Re: [sqlite] UTF8 LIKE stranges

[Vlczech - Tomáš Volf]

Sorry for "spam", I hope that previous HTML form of mail (with bullet lists) 
will be readable. There is, for sure and better readability for non-HTML clients, plain 
text version of previous mail:
 
Hello,
I have some strange behaviout in LIKE query in SQLite. Letš see some very 
simplified example:
 
Let's have a table
CREATE TABLE people (
  firstname  TEXT,
  surname TEXT
);
and in it following data:
INSERT INTO people('Tomáš', 'Surname');
created by sqlite3_exec() function.
 
 
Then I use sqlite3_prepare_v2() function with for example this SQL query string: 
"SELECT * FROM people WHERE firstname LIKE ?".
For binding I use: sqlite3_bind_text(stmt, 1, name.c_str(), -1, SQLITE_STATIC); where name is some 
string for bellow, with "%" char at the end (for example name = "Tom%").
 
Then I use sqlite3_exec() function with callback, where sql string is 
sqlite3_expanded_sql(stmt) from previous rows.
 
The problem is, that row with name "Tomáš" is selected also by following 
strings (with CZ national chars) in variable name used in sqlite3_bind_text:
* "Tomě", but no longer with "Toměš"
* "Tomč", but no longer with "Tomčš"
* "Tomř" as well as "Tomřš"
* "Tomý" as well as "Tomýš"
* "Tomí", but no longer with "Tomíš"
* "Tomé", but no longer with "Toméš"
* "Tomů" as well as "Tomůš"
* "Tomú", but no longer with "Tomúš"
* "Tomď", but no longer with "Tomďš"
* "Tomň", but no longer with "Tomňš"
* "Tomáž"
* "Tomáť " (without space - it is written purposely cause hook above t char is 
not so visible without the space)
 
+ all of combination where "Tom*š" is successfull and "Tomá*" is successfull:
* á = ř: Tomřž, Tomřť
* á = ý: Tomýž, Tomýť
* á = ů: Tomůž, Tomůť
* á = ï (see bellow): Tomïž, Tomïť 
Following CZ national chars do not lead to select row with name "Tomáš", which 
is correct behavior:
* "Tomš"
* "Tomž"
* "Tomť " (without space - it is written purposely cause hook above t char is 
not so visible without the space) 
 
For example the problem with other national chars from german language which also select 
row with name "Tomáš":
* "Tomä", but no longer with "Tomäš"
* "Tomë", but no longer with "Tomëš"
* "Tomö", but no longer with "Tomöš"
* "Tomü" as well as "Tomüš" 
Following national chars do not lead to select row name "Tomáš", which is 
correct behavior:
* "Tomï"
 
 
Or it is only some misuse of sqlite3_*() functions?
 
Thank you, with best regards Tomáš Volf.
 
 
__

Od: Vlczech - Tomáš Volf <vlcek...@centrum.cz>
Komu: <sqlite-users@mailinglists.sqlite.org>
Datum: 23.05.2017 21:28
Předmět: UTF8 LIKE stranges


Hello,
I have some strange behaviout in LIKE query in SQLite. Letš see some very 
simplified example:
 
Let's have a table
CREATE TABLE people (
  firstname  TEXT,
  surname TEXT
);
and in it following data:
INSERT INTO people('Tomáš', 'Surname');
created by sqlite3_exec() function.
 
 
Then I use sqlite3_prepare_v2() function with for example this SQL query string: 
"SELECT * FROM people WHERE firstname LIKE ?".
For binding I use: sqlite3_bind_text(stmt, 1, name.c_str(), -1, SQLITE_STATIC); where name is some 
string for bellow, with "%" char at the end (for example name = "Tom%").
 
Then I use sqlite3_exec() function with callback, where sql string is 
sqlite3_expanded_sql(stmt) from previous rows.
 
The problem is, that row with name "Tomáš" is selected also by following 
strings (with CZ national chars) in variable name used in sqlite3_bind_text:
"Tomě", but no longer with "Toměš""Tomč", but no longer with "Tomčš""Tomř" as well as "Tomřš""Tomý" as well as "Tomýš""Tomí", but no longer with "Tomíš""Tomé", but no longer with 
"Toméš""Tomů" as well as "Tomůš""Tomú", but no longer with "Tomúš""Tomď", but no longer with "Tomďš""Tomň", but no longer with "Tomňš""Tomáž""Tomáť " (without space - it is written purposely cause hook above t 
char is not so visible without the space)+ all of combination where "Tom*š" is successfull and "Tomá*" is successfull:
á = ř: Tomřž, Tomřťá = ý: Tomýž, Tomýťá = ů: Tomůž, Tomůťá = ï (see bellow): Tomïž, 
TomïťFollowing CZ national chars do not lead to select row with name "Tomáš", 
which is correct behavior:
"Tomš""Tomž""Tomť " (without space - it is written purposely cause hook above t char is 
not so visible without the space)For example the problem with other national chars from german language which also 
select row with name "Tomáš":
"Tomä", but no longer with "Tomäš""Tomë", but no longer with "Tomëš""Tomö", but no longer with 
"Tomöš""Tomü" as well as "Tomüš"Followingnational chars do not lead to select row name "Tomáš", which is correct behavior:
"Tomï" 
Or it is only some misuse of sqlite3_*() functions?
 
Thank you, with best regards Tomáš Volf.

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

[sqlite] The LIKE optimization breaks user defined like functions

[Clemens Ladisch]

Domingo Alvarez Duarte wrote:
> Today I decided to see why my applications stop using index when using
> my user defined "like" function

The optimization replaces the LIKE with two comparisons.  Installing
a user-defined function implies that your new LIKE works differently,
so this optimization might no longer work.

> I was looking at the documentation and didn't saw any warning/mention
> to this change affecting user defined "like" functions

 says:
| 4. The built-in functions used to implement LIKE and GLOB must not have
|been overloaded


Regards,
Clemens

[sqlite] The LIKE optimization breaks user defined like functions

[Domingo Alvarez Duarte]

Hello !

Today I decided to see why my applications stop using index when using 
my user defined "like" function and it seems that during the creation of 
the "LIKE" optimization sqlite3 made some special settings to the 
builtin "like" function but didn't exposed it to third party developers.

I was looking at the documentation and didn't saw any warning/mention to 
this change affecting user defined "like" functions and before it was 
perfectly "legal" to overwrite the "like" function.

So I'm asking to make the extra settings public/documented to allow 
again it be user defined.

Cheers !

Mainly flags set by a private only function:

SQLITE_FUNC_LIKE

SQLITE_FUNC_CASE

/*
** Set the LIKEOPT flag on the 2-argument function with the given name.
*/
static void setLikeOptFlag(sqlite3 *db, const char *zName, u8 flagVal){
   FuncDef *pDef;
   pDef = sqlite3FindFunction(db, zName, 2, SQLITE_UTF8, 0);
   if( ALWAYS(pDef) ){
 pDef->funcFlags |= flagVal;
   }
}

And also it blindly overwrites any user defined function by it's own 
builtin with this pragma:

pragma case_sensitive_like;

   /* Reinstall the LIKE and GLOB functions.  The variant of LIKE
   ** used will be case sensitive or not depending on the RHS.
   */
   case PragTyp_CASE_SENSITIVE_LIKE: {
 if( zRight ){
   sqlite3RegisterLikeFunctions(db, sqlite3GetBoolean(zRight, 0));
 }else{
   FuncDef *pDef = sqlite3FindFunction(db, "like", 2, SQLITE_UTF8, 0);
   returnSingleInt(v, "case_sensitive_like", pDef && 
(pDef->funcFlags & SQLITE_FUNC_CASE));
 }
   }
   break;

/*
** Register the built-in LIKE and GLOB functions.  The caseSensitive
** parameter determines whether or not the LIKE operator is case
** sensitive.  GLOB is always case sensitive.
*/
void sqlite3RegisterLikeFunctions(sqlite3 *db, int caseSensitive){
   struct compareInfo *pInfo;
   if( caseSensitive ){
 pInfo = (struct compareInfo*)
   }else{
 pInfo = (struct compareInfo*)
   }
   sqlite3CreateFunc(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0);
   sqlite3CreateFunc(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0);
   sqlite3CreateFunc(db, "glob", 2, SQLITE_UTF8,
   (struct compareInfo*), likeFunc, 0, 0, 0);
   setLikeOptFlag(db, "glob", SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE);
   setLikeOptFlag(db, "like",
   caseSensitive ? (SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE) : 
SQLITE_FUNC_LIKE);
}

/*
** pExpr points to an expression which implements a function.  If
** it is appropriate to apply the LIKE optimization to that function
** then set aWc[0] through aWc[2] to the wildcard characters and
** return TRUE.  If the function is not a LIKE-style function then
** return FALSE.
**
** *pIsNocase is set to true if uppercase and lowercase are equivalent for
** the function (default for LIKE).  If the function makes the distinction
** between uppercase and lowercase (as does GLOB) then *pIsNocase is set to
** false.
*/
int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char 
*aWc){
   FuncDef *pDef;
   if( pExpr->op!=TK_FUNCTION
|| !pExpr->x.pList
|| pExpr->x.pList->nExpr!=2
   ){
 return 0;
   }
   assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
   pDef = sqlite3FindFunction(db, pExpr->u.zToken, 2, SQLITE_UTF8, 0);
   if( NEVER(pDef==0) || (pDef->funcFlags & SQLITE_FUNC_LIKE)==0 ){
 return 0;
   }

   /* The memcpy() statement assumes that the wildcard characters are
   ** the first three statements in the compareInfo structure.  The
   ** asserts() that follow verify that assumption
   */
   memcpy(aWc, pDef->pUserData, 3);
   assert( (char*) == (char*) );
   assert( &((char*))[1] == (char*) );
   assert( &((char*))[2] == (char*) );
   *pIsNocase = (pDef->funcFlags & SQLITE_FUNC_CASE)==0;
   return 1;
}

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[R.Smith]

On 2015-03-06 03:19 PM, Richard Hipp wrote:
> Yikes! Actually (1) cannot be determined for normal (non-virtual) 
> tables either because the value could be a BLOB even if the column 
> affinity is TEXT. And so the current LIKE optimization is not valid 
> *ever*. See ticket https://www.sqlite.org/src/tktview/05f43be8fdda9f I 
> don't yet know how we will fix this... 

True, but who uses LIKE on a BLOB?  Any such usage has to be by accident 
- though granted, this can happen. Maybe a simple documentation note 
stating that LIKE operator on BLOB values will have undefined results?

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Hick Gunter]

Using LIKE on a BLOB is not the problem.

It is the LIKE optimization that is broken, because it requires a BLOB to sort 
AFTER a text, which is never the case, while the LIKE function compares an 
expression that may contain wildcards to the raw data, which may be the case.

-Urspr?ngliche Nachricht-
Von: R.Smith [mailto:rsmith at rsweb.co.za]
Gesendet: Freitag, 06. M?rz 2015 14:25
An: sqlite-users at mailinglists.sqlite.org
Betreff: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite 
with 'like'


On 2015-03-06 03:19 PM, Richard Hipp wrote:
> Yikes! Actually (1) cannot be determined for normal (non-virtual)
> tables either because the value could be a BLOB even if the column
> affinity is TEXT. And so the current LIKE optimization is not valid
> *ever*. See ticket https://www.sqlite.org/src/tktview/05f43be8fdda9f I
> don't yet know how we will fix this...

True, but who uses LIKE on a BLOB?  Any such usage has to be by accident
- though granted, this can happen. Maybe a simple documentation note stating 
that LIKE operator on BLOB values will have undefined results?

___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Dominique Devienne]

On Fri, Mar 6, 2015 at 1:21 PM, Hick Gunter  wrote:

> And then there remain to be considered the effects of the pragma
> CASE_SENSITIVE_LIKE
>

Good point.

But that's no different from the case when an application overrides the
LIKE behavior via a custom function, and the vtable can similarly decide to
ignore it or not;
Or can even decide changing like the semantic of LIKE, again just like an
application overrides.

Which is way I think the point you raise is tangential to SQLite not giving
vtables the opportunity to optimize LIKE. --DD

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Hick Gunter]

And then there remain to be considered the effects of the pragma 
CASE_SENSITIVE_LIKE

-Urspr?ngliche Nachricht-
Von: Dominique Devienne [mailto:ddevienne at gmail.com]
Gesendet: Freitag, 06. M?rz 2015 10:30
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite 
with 'like'

On Fri, Mar 6, 2015 at 10:12 AM, R.Smith  wrote:

> On 2015-03-06 09:42 AM, Dominique Devienne wrote:
>
>> On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp  wrote:
>>
>>
>>> The LIKE operator can be overridden by the application to mean
>>> anything the application wants - it is not compelled to follow
>>> standard SQL semantics.  For that reason, virtual tables are unable
>>> to optimize using LIKE since they have no way of knowing what it will do.
>>>
>>> Works as designed.
>>>
>>>  Sure. But SQLite knows whether LIKE is overriden by the application
>>> or
>> not,
>> so shouldn't it only hide LIKE from the virtual table when it detects
>> it, instead of wholesale preventing the vtable from optimizing the
>> "normal semantic" LIKE case? My $0.02. --DD
>>
>
> I'm sure this is possible, but how would it be feasible?  If this was
> implemented, how would you know as a programmer designing a virtual
> table (or many other interface things) whether or not  the target you
> operate on will/will not be able to run some bit of code?  Even if you
> could query the API to find out whether it is possible (i.e. the
> normal LIKE is used), you still need to use that result as a specifier
> to decide which code block to implement. And if you are going to have
> to do two code blocks... might as well do the one where LIKE isn't supported 
> right from the start.
>
> Now if there was a setting where you could /force/ the standard LIKE
> to be used along with supporting the v-table interface, that might be
> something, but that might break a whole other universe of
> possibilities for v-table users and end-user customization.


That's not what I was saying. DRH is saying the vtable *never* sees the LIKE 
and SQLite itself a-posteriori filters out the rows based on LIKE. The vtable 
is not aware it's been denied seeing the LIKE. I'm saying that (in the vast 
majority of) cases when LIKE is not overriden by the application (globally or 
for that vtable), something SQLite can know, SQLite could pass the LIKE 
constraint to the xBestIndex, and let the vtable decide whether it can handle 
(i.e. optimize) LIKE or not, and whether SQLite should double-check it or not 
(as usual for vtable indexing). When it is overriden, it behaves as now. But 
that way the vtable has at least the opportunity to optimize LIKE, unlike now.

Whether that's a priority, obviously not given what we've read so far, but 
whether that's technically possible, from 30,000 ft I don't see why not given 
the above. My $0.02. --DD ___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[R.Smith]

On 2015-03-06 09:42 AM, Dominique Devienne wrote:
> On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp  wrote:
>
>>
>> The LIKE operator can be overridden by the application to mean
>> anything the application wants - it is not compelled to follow
>> standard SQL semantics.  For that reason, virtual tables are unable to
>> optimize using LIKE since they have no way of knowing what it will do.
>>
>> Works as designed.
>>
> Sure. But SQLite knows whether LIKE is overriden by the application or not,
> so shouldn't it only hide LIKE from the virtual table when it detects it,
> instead of wholesale preventing the vtable from optimizing the "normal
> semantic" LIKE case? My $0.02. --DD

I'm sure this is possible, but how would it be feasible?  If this was 
implemented, how would you know as a programmer designing a virtual 
table (or many other interface things) whether or not  the target you 
operate on will/will not be able to run some bit of code?  Even if you 
could query the API to find out whether it is possible (i.e. the normal 
LIKE is used), you still need to use that result as a specifier to 
decide which code block to implement. And if you are going to have to do 
two code blocks... might as well do the one where LIKE isn't supported 
right from the start.

Now if there was a setting where you could /force/ the standard LIKE to 
be used along with supporting the v-table interface, that might be 
something, but that might break a whole other universe of possibilities 
for v-table users and end-user customization.

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Dominique Devienne]

On Fri, Mar 6, 2015 at 10:12 AM, R.Smith  wrote:

> On 2015-03-06 09:42 AM, Dominique Devienne wrote:
>
>> On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp  wrote:
>>
>>
>>> The LIKE operator can be overridden by the application to mean
>>> anything the application wants - it is not compelled to follow
>>> standard SQL semantics.  For that reason, virtual tables are unable to
>>> optimize using LIKE since they have no way of knowing what it will do.
>>>
>>> Works as designed.
>>>
>>>  Sure. But SQLite knows whether LIKE is overriden by the application or
>> not,
>> so shouldn't it only hide LIKE from the virtual table when it detects it,
>> instead of wholesale preventing the vtable from optimizing the "normal
>> semantic" LIKE case? My $0.02. --DD
>>
>
> I'm sure this is possible, but how would it be feasible?  If this was
> implemented, how would you know as a programmer designing a virtual table
> (or many other interface things) whether or not  the target you operate on
> will/will not be able to run some bit of code?  Even if you could query the
> API to find out whether it is possible (i.e. the normal LIKE is used), you
> still need to use that result as a specifier to decide which code block to
> implement. And if you are going to have to do two code blocks... might as
> well do the one where LIKE isn't supported right from the start.
>
> Now if there was a setting where you could /force/ the standard LIKE to be
> used along with supporting the v-table interface, that might be something,
> but that might break a whole other universe of possibilities for v-table
> users and end-user customization.


That's not what I was saying. DRH is saying the vtable *never* sees the
LIKE and SQLite itself a-posteriori filters out the rows based on LIKE. The
vtable is not aware it's been denied seeing the LIKE. I'm saying that (in
the vast majority of) cases when LIKE is not overriden by the application
(globally or for that vtable), something SQLite can know, SQLite could pass
the LIKE constraint to the xBestIndex, and let the vtable decide whether it
can handle (i.e. optimize) LIKE or not, and whether SQLite should
double-check it or not (as usual for vtable indexing). When it is
overriden, it behaves as now. But that way the vtable has at least the
opportunity to optimize LIKE, unlike now.

Whether that's a priority, obviously not given what we've read so far, but
whether that's technically possible, from 30,000 ft I don't see why not
given the above. My $0.02. --DD

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Dominique Devienne]

On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp  wrote:

> On 3/5/15, Mike Nicolino  wrote:
> > I'm using System.Data.SQLite version 1.0.95.0 and have what appears to
> be a
> > bug with Virtual Tables.  Queries using 'like' in the where clause are
> not
> > getting the like clause passed to BestIndex as a query constraint.
> > Specifically:
> >
> >
> > -  Simple query: select * from foo where name like 'a%'
>
> The LIKE operator can be overridden by the application to mean
> anything the application wants - it is not compelled to follow
> standard SQL semantics.  For that reason, virtual tables are unable to
> optimize using LIKE since they have no way of knowing what it will do.
>
> Works as designed.
>

Sure. But SQLite knows whether LIKE is overriden by the application or not,
so shouldn't it only hide LIKE from the virtual table when it detects it,
instead of wholesale preventing the vtable from optimizing the "normal
semantic" LIKE case? My $0.02. --DD

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Richard Hipp]

On 3/6/15, Dominique Devienne  wrote:
>
> Sure. But SQLite knows whether LIKE is overriden by the application or not,
> so shouldn't it only hide LIKE from the virtual table when it detects it,
> instead of wholesale preventing the vtable from optimizing the "normal
> semantic" LIKE case?

It's more complicated than that.

The optimization in question is to convert WHERE clause terms of the form:

xyz LIKE 'abc%'

Into:

xyz>='abc' AND xyz<'abd'

But this optimization is only valid if (1) xyz is a text value, not a
numeric value and (2) xyz has the "nocase" collating sequence.  We
typically do not know either for a virtual table.  You might know (2)
if the expression has an explicit COLLATE clause:

xyz LIKE 'abc%' COLLATE nocase

But SQLite does not have a mechanism whereby a virtual table can tell
the query planner that the value of a column will never be numeric.

Yikes!  Actually (1) cannot be determined for normal (non-virtual)
tables either because the value could be a BLOB even if the column
affinity is TEXT.  And so the current LIKE optimization is not valid
*ever*.  See ticket https://www.sqlite.org/src/tktview/05f43be8fdda9f

I don't yet know how we will fix this...
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Mike Nicolino]

Hmm, yes this will work.  Though re-educating users writing their own queries 
to avoid like in this case will be an ongoing challenge. :)  But I do 
understand that generic 'like' support for Virtual Tables given the ability to 
override would be very challenging to implement generically.

MikeN


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jay Kreibich
Sent: Thursday, March 05, 2015 10:55 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite 
with 'like'


On Mar 5, 2015, at 12:30 PM, Mike Nicolino  
wrote:

> I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a 
> bug with Virtual Tables.  Queries using 'like' in the where clause are not 
> getting the like clause passed to BestIndex as a query constraint.  
> Specifically:
> 


"LIKE" is not a supported virtual table index constraint.  See: 
http://www.sqlite.org/c3ref/c_index_constraint_eq.html   As such, it cannot be 
passed to a virtual table implementation, and the constraint is handled by the 
SQLite engine above the virtual table implementation.

Given that the LIKE expression is translated into an SQL function, which the 
application can override, it would be difficult for a virtual table to 
correctly implement a LIKE operation internally, while matching the exact 
functionality of the current LIKE function.

Consider a statement like this:

SELECT * FROM myVirtualTable AS vt WHERE returnThisRow_CustomFunction( 
vt.col1 );

If returnThisRow_CustomFunction() is a function returns a true or false based 
on... well, who knows what... there is no way for a virtual table 
implementation to understand the inter-workings of that function and pre-filter 
the rows.  LIKE is no different.



It should be noted that MATCH is a supported virtual table index constraint 
supported.  Along with the virtual table xFindFunction() function allows a 
virtual table to implement a table specific filter function.  This is how the 
FTS extensions implement searches.

Consider providing a virtual table specific MATCH function, over-ride use on 
your table with xFindFunction(), and rewrite statements using MATCH rather than 
LIKE.

See the FTS modules as examples.  You might want to start here: 
https://www.sqlite.org/fts3.html


 -j



--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Mike Nicolino]

> I'm using System.Data.SQLite version 1.0.95.0 and have what appears to 
> be a bug with Virtual Tables.  Queries using 'like' in the where 
> clause are not getting the like clause passed to BestIndex as a query 
> constraint.
> Specifically:
>
>
> -  Simple query: select * from foo where name like 'a%'

The LIKE operator can be overridden by the application to mean anything the 
application wants - it is not compelled to follow standard SQL semantics.  For 
that reason, virtual tables are unable to optimize using LIKE since they have 
no way of knowing what it will do.

Works as designed.


Not communicating the like constraint to virtual tables make it impossible for 
a virtual table to do query optimization to be done in that case.  I realize 
like behavior can be overridden, but the resulting 'query' still needs to 
filter down to virtual table in some way to avoid full table scans.  Otherwise 
any queries using 'like' against a virtual table of a substantial size become 
potentially unusable depending on 'time' required for a full table scan.

Incidentally, this used to 'work' in a much older version of SQLite, though the 
semantics may not have been correct in all cases.  Version 3.7.7.1, ended up 
transforming like to of pair of constraints in the "like 'a%'" case which were 
passed to BestIndex.

Thanks,
MikeN



-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Thursday, March 05, 2015 10:46 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite 
with 'like'

On 3/5/15, Mike Nicolino  wrote:
> I'm using System.Data.SQLite version 1.0.95.0 and have what appears to 
> be a bug with Virtual Tables.  Queries using 'like' in the where 
> clause are not getting the like clause passed to BestIndex as a query 
> constraint.
> Specifically:
>
>
> -  Simple query: select * from foo where name like 'a%'

The LIKE operator can be overridden by the application to mean anything the 
application wants - it is not compelled to follow standard SQL semantics.  For 
that reason, virtual tables are unable to optimize using LIKE since they have 
no way of knowing what it will do.

Works as designed.

>
> -  Break inside module BestIndex
>
> -  SQLiteIndex.Inputs.Constraints has 0 length (no constraints)
>
> The above causes a full table scan of the virtual table for queries 
> using 'like', which is very bad for any virtual table of a substantial size.
> Virtual tables need to be able to use 'like' clauses to restrict 
> result set size.
>
> Before I bug this issue, is anyone aware of it and have any workaround?
> Currently, the only workaround I've got, is telling users don't use 'like'
> in their queries (obviously not a good thing).
>
> Thanks,
> MikeN
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Mike Nicolino]

I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a bug 
with Virtual Tables.  Queries using 'like' in the where clause are not getting 
the like clause passed to BestIndex as a query constraint.  Specifically:


-  Simple query: select * from foo where name like 'a%'

-  Break inside module BestIndex

-  SQLiteIndex.Inputs.Constraints has 0 length (no constraints)

The above causes a full table scan of the virtual table for queries using 
'like', which is very bad for any virtual table of a substantial size.  Virtual 
tables need to be able to use 'like' clauses to restrict result set size.

Before I bug this issue, is anyone aware of it and have any workaround?  
Currently, the only workaround I've got, is telling users don't use 'like' in 
their queries (obviously not a good thing).

Thanks,
MikeN

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Richard Hipp]

On 3/5/15, Mike Nicolino  wrote:
> Incidentally, this used to 'work' in a much older version of SQLite, though
> the semantics may not have been correct in all cases.  Version 3.7.7.1,
> ended up transforming like to of pair of constraints in the "like 'a%'" case
> which were passed to BestIndex.
>

That was a bug - it could lead to incorrect answers depending on the
virtual table and its content.  The bug was fixed on 2012-03-29.
https://www.sqlite.org/src/timeline?c=2012-03-29+14:29:07

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Richard Hipp]

On 3/5/15, Mike Nicolino  wrote:
> I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a
> bug with Virtual Tables.  Queries using 'like' in the where clause are not
> getting the like clause passed to BestIndex as a query constraint.
> Specifically:
>
>
> -  Simple query: select * from foo where name like 'a%'

The LIKE operator can be overridden by the application to mean
anything the application wants - it is not compelled to follow
standard SQL semantics.  For that reason, virtual tables are unable to
optimize using LIKE since they have no way of knowing what it will do.

Works as designed.

>
> -  Break inside module BestIndex
>
> -  SQLiteIndex.Inputs.Constraints has 0 length (no constraints)
>
> The above causes a full table scan of the virtual table for queries using
> 'like', which is very bad for any virtual table of a substantial size.
> Virtual tables need to be able to use 'like' clauses to restrict result set
> size.
>
> Before I bug this issue, is anyone aware of it and have any workaround?
> Currently, the only workaround I've got, is telling users don't use 'like'
> in their queries (obviously not a good thing).
>
> Thanks,
> MikeN
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'

[Jay Kreibich]

On Mar 5, 2015, at 12:30 PM, Mike Nicolino  
wrote:

> I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a 
> bug with Virtual Tables.  Queries using 'like' in the where clause are not 
> getting the like clause passed to BestIndex as a query constraint.  
> Specifically:
> 


?LIKE? is not a supported virtual table index constraint.  See: 
http://www.sqlite.org/c3ref/c_index_constraint_eq.html   As such, it cannot be 
passed to a virtual table implementation, and the constraint is handled by the 
SQLite engine above the virtual table implementation.

Given that the LIKE expression is translated into an SQL function, which the 
application can override, it would be difficult for a virtual table to 
correctly implement a LIKE operation internally, while matching the exact 
functionality of the current LIKE function.

Consider a statement like this:

SELECT * FROM myVirtualTable AS vt WHERE returnThisRow_CustomFunction( 
vt.col1 );

If returnThisRow_CustomFunction() is a function returns a true or false based 
on? well, who knows what? there is no way for a virtual table implementation to 
understand the inter-workings of that function and pre-filter the rows.  LIKE 
is no different.



It should be noted that MATCH is a supported virtual table index constraint 
supported.  Along with the virtual table xFindFunction() function allows a 
virtual table to implement a table specific filter function.  This is how the 
FTS extensions implement searches.

Consider providing a virtual table specific MATCH function, over-ride use on 
your table with xFindFunction(), and rewrite statements using MATCH rather than 
LIKE.

See the FTS modules as examples.  You might want to start here: 
https://www.sqlite.org/fts3.html


 -j



--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[E.Pasma]

Op 21 jul 2013, om 18:06 heeft Simon Slavin het volgende geschreven:


On 21 Jul 2013, at 4:41pm, E.Pasma  wrote:

Is a change in SQLite imaginable such that column expressions are  
not re-evaluated with each reference to the column alias?

...
...  This is partly because the order of evaluation of terms in a  
SELECT is not defined: SQL permits those three values to be  
evaluated in any order. ...


Your suggestion introduces a new requirement on how SQL works that  
it must figure out the "AS" expressions first.  Which might do  
nicely for your example but it would cause delays in other commands.


That is a plausible reason.
Just one more thought with respect to the originally posted query:

select 7 as a, 8 as b, a / b as c;

This might then as well be accepted by sqlite as if it occured in the  
order by clause. 
___

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Petite Abeille]

On Jul 21, 2013, at 7:47 PM, Igor Tandetnik  wrote:

> This query is in fact perfectly legal. It's OK to refer to column aliases in 
> ORDER BY clause.

Perhaps in SQLite, yes. 

select 1 as a order by 1;
select 1 as a order by a;
select x as a from ( select 1 as x )  order by a;

But this is far from universal.

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Igor Tandetnik]

On 7/21/2013 12:06 PM, Simon Slavin wrote:

One of the problems with this is that it's not standard SQL.  You're not meant 
to be able to refer to column aliases inside the SELECT that defines them.  For 
instance

SELECT yearJoined AS y, ageWhenJoined AS a, (y-a) AS yob FROM members

is not allowed in the SQL standard.  This is partly because the order of 
evaluation of terms in a SELECT is not defined: SQL permits those three values 
to be evaluated in any order.  Now taking a look at your query

select id, (subquery) as c from categories order by c


This query is in fact perfectly legal. It's OK to refer to column 
aliases in ORDER BY clause.

--
Igor Tandetnik

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Luuk]

On 21-07-2013 12:01, E.Pasma wrote:


Op 21 jul 2013, om 11:27 heeft Mikael het volgende geschreven:


Hi Igor,

Ah I just noticed how you wrote your query and it delivers for it indeed.

Here's an arbitrary example verifying its works.

Neat - thanks!

sqlite3 test.sqlite
create table categories (id number);
insert into categories (id) values (5),(10),(15);
create table ot (v number);
insert into ot (v) values (2),(4),(6),(8),(14),(16),(20);

select id, a, b, 1.0 * a / b as c from
(
select
id,
(select sum(v) from ot as ot1 where ot1.v > categories.id) as a,
(select sum(v) from ot as ot2 where ot2.v < categories.id) AS b
from categories
)
order by c;

Mikael


Only the execution plan of this query is not optimal:
0|0|0|SCAN TABLE categories (~100 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
4|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5
5|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
6|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY





It's not optiomal, but would this be better, or worse?

select id, sum(a) as a, sum(b) as b, 1.0 * sum(a) / sum(b) as c FROM
(
select
 id,
 (case when ot.v>categories.id then ot.v else 0 end) as a,
 (case when ot.vi suspect that this wil depend on the number of rows in both tables, but 
i dont know how that will influence this query ;)



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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Keith Medcalf]

You can also use this form:

select id, a, b, 1.0 * a / b as c from
(
select
id,
(select sum(v) from ot as ot1 where ot1.v > categories.id) as a,
(select sum(v) from ot as ot2 where ot2.v < categories.id) AS b
from categories order by +id
)
order by c;



> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Clemens Ladisch
> Sent: Sunday, 21 July, 2013 07:43
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as
> c; " possible whatsoever?
> 
> RSmith wrote:
> > On 2013/07/21 12:01, E.Pasma wrote:
> >> Only the execution plan of this query is not optimal:
> >> 0|0|0|SCAN TABLE categories (~100 rows)
> >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
> >> 1|0|0|SCAN TABLE ot AS ot1 (~33 rows)
> >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
> >> 2|0|0|SCAN TABLE ot AS ot2 (~33 rows)
> >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
> >> 3|0|0|SCAN TABLE ot AS ot1 (~33 rows)
> >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
> >> 4|0|0|SCAN TABLE ot AS ot2 (~33 rows)
> >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5
> >> 5|0|0|SCAN TABLE ot AS ot1 (~33 rows)
> >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
> >> 6|0|0|SCAN TABLE ot AS ot2 (~33 rows)
> >> 0|0|0|USE TEMP B-TREE FOR ORDER BY
> >
> > Well yes, the plan does not read like one would expect an optimal
> plan
> > to read like - but to the purpose of the original request there is
> no
> > more-optimal a plan, is there?
> 
> SQLite always attempts to flatten such subqueries:
> <http://www.sqlite.org/optoverview.html#flattening>
> 
> However, this can be counterproductive when the values in the subquery
> are subquery lookups themselves; storing the values once in a
> temporary
> table might be faster.
> 
> To prevent SQLite from flattening, break one of the rules from the
> link
> above; for example, add an OFFSET clause:
> 
> select id, a, b, 1.0 * a / b as c from
> (
> select
> id,
> (select sum(v) from ot as ot1 where ot1.v > categories.id) as a,
> (select sum(v) from ot as ot2 where ot2.v < categories.id) AS b
> from categories
> limit -1 offset 0
> )
> order by c;
> 
> 1|0|0|SCAN TABLE categories (~100 rows)
> 1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
> 2|0|0|SCAN TABLE ot AS ot1 (~33 rows)
> 1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
> 3|0|0|SCAN TABLE ot AS ot2 (~33 rows)
> 0|0|0|SCAN SUBQUERY 1 (~4294967295 rows)
> 0|0|0|USE TEMP B-TREE FOR ORDER BY
> 
> 
> Regards,
> Clemens
> ___
> 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] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Simon Slavin]

On 21 Jul 2013, at 4:41pm, E.Pasma  wrote:

> Is a change in SQLite imaginable such that column expressions are not 
> re-evaluated with each reference to the column alias?
> This could also improve queries that use aliases only in the order by clause, 
> like
>   select id, (subquery) as c from categories order by c;

One of the problems with this is that it's not standard SQL.  You're not meant 
to be able to refer to column aliases inside the SELECT that defines them.  For 
instance

SELECT yearJoined AS y, ageWhenJoined AS a, (y-a) AS yob FROM members

is not allowed in the SQL standard.  This is partly because the order of 
evaluation of terms in a SELECT is not defined: SQL permits those three values 
to be evaluated in any order.  Now taking a look at your query

select id, (subquery) as c from categories order by c

SQL can choose to evaluate the ORDER BY clause first, and only then to evaluate 
the expressions "id" and "c".  But that leaves it ordering by a "c" value which 
it hasn't evaluated yet.

Your suggestion introduces a new requirement on how SQL works that it must 
figure out the "AS" expressions first.  Which might do nicely for your example 
but it would cause delays in other commands.

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[E.Pasma]

Op 21 jul 2013, om 15:43 heeft Clemens Ladisch het volgende geschreven:


RSmith wrote:

On 2013/07/21 12:01, E.Pasma wrote:

Only the execution plan of this query is not optimal:
0|0|0|SCAN TABLE categories (~100 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
4|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5
5|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
6|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY


Well yes, the plan does not read like one would expect an optimal  
plan

to read like - but to the purpose of the original request there is no
more-optimal a plan, is there?


SQLite always attempts to flatten such subqueries:


However, this can be counterproductive when the values in the subquery
are subquery lookups themselves; storing the values once in a  
temporary

table might be faster.

To prevent SQLite from flattening, break one of the rules from the  
link

above; for example, add an OFFSET clause:

select id, a, b, 1.0 * a / b as c from
(
select
id,
(select sum(v) from ot as ot1 where ot1.v > categories.id) as a,
(select sum(v) from ot as ot2 where ot2.v < categories.id) AS b
from categories
limit -1 offset 0
)
order by c;

1|0|0|SCAN TABLE categories (~100 rows)
1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SCAN TABLE ot AS ot1 (~33 rows)
1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|SCAN SUBQUERY 1 (~4294967295 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY


Regards,
Clemens

Is a change in SQLite imaginable such that column expressions are not  
re-evaluated with each reference to the column alias?
This could also improve queries that use aliases only in the order by  
clause, like

   select id, (subquery) as c from categories order by c;
Sorry if this is beyond the subject of thie thread,
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Clemens Ladisch]

RSmith wrote:
> On 2013/07/21 12:01, E.Pasma wrote:
>> Only the execution plan of this query is not optimal:
>> 0|0|0|SCAN TABLE categories (~100 rows)
>> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
>> 1|0|0|SCAN TABLE ot AS ot1 (~33 rows)
>> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
>> 2|0|0|SCAN TABLE ot AS ot2 (~33 rows)
>> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
>> 3|0|0|SCAN TABLE ot AS ot1 (~33 rows)
>> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
>> 4|0|0|SCAN TABLE ot AS ot2 (~33 rows)
>> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5
>> 5|0|0|SCAN TABLE ot AS ot1 (~33 rows)
>> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
>> 6|0|0|SCAN TABLE ot AS ot2 (~33 rows)
>> 0|0|0|USE TEMP B-TREE FOR ORDER BY
>
> Well yes, the plan does not read like one would expect an optimal plan
> to read like - but to the purpose of the original request there is no
> more-optimal a plan, is there?

SQLite always attempts to flatten such subqueries:


However, this can be counterproductive when the values in the subquery
are subquery lookups themselves; storing the values once in a temporary
table might be faster.

To prevent SQLite from flattening, break one of the rules from the link
above; for example, add an OFFSET clause:

select id, a, b, 1.0 * a / b as c from
(
select
id,
(select sum(v) from ot as ot1 where ot1.v > categories.id) as a,
(select sum(v) from ot as ot2 where ot2.v < categories.id) AS b
from categories
limit -1 offset 0
)
order by c;

1|0|0|SCAN TABLE categories (~100 rows)
1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SCAN TABLE ot AS ot1 (~33 rows)
1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|SCAN SUBQUERY 1 (~4294967295 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY


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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[RSmith]

Well yes, the plan does not read like one would expect an optimal plan to read like - but to the purpose of the original request 
there is no more-optimal a plan, is there?.
The entire column used to sort by  is made up on the spot and therefore temp BTrees are needed and all the other quirks, as 
expected. It's as optimal as it gets for this kind of query, unless I'm missing an obvious deficiency.



On 2013/07/21 12:01, E.Pasma wrote:

Only the execution plan of this query is not optimal:
0|0|0|SCAN TABLE categories (~100 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
4|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5
5|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
6|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY




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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[E.Pasma]

Op 21 jul 2013, om 11:27 heeft Mikael het volgende geschreven:


Hi Igor,

Ah I just noticed how you wrote your query and it delivers for it  
indeed.


Here's an arbitrary example verifying its works.

Neat - thanks!

sqlite3 test.sqlite
create table categories (id number);
insert into categories (id) values (5),(10),(15);
create table ot (v number);
insert into ot (v) values (2),(4),(6),(8),(14),(16),(20);

select id, a, b, 1.0 * a / b as c from
(
select
id,
(select sum(v) from ot as ot1 where ot1.v > categories.id) as a,
(select sum(v) from ot as ot2 where ot2.v < categories.id) AS b
from categories
)
order by c;

Mikael


Only the execution plan of this query is not optimal:
0|0|0|SCAN TABLE categories (~100 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
4|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5
5|0|0|SCAN TABLE ot AS ot1 (~33 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
6|0|0|SCAN TABLE ot AS ot2 (~33 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY



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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Mikael]

Hi Igor,

Ah I just noticed how you wrote your query and it delivers for it indeed.

Here's an arbitrary example verifying its works.

Neat - thanks!

sqlite3 test.sqlite
create table categories (id number);
insert into categories (id) values (5),(10),(15);
create table ot (v number);
insert into ot (v) values (2),(4),(6),(8),(14),(16),(20);

select id, a, b, 1.0 * a / b as c from
(
select
id,
(select sum(v) from ot as ot1 where ot1.v > categories.id) as a,
(select sum(v) from ot as ot2 where ot2.v < categories.id) AS b
from categories
)
order by c;

Mikael

2013/7/21 Igor Tandetnik 

> On 7/20/2013 7:04 PM, Mikael wrote:
>
>> C is not a present column, but is the result of an expression involving A
>> and B (namely, the expression A / B).
>>
>
> Yes. And this is notable because...?
>
>
>  Can you please take this into consideration and update the query you
>> proposed? :))
>>
>
> In what way do you believe the query as written is deficient?
>
> --
> 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] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Igor Tandetnik]

On 7/20/2013 7:04 PM, Mikael wrote:

C is not a present column, but is the result of an expression involving A
and B (namely, the expression A / B).


Yes. And this is notable because...?


Can you please take this into consideration and update the query you
proposed? :))


In what way do you believe the query as written is deficient?
--
Igor Tandetnik

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Mikael]

Hi Igor,

C is not a present column, but is the result of an expression involving A
and B (namely, the expression A / B).

Can you please take this into consideration and update the query you
proposed? :))

Thanks,
Mikael

2013/7/20 Igor Tandetnik 

> On 7/20/2013 9:54 AM, Mikael wrote:
>
>> So again,
>>
>> SELECT
>> id,
>> (SELECT [very complex subselect here, that uses categories.id as input])
>> AS
>> a,
>> (SELECT [another very complex subselect here, that uses categories.id as
>> input]) AS b,
>> a / b AS c
>> FROM categories
>> ORDER BY c;
>>
>
> select id, a, b, a/b as c from (
>
> SELECT
> id,
> (SELECT [very complex subselect here, that uses categories.id as input])
> AS
> a,
> (SELECT [another very complex subselect here, that uses categories.id as
> input]) AS b
> FROM categories
> )
> ORDER BY c;
>
> --
> 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] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Igor Tandetnik]

On 7/20/2013 3:29 PM, E.Pasma wrote:

Op 20 jul 2013, om 16:13 heeft Igor Tandetnik het volgende geschreven:

select id, a, b, a/b as c from (
SELECT
id,
(SELECT [very complex subselect here, that uses categories.id as
input]) AS
a,
(SELECT [another very complex subselect here, that uses categories.id as
input]) AS b
FROM categories
)
ORDER BY c;


Hi, in my opinion there is nothing funky about this query and I hope my
colleagues agree. However it can be slower than strictly needed. I
experienced that the "very complex subqueries" are evaluated each time
again when referenced from the outer query.


My experience is different. The results of the inner query are saved 
into an ephemeral table, which is then sorted, and iterated over by the 
outer query.

--
Igor Tandetnik

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[E.Pasma]

Op 20 jul 2013, om 16:13 heeft Igor Tandetnik het volgende geschreven:


On 7/20/2013 9:54 AM, Mikael wrote:

So again,

SELECT
id,
(SELECT [very complex subselect here, that uses categories.id as  
input]) AS

a,
(SELECT [another very complex subselect here, that uses  
categories.id as

input]) AS b,
a / b AS c
FROM categories
ORDER BY c;


select id, a, b, a/b as c from (
SELECT
id,
(SELECT [very complex subselect here, that uses categories.id as  
input]) AS

a,
(SELECT [another very complex subselect here, that uses  
categories.id as

input]) AS b
FROM categories
)
ORDER BY c;

--
Igor Tandetnik


Hi, in my opinion there is nothing funky about this query and I hope  
my colleagues agree. However it can be slower than strictly needed. I  
experienced that the "very complex subqueries" are evaluated each time  
again when referenced from the outer query. That is three times here:

- select a, b
- select a / b as c
- order by c
I like to make myself more clear in a seperate mail and work out a  
reproducible case because I hope this could be improved in the  
optimizer.

Thanks, EPasma (sqlite version 3.7.15)

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Michael Black]

Is this some mental exercise? Why can't you do this in the calling code
rather than some funky SQL select?
Or add a custom function?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mikael
Sent: Saturday, July 20, 2013 8:54 AM
To: General Discussion of SQLite Database; luu...@gmail.com
Subject: Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; "
possible whatsoever?

Ah I realize now I didn't write it out in the example, but by thing and
thing2 I just allegorically wanted to represent a *very complex* subselect,
so here we go more clearly:

Inlining this subselect's SQL expression in the "A / B" part would make it
need to execute once more, which would make it take double the time, which
would be really long.

So again,

SELECT
id,
(SELECT [very complex subselect here, that uses categories.id as input]) AS
a,
(SELECT [another very complex subselect here, that uses categories.id as
input]) AS b,
a / b AS c
FROM categories
ORDER BY c;


any way to do it whatsoever?

Thanks :))
Mikael


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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Igor Tandetnik]

On 7/20/2013 9:54 AM, Mikael wrote:

So again,

SELECT
id,
(SELECT [very complex subselect here, that uses categories.id as input]) AS
a,
(SELECT [another very complex subselect here, that uses categories.id as
input]) AS b,
a / b AS c
FROM categories
ORDER BY c;


select id, a, b, a/b as c from (
SELECT
id,
(SELECT [very complex subselect here, that uses categories.id as input]) AS
a,
(SELECT [another very complex subselect here, that uses categories.id as
input]) AS b
FROM categories
)
ORDER BY c;

--
Igor Tandetnik

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Mikael]

Ah I realize now I didn't write it out in the example, but by thing and
thing2 I just allegorically wanted to represent a *very complex* subselect,
so here we go more clearly:

Inlining this subselect's SQL expression in the "A / B" part would make it
need to execute once more, which would make it take double the time, which
would be really long.

So again,

SELECT
id,
(SELECT [very complex subselect here, that uses categories.id as input]) AS
a,
(SELECT [another very complex subselect here, that uses categories.id as
input]) AS b,
a / b AS c
FROM categories
ORDER BY c;


any way to do it whatsoever?

Thanks :))
Mikael

2013/7/20 Luuk 

>
> SELECT
>   id,
>   othertable.thing as a,
>   othertable2.thing as b,
>   othertable.thing/othertable2.**thing as c
> FROM categories
> INNER jOIN othertable ON othertable.something = categories.id
> INNER JOIN othertable2 ON othertable2.something2 = categories.id
> ORDER BY c;
>
>
> __**_
> 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] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Luuk]

On 20-07-2013 15:26, Mikael wrote:


SELECT
id,
(SELECT thing FROM othertable WHERE othertable.something = categories.id)
AS a,
(SELECT thing2 FROM othertable2 WHERE othertable2.something2 = categories.id)
AS b,
a / b AS c
FROM categories ORDER BY c;


Is there any way whatsoever to do this?



SELECT
  id,
  othertable.thing as a,
  othertable2.thing as b,
  othertable.thing/othertable2.thing as c
FROM categories
INNER jOIN othertable ON othertable.something = categories.id
INNER JOIN othertable2 ON othertable2.something2 = categories.id
ORDER BY c;

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

Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Mikael]

Hi Clemens!

Hmm. Let's see how this fits into the bigger picture:

I have a table "categories". I'm doing a select for each of its id:s i.e:

SELECT id FROM categories;


Now, what I want to do is that I want to do two separate subselects that
use categories.id for the respective row the categories select is at, as
input.

So something like

SELECT
id,
(SELECT thing FROM othertable WHERE othertable.something = categories.id)
AS a,
(SELECT thing2 FROM othertable2 WHERE othertable2.something2 = categories.id)
AS b,
a / b AS c
FROM categories ORDER BY c;


Is there any way whatsoever to do this?

Thanks,
Mikael

2013/7/20 Clemens Ladisch 

> Mikael wrote:
> > Is anything like "select 7 as a, 8 as b, a / b as c;" possible?
>
> Not directy, but you could use a subquery:
>
>   SELECT *, a / b AS c FROM (SELECT 7 AS a, 8 AS b);
>
>
> Regards,
> Clemens
> ___
> 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] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Clemens Ladisch]

Mikael wrote:
> Is anything like "select 7 as a, 8 as b, a / b as c;" possible?

Not directy, but you could use a subquery:

  SELECT *, a / b AS c FROM (SELECT 7 AS a, 8 AS b);


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

[sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

[Mikael]

Hi!

Is anything like "select 7 as a, 8 as b, a / b as c;" possible?

I.e., I select one value into one column and another into another (it's a
subselect, expression etc.), and then I want to generate a separate column
that's an expression involving both the earlier two generated values.
Possibly even giving that value a name so I can sort based on it.

Is this doable whatsoever?

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

Re: [sqlite] simple like query optimization

[Durga D]

Here, a,b,d,e,f,u and v are variable length strings.

delimiter '/ ' is constant.

On Fri, Jan 13, 2012 at 12:22 PM, Durga D  wrote:

> Hi All,
>
> I have a table like this:
>
> h   t0info( h is primary key, t0info is unique)
> ---
>
> 1/a/d/u
> 2/a/e/u
> 3/a/f/u
> 4/a/g/v
> 5/a/b/c/d/e/f
>
> input is : /a
> output should be: d, e f, g and b
>
> I am doing this way: select t0info from t0 where t0info like '/a%';
>
> processing on string operations on result set to get d, e, f, g and b.
>
> any alternative solution for this?
>
> Thanks in advance,
>  Durga.
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] simple like query optimization

[Durga D]

Hi All,

I have a table like this:

h   t0info( h is primary key, t0info is unique)
---

1/a/d/u
2/a/e/u
3/a/f/u
4/a/g/v
5/a/b/c/d/e/f

input is : /a
output should be: d, e f, g and b

I am doing this way: select t0info from t0 where t0info like '/a%';

processing on string operations on result set to get d, e, f, g and b.

any alternative solution for this?

Thanks in advance,
Durga.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

[Nico Williams]

On Fri, Nov 11, 2011 at 1:16 PM, Simon Slavin  wrote:
> On 11 Nov 2011, at 6:09pm, Nico Williams wrote:
>> blob = blob should be a binary comparison
>>
>> blob = string should be a string comparison
>>
>> blob LIKE pattern should either treat the blob as a string or not, but
>> I don't see why either behavior should imply that blob = blob should
>> be anything but a binary comparison or blob = string anything other
>> than a string comparison.
>>
>> I personally see no reason why LIKE shouldn't apply the pattern to the
>> whole blob, but GLOB and REGEXP must apply only to strings (or BLOBs
>> cast to TEXT).
>
> You know, actually the only one of those I think should work is the first: 
> you can compare two blobs for identity.  Anything which treats a BLOB like a 
> string should yield an error of some kind (null ?).  I think of a BLOB as a 
> black box of some kind: you treat it as a whole, and don't look inside it.  
> If you want parts of it you need to know whether to treat it as a string, a 
> float, a colour, etc..

I like the ability to cast blobs to text.  I do agree that NULs in
blobs must be treated as string ending NULs (if nothing else because
TEXT in SQLite3 is supposed to be Unicode).  But I would agree that an
explicit cast should be required, that blobs and text should always
compare as not equal unless a cast is involved.

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

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

[Simon Slavin]

On 11 Nov 2011, at 6:09pm, Nico Williams wrote:

> blob = blob should be a binary comparison
> 
> blob = string should be a string comparison
> 
> blob LIKE pattern should either treat the blob as a string or not, but
> I don't see why either behavior should imply that blob = blob should
> be anything but a binary comparison or blob = string anything other
> than a string comparison.
> 
> I personally see no reason why LIKE shouldn't apply the pattern to the
> whole blob, but GLOB and REGEXP must apply only to strings (or BLOBs
> cast to TEXT).

You know, actually the only one of those I think should work is the first: you 
can compare two blobs for identity.  Anything which treats a BLOB like a string 
should yield an error of some kind (null ?).  I think of a BLOB as a black box 
of some kind: you treat it as a whole, and don't look inside it.  If you want 
parts of it you need to know whether to treat it as a string, a float, a 
colour, etc..

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

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

[Nico Williams]

On Fri, Nov 11, 2011 at 11:55 AM, Yuriy Kaminskiy  wrote:
> One way or other, "=", "LIKE" and "GLOB" results should be consistent.
> If string is NUL-terminated, "=" should ignore everything after NUL.
> If string is length-terminated, "LIKE" should not ignore bytes after NUL.

blob = blob should be a binary comparison

blob = string should be a string comparison

blob LIKE pattern should either treat the blob as a string or not, but
I don't see why either behavior should imply that blob = blob should
be anything but a binary comparison or blob = string anything other
than a string comparison.

I personally see no reason why LIKE shouldn't apply the pattern to the
whole blob, but GLOB and REGEXP must apply only to strings (or BLOBs
cast to TEXT).

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

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

[Yuriy Kaminskiy]

Simon Slavin wrote:
> On 9 Nov 2011, at 8:03pm, Yuriy Kaminskiy wrote:
> 
>> Look at: SELECT hex(X'1245005679'),hex(X'1245001234');
>> 
>> And compare: SELECT X'1245005679' LIKE X'1245001234'; 1 -- incorrect SELECT
>> X'1245005679' = X'1245001234'; 0 -- correct SELECT X'1245005679' >
>> X'1245001234'; 1 -- correct
>> 
>> "LIKE" (in both "native" and "icu" implementations) ignores value length 
>> (sqlite_value_bytes) and stops at NUL character. Compare that with "=" and
>> ">" that compares full value.
>> 
>> Arguable SQL_BLOB should be illegal operand for LIKE/GLOB, but then this
>> should be documented and they should return error when called with BLOB
>> argument instead of returning nonsense.
> 
> The definition of LIKE on the expression page states (not with complete
> clarity) that LIKE operates on strings.  So we're back to an old favourite: a
> decision as to what constitutes a string inside SQLite.  One definition would
> say that a string would terminate with the first 0x00 no matter how many
> bytes are stored (C style strings).  Another would say that the string
> terminates with the last byte stored (Pascal style strings).

One way or other, "=", "LIKE" and "GLOB" results should be consistent.
If string is NUL-terminated, "=" should ignore everything after NUL.
If string is length-terminated, "LIKE" should not ignore bytes after NUL.

If devs have no time to change code and/or make decisions now, those cases
should be at least documented as "function/operation A,B,C (currently) have
undefined behavior on BLOB". Not very nice, but randomly stumbling over such
surprises is certainly worse.

... and few related strangeness: UPPER/LOWER/*TRIM/REPLACE also accept BLOB
argument, but return *TEXT* instead. With somewhat unexpected result:

sqlite> SELECT length(   X'41424300414243313233');
10
sqlite> SELECT length( lower(X'41424300414243313233'));
3
sqlite> SELECT length(CAST(lower(X'41424300414243313233') AS BLOB));
10
sqlite> .mode insert
sqlite> select  rtrim(X'4142430061626333',X'334363');
INSERT INTO table VALUES('ABC');
sqlite> select CAST(rtrim(X'4142430061626333',X'334363') AS BLOB)
INSERT INTO table VALUES(X'414243006162');

(BTW, one can consider that *TRIM, LIKE and GLOB semantic on BLOB should be
different - with BLOB they should operate on *bytes*, not *utf-8 chars*; one
more reason to state "undefined behavior" for now).

Same with || operator (OP_Concat): it takes BLOB, but make result TEXT (leading
to [arguable illegal] TEXT with embedded NUL {or with broken UTF-X encoding}:
X'1234' || X'004567' [also look at
, where this
problem triggered invalid .dump])

SUBSTR also documented to operate *on string*, but, unlike above functions, when
supplied with BLOB argument it correctly returns BLOB value.

Not sure if it worth changing code, but certainly should be somehow mentioned in
documentation (including clearly stated "undocumented behavior on BLOB" as 
option).

> I don't think any official definition is stated anywhere in the documentation
> intended for users.  (I haven't looked at comments in the source code.)  And
> I suspect that if DRH wants to decide one way or another, this may point up
> some inconsistencies in the codebase which should be quickly fixed.

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

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

[Yuriy Kaminskiy]

Roger Andersson wrote:
>  On 11/09/11 19:42, Yuriy Kaminskiy wrote:
>> Paul Corke wrote:
>>> On 09 November 2011 15:32, hmas wrote:
>>>
 sqlite>  select hex(foocol)  from footable where foocol like
 '98012470700566';
 39393939393830313234373037303035363600
>>> It looks like there's an extra 00 on the end.
>>>
>>> x'3900' != x'39'
>> That said, it seems LIKE operator is buggy.
>> sqlite>  SELECT X'1245005679' LIKE X'1245001234';
>> 1
>>
> 
> On Windows
> 
> sqlite3.exe
> SQLite version 3.7.9 2011-11-01 00:52:41
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> SELECT X'1245005679',X'1245001234';
> ↕E|↕E

These strings are (intentionally) not printable, no surprise here.

Look at:
SELECT hex(X'1245005679'),hex(X'1245001234');

And compare:
SELECT X'1245005679' LIKE X'1245001234';
1 -- incorrect
SELECT X'1245005679' = X'1245001234';
0 -- correct
SELECT X'1245005679' > X'1245001234';
1 -- correct

"LIKE" (in both "native" and "icu" implementations) ignores value length
(sqlite_value_bytes) and stops at NUL character. Compare that with "=" and ">"
that compares full value.

Arguable SQL_BLOB should be illegal operand for LIKE/GLOB, but then this should
be documented and they should return error when called with BLOB argument
instead of returning nonsense.

Well, not sure, maybe shell.c is also buggy in respect with printing blobs (only
".mode insert" handles SQLITE_BLOB properly [IIRC, NUL-in-middle is only legal
in BLOB], other .mode's treats everything as text and ignores length, and so
arguable buggy), but that's different issue.

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

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

[Roger Andersson]

 On 11/09/11 19:42, Yuriy Kaminskiy wrote:

Paul Corke wrote:

On 09 November 2011 15:32, hmas wrote:


sqlite>  select hex(foocol)  from footable where foocol like
'98012470700566';
39393939393830313234373037303035363600

It looks like there's an extra 00 on the end.

x'3900' != x'39'

That said, it seems LIKE operator is buggy.
sqlite>  SELECT X'1245005679' LIKE X'1245001234';
1

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



On Windows

sqlite3.exe
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT X'1245005679',X'1245001234';
↕E|↕E

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

[sqlite] [bug] LIKE operator ignores rest of string after NUL character (was: select ... where [=] or [like])

[Yuriy Kaminskiy]

Paul Corke wrote:
> On 09 November 2011 15:32, hmas wrote:
> 
>> sqlite> select hex(foocol)  from footable where foocol like
>> '98012470700566';
>> 39393939393830313234373037303035363600
> 
> It looks like there's an extra 00 on the end.
> 
> x'3900' != x'39'

That said, it seems LIKE operator is buggy.
sqlite> SELECT X'1245005679' LIKE X'1245001234';
1

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

Re: [sqlite] database like ms northwind

[Eugene N]

As i understand it, Northwind is simply an example of Ms access (populated
db). So, if you need an example of access, buy access and you get Northwind;
If, on the other hand, you dont buy it, you cant use access at all (unless
by broking the law);

If you are looking for something like Northwind for access, but not for
access, then this pops up:
http://stackoverflow.com/questions/2100982/alternatives-to-northwind

2011/10/23 saeed ahmed 

> i want to make a databse like Northwind of ms Access.from where i can get
> guidance or download something similar?
> ___
> 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] database like ms northwind

[saeed ahmed]

i want to make a databse like Northwind of ms Access.from where i can get
guidance or download something similar?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Fedor Tyurin]

Were you able to solve the problem? What solution have to chosen?

BR,
Fedor

I'd appreciate any suggestions on good ways to do this, I'm neither an SQL
or
sqlite expert, so I might be thinking about it all wrong.
I have something like a (read-only) address book/rolodex, with interactive
searching. As users type into the search box, I need to first know for each
section how many rows match the substring typed so far. I only display the
rows that are visible on screen.
I have two queries:
(A) I count the rows in a letter group.
If they typed "e":
select substr(name,1,1), count(*) from my_table where name like '%e%'
group by substr(name,1,1);
A|94
B|118
C|131
...
This is too slow, ~3sec, with 2500 rows, and we want to have 1 rows.
Worse, when they type "es", the search is as slow after they type "s" as
when
they typed "e", even though the "es" rows are a sub-set of the rows that
matched "e".
FTS3 only searches full terms/words by default, but I think if I built a
custom
tokenizer that returned all the suffix trees for a name:
"fu bar" => [ "r", "ar", "bar", " bar", "u bar", "fu bar"]
That I could do rewrite query (A) like this:
select substr(name,1,1), count(*) from my_table where name match 'e*'
group by substr(name,1,1);
Is this a reasonable approach? Is there a better way? Has somebody
else done this?


(B) I access specific rows within a letter group.
For visible rows, I fetch them by offset into a letter group, so row 4 in
the
"g" section of names containing "e" would be:
select * from my_table where name like "g%" and name like "%e%" order
by name limit 1 offset 4;
The performance for this is OK, right now, I think it's because the first
LIKE
can use the index, so the linear scan is over only a few hundred rows. Or it
could be that the on-screen display of each row is slower than the DB
search. I
think it might become a problem, though.
I'm not sure how I would rewrite this to use FTS3 if it turns out to be to
slow
for a larger DB, maybe a tokenizer that puts the first letter of the name as
the first letter of every suffix?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Black, Michael (IS)]

Sounds to me like Boyer-Moore is needed
http://en.wikipedia.org/wiki/Boyer%E2%80%93Moore_string_search_algorithm
 
And...I would probably pre-load the database table into 26 seperate memory 
tables to avoid any SQL interactivity at all other than the initial loading.  
Adding the SQL layer slows things down far too much.
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Tim Romano
Sent: Mon 8/9/2010 7:00 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 
with suffix-tree tokenizer be the fast way?



First, permit me a little rant. As a user, I dislike this kind of
incremental search feature if there's no easy way to toggle it or to
configure it and the list of items will be large enough to cause a typing
lag. The feature can become an intrusive nuisance, the opposite of what is
intended.  Browsers put this feature on the URL address bar and Google has
it on its search-input. Keystrokes entered often get swallowed up. It's
worse than typing on a 300 baud dumb terminal, for at least on those ancient
machines your characters would eventually be displayed on the green screen,
whereas with today's browsers the characters often just get eaten; I find
myself having to retype the first few characters of a URL or search term far
too often.

I agree with Radzi's suggestion. Once you have the initial set of of hits
(rowid, name)  in an array, do the rest in procedurally rather than going
back against the database with a new SQL query and a longer search string.
That will be much faster that issuing a new SQL query after every keystroke.
 I would wait until the user had typed at least two characters before
kicking off the initial search because finding every value that contains a
common letter is not helpful when the list of matches is a very long one.

Regards
Tim Romano
Swarthmore PA




On Fri, Aug 6, 2010 at 9:54 PM, Scott Hess <sh...@google.com> wrote:

> On Fri, Aug 6, 2010 at 6:08 PM, Sam Roberts <vieuxt...@gmail.com> wrote:
> > On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess <sh...@google.com> wrote:
> >> On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts <vieuxt...@gmail.com>
> wrote:
> >>> FTS3 only searches full terms/words by default, but I think if I built
> a custom
> >>> tokenizer that returned all the suffix trees for a name:
> >>
> >> FTS3 can do prefix searches, MATCH 'a*'.  Also, it aimed to support
> >
> > Prefix searches don't allow matching in the middle of words. For
> > example, I want  "bert"
> > to match my name, "roberts".
>
> Darn.  Sorry, was only thinking with half my brain, and that half
> connected your problem up with some past idea.  You're right, you'd
> need the tidbits to get at the interior substrings.
>
> That said, you should be able to pretty easily copy the current
> tokenizer and modify it to return multiple tokens at a single
> location.
>
> -scott
> ___
> 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

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Tim Romano]

First, permit me a little rant. As a user, I dislike this kind of
incremental search feature if there's no easy way to toggle it or to
configure it and the list of items will be large enough to cause a typing
lag. The feature can become an intrusive nuisance, the opposite of what is
intended.  Browsers put this feature on the URL address bar and Google has
it on its search-input. Keystrokes entered often get swallowed up. It's
worse than typing on a 300 baud dumb terminal, for at least on those ancient
machines your characters would eventually be displayed on the green screen,
whereas with today's browsers the characters often just get eaten; I find
myself having to retype the first few characters of a URL or search term far
too often.

I agree with Radzi's suggestion. Once you have the initial set of of hits
(rowid, name)  in an array, do the rest in procedurally rather than going
back against the database with a new SQL query and a longer search string.
That will be much faster that issuing a new SQL query after every keystroke.
 I would wait until the user had typed at least two characters before
kicking off the initial search because finding every value that contains a
common letter is not helpful when the list of matches is a very long one.

Regards
Tim Romano
Swarthmore PA




On Fri, Aug 6, 2010 at 9:54 PM, Scott Hess  wrote:

> On Fri, Aug 6, 2010 at 6:08 PM, Sam Roberts  wrote:
> > On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess  wrote:
> >> On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts 
> wrote:
> >>> FTS3 only searches full terms/words by default, but I think if I built
> a custom
> >>> tokenizer that returned all the suffix trees for a name:
> >>
> >> FTS3 can do prefix searches, MATCH 'a*'.  Also, it aimed to support
> >
> > Prefix searches don't allow matching in the middle of words. For
> > example, I want  "bert"
> > to match my name, "roberts".
>
> Darn.  Sorry, was only thinking with half my brain, and that half
> connected your problem up with some past idea.  You're right, you'd
> need the tidbits to get at the interior substrings.
>
> That said, you should be able to pretty easily copy the current
> tokenizer and modify it to return multiple tokens at a single
> location.
>
> -scott
> ___
> 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] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Scott Hess]

On Fri, Aug 6, 2010 at 6:08 PM, Sam Roberts  wrote:
> On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess  wrote:
>> On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts  wrote:
>>> FTS3 only searches full terms/words by default, but I think if I built a 
>>> custom
>>> tokenizer that returned all the suffix trees for a name:
>>
>> FTS3 can do prefix searches, MATCH 'a*'.  Also, it aimed to support
>
> Prefix searches don't allow matching in the middle of words. For
> example, I want  "bert"
> to match my name, "roberts".

Darn.  Sorry, was only thinking with half my brain, and that half
connected your problem up with some past idea.  You're right, you'd
need the tidbits to get at the interior substrings.

That said, you should be able to pretty easily copy the current
tokenizer and modify it to return multiple tokens at a single
location.

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

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Mohd Radzi Ibrahim]

Have you not consider loading the whole rows into memory array and use simple 
string search or regexp? I'm sure 10,000 records could be search a blink.

best regards,
Radzi.
On 6-Aug-2010, at 3:42 AM, Sam Roberts wrote:

> I'd appreciate any suggestions on good ways to do this, I'm neither an SQL or
> sqlite expert, so I might be thinking about it all wrong.
> 
> I have something like a (read-only) address book/rolodex, with interactive
> searching. As users type into the search box, I need to first know for each
> section how many rows match the substring typed so far.  I only display the
> rows that are visible on screen.
> 
> I have two queries:
> 
> (A) I count the rows in a letter group.
> 
> If they typed "e":
> 
> select substr(name,1,1), count(*) from my_table where name like '%e%'
> group by substr(name,1,1);
> A|94
> B|118
> C|131
> ...
> 
> This is too slow, ~3sec, with 2500 rows, and we want to have 1 rows.
> 
> Worse, when they type "es", the search is as slow after they type "s" as when
> they typed "e", even though the "es" rows are a sub-set of the rows that
> matched "e".
> 
> FTS3 only searches full terms/words by default, but I think if I built a 
> custom
> tokenizer that returned all the suffix trees for a name:
> 
> "fu bar" => [ "r", "ar", "bar", " bar", "u bar", "fu bar"]
> 
> That I could do rewrite query (A) like this:
> 
> select substr(name,1,1), count(*) from my_table where name match 'e*'
> group by substr(name,1,1);
> 
> Is this a reasonable approach? Is there a better way? Has somebody
> else done this?
> 
> 
> 
> (B) I access specific rows within a letter group.
> 
> For visible rows, I fetch them by offset into a letter group, so row 4 in the
> "g" section of names containing "e" would be:
> 
> select * from my_table where name like "g%" and name like "%e%" order
> by name limit 1 offset 4;
> 
> The performance for this is OK, right now, I think it's because the first LIKE
> can use the index, so the linear scan is over only a few hundred rows. Or it
> could be that the on-screen display of each row is slower than the DB search. 
> I
> think it might become a problem, though.
> 
> I'm not sure how I would rewrite this to use FTS3 if it turns out to be to 
> slow
> for a larger DB, maybe a tokenizer that puts the first letter of  the name as
> the first letter of every suffix?
> ___
> 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] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Sam Roberts]

On Fri, Aug 6, 2010 at 11:32 AM, Scott Hess  wrote:
> On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts  wrote:
>> FTS3 only searches full terms/words by default, but I think if I built a 
>> custom
>> tokenizer that returned all the suffix trees for a name:
>
> FTS3 can do prefix searches, MATCH 'a*'.  Also, it aimed to support

Prefix searches don't allow matching in the middle of words. For
example, I want  "bert"
to match my name, "roberts".

So, I think I'd need to tokenize roberts as "s", "ts", ..., "berts",
"oberts", ... etc.

Then do a prefix match for "bert*" in order to see that "roberts" matches.

Lucky, I don't need or care about any of the snippeting stuff, because
I'm matching short strings (names).

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

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Scott Hess]

On Thu, Aug 5, 2010 at 12:42 PM, Sam Roberts  wrote:
> FTS3 only searches full terms/words by default, but I think if I built a 
> custom
> tokenizer that returned all the suffix trees for a name:

FTS3 can do prefix searches, MATCH 'a*'.  Also, it aimed to support
multiple hits at the same position, for stemming purposes.  So you
might be able to get away with making a copy of fts3_tokenizer1.c, and
modifying it to keep an additional flag in the cursor to let you
return each token twice (once reversed).

I can't offhand think of how to distinguish the resulting prefix
matches from suffix matches.  Maybe you can work that out yourself by
using the rows returned to figure it out.  Also note that this will
possibly interact poorly with the snippeting and offset functions.

As a short-term proof-of-concept hack, you could just have two tables.
 Insert your originals into one table, then take last_insert_rowid()
and insert the document reversed into the other table.

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

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Sam Roberts]

On Fri, Aug 6, 2010 at 6:11 AM, Adam DeVita  wrote:
> A variant on Simon's plan.
> Are the 10,000 rows static, slowly changing, or frequently changing?

Never change, it's read-only.

>  Does
> it make sense to pre-calculate some counts at the time data is loaded?
>  Is
> this memory constrained so much that you can't afford 1 or 2 MB to let you
> look up based on ints? (I'm assuming that one letter is all you are after,
> either 'starts with' or 'contains' and not in order combinations.)

No, substrings, it's just that I then need a count of matching
substrings by first char.

Good idea, there are a number of other queries where pre-calculating
is linear in the space cost, but here the the usage is interactive
search, where as they type more of the name, it narrows down the
search results as people type in more.

Pre-calculating would be about 40 factorial in space, there are about
64000 3-character strings, and then once  they typed the 4th char in
it would be slow again. Of course, not all of those exist. Hm. Maybe
I'll try to precalculate the suffix tree, and see how many results
there really are, I don't need to store zero results.

The fastest I've found so far is using FTS3. Its a little slow, but
not unusably so. There are only 2500 rows now, I hope that it will
scale well as the DB increases in size. I'm still considering other
approaches, maybe a custom b-tree.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Adam DeVita]

A variant on Simon's plan.
Are the 10,000 rows static, slowly changing, or frequently changing?   Does
it make sense to pre-calculate some counts at the time data is loaded?  Is
this memory constrained so much that you can't afford 1 or 2 MB to let you
look up based on ints? (I'm assuming that one letter is all you are after,
either 'starts with' or 'contains' and not in order combinations.)

Adam

On Thu, Aug 5, 2010 at 5:40 PM, Simon Slavin  wrote:

>
> On 5 Aug 2010, at 10:03pm, Sam Roberts wrote:
>
> > But do you think the section would make the counting faster? I think
> > I'd have to get the row counts like this, which would still do the
> > slow full table scan:
> >
> >  select section, count(*) from my_table where name like '%e%' group by
> section;
>
> But 'group by section' can profit from the index on the section column so
> it should be faster.
>
> As with all these things, the suggestion is to try it and see.  You should
> try six or seven different solutions including shuffling columns and indexes
> before you settle on the one that will be in your final code.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Simon Slavin]

On 5 Aug 2010, at 10:03pm, Sam Roberts wrote:

> But do you think the section would make the counting faster? I think
> I'd have to get the row counts like this, which would still do the
> slow full table scan:
> 
>  select section, count(*) from my_table where name like '%e%' group by 
> section;

But 'group by section' can profit from the index on the section column so it 
should be faster.

As with all these things, the suggestion is to try it and see.  You should try 
six or seven different solutions including shuffling columns and indexes before 
you settle on the one that will be in your final code.

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

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Sam Roberts]

On Thu, Aug 5, 2010 at 1:37 PM, Simon Slavin  wrote:
>
> On 5 Aug 2010, at 8:42pm, Sam Roberts wrote:
>
>> select substr(name,1,1), count(*) from my_table where name like '%e%'
>> group by substr(name,1,1);
>
> If you are constantly going to be using the first character of the name like 
> that, give it a column of its own with its own index.

That's a good idea. I think it would help a lot with row fetching if
section was it's own column:

  select * from my_table where section is "g" and name like "%e%"
order by name limit 1 offset 4;

But do you think the section would make the counting faster? I think
I'd have to get the row counts like this, which would still do the
slow full table scan:

  select section, count(*) from my_table where name like '%e%' group by section;

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

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Simon Slavin]

On 5 Aug 2010, at 8:42pm, Sam Roberts wrote:

> select substr(name,1,1), count(*) from my_table where name like '%e%'
> group by substr(name,1,1);

If you are constantly going to be using the first character of the name like 
that, give it a column of its own with its own index.

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

[sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

[Sam Roberts]

I'd appreciate any suggestions on good ways to do this, I'm neither an SQL or
sqlite expert, so I might be thinking about it all wrong.

I have something like a (read-only) address book/rolodex, with interactive
searching. As users type into the search box, I need to first know for each
section how many rows match the substring typed so far.  I only display the
rows that are visible on screen.

I have two queries:

(A) I count the rows in a letter group.

If they typed "e":

select substr(name,1,1), count(*) from my_table where name like '%e%'
group by substr(name,1,1);
A|94
B|118
C|131
...

This is too slow, ~3sec, with 2500 rows, and we want to have 1 rows.

Worse, when they type "es", the search is as slow after they type "s" as when
they typed "e", even though the "es" rows are a sub-set of the rows that
matched "e".

FTS3 only searches full terms/words by default, but I think if I built a custom
tokenizer that returned all the suffix trees for a name:

"fu bar" => [ "r", "ar", "bar", " bar", "u bar", "fu bar"]

That I could do rewrite query (A) like this:

select substr(name,1,1), count(*) from my_table where name match 'e*'
group by substr(name,1,1);

Is this a reasonable approach? Is there a better way? Has somebody
else done this?



(B) I access specific rows within a letter group.

For visible rows, I fetch them by offset into a letter group, so row 4 in the
"g" section of names containing "e" would be:

select * from my_table where name like "g%" and name like "%e%" order
by name limit 1 offset 4;

The performance for this is OK, right now, I think it's because the first LIKE
can use the index, so the linear scan is over only a few hundred rows. Or it
could be that the on-screen display of each row is slower than the DB search. I
think it might become a problem, though.

I'm not sure how I would rewrite this to use FTS3 if it turns out to be to slow
for a larger DB, maybe a tokenizer that puts the first letter of  the name as
the first letter of every suffix?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOT LIKE

[bartsmissaert]

Try this instead:
SELECT * FROM suppliers
WHERE (NOT supplier_name LIKE '%blabla%');

RBS


> Does anybody know wether the "NOT LIKE" condition such as
>
> SELECT * FROM suppliers
> WHERE supplier_name NOT LIKE '%blabla%';
>
> works in sqlite?
> I used it in a query but it resulted in inconsistent data.
>
> 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] NOT LIKE

[Daniele Liscia]

Does anybody know wether the "NOT LIKE" condition such as

SELECT * FROM suppliers
WHERE supplier_name NOT LIKE '%blabla%';

works in sqlite?
I used it in a query but it resulted in inconsistent data.

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

Re: [sqlite] Parenthetical LIKE query ??

[P Kishor]

On 1/4/09, Ben Marchbanks  wrote:
> SELECT * FROM `pagesText` WHERE  pageText LIKE ( "%muffler%" , "%clamp%" )
>
>  Is there a nice way to write a query using parenthetical LIKE ?

afaik, there is no option for using LIKE in the manner of IN (as you
are trying to do above). You can match for each pattern separately
using OR between each clause.

Also, use double quotes to quote the table or column names and single
quotes to delimit the pattern... so, you should use

SELECT * FROM "pagesText" WHERE pageText LIKE '%muffler%' OR pageText
LIKE '%clamp%';


>
>  Queries like this can get quite long otherwise
>
>  Any suggestions ?
>
>
>  *Ben Marchbanks*
>
>  www.magazooms.com 
>  Signature
>  Email: b...@magazooms.com 
>  Phone: (864) 284.9918
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parenthetical LIKE query ??

[Igor Tandetnik]

"Ben Marchbanks"  wrote in
message news:49615144.7090...@alqemy.com
> SELECT * FROM `pagesText` WHERE  pageText LIKE ( "%muffler%" ,
> "%clamp%" )
>
> Is there a nice way to write a query using parenthetical LIKE ?

SELECT * FROM pagesText
WHERE pageText LIKE '%muffler%'  OR pageText LIKE '%clamp%';

If you have a long list of patterns you want to compare against, insert 
them all into a temp table then do something like this:

SELECT * FROM pagesText WHERE EXISTS (
select 1 from patterns where pageText LIKE '%' || pattern || '%');

Igor Tandetnik 



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

[sqlite] Parenthetical LIKE query ??

[Ben Marchbanks]

SELECT * FROM `pagesText` WHERE  pageText LIKE ( "%muffler%" , "%clamp%" )

Is there a nice way to write a query using parenthetical LIKE ?

Queries like this can get quite long otherwise

Any suggestions ?


*Ben Marchbanks*

www.magazooms.com 
Signature
Email: b...@magazooms.com 
Phone: (864) 284.9918
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be

[Peter Jacobi]

I'm aware that ICU is able to provide a very general solution, but I'm
wondering about two other options:

(1) Just as an OS abstraction layer is in place for I/O, wouldn't it
be possible to use an OS abstraction layer for L14N? So that for
example uppercasing is forwarded to LCMapString(LCMAP_UPPERCASE) on
Win32. That would bring the Sqlite behaviour in line with the handling
in the application program itself (provided that it uses OS APIs and
not ICU).

(2) I'm under the impression, that the problematic cases (german
sharp-s, turkic i) are few compared with all the cases where a simple
lookup would things make work. If I'm not mistaken, a lookup table of
2048 entries handling all 2 byte UTF-8 characters would already cover
all the joint character repertoire of all ISO-8859-*  (and their MSFT
counterparts). Thai (in ISO 8859-11) is using three byte UTF-8 but
doesn't have upper/lower case.

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

Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be

[Thomas Mittelstaedt]

Thanks for that pointer to the icu project. Did not know about that!!

thomas

Am Freitag, den 14.11.2008, 15:27 +0200 schrieb Elefterios
Stamatogiannakis:
> Has anybody successfully compiled sqlite with icu for win32?
> 
> I haven't managed to find an libicu for mingw. Any tips welcome.
> 
> lefteris
> 
> D. Richard Hipp wrote:
> > On Nov 14, 2008, at 8:08 AM, Martin Engelschalk wrote:
> > 
> >> Hi all,
> >>
> >> the ICU project is a very powerful tool to handle codepages, and also
> >> supports regular expressions (using a class named "RegexMatcher", see
> >> http://icu-project.org/apiref/icu4c/classRegexMatcher.html).
> >> So, it should be relatively easy to replace the like() - function in
> >> sqlite (see http://www.sqlite.org/lang_corefunc.html#like and
> >> http://www.sqlite.org/c3ref/create_function.html)
> >>
> > 
> > http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt=1.2
> > 
> > D. Richard Hipp
> > [EMAIL PROTECTED]
> > 
> > 
> > 
> > ___
> > 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

Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be

[Elefterios Stamatogiannakis]

Has anybody successfully compiled sqlite with icu for win32?

I haven't managed to find an libicu for mingw. Any tips welcome.

lefteris

D. Richard Hipp wrote:
> On Nov 14, 2008, at 8:08 AM, Martin Engelschalk wrote:
> 
>> Hi all,
>>
>> the ICU project is a very powerful tool to handle codepages, and also
>> supports regular expressions (using a class named "RegexMatcher", see
>> http://icu-project.org/apiref/icu4c/classRegexMatcher.html).
>> So, it should be relatively easy to replace the like() - function in
>> sqlite (see http://www.sqlite.org/lang_corefunc.html#like and
>> http://www.sqlite.org/c3ref/create_function.html)
>>
> 
> http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt=1.2
> 
> D. Richard Hipp
> [EMAIL PROTECTED]
> 
> 
> 
> ___
> 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] bug? like-search with german umlaut is case-sensitive, should not be

[D. Richard Hipp]

On Nov 14, 2008, at 8:08 AM, Martin Engelschalk wrote:

> Hi all,
>
> the ICU project is a very powerful tool to handle codepages, and also
> supports regular expressions (using a class named "RegexMatcher", see
> http://icu-project.org/apiref/icu4c/classRegexMatcher.html).
> So, it should be relatively easy to replace the like() - function in
> sqlite (see http://www.sqlite.org/lang_corefunc.html#like and
> http://www.sqlite.org/c3ref/create_function.html)
>

http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt=1.2

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be

[Martin Engelschalk]

Hi all,

the ICU project is a very powerful tool to handle codepages, and also 
supports regular expressions (using a class named "RegexMatcher", see 
http://icu-project.org/apiref/icu4c/classRegexMatcher.html).
So, it should be relatively easy to replace the like() - function in 
sqlite (see http://www.sqlite.org/lang_corefunc.html#like and 
http://www.sqlite.org/c3ref/create_function.html)

Martin

Igor Tandetnik wrote:
> "Thomas Mittelstaedt"
> <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>   
>> Just did a search on my database using
>> SELECT * FROM ku2008 where "Empfaenger 1" like '%köck%';
>>
>> and nothing was found. Doing a SELECT * FROM ku2008 where "Empfaenger
>> 1" like '%kÖck%'; with the capital umlaut did find the record.
>> 
>
> http://sqlite.org/lang_expr.html
>
> "SQLite only understands upper/lower case for 7-bit Latin characters. 
> Hence the LIKE operator is case sensitive for 8-bit iso8859 characters 
> or UTF-8 characters. For example, the expression 'a' LIKE 'A' is TRUE 
> but 'æ' LIKE 'Æ' is FALSE."
>
> Apparently, it's possible to integrate SQLite with ICU 
> (http://icu-project.org/) to support properly localized collation and 
> case folding. I don't know the details, hopefully someone more 
> knowledgeable will chime in.
>
> Igor Tandetnik 
>
>
>
>   
> 
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

-- 

* Codeswift GmbH *
Traunstr. 30
A-5026 Salzburg-Aigen
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 12120 / 204645
[EMAIL PROTECTED]
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be

[Michael Schlenker]

Thomas Mittelstaedt schrieb:
> Hallo,
> 
> Just did a search on my database using 
> SELECT * FROM ku2008 where "Empfaenger 1" like '%köck%';
> 
> and nothing was found. Doing a SELECT * FROM ku2008 where "Empfaenger 1"
> like '%kÖck%'; with the capital umlaut did find the record. 
> The data is utf-8! my sqlite version is 3.5.9 on ubuntu hardy.
>
Documented bug, see the sqlite expressions documentation page which states:
http://www.sqlite.org/lang_expr.html

(A bug: SQLite only understands upper/lower case for 7-bit Latin characters.
Hence the LIKE operator is case sensitive for 8-bit iso8859 characters or
UTF-8 characters. For example, the expression 'a' LIKE 'A' is TRUE but 'æ'
LIKE 'Æ' is FALSE.).

But its hard to fix as you would need language information for the data to
get the upper/lower thing always correct (just think about the ß -> SS
anomaly in german).

Michael

-- 
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: [EMAIL PROTECTED]

Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be

[Martin Engelschalk]

Hello Thomas,

I have the same problem. There is no readily available function for 
converting utf-8 characters outside 7-bit-Ascii from lower to upper, so 
sqlite does not use one.
To achieve this, you have to write your own function and/or incorporate 
something like ICU into your project. I still have hte work before me.

Martin

Thomas Mittelstaedt wrote:
> Hallo,
>
> Just did a search on my database using 
> SELECT * FROM ku2008 where "Empfaenger 1" like '%köck%';
>
> and nothing was found. Doing a SELECT * FROM ku2008 where "Empfaenger 1"
> like '%kÖck%'; with the capital umlaut did find the record. 
> The data is utf-8! my sqlite version is 3.5.9 on ubuntu hardy.
>
> thomas
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   

-- 

* Codeswift GmbH *
Traunstr. 30
A-5026 Salzburg-Aigen
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 12120 / 204645
[EMAIL PROTECTED]
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

[sqlite] bug? like-search with german umlaut is case-sensitive, should not be

[Thomas Mittelstaedt]

Hallo,

Just did a search on my database using 
SELECT * FROM ku2008 where "Empfaenger 1" like '%köck%';

and nothing was found. Doing a SELECT * FROM ku2008 where "Empfaenger 1"
like '%kÖck%'; with the capital umlaut did find the record. 
The data is utf-8! my sqlite version is 3.5.9 on ubuntu hardy.

thomas


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

Re: [sqlite] NOT LIKE statement

[James]

The only wild cards affecting operation of the LIKE operator are '%' and
'_'.
So the SQLite doesn't support '[ ]' and '^'. Right?
Thank you.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Simon Davies
Sent: Wednesday, June 25, 2008 5:35 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] NOT LIKE statement

You seemed to be expecting that
name NOT LIKE 'InternetGatewayDevice.%.[1-9]' AND name NOT LIKE
'InternetGatewayDevice.%.[^1-9]
would exclude some of the rows you are getting.
This is incorrect. The only wild cards affecting operation of the LIKE
operator are '%' and '_'.

Regards,
Simon


2008/6/25 James <[EMAIL PROTECTED]>:
> Hi, Simon:
>Thanks for help me solve this problem.
>I have study the link you give me. But I still don't understand why
> my original SQL statement can't work. Could you explain in detail?
>Thank you.
>
> James
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Simon Davies
> Sent: Wednesday, June 25, 2008 4:40 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] NOT LIKE statement
>
> I can get your expected results from the data you have given, as shown
> below:
>
> sqlite> CREATE TABLE tst( name text );
> sqlite>
> sqlite> insert into tst values( 'InternetGatewayDevice.DeviceInfo.' );
> sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.1' );
> sqlite> insert into tst values(
> 'InternetGatewayDevice.DeviceInfo.AdditionHardwareVersion' );
> sqlite> insert into tst values(
> 'InternetGatewayDevice.DeviceInfo.SerialNumber' );
> sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.2' );
> sqlite> insert into tst values( 'InternetGatewayDevice.ManagementServer.'
);
> sqlite> insert into tst values(
'InternetGatewayDevice.ManagementServer.URL'
> );
> sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.3' );
> sqlite> insert into tst values(
> 'InternetGatewayDevice.ManagementServer.Username' );
> sqlite> insert into tst values(
> 'InternetGatewayDevice.ManagementServer.DownloadProgressURL' );
> sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.4' );
> sqlite>
> sqlite>
> sqlite> select name from tst where name like 'InternetGatewayDevice.%';
> InternetGatewayDevice.DeviceInfo.
> InternetGatewayDevice.DeviceInfo.AdditionHardwareVersion
> InternetGatewayDevice.DeviceInfo.SerialNumber
> InternetGatewayDevice.ManagementServer.
> InternetGatewayDevice.ManagementServer.URL
> InternetGatewayDevice.ManagementServer.Username
> InternetGatewayDevice.ManagementServer.DownloadProgressURL
> sqlite>
> sqlite>
> sqlite> select name from tst where name like 'InternetGatewayDevice.%'
> and name not like 'InternetGatewayDevice.%._%';
> InternetGatewayDevice.DeviceInfo.
> InternetGatewayDevice.ManagementServer.
> sqlite>
>
> Rgds,
> Simon
>
> 2008/6/25 James <[EMAIL PROTECTED]>:
>> Hi,
>> I will read that.
>> But I want to know that is it possible to get the expected result?
>> Thank you.
>>
>>
>> -Original Message-
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED] On Behalf Of Simon Davies
>> Sent: Wednesday, June 25, 2008 3:57 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] NOT LIKE statement
>>
>> Hi James,
>>
>> I think the problem lies with your expectations.
>>
>> Read the section on the LIKE operator in
>> http://www.sqlite.org/lang_expr.html
>>
>> Rgds,
>> Simon
>>
>> 2008/6/25 James <[EMAIL PROTECTED]>:
>>> Hi,
>>>
>>> I execute the SQL statement [SELECT Name FROM tr069;] and get the result
>>>
>>> InternetGatewayDevice.DeviceInfo.
>>>
>>> InternetGatewayDevice.DeviceInfo.AdditionHardwareVersion
>>>
>>> InternetGatewayDevice.DeviceInfo.AdditionalSoftwareVersion
>>>
>>> InternetGatewayDevice.DeviceInfo.Description
>>>
>>> InternetGatewayDevice.DeviceInfo.DeviceLog
>>>
>>> InternetGatewayDevice.DeviceInfo.DeviceStatus
>>>
>>> InternetGatewayDevice.DeviceInfo.EnabledOptions
>>>
>>> InternetGatewayDevice.DeviceInfo.FirstUseDate
>>>
>>> InternetGatewayDevice.DeviceInfo.HardwareVersion
>>>
>>> InternetGatewayDevice.DeviceInfo.SoftwareVersion
>>>
>>> InternetGatewayDevice.DeviceInfo.Manufacturer
>>>
>>> InternetGatewayDevice.DeviceInfo.ManufacturerOUI
>>>
>>> InternetGatewayDe

Re: [sqlite] NOT LIKE statement

[Igor Tandetnik]

"P Kishor" <[EMAIL PROTECTED]> wrote in
message
news:[EMAIL PROTECTED]
> You are expecting the LIKE (or NOT LIKE) operator to behave like a
> RegExp operator. No, it doesn't. It just has a single wildcard
> specifier, the % sign.

Two of them:  % (a sequence of zero or more of arbitrary characters) and 
_ (exactly one arbitrary character).

Igor Tandetnik 



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

Re: [sqlite] NOT LIKE statement

[P Kishor]

On 6/25/08, James <[EMAIL PROTECTED]> wrote:
> Hi, Simon:
> Thanks for help me solve this problem.
> I have study the link you give me. But I still don't understand why
>  my original SQL statement can't work. Could you explain in detail?

You are expecting the LIKE (or NOT LIKE) operator to behave like a
RegExp operator. No, it doesn't. It just has a single wildcard
specifier, the % sign.


> Thank you.
>
>
>  James
>
> -Original Message-
>  From: [EMAIL PROTECTED]
>  [mailto:[EMAIL PROTECTED] On Behalf Of Simon Davies
>
> Sent: Wednesday, June 25, 2008 4:40 PM
>  To: General Discussion of SQLite Database
>  Subject: Re: [sqlite] NOT LIKE statement
>
>  I can get your expected results from the data you have given, as shown
>  below:
>
>  sqlite> CREATE TABLE tst( name text );
>  sqlite>
>  sqlite> insert into tst values( 'InternetGatewayDevice.DeviceInfo.' );
>  sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.1' );
>  sqlite> insert into tst values(
>  'InternetGatewayDevice.DeviceInfo.AdditionHardwareVersion' );
>  sqlite> insert into tst values(
>  'InternetGatewayDevice.DeviceInfo.SerialNumber' );
>  sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.2' );
>  sqlite> insert into tst values( 'InternetGatewayDevice.ManagementServer.' );
>  sqlite> insert into tst values( 'InternetGatewayDevice.ManagementServer.URL'
>  );
>  sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.3' );
>  sqlite> insert into tst values(
>  'InternetGatewayDevice.ManagementServer.Username' );
>  sqlite> insert into tst values(
>  'InternetGatewayDevice.ManagementServer.DownloadProgressURL' );
>  sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.4' );
>  sqlite>
>  sqlite>
>  sqlite> select name from tst where name like 'InternetGatewayDevice.%';
>  InternetGatewayDevice.DeviceInfo.
>  InternetGatewayDevice.DeviceInfo.AdditionHardwareVersion
>  InternetGatewayDevice.DeviceInfo.SerialNumber
>  InternetGatewayDevice.ManagementServer.
>  InternetGatewayDevice.ManagementServer.URL
>  InternetGatewayDevice.ManagementServer.Username
>  InternetGatewayDevice.ManagementServer.DownloadProgressURL
>  sqlite>
>  sqlite>
>  sqlite> select name from tst where name like 'InternetGatewayDevice.%'
>  and name not like 'InternetGatewayDevice.%._%';
>  InternetGatewayDevice.DeviceInfo.
>  InternetGatewayDevice.ManagementServer.
>  sqlite>
>
>  Rgds,
>  Simon
>
>  2008/6/25 James <[EMAIL PROTECTED]>:
>  > Hi,
>  > I will read that.
>  > But I want to know that is it possible to get the expected result?
>  > Thank you.
>  >
>  >
>  > -Original Message-
>  > From: [EMAIL PROTECTED]
>  > [mailto:[EMAIL PROTECTED] On Behalf Of Simon Davies
>  > Sent: Wednesday, June 25, 2008 3:57 PM
>  > To: General Discussion of SQLite Database
>  > Subject: Re: [sqlite] NOT LIKE statement
>  >
>  > Hi James,
>  >
>  > I think the problem lies with your expectations.
>  >
>  > Read the section on the LIKE operator in
>  > http://www.sqlite.org/lang_expr.html
>  >
>  > Rgds,
>  > Simon
>  >
>  > 2008/6/25 James <[EMAIL PROTECTED]>:
>  >> Hi,
>  >>
>  >> I execute the SQL statement [SELECT Name FROM tr069;] and get the result
>  >>
>  >> InternetGatewayDevice.DeviceInfo.
>  >>
>  >> InternetGatewayDevice.DeviceInfo.AdditionHardwareVersion
>  >>
>  >> InternetGatewayDevice.DeviceInfo.AdditionalSoftwareVersion
>  >>
>  >> InternetGatewayDevice.DeviceInfo.Description
>  >>
>  >> InternetGatewayDevice.DeviceInfo.DeviceLog
>  >>
>  >> InternetGatewayDevice.DeviceInfo.DeviceStatus
>  >>
>  >> InternetGatewayDevice.DeviceInfo.EnabledOptions
>  >>
>  >> InternetGatewayDevice.DeviceInfo.FirstUseDate
>  >>
>  >> InternetGatewayDevice.DeviceInfo.HardwareVersion
>  >>
>  >> InternetGatewayDevice.DeviceInfo.SoftwareVersion
>  >>
>  >> InternetGatewayDevice.DeviceInfo.Manufacturer
>  >>
>  >> InternetGatewayDevice.DeviceInfo.ManufacturerOUI
>  >>
>  >> InternetGatewayDevice.DeviceInfo.ModelName
>  >>
>  >> InternetGatewayDevice.DeviceInfo.ProductClass
>  >>
>  >> InternetGatewayDevice.DeviceInfo.ProvisioningCode
>  >>
>  >> InternetGatewayDevice.DeviceInfo.SerialNumber
>  >>
>  >> InternetGatewayDevice.DeviceInfo.UpTi

Re: [sqlite] NOT LIKE statement

[Simon Davies]

You seemed to be expecting that
name NOT LIKE 'InternetGatewayDevice.%.[1-9]' AND name NOT LIKE
'InternetGatewayDevice.%.[^1-9]
would exclude some of the rows you are getting.
This is incorrect. The only wild cards affecting operation of the LIKE
operator are '%' and '_'.

Regards,
Simon


2008/6/25 James <[EMAIL PROTECTED]>:
> Hi, Simon:
>Thanks for help me solve this problem.
>I have study the link you give me. But I still don't understand why
> my original SQL statement can't work. Could you explain in detail?
>Thank you.
>
> James
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Simon Davies
> Sent: Wednesday, June 25, 2008 4:40 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] NOT LIKE statement
>
> I can get your expected results from the data you have given, as shown
> below:
>
> sqlite> CREATE TABLE tst( name text );
> sqlite>
> sqlite> insert into tst values( 'InternetGatewayDevice.DeviceInfo.' );
> sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.1' );
> sqlite> insert into tst values(
> 'InternetGatewayDevice.DeviceInfo.AdditionHardwareVersion' );
> sqlite> insert into tst values(
> 'InternetGatewayDevice.DeviceInfo.SerialNumber' );
> sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.2' );
> sqlite> insert into tst values( 'InternetGatewayDevice.ManagementServer.' );
> sqlite> insert into tst values( 'InternetGatewayDevice.ManagementServer.URL'
> );
> sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.3' );
> sqlite> insert into tst values(
> 'InternetGatewayDevice.ManagementServer.Username' );
> sqlite> insert into tst values(
> 'InternetGatewayDevice.ManagementServer.DownloadProgressURL' );
> sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.4' );
> sqlite>
> sqlite>
> sqlite> select name from tst where name like 'InternetGatewayDevice.%';
> InternetGatewayDevice.DeviceInfo.
> InternetGatewayDevice.DeviceInfo.AdditionHardwareVersion
> InternetGatewayDevice.DeviceInfo.SerialNumber
> InternetGatewayDevice.ManagementServer.
> InternetGatewayDevice.ManagementServer.URL
> InternetGatewayDevice.ManagementServer.Username
> InternetGatewayDevice.ManagementServer.DownloadProgressURL
> sqlite>
> sqlite>
> sqlite> select name from tst where name like 'InternetGatewayDevice.%'
> and name not like 'InternetGatewayDevice.%._%';
> InternetGatewayDevice.DeviceInfo.
> InternetGatewayDevice.ManagementServer.
> sqlite>
>
> Rgds,
> Simon
>
> 2008/6/25 James <[EMAIL PROTECTED]>:
>> Hi,
>> I will read that.
>> But I want to know that is it possible to get the expected result?
>> Thank you.
>>
>>
>> -Original Message-
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED] On Behalf Of Simon Davies
>> Sent: Wednesday, June 25, 2008 3:57 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] NOT LIKE statement
>>
>> Hi James,
>>
>> I think the problem lies with your expectations.
>>
>> Read the section on the LIKE operator in
>> http://www.sqlite.org/lang_expr.html
>>
>> Rgds,
>> Simon
>>
>> 2008/6/25 James <[EMAIL PROTECTED]>:
>>> Hi,
>>>
>>> I execute the SQL statement [SELECT Name FROM tr069;] and get the result
>>>
>>> InternetGatewayDevice.DeviceInfo.
>>>
>>> InternetGatewayDevice.DeviceInfo.AdditionHardwareVersion
>>>
>>> InternetGatewayDevice.DeviceInfo.AdditionalSoftwareVersion
>>>
>>> InternetGatewayDevice.DeviceInfo.Description
>>>
>>> InternetGatewayDevice.DeviceInfo.DeviceLog
>>>
>>> InternetGatewayDevice.DeviceInfo.DeviceStatus
>>>
>>> InternetGatewayDevice.DeviceInfo.EnabledOptions
>>>
>>> InternetGatewayDevice.DeviceInfo.FirstUseDate
>>>
>>> InternetGatewayDevice.DeviceInfo.HardwareVersion
>>>
>>> InternetGatewayDevice.DeviceInfo.SoftwareVersion
>>>
>>> InternetGatewayDevice.DeviceInfo.Manufacturer
>>>
>>> InternetGatewayDevice.DeviceInfo.ManufacturerOUI
>>>
>>> InternetGatewayDevice.DeviceInfo.ModelName
>>>
>>> InternetGatewayDevice.DeviceInfo.ProductClass
>>>
>>> InternetGatewayDevice.DeviceInfo.ProvisioningCode
>>>
>>> InternetGatewayDevice.DeviceInfo.SerialNumber
>>>
>>> InternetGatewayDevice.DeviceInfo.UpTime
>>>
>>> InternetGatewayDevice.ManagementServer.
>>>
&

Re: [sqlite] NOT LIKE statement

[James]

Hi, Simon:
Thanks for help me solve this problem.
I have study the link you give me. But I still don't understand why
my original SQL statement can't work. Could you explain in detail? 
Thank you.

James
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Simon Davies
Sent: Wednesday, June 25, 2008 4:40 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] NOT LIKE statement

I can get your expected results from the data you have given, as shown
below:

sqlite> CREATE TABLE tst( name text );
sqlite>
sqlite> insert into tst values( 'InternetGatewayDevice.DeviceInfo.' );
sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.1' );
sqlite> insert into tst values(
'InternetGatewayDevice.DeviceInfo.AdditionHardwareVersion' );
sqlite> insert into tst values(
'InternetGatewayDevice.DeviceInfo.SerialNumber' );
sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.2' );
sqlite> insert into tst values( 'InternetGatewayDevice.ManagementServer.' );
sqlite> insert into tst values( 'InternetGatewayDevice.ManagementServer.URL'
);
sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.3' );
sqlite> insert into tst values(
'InternetGatewayDevice.ManagementServer.Username' );
sqlite> insert into tst values(
'InternetGatewayDevice.ManagementServer.DownloadProgressURL' );
sqlite> insert into tst values( 'RubbishForTesting.DeviceInfo.4' );
sqlite>
sqlite>
sqlite> select name from tst where name like 'InternetGatewayDevice.%';
InternetGatewayDevice.DeviceInfo.
InternetGatewayDevice.DeviceInfo.AdditionHardwareVersion
InternetGatewayDevice.DeviceInfo.SerialNumber
InternetGatewayDevice.ManagementServer.
InternetGatewayDevice.ManagementServer.URL
InternetGatewayDevice.ManagementServer.Username
InternetGatewayDevice.ManagementServer.DownloadProgressURL
sqlite>
sqlite>
sqlite> select name from tst where name like 'InternetGatewayDevice.%'
and name not like 'InternetGatewayDevice.%._%';
InternetGatewayDevice.DeviceInfo.
InternetGatewayDevice.ManagementServer.
sqlite>

Rgds,
Simon

2008/6/25 James <[EMAIL PROTECTED]>:
> Hi,
> I will read that.
> But I want to know that is it possible to get the expected result?
> Thank you.
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Simon Davies
> Sent: Wednesday, June 25, 2008 3:57 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] NOT LIKE statement
>
> Hi James,
>
> I think the problem lies with your expectations.
>
> Read the section on the LIKE operator in
> http://www.sqlite.org/lang_expr.html
>
> Rgds,
> Simon
>
> 2008/6/25 James <[EMAIL PROTECTED]>:
>> Hi,
>>
>> I execute the SQL statement [SELECT Name FROM tr069;] and get the result
>>
>> InternetGatewayDevice.DeviceInfo.
>>
>> InternetGatewayDevice.DeviceInfo.AdditionHardwareVersion
>>
>> InternetGatewayDevice.DeviceInfo.AdditionalSoftwareVersion
>>
>> InternetGatewayDevice.DeviceInfo.Description
>>
>> InternetGatewayDevice.DeviceInfo.DeviceLog
>>
>> InternetGatewayDevice.DeviceInfo.DeviceStatus
>>
>> InternetGatewayDevice.DeviceInfo.EnabledOptions
>>
>> InternetGatewayDevice.DeviceInfo.FirstUseDate
>>
>> InternetGatewayDevice.DeviceInfo.HardwareVersion
>>
>> InternetGatewayDevice.DeviceInfo.SoftwareVersion
>>
>> InternetGatewayDevice.DeviceInfo.Manufacturer
>>
>> InternetGatewayDevice.DeviceInfo.ManufacturerOUI
>>
>> InternetGatewayDevice.DeviceInfo.ModelName
>>
>> InternetGatewayDevice.DeviceInfo.ProductClass
>>
>> InternetGatewayDevice.DeviceInfo.ProvisioningCode
>>
>> InternetGatewayDevice.DeviceInfo.SerialNumber
>>
>> InternetGatewayDevice.DeviceInfo.UpTime
>>
>> InternetGatewayDevice.ManagementServer.
>>
>> InternetGatewayDevice.ManagementServer.URL
>>
>> InternetGatewayDevice.ManagementServer.Username
>>
>> InternetGatewayDevice.ManagementServer.Password
>>
>> InternetGatewayDevice.ManagementServer.PeriodicInformEnable
>>
>> InternetGatewayDevice.ManagementServer.PeriodicInformInterval
>>
>> InternetGatewayDevice.ManagementServer.PeriodicInformTime
>>
>> InternetGatewayDevice.ManagementServer.ParameterKey
>>
>> InternetGatewayDevice.ManagementServer.ConnectionRequestURL
>>
>> InternetGatewayDevice.ManagementServer.ConnectionRequestUsername
>>
>> InternetGatewayDevice.ManagementServer.ConnectionRequestPassword
>>
>> InternetGatewayDevice.ManagementServer.UpgradesManaged
>>
>> InternetGatewayDevice.ManagementServer.KickURL
>>
>> InternetGate