Alright, I don't think I'm supposed to type it word for word, so instead I'll give a somewhat detailed gist. Though speaking after I've written it, I still feel bad because I don't know this topic well enough to talk about it entirely on my own, so the majority of this answer after this paragraph is still from the book (not so much my own words or work). Also the question changed a little bit since I started, but hopefully what's below is still sufficient. First though, I'll answer the actual title question here: Yes. Whether delta functions, Lighthill's stress tensor, etc., these source terms have compact support, so they are identically zero outside of the source location. This means that acoustic pressure and density obey a nonhomogeneous equation while in the source location and a homogeneous equation otherwise.
Now, what follows is coming from 5.4 (Acoustic radiation of a piston in a plane) of Acoustics, Aeroacoustics, and Vibrations by F. Anselmet and P. Mattei. Much of the notation is still the same, which is quite nice.
It starts off "Here we focus on the acoustic pressure created by a circular piston, which is indefinitely extended by a perfectly rigid plane (we often use the word 'baffled') with respect to this configuration)," which they mention is a good approximation of a speaker (a membrane embedded into a rigid enclosure).
There are some assumptions: Piston movement is harmonic, the medium has no influence on the piston's vibration, the space is $\mathbb{R}^3$ with $z > 0$ making up the fluid with density $\rho_0$ and speed of sound $c_0$ and fluid at $z < 0$ is basically ignored. The piston itself sits in the plane $\Sigma$ at $z = 0$ with a radius $R$ centered at the origin defining the domain $S$ and is set in motion by a vibratory displacement $u = u_0 e^{-i\omega t}$ (here $u_0 = \mathrm{const.}$ and $k = \omega / c_0$ will be the wavenumber), which in the rest of the problem, the time dependence is omitted. There is also no acoustic source within the fluid (no further source I suppose). Finally, the fluid is ideal (no viscosity).
Then the acoustic pressure radiated by the piston $p(M)$ (the authors use single letters to represent coordinates, so $M = (x,y,z)$) obeys the (truly) homogeneous Helmholtz equation
\begin{align}
(\Delta + k^2)p(M) = 0 && (z > 0)
\end{align}
with
\begin{align}
\frac{\partial p}{\partial \vec{n}}(Q) &= \omega^2 \rho_0 u_0 & (Q &\in S) \\
\frac{\partial p}{\partial \vec{n}}(Q) &= 0 & (Q &\in \Sigma - S).
\end{align}
Formulating the solution using the Green's function $G$ is then the convolution of the kernel ("the Green's function of the medium") and "the vibration field of the surface."
$$
p(M) = -\omega^2 \rho_0 \int_S u_0 G(M, Q) \,\mathrm{d}\sigma(Q),
$$
for which $G$ is found to obey
\begin{align}
(\Delta + k^2)G(M,M') &= \delta(M - M') & (z, z' &> 0) \\
\frac{\partial G}{\partial \vec{n}_Q}(Q,M) &= 0 & (Q &\in \Sigma).
\end{align}
$G$ is found via the method of images and is
$$
G(M, M') = -\frac{e^{i k d(M, M')}}{4\pi d(M, M')} -\frac{e^{i k d(M, M'')}}{4\pi d(M, M'')},
$$
where $M = (x,y,z)$, $M' = (x', y', z')$, $M'' = (x', y', -z')$, and $d(M, M')$ is the Euclidean distance between $M$ and $M'$.
Then it says some stuff about the solution $p(M)$ and goes on that on the $\Sigma$ plane ($z' = 0$), $M' = M''$ so the acoustic pressure radiated by the piston is given by
$$
P(M) = \omega^2 \rho_0 u_0 \int_S \frac{e^{ik\sqrt{(x - x')^2 + (y - y')^2 + z^2}}}{2\pi\sqrt{(x - x')^2 + (y - y')^2 + z^2}} \,\mathrm{d}x' \,\mathrm{d}y',
$$
which holds for the pressure field for a piston of any form, though is an approximate analytical result for particularly the circular piston.
Now I'll speak again for a little bit. This is a great result, but it's of fairly little use to us right now. But it gets better. At first it seems a little strange to see an unknown $f(\theta)$ appear in your expression for $p$ which is then immediately defined afterwards. Why do this? Why not just write it all out as one? I believe the reason is to emphasize that this function is the directivity function of the emitted radiation - it determines more or less where the sound is actually going. I say that for a reason - the next section is 5.4.1 (Far-field radiation of a circular piston: directivity).
They begin by switching to spherical coordinates where
\begin{align}
x &= r \sin\theta \cos\phi && x' = r' \sin\theta' \cos\phi' \\
y &= r \sin\theta \sin\phi \qquad \text{and} \quad && y' = r' \sin\theta' \sin\phi' \\
z &= r \cos\theta && z' = r' \cos\theta'
\end{align}
and performing approximations (I've skipped some already) using the fact that $M$ is now far away while $M'$ is a point on the piston very close to the origin. So using $r \gg r'$, $kr \gg 1$ (far-field), and $z' = 0$ ($\theta' = \pi/2$) on the $\Sigma$ plane,
$$
\frac{e^{ik\sqrt{(x - x')^2 + (y - y')^2 + z^2}}}{2\pi\sqrt{(x - x')^2 + (y - y')^2 + z^2}} \approx \frac{e^{ikr}}{2\pi r} e^{-ik r' \sin\theta \cos(\phi - \phi')}.
$$
Then the acoustic pressure radiated by the piston in the far-field is given by
\begin{align}
p(M) &\approx \omega^2 \rho_0 u_0 \frac{e^{ikr}}{2\pi r} \int_0^R r' \int_0^{2\pi} e^{-ikr' \sin\theta \cos\phi} \,\mathrm{d}\phi \,\mathrm{d}r' \\
&\approx \omega^2 \rho_0 u_0 \frac{e^{ikr}}{r} \int_0^R r' J_0(kr' \sin\theta) \,\mathrm{d}r' \quad (\text{since } J_0(x) = \int_0^{2\pi} e^{-i x \cos\phi} \,\mathrm{d}\phi) \\
&\approx \omega^2 \rho_0 u_0 R^2 \frac{e^{ikr}}{r} \frac{1}{k^2 R^2 \sin^2\theta} \int_0^{k R \sin\theta} z J_0(z) \,\mathrm{d}z \\
&\approx \omega^2 \rho_0 u_0 R^2 \frac{e^{ikr}}{r} \frac{J_1(k R \sin\theta)}{k R \sin\theta} \quad (\text{since } \int_0^z t^n J_{n-1}(t) \,\mathrm{d}t = z^n J_n(z), n > 0).
\end{align}
Thus the directivity can be written
$$
\Xi(\theta, \phi) = -4\pi \omega^2 \rho_0 u_0 R^2 \frac{J_1(k R \sin\theta)}{k R \sin\theta}.
$$
The solution is a little bit different from what you have; I included all the details early on to let you decide if it's the same problem or not since I don't know exactly which problem you're asking about.
Regardless, after going through this, I think I can say the reason Helmholtz's equation doesn't give identically zero back outside of the source is just because of assumptions and approximations. You can plot $(\Delta + k^2)p$ using reasonable values for the constants and see that it does indeed go to zero, just far away from the source. And it doesn't give $f$ back either because $f$ is not a source term, it's a directivity function. Clearly there are some differences in how the two books define their terms, but even if this isn't the same problem, I think what's above is enough to show the point.
This is a little too long to respond in a comment so I'll just put it here. Firstly, thank you! The majority of the answer is from the book though so big props to it. I did say I skipped some steps so it might be useful to go back and see what actually happened to get the solution.
The $\delta$ term in the PDE for $G$ is standard for any differential equation. It happens every single time you do a Green's function approach. I looked through my notes a little as well and I (very conveniently) have written "All motion can be defined by homogeneous wave equation except for at the source location.
$$
\frac{1}{c_0^2} \frac{\partial^2 G}{\partial \tau^2} - \frac{\partial^2 G}{\partial y_i^2} = \delta(x - y, t - \tau).
$$
At this point $(y, \tau)$ we have a 'source term.' A sound source is by definition not sound - it is not a propagating wave. The Green's function represents the wave field produced in $\tau$ and $y$ by a unit impulse at $t$ and $x$."
This is of course is written in the time domain, but of little matter. The concept is the same.
Now there's a trick you can do to get that first expression of $p$. You take the two PDEs for $p$ and $G$, multiply each by the other variable ($G$ and $p$ respectively), take their difference, and integrate over the whole space. Now you have an exact expression for $p$! But it's an integral equation...darn. But (again) with some extra clever trickery, you can simplify things. You can cleanly separate the Laplacian from the terms and make the integrand the divergence of gradients, and now suddenly we're in a position to use the divergence theorem. This is where the text picks up. It has
$$
p(M) = \int_\Sigma p(Q) \frac{\partial G}{\partial \vec{n}_Q}(M, Q) - \frac{\partial p}{\partial \vec{n}}(Q) G(M, Q) \,\mathrm{d}\Sigma(Q).
$$
This is what drove the first expression of $p$ since the normal derivative of the Green's function on the $\Sigma$ plane (the $z$ derivative) is zero and we happen to already know what $\partial_{\vec{n}}p(Q)$ is on $\Sigma$ - it's $\omega^2 \rho_0 u_0$ inside the source and $0$ outside the source. This term is the source, and that's emphasized by the fact that we're performing a convolution of it with the Green's function, which describes the propagation of the wave field.
To answer the questions explicitly, $p$ is a solution to the homogeneous equation outside of the piston. Regarding for wherever $\sin\theta = 0$, the equations don't exactly work like that. You don't get to plug in particular values first and then take derivatives (like the difference between $f'(0)$ and $(f(0))'$ for some nice function $f(x) : \mathbb{R} \to \mathbb{R}$. Here $f'(0)$ might be zero, but $(f(0))'$ is always zero, so trying to say they're the same means nothing). The Green's function moreso depends on the (linear) differential operator and the boundary conditions; it doesn't care at all what the 'forcing' term is in the original differential equation. If everything were zero, including boundary conditions, then no, you wouldn't need a Green's function because everything is zero. No source, no propagation, no sound. Finally, I think classification of homogeneous or inhomogeneous was sorted from what I found and quoted in my notes.
