Re: [sqlite] SQLite3 Tutorial error

[dandl]

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

> How about adding the above to the permanent docs?

>>>SQLite docs do not usually include examples or tutorial information.  It 
>>>would be more consistent just to take the text which is currently comments 
>>>in the source code file and include it in the docs.  You can see those 
>>>comments (or possibly an old version of them) here:

I agree. That's pretty clear, but not so easy to access where it is.

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

[James K. Lowden]

On Fri, 06 Jan 2017 13:48:26 -0700
"Keith Medcalf"  wrote:

> Both "glob" and "like" call the same function, likeFunc with
> different sets of user_data.  likeFunc does a bunch of validation
> then calls patternCompare which actually implements the like and glob
> functionality.  How like and glob work are documented in the preface
> to patternCompare.
> 
> like implements the standard sql like using % (0 or more) and _
> (exactly 1 char) as wildcard matches.
> 
> glob implements unix globbing using * (0 or more) and ? (exactly 1)
> as wildcard matches. "sets" of characters are indicated by  squockets
> (square brackets -- []).  Different from the standard unix glob
> however, it uses ^ to invert the sense of a set rather than an !.
> Since it is unicode, a character is [\u-\u10].  [^1-7] is
> equivalent to a match of any of the remaining unicode characters.

Thank you both for explaining where and how the glob functionality is
implemented.  

Along with David, I'd like to suggest this information be added to the
user documentation. Glob syntax is as much a part of SQLite's SQL
syntax as date & time functions are.  

--jkl
___
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/7/17, Simon Slavin  wrote:
>
> 
>
> I have a question to add.  How unicode-compliant are these functions ?

There is the usual ASCIi-only case-folding for LIKE.  But otherwise,
the function should work with unicode.

-- 
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

[Simon Slavin]

On 7 Jan 2017, at 6:27am, dandl  wrote:

> How about adding the above to the permanent docs?

SQLite docs do not usually include examples or tutorial information.  It would 
be more consistent just to take the text which is currently comments in the 
source code file and include it in the docs.  You can see those comments (or 
possibly an old version of them) here:



I have a question to add.  How unicode-compliant are these functions ?  Given 
the difficulty in identifying a unicode character, I’m guessing "not".

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

[dandl]

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

>>>SQLite does not use the glob function from the standard library -- the 
>>>function is defined in func.c
>>>
>>>Both "glob" and "like" call the same function, likeFunc with different sets 
>>>of user_data.  likeFunc does a bunch of validation then calls patternCompare 
>>>which actually implements the like and glob functionality.  How like and 
>>>glob work are documented in the preface to patternCompare.
>>>
>>>like implements the standard sql like using % (0 or more) and _ (exactly 1 
>>>char) as wildcard matches.
>>>
>>>glob implements unix globbing using * (0 or more) and ? (exactly 1) as 
>>>wildcard matches. "sets" of characters are indicated by  squockets (square 
>>>brackets -- []).  Different from the standard unix glob however, it uses ^ 
>>>to invert the sense of a set rather than an !.  Since it is unicode, a 
>>>character is [\u-\u10].  [^1-7] is equivalent to a match of any of 
>>>the remaining unicode characters.
>>>
>>>thus in unix/linux one may pronounce "match anything where one character is 
>>>not the digits 1 through 7" as *[!1-7]* one would pronounce the same request 
>>>to SQLite as *[^1-7]*
>>>
>>>This of course would match any string that was not composed entirely of only 
>>>the characters 1 through 7 (not that there are no characters 1 through 7 in 
>>>the string) -- and must be at least 1 character long.
>>>
>>>If one wanted to match strings that contained a 1 through 7 anywhere within, 
>>>then one would pronounce *[1-7]* on both unix/linux and to SQLite
>>>
>>>Were one to want a glob that excluded all strings that contained the digits 
>>>1 though 7 anywhere within, then one would pronounce, in SQLite, WHERE NOT x 
>>>GLOB '*[1-7]*' -- though this would also now match 0 length strings.
>>>
>>>There is no way to "invert" the match-sense of a glob pattern within the 
>>>pattern itself.  That is, one cannot use '^*[1-7]*' as an equivalent to the 
>>>above inversion of the results of a positive match.  GLOB patterns only 
>>>search for a positive match, not an exclusion.  The [^stuf] excludes the 
>>>characters or range provided from the characters matched by a ? -- [^stuf] 
>>>is not an exclusion of the characters stuf but rather a match for any of the 
>>>other unicode characters except stuf -- in other words a "somewhat limited 
>>>?".

How about adding the above to the permanent docs? AFAIK there is currently no 
way to know all this stuff other than reading source.

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]

On Friday, 6 January, 2017 12:49, James K. Lowden  
wrote:

