Обсуждение: BUG #14349: Parse failure (?) when LIKE swapped out for ~~

Поиск
Список
Период
Сортировка

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

От
its+postgres@csuhta.com
Дата:
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM0OQpMb2dnZWQgYnk6ICAg
ICAgICAgIENvcmV5IENzdWh0YQpFbWFpbCBhZGRyZXNzOiAgICAgIGl0cytw
b3N0Z3Jlc0Bjc3VodGEuY29tClBvc3RncmVTUUwgdmVyc2lvbjogOS41LjQK
T3BlcmF0aW5nIHN5c3RlbTogICBtYWNPUwpEZXNjcmlwdGlvbjogICAgICAg
IAoKSGVyZSdzIHRoaXMgcXVlcnk6DQoNClNFTEVDVCAxIFdIRVJFIGxvd2Vy
KCdBUlRJU1QnKSBMSUtFICclJ3x8J2FydCd8fCclJzsgDQpXaGljaCBzZWxl
Y3RzOg0KMQ0KDQpCdXQgaWYgeW91IGV4Y2hhbmdlIH5+IGZvciB0aGUgTElL
RSBzdGF0ZW1lbnQgaGVyZSwgUG9zdGdyZXMgZG9lcyBub3QKcHJvY2VzcyBp
dCB0aGUgc2FtZSB3YXk6DQoNClNFTEVDVCAxIFdIRVJFIGxvd2VyKCdBUlRJ
U1QnKSB+fiAnJSd8fCdhcnQnfHwnJSc7IA0KUmV0dXJucyB0aGUgZXJyb3I6
DQphcmd1bWVudCBvZiBXSEVSRSBtdXN0IGJlIHR5cGUgYm9vbGVhbiwgbm90
IHR5cGUgdGV4dA0KTElORSAxOiBTRUxFQ1QgMSBXSEVSRSBsb3dlcignQVJU
SVNUJykgfn4gJyUnfHwnYXJ0J3x8JyUnDQoNCklzIHRoaXMgZXhwZWN0ZWQ/
IFRoZSBkb2N1bWVudGF0aW9uIGltcGxpZXMgeW91IGNhbiBzd2FwIHRoZXNl
IG9wZXJhdG9ycyBhbmQKZ2V0IHRoZSBzYW1lIHJlc3VsdHMuDQoNCkkgYmVs
aWV2ZSBJIGhhdmUgYSBtaW5pbXVtIGV4YW1wbGUgYWJvdmUsIHRoZSBzd2Fw
IHdvcmtzIGlmIHlvdSBkbyBub3QgdXNlCnx8IG9yIGlmIHlvdSB3cmFwIHRo
ZSBsZWZ0IGFuZCByaWdodCBzaWRlIG9mIHRoZSBleHByZXNzaW9uIGluIHBh
cmVudGhlc2lzCnRvIGhlbHAgdGhlIHBhcnNlci4KCg==

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

От
Kyotaro HORIGUCHI
Дата:
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