[CMake] Deprecate the `rpath` option and always append relative rpath #22828

Job	Run time
Upload Event Payload	3s
mac13 ARM64 builtin_zlib=ON	32m 7s
Windows 10 x64 Release	1h 50m 7s
mac14 X64 CMAKE_CXX_STANDARD=20	1h 17m 45s
mac15 ARM64 CMAKE_CXX_STANDARD=23	34m 35s
Windows 10 x86 Release	1h 38m 58s
mac-beta ARM64 CMAKE_CXX_STANDARD=23	58m 15s
fedora42 CMAKE_CXX_STANDARD=23	1h 12m 13s
fedora43 CMAKE_CXX_STANDARD=23	1h 7m 51s
ubuntu2510 CMAKE_CXX_STANDARD=23	1h 19m 43s
alma10	1h 11m 40s
ubuntu22 imt=Off, CMAKE_BUILD_TYPE=Debug	1h 45m 56s
alma9 modules_off runtime_cxxmodules=Off	1h 9m 55s
alma9 march_native CMAKE_BUILD_TYPE=RelWithDebInfo, CMAKE_CXX_FLAGS=-march=native, CMAKE_C_FLAGS=-march=native, builtin_zlib=ON, builtin_zstd=ON	39m 32s
rawhide Fedora pydebug CMAKE_CXX_STANDARD=23	1h 16m 23s
ubuntu2404 CMAKE_BUILD_TYPE=Debug	2h 2m 20s
alma9 CMAKE_BUILD_TYPE=Debug	1h 48m 7s
ubuntu2504 CMAKE_CXX_STANDARD=23	1h 58m 1s
alma10 clang Ninja CMAKE_C_COMPILER=clang, CMAKE_CXX_COMPILER=clang++	1h 2m 5s
debian125 CMAKE_CXX_STANDARD=20, dev=ON, CMAKE_CXX_FLAGS=-Wsuggest-override	1h 14m 19s
alma8	1h 11m 28s
alma9 arm64 CMAKE_BUILD_TYPE=RelWithDebInfo, builtin_zlib=ON, builtin_zstd=ON	56m 49s
	1d 2h 48m 12s

Provide feedback