Re: [sqlite] SQLite3 Tutorial error

[Ken Wagner]

Keith,

It appears that the folks at SQLiteTutorial.net have a coding anomaly.

They are not following the UNIX / SQLite3 GLOB patterns. And so, too, do 
the other guys.


I am adjusting my usage accordingly. I will advise users of the other 
products to NOT use the GLOB "*[^1-9]*" pattern. SQLiteTutorial.net has 
already been advised. (No reply, thus far in 2 days.)


Yeah, I guess it must be the Dim Sum!!

Many thanks,

Ken


On 01/05/2017 12:46 AM, Keith Medcalf wrote:

 From the (current) source code:

/*
** Compare two UTF-8 strings for equality where the first string is
** a GLOB or LIKE expression.  Return values:
**
**SQLITE_MATCH:Match
**SQLITE_NOMATCH:  No match
**SQLITE_NOWILDCARDMATCH:  No match in spite of having * or % wildcards.
**
** Globbing rules:
**
**  '*'   Matches any sequence of zero or more characters.
**
**  '?'   Matches exactly one character.
**
** [...]  Matches one character from the enclosed list of
**characters.
**
** [^...] Matches one character not in the enclosed list.
**
** With the [...] and [^...] matching, a ']' character can be included
** in the list by making it the first character after '[' or '^'.  A
** range of characters can be specified using '-'.  Example:
** "[a-z]" matches any single lower-case letter.  To match a '-', make
** it the last character in the list.
**
** Like matching rules:
**
**  '%'   Matches any sequence of zero or more characters
**
*** '_'   Matches any one character
**
**  EcWhere E is the "esc" character and c is any other
**character, including '%', '_', and esc, match exactly c.
**
** The comments within this routine usually assume glob matching.
**
** This routine is usually quick, but can be N**2 in the worst case.
*/


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of dandl
Sent: Wednesday, 4 January, 2017 23:18
To: 'SQLite mailing list'
Subject: Re: [sqlite] SQLite3 Tutorial error

A question: this appears to be the Posix standard for globbing ie Patterns
Used for Filename Expansion:
http://pubs.opengroup.org/onlinepubs/007908799/xcu/chap2.html#tag_001_013_
003

And this is Linux:
https://linux.die.net/man/7/glob

Is this what Sqlite intends to conform to? Because the convention here is
[!0-9] and not [^0-9].

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org


___
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 Tutorial error

[Ken Wagner]

Keith,

This has really helped.

SQLite Tutorial site is using: 3.12.12016-04-08 15:09:49 
fe7d3b75fe1bde41511b323925af8ae1b910bc4d


I get the same results as you did below. So, there is some sort of error 
or different code being used. It may have to do with encoding. I will 
test this further tomorrow.


Many thanks,

Ken


On 01/05/2017 12:29 AM, Keith Medcalf wrote:

sqlite3s < globtest.sql

select sqlite_version(), sqlite_source_id();
3.11.0|2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
create table x(x text);
insert into x values('ABC');
insert into x values('5BC');
insert into x values('A5C');
insert into x values('AB5');
insert into x values('1BC');
insert into x values('5B1');
insert into x values('A51');
insert into x values('1B5');
insert into x values('123');

select x from x where x glob '*[^1-9]*';
ABC
5BC
A5C
AB5
1BC
5B1
A51
1B5
select x from x where x glob '*[1-9]*';
5BC
A5C
AB5
1BC
5B1
A51
1B5
123
select x from x where not x glob '*[^1-9]*';
123
select x from x where not x glob '*[1-9]*';
ABC

The GLOB works exactly the same in all cases, including on 3.11.0.

The pattern *[1-9]* matches "any string containing at least 1 character 1 thru 
9"
The pattern *[^1-9]* matches "any string containing 1 character that is not 1 
thru 9 (that is, only strings composed entirely of the characters 1 thru 9 are 
included, those composed entirely of characters 1 thru 9 are excluded).


I do not understand the part where you say "also 3.15.0. Worked as in #1 above in 
both cases in the SQLite Tutorial, but oppositely in the SQLite3 CLI." because using 
python to call the sqlite3 also produces the same results as the CLI:


import apsw
import apswrow
dbr=apsw.Connection('globtest.db')

for row in db.cursor().execute("select x from x where x glob '*[^1-9]*';"):

...  print row
...
Row(x=u'ABC')
Row(x=u'5BC')
Row(x=u'A5C')
Row(x=u'AB5')
Row(x=u'1BC')
Row(x=u'5B1')
Row(x=u'A51')
Row(x=u'1B5')
This would suggest to me that either (a) the internal GLOB function was 
overridden or (b) that even though you thought you changed the underlying 
version of SQLite, you did not.  Did you select sqlite_version() and 
sqlite_source_id() to see what version of sqlite was being used?   In the 
Python above it is currently:


for row in cr.cursor().execute("select sqlite_version(), sqlite_source_id();"):

