Finally I got this resolved myself.
However, this is a good event for learning. Apparently the upgrade process envisioned by Apple is not well-thought through enough. By going through the process with some problematic hardware (well, I don't know beforehand that I have some problematic devices), I noticed that the internal structure of the upgrade process could be improved.
To recap my problem, I downloaded the High Sierra installer, start the upgrade and then ends up with an error message
saying that it cannot be installed. I then fire up the disk utility in the macOS installer, checked the disk and it returned an error of invalid count -69862 or something. I ignored the issue and try again. The installation failed again and I got OSStatus Error 2.
I tried AHT as instructed by the screen. Now it reported 4MEM/61/4000000 blah blah blah. This one indicates that I have a faulty RAM module. And High Sierra said installation cannot proceed with installation and my hard disk (an SSD) is left in a state that is not bootable and locked. I bought some new RAM modules, replace it and hope the installation can proceed without problem. Of course, it did not happen.
Then I .... (skipped one million words) ... tried all sorts of things upgrading downgrading blah blah blah without success. Time machine makes things worse because the macOS is also restored, messing up the already messed up installation. Better restore onto clean hard disk rather than on top of the messed up hard disk. One week passed.
What I can conclude is that during failure, actually there is no way to recover from the installation. It seems that the fact that High Sierra converting HFS+ into APFS for SSD behind the scene might need an escape path. Since I got error during disk check, High Sierra does not know what to do and it just said cannot be installed, leaving all moved files and copied files intact without recovering (no housekeeping for rolling back). And the fact that macOS compresses stuff in RAM but encountered some faulty RAM also might contribute to the behaviour of the installer that I encountered. I also suspect due to RAM issue, the High Sierra installer might got corrupted in memory or during download. I think these two issues are not envisioned because the assumption that RAM is good and hard disk file count is correct seems to be working so far so good.
I can only reinstalled Mountain Lion and reboot into Finder without problem. El Capitan and Sierra both failed in installation and cannot boot into Finder.
Enough for the rampant. If you somehow ends up in situation like me, may be this is your solution:
1) get a big enough hard disk, format it
2) get High Sierra installer and create a bootable one
3) clean install High Sierra onto this newly formatted hard disk
4) use Migration assistant to transfer the users to the new hard disk
5) optionally, format the original hard disk and either clone or repeat step 1-4 to transfer all content back
As a by-product, I also learned how to upgrade the late 2012 iMac myself. If you do not care about warranty, it might be just for you. Kinda fun.
