Wrong count return by select count query

Questions / Help
1

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

Thanks

2

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

3

yes.

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]
4

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
5

still the same result.

6

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…

7

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.

8

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.
9
  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.
10

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.

3 Likes
11

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