The query planner optimizes it. Why would you want a compiler to optimize SQL? The nature of your data affects how it is optimized! The declarative statement to retrieve data must be interpreted based upon the nature of that data. You can't pre-optimize without knowing something about your data, in which case, you are basically storing some of the information outside of the database.

I mean don't use SQL at all. Use a real programming language like Scala, and let Spark (or Flink etc.) do the translation and optimization: https://www.databricks.com/glossary/catalyst-optimizer

I don't understand why anyone would prefer SQL to that for anything beyond a simple SQL query. And it's not just my opinion: industry at large uses Spark for production with complex queries. SQL is for analysts.

Now I’m totally confused. SQL is a syntax for querying data. Spark SQL is SQL. You are talking about a different implementation of a server.

SQL is for analysts? Everyone uses SQL.

Do you use Spark? This is what it looks like:

https://spark.apache.org/examples.html

SQL is just a DSL; there's nothing magical about it. If you ditch it you can get your type safety, composability, and testability back, like so:

https://boringml.com/docs/platforms/spark/testing-dataframes...

Any ORM provides the same features for any SQL database. There is nothing special going on here. If perhaps the database autogenerated a bunch of classes, maybe that’s interesting? I think some projects have introspected a database and created all the boilerplate language classes before.

There is nothing magical about wrapping database objects in language classes. This has been happening forever.

https://docs.sqlalchemy.org/en/20/orm/quickstart.html#select...

Nothing magical about using a function call rather than raw SQL.

No, it does not. Furthermore, if you're using an ORM you're not programming in SQL any more. You're using a poor man's Spark. Spark lets you drop down to SQL in all its APIs too.

I don't understand your argument if you're comfortable with ORMs.

I’m not even sure what we are debating. Spark is for big data work, it isn’t something you would typically use as a general purpose database backing your application. My original point way back is that expecting customers to learn an entirely new paradigm for storing and querying data is a poor decision, and limits your work to niche use cases. Spark has a large niche but it isn’t comparable to MySQL / Postgres or other newish data stores like Dynamo.

Furthermore any competent engineer knows SQL because ORMs are cumbersome and annoying for anything except basic use.