BUG #14512: Backslashes in LIKE

On Tue, Jan 24, 2017 at 10:25 AM, <vojta.rylko@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 14512
Logged by: Vojtěch Rylko
Email address: vojta.rylko@gmail.com
PostgreSQL version: 9.5.5
Operating system: Linux 3.19.0-32-generic x86_64
Description:

Hi, LIKE behaves differently depending on left side.

​???​

select 1 where '\' like '\\\'; -- one and three backslashes

?column?
----------
(0 rows)

select 1 where '\\' like '\\\'; -- two and three backslashes

ERROR: LIKE pattern must not end with escape character

​The right-hand side is the "pattern" - i.e., <\\\> - which ends with the
escape...

​David J.
​

On Tue, Jan 24, 2017 at 10:32 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Tue, Jan 24, 2017 at 10:25 AM, <vojta.rylko@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 14512
Logged by: Vojtěch Rylko
Email address: vojta.rylko@gmail.com
PostgreSQL version: 9.5.5
Operating system: Linux 3.19.0-32-generic x86_64
Description:

Hi, LIKE behaves differently depending on left side.

​???​

select 1 where '\' like '\\\'; -- one and three backslashes

?column?
----------
(0 rows)

select 1 where '\\' like '\\\'; -- two and three backslashes

ERROR: LIKE pattern must not end with escape character

​The right-hand side is the "pattern" - i.e., <\\\> - which ends with the
escape...

​Never mind - I was multi-tasking and mis-read what you wrote...

I'll give it more attention when I have a moment if no one else chimes in
first.

David J.​

On Tue, Jan 24, 2017 at 10:40 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Tue, Jan 24, 2017 at 10:32 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Tue, Jan 24, 2017 at 10:25 AM, <vojta.rylko@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference: 14512
Logged by: Vojtěch Rylko
Email address: vojta.rylko@gmail.com
PostgreSQL version: 9.5.5
Operating system: Linux 3.19.0-32-generic x86_64
Description:

Hi, LIKE behaves differently depending on left side.

​???​

select 1 where '\' like '\\\'; -- one and three backslashes

?column?
----------
(0 rows)

select 1 where '\\' like '\\\'; -- two and three backslashes

ERROR: LIKE pattern must not end with escape character

​The right-hand side is the "pattern" - i.e., <\\\> - which ends with the
escape...

​Never mind - I was multi-tasking and mis-read what you wrote...

I'll give it more attention when I have a moment if no one else chimes in
first.

​Not a hacker but I'd say that the '\' LIKE '\\\' expression is
encountering an invalid optimization that determines that the LIKE cannot
succeed (due to string length differences, probably) - it too should fail
like the other '\\' LIKE '\\\' example.

So, it is a "failure to fail" type of bug. Confirmed using a 9.3.12
instance.

David J.​

vojta.rylko@gmail.com writes:

Hi, LIKE behaves differently depending on left side.

select 1 where '\' like '\\\'; -- one and three backslashes

?column?
----------
(0 rows)

select 1 where '\\' like '\\\'; -- two and three backslashes

ERROR: LIKE pattern must not end with escape character

I see no bug here. The pattern is wrong, but it happens not to notice in
the first case because it never reaches the buggy part of the pattern.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Tue, Jan 24, 2017 at 11:02 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

vojta.rylko@gmail.com writes:

Hi, LIKE behaves differently depending on left side.

select 1 where '\' like '\\\'; -- one and three backslashes

?column?
----------
(0 rows)

select 1 where '\\' like '\\\'; -- two and three backslashes

ERROR: LIKE pattern must not end with escape character

I see no bug here. The pattern is wrong, but it happens not to notice in
the first case because it never reaches the buggy part of the pattern.

Then consider a feature request that a malformed pattern be detected and
fail independent of the data being checked. Such non-deterministic failure
is at least a POLA violation and makes what should be a basically
compile-time error into a run-time one.

I will agree that It is not a back-patchable bug (unless we decide to never
fail and instead have a malformed pattern always return false - we'd at
least be consistent - though probably not in a desirable way) but would
say it is a defect that should be addressed in v10.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Then consider a feature request that a malformed pattern be detected and
fail independent of the data being checked. Such non-deterministic failure
is at least a POLA violation and makes what should be a basically
compile-time error into a run-time one.

Meh. We could do something like the attached, but I think it would be a
net performance drag in practically all cases, and I doubt it is worth it.

regards, tom lane

2017-01-24 19:15 GMT+01:00 David G. Johnston <david.g.johnston@gmail.com>:

Then consider a feature request that a malformed pattern be detected and fail independent of the data being checked. Such non-deterministic failure is at least a POLA violation and makes what should be a basically compile-time error into a run-time one.

