Thread: BUG #18765: Inconsistent behaviour and errors with LIKE
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
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.
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.
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.
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