pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18765: Inconsistent behaviour and errors with LIKE
6+ messages / 5 participants
[nested] [flat]
* BUG #18765: Inconsistent behaviour and errors with LIKE
@ 2025-01-03 16:47 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 6+ messages in thread
From: PG Bug reporting form @ 2025-01-03 16:47 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: anmol.mohanty@salesforce.com
The following bug has been logged on the website:
Bug reference: 18765
Logged by: Anmol Mohanty
Email address: anmol.mohanty@salesforce.com
PostgreSQL version: 17.0
Operating system: NA(used fiddle tool at sqlfiddle.com)
Description:
select 'a\' like 'a\'; -- error - LIKE pattern must not end with escape
character
select 'a' like 'a\'; -- f - query runs
This doesn't make sense. The LIKE pattern is incorrect in both.
I've also checked around constant folding and runtime non-literal values by
inserting into cte's and temp tables. Same outcome.
```
WITH a AS (SELECT 'xyz' AS value)
SELECT value LIKE 'xyz\' AS result
FROM a;
```
f
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18765: Inconsistent behaviour and errors with LIKE
@ 2025-01-03 17:21 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2025-01-03 17:21 UTC (permalink / raw)
To: anmol.mohanty@salesforce.com; pgsql-bugs@lists.postgresql.org
On Fri, Jan 3, 2025 at 10:04 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18765
> Logged by: Anmol Mohanty
> Email address: anmol.mohanty@salesforce.com
> PostgreSQL version: 17.0
> Operating system: NA(used fiddle tool at sqlfiddle.com)
> Description:
>
> select 'a\' like 'a\'; -- error - LIKE pattern must not end with escape
> character
> select 'a' like 'a\'; -- f - query runs
> This doesn't make sense. The LIKE pattern is incorrect in both.
>
> I've also checked around constant folding and runtime non-literal values by
> inserting into cte's and temp tables. Same outcome.
>
> ```
> WITH a AS (SELECT 'xyz' AS value)
> SELECT value LIKE 'xyz\' AS result
> FROM a;
> ```
> f
>
>
Working as designed.
Execution optimization combined with non-compilation.
You may not get a failure if the condition is proven to evaluate to false
before encountering the malformed part of the expression. There is no
compilation step that evaluates the supplied text for correctness
independent of its usage. The input string running out of characters while
the test string still has some is known to result in a false outcome.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18765: Inconsistent behaviour and errors with LIKE
@ 2025-01-06 19:07 Anmol Mohanty <anmol.mohanty@salesforce.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Anmol Mohanty @ 2025-01-06 19:07 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
This is a violation of the SQL standard.
[image: image.png]
`b.i.2` states an exception must be raised if PATTERN doesn't follow the
semantics outlined.
On Fri, Jan 3, 2025 at 9:22 AM David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Fri, Jan 3, 2025 at 10:04 AM PG Bug reporting form <
> noreply@postgresql.org> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 18765
>> Logged by: Anmol Mohanty
>> Email address: anmol.mohanty@salesforce.com
>> PostgreSQL version: 17.0
>> Operating system: NA(used fiddle tool at sqlfiddle.com)
>> Description:
>>
>> select 'a\' like 'a\'; -- error - LIKE pattern must not end with escape
>> character
>> select 'a' like 'a\'; -- f - query runs
>> This doesn't make sense. The LIKE pattern is incorrect in both.
>>
>> I've also checked around constant folding and runtime non-literal values
>> by
>> inserting into cte's and temp tables. Same outcome.
>>
>> ```
>> WITH a AS (SELECT 'xyz' AS value)
>> SELECT value LIKE 'xyz\' AS result
>> FROM a;
>> ```
>> f
>>
>>
> Working as designed.
>
> Execution optimization combined with non-compilation.
>
> You may not get a failure if the condition is proven to evaluate to false
> before encountering the malformed part of the expression. There is no
> compilation step that evaluates the supplied text for correctness
> independent of its usage. The input string running out of characters while
> the test string still has some is known to result in a false outcome.
>
> David J.
>
>
Attachments:
image.png (image/png, 315.4K) download
view image
* Re: BUG #18765: Inconsistent behaviour and errors with LIKE
@ 2025-01-06 19:25 David G. Johnston <david.g.johnston@gmail.com>
parent: Anmol Mohanty <anmol.mohanty@salesforce.com>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2025-01-06 19:25 UTC (permalink / raw)
To: Anmol Mohanty <anmol.mohanty@salesforce.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Mon, Jan 6, 2025 at 12:07 PM Anmol Mohanty <anmol.mohanty@salesforce.com>
wrote:
> This is a violation of the SQL standard.
>
>>
>>
We do discuss where we vary from the SQL Standard in the documentation for
LIKE so a sentence covering this deviation seems warranted.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18765: Inconsistent behaviour and errors with LIKE
@ 2025-01-08 20:52 Peter Eisentraut <peter@eisentraut.org>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Peter Eisentraut @ 2025-01-08 20:52 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; Anmol Mohanty <anmol.mohanty@salesforce.com>; +Cc: pgsql-bugs@lists.postgresql.org
On 06.01.25 20:25, David G. Johnston wrote:
> On Mon, Jan 6, 2025 at 12:07 PM Anmol Mohanty
> <anmol.mohanty@salesforce.com <mailto:anmol.mohanty@salesforce.com>> wrote:
>
> This is a violation of the SQL standard.
>
> We do discuss where we vary from the SQL Standard in the
> documentation for LIKE so a sentence covering this deviation seems
> warranted.
I have also come across this issue recently, and I think we should
actually fix it. It makes sense to verify that the pattern is
syntactically correct before trying to use it, instead of just using it
incrementally and then erroring when you happen to hit the problematic bits.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #18765: Inconsistent behaviour and errors with LIKE
@ 2025-01-08 21:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <peter@eisentraut.org>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2025-01-08 21:12 UTC (permalink / raw)
To: Peter Eisentraut <peter@eisentraut.org>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Anmol Mohanty <anmol.mohanty@salesforce.com>; pgsql-bugs@lists.postgresql.org
Peter Eisentraut <peter@eisentraut.org> writes:
> I have also come across this issue recently, and I think we should
> actually fix it. It makes sense to verify that the pattern is
> syntactically correct before trying to use it, instead of just using it
> incrementally and then erroring when you happen to hit the problematic bits.
I'm concerned about the performance cost of adding an extra scan of
the pattern --- a scan that has exactly zero benefit in all normal
use cases. Admittedly typical patterns aren't very big, but still
the cost/benefit ratio is bad for most people.
I wonder if we could buy back whatever it'd cost us by treating
this initial scan as a "compilation" of the LIKE pattern into
some form that would make the actual string search cheaper.
My gut says that building a representation that contains tokens
like "any character", "zero or more any characters", or "literal
string of N bytes" could save some cycles while searching.
In ILIKE, we could perhaps also case-fold the pattern at this
stage.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-01-08 21:12 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-03 16:47 BUG #18765: Inconsistent behaviour and errors with LIKE PG Bug reporting form <noreply@postgresql.org>
2025-01-03 17:21 ` David G. Johnston <david.g.johnston@gmail.com>
2025-01-06 19:07 ` Anmol Mohanty <anmol.mohanty@salesforce.com>
2025-01-06 19:25 ` David G. Johnston <david.g.johnston@gmail.com>
2025-01-08 20:52 ` Peter Eisentraut <peter@eisentraut.org>
2025-01-08 21:12 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox