Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Scott Robison]

On Jan 16, 2015 8:05 AM, "Simon Slavin"  wrote:
>
>
> On 16 Jan 2015, at 12:23pm, Jay Kreibich  wrote:
>
> > They can all be (re)defined, some just happen to have default functions:
> >
> > https://www.sqlite.org/lang_expr.html#like
>
> Might be worth noting here that there can be a danger in replacing the
definitions of default functions.

Excellent points. As I said earlier, I am not planning to change anything,
just was curious based on my flawed inference from the core functions page.
I am playing with some C++ code to help provide some compile time checking
of queries that isn't possible with plain C string literal based queries,
and was curious about the functions vs the operators.

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

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Simon Slavin]

On 16 Jan 2015, at 12:23pm, Jay Kreibich  wrote:

> They can all be (re)defined, some just happen to have default functions:
> 
> https://www.sqlite.org/lang_expr.html#like

Might be worth noting here that there can be a danger in replacing the 
definitions of default functions.  These functions might be used in your schema 
(e.g. for CHECK constraints) and may be overridden in your program before it 
handles data.  Someone could then open the same database in another program 
(e.g. the Shell Tool) which had instead the default functions and use that to 
add data.

If instead you define custom functions and use those in your schema, if someone 
opens your database in another program it will have no definition for the 
function, and produce an error.

I'm not saying that overriding functions is always bad, just reminding people 
of the danger.

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

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Jay Kreibich]

On Jan 16, 2015, at 6:56 AM, Richard Hipp  wrote:

> On 1/16/15, Scott Robison  wrote:
>> LIKE & GLOB can be overridden with user defined functions. According to
>> https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument
>> function, GLOB can be a 2 argument function, and neither MATCH nor REGEXP
>> can be redefined.
> 
> Where did you read that MATCH and REGEXP could not be redefined?  That
> is a bug in the documentation that needs to be fixed.  They are
> undefined by default and are given meaning by redefining.  They would
> be useless if they were not redefinable.
> 


I think the OP interpreted the lack of a match() or regexp() function on the 
lang_corefunc.html page as meaning they could not be defined/redefined.

 -j


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

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





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

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Scott Robison]

On Fri, Jan 16, 2015 at 5:56 AM, Richard Hipp  wrote:

> On 1/16/15, Scott Robison  wrote:
> > LIKE & GLOB can be overridden with user defined functions. According to
> > https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument
> > function, GLOB can be a 2 argument function, and neither MATCH nor REGEXP
> > can be redefined.
>
> Where did you read that MATCH and REGEXP could not be redefined?  That
> is a bug in the documentation that needs to be fixed.  They are
> undefined by default and are given meaning by redefining.  They would
> be useless if they were not redefinable.
>

I was browsing the list of functions at
https://www.sqlite.org/lang_corefunc.html and inferred from the lack of
REGEXP or MATCH functions (since they are effectively undefined) that their
presence would not impact the REGEXP or MATCH operators.

In like fashion, the GLOB function only documents a two argument form, not
a three argument form.

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

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Richard Hipp]

On 1/16/15, Scott Robison  wrote:
> So the only remaining question is whether there is any functional
> difference between the LIKE & GLOB SQL functions and the same named
> operators (other than argument order)? Is there a reason to prefer one or
> the other in SQL syntax?

They are aliases for one another.  Syntactic sugar.  You can see this
by using EXPLAIN:

   .explain
   EXPLAIN SELECT * FROM sqlite_master WHERE name LIKE 'abc%';
   EXPLAIN SELECT * FROM sqlite_master WHERE like('abc%',name);

Both generate identical bytecode.

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

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Richard Hipp]

On 1/16/15, Scott Robison  wrote:
> LIKE & GLOB can be overridden with user defined functions. According to
> https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument
> function, GLOB can be a 2 argument function, and neither MATCH nor REGEXP
> can be redefined.

Where did you read that MATCH and REGEXP could not be redefined?  That
is a bug in the documentation that needs to be fixed.  They are
undefined by default and are given meaning by redefining.  They would
be useless if they were not redefinable.

>
> MATCH is only used in FTS queries if my understanding is correct, so I can
> appreciate why MATCH can't be redefined given the close integration between
> the keyword and a specific virtual table module.
>
> That leaves LIKE & GLOB & REGEXP. Why the differences in redefinition
> capabilities? Why can't an ESCAPE clause form of GLOB be redefined? Why
> can't any form of REGEXP be redefined? Or are some or all of these
> "exceptions" possible but the documentation doesn't cover them?
>
> I have no desire to redefine anything myself, this is primarily an exercise
> in understanding why the design choices were made, and I am wondering if
> there is ever a reason in SQL code to choose the function form of the LIKE
> (or other) operator over the operator itself. Is it intended that the
> operator form of the expression will always behave exactly like the
> function form of the expression (with the appropriate reordering of
> arguments)?
>
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Scott Robison]

On Fri, Jan 16, 2015 at 5:23 AM, Jay Kreibich  wrote:

>
> On Jan 16, 2015, at 5:06 AM, Scott Robison 
> wrote:
>
> > LIKE & GLOB can be overridden with user defined functions. According to
> > https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument
> > function, GLOB can be a 2 argument function, and neither MATCH nor REGEXP
> > can be redefined.
>
> They can all be (re)defined, some just happen to have default functions:
>
> https://www.sqlite.org/lang_expr.html#like


Thanks for the link. I was so focused on the syntax at the top of the page
and the function lists that I never scrolled down far enough, obviously.

So the only remaining question is whether there is any functional
difference between the LIKE & GLOB SQL functions and the same named
operators (other than argument order)? Is there a reason to prefer one or
the other in SQL syntax?

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

Re: [sqlite] LIKE vs GLOB vs MATCH vs REGEXP

[Jay Kreibich]

On Jan 16, 2015, at 5:06 AM, Scott Robison  wrote:

> LIKE & GLOB can be overridden with user defined functions. According to
> https://www.sqlite.org/lang_corefunc.html LIKE can be a 2 or 3 argument
> function, GLOB can be a 2 argument function, and neither MATCH nor REGEXP
> can be redefined.

They can all be (re)defined, some just happen to have default functions:

https://www.sqlite.org/lang_expr.html#like


  -j

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

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





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