As I understand it, a main point is to split the Domain Logic (Business Logic) from the Infrastructure (DB, File System, etc.).
This is the foundation of the misunderstanding: the purpose of DDD isn't to separate things along a hard line like "this is in the SQL server, so must not be BL", the purpose of DDD is to separate domains and create barriers between them that allow the internals of a domain to be completely separate from the internals of another domain, and to define shared externals between them.
Don't think of "being in SQL" as the BL/DL barrier—that's not what it is. Instead, think of "this is the end of the internal domain" as the barrier.
Each domain should have external-facing API's that allow it to work with all the other domains: in the case of the data storage layer, it should have read/write (CRUD) actions for the data-objects it stores. This means SQL itself isn't really the barrier, the
PROCEDURE components are. You should never read directly from the table: that is the implementation detail DDD tells us that, as an external consumer, we should not worry about.
Consider your example:
What I am wondering is, what happens when I have very complex queries like a Material Resource Calculation Query? In that kind of query you work with heavy set operations, the kind of thing that SQL was designed for.
This is exactly what should be in SQL then, and it's not a violation of DDD. It's what we made DDD for. With that calculation in SQL, that becomes part of the BL/DL. What you would do is use a separate view / stored procedure / what-have-you, and keep the business logic separated from the data-layer, as that is your external API. In fact, your data-layer should be another DDD Domain Layer, where your data-layer has it's own abstractions to work with the other domain layers.
Doing these calculations in the infrastructure can't happen too, because the DDD pattern allows for changes in the infrastructure without changing the Domain Layer and knowing that MongoDB doesn't have the same capabilities of e.g. SQL Server, that can't happen.
That's another misunderstanding: it says implementation details internally can change without changing other domain layers. It doesn't say you can just replace a whole infrastructure piece.
Again, keep in mind, DDD is about hiding internals with well-defined external API's. Where those API's sit is a totally different question, and DDD doesn't define that. It simply defines that these API's exist, and should never change.
DDD isn't setup to allow you to ad-hoc replace MSSQL with MongoDB—those are two totally different infrastructure components.
Instead, let's use an analogy for what DDD defines: gas vs. electric cars. Both of the vehicles have two completely different methods for creating propulsion, but they have the same API's: an on/off, a throttle/brake, and wheels to propel the vehicle. DDD says that we should be able to replace the engine (gas or electric) in our car. It doesn't say we can replace the car with a motorcycle, and that's effectively what MSSQL → MongoDB is.