This is not pure compile-time "error" as pattern in LIKE could be
dynamic expression, for example:

root=# create table t (a varchar);
CREATE TABLE

root=# insert into t values ('\\\');
INSERT 0 1

root=# select * from t t1 cross join t t2 where t1.a like t2.a;
ERROR: LIKE pattern must not end with escape character

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

2017-01-24 18:48 GMT+01:00 David G. Johnston <david.g.johnston@gmail.com>:

​Not a hacker but I'd say that the '\' LIKE '\\\' expression is
encountering an invalid optimization that determines that the LIKE cannot
succeed (due to string length differences, probably) - it too should fail
like the other '\\' LIKE '\\\' example.

So, it is a "failure to fail" type of bug. Confirmed using a 9.3.12
instance.

From user perspective I see this bug quite similar to behaviour of boolean
expression evaluation, where it is stated in documentation:

if the result of an expression can be determined by evaluating only some

parts of it, then other subexpressions might not be evaluated at all -- 4.2.14.
Expression Evaluation Rules

So I expect this:

root=# select 1 where '\\' like '\\\';
ERROR: LIKE pattern must not end with escape character
root=# select 1 where false and '\\' like '\\\';
?column?
----------
(0 rows)

same as I expect

root=# select 1 where 1/0 = 0 and false;
ERROR: division by zero
root=# select 1 where false and 1/0 = 0;
?column?
----------
(0 rows)

(Note that examples above are not deterministic because of unspecified
order of subexpressions evaluation in where clause.)

But reported behaviour confuses me as it seems like leaked internals of
LIKE implementation.

On Wed, Jan 25, 2017 at 2:16 AM, Vojtěch Rylko <vojta.rylko@gmail.com>
wrote:

2017-01-24 19:15 GMT+01:00 David G. Johnston <david.g.johnston@gmail.com>:

Then consider a feature request that a malformed pattern be detected and

fail independent of the data being checked. Such non-deterministic failure
is at least a POLA violation and makes what should be a basically
compile-time error into a run-time one.

This is not pure compile-time "error" as pattern in LIKE could be
dynamic expression, for example:

But now we're no longer talking about an expression of the form "LIKE
constant". In SQL whenever you decide to write a query that involves
tables and columns you run the risk of introducing run-time bugs if you
make assumptions about the contents of those columns that fail to hold. So
if you decide to write queries of that form you should define table t like
so:

create table t (a varchar check (a !~ '\$')) -- which could be improved
upon depending on your needs - but it would at least catch every actual
invalid expression at the cost of disallowing valid ones.

David J.

On Wed, Jan 25, 2017 at 2:28 AM, Vojtěch Rylko <vojta.rylko@gmail.com>
wrote:

2017-01-24 18:48 GMT+01:00 David G. Johnston <david.g.johnston@gmail.com>:

​Not a hacker but I'd say that the '\' LIKE '\\\' expression is
encountering an invalid optimization that determines that the LIKE cannot
succeed (due to string length differences, probably) - it too should fail
like the other '\\' LIKE '\\\' example.

So, it is a "failure to fail" type of bug. Confirmed using a 9.3.12
instance.

From user perspective I see this bug quite similar to behaviour of boolean
expression evaluation, where it is stated in documentation:

if the result of an expression can be determined by evaluating only some

parts of it, then other subexpressions might not be evaluated at all -- 4.2.14.
Expression Evaluation Rules

So I expect this:

root=# select 1 where '\\' like '\\\';
ERROR: LIKE pattern must not end with escape character
root=# select 1 where false and '\\' like '\\\';
?column?
----------
(0 rows)

same as I expect

root=# select 1 where 1/0 = 0 and false;
ERROR: division by zero
root=# select 1 where false and 1/0 = 0;
?column?
----------
(0 rows)

(Note that examples above are not deterministic because of unspecified
order of subexpressions evaluation in where clause.)

But reported behaviour confuses me as it seems like leaked internals of
LIKE implementation.

​I think we all agree that it does. The opinions we are looking for are
whether, given that you've written a correctly formed LIKE pattern, do you
want every single instance of testing against that pattern to be preceded
by a test that checks whether the given pattern is valid?​ While not
measured it is a run-time cost that should return true in nearly all cases
expect for development bugs.

I use RegEx a lot - I'm already used to the cost being built-in and,
frankly, when doing string comparison work, I suspect that the order of
magnitude such a pre-check would add would be nominal.

Given that any supposedly successful match against the pattern would fail
in the case of a silly typo of this form I'm leaning more to the fact that
having a bad pattern escape detection would be very difficult. Patterns
that check for invalid data are more at risk...

select 1 where 'abc\' like 'abc\'; -- fails, supposed to use \\ on the end
of the pattern

Given time to think about it more I'm now leaning toward keeping the
present behavior.

David J.