SQLite3 Tutorial error

classic Classic list List threaded Threaded
53 messages Options
123
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 Tutorial error

Richard Hipp-3
On 1/3/17, Ken Wagner <[hidden email]> wrote:
> Hi SQLite,
>
> In the SQLite3 Tutorial
What tutorial are you referring to?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:
>> Hi SQLite,
>>
>> In the SQLite3 Tutorial
> What tutorial are you referring to?
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 Tutorial error

Jens Alfke-2

> On Jan 3, 2017, at 9:17 PM, Ken Wagner <[hidden email]> wrote:
>
> About 2/3 the way down the page at:
>
> http://www.sqlitetutorial.net/sqlite-glob/ <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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:
>>
>> About 2/3 the way down the page at:
>>
>> http://www.sqlitetutorial.net/sqlite-glob/ <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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 Tutorial error

R Smith
In reply to this post by Ken Wagner


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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 Tutorial error

Ken Wagner
In reply to this post by R Smith
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 Tutorial error

Ken Wagner
In reply to this post by R Smith
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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 Tutorial error

Simon Slavin-3

On 4 Jan 2017, at 1:43pm, Ken Wagner <[hidden email]> 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:

<https://sqlite.org/cli.html>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 Tutorial error

R Smith
In reply to this post by Ken Wagner


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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 Tutorial error

Ken Wagner
In reply to this post by R Smith
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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 Tutorial error

Ken Wagner
In reply to this post by Simon Slavin-3
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 <[hidden email]> 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:
>
> <https://sqlite.org/cli.html>
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 Tutorial error

Ken Wagner
In reply to this post by R Smith
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 Tutorial error

Ken Wagner
In reply to this post by R Smith
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 Tutorial error

Ken Wagner
In reply to this post by R Smith
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 Tutorial error

Richard Hipp-3
In reply to this post by Simon Slavin-3
On 1/4/17, Simon Slavin <[hidden email]> wrote:

>
> On 4 Jan 2017, at 1:43pm, Ken Wagner <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:
>> On 4 Jan 2017, at 1:43pm, Ken Wagner <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 Tutorial error

R Smith
In reply to this post by Ken Wagner


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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 Tutorial error

Jens Alfke-2
In reply to this post by R Smith

> On Jan 4, 2017, at 5:57 AM, R Smith <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
123