For instance if you eat shit and fall laterally the frame is likely to crack :).
When you invest in these kinds of bikes you end up having to get them x-rayed after accidents to verify they aren't having sub-surface cracks and are safe to ride.
Aluminum will also stress crack and just fracture.
Steel is dramatically more ductile and will most likely deform instead of catastrophically failing.
Like "Ozark Trail" bikes sold by Walmart, bikes for toddlers and what not.
These bikes are massively heavy and susceptible to rust.
I have bikes made of steel, aluminium and carbon fibre. I don't worry about the longevity of any of them.
Hydro-formed aluminium is cheap, relatively light and easy to live with IMHO. Horses for courses.
I also think geometry of a frame is far more important than frame material for things like comfort but that's a different story. i.e. how much can the seat post flex.
Maybe if you can find an X-ray lab that offers 2 for 1 coupons, you can get the bike imaged for free.
Friend fell over at a red light from a stop (was clipped in on the pedal on the wrong side and fell over). Had to get frame x-rayed and it was cracked inside. He was fine of course...
Pro tri bikes? I haven't been close to that world in 15 years, but even then the high end started somewhere around $10-15k and looked little more approachable to a less specialized cyclist than these do. You're not wrong they are poorly suited for general-purpose riding, but it would also surprise me quite a lot to see one used that way.
I got my D500 that way, actually. Used, but in perfect condition and with a shutter count below 5000 - per the fellow at the used counter, the former owner had said it was 'too much camera for him.' Considering his error got me an effectively new body at 2/3 the new price, I hope rich folks go on making such mistakes for a long time to come!