Unenumerbrated
Submitter Valts shares an enumerated mystery:
This happened a few years ago (before COVID). A colleague called me over because he couldn't make sense of the results he was getting from some MySQL queries in MySQL Workbench. I don't remember the queries in particular, but let's say select * from payments where client_id=1234 showed 12 rows, all having the successful field set to 1. But when you selected select count(*) from payments where client_id=1234 and successful=1 you got back 0 rows. And this was reproducible on my machine, too. Yet in code similar queries worked just fine.
After a lot of head-scratching and increasingly bizarre hypotheses, we finally figured out what was going on. In the far, far past, in dark days gone by, someone had defined the database field as enum('0', '1'). This SEEMS pretty rational ... until you start reading up how MySQL treats enums. Enums, it turns out, are all STRING values. But in queries you can use either their string values, or their integer indices, starting at 1. So in this case, the string "0" has the integer index of "1", and the string "1" has the integer index of "2". So select * from payments where successful='1' is quite different from select * from payments where successful=1.
For extra confusion, under most other circumstances MySQL normally silently converts between strings and integers as needed. Some ORM/QueryBuilder frameworks even simply quote ALL values and let MySQL handle the conversions.
Facepalms were had.
In the aftermath, I blame the person who defined this field more than I blame MySQL for its bizzare enum conventions. If you're doing enums (which are generally a good thing), then give them proper names like successful and unsuccessful or something. Not 0 and 1.
This code and this database table is still in use today, but luckily it's the only such gotcha in the entire system. Enums in the database are scarce and the rest of them are sane.