> Except that you know/expect how exactly it will be executed
You're writing bad tests. Do yourself a favor and write tests that pass "it [satisfies some human expectation]". Stop writing tests that care about how it's implemented.
I would like to know what to do if it fails to deliver results on time on a big enough dataset. Then someone will say hey, don’t use COUNT DISTINCT? But why, tests showed no issues and I don’t care how it’s implemented. What to do with that loop?
You're hitting on the difference between unit tests and integration tests. Unit tests should generally not try to test load limits or timing, while integration tests should have the freedom to load millions of rows into a table and verify it doesn't take too long to query the table. Unit tests should be quick enough to run before every commit, while integration tests run on a shared server with enough resources to exercise limits. Most interesting projects need both unit and integration tests.
Yes, but the original discussion was declarative vs imperative, and tests were introduced only as an analogy by gp. I just showed that it doesn’t really apply, it was not a question on how to test in general.
You can still write your test to express user intent (`it completes in [time window a user expects]`), generate the load your representative of what your user would encounter, and test that. If your test looks for `COUNT DISTINCT`, you aren't verifying that the results are delivered in the time expected, you're testing something else entirely and you may have no idea what a user will experience.
You're writing bad tests. Do yourself a favor and write tests that pass "it [satisfies some human expectation]". Stop writing tests that care about how it's implemented.