pg.ddx.io pgsql-bugs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
BUG #16510: Count Distinct with non distinct column in combination with string constants throws error 4+ messages / 3 participants [nested] [flat]
* BUG #16510: Count Distinct with non distinct column in combination with string constants throws error @ 2020-06-25 13:02 PG Bug reporting form <noreply@postgresql.org> 0 siblings, 1 reply; 4+ messages in thread From: PG Bug reporting form @ 2020-06-25 13:02 UTC (permalink / raw) To: pgsql-bugs@lists.postgresql.org; +Cc: aceonline@gmx.de The following bug has been logged on the website: Bug reference: 16510 Logged by: Aceonline Email address: aceonline@gmx.de PostgreSQL version: 11.7 Operating system: Unix (bitnami/postgresql:11.7.0 image) Description: Minimal Reproduction: CREATE TABLE public.testtable ( column1 int4 NOT NULL, column2 varchar NULL ); insert into public.testtable values(1, 'test1'); insert into public.testtable values(2, 'test1'); SELECT COUNT( DISTINCT (testtable.column2, 'blub') ) FROM public.testtable; ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16510: Count Distinct with non distinct column in combination with string constants throws error @ 2020-06-25 14:18 Daniel Gustafsson <daniel@yesql.se> parent: PG Bug reporting form <noreply@postgresql.org> 0 siblings, 1 reply; 4+ messages in thread From: Daniel Gustafsson @ 2020-06-25 14:18 UTC (permalink / raw) To: aceonline@gmx.de, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org> > On 25 Jun 2020, at 15:02, PG Bug reporting form <noreply@postgresql.org> wrote: > SELECT > COUNT( DISTINCT (testtable.column2, 'blub') ) > FROM > public.testtable; PostgreSQL doesn't know which datatype you expect 'blub' to be, as it isn't related to the testtable relation in your query. If you cast to the datatype of your choice you will get the expected result. postgres=# SELECT COUNT(DISTINCT(testtable.column2, 'blub')) FROM public.testtable; ERROR: could not identify a comparison function for type unknown postgres=# SELECT COUNT(DISTINCT(testtable.column2, 'blub'::varchar)) FROM public.testtable; count ------- 1 (1 row) cheers ./daniel ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16510: Count Distinct with non distinct column in combination with string constants throws error @ 2020-06-25 14:32 Tom Lane <tgl@sss.pgh.pa.us> parent: Daniel Gustafsson <daniel@yesql.se> 0 siblings, 1 reply; 4+ messages in thread From: Tom Lane @ 2020-06-25 14:32 UTC (permalink / raw) To: Daniel Gustafsson <daniel@yesql.se>; +Cc: aceonline@gmx.de, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org> Daniel Gustafsson <daniel@yesql.se> writes: > PostgreSQL doesn't know which datatype you expect 'blub' to be, as it isn't > related to the testtable relation in your query. If you cast to the datatype > of your choice you will get the expected result. > postgres=# SELECT COUNT(DISTINCT(testtable.column2, 'blub')) FROM public.testtable; > ERROR: could not identify a comparison function for type unknown The reason for this might be a little more obvious if you wrote the implicit row constructor explicitly, ie SELECT COUNT(DISTINCT ROW(testtable.column2, 'blub')) FROM public.testtable; The row's datatype is indeterminate as-specified. Perhaps there's room to argue that we should allow 'unknown' to decay to 'text' automatically in this context, but I'm not in a big hurry to do that. It seems better to make people be explicit about which datatype they intend inside such complex, infrequently-used constructs. regards, tom lane ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16510: Count Distinct with non distinct column in combination with string constants throws error @ 2020-06-25 14:42 Daniel Gustafsson <daniel@yesql.se> parent: Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 4+ messages in thread From: Daniel Gustafsson @ 2020-06-25 14:42 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: aceonline@gmx.de, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org> > On 25 Jun 2020, at 16:32, Tom Lane <tgl@sss.pgh.pa.us> wrote: > The reason for this might be a little more obvious if you wrote the > implicit row constructor explicitly, ie > > SELECT COUNT(DISTINCT ROW(testtable.column2, 'blub')) FROM public.testtable; That's a good point, that's a clearer explanation. > Perhaps there's room to argue that we should allow 'unknown' to decay to > 'text' automatically in this context, but I'm not in a big hurry to do > that. It seems better to make people be explicit about which datatype > they intend inside such complex, infrequently-used constructs. Agreed, it sounds like something that will just work in most cases but run the risk of introducing subtle bugs in the cases where it doesn't. cheers ./daniel ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-06-25 14:42 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-06-25 13:02 BUG #16510: Count Distinct with non distinct column in combination with string constants throws error PG Bug reporting form <noreply@postgresql.org> 2020-06-25 14:18 ` Daniel Gustafsson <daniel@yesql.se> 2020-06-25 14:32 ` Tom Lane <tgl@sss.pgh.pa.us> 2020-06-25 14:42 ` Daniel Gustafsson <daniel@yesql.se>
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