[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.

6 other commits on which #2843 depends were also backported, they are:

- #2887 for `SessionState` lifecycle control
- #2675, #2823 & #3060 for major test suite refactoring and bug fixes
- #2164, for Parquet test suites updates
- #2493, for reading `spark.sql.*` configurations
Yes, these changes were introduced while resolving merge conflicts. Will revert them.