Write regression test guidelines

[marcocitus]

It's hard to remember all the rules that make for good regression tests. We should add a document to the wiki that succinctly describes the rules to follow.

Some basic ideas:

If applicable, test whether a new feature:

• shows the expected error messages when used incorrectly
• works with prepared statements
• works in a transaction block
• works in the same transaction as other commands (e.g. DDL)
• works with reference tables
• works with MX
• locks tables and shards appropriately
• ...

(don't need to have all of these all the time, but just good to remember pitfalls)

Do not:

• Show output that depends on prior or concurrent tests
• Use COPY with files
• Show unordered SELECT results
• Show timing
• Show shard IDs
• EXPLAIN without (COSTS OFF)
• Use the same tables across different tests
• Use DEBUG output unless
• ...

(unless absolutely necessary, e.g. EXPLAIN always shows shard IDs)

[anarazel]

Don't use the same tables across different tests

That one I actually quite strongly disagree with - we shouldn't duplicate tables all the time. That slows things down, duplicates code, duplicates data loading (copy from file in the worst cases), etc. There's cases where that's necessary (e.g. when screwing with the table definition), but we really don't need 10 different lineitem_hash copies.

Points I'd make additionally:

• don't duplicate infrastructure (tables, functions, ...), put them into a common file at the beginning
• add somewhat weird tests, without comment explaining why that's correct/useful
• unnecessarily add steps serially, instead of parallel with other tests
• use version dependent things from psql, unless necessary. E.g. \d output changes between 9.6 and 10.

[lithp]

It's probably worth adding reasons for the guidelines as well.

• To iterate quickly while developing locally it's common to change the schedule to only run a small subset of our tests. That's made a lot harder when tests randomly depend on each other!
• When tests re-use definitions from earlier it becomes harder to debug them, "wait, which type is this column again?"
• When tests modify shared global state they become less likely to nicely run in parallel with other tests

---

Don't use the same tables across different tests

That one I actually quite strongly disagree with - we shouldn't duplicate tables all the time. That slows things down, duplicates code, duplicates data loading (copy from file in the worst cases), etc. There's cases where that's necessary (e.g. when screwing with the table definition), but we really don't need 10 different lineitem_hash copies.

Of course we have no need for multiple iterations of setting up big tables like lineitem or supplier but I'm not sure Marco was suggesting that. There are lots of small tables which are created just for the purpose of a few checks and the benefits of having their definitions right next to the tests is well-worth the cost of a few extra create_distributed_table calls.