(I'm not the OP, but I will butt in with my two cents.)

Anything that isn't CRUD or a simple aggregation. Real world applications tend to have: oddball joins, subqueries, window functions, case statements and all other sorts of crazy once you get into the value-add parts of the system. What I really need is a clever way to project the value or set into a structure that I can easily process or convert into UI elements.

On the subject of CRUD, I would be really impressed with an ORM that would detect my constraints and enforce them in the application layer. I am a big fan of DRY, but I am a bigger fan of bulletproof relational models that prevent bad data from ever getting to the system. So rather than declaring these sorts of things in the app code, where they won't be enforced in things like stored procs or ad hoc SQL, I'd much rather have the database be the one source of the data constraints. For really complicated things (e.g. validation triggers), I'd want a way to communicate a violation back up to the ORM layer.

Not sure if this is what you wanted, but for some reason I felt compelled to brain dump what I have been thinking on the subject as of late.

Seconded. The worst thing that happened to me was messing up lazy and eager loading and accidentally pulling the whole database into memory which went unnoticed for some time because there was never much data in the database during development. This happened using NHibernate and on my first project in my first job with my first exposure to an O/R mapper; no really bad experience ever since.