Another one is MIN and MAX ignore NULL values, which make for some interesting rollback scenarios.
I also swear I have seen a gotcha involving UPDATE WHERE IN and not throwing an error where it should have, which is why I always quadruple check my update statements, but I wasn't able to reproduce it and couldn't find any information online. I haven't seen the issue in so long I forgot what it was, but it would update all rows in your table even if your WHERE clause was proper.
Also OR/AND can return non-null results even if NULL is one side of the operator:
(NULL AND 0) gives 0
(0 AND NULL) gives 0
(NULL AND 1) gives NULL
(1 AND NULL) gives NULL
(NULL AND NULL) gives NULL
(NULL OR 0) gives NULL
(0 OR NULL) gives NULL
(NULL OR 1) gives 1
(1 OR NULL) gives 1
(NULL OR NULL) gives NULL
I also swear I have seen a gotcha involving UPDATE WHERE IN and not throwing an error where it should have, which is why I always quadruple check my update statements, but I wasn't able to reproduce it and couldn't find any information online. I haven't seen the issue in so long I forgot what it was, but it would update all rows in your table even if your WHERE clause was proper.