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