Let $A$ be any ring (commutative with unity). We show (falsely) that $\mathfrak{a} = \mathfrak{a^2}$ for any ideal $\mathfrak{a} \subseteq A$.
Let $$ 0 \longrightarrow \mathfrak{a} \longrightarrow A \longrightarrow A/\mathfrak{a} \longrightarrow 0 $$ be the standard exact sequence which embeds $\mathfrak{a} \hookrightarrow A$ and projects $A$ onto $A/\mathfrak{a}$. Tensoring with $A/\mathfrak{a}$, we obtain a sequence $$ \mathfrak{a} \otimes_A A/\mathfrak{a} \longrightarrow A \otimes_A A/\mathfrak{a} \longrightarrow A/\mathfrak{a} \otimes_A A/\mathfrak{a} \longrightarrow 0 $$ Using the fact that $\mathfrak{a} \otimes A/\mathfrak{a} \cong \mathfrak{a}/\mathfrak{a}^2$, $A \otimes A/\mathfrak{a} \cong A/\mathfrak{a}$, and $A/\mathfrak{a} \otimes_A A/\mathfrak{a} \cong A/\mathfrak{a}$, we derive an exact sequence $$ \mathfrak{a}/\mathfrak{a}^2 \longrightarrow A/\mathfrak{a} \longrightarrow A/\mathfrak{a} \longrightarrow 0 $$ Since $A/\mathfrak{a} \to A/\mathfrak{a}$ is an isomorphism (in particular, injective), we must have $\mathfrak{a} = \mathfrak{a}^2$, which is absurd for arbitrary $A$ and $\mathfrak{a}$.
Where is the error in this proof? I have a feeling that I've missed something silly, but I can't seem to find the hole in the logic.
The mistake is that you conclude from the fact that $\mathfrak{a}/\mathfrak{a}^2 \to A/\mathfrak{a}$ is the zero map that $\mathfrak{a}/\mathfrak{a}^2=0$. This would only work if we know that $\mathfrak{a}/\mathfrak{a}^2 \to A/\mathfrak{a}$ is injective, but since the sequence is only right exact, this doesn't work.
Incidentally, what you have given is a perfectly fine proof for $\mathfrak{a}=\mathfrak{a}^2$ IF we assume that $A/\mathfrak{a}$ is flat. This does happen, e.g. in von Neumann regular rings, where every module is flat. As the proof shows, it is true that every ideal in a von Neumann regular ring is idempotent.