This answer is taken from The Feynman Lectures on Physics, Vol. II, Ch. 40: The Flow of Dry Water, Section 40-3: Steady flow—Bernoulli’s theorem.
Imagine a bundle of adjacent streamlines which form a stream tube as sketched in the figure.
From equation of continuity, we can write that $A_1 v_1 = A_2 v_2$. Density of the fluid is constant. Now we calculate the work done by the fluid pressure. The work done on the fluid entering at $A_1$ is $p_1 A_1 v_1 \Delta t$ and th work given up at $A_2$ is $p_1 A_1 v_1 \Delta t$. The net work on the fluid between $A_1$ and $A_2$, is therefore
$$p_1 A_1 v_1 \Delta t - p_2 A_2 v_2 \Delta t \tag1$$
which must equal the increase in the energy of a mass $\Delta M$ of fluid in going from $A_1$ to $A_2$. In other words,
$$p_1 A_1 v_1 \Delta t - p_2 A_2 v_2 \Delta t = \Delta M (E_2 - E_1) \tag2$$
where $E_1$ is the energy per unit mass of fluid at $A_1$, and $E_2$ is the energy per unit mass at $A_2$. The energy per unit mass of the fluid can be written as
$$E = \frac{1}{2}v^2 + \phi + U,$$
where $\frac{1}{2}v^2$ is the kinetic energy per unit mass, $\phi$ is the potential energy per unit mass, and $U$ is an additional term which represents the internal energy per unit mass of fluid. The internal energy might correspond, for example, to the thermal energy in a compressible fluid, or to chemical energy. All these quantities can vary from point to point.
Now, I think that this internal energy can also include the net rotational energy of the individual molecules. So, if the fluid is irrotational, then the contribution of the rotational motion of the individual particles will become zero. If the flow is rotational, we cannot guarantee that the rotational energy of the molecules per unit mass is same everywhere.
Using this form for the energies in $(2)$, we have
$$\frac{p_1 v_1 A_1 \Delta t}{\Delta M} - \frac{p_2 v_2 A_2 \Delta t}{\Delta M} = \frac{1}{2}v_2^2 + \phi_2 + U_2 - \frac{1}{2}v_1^2 + \phi_1 + U_1$$
But $\Delta M = \rho A v \Delta t$, so we get
$$\frac{p_1}{\rho} + \frac{1}{2}v_1^2 + \phi_1 + U_1 = \frac{p_2}{\rho} + \frac{1}{2}v_2^2 + \phi_1 + U_2,$$
which is the Bernoulli result with an additional term for the internal energy. If the fluid is incompressible and irrotational, the internal energy term is the same on both sides, and we get again that $$p + \frac{1}{2}\rho v^2 + \rho g y$$ holds along any streamline.
Your observation is correct. For steady irrotational flow of an incompressible, inviscid fluid with constant density, the Euler equation of momentum reduces to
$$\nabla \left(\frac{p}{\rho} + \frac{1}{2} |\mathbf{u}|^2 + \chi \right) = 0$$
Integrating the components with respect to the spatial variables, we get the general solution
$$\frac{p}{\rho} + \frac{1}{2} |\mathbf{u}|^2 + \chi = c(t),$$
where the arbitrary function $t \mapsto c(t)$ changes nothing about the flow field and can be absorbed into the pressure. Recall that the pressure field corresponding to a particular velocity field is never uniquely determined since an arbitrary reference pressure can be added. The pressure and body-force potential only affect the velocity through their spatial gradients and adding an arbitrary function of time has no impact.
More generally for irrotational flow where $\nabla \times \mathbf{u} =0$, the velocity field is the gradient of a potential, $\mathbf{u} = \nabla\phi$, and the momentum equation reduces to
$$\frac{\partial\nabla \phi}{\partial t}= -\nabla \left(\frac{p}{\rho} + \frac{1}{2} |\nabla \phi|^2 + \chi \right),$$
which upon integration yields
$$\frac{\partial \phi}{\partial t}+\frac{p}{\rho} + \frac{1}{2} |\nabla \phi|^2 + \chi = c(t)$$
Again, in steady flow where $\frac{\partial \phi}{\partial t} = 0$, the appearance of the time-dependent $c(t)$ is not excluded.
Best Answer
Bernoulli's equation is really like an energy conservation equation: if you multiply both sides by the mass flow $\dot{m}$ (also assumed constant) you get:
$$\frac12 \dot{m}v^2+\dot{m}gh+\dot{m}\frac{p}{d}=C$$
The terms are all energy per unit of time. The first one, $\frac12 \dot{m}v^2$, represents translational kinetic energy (per unit of time) of the fluid. But there's no term included for rotational kinetic energy (after all, fluid running through conduits rarely rotate!) So using Bernoulli we assume only translational motion of the fluid.
The equation applies only to inviscid fluids because fluids with significant viscosity experience viscous energy losses, which are not conserved: the energy lost due to viscous friction would have to be supplied, for example by extra pressure, to prevent deceleration ($\dot{m}$ decreasing).