Subqueries in where conditions using a having expression themselves fail on postgres if compound keys are involved

[nilmerg]

A query like this:

SELECT event.id,
       event.time,
       event.source_id,
       event.object_id,
       event.type,
       event.severity,
       event.message,
       event.username,
       event_source.id             AS event_source_id,
       event_source.type           AS event_source_type,
       event_source.name           AS event_source_name,
       event_object.id             AS event_object_id,
       event_object.host           AS event_object_host,
       event_object.service        AS event_object_service,
       event_incident.id           AS event_incident_id,
       event_incident.object_id    AS event_incident_object_id,
       event_incident.started_at   AS event_incident_started_at,
       event_incident.recovered_at AS event_incident_recovered_at,
       event_incident.severity     AS event_incident_severity
FROM event
         INNER JOIN source event_source ON event_source.id = event.source_id
         INNER JOIN object event_object ON event_object.id = event.object_id
         LEFT JOIN incident_event event_incident_event ON event_incident_event.event_id = event.id
         LEFT JOIN incident event_incident ON event_incident.id = event_incident_event.incident_id
WHERE (EXISTS(SELECT (1)
              FROM object_extra_tag sub_object_extra_tag
                       INNER JOIN object sub_object_extra_tag_object
                                  ON sub_object_extra_tag_object.id = sub_object_extra_tag.object_id
                       INNER JOIN event sub_object_extra_tag_object_event
                                  ON sub_object_extra_tag_object_event.object_id = sub_object_extra_tag_object.id
              WHERE (sub_object_extra_tag_object_event.id = event.id)
                AND ((sub_object_extra_tag.tag = 'hostgroup / linux - servers') OR
                     (sub_object_extra_tag.tag = 'servicegroup / ping'))
              HAVING COUNT(DISTINCT sub_object_extra_tag.object_id, sub_object_extra_tag.source_id,
                           sub_object_extra_tag.tag) >= 2))
  AND (event.time <= 1680271102)
ORDER BY event.time desc
LIMIT 26

fails with this message:

SQLSTATE[42883]: Undefined function: 7 ERROR: function count(bytea, bigint, text) does not exist
LINE 1: ... = $1) OR (sub_object_extra_tag.tag = $2)) HAVING COUNT(DIST...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

[julianbrost]

You need an extra set of parenthesis around multiple column names:

noma_test=# SELECT COUNT(DISTINCT (object_id, source_id, tag)) FROM object_extra_tag;
 count 
-------
     0
(1 row)

[nilmerg]

And why? Didn't find any explanation myself online.

[julianbrost]

I don't know, all I can say is that it works.

The syntax in the documentation actually reads like it should work without (but well, it doesn't):

aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

[nilmerg]

May be fixed by #108

[raviks789]

This is an error caused by COUNT function and not DISTINCT. COUNT function takes only one argument. But in case of MySQL when COUNT(DISTINCT ) is used, it allows for multiple columns to be specified after DISTINCT. Check the MySQL documentation mentioning the same. However in standard SQL, they must be concatenated into one column which is done implicitly in case of Postgres when you surround the columns with parenthesis when using DISTINCT.

See the below examples demonstrating the same using object_id_tag table:

1. DISTINCT of multiple column combination:

   Postgres with parenthesis:

   SELECT DISTINCT(tag, value) FROM object_id_tag LIMIT 10;

   

   MySQL equivalent with CONCAT:

   SELECT DISTINCT(CONCAT('(', tag, ',', value, ')')) FROM object_id_tag limit 10;

   

2. Use of COUNT(DISTINCT ):

   Postgres:

   SELECT COUNT(DISTINCT(tag, value) FROM object_id_tag;

   MySQL:

   SELECT COUNT(DISTINCT tag, value) FROM object_id_tag;

   Outputs the same result as (in my case 982):

   SELECT COUNT(*) FROM (SELECT DISTINCT tag, value FROM object_id_tag) AS object_tag_value;