Thread: BUG #16675: VALUES not working for CITEXT
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'));
DROP TABLE Test;
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)
┌──────────┐
│ ?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)
┌──────────┐
│ ?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)
┌───────────────────────────────────────────────────────────────────────────────────┐
│ 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)
┌─────────────────────────────────────────────────────────────┐
│ 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)
┌───────────────────────────────────────────────────────────────────────────────────┐
│ 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;