Compiling down to fast bytecode is important and what I am thinking is more of a "pythonesque"/"javaesque" than an actual thing on a python VM / java VM. Limit the allowable syntax, it doesn't need to be full-spectrum python/java where you can recursively declare objects/etc, just what can be mapped into SQL.

Give me Javacard+LINQ that compiles to bytecode with cursors. https://youtu.be/31D94QOo2gY?t=607

(OK not real javacard but... a minimal java equivalent that compiles to C bytecode, with LINQ, and with stack allocated variables/etc.)

Views as subqueries are a good idea though, we don't hardly ever use them so I mostly don't think about it. It takes more authorization to modify a DB object (view) than program code, so we have a political bias against it...

> Problem with query optimization is that it needs to be done at runtime, you can't optimize it in some procedure language easily. The optimal way to retrieve data depends on the number of records in your tables, the where clauses you use, the actual values you are filtering by, the records already in cache, etc.

> 99% of all programmers would not be able to program better performing queries when doing this with a procedural language or streams expressions or it would take them way too long.

That's probably fair but also leaves you at the mercy of the stats/query planner when it doesn't work the way you want.

Maybe a middle ground would be to make the query command an "expectation" and if the plan doesn't match the expectation then a connection flag is raised and you can check that at the end of your session, so if it's set you know to look for that session's query planner data.

lol I know there's no way that wouldn't get muted and I'm sure programmers would end up tilting at the windmill anyway but