In any given scenario you can find an example where it is "good" and an example where it is "bad" to live in a HCOL area or a LCOL area financially.
Talking "normal", "average", and "median", gives a general perspective for most people.
The median income in San Francisco in 2016 was $84,675 according to the St. Louis Federal Reserve Bank data.
The cost of living in San Francisco is "272.60" per bestplaces.net (compared to a baseline "100" for the average cost of living in the US).
The median income in the US in 2016 $56,516.
Cost of living being 2.726 times the US average in SF would give a roughly equivalent income of the US median being $154,026. However, the actual median income in SF is only 55% of that amount.
The obvious conclusion from that simple set of data is that the "average" household in SF probably has less disposable income than the average household in the US, despite having a higher income.
If you go to the calculator sites, they'll tell you what they calculate an "equivalent' salary to be, but that isn't what "most" people will actually get. The average person will get a significantly lower salary in the HCOL area than their LCOL salary.
That doesn't mean there aren't "winners" or legitimate reasons why it can be preferential or even necessary, but the "average" person in a median income job isn't going to benefit financially by going from a LCOL area to a HCOL area for the relatively small pay increase generally associated with such a move.