An insidious SQL bug I wrote

(permess.mt)[https://permess.mt/] is a side-project which has been ongoing for several years now, in fact the first commit was made on Sun Aug 23 10:56:26 2020, it was still relatively early in my career at that time.

Being a side-project, it doesn’t enjoy the same level of attention as my professional work, and so I never wrote any tests for it, some manual testing is all that has been done, especially since it has very few, if any, users other than myself.

Recently I refacted some code to try and amend how certain permits are selected for scraping. Nothing obviously broke, the query was not throwing any exceptions.

Eventually I realised that I had essentially broken the query completely from a feature perspective, even though it was correct from a funcional perspective.

Here’s the query before:

SELECT DISTINCT pu.id
FROM permit_urls pu
LEFT JOIN permit_details pd ON pu.id = pd.permit_url_id
WHERE pd.permit_type = $1 AND (pd.permit_url_id IS NULL OR pu.decided = 'f')
LIMIT $2;