Wrong count return by select count query

This is my query:

from i0 in Infection,
right_join: u1 in UserPermission,
on: u1.facility_id == i0.facility_id, left_join: e2 in Encounter,
on: i0.encounter_id == e2.id, left_join: p3 in Patient,
on: e2.patient_id == p3.id, right_join: e4 in Encounter,
on: e4.id == i0.encounter_id, right_join: p5 in Patient,
on: ilike(p3.first_name, ^"%1364%") or ilike(p3.last_name, ^"%1364%") or (ilike(p3.identifier, ^"%1364%") or 
p3.ssn == ^"1364" or i0.id == ^1364),
where: u1.user_id == ^1 and u1.facility_id in ^[1, 2, 3],
where: i0.instance_id == ^1, where: i0.is_active == ^true and ^true,
where: i0.is_complete == ^"true" and ^true,
where: i0.meets_criteria == ^"true" and ^true,
where: i0.nhsn_reportable == ^"false" and ^true, distinct: true,
select: count("*")

This query returned the total number of patients from the patient’s table, which is wrong but if I execute this query without select, it returned only 1 record from infection, which is correct.

It returns correct count without this line of code:

on: ilike(p3.first_name, ^"%1364%") or ilike(p3.last_name, ^"%1364%") or (ilike(p3.identifier, ^"%1364%") or 
p3.ssn == ^"1364" or i0.id == ^1364)

I can’t understand why select(count) is counting records from the patient table and not from the infection table

Any help/guidance will be much appreciated


What actual SQL queries are generated? Can you see them in your log?


SELECT DISTINCT count('*') FROM "infections" AS i0 RIGHT OUTER JOIN "users_facilities_permissions" AS 
u1 ON u1."facility_id" = i0."facility_id" LEFT OUTER JOIN "encounters" AS e2 ON i0."encounter_id" = e2."id" 
LEFT OUTER JOIN "patients" AS p3 ON e2."patient_id" = p3."id" RIGHT OUTER JOIN "encounters" AS e4 
ON e4."id" = i0."encounter_id" RIGHT OUTER JOIN "patients" AS p5 ON ((p3."first_name" ILIKE $1) OR 
(p3."last_name" ILIKE $2)) OR (((p3."identifier" ILIKE $3) OR (p3."ssn" = $4)) OR (i0."id" = $5)) WHERE 
((u1."user_id" = $6) AND u1."facility_id" = ANY($7)) AND (i0."instance_id" = $8) AND ((i0."is_active" = $9) AND 
 $10) AND ((i0."is_complete" = $11) AND $12) AND ((i0."meets_criteria" = $13) AND $14) AND 
((i0."nhsn_reportable" = $15) AND $16) ["%1364%", "%1364%", "%1364%", "1364", 1364, 1, [1, 2, 3], 1, 
 true, true, true, true, true, true, false, true]

Iirc the distinct only does it work on the result set. And in your case, due to count, the result set is just a single number.

Try to remove the distinct and compare count and returned data that way.

1 Like

still the same result.

Then I’m not sure what it could be. I do not consider it a problem with ecto though, but the query is evaluated by your database…

it is working fine if I select individual fields with select. But as soon as I introduce count. it returns wrong number of records count.

Two things you might try:

  1. Try COUNT(DISTINCT *) instead of DISTINCT COUNT(*). This might require a fragment.
  2. Execute the SQL you noted five messages above in psql to confirm that the query is actually returning the result you expect.
  1. Even without distinct, it’s not returning correct count.
  2. It returns the correct result even in Ecto if I don’t use select count.
    What I don’t understand is why it’s returning the wrong result for count, but works fine without it.

I can understand the frustration, there are lot of joins in your query, many of them outer joins, and I can never remember the precedence rules.

Thats why I suggested running the SQL you posted above in psql just to confirm (or not) that its the generated query correctly represents what you’re after.


This absolutely. Ecto doesn’t control things like COUNT or what the database returns, only massage it into structures based on the types in a schema, so if the information is wrong then the query is wrong.

If you can create an example setup at https://sqltest.net/ along with the query then we can help you via it as well. :slight_smile:

1 Like