pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #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