QGIS: How to avoid using coalesce() in GPKG query layer for correct type casting?

I query the following layer via DB Manager:

select fid, geom, cast((field2*100/field1-100) as double) as dev from table

field1 and field2 are integers and have NULL values. Now in the DB manager’s result window it looks fine but when loaded as layer QGIS reads the column as string. Adjusting the query with coalesce():

select fid, geom, coalesce(cast((field2*100/field1-100) as real),0.0) as dev from table

QGIS reads the column as real.

select fid, geom, coalesce(cast((field2*100/field1-100) as real),0) as dev from table

QGIS reads the column as integer.
Now I know there’s this answer I consider to be a workaround for visualization and I don’t want to filter NULL values beforehand since this snipped is supposed to be part of larger query involving all rows.

Why on earth does it need the coalesce function for a correct typecasting? I thought having NULL values in an attribute table something totally normal for QGIS? Or do I have to redefine ‘NULL’ as NULL again? Why is this stuff that incompatible?