...  print row
...
Row(sqlite_version=u'3.17.0', sqlite_source_id=u'2017-01-04 04:18:00 
80ad317f89c46db0d0d252aefdc036a34a61183d')



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of Ken Wagner
Sent: Wednesday, 4 January, 2017 22:36
To: SQLite mailing list
Subject: Re: [sqlite] SQLite3 Tutorial error

Keith,

Thanks for the further explanation and the ll alias.

What is so hard to digest is that:

  1. SQLiteMan - a Linux GUI does the *[^1-9]* pattern and omits rows
with digits 1-9 in them. Using 3.11.0.
  2. DBBrowser - Linux GUI does the same using 3.9.2 and 3.11.0 in
different versions, one older.
  3. SQLite Tutorial @ www.sqlitetutorial.net - Has GLOB lesson page.
Uses version @ https://www.sqlite.org/download.html. I downloaded
sqlite-tools-linux-x86-3160100.zip
(1.77
MiB), also 3.15.0. Worked as in #1 above in both cases in the SQLite
Tutorial, but oppositely in the SQLite3 CLI.
  4. The FireFox 50 SQLite Manager add-on. This one is quite good.
Good layouts, easy to use and detailed. Works as in #1 above. Uses
SQLite 3.13.0.

Yet the SQLite3 CLI does the OPPOSITE and _includes_ any names with
digits 1-9 in them with vv 3.15.0 and 3.16.0.

Do you have any idea why? Is it really the dim sum??

This will have to be explained to my intended customers because some
will use the SQLite3 CLI. Most will use a GUI as they are not very
computer literate. I will be offering them training and also directing
them to training at a good web SQLite tutorial.

All of the above are using the 'chinook.db'. My system is Linux/Ubuntu
16.04, SQLite3 3.15.0 and 3.16.0 CLIs and the above programs. I use the
CLI in both the Terminator and Gnome-Terminal. Some minor differences
with encoding.


Ken


On 01/04/2017 10:49 PM, Keith Medcalf wrote:


Yes.  The GLOB was invented on Unix.  I posted an example of the Unix

filename globbing (which has not changed, to my knowledge, since the
60's), which works exactly the same as the GLOB operator in SQLite 3.9.0
through the current head of trunk.  Perhaps there were minor changes, but
nothing that affects the output of the *[1-9]* or *[^1-9]* patterns when
applied to the same data used in the Linux demonstration.  However, I did
not try and build every single version of SQLite between 3.9.0 to 3.17.0
to see if one of them happened to be broken.  The two ends and a sampling
from the middle all worked the same.

And by the way, GLOB predates REGEX by about 15 years.  REGEX borrowed

(and modified) GLOB syntax.

(in case you have never used

Re: [sqlite] SQLite3 Tutorial error

[Keith Medcalf]

From the (current) source code:

/*
** Compare two UTF-8 strings for equality where the first string is
** a GLOB or LIKE expression.  Return values:
**
**SQLITE_MATCH:Match
**SQLITE_NOMATCH:  No match
**SQLITE_NOWILDCARDMATCH:  No match in spite of having * or % wildcards.
**
** Globbing rules:
**
**  '*'   Matches any sequence of zero or more characters.
**
**  '?'   Matches exactly one character.
**
** [...]  Matches one character from the enclosed list of
**characters.
**
** [^...] Matches one character not in the enclosed list.
**
** With the [...] and [^...] matching, a ']' character can be included
** in the list by making it the first character after '[' or '^'.  A
** range of characters can be specified using '-'.  Example:
** "[a-z]" matches any single lower-case letter.  To match a '-', make
** it the last character in the list.
**
** Like matching rules:
**
**  '%'   Matches any sequence of zero or more characters
**
*** '_'   Matches any one character
**
**  EcWhere E is the "esc" character and c is any other
**character, including '%', '_', and esc, match exactly c.
**
** The comments within this routine usually assume glob matching.
**
** This routine is usually quick, but can be N**2 in the worst case.
*/

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of dandl
> Sent: Wednesday, 4 January, 2017 23:18
> To: 'SQLite mailing list'
> Subject: Re: [sqlite] SQLite3 Tutorial error
> 
> A question: this appears to be the Posix standard for globbing ie Patterns
> Used for Filename Expansion:
> http://pubs.opengroup.org/onlinepubs/007908799/xcu/chap2.html#tag_001_013_
> 003
> 
> And this is Linux:
> https://linux.die.net/man/7/glob
> 
> Is this what Sqlite intends to conform to? Because the convention here is
> [!0-9] and not [^0-9].
> 
> Regards
> David M Bennett FACS
> 
> Andl - A New Database Language - andl.org
> 
> 
> ___
> 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] SQLite3 Tutorial error

[Keith Medcalf]

>sqlite3s < globtest.sql
select sqlite_version(), sqlite_source_id();
3.11.0|2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
create table x(x text);
insert into x values('ABC');
insert into x values('5BC');
insert into x values('A5C');
insert into x values('AB5');
insert into x values('1BC');
insert into x values('5B1');
insert into x values('A51');
insert into x values('1B5');
insert into x values('123');

select x from x where x glob '*[^1-9]*';
ABC
5BC
A5C
AB5
1BC
5B1
A51
1B5
select x from x where x glob '*[1-9]*';
5BC
A5C
AB5
1BC
5B1
A51
1B5
123
select x from x where not x glob '*[^1-9]*';
123
select x from x where not x glob '*[1-9]*';
ABC

The GLOB works exactly the same in all cases, including on 3.11.0.  

The pattern *[1-9]* matches "any string containing at least 1 character 1 thru 
9"
The pattern *[^1-9]* matches "any string containing 1 character that is not 1 
thru 9 (that is, only strings composed entirely of the characters 1 thru 9 are 
included, those composed entirely of characters 1 thru 9 are excluded).


I do not understand the part where you say "also 3.15.0. Worked as in #1 above 
in both cases in the SQLite Tutorial, but oppositely in the SQLite3 CLI." 
because using python to call the sqlite3 also produces the same results as the 
CLI:

>>> import apsw
>>> import apswrow
>>> dbr=apsw.Connection('globtest.db')
 for row in db.cursor().execute("select x from x where x glob '*[^1-9]*';"):
...  print row
...
Row(x=u'ABC')
Row(x=u'5BC')
Row(x=u'A5C')
Row(x=u'AB5')
Row(x=u'1BC')
Row(x=u'5B1')
Row(x=u'A51')
Row(x=u'1B5')
>

This would suggest to me that either (a) the internal GLOB function was 
overridden or (b) that even though you thought you changed the underlying 
version of SQLite, you did not.  Did you select sqlite_version() and 
sqlite_source_id() to see what version of sqlite was being used?   In the 
Python above it is currently:

>>> for row in cr.cursor().execute("select sqlite_version(), 
>>> sqlite_source_id();"):
...  print row
...
Row(sqlite_version=u'3.17.0', sqlite_source_id=u'2017-01-04 04:18:00 
80ad317f89c46db0d0d252aefdc036a34a61183d')
>



> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Ken Wagner
> Sent: Wednesday, 4 January, 2017 22:36
> To: SQLite mailing list
> Subject: Re: [sqlite] SQLite3 Tutorial error
> 
> Keith,
> 
> Thanks for the further explanation and the ll alias.
> 
> What is so hard to digest is that:
> 
>  1. SQLiteMan - a Linux GUI does the *[^1-9]* pattern and omits rows
> with digits 1-9 in them. Using 3.11.0.
>  2. DBBrowser - Linux GUI does the same using 3.9.2 and 3.11.0 in
> different versions, one older.
>  3. SQLite Tutorial @ www.sqlitetutorial.net - Has GLOB lesson page.
> Uses version @ https://www.sqlite.org/download.html. I downloaded
> sqlite-tools-linux-x86-3160100.zip
> (1.77
> MiB), also 3.15.0. Worked as in #1 above in both cases in the SQLite
> Tutorial, but oppositely in the SQLite3 CLI.
>  4. The FireFox 50 SQLite Manager add-on. This one is quite good.
> Good layouts, easy to use and detailed. Works as in #1 above. Uses
> SQLite 3.13.0.
> 
> Yet the SQLite3 CLI does the OPPOSITE and _includes_ any names with
> digits 1-9 in them with vv 3.15.0 and 3.16.0.
> 
> Do you have any idea why? Is it really the dim sum??
> 
> This will have to be explained to my intended customers because some
> will use the SQLite3 CLI. Most will use a GUI as they are not very
> computer literate. I will be offering them training and also directing
> them to training at a good web SQLite tutorial.
> 
> All of the above are using the 'chinook.db'. My system is Linux/Ubuntu
> 16.04, SQLite3 3.15.0 and 3.16.0 CLIs and the above programs. I use the
> CLI in both the Terminator and Gnome-Terminal. Some minor differences
> with encoding.
> 
> 
> Ken
> 
> 
> On 01/04/2017 10:49 PM, Keith Medcalf wrote:
> 
> > Yes.  The GLOB was invented on Unix.  I posted an example of the Unix
> filename globbing (which has not changed, to my knowledge, since the
> 60's), which works exactly the same as the GLOB operator in SQLite 3.9.0
> through the current head of trunk.  Perhaps there were minor changes, but
> nothing that affects the output of the *[1-9]* or *[^1-9]* patterns when
> applied to the same data used in the Linux demonstration.  However, I did
> not try and build every single version of SQLite between 3.9.0 to 3.17.0
> to see if one of them happened to be broken.  The two ends and a sampling
> from the middle all worked the same.
> >
> > And by the way, GLOB predates REGEX by about 15 years.  REGEX borrowed
> (and modified) GLOB syntax.
> >
> > (in case you have never used a Linux/Unix system with an ll command
> alias, the command to create it is:  alias ll='ls -l')
> >
> > Are you ABSOLUTELY SURE that the authors of the third-party tools have
> not provided their own GLOB function that works 

Re: [sqlite] SQLite3 Tutorial error

[dandl]

A question: this appears to be the Posix standard for globbing ie Patterns Used 
for Filename Expansion:
http://pubs.opengroup.org/onlinepubs/007908799/xcu/chap2.html#tag_001_013_003

And this is Linux:
https://linux.die.net/man/7/glob

Is this what Sqlite intends to conform to? Because the convention here is 
[!0-9] and not [^0-9].

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org


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

Re: [sqlite] SQLite3 Tutorial error

[Michael Falconer]

UmSnobol?

Nope absolutely deny all knowledge. Well.actually, didn't cut any code
in it but did hear about Spitbol. No I kid you not, sort of a super Snobol
I was led to believe. I was cutting Cobol at the time, eons ago. :-) A
colleague told me about it and I took a look but not a close one.



On 5 January 2017 at 16:35, Ken Wagner  wrote:

> Keith,
>
> Thanks for the further explanation and the ll alias.
>
> What is so hard to digest is that:
>
> 1. SQLiteMan - a Linux GUI does the *[^1-9]* pattern and omits rows
> with digits 1-9 in them. Using 3.11.0.
> 2. DBBrowser - Linux GUI does the same using 3.9.2 and 3.11.0 in
> different versions, one older.
> 3. SQLite Tutorial @ www.sqlitetutorial.net - Has GLOB lesson page.
> Uses version @ https://www.sqlite.org/download.html. I downloaded
> sqlite-tools-linux-x86-3160100.zip  qlite-tools-linux-x86-3160100.zip>(1.77 MiB), also 3.15.0. Worked as in
> #1 above in both cases in the SQLite Tutorial, but oppositely in the
> SQLite3 CLI.
> 4. The FireFox 50 SQLite Manager add-on. This one is quite good. Good
> layouts, easy to use and detailed. Works as in #1 above. Uses SQLite 3.13.0.
>
> Yet the SQLite3 CLI does the OPPOSITE and _includes_ any names with digits
> 1-9 in them with vv 3.15.0 and 3.16.0.
>
> Do you have any idea why? Is it really the dim sum??
>
> This will have to be explained to my intended customers because some will
> use the SQLite3 CLI. Most will use a GUI as they are not very computer
> literate. I will be offering them training and also directing them to
> training at a good web SQLite tutorial.
>
> All of the above are using the 'chinook.db'. My system is Linux/Ubuntu
> 16.04, SQLite3 3.15.0 and 3.16.0 CLIs and the above programs. I use the CLI
> in both the Terminator and Gnome-Terminal. Some minor differences with
> encoding.
>
>
> Ken
>
>
>
> On 01/04/2017 10:49 PM, Keith Medcalf wrote:
>
> Yes.  The GLOB was invented on Unix.  I posted an example of the Unix
>> filename globbing (which has not changed, to my knowledge, since the 60's),
>> which works exactly the same as the GLOB operator in SQLite 3.9.0 through
>> the current head of trunk.  Perhaps there were minor changes, but nothing
>> that affects the output of the *[1-9]* or *[^1-9]* patterns when applied to
>> the same data used in the Linux demonstration.  However, I did not try and
>> build every single version of SQLite between 3.9.0 to 3.17.0 to see if one
>> of them happened to be broken.  The two ends and a sampling from the middle
>> all worked the same.
>>
>> And by the way, GLOB predates REGEX by about 15 years.  REGEX borrowed
>> (and modified) GLOB syntax.
>>
>> (in case you have never used a Linux/Unix system with an ll command
>> alias, the command to create it is:  alias ll='ls -l')
>>
>> Are you ABSOLUTELY SURE that the authors of the third-party tools have
>> not provided their own GLOB function that works differently, perhaps in
>> accordance with their Dim Sum because their little hearts did not desire
>> the built in one?
>>
>> -Original Message-
>>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>>> On Behalf Of Ken Wagner
>>> Sent: Wednesday, 4 January, 2017 21:24
>>> To: SQLite mailing list
>>> Subject: Re: [sqlite] SQLite3 Tutorial error
>>>
>>> Aha! GLOB is an implementation of the UNIX Glob function. It just
>>> borrows the regex character notation of [^1-9].
>>>
>>> I have 3.15 and 3.16 CLIs installed. Trying any other CLI versions at
>>> this point won't help for the customer. They will be using a SQLite3
>>> GUI. I will explain the difference between the CLI and the GUI versions.
>>>
>>> Where is this UNIX Glob notation spelled out? My system is Linux. I
>>> expect the UNIX version will be somewhat different.
>>>
>>> The Linux GLOB is used to find patterns in pathnames (not databases.) Is
>>> the Unix version the same?
>>>
>>> Thanks,
>>>
>>> Ken
>>>
>>> On 01/04/2017 11:51 AM, R Smith wrote:
>>>

 On 2017/01/04 7:01 PM, Jens Alfke wrote:

> On Jan 4, 2017, at 5:57 AM, R Smith  wrote:
>>
>> As I have it (and as is implemented by SQLite) the GLOB operator
>> implements a REGEXP that matches against a regexp pattern
>>
> No, these are NOT regular expressions in the usual sense of the word.
> GLOB's syntax is incompatible with what are commonly called “regular
> expressions”, and its feature set is a lot more limited. (It may
> technically implement a type of regular expression in the underlying
> algorithmic sense, but I think using the term is misleading.)
>
 Quite correct, I meant REGEXP as an internal function of the
 Regular-expression type, not the official "regular expression" syntax
 - So a "misleading term" then in your words. Allow me to be more clear
 then: GLOB in SQLite specifically matches Unix file globbing syntax
 (which is very different to official RegEx). 3rd party utili

Re: [sqlite] SQLite3 Tutorial error

[Ken Wagner]

Keith,

Thanks for the further explanation and the ll alias.

What is so hard to digest is that:

1. SQLiteMan - a Linux GUI does the *[^1-9]* pattern and omits rows 
with digits 1-9 in them. Using 3.11.0.
2. DBBrowser - Linux GUI does the same using 3.9.2 and 3.11.0 in 
different versions, one older.
3. SQLite Tutorial @ www.sqlitetutorial.net - Has GLOB lesson page. 
Uses version @ https://www.sqlite.org/download.html. I downloaded 
sqlite-tools-linux-x86-3160100.zip 
(1.77 
MiB), also 3.15.0. Worked as in #1 above in both cases in the SQLite 
Tutorial, but oppositely in the SQLite3 CLI.
4. The FireFox 50 SQLite Manager add-on. This one is quite good. 
Good layouts, easy to use and detailed. Works as in #1 above. Uses 
SQLite 3.13.0.


Yet the SQLite3 CLI does the OPPOSITE and _includes_ any names with 
digits 1-9 in them with vv 3.15.0 and 3.16.0.


Do you have any idea why? Is it really the dim sum??

This will have to be explained to my intended customers because some 
will use the SQLite3 CLI. Most will use a GUI as they are not very 
computer literate. I will be offering them training and also directing 
them to training at a good web SQLite tutorial.


All of the above are using the 'chinook.db'. My system is Linux/Ubuntu 
16.04, SQLite3 3.15.0 and 3.16.0 CLIs and the above programs. I use the 
CLI in both the Terminator and Gnome-Terminal. Some minor differences 
with encoding.



Ken


On 01/04/2017 10:49 PM, Keith Medcalf wrote:


Yes.  The GLOB was invented on Unix.  I posted an example of the Unix filename 
globbing (which has not changed, to my knowledge, since the 60's), which works 
exactly the same as the GLOB operator in SQLite 3.9.0 through the current head 
of trunk.  Perhaps there were minor changes, but nothing that affects the 
output of the *[1-9]* or *[^1-9]* patterns when applied to the same data used 
in the Linux demonstration.  However, I did not try and build every single 
version of SQLite between 3.9.0 to 3.17.0 to see if one of them happened to be 
broken.  The two ends and a sampling from the middle all worked the same.

And by the way, GLOB predates REGEX by about 15 years.  REGEX borrowed (and 
modified) GLOB syntax.

(in case you have never used a Linux/Unix system with an ll command alias, the 
command to create it is:  alias ll='ls -l')

Are you ABSOLUTELY SURE that the authors of the third-party tools have not 
provided their own GLOB function that works differently, perhaps in accordance 
with their Dim Sum because their little hearts did not desire the built in one?


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of Ken Wagner
Sent: Wednesday, 4 January, 2017 21:24
To: SQLite mailing list
Subject: Re: [sqlite] SQLite3 Tutorial error

Aha! GLOB is an implementation of the UNIX Glob function. It just
borrows the regex character notation of [^1-9].

I have 3.15 and 3.16 CLIs installed. Trying any other CLI versions at
this point won't help for the customer. They will be using a SQLite3
GUI. I will explain the difference between the CLI and the GUI versions.

Where is this UNIX Glob notation spelled out? My system is Linux. I
expect the UNIX version will be somewhat different.

The Linux GLOB is used to find patterns in pathnames (not databases.) Is
the Unix version the same?

Thanks,

Ken

On 01/04/2017 11:51 AM, R Smith wrote:


On 2017/01/04 7:01 PM, Jens Alfke wrote:

On Jan 4, 2017, at 5:57 AM, R Smith  wrote:

As I have it (and as is implemented by SQLite) the GLOB operator
implements a REGEXP that matches against a regexp pattern

No, these are NOT regular expressions in the usual sense of the word.
GLOB's syntax is incompatible with what are commonly called “regular
expressions”, and its feature set is a lot more limited. (It may
technically implement a type of regular expression in the underlying
algorithmic sense, but I think using the term is misleading.)

Quite correct, I meant REGEXP as an internal function of the
Regular-expression type, not the official "regular expression" syntax
- So a "misleading term" then in your words. Allow me to be more clear
then: GLOB in SQLite specifically matches Unix file globbing syntax
(which is very different to official RegEx). 3rd party utilities can
override both the internal REGEXP and GLOB functions with custom
versions.
https://sqlite.org/lang_expr.html#glob

The bit I don't know for sure is whether Unix file globbing will
regard 'AB5' as matching '*[^1-9]*' or not? I know in SQLite it
matches (and I believe this to be correct, but I could be mistaken and
I don't have a Unix box handy to test).

Either way, the concern is more towards consistency than specific
operation.
The SQLite scripts I sent previously will reveal any difference
between versions if they exist. Have you tried it on different
versions of the CLI?


___
sqlite-u

Re: [sqlite] SQLite3 Tutorial error

[dandl]

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Keith Medcalf

>>>Yes.  The GLOB was invented on Unix.  I posted an example of the Unix 
>>>filename globbing (which has not changed, to my knowledge, since the 60's), 
>>>which works exactly the same as the GLOB operator in SQLite 3.9.0 through 
>>>the current head of trunk.  Perhaps there were minor changes, but nothing 
>>>that affects the output of the *[1-9]* or *[^1-9]* patterns when applied to 
>>>the same data used in the Linux demonstration.  However, I did not try and 
>>>build every single version of SQLite between 3.9.0 to 3.17.0 to see if one 
>>>of them happened to be broken.  The two ends and a sampling from the middle 
>>>all worked the same.

I believe file system globbing originated on Unix in around 1969. It was not 
then thought to bear any particular relationship to regular expressions AFAIK.

>>>And by the way, GLOB predates REGEX by about 15 years.  REGEX borrowed (and 
>>>modified) GLOB syntax.

I believe the computer science underlying regex dates from work by Kleene in 
1956. I don't have the paper, but my impression is that it had marked 
similarities to modern usage. There were competing ideas in early languages 
(anyone remember Snobol?) but Thompson provided an implementation of Kleene's 
regex in the Unix text editor 'ed' in around 1969, based on even earlier work 
at IBM. Using regex in compilers (like lex) came later.

AFAIK glob and regex appeared in Unix at more or less the same time ie very 
early, but they were always distinct.

>>>(in case you have never used a Linux/Unix system with an ll command alias, 
>>>the command to create it is:  alias ll='ls -l')

>>>Are you ABSOLUTELY SURE that the authors of the third-party tools have not 
>>>provided their own GLOB function that works differently, perhaps in 
>>>accordance with their Dim Sum because their little hearts did not desire the 
>>>built in one?

I would be very disappointed to find that someone was implementing regex and 
calling it glob. That would be a mistake. Glob is glob, and Sqlite has it right 
IMHO.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org





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

Re: [sqlite] SQLite3 Tutorial error

[Keith Medcalf]

Yes.  The GLOB was invented on Unix.  I posted an example of the Unix filename 
globbing (which has not changed, to my knowledge, since the 60's), which works 
exactly the same as the GLOB operator in SQLite 3.9.0 through the current head 
of trunk.  Perhaps there were minor changes, but nothing that affects the 
output of the *[1-9]* or *[^1-9]* patterns when applied to the same data used 
in the Linux demonstration.  However, I did not try and build every single 
version of SQLite between 3.9.0 to 3.17.0 to see if one of them happened to be 
broken.  The two ends and a sampling from the middle all worked the same.

And by the way, GLOB predates REGEX by about 15 years.  REGEX borrowed (and 
modified) GLOB syntax.

(in case you have never used a Linux/Unix system with an ll command alias, the 
command to create it is:  alias ll='ls -l')

Are you ABSOLUTELY SURE that the authors of the third-party tools have not 
provided their own GLOB function that works differently, perhaps in accordance 
with their Dim Sum because their little hearts did not desire the built in one?

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Ken Wagner
> Sent: Wednesday, 4 January, 2017 21:24
> To: SQLite mailing list
> Subject: Re: [sqlite] SQLite3 Tutorial error
> 
> Aha! GLOB is an implementation of the UNIX Glob function. It just
> borrows the regex character notation of [^1-9].
> 
> I have 3.15 and 3.16 CLIs installed. Trying any other CLI versions at
> this point won't help for the customer. They will be using a SQLite3
> GUI. I will explain the difference between the CLI and the GUI versions.
> 
> Where is this UNIX Glob notation spelled out? My system is Linux. I
> expect the UNIX version will be somewhat different.
> 
> The Linux GLOB is used to find patterns in pathnames (not databases.) Is
> the Unix version the same?
> 
> Thanks,
> 
> Ken
> 
> On 01/04/2017 11:51 AM, R Smith wrote:
> >
> >
> > On 2017/01/04 7:01 PM, Jens Alfke wrote:
> >>> On Jan 4, 2017, at 5:57 AM, R Smith  wrote:
> >>>
> >>> As I have it (and as is implemented by SQLite) the GLOB operator
> >>> implements a REGEXP that matches against a regexp pattern
> >> No, these are NOT regular expressions in the usual sense of the word.
> >> GLOB's syntax is incompatible with what are commonly called “regular
> >> expressions”, and its feature set is a lot more limited. (It may
> >> technically implement a type of regular expression in the underlying
> >> algorithmic sense, but I think using the term is misleading.)
> >
> > Quite correct, I meant REGEXP as an internal function of the
> > Regular-expression type, not the official "regular expression" syntax
> > - So a "misleading term" then in your words. Allow me to be more clear
> > then: GLOB in SQLite specifically matches Unix file globbing syntax
> > (which is very different to official RegEx). 3rd party utilities can
> > override both the internal REGEXP and GLOB functions with custom
> > versions.
> > https://sqlite.org/lang_expr.html#glob
> >
> > The bit I don't know for sure is whether Unix file globbing will
> > regard 'AB5' as matching '*[^1-9]*' or not? I know in SQLite it
> > matches (and I believe this to be correct, but I could be mistaken and
> > I don't have a Unix box handy to test).
> >
> > Either way, the concern is more towards consistency than specific
> > operation.
> > The SQLite scripts I sent previously will reveal any difference
> > between versions if they exist. Have you tried it on different
> > versions of the CLI?
> >
> >
> > ___
> > 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 Tutorial error

[Ken Wagner]

Aha! GLOB is an implementation of the UNIX Glob function. It just 
borrows the regex character notation of [^1-9].


I have 3.15 and 3.16 CLIs installed. Trying any other CLI versions at 
this point won't help for the customer. They will be using a SQLite3 
GUI. I will explain the difference between the CLI and the GUI versions.


Where is this UNIX Glob notation spelled out? My system is Linux. I 
expect the UNIX version will be somewhat different.


The Linux GLOB is used to find patterns in pathnames (not databases.) Is 
the Unix version the same?


Thanks,

Ken

On 01/04/2017 11:51 AM, R Smith wrote:



On 2017/01/04 7:01 PM, Jens Alfke wrote:

On Jan 4, 2017, at 5:57 AM, R Smith  wrote:

As I have it (and as is implemented by SQLite) the GLOB operator 
implements a REGEXP that matches against a regexp pattern
No, these are NOT regular expressions in the usual sense of the word. 
GLOB's syntax is incompatible with what are commonly called “regular 
expressions”, and its feature set is a lot more limited. (It may 
technically implement a type of regular expression in the underlying 
algorithmic sense, but I think using the term is misleading.)


Quite correct, I meant REGEXP as an internal function of the 
Regular-expression type, not the official "regular expression" syntax 
- So a "misleading term" then in your words. Allow me to be more clear 
then: GLOB in SQLite specifically matches Unix file globbing syntax 
(which is very different to official RegEx). 3rd party utilities can 
override both the internal REGEXP and GLOB functions with custom 
versions.

https://sqlite.org/lang_expr.html#glob

The bit I don't know for sure is whether Unix file globbing will 
regard 'AB5' as matching '*[^1-9]*' or not? I know in SQLite it 
matches (and I believe this to be correct, but I could be mistaken and 
I don't have a Unix box handy to test).


Either way, the concern is more towards consistency than specific 
operation.
The SQLite scripts I sent previously will reveal any difference 
between versions if they exist. Have you tried it on different 
versions of the CLI?



___
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] SQLite3 Tutorial error

[Ken Wagner]

Yes, I am beginning to understand that. SQLite3 is its own GLOB standard.

I will abide by that.

It is just very confusing when 5 other apps using SQLite as their DB 
engine all report the opposite.

The SQLite versions they use are 3.9.2, 3.10.1, 3.11.0 and 3.13.0.

Example: the SQLite Manager in FireFox 50 uses SQLite 3.13.0 and 
faithfully removes the names with digits in them.


I am developing an app for contractors which will include a SQLite3 GUI 
so they can browse their files at will. The CLI is not viable for them 
as they are not very computer literate. They do grasp the 'Select * from 
parts where ;' syntax.


And there's the rub: GLOB "*[^1-9]*" works in the GUI tools that use 
SQLite but not in the SQLite CLI.


This introduces an additional support issue because some users will 
migrate to the CLI.


If you go to www.sqlitetutorial.net they, too, use and also teach the 
GLOB "*[^1-9]*" usage.


My issue is: Why the discrepancy. The other 5 apps all honor the GLOB 
"*[^1-9]*" usage. Only SQLite3 CLI doesn't. If this was deprecated it 
would be understandable. But, no such proviso is shown.


Do you not think that's a bit odd?

Rest assured, I will abide by the SQLite 3.15.0 and future upgrades. 
It's just extra work and support, places a blot on the escutcheon of 
efficiency. It's awkward mixing the GLOB and Regex metaphors.


Ken


On 01/04/2017 11:01 AM, Jens Alfke wrote:

On Jan 4, 2017, at 5:57 AM, R Smith  wrote:

As I have it (and as is implemented by SQLite) the GLOB operator implements a 
REGEXP that matches against a regexp pattern

No, these are NOT regular expressions in the usual sense of the word. GLOB's 
syntax is incompatible with what are commonly called “regular expressions”, and 
its feature set is a lot more limited. (It may technically implement a type of 
regular expression in the underlying algorithmic sense, but I think using the 
term is misleading.)

Case in point: the string "*[^1-9]*" is illegal in every regex syntax I know 
of, because “*” is a postfix operator in regex and can’t appear at the start of the 
string.

Thanks to Dr. Hipp for quoting the exact definition. It looks like this is 
basically the same syntax as the Unix glob(3) function, which is familiar to 
anyone who’s used a Unix shell.

—Jens
___
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] Hiring Senior Full-Stack Software Engineer w/ SQLite experience (NYC)

[Vince Scafaria]

DotAlign recently closed a $3.2 million round after blue chip client 
validation, and we now seek to rapidly grow beyond our founding team. We seek 
full stack engineers who are passionate about end user experience and 
workflows, who also crave the opportunity to unlock value from data, and who 
want to design and ship exciting functionality. Our stack makes very heavy use 
of SQLite to power some interesting Big Data use cases, and so posting here 
seemed logical. Compensation is competitive - cash and equity. The job would be 
in NYC.

http://dotalign.com/careers

Vince Scafaria, CEO
DotAlign, Inc.
+1.212.706.8630 W
vi...@dotalign.com

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

Re: [sqlite] SQLite3 Tutorial error

[R Smith]

On 2017/01/04 9:14 PM, Keith Medcalf wrote:

~/test# uname -a
Linux raspberrypi 4.9.0-v7+ #939 SMP Thu Dec 15 18:07:48 GMT 2016 armv7l 
GNU/Linux
~/test# echo . > 5AB
~/test# echo . > A5B
~/test# echo . > AB5
~/test# echo . > 5
~/test# echo . > ABC
~/test# ll *[1-9]*
-rw-r--r-- 1 root root 2 Jan  4 12:11 5
-rw-r--r-- 1 root root 2 Jan  4 12:10 5AB
-rw-r--r-- 1 root root 2 Jan  4 12:10 A5B
-rw-r--r-- 1 root root 2 Jan  4 12:11 AB5
~/test# ll *[^1-9]*
-rw-r--r-- 1 root root 2 Jan  4 12:10 5AB
-rw-r--r-- 1 root root 2 Jan  4 12:10 A5B
-rw-r--r-- 1 root root 2 Jan  4 12:11 AB5
-rw-r--r-- 1 root root 2 Jan  4 12:11 ABC


Thank you Keith - this matches how SQLite does it and how I expected it 
to work - so that is solved.


All that remains is to see whether there ever were versions of SQLite 
(in between 3.9 and 3.16) that didn't work like this.


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

Re: [sqlite] SQLite3 Tutorial error

[Keith Medcalf]

~/test# uname -a
Linux raspberrypi 4.9.0-v7+ #939 SMP Thu Dec 15 18:07:48 GMT 2016 armv7l 
GNU/Linux
~/test# echo . > 5AB
~/test# echo . > A5B
~/test# echo . > AB5
~/test# echo . > 5
~/test# echo . > ABC
~/test# ll *[1-9]*
-rw-r--r-- 1 root root 2 Jan  4 12:11 5
-rw-r--r-- 1 root root 2 Jan  4 12:10 5AB
-rw-r--r-- 1 root root 2 Jan  4 12:10 A5B
-rw-r--r-- 1 root root 2 Jan  4 12:11 AB5
~/test# ll *[^1-9]*
-rw-r--r-- 1 root root 2 Jan  4 12:10 5AB
-rw-r--r-- 1 root root 2 Jan  4 12:10 A5B
-rw-r--r-- 1 root root 2 Jan  4 12:11 AB5
-rw-r--r-- 1 root root 2 Jan  4 12:11 ABC

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of R Smith
> Sent: Wednesday, 4 January, 2017 10:52
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] SQLite3 Tutorial error
> 
> 
> 
> On 2017/01/04 7:01 PM, Jens Alfke wrote:
> >> On Jan 4, 2017, at 5:57 AM, R Smith  wrote:
> >>
> >> As I have it (and as is implemented by SQLite) the GLOB operator
> implements a REGEXP that matches against a regexp pattern
> > No, these are NOT regular expressions in the usual sense of the word.
> GLOB's syntax is incompatible with what are commonly called “regular
> expressions”, and its feature set is a lot more limited. (It may
> technically implement a type of regular expression in the underlying
> algorithmic sense, but I think using the term is misleading.)
> 
> Quite correct, I meant REGEXP as an internal function of the
> Regular-expression type, not the official "regular expression" syntax -
> So a "misleading term" then in your words. Allow me to be more clear
> then: GLOB in SQLite specifically matches Unix file globbing syntax
> (which is very different to official RegEx). 3rd party utilities can
> override both the internal REGEXP and GLOB functions with custom versions.
> https://sqlite.org/lang_expr.html#glob
> 
> The bit I don't know for sure is whether Unix file globbing will regard
> 'AB5' as matching '*[^1-9]*' or not? I know in SQLite it matches (and I
> believe this to be correct, but I could be mistaken and I don't have a
> Unix box handy to test).
> 
> Either way, the concern is more towards consistency than specific
> operation.
> The SQLite scripts I sent previously will reveal any difference between
> versions if they exist. Have you tried it on different versions of the
> CLI?
> 
> 
> ___
> 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] SQLite3 Tutorial error

[R Smith]

Apologies Jens, the final paragraphs in this reply where I used "you" 
were intended to the OP (Ken Wagner) and not yourself, of course.



On 2017/01/04 7:51 PM, R Smith wrote:



On 2017/01/04 7:01 PM, Jens Alfke wrote:

On Jan 4, 2017, at 5:57 AM, R Smith  wrote:

As I have it (and as is implemented by SQLite) the GLOB operator 
implements a REGEXP that matches against a regexp pattern
No, these are NOT regular expressions in the usual sense of the word. 
GLOB's syntax is incompatible with what are commonly called “regular 
expressions”, and its feature set is a lot more limited. (It may 
technically implement a type of regular expression in the underlying 
algorithmic sense, but I think using the term is misleading.)


Quite correct, I meant REGEXP as an internal function of the 
Regular-expression type, not the official "regular expression" syntax 
- So a "misleading term" then in your words. Allow me to be more clear 
then: GLOB in SQLite specifically matches Unix file globbing syntax 
(which is very different to official RegEx). 3rd party utilities can 
override both the internal REGEXP and GLOB functions with custom 
versions.

https://sqlite.org/lang_expr.html#glob

The bit I don't know for sure is whether Unix file globbing will 
regard 'AB5' as matching '*[^1-9]*' or not? I know in SQLite it 
matches (and I believe this to be correct, but I could be mistaken and 
I don't have a Unix box handy to test).


Either way, the concern is more towards consistency than specific 
operation.
The SQLite scripts I sent previously will reveal any difference 
between versions if they exist. Have you tried it on different 
versions of the CLI?


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

Re: [sqlite] SQLite3 Tutorial error

[R Smith]

On 2017/01/04 7:01 PM, Jens Alfke wrote:

On Jan 4, 2017, at 5:57 AM, R Smith  wrote:

As I have it (and as is implemented by SQLite) the GLOB operator implements a 
REGEXP that matches against a regexp pattern

No, these are NOT regular expressions in the usual sense of the word. GLOB's 
syntax is incompatible with what are commonly called “regular expressions”, and 
its feature set is a lot more limited. (It may technically implement a type of 
regular expression in the underlying algorithmic sense, but I think using the 
term is misleading.)


Quite correct, I meant REGEXP as an internal function of the 
Regular-expression type, not the official "regular expression" syntax - 
So a "misleading term" then in your words. Allow me to be more clear 
then: GLOB in SQLite specifically matches Unix file globbing syntax 
(which is very different to official RegEx). 3rd party utilities can 
override both the internal REGEXP and GLOB functions with custom versions.

https://sqlite.org/lang_expr.html#glob

The bit I don't know for sure is whether Unix file globbing will regard 
'AB5' as matching '*[^1-9]*' or not? I know in SQLite it matches (and I 
believe this to be correct, but I could be mistaken and I don't have a 
Unix box handy to test).


Either way, the concern is more towards consistency than specific operation.
The SQLite scripts I sent previously will reveal any difference between 
versions if they exist. Have you tried it on different versions of the CLI?



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

Re: [sqlite] SQLite3 Tutorial error

[Jens Alfke]

> On Jan 4, 2017, at 5:57 AM, R Smith  wrote:
> 
> As I have it (and as is implemented by SQLite) the GLOB operator implements a 
> REGEXP that matches against a regexp pattern

No, these are NOT regular expressions in the usual sense of the word. GLOB's 
syntax is incompatible with what are commonly called “regular expressions”, and 
its feature set is a lot more limited. (It may technically implement a type of 
regular expression in the underlying algorithmic sense, but I think using the 
term is misleading.)

Case in point: the string "*[^1-9]*" is illegal in every regex syntax I know 
of, because “*” is a postfix operator in regex and can’t appear at the start of 
the string.

Thanks to Dr. Hipp for quoting the exact definition. It looks like this is 
basically the same syntax as the Unix glob(3) function, which is familiar to 
anyone who’s used a Unix shell.

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

Re: [sqlite] SQLite3 Tutorial error

[R Smith]

On 2017/01/04 4:11 PM, Ken Wagner wrote:

Simon,

Yes, I am using the SQLite3 CLI. That's how I discovered the different 
GLOB outcomes.


I now understand that the SQLite3 GLOB function is not fully 
documented so it's best to just experiment with the GLOB function at 
the CLI for SQLite3. And avoid using SQLite3 versions prior to 3.15.


It's doable. Just adds extra work requiring checking.


Again, as far as SQLite is concerned, I see no difference between 
version 3.15/3.15 and 3.9 (did not check earlier versions). Can you give 
an example of where different versions of SQLite (not DB Browser or any 
other 3rd party tools) differ in its implementation? i.e. example 
Database, Query and SQLite CLI versions which produces different answers 
- because if this is really true, you may have stumbled on a bug. (You 
say it worked differently in 3.11 at the very least?)


Here is a script that can be run, and below it I include the results of 
how that script runs in SQLitespeed using SQLite version 3.16 (which 
runs the same for SQLite 3.9, but I don't have a 3.11 DLL handy). Maybe 
you can run it in 3.11 or whatever other one you think isn't working 
(CLI that is) and post the result - Thanks.


CREATE TABLE t(a);

INSERT INTO t (a) VALUES
(''),
('5'),
('5AB'),
('A5B'),
('AB5');

SELECT rowid, * FROM t WHERE a GLOB '*[0-9]*';

SELECT rowid, * FROM t WHERE a GLOB '*[^0-9]*';

SELECT rowid, * FROM t WHERE a NOT GLOB '*[0-9]*';

DROP TABLE t;



Script result:

  -- SQLite version 3.16.1  [ Release: 2017-01-03 ]  on SQLitespeed 
version 2.0.2.4.


  -- Script Items: 6  Parameter Count: 0
  -- 2017-01-04 16:27:57.181  |  [Info]   Script Initialized, 
Started executing...
  -- 



CREATE TABLE t(a);

INSERT INTO t (a) VALUES
(''),
('5'),
('5AB'),
('A5B'),
('AB5');

SELECT rowid, * FROM t WHERE a GLOB '*[0-9]*';


  -- rowid|   a
  --  | -
  --   2  |   5
  --   3  |  5AB
  --   4  |  A5B
  --   5  |  AB5

SELECT rowid, * FROM t WHERE a GLOB '*[^0-9]*';


  -- rowid|   a
  --  | -
  --   3  |  5AB
  --   4  |  A5B
  --   5  |  AB5

SELECT rowid, * FROM t WHERE a NOT GLOB '*[0-9]*';


  -- rowid|  a
  --  | ---
  --   1  |

DROP TABLE t;

  --   Script Stats: Total Script Execution Time: 0d 00h 00m and 
00.016s
  -- Total Script Query Time: -- --- --- --- 
--.

  -- Total Database Rows Changed: 5
  -- Total Virtual-Machine Steps: 319
  -- Last executed Item Index:6
  -- Last Script Error:
  -- 







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

Re: [sqlite] SQLite3 Tutorial error

[Ken Wagner]

Richard,

Why, thank you, for your very prompt reply and resolution for this 'nit'.

If I can be of any assistance in testing (or taxing) the system, please 
do inform me.


You have a jewel of a product and I enjoy using it immensely. Thanks for 
all the perseverance and patience.


You must have a considerable reservoir of both. Much appreciated.

Thank you for the artifact link. Very helpful.

I shall hold out for much smoother 3.16.1 rollout. My deepest empathies.

Best regards, Ken

On 01/04/2017 08:38 AM, Richard Hipp wrote:

On 1/4/17, Simon Slavin  wrote:

On 4 Jan 2017, at 1:43pm, Ken Wagner  wrote:


There is yet another product "DB Browser for SQLite" using SQLite v 3.9.2.
It, too, omits any row where name contains any char 1 thru 9. It appears
SQLite at one point did this as 'GLOB '*[^1-9]*'.

But it does not do so now. Does SQLite3 provide a detailed syntax
description of the GLOB permutations honored (and, perhaps, those
deprecated?)

No.  Not only is there no documentation for GLOB ...

Ugh.  Ok, I will fix that.  Meanwhile, you have
https://www.sqlite.org/src/artifact/c67273e1ec08abbd?ln=618-633

FWIW: The 3.16.1 release is not going well.  A potentially serious
problem has been discovered by Firefox.  And NDS also reports a lesser
concern.  So I am busy with other things right now, and I don't really
have time to deal with GLOB documentation right this moment.  Y'all
seem to be doing a good job of working this out.  Please continue...


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

Re: [sqlite] SQLite3 Tutorial error

[Richard Hipp]

On 1/4/17, Simon Slavin  wrote:
>
> On 4 Jan 2017, at 1:43pm, Ken Wagner  wrote:
>
>> There is yet another product "DB Browser for SQLite" using SQLite v 3.9.2.
>> It, too, omits any row where name contains any char 1 thru 9. It appears
>> SQLite at one point did this as 'GLOB '*[^1-9]*'.
>>
>> But it does not do so now. Does SQLite3 provide a detailed syntax
>> description of the GLOB permutations honored (and, perhaps, those
>> deprecated?)
>
> No.  Not only is there no documentation for GLOB ...

Ugh.  Ok, I will fix that.  Meanwhile, you have
https://www.sqlite.org/src/artifact/c67273e1ec08abbd?ln=618-633

FWIW: The 3.16.1 release is not going well.  A potentially serious
problem has been discovered by Firefox.  And NDS also reports a lesser
concern.  So I am busy with other things right now, and I don't really
have time to deal with GLOB documentation right this moment.  Y'all
seem to be doing a good job of working this out.  Please continue...
-- 
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] SQLite3 Tutorial error

[Ken Wagner]

Ryan,

Both 'AB6' or '5AB' fail the '[^1-9]' test. So, too do 'New Vol 4' and 
'#1'.


Ken


On 01/04/2017 07:57 AM, R Smith wrote:



On 2017/01/04 3:43 PM, Ken Wagner wrote:
Yes, I changed the query to NOT GLOB '*[1-9]*' and then it omitted 
the 1-9 char-containing entries.


However the logic of 'zero or any chars, then any single char NOT 1 
thru 9, then zero or any chars' should OMIT any name with a 1 thru 9 
in it regardless if it is 'Vol. 3', for example, or 'Vol. 33' because 
as soon as any single 1 thru 9 char is encountered the expression is 
false, i.e., it contains at least one char of 1 thru 9.



This is contrary to my understanding of the GLOB phrase (and I could 
well be wrong about it). As I have it (and as is implemented by 
SQLite) the GLOB operator implements a REGEXP that matches against a 
regexp pattern such that the program '*[^1-9]*' expanded means:

  * : ANY or none characters,
 Followed by
  [ : A character which is -
^ : NOT
1-9 : A character between 1 and 9
  ]
Followed by
  * : ANY or none characters

In English one can simply ask: "Does it have zero or more characters 
of any kind, followed by something that isn't a number 1 through 9, 
and then again zero or more characters of any kind?"


This answer seems to me to be YES (i.e. MATCH) all of the following:
'A', 'ABC', 'AB6' or '5AB'
but not match:
'', '1979' or '5'

Sure, one can construe the value '5AB' to mean Any characters () + 5 + 
Any characters (AB) to cause a non-match, but one can also construe 
the term '5AB' to mean Any characters (5) + NOT a number (A) + Any 
characters (B) and so it will match. Wildcards are tricky, and 
wildcards implemented in the negative are even worse.


I personally agree with how it is done in off-the-shelf SQLite (though 
my opinion is not important, what does the standard say? Is there a 
standard?). If something else has a different implementation it is 
very easy for any 3rd party item to override the GLOB and REGEXP 
functions to a custom implementation, which is probably what you are 
seeing.





There is yet another product "DB Browser for SQLite" using SQLite v 
3.9.2. It, too, omits any row where name contains any char 1 thru 9. 
It appears SQLite at one point did this as 'GLOB '*[^1-9]*'.


But it does not do so now. Does SQLite3 provide a detailed syntax 
description of the GLOB permutations honored (and, perhaps, those 
deprecated?)


Because it may or may not override the GLOB and/or REGEXP functions.

SQLite claims to use the INFIX implementation of LIKE and GLOB 
natively. Perhaps that is a starting point for research?


Hope that helps!
Ryan

___
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] SQLite3 Tutorial error

[Ken Wagner]

Ryan,

The Regex description of '[^0-9]' states NOT any 0 thru 9 char in any 
SINGLE char position. It can be amended to 1-9 or 3-7 or 1-4 as the user 
sees fit.


Tested it using Ruby and Rubular, a Regex Tester.

HTH,

Ken


On 01/04/2017 07:57 AM, R Smith wrote:



On 2017/01/04 3:43 PM, Ken Wagner wrote:
Yes, I changed the query to NOT GLOB '*[1-9]*' and then it omitted 
the 1-9 char-containing entries.


However the logic of 'zero or any chars, then any single char NOT 1 
thru 9, then zero or any chars' should OMIT any name with a 1 thru 9 
in it regardless if it is 'Vol. 3', for example, or 'Vol. 33' because 
as soon as any single 1 thru 9 char is encountered the expression is 
false, i.e., it contains at least one char of 1 thru 9.



This is contrary to my understanding of the GLOB phrase (and I could 
well be wrong about it). As I have it (and as is implemented by 
SQLite) the GLOB operator implements a REGEXP that matches against a 
regexp pattern such that the program '*[^1-9]*' expanded means:

  * : ANY or none characters,
 Followed by
  [ : A character which is -
^ : NOT
1-9 : A character between 1 and 9
  ]
Followed by
  * : ANY or none characters

In English one can simply ask: "Does it have zero or more characters 
of any kind, followed by something that isn't a number 1 through 9, 
and then again zero or more characters of any kind?"


This answer seems to me to be YES (i.e. MATCH) all of the following:
'A', 'ABC', 'AB6' or '5AB'
but not match:
'', '1979' or '5'

Sure, one can construe the value '5AB' to mean Any characters () + 5 + 
Any characters (AB) to cause a non-match, but one can also construe 
the term '5AB' to mean Any characters (5) + NOT a number (A) + Any 
characters (B) and so it will match. Wildcards are tricky, and 
wildcards implemented in the negative are even worse.


I personally agree with how it is done in off-the-shelf SQLite (though 
my opinion is not important, what does the standard say? Is there a 
standard?). If something else has a different implementation it is 
very easy for any 3rd party item to override the GLOB and REGEXP 
functions to a custom implementation, which is probably what you are 
seeing.





There is yet another product "DB Browser for SQLite" using SQLite v 
3.9.2. It, too, omits any row where name contains any char 1 thru 9. 
It appears SQLite at one point did this as 'GLOB '*[^1-9]*'.


But it does not do so now. Does SQLite3 provide a detailed syntax 
description of the GLOB permutations honored (and, perhaps, those 
deprecated?)


Because it may or may not override the GLOB and/or REGEXP functions.

SQLite claims to use the INFIX implementation of LIKE and GLOB 
natively. Perhaps that is a starting point for research?


Hope that helps!
Ryan

___
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] SQLite3 Tutorial error

[Ken Wagner]

Ryan,

I will use the SQLite3 CLI as the acid test.

But I still don't grasp why v 3.11.0 honors the GLOB by omitting any row 
where name contains a 1 thru 9 char but 3.15.0 and 3.16.0 do not.


I expect I have missed a deprecation. It's just confusing and extra 
nit-picking and testing.


Thanks for the info.

Ken


On 01/04/2017 07:57 AM, R Smith wrote:



On 2017/01/04 3:43 PM, Ken Wagner wrote:
Yes, I changed the query to NOT GLOB '*[1-9]*' and then it omitted 
the 1-9 char-containing entries.


However the logic of 'zero or any chars, then any single char NOT 1 
thru 9, then zero or any chars' should OMIT any name with a 1 thru 9 
in it regardless if it is 'Vol. 3', for example, or 'Vol. 33' because 
as soon as any single 1 thru 9 char is encountered the expression is 
false, i.e., it contains at least one char of 1 thru 9.



This is contrary to my understanding of the GLOB phrase (and I could 
well be wrong about it). As I have it (and as is implemented by 
SQLite) the GLOB operator implements a REGEXP that matches against a 
regexp pattern such that the program '*[^1-9]*' expanded means:

  * : ANY or none characters,
 Followed by
  [ : A character which is -
^ : NOT
1-9 : A character between 1 and 9
  ]
Followed by
  * : ANY or none characters

In English one can simply ask: "Does it have zero or more characters 
of any kind, followed by something that isn't a number 1 through 9, 
and then again zero or more characters of any kind?"


This answer seems to me to be YES (i.e. MATCH) all of the following:
'A', 'ABC', 'AB6' or '5AB'
but not match:
'', '1979' or '5'

Sure, one can construe the value '5AB' to mean Any characters () + 5 + 
Any characters (AB) to cause a non-match, but one can also construe 
the term '5AB' to mean Any characters (5) + NOT a number (A) + Any 
characters (B) and so it will match. Wildcards are tricky, and 
wildcards implemented in the negative are even worse.


I personally agree with how it is done in off-the-shelf SQLite (though 
my opinion is not important, what does the standard say? Is there a 
standard?). If something else has a different implementation it is 
very easy for any 3rd party item to override the GLOB and REGEXP 
functions to a custom implementation, which is probably what you are 
seeing.





There is yet another product "DB Browser for SQLite" using SQLite v 
3.9.2. It, too, omits any row where name contains any char 1 thru 9. 
It appears SQLite at one point did this as 'GLOB '*[^1-9]*'.


But it does not do so now. Does SQLite3 provide a detailed syntax 
description of the GLOB permutations honored (and, perhaps, those 
deprecated?)


Because it may or may not override the GLOB and/or REGEXP functions.

SQLite claims to use the INFIX implementation of LIKE and GLOB 
natively. Perhaps that is a starting point for research?


Hope that helps!
Ryan

___
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] SQLite3 Tutorial error

[Ken Wagner]

Simon,

Yes, I am using the SQLite3 CLI. That's how I discovered the different 
GLOB outcomes.


I now understand that the SQLite3 GLOB function is not fully documented 
so it's best to just experiment with the GLOB function at the CLI for 
SQLite3. And avoid using SQLite3 versions prior to 3.15.


It's doable. Just adds extra work requiring checking.

Thanks,

Ken

On 01/04/2017 07:54 AM, Simon Slavin wrote:

On 4 Jan 2017, at 1:43pm, Ken Wagner  wrote:


There is yet another product "DB Browser for SQLite" using SQLite v 3.9.2. It, 
too, omits any row where name contains any char 1 thru 9. It appears SQLite at one point 
did this as 'GLOB '*[^1-9]*'.

But it does not do so now. Does SQLite3 provide a detailed syntax description 
of the GLOB permutations honored (and, perhaps, those deprecated?)

No.  Not only is there no documentation for GLOB but a programmer can replace 
SQLite’s GLOB function with their own one, using the external function 
interface.  You should not produce important production code which relies on 
the implementation of GLOB unless you control every link in the programming 
chain.  And if you’re using a 3rd Party browser then you are obviously not 
doing that.

If you want to test how SQLite itself handles GLOB, please use the SQLIte 
command-line tool.  This can be downloaded as one of the 'precompiled binaries' 
for your platform, and is documented here:



This tool is written and supported by the SQLite development team and conforms 
in all ways to how SQLite is meant to be used.  If you find a bug in that tool, 
it will be fixed.

If you have problems with any other program which uses SQLite you are going to 
get better results by contacting whoever developed that program.

Simon.
___
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] SQLite3 Tutorial error

[Ken Wagner]

Ryan,

FWIW -- I upgraded the DB Browser from v. 3.9 to v 3.11.0. This too 
honors the GLOB '*[^1-9]*'  by omitting any entry with a 1 thru 9 in it 
in any char position.


This sqlitebrower is on git hub. It's a nice SQLite tool. (So far...)

Ken


On 01/04/2017 07:13 AM, R Smith wrote:
Just one more point of clarity, in case my previous explanation did 
not high-light this:


The query:
select trackid, name from tracks where name GLOB '*[^1-9]*';

is completely different to the query:
select trackid, name from tracks where name NOT GLOB '*[1-9]*';

The prior excludes only names which are completely non-zero-containing 
integers (such as "1979"), the latter excludes any value that contains 
a character between 1 and 9 at any position (such as "Opus No. 1" or 
"20 Flight Rock"). The two queries merely intersect in one specific 
point in the Chinook DB (at "1979", that is) but they have nothing 
else in common.


Cheers,
Ryan


On 2017/01/04 2:53 PM, R Smith wrote:



On 2017/01/04 7:17 AM, Ken Wagner wrote:

About 2/3 the way down the page at:

http://www.sqlitetutorial.net/sqlite-glob/   Get names without 
[1-9].


select trackid, name from tracks where name GLOB '*[^1-9]*';

Works properly in SQLiteMan and the SQLite Tutorial. ( Two 
different products, not officially part of sqlite.org, I think, but 
use the sqlite app.)


But not in sqlite3 3.15.1 and 3.16.1.



That should not "work" in ANY version of SQLite (depending on what 
you mean by "work"). The phrase '*[^1-9]*' essentially asks to match 
'Anything, followed by something that is NOT a character 1 through 9, 
followed by Anything again' which is really ANYTHING that isn't 
specifically an integer which doesn't contain zeroes. i.e. it will 
exclude a name like "1996" but not a name like "2016" or "123.456", 
so in the Chinook database (which the tutorial uses) the query will 
list ALL thousands of names except one (namely a song titled: "1979") 
so it would be hard for you to differentiate between the query that 
"works" and one that doesn't.


Perhaps suggest to the Tutorial creators to use phrases like 
'*[^0-9]*' in stead of 1-9 to start with, and then maybe an example 
that has more than one single exclusion across the entire DB (which 
make the results seem superfluous and not very educational).


Another confusing bit is your claim that it "works" in an earlier 
version and not in the newer versions - I get the same results in 3.9 
and 3.16.

May I ask how it differs for you?


___
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 Tutorial error

[R Smith]

On 2017/01/04 3:43 PM, Ken Wagner wrote:
Yes, I changed the query to NOT GLOB '*[1-9]*' and then it omitted the 
1-9 char-containing entries.


However the logic of 'zero or any chars, then any single char NOT 1 
thru 9, then zero or any chars' should OMIT any name with a 1 thru 9 
in it regardless if it is 'Vol. 3', for example, or 'Vol. 33' because 
as soon as any single 1 thru 9 char is encountered the expression is 
false, i.e., it contains at least one char of 1 thru 9.



This is contrary to my understanding of the GLOB phrase (and I could 
well be wrong about it). As I have it (and as is implemented by SQLite) 
the GLOB operator implements a REGEXP that matches against a regexp 
pattern such that the program '*[^1-9]*' expanded means:

  * : ANY or none characters,
 Followed by
  [ : A character which is -
^ : NOT
1-9 : A character between 1 and 9
  ]
Followed by
  * : ANY or none characters

In English one can simply ask: "Does it have zero or more characters of 
any kind, followed by something that isn't a number 1 through 9, and 
then again zero or more characters of any kind?"


This answer seems to me to be YES (i.e. MATCH) all of the following:
'A', 'ABC', 'AB6' or '5AB'
but not match:
'', '1979' or '5'

Sure, one can construe the value '5AB' to mean Any characters () + 5 + 
Any characters (AB) to cause a non-match, but one can also construe the 
term '5AB' to mean Any characters (5) + NOT a number (A) + Any 
characters (B) and so it will match. Wildcards are tricky, and wildcards 
implemented in the negative are even worse.


I personally agree with how it is done in off-the-shelf SQLite (though 
my opinion is not important, what does the standard say? Is there a 
standard?). If something else has a different implementation it is very 
easy for any 3rd party item to override the GLOB and REGEXP functions to 
a custom implementation, which is probably what you are seeing.





There is yet another product "DB Browser for SQLite" using SQLite v 
3.9.2. It, too, omits any row where name contains any char 1 thru 9. 
It appears SQLite at one point did this as 'GLOB '*[^1-9]*'.


But it does not do so now. Does SQLite3 provide a detailed syntax 
description of the GLOB permutations honored (and, perhaps, those 
deprecated?)


Because it may or may not override the GLOB and/or REGEXP functions.

SQLite claims to use the INFIX implementation of LIKE and GLOB natively. 
Perhaps that is a starting point for research?


Hope that helps!
Ryan

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

Re: [sqlite] SQLite3 Tutorial error

[Simon Slavin]

On 4 Jan 2017, at 1:43pm, Ken Wagner  wrote:

> There is yet another product "DB Browser for SQLite" using SQLite v 3.9.2. 
> It, too, omits any row where name contains any char 1 thru 9. It appears 
> SQLite at one point did this as 'GLOB '*[^1-9]*'.
> 
> But it does not do so now. Does SQLite3 provide a detailed syntax description 
> of the GLOB permutations honored (and, perhaps, those deprecated?)

No.  Not only is there no documentation for GLOB but a programmer can replace 
SQLite’s GLOB function with their own one, using the external function 
interface.  You should not produce important production code which relies on 
the implementation of GLOB unless you control every link in the programming 
chain.  And if you’re using a 3rd Party browser then you are obviously not 
doing that.

If you want to test how SQLite itself handles GLOB, please use the SQLIte 
command-line tool.  This can be downloaded as one of the 'precompiled binaries' 
for your platform, and is documented here:



This tool is written and supported by the SQLite development team and conforms 
in all ways to how SQLite is meant to be used.  If you find a bug in that tool, 
it will be fixed.

If you have problems with any other program which uses SQLite you are going to 
get better results by contacting whoever developed that program.

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

Re: [sqlite] SQLite3 Tutorial error

[Ken Wagner]

Yes, I changed the query to NOT GLOB '*[1-9]*' and then it omitted the 
1-9 char-containing entries.


However the logic of 'zero or any chars, then any single char NOT 1 thru 
9, then zero or any chars' should OMIT any name with a 1 thru 9 in it 
regardless if it is 'Vol. 3', for example, or 'Vol. 33' because as soon 
as any single 1 thru 9 char is encountered the expression is false, 
i.e., it contains at least one char of 1 thru 9.


There is yet another product "DB Browser for SQLite" using SQLite v 
3.9.2. It, too, omits any row where name contains any char 1 thru 9. It 
appears SQLite at one point did this as 'GLOB '*[^1-9]*'.


But it does not do so now. Does SQLite3 provide a detailed syntax 
description of the GLOB permutations honored (and, perhaps, those 
deprecated?)


Thanks.

Ken Wagner


On 01/04/2017 07:13 AM, R Smith wrote:
Just one more point of clarity, in case my previous explanation did 
not high-light this:


The query:
select trackid, name from tracks where name GLOB '*[^1-9]*';

is completely different to the query:
select trackid, name from tracks where name NOT GLOB '*[1-9]*';

The prior excludes only names which are completely non-zero-containing 
integers (such as "1979"), the latter excludes any value that contains 
a character between 1 and 9 at any position (such as "Opus No. 1" or 
"20 Flight Rock"). The two queries merely intersect in one specific 
point in the Chinook DB (at "1979", that is) but they have nothing 
else in common.


Cheers,
Ryan


On 2017/01/04 2:53 PM, R Smith wrote:



On 2017/01/04 7:17 AM, Ken Wagner wrote:

About 2/3 the way down the page at:

http://www.sqlitetutorial.net/sqlite-glob/   Get names without 
[1-9].


select trackid, name from tracks where name GLOB '*[^1-9]*';

Works properly in SQLiteMan and the SQLite Tutorial. ( Two 
different products, not officially part of sqlite.org, I think, but 
use the sqlite app.)


But not in sqlite3 3.15.1 and 3.16.1.



That should not "work" in ANY version of SQLite (depending on what 
you mean by "work"). The phrase '*[^1-9]*' essentially asks to match 
'Anything, followed by something that is NOT a character 1 through 9, 
followed by Anything again' which is really ANYTHING that isn't 
specifically an integer which doesn't contain zeroes. i.e. it will 
exclude a name like "1996" but not a name like "2016" or "123.456", 
so in the Chinook database (which the tutorial uses) the query will 
list ALL thousands of names except one (namely a song titled: "1979") 
so it would be hard for you to differentiate between the query that 
"works" and one that doesn't.


Perhaps suggest to the Tutorial creators to use phrases like 
'*[^0-9]*' in stead of 1-9 to start with, and then maybe an example 
that has more than one single exclusion across the entire DB (which 
make the results seem superfluous and not very educational).


Another confusing bit is your claim that it "works" in an earlier 
version and not in the newer versions - I get the same results in 3.9 
and 3.16.

May I ask how it differs for you?


___
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 Tutorial error

[Ken Wagner]

The "select trackid, name from tracks where name GLOB '*[^1-9]*';" query 
works as expected and does NOT include names with 1 thru 9 in them in 
SQLiteMan, a Linux/Ubuntu SQL manager. It uses SQLite as the DB backend.


The logic of any # chars but NOT 1 thru 9 plus any # chars means NOT 
1,2,3,4,5,6,7,8 or 9 in name col.


This also works the same in the 'sqlitetutorial.net' example given.

The issue is not which works how; the issue is why the difference? I.e., 
consistency.


I have notified the 'sqlitetutorial.net' folks of the discrepancy.

I added the SQLite Manager tool to FireFox v 50.1.0 and it, too, omits 
any # 1-9 in the query results using " GLOB '*[^1-9]*' ".


How to resolve??

Thanks.


On 01/04/2017 06:53 AM, R Smith wrote:



On 2017/01/04 7:17 AM, Ken Wagner wrote:

About 2/3 the way down the page at:

http://www.sqlitetutorial.net/sqlite-glob/   Get names without 
[1-9].


select trackid, name from tracks where name GLOB '*[^1-9]*';

Works properly in SQLiteMan and the SQLite Tutorial. ( Two 
different products, not officially part of sqlite.org, I think, but 
use the sqlite app.)


But not in sqlite3 3.15.1 and 3.16.1.



That should not "work" in ANY version of SQLite (depending on what you 
mean by "work"). The phrase '*[^1-9]*' essentially asks to match 
'Anything, followed by something that is NOT a character 1 through 9, 
followed by Anything again' which is really ANYTHING that isn't 
specifically an integer which doesn't contain zeroes. i.e. it will 
exclude a name like "1996" but not a name like "2016" or "123.456", so 
in the Chinook database (which the tutorial uses) the query will list 
ALL thousands of names except one (namely a song titled: "1979") so it 
would be hard for you to differentiate between the query that "works" 
and one that doesn't.


Perhaps suggest to the Tutorial creators to use phrases like 
'*[^0-9]*' in stead of 1-9 to start with, and then maybe an example 
that has more than one single exclusion across the entire DB (which 
make the results seem superfluous and not very educational).


Another confusing bit is your claim that it "works" in an earlier 
version and not in the newer versions - I get the same results in 3.9 
and 3.16.

May I ask how it differs for you?


___
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] SQLite3 Tutorial error

[R Smith]

Just one more point of clarity, in case my previous explanation did not 
high-light this:


The query:
select trackid, name from tracks where name GLOB '*[^1-9]*';

is completely different to the query:
select trackid, name from tracks where name NOT GLOB '*[1-9]*';

The prior excludes only names which are completely non-zero-containing 
integers (such as "1979"), the latter excludes any value that contains a 
character between 1 and 9 at any position (such as "Opus No. 1" or "20 
Flight Rock"). The two queries merely intersect in one specific point in 
the Chinook DB (at "1979", that is) but they have nothing else in common.


Cheers,
Ryan


On 2017/01/04 2:53 PM, R Smith wrote:



On 2017/01/04 7:17 AM, Ken Wagner wrote:

About 2/3 the way down the page at:

http://www.sqlitetutorial.net/sqlite-glob/   Get names without 
[1-9].


select trackid, name from tracks where name GLOB '*[^1-9]*';

Works properly in SQLiteMan and the SQLite Tutorial. ( Two 
different products, not officially part of sqlite.org, I think, but 
use the sqlite app.)


But not in sqlite3 3.15.1 and 3.16.1.



That should not "work" in ANY version of SQLite (depending on what you 
mean by "work"). The phrase '*[^1-9]*' essentially asks to match 
'Anything, followed by something that is NOT a character 1 through 9, 
followed by Anything again' which is really ANYTHING that isn't 
specifically an integer which doesn't contain zeroes. i.e. it will 
exclude a name like "1996" but not a name like "2016" or "123.456", so 
in the Chinook database (which the tutorial uses) the query will list 
ALL thousands of names except one (namely a song titled: "1979") so it 
would be hard for you to differentiate between the query that "works" 
and one that doesn't.


Perhaps suggest to the Tutorial creators to use phrases like 
'*[^0-9]*' in stead of 1-9 to start with, and then maybe an example 
that has more than one single exclusion across the entire DB (which 
make the results seem superfluous and not very educational).


Another confusing bit is your claim that it "works" in an earlier 
version and not in the newer versions - I get the same results in 3.9 
and 3.16.

May I ask how it differs for you?


___
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] SQLite3 Tutorial error

[R Smith]

On 2017/01/04 7:17 AM, Ken Wagner wrote:

About 2/3 the way down the page at:

http://www.sqlitetutorial.net/sqlite-glob/   Get names without [1-9].

select trackid, name from tracks where name GLOB '*[^1-9]*';

Works properly in SQLiteMan and the SQLite Tutorial. ( Two 
different products, not officially part of sqlite.org, I think, but 
use the sqlite app.)


But not in sqlite3 3.15.1 and 3.16.1.



That should not "work" in ANY version of SQLite (depending on what you 
mean by "work"). The phrase '*[^1-9]*' essentially asks to match 
'Anything, followed by something that is NOT a character 1 through 9, 
followed by Anything again' which is really ANYTHING that isn't 
specifically an integer which doesn't contain zeroes. i.e. it will 
exclude a name like "1996" but not a name like "2016" or "123.456", so 
in the Chinook database (which the tutorial uses) the query will list 
ALL thousands of names except one (namely a song titled: "1979") so it 
would be hard for you to differentiate between the query that "works" 
and one that doesn't.


Perhaps suggest to the Tutorial creators to use phrases like '*[^0-9]*' 
in stead of 1-9 to start with, and then maybe an example that has more 
than one single exclusion across the entire DB (which make the results 
seem superfluous and not very educational).


Another confusing bit is your claim that it "works" in an earlier 
version and not in the newer versions - I get the same results in 3.9 
and 3.16.

May I ask how it differs for you?


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

Re: [sqlite] Can the Explain explain what is going on here?

[Domonic Tom]

Thanks Simon.

I solved the issue by making sqlite3 *dbhandle  = NULL then testing it for a 
null value afterwards.  It works fine.





From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Wednesday, 4 January 2017 9:47 AM
To: SQLite mailing list
Subject: Re: [sqlite] Can the Explain explain what is going on here?


On 4 Jan 2017, at 12:09am, Bart Smissaert  wrote:

> Secondly, can the Explain statement tell me that indeed the UDF runs 10
> times in the first SQL and only once for the second SQL?

What makes you think that this is the case ?  Why would SQLite not be running 
the UDF 10 times in both cases ?

Did you build any caching of results into your UDF ?  If not, then SQLite has 
no idea it can optimize it by checking to see if it's using the same parameters 
each time.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
sqlite-users Info 
Page
mailinglists.sqlite.org
To see the collection of prior postings to the list, visit the sqlite-users 
Archives. (The current archive is only available to the list ...



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