Skip to content

Reworked SolicitationORMRepository.list_by_user to fix Cartesian product join

Daniel Lyons requested to merge fix-cartesian-product-query-in-solicitation-users into main

I noticed a warning in the logs the other day:

/middle_layer/solicit/application_layer/orm_repositories/src/solicitation.py:118: 
SAWarning: SELECT statement has a cartesian product between FROM element(s) "solicitations" and FROM element "anon_1".  
Apply join condition(s) between each element to resolve.

The reason for this error was that the generated SQL looked kind of like this:

SELECT * 
FROM 
    solicitations, 
    (SELECT * FROM solicitations UNION ...) anon1
ORDER BY solicitation_id

Without a join between solicitations and anon1, we are actually fetching all the solicitations once for each solicitation. That's what the warning is about. Usually, we have such a small number of solicitations that this isn't a huge issue, and apparently something further down the line must be removing duplicates.

Without this change we would probably eventually be facing an extremely large performance problem here, in production, when the number of solicitations gets significantly larger. Better to address it now.

Merge request reports