> On Fri, 6 Jan 2017 10:23:06 +1100
> "dandl"  wrote:
> 
> > Unix globbing for Linux is defined here:
> > http://man7.org/linux/man-pages/man7/glob.7.html. AFAICT Sqlite does
> > not implement this behaviour.
> 
> A quick scan of SQLite sources shows only references to the glob
> function, no implementation.  In func.c, we find
> 
> LIKEFUNC(glob, 2, , SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
> 
> It looks to me like SQLite imports glob(3) as its default
> implementation.  Have you an example for which a glob pattern behaves
> differently in SQLite versus C?
> 
> (For those following along at home, bear in mind that glob(3) need not
> necessarily be what your favorite shell uses.)
> 
> If indeed SQLite is using the glob function from libc, ISTM it's
> perfectly sufficient to refer to glob(7) for syntax, since that's the
> documentation for the controlling implementation.

SQLite does not use the glob function from the standard library -- the function 
is defined in func.c

Both "glob" and "like" call the same function, likeFunc with different sets of 
user_data.  likeFunc does a bunch of validation then calls patternCompare which 
actually implements the like and glob functionality.  How like and glob work 
are documented in the preface to patternCompare.

like implements the standard sql like using % (0 or more) and _ (exactly 1 
char) as wildcard matches.

glob implements unix globbing using * (0 or more) and ? (exactly 1) as wildcard 
matches. "sets" of characters are indicated by  squockets (square brackets -- 
[]).  Different from the standard unix glob however, it uses ^ to invert the 
sense of a set rather than an !.  Since it is unicode, a character is 
[\u-\u10].  [^1-7] is equivalent to a match of any of the remaining 
unicode characters.

thus in unix/linux one may pronounce "match anything where one character is not 
the digits 1 through 7" as *[!1-7]*
one would pronounce the same request to SQLite as *[^1-7]*

This of course would match any string that was not composed entirely of only 
the characters 1 through 7 (not that there are no characters 1 through 7 in the 
string) -- and must be at least 1 character long.

If one wanted to match strings that contained a 1 through 7 anywhere within, 
then one would pronounce *[1-7]* on both unix/linux and to SQLite

Were one to want a glob that excluded all strings that contained the digits 1 
though 7 anywhere within, then one would pronounce, in SQLite, WHERE NOT x GLOB 
'*[1-7]*' -- though this would also now match 0 length strings.

There is no way to "invert" the match-sense of a glob pattern within the 
pattern itself.  That is, one cannot use '^*[1-7]*' as an equivalent to the 
above inversion of the results of a positive match.  GLOB patterns only search 
for a positive match, not an exclusion.  The [^stuf] excludes the characters or 
range provided from the characters matched by a ? -- [^stuf] is not an 
exclusion of the characters stuf but rather a match for any of the other 
unicode characters except stuf -- in other words a "somewhat limited ?".





___
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/6/17, James K. Lowden  wrote:
>
> A quick scan of SQLite sources shows only references to the glob
> function, no implementation.

The implementation is built-in here:
https://www.sqlite.org/src/artifact/d8582ee919759756?ln=610-770

For portability reasons, SQLite avoids using routines from the
standard library.  For example,  atof() scan behave differently
depending on LOCALE and so SQLite has its own code for text-to-float
conversion.

-- 
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

[James K. Lowden]

On Fri, 6 Jan 2017 10:23:06 +1100
"dandl"  wrote:

> Unix globbing for Linux is defined here:
> http://man7.org/linux/man-pages/man7/glob.7.html. AFAICT Sqlite does
> not implement this behaviour.

A quick scan of SQLite sources shows only references to the glob
function, no implementation.  In func.c, we find

LIKEFUNC(glob, 2, , SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),

It looks to me like SQLite imports glob(3) as its default
implementation.  Have you an example for which a glob pattern behaves
differently in SQLite versus C?  

(For those following along at home, bear in mind that glob(3) need not
necessarily be what your favorite shell uses.)

If indeed SQLite is using the glob function from libc, ISTM it's
perfectly sufficient to refer to glob(7) for syntax, since that's the
documentation for the controlling implementation.  

--jkl

___
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]

David,

Yes. That would be a big assist. I am new to using SQLite3 and found the 
GLOB function erratic in practice -- not on SQLite3 but on other web 
sites using SQLite. They yielded completely opposite results.


Second the motion.

Ken


On 01/05/2017 05:23 PM, dandl wrote:

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


They’re probably using the external function interface to override the internal 
globbing function.  And by the look of the results at least one of the 
programmers involved thinks that GLOB and REGEX do the same thing.

I think you're right. One of the contributing problems is that the behaviour of 
GLOB is not defined in the documentation. Here is all it says:

