Re: BUG #16675: VALUES not working for CITEXT

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

> The following bug has been logged on the website:
>
> Bug reference: 16675
> Logged by: Mark Cowne
> Email address: mcowne(at)webroot(dot)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

> DROP TABLE Test;
>
>