Let me attempt to go just slightly further than the other answers so far. My ability to explain this well may be limited by the fact that I'm not an engineer. I'd welcome any corrections.
Fatigue-related lifetimes
This section details frame life in relation to fatigue, i.e. after repeaated normal use without crashes or damage. Conventional wisdom is that steel and titanium are the most durable frame materials, e.g. [this answer] to a related question1. This requires some unpacking. Many materials experience fatigue after repeated loadings (e.g. pedal strokes). Let's just quote Wikipedia on this bit:
In materials science, fatigue is the weakening of a material caused by cyclic loading that results in progressive and localized structural damage and the growth of cracks. Once a crack has initiated, each loading cycle will grow the crack a small amount, typically producing striations on some parts of the fracture surface. The crack will continue to grow until it reaches a critical size, which occurs when the stress intensity factor of the crack exceeds the fracture toughness of the material, producing rapid propagation and typically complete fracture of the structure.
Steel and titanium, however, have a fatigue limit. That is, any loading below a certain critical amount will not cause fatigue at all. I'm not sure what's the critical amount for each material, however, and I'm not sure what sorts of impacts would exceed it (e.g. I suspect many vehicular impacts exceed that limit for both metals). Aluminum (link to Lennard Zinn) does not have a fatigue limit. Any amount of load will weaken the metal, and it will eventually fatigue and crack.
However, recall that they make airplanes out of aluminum, and planes can be in service for decades. So, depending on the quality of construction, we might expect aluminum frames to be able to have very long lifespans. Indeed, Sheldon Brown's site has a link to a 1997 test of 12 frames by Tour Magazine (translated from German by Damon Rinard). The test contained 4 aluminum frames (e.g. Cannondale CAAD 3), 4 steel frames (e.g. lugged De Rosa SLX, welded Fondriest), some carbon (e.g. Trek OCLV monocoque, Time Helix with carbon tubes in aluminum lugs), and some titanium (e.g. a Merlin Team Road).
These were all high-end frames, and they may have skewed towards light, thin tubing. However, one carbon and two aluminum frames (Trek OCLV, CAAD3, and Principia RSL) made it to the end of the test, while none of the selected steel frames did. So, aluminum may have a theoretical limit, but it may be difficult for amateur cyclists to exceed it in practice.
I am not sure what, specifically, I heard about carbon fiber and fatigue earlier on in my career. Carbon fiber may actually not be very susceptible to fatigue. In this Cyclingtips article, several bicycle composites engineers say that it does not fatigue in the sense discussed above. Carbon fiber frames may last your lifetime, barring damage. The aviation industry is increasingly moving to carbon parts, and again, they would not do so if carbon were fragile. Planes have to have a multi-decade service life because they are extremely expensive.
Manufacturing processes
Again, I'm not a materials engineer, but many of the frames in that 1997 test failed at joints, e.g. at lugs or at weld zones. For titanium, the welds must be done under an inert gas. If any oxygen or nitrogen gets into the weld, it can make the material there brittle, and that will crack. I am not sure what the chemical issues are for steel and aluminum, but I suspect contamination at the welds is also possible. Quoting Zinn again:
If you had a steel or titanium frame, I could make no such prediction of certain fatigue failure. That’s because, if the frame’s designer chooses steel or titanium tubes whose tensile strength and dimensions (wall thickness, diameter, and shape) are such that the stresses seen while riding will never exceed — say, 40 percent of its tensile strength in its heat-affected (i.e., weld) zones — then the frame will last indefinitely in the absence of a crash. Of course, notches or dents or poor welds (or, in the case of steel, rust) will lower that limit (as well as lower the tensile strength) and cause fatigue failure to occur at a lower stress or lower number of cycles.
Zinn also alludes to the heat affected zones in steel or titanium. I believe this means that due to the intense heat during welding, the frame is weaker at the zones affected by heat. I think this is one reason why silver brazing or lugs were a potential alternative to welded steel, as those processes are done at a lower temperature. However, the Tour test shows that lugged steel can also break at the joints. That might be due to contamination in the lugs. We obviously don't know precisely why the frames in the Tour test broke, but reading the table of failures on Brown's site, many of them seemed to be at joints. That makes me think those were failures of manufacturing processes.
To my knowledge, carbon frames are typically made out of pre-formed carbon sheets that are arranged in a mould. Then, resin is added, and they bake the frames. I believe that usually the front and rear triangles are made separately, then bonded together. Carbon frames may have different issues than heat treatment. Raoul Luescher has a Youtube channel where he cuts into crashed carbon frames that are sent to him for evaluation. He frequently finds voids (i.e. holes) in the carbon. He's an ex aeronautics engineer. He seems to indicate that these are issues, but I'm not sure I've seen a detailed explanation. I'd suspect that voids can act the same way as tiny cracks in metal frames, and that repeated loads could cause the void to propagate (i.e. expand) over time, and eventually lead to what we might perceive as a fatigue failure.
Also, carbon fiber is in fact made of many thin and very strong fibers. In one podcast regarding a recent group of carbon fork failures, he said that some forks were designed with fairly square corners in the steerer to help cables pass through the area. He reported that the sharp bends are very stressful for the carbon, and that this can be a point of failure later on. I recall (not able to find the link) to a Youtube video where he discussed handlebars with holes for internal cable routing (for electronic drivetrains), and he said those holes could be a point of failure if the carbon took loads as well. Basically, never mind manufacturing defects per se, some design choices might create points of failure that the bike company engineers weren't expecting in carbon fiber.
Damage
Back to the Zinn passage quoted, dents can be a starting failure point in steel or titanium frames despite their materials characteristics. Obviously, they'd have the same effect on aluminum frames. Many of us have the sense that carbon fiber is fragile, and that probably stems from its known lack of resistance to impact damage. So, you could damage your frame from even relatively small impacts. That might not cause an immediate failure, but it might eventually cause the frame to fail. Steel can rust as well. However, I believe carbon frames may also corrode from salt. Unpainted aluminum can as well.
TL;DR for consumers
Manufacturing process defects and damage are probably the biggest threats to your frame’s life. I would wager that all frames are possibly at risk from these issues.
Be especially careful handling any carbon frame, and any frame built of very light tubing. The conventional wisdom among the Internet forums I frequent is to be skeptical of any used carbon for this reason. Consumers might want to be skeptical of frames which are pushing the performance boundaries of the material involved, e.g. frames with ultralight tubing. Consumers might want to bias their selection towards manufacturers with lifetime warranties, although that does depend on the manufacturer surviving your lifetime, and on you maintaining sufficient records.
In theory, consumers might be able to avoid manufacturers with reputations for frame defects. The problem is, I’m not sure how you would practically assess this aside from gathering anecdotes.
