The correlation coefficient is given by
$$\rho_{XY}=\frac{R_{XY}-\mu_X \, \mu_Y}{\sigma_X \, \sigma_Y}$$
If the product $\mu_X \, \mu_Y \neq 0$ and $\rho_{XY}\neq 0$, then we can have two cases:
$R_{XY}= 0$ when $X$ and $Y$ are orthogonal;
$R_{XY}\neq 0$ when $X$ and $Y$ are not orthogonal to each other;
So I see from Case $1$ that $R_{XY}= 0$ is possible to have when $\rho_{XY}\neq 0$. Is my reasoning correct? Is this not counterintuitive?
Yes, it is possible.
Following this very good paper on the topic, consider two random variables $X$ and $Y$ with the following realizations:
$$X = (1, -5, 3, -1), Y = (5, 1, 1, 3)$$
You have:
$$R_{XY} = E(XY') = E(1\times5 - 5\times1 + 3\times1 - 1\times3) = 0$$
but:
$$\mu_{X} = -\frac{1}{2}, \mu_{Y} = \frac{5}{2} \Rightarrow \rho_{X,Y} \neq 0$$
In general, recall that while both orthogonality and uncorrelation imply linear independence, there's no implication between orthogonality and uncorrelation themselves.
EDITING TO ANSWER COMMENT:
When it comes to stochastic processes, following your notation, we say that $(X_{t})_{t \geq 1}$ $(Y_{t})_{t \geq 1}$ are uncorrelated if:
$$\forall t_{1}, t_{2}, COV_{X,Y}(t_{1}, t_{2}) = R_{XY}(t_{1}, t_{2}) - \mu_{X}(t_{1})\mu_{Y}(t_{2}) = 0 $$
while we say that they're orthogonal if:
$$\forall t_{1}, t_{2}, R_{XY}(t_{1}, t_{2}) = E[X(t_{1})Y(t_{2})'] = 0$$
so the same reasoning as before applies. Here for a broader analysis.