[SPARK-3971][SQL] Backport #2843 to branch-1.1

This PR backports #2843 to branch-1.1. The key difference is that this one doesn't support Hive 0.13.1 and thus always returns `0.12.0` when `spark.sql.hive.version` is queried.

5 other commits on which #2843 depends were also backported, they are:

- #2887 (for `SessionState` lifecycle control)
- #2214, #2823 & #3060 (for major test suite refactoring and bug fixes), and
- #2493 (for reading `spark.sql.*` configurations).