BUG #16675: VALUES not working for CITEXT

pá 16. 10. 2020 v 13:01 odesílatel PG Bug reporting form <
noreply@postgresql.org> napsal:

The following bug has been logged on the website:

Bug reference: 16675
Logged by: Mark Cowne
Email address: mcowne@webroot.com
PostgreSQL version: 11.7
Operating system: x86_64-pc-linux-gnu
Description:

-- Fourth SELECT doesn't return anything and should.

CREATE TABLE Test(Col CITEXT NOT NULL PRIMARY KEY);

INSERT INTO Test(Col) VALUES('ABC');

SELECT Col FROM Test WHERE Col IN ('abc');
SELECT Col FROM Test WHERE Col IN ('ABC');
SELECT Col FROM Test WHERE Col IN (VALUES('ABC'));
SELECT Col FROM Test WHERE Col IN (VALUES('abc'));

This is not a bug - it is a feature and an effect of some others features.

Usual literal constant in Postgres has an "unknown" type. Real type is
derived from context.

postgres=# select citext 'ABC' = 'abc';
┌──────────┐
│ ?column? │
╞══════════╡
│ t │
└──────────┘
(1 row)

so there is comparison of citext <-> unknown ==> citext <-> citext

but VALUES klause force unknown to text type.

postgres=# select citext 'ABC' = text 'abc';
┌──────────┐
│ ?column? │
╞══════════╡
│ f │
└──────────┘
(1 row)

"text" type is marked as prefered - so if there are more possibilities and
one is type "text", then this type is selected without raising an error.

postgres=# explain verbose SELECT Col FROM Test WHERE Col IN ('abc');
┌───────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN
│
╞═══════════════════════════════════════════════════════════════════════════════════╡
│ Index Only Scan using test_pkey on public.test (cost=0.15..8.17 rows=1
width=32) │
│ Output: col
│
│ Index Cond: (test.col = 'abc'::citext)
│
└───────────────────────────────────────────────────────────────────────────────────┘
(3 rows)

postgres=# explain verbose SELECT Col FROM Test WHERE Col IN
(VALUES('abc'));
┌─────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═════════════════════════════════════════════════════════════╡
│ Seq Scan on public.test (cost=0.00..27.00 rows=1 width=32) │
│ Output: test.col │
│ Filter: ((test.col)::text = 'abc'::text) │
└─────────────────────────────────────────────────────────────┘
(3 rows)

if you want force cast to citext type, you should to use explicit cast
inside VALUES clause

postgres=# explain verbose SELECT Col FROM Test WHERE Col IN
(VALUES('abc'::citext));
┌───────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN
│
╞═══════════════════════════════════════════════════════════════════════════════════╡
│ Index Only Scan using test_pkey on public.test (cost=0.15..8.17 rows=1
width=32) │
│ Output: test.col
│
│ Index Cond: (test.col = 'abc'::citext)
│
└───────────────────────────────────────────────────────────────────────────────────┘
(3 rows)

postgres=# SELECT Col FROM Test WHERE Col IN (VALUES('abc'::citext));
┌─────┐
│ col │
╞═════╡
│ ABC │
└─────┘
(1 row)

Regards

Pavel