Minecraft wiki entry for Development_versions, associated text: /execute in now respects dimension scaling for relative and local coordinates: the execution position (only the X/Z part) of a command is divided by 8 when traveling from the Overworld to the Nether, and is multiplied by 8 when vice versa.
If a value is added after the tilde, that value is added only after the new scaled position is calculated. For instance, ~5 will first scale the player's current position (multiplying by eight when traveling to the Overworld or dividing by eight when going to the Nether) before adding five to that outputted value.
For example, if a player at position (16,64,16) in Overworld runs command /execute in minecraft:the_nether run tp ~ ~ ~, the player would now be teleported to (2,64,2) in the Nether.
Relatively, if a player at position (2,64,2) in the Nether runs command /execute in minecraft:overworld run tp ~ ~ ~, the player would now be teleported to (16,64,16) in Overworld.
If a player at position (80,64,80) in Overworld runs command /execute in minecraft:the_nether run tp ~ ~ ~5, the player would now be teleported to (10,64,15) in the Nether.
If a player at position (16,64,16) in Overworld runs command /execute in minecraft:the_nether run tp 16 64 16, the player would still be teleported to (16,64,16) in the Nether.
The behavior before this version can be done by running command /execute in <target dimension> positioned as @s run tp @s ~ ~ ~.
Applies to custom dimensions as well.