Re: BUG #18765: Inconsistent behaviour and errors with LIKE

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(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Fri, Jan 3, 2025 at 10:04 AM PG Bug reporting form <
> noreply(at)postgresql(dot)org> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 18765
>> Logged by: Anmol Mohanty
>> Email address: anmol(dot)mohanty(at)salesforce(dot)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.
>
>