"The GLOB operator is similar to LIKE but uses the Unix file globbing syntax for its 
wildcards. Also, GLOB is case sensitive, unlike LIKE. Both GLOB and LIKE may be preceded 
by the NOT keyword to invert the sense of the test. The infix GLOB operator is 
implemented by calling the function glob(Y,X) and can be modified by overriding that 
function."

Unix globbing for Linux is defined here: 
http://man7.org/linux/man-pages/man7/glob.7.html. AFAICT Sqlite does not 
implement this behaviour.

Perhaps some accurate documentation for GLOB in Sqlite would help to clarify 
things?

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

[Ken Wagner]

Danap,

I thought so, too. But it is not the case.

I am cross-checking with the Unix/SQLite results, using Unix/SQLite as 
the base reference.


Ken


On 01/05/2017 01:30 PM, dmp wrote:

Message: 21
Date: Wed, 4 Jan 2017 22:10:59 -0600
From: Ken Wagner <beauco...@gmail.com>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] SQLite3 Tutorial error
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 tried the example from R. Smith and got the exact same results
in my own SQLite Interface, Ajqvue, using the SQLITE JDBC.

I don't know what those other apps may be doing, but they should
just pass the query created by the user "DIRECTLY" to the SQLite DB
engine without any modification. Likewise the result should also
not be mucked with before presentation.

danap.

___
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

[Jens Alfke]

> On Jan 5, 2017, at 3:23 PM, dandl  wrote:
> 
> Perhaps some accurate documentation for GLOB in Sqlite would help to clarify 
> things?

+1. I for one was unaware that glob understands the “[…]” syntax for character 
classes (both in SQLite and in the Unix glob(3) function), despite having used 
SQLite for 12 years and Unix for several decades.

—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

[dandl]

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

>>>They’re probably using the external function interface to override the 
>>>internal globbing function.  And by the look of the results at least one of 
>>>the programmers involved thinks that GLOB and REGEX do the same thing.

I think you're right. One of the contributing problems is that the behaviour of 
GLOB is not defined in the documentation. Here is all it says:

"The GLOB operator is similar to LIKE but uses the Unix file globbing syntax 
for its wildcards. Also, GLOB is case sensitive, unlike LIKE. Both GLOB and 
LIKE may be preceded by the NOT keyword to invert the sense of the test. The 
infix GLOB operator is implemented by calling the function glob(Y,X) and can be 
modified by overriding that function."

Unix globbing for Linux is defined here: 
http://man7.org/linux/man-pages/man7/glob.7.html. AFAICT Sqlite does not 
implement this behaviour.

Perhaps some accurate documentation for GLOB in Sqlite would help to clarify 
things?

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

[Simon Slavin]

On 5 Jan 2017, at 7:30pm, dmp  wrote:

> I don't know what those other apps may be doing, but they should
> just pass the query created by the user "DIRECTLY" to the SQLite DB
> engine without any modification. Likewise the result should also
> not be mucked with before presentation.

They’re probably using the external function interface to override the internal 
globbing function.  And by the look of the results at least one of the 
programmers involved thinks that GLOB and REGEX do the same thing.

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

[dmp]

> Message: 21
> Date: Wed, 4 Jan 2017 22:10:59 -0600
> From: Ken Wagner <beauco...@gmail.com>
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] SQLite3 Tutorial error

> 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 tried the example from R. Smith and got the exact same results
in my own SQLite Interface, Ajqvue, using the SQLITE JDBC.

I don't know what those other apps may be doing, but they should
just pass the query created by the user "DIRECTLY" to the SQLite DB
engine without any modification. Likewise the result should also
not be mucked with before presentation.

danap.

___
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,

Thanks. I have saved the Unix GLOB reference.

When I inferred that other versions of SQLite gave the other results, it 
was thru the other SQLite GUI tools and the version of SQLite that they 
used.


Thanks for helping to make clear what was going on.

Ken



On 01/05/2017 02:53 AM, R Smith wrote:


On 2017/01/05 9:04 AM, Ken Wagner wrote:

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.)




What the 3rd party people do is likely override the GLOB and REGEXP 
functions, either when compiling their SQLite versions or at runtime 
using the user-function api. People add their own GLOB sometimes 
purely because they add REGEXP functions (which is not defined by 
default in SQLite) and the new REGEXP comes with a GLOB sibling that 
gets added and they perhaps prefer the outcome as it happens in SQLite 
tutorial and DB Browser etc.  These alternate globbing methodologies 
may even be preferred by you or your customers.


The similarity between the different tools suggest to me they probably 
use the same added code / library which someone made to "improve" over 
the standard Unix file globbing. I wouldn't want or expect the SQLite 
tutorial people to change how they implement the globbing, but they 
should at least notify learners of the difference.


What you and your customers need to know is:
  - SQLite uses Unix file globbing exactly
  - 3rd party tools which implement SQLite engine may override this 
(and seemingly mostly do so)
  - You can expect different GLOB returns via different tools, and 
these need to be understood during use. (Hopefully most tools are 
consistent in their alteration)
  - SQLite engine as used in the SQLite CLI will always return the 
exact same Unix file globbing results in queries.


The big question here was on the last point where you suggested you 
saw different results using some version of the SQLite CLI than 
another - but I think Keith is correct in assuming it's simply a 
mistake, however, if you DO find a difference in any version, please 
let us know - that would be a bug (or a concern at the very least).


Thanks,
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

[R Smith]

On 2017/01/05 9:04 AM, Ken Wagner wrote:

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.)




What the 3rd party people do is likely override the GLOB and REGEXP 
functions, either when compiling their SQLite versions or at runtime 
using the user-function api. People add their own GLOB sometimes purely 
because they add REGEXP functions (which is not defined by default in 
SQLite) and the new REGEXP comes with a GLOB sibling that gets added and 
they perhaps prefer the outcome as it happens in SQLite tutorial and DB 
Browser etc.  These alternate globbing methodologies may even be 
preferred by you or your customers.


The similarity between the different tools suggest to me they probably 
use the same added code / library which someone made to "improve" over 
the standard Unix file globbing. I wouldn't want or expect the SQLite 
tutorial people to change how they implement the globbing, but they 
should at least notify learners of the difference.


What you and your customers need to know is:
  - SQLite uses Unix file globbing exactly
  - 3rd party tools which implement SQLite engine may override this 
(and seemingly mostly do so)
  - You can expect different GLOB returns via different tools, and 
these need to be understood during use. (Hopefully most tools are 
consistent in their alteration)
  - SQLite engine as used in the SQLite CLI will always return the 
exact same Unix file globbing results in queries.


The big question here was on the last point where you suggested you saw 
different results using some version of the SQLite CLI than another - 
but I think Keith is correct in assuming it's simply a mistake, however, 
if you DO find a difference in any version, please let us know - that 
would be a bug (or a concern at the very least).


Thanks,
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

[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
<https://www.sqlite.org/2017/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

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
> <https://www.sqlite.org/2017/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.  

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 <beauco...@gmail.com> 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 <https://www.sqlite.org/2017/s
> 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 <rsm...@rsweb.co.za> 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 

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 
<https://www.sqlite.org/2017/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 <rsm...@rsweb.co.za> 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 

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 <rsm...@rsweb.co.za> 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

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 <rsm...@rsweb.co.za> 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] SQLite3 Tutorial error

[Ken Wagner]

Thanks, Jens.

I will do that.

- Ken


On 01/04/2017 12:29 AM, Jens Alfke wrote:

On Jan 3, 2017, at 9:17 PM, Ken Wagner  wrote:

About 2/3 the way down the page at:

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

You should probably report this to the people who run that website. It’s not 
associated with SQLite itself.

—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

Re: [sqlite] SQLite3 Tutorial error

[Jens Alfke]

> On Jan 3, 2017, at 9:17 PM, Ken Wagner  wrote:
> 
> About 2/3 the way down the page at:
> 
> http://www.sqlitetutorial.net/sqlite-glob/ 
>    Get names without [1-9].

You should probably report this to the people who run that website. It’s not 
associated with SQLite itself.

—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

[Ken Wagner]

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.

This works as expected in sqlite3 (3.15.1 and 3.16.1 :

select trackid, name from tracks where name not GLOB '*[1-9]*'; Gets 
names without [1-9].




On 01/03/2017 07:37 PM, Richard Hipp wrote:

On 1/3/17, Ken Wagner  wrote:

Hi SQLite,

In the SQLite3 Tutorial

What tutorial are you referring to?



___
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/3/17, Ken Wagner  wrote:
> Hi SQLite,
>
> In the SQLite3 Tutorial
What tutorial are you referring to?

-- 
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

[sqlite] SQLite3 Tutorial error

[Ken Wagner]

Hi SQLite,

In the SQLite3 Tutorial the following query is said to find all names 
WITHOUT numbers in them. But it fails to do so.


select trackid, name from tracks where name GLOB '*[^1-9]*';   per the 
tutorial fails to list names WITHOUT numbers in them.


However, this DOES work:

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

Is this a tutorial error?  Using SQLiteMan the first query DOES work. 
But not in SQLite 3.15.1 or 3.16.1.


Is this a bug or a tutorial error? Can't tell which version of SQLite 
SQLiteMan is using.


System: Ubuntu 16.04, SQLite3 (3.15.1 and 3.16.1). Also tested using 
Ruby 2.3.3 with ruby-sqlite extension.


Thanks,

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