> What is the order of the two calls to `query` actually matters, but how could the language know?
The language can't "know the order" of these calls, since they are not ordered. No information is passed from one call to the other, hence neither is in each other's past light cone.
If you want to impose some order, you can introduce a data-dependency between the calls; e.g. returning some sort of value from the "INSERT" call, and incorporating that into the "SELECT" call. Examples include:
- Some sort of 'response' from the database, e.g. indicating success/failure
- GHC's implementation of IO as passing around a unit value for the "RealWorld"
- Lamport clocks
- A hash of the previous state (git, block chains, etc.)
- The 'connection' value itself (most useful in conjunction with linear types, or equivalent, to prevent "stale" connections being re-used)
- Continuation-passing style (passing around the continuation/stacktrace)
> languages that don't strictly define it make this type of thing very error prone
On the contrary, attempting to define a total order on such spatially-separated events is very error prone. Attempting to impose such Newtonian assumptions on real-world systems, from CPU cores to geographically distributed systems, leads to all sorts of inconsistencies and problems.
This is another example of opt-ins being better than defaults. It's more useful and clear to have no implicit order of calculations imposed by default, so that everything is automatically concurrent/distributed. If we want to impose some ordering, we can do so using the above mechanisms.
Attempting to go the other way (trying to run serial programs with concurrent semantics) is awkward and error-prone. See: multithreading.
See also https://en.wikipedia.org/wiki/Relativistic_programming
Note that you haven't specified the database semantics either.
Perhaps the connection points to a 'snapshot' of the contents, like in Datomic; in which case doing an "INSERT" will not affect a "SELECT". In this case, a "SELECT" will only see the results of an "INSERT" if we query against an updated connection (i.e. if we introduce a data dependency!).
Perhaps performing multiple queries against the same connection causes the database history to branch into "multiple worlds": each correct on its own, but mutually-contradictory. That's how distributed systems tend to work; with various concensus algorithms to try and merge these different histories into some eventually-consistent whole.
PS: There is a well-defined answer in this example; since the "INSERT" query is dead code, it should never be evaluated ;)
PPS: Even in the "normal" case of executing these queries like statements, from top-to-bottom, against a "normal" SQL database, the semantics are under-defined. For example, if 'query' is asynchronous, the second query may race against the first (e.g. taking a faster path to a remote database and getting executed first). This can be prevented by making 'query' synchronous; however, that's just another way of saying we need a response from the database (i.e. a data dependency!)