Ha! I love it. I guess "There is one here" ≠ "Here is one—there!"
Then the other thing I'm wondering about is not so much how did it get this way, but why? Is "there needs to be" purely an enabler of passive voice? Why don't we just say, "We need a cherry to be on the cupcake"?
(1) The dummy subject is a different word from the location (though etymologically of course they're the same). We can say 'There's one here' - we don't mean it's there. Emphatic 'There's one', pointing at it, uses location 'there', not existential 'there'. The existential one has no semantic content, it's just a subject for a verb that needs one.
And though both are traditionally called adverbs, in fact the dummy subject is a pronoun and the location word is (probably, arguably) a preposition.
(2) I have no idea of the grammar of 'need(s) be' - it's always mystified me. Certainly frozen, anyway, some subjunctive construction or something, so not relevant to the ordinary grammar of Present-day English.
Would you say that "there is a cherry on the cupcake" and "a cherry is there on the cupcake" are semantically equivalent? How about "there needs to be a cherry on the cupcake" versus "a cherry needs to be on the cupcake there"? I know you already addressed the irrationality of the cherry's needing to be somewhere, but what I'm getting at is whether "there" as a subject pronoun in the former instances is distinct from "there" indicating precise location in the latter; and in the latter, is it actually an adverb?
Also, is the lexical marking of "need" as taking "to" before an infinitival a relatively recent convention? Or did "needs be" only ever occur without the infinitival following?
How does it get this way? Consider first a straightforward construction of subject + verb + complement: A cherry is on the cupcake. We can turn this into an existential by introducing the pronoun subject 'there' and displacing the former subject beyond the verb (but still before the complement): There is a cherry on the cupcake. (The CGEL accordingly calls the post-verbal phrase 'a cherry' the displaced subject.)
Now what if this situation does not currently obtain, but should—it is needful? Who or what needs? Well, logically, the cherry doesn't need anything. I might need a new job, a wee, or to be home by seven, but the cherry scarcely 'needs' to be anywhere, even if there needs to be a cherry on the cupcake. So the subject of 'need', when we introduce it, is impersonal or unspecified. Logically: NEED there is a cherry on the cupcake.
Verbs require subjects, however, so the pronoun 'there' raises up to subject of 'need' (which is called a raising verb), leaving a gap in the subject position of 'is': There needs _ is a cherry on the cupcake.
Now 'is' needs to case-mark its subject, but it can't do that to a gap, so it has to be changed to the non-case-marking plain formbe. Also, 'need' is lexically marked as taking 'to' before an infinitival clause ('need her to go' as opposed to 'make', which doesn't, 'make her go'). So the final output with a raised subject of 'need' is: There needs to be a cherry on the cupcake.