From: | Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | its+postgres(at)csuhta(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #14349: Parse failure (?) when LIKE swapped out for ~~ |
Date: | 2016-10-03 08:14:06 |
Message-ID: | 20161003.171406.191036624.horiguchi.kyotaro@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hello,
At Mon, 03 Oct 2016 06:09:24 +0000, its+postgres(at)csuhta(dot)com wrote in <20161003060924(dot)27236(dot)38178(at)wrigleys(dot)postgresql(dot)org>
> The following bug has been logged on the website:
>
> Bug reference: 14349
> Logged by: Corey Csuhta
> Email address: its+postgres(at)csuhta(dot)com
> PostgreSQL version: 9.5.4
> Operating system: macOS
> Description:
>
> Here's this query:
>
> SELECT 1 WHERE lower('ARTIST') LIKE '%'||'art'||'%';
> Which selects:
> 1
>
> But if you exchange ~~ for the LIKE statement here, Postgres does not
> process it the same way:
>
> SELECT 1 WHERE lower('ARTIST') ~~ '%'||'art'||'%';
> Returns the error:
> argument of WHERE must be type boolean, not type text
> LINE 1: SELECT 1 WHERE lower('ARTIST') ~~ '%'||'art'||'%'
SELECT 1 WHERE lower('ARTIST') ~~ ('%'||'art'||'%');
The above works as you mentioned below. The operators '~~' and
'||' are assumed 'other native operators' then given the same
precedence. LIKE has lower precedance to them.
This is described here.
https://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html#SQL-PRECEDENCE
> Is this expected? The documentation implies you can swap these operators and
> get the same results.
Yes, it is a designed behavior. Any two equivalent operators are
logically swappable but some adjustment might be needed by the
reason of operator precedences.
> I believe I have a minimum example above, the swap works if you do not use
> || or if you wrap the left and right side of the expression in parenthesis
> to help the parser.
Cheers.
--
Kyotaro Horiguchi
NTT Open Source Software Center
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2016-10-03 08:16:39 | Re: BUG #14319: Logical decoding dropping statements in subtransactions |
Previous Message | its+postgres | 2016-10-03 06:09:24 | BUG #14349: Parse failure (?) when LIKE swapped out for ~~ |