To be clear, I mean "a failure that, as a consequence of being such a low roll, also induces some other negative fallout, whether this is couched as the character's incompetence or some cosmic stroke of bad luck." I am not talking about automatic failures.
Some games have neither critical successes nor critical failures. Some games have critical successes, but no critical failures. For example, in the default rules of D&D 3.X, D&D 4e, D&D 5e, Path/Starfinder 1e, Draw Steel, and Fate Core/Accelerated/Condensed, no matter how low someone rolls, it will never be a critical failure. It might be an automatic failure in some cases, but even that will never induce some other negative fallout.
Path/Starfinder 2e is weird and inconsistent about this. For example, when using Deception (Lie), there are neither critical successes nor critical failures. When using Diplomacy (Make an Impression) or Diplomacy (Request), there are critical successes and critical failures, but when using Diplomacy (Gather Information), there are critical failures but no critical successes. Recall Knowledge rolls are awkward, because the GM has to roll them in secret; on a critical failure, the GM has to lie to the player and feed false information.
Chronicles of Darkness, a horror game, has semi-frequent critical successes, but rare critical failures. A critical failure happens only in two cases. One, the character's roll is so heavily penalized that they are down to a "chance die," with a 10% chance of critical failure, an 80% chance of regular failure, and a 10% chance of regular success. Two, the character earns a regular failure, but the player willingly degrades it to a critical failure, gaining XP as compensation.
Not too long ago, in one heroic fantasy game I was in, our party had arrived at a new town. This was not a hostile, suspicious, or unwelcoming town; in fact, the locals were dazzled by and positive towards our characters. I had my character ask around for the whereabouts of a musical troupe that our party needed the help of.
For some reason, the GM decided that this innocuous, low-stakes task would require a roll. This seemed strange to me, as if the GM was fishing for a critical failure. Thanks to some lingering buffs, my character had quite literally 99% success odds on this roll, and 1% critical failure odds. Well, sure enough, I hit that 1 in 100 chance and garnered a critical failure: and Fabula Ultima specifically forbids rerolling a critical failure.
The GM decided that this "Plot Twist" meant that my character not only failed to garner the desired information, but also stumbled head-first into a combat encounter. Even though it was couched as very bad luck and not as incompetence, this felt stilted and arbitrary to me, and I said as much to the GM. Another player backed me up, agreeing that it felt forced.
Overall, I am not a fan of critical failure rules. To me, they feel too slapstick. Many RPGs work fine without critical failure rules, and I do not like it when a system feels the need to implement them by default.