I recommend reading the description of the option `VerifyHostKeyDns` in the `ssh_config` man page.
If set to `yes`, you get automatic trust-on-first-use (no user prompt) if you use DNSSec, and you get the current asking-the-user behavior if your DNSSec is broken or you are under attack.
Obviously it's more secure if you use DNSSec, because that way you can reflexively deny any request to manually verify a host key, but it provides value regardless.
That site doesn't mention that when DNSSec is absent, the behaviour of SSH is identical to what happens if you hadn't used the SSHFP record at all, except that for unsophisticated attackers it also displays "no matching host key found in DNS".
So even without DNSSec using the SSHFP records is an improvement over not using them because some of the time it tells you for certain you're being interfered with.
There is no situation in which an insecure DNS response is auto-trusted by the SSH client.
If set to `yes`, you get automatic trust-on-first-use (no user prompt) if you use DNSSec, and you get the current asking-the-user behavior if your DNSSec is broken or you are under attack.
Obviously it's more secure if you use DNSSec, because that way you can reflexively deny any request to manually verify a host key, but it provides value regardless.