pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16675: VALUES not working for CITEXT
2+ messages / 2 participants
[nested] [flat]
* BUG #16675: VALUES not working for CITEXT
@ 2020-10-16 10:10 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2020-10-16 10:10 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: mcowne@webroot.com
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;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #16675: VALUES not working for CITEXT
@ 2020-10-16 11:20 Pavel Stehule <pavel.stehule@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Pavel Stehule @ 2020-10-16 11:20 UTC (permalink / raw)
To: mcowne@webroot.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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
> DROP TABLE Test;
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-10-16 11:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-10-16 10:10 BUG #16675: VALUES not working for CITEXT PG Bug reporting form <noreply@postgresql.org>
2020-10-16 11:20 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox