BUG #14349: Parse failure (?) when LIKE swapped out for ~~

Hello,

At Mon, 03 Oct 2016 06:09:24 +0000, its+postgres@csuhta.com wrote in <20161003060924.27236.38178@wrigleys.postgresql.org>

The following bug has been logged on the website:

Bug reference: 14349
Logged by: Corey Csuhta
Email address: its+postgres@csuhta.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

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