The adjustment has to happen manually indeed.
The return value of a call is either
DispatchResultWithPostInfo. The first type cannot refund any weight and implies that the user always pays the worst-case fee.
DispatchResultWithPostInfo; you can adjust the actually used weight by returning it.
One example would be in the Alliance pallet, which gets in a worst-case
witness and then adjusts to the actually used weight. Another one is in the Ranked-Collective, where the transaction will cost nothing in certain cases.
There is an issue open in Substrate that will address automatic refunds, but only for the PoV dimension of the Weight. The execution time will still need to be adjusted for manually.
To answer the Q in the title:
The consequence is that the users pays more fees than are actually required for spam protection of the chain. Depending on the discrepancy between worst-case and actual-case weight, this can be no problem at all or a huge problem. All depending on how large that discrepancy is.
Therefore in Substrate we normally do the manual refunding when we assume the discrepancy to be large enough to justify the additional code, benchmarking and testing effort.