weirdly the subquery returns an error if I execute it directly:

mysql> select 1 from host_software hs where hs.software_id=software.id;
ERROR 1054 (42S22): Unknown column 'software.id' in 'where clause'

this subquery alone will fail because it only works if you use it in a subselect where software is a table outside of it

ah right, my bad 🙂