pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / 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>
  2020-06-25 14:18 ` Re: BUG #16510: Count Distinct with non distinct column in combination with string constants throws error Daniel Gustafsson <daniel@yesql.se>
  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 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   ` Re: BUG #16510: Count Distinct with non distinct column in combination with string constants throws error Tom Lane <tgl@sss.pgh.pa.us>
  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 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 ` Re: BUG #16510: Count Distinct with non distinct column in combination with string constants throws error Daniel Gustafsson <daniel@yesql.se>
@ 2020-06-25 14:32   ` Tom Lane <tgl@sss.pgh.pa.us>
  2020-06-25 14:42     ` Re: BUG #16510: Count Distinct with non distinct column in combination with string constants throws error 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 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 ` Re: BUG #16510: Count Distinct with non distinct column in combination with string constants throws error Daniel Gustafsson <daniel@yesql.se>
  2020-06-25 14:32   ` Re: BUG #16510: Count Distinct with non distinct column in combination with string constants throws error Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-06-25 14:42     ` Daniel Gustafsson <daniel@yesql.se>
  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