The CEO with the affair could presumably be replaced if romantic fidelity is important to the company or if the relationship violated company policies, but if it doesn't affect their business choices, maybe it's not needed.
A CEO with strong control is a lot harder to replace, and fighting over replacing such a CEO is likely to happen in public and be a negative for the company. General impulsiveness is, IMHO, more likely to show up in business choices than romantic impulsiveness (if that's what was the basis for the affair), and business choices made for impulsive reasons at the very least increases volitility and may likely reduce expected value. If I'm investing in an established company, I think I want stability and rational choices, not volatility and impulsive choices. But then, I don't invest in individual companies, apart from stock based compensation.
The underlying performance of the company does not change based off of random tweets. If Tesla is good enough to take over the entire automobile industry even the CEO pulling off his pants walking around in public is a separate issue to actual performance.
During a more conservative time... waaay back. A company was doing very well but the CEO had an affair. The public caught wind of the affair and the stock tanked, because people had your exact same philosophy. Those people cared about the behavior of the CEO while ignoring the metrics of the company. Warren Buffet looked at the fundamental performance of the company and saw that it was doing quite well, so he bought it at super low prices.
Eventually the stock price changed and began to reflect the fundamentals of the actual business as people forgot about the behavior of the CEO.
You shouldn't care about his erratic behavior. You should care about the business sector and the overall performance of the company.