The reason why there is confusion is that the literal and the figurative are being conflated!
"And when man dies, the body becomes dust and returns to the earth and the spirit returns to God who gave it (Ecclesiastes 3:20, 12:7)."
We are attempting to speak in literal terms when we bring up "the body", but all that results in is confusion because we're conflating it with a metaphor, i.e. "becomes dust and returns to the earth." The statement "the body because dust and returns to the earth" is meaningless because it is neither literally true nor figuratively true. In general, bodies do not literally become dust and return to the earth. Some do(via decomposition), but most bodies do not(some are preserved in tombs for thousands of years). And bodies do not figuratively become dust and return to the earth; humans do! That is, if we're speaking in terms of the metaphor employed in the scriptures themselves, then it is human beings that figuratively "return to the dust", and not bodies. Nowhere in all of scripture is a body said to be "dust", or said to "return to the dust of the ground"; only humans are said to be "dust" or "return to the dust of the ground."
Genesis 2:7 Then Yahweh God formed the man of dust from the ground, and breathed into his nostrils the breath of life; and the man became a living person. (Notice it does not say that God formed man's body from the dust of the ground; it says He formed the man from the dust of the ground.)
Genesis 3:19 By the sweat of your face you shall eat bread, till you return to the ground, for out of it you were taken; for you are dust, and to dust you shall return.” (Notice that it does not say that our bodies return to the ground, for out of it were our bodies taken. Nor does it say that our bodies are dust, and to dust do our bodies return. God is speaking to the man himself. The man himself was taken from the ground, and will return to the ground. The man himself is dust; he himself will return to dust.)
Genesis 18:27 Then Abraham answered, “Now that I have ventured to speak to the Lord—though I am but dust and ashes— (Abraham refers to himself as dust, not to his body as dust.)
Psalm 22:15 My strength is dried up like a potsherd, and my tongue sticks to the roof of my mouth. You lay me in the dust of death. (Notice it says, "you lay ME[human being, living person] in the dust of death," and not "you lay my body in the dust of death.")
Psalm 30:9 “What profit is there in my death, if I go down to the pit? Will the dust praise you? Will it tell of your faithfulness? (Some say that the "dust" in this passage is a reference, not to man himself, but to dead bodies/corpses, i.e. how they are unable to praise God. To that I say, can regular bodies praise God? A body does not praise God; people do. People[human beings, living persons] praise God, not bodies[whether functional or nonfunctional]. When David asks, "will the dust praise you", he doesn't mean, "will a corpse praise you", but "will a dead person praise you". This is fully consistent with the fact that never once is a body said to be dust, but countless times are people said to be dust.)
Psalm 90:3 You return man to dust and say, “Return, O children of man!” (Who returns to dust? Man, not man's body, returns to dust.)
Psalm 103:14 For He knows our frame; He is mindful that we are dust. (Once more, WE are dust; not our bodies.)
Psalm 104:29 When you hide your face, they are dismayed; when you take away their spirit, they die and return to their dust. (Does it say, "their bodies die and their bodies return to the dust"? No, it says, "they[living persons, human beings] die and return to their dust.")
Psalm 146:4 When his spirit departs, he returns to the ground; on that very day his plans perish. (Once more, it is said that human beings return to the ground, i.e. HE returns to the ground. It is not said that our bodies return to the ground.)
Ecclesiastes 3:19-20 For what happens to the children of man and what happens to the beasts is the same; as one dies, so dies the other. They all have the same breath, and man has no advantage over the beasts, for all is vanity. 20 All go to one place. All are from the dust, and to dust all return. (What does the "all" refer to in verse 20? The bodies of beasts and man, or beasts and man themselves? The context is clearly about man and beasts themselves[i.e. verse 19 says "man{not the bodies of man} has no advantage over the beasts{not the bodies of beasts}"]. Thus, Ecclesiastes 3:20 is saying that ALL men[human beings] and beasts[living creatures] return to the dust.)
Job 7:21 Why do You not pardon my transgression and take away my iniquity? For soon I will lie down in the dust; You will seek me, but I will be no more.” (Does Job say that his body will lie down in the dust? No, he says that he will lie down in the dust.)
Job 10:9 Please remember that You molded me like clay. Would You now return me to dust? (Does Job say, "would you now return my body to dust"? No, he says, "would you now return ME to the dust." Once more, human beings, not bodies, return to the dust. We are dust; not our bodies.)
Job 17:16 Will it go down to the bars of Sheol? Shall we descend together into the dust?” (Here Job asks whether he will descend into the dust, to Sheol, together with his hope(see previous verse). He talks about himself descending into the dust; not about his body descending into the dust.)
Job 34:14-15 If He were to determine to do so, If He were to gather His spirit and His breath to Himself, 15 Humanity would perish together, And mankind would return to dust. (This is so clear and unambiguous, it's beautiful. If God were to take our spirits and breath away, all of mankind would perish and return to the dust. It does not say that all bodies would return to the dust, but that mankind[the set of all human beings] itself would return to the dust.)
The point is; human beings return to the dust, not bodies. Hence this, "the body becomes dust and returns to the earth", is erroneous. The entire expression, "man is dust" is figurative; not literal. What is it figurative of? The fragile, perishable nature of mankind(i.e. how we are frail and earthly like dust. Could dust win in a fight against, say, a child? Even children play around in the dust of the ground, digging into the ground with their hands, playfully throwing dirt around like it's nothing. That's what we are like compared to God; He could throw us around like dirt if He wanted to[cf. Psalm 90:3, Job 10:9, Job 34:14-15]). And the entire expression, "man returns to the dust of the ground" is figurative of the fact that those who die have expired, perished completely; they are silent, lifeless, and inactive, just as the dust of the ground is.
Also, God says that man returns to the dust of the ground. You cannot return to something you've never been to. If being "in the dust of the ground" is a metaphor for being silent, inactive, and lifeless(as the dust is), then in order for man to return to a state of silence and inactivity, they would have had to be in such a state beforehand! God is saying that when He formed man, man was nothing but lifeless and inactive; nonexistent. That is, until God, using His own breath, breathed the breath of life into man(Genesis 2:7). We owe our existence to God. If God did not breathe the breath of life into man, then man would never have existed! It follows then that if God takes the breath of life away from man, man ceases to exist, i.e. becomes lifeless and inactive(returns to the dust). Does any scripture affirm this notion? Yes, Job 34:14-15 does! See how it all comes together?
So, when God says that man will return to the dust of the ground, He means that he will return to the state he was in before God breathed the breath of life into him, i.e. silent, inactive, and lifeless.
But all this is where the metaphor ends. "Dust of the ground" ≠ Sheol. Nowhere is that connection made in scripture. "But don't we go to both the dust of the ground and Sheol when we die?" Yes, and no! Before anything else, let's examine your first three alternatives.
(1) The body goes to Sheol: If the body goes to Sheol, then what is Sheol to begin with? Is Sheol in the physical realm? Do the molecules of our deceased bodies go there when we die? Does this even make sense?
The body does not go to Sheol. Nowhere in the Bible is the body said to go to Sheol.
(2) The spirit goes to Sheol: If the spirit goes to Sheol, would that mean that Sheol is a spiritual place in the spiritual realm, just as Heaven is? Further, since the spirit returns to God, it would follow that the presence of God is in Sheol or God is in charge of Sheol somehow.
Yes, all that would follow if the spirit went to Sheol. Unfortunately, nowhere in all of scripture is the spirit of human beings said to go to Sheol or be in Sheol. Thus, the spirit does not go to Sheol. To see why the spirit does not go to Sheol, see my answer here. To see how spirits and souls are distinct(and how the latter is not immortal), see my answer here. To see why it is orders of magnitude more reasonable to believe that the state of the dead is unconsciousness as opposed to consciousness, see my answer here.
(3) Man as a whole goes to Sheol: This alternative doesn't make much sense to be honest. If body and spirit part ways upon death, it wouldn't make sense to say that "man as a whole" goes somewhere, since "man as a whole" (body + spirit) no longer exists.
This is 100% valid. It would be nonsense. To help illustrate, we can make a deductive argument like this;
P1: Man is man because of the unity between the physical and the spiritual(body + spirit).
P2: When man becomes dead, the unity between body and spirit is annihilated.
P3: When something is annihilated, it ceases to exist, i.e. becomes nonexistent.
P4: Man goes to Sheol upon death.
P5: Sheol is a place.
P6: Places exist.
C1: Man(who IS the unity between body and spirit) becomes annihilated upon death(because the unity that made man exist has been annihilated). (Follows deductively from P1 and P2).
C2: Man becomes nonexistent upon death; the state of a dead person is nonexistence. (Follows deductively from P3 and C1)
C3: Man goes to a place upon death(follows deductively from P4 and P5).
C4: Something nonexistent goes to a place that exists(follows deductively from P3, P6, C2, and C3).
Conclusion 4 is nonsensical. Nonexistence is, well, nonexistence! It doesn't exist. Therefore, something that doesn't exist can't go somewhere that does exist. That's impossible and meaningless. Now, let's look at your fourth and last alternative and see if we can alter anything in our deductive argument to make the nonsensical conclusion vanish.
(4) We go nowhere, Ecclesiastes 9:10 is just being metaphorical: In other words, there is no longer a "self" that goes anywhere. Man ceases to exist upon death and Sheol is just a metaphorical term for a state of non-being / non-existence.
So, Sheol itself is not a literal place, but figurative of the state of the dead. If so, then "going to Sheol" would be synonymous with becoming dead, i.e. dying. If the state of those who are dead is nonexistence, then going to Sheol(which is figurative of the state of the dead, i.e. nonexistence) would be synonymous with becoming nonexistent. And "being in Sheol" would be synonymous with being dead, i.e. being nonexistent. Is that what we see in scripture? Yes. Notice the synonymous parallelism in the following passages.
1 Samuel 2:6 The Lord kills and brings to life; he brings down to Sheol and raises up. (Killing[the process of making someone dead] is synonymous with "bringing down to Sheol", i.e. to "go to Sheol" is one and the same as dying.)
Psalm 18:5 the cords of Sheol entangled me; the snares of death confronted me. (David is not in two distinct situations. The cords of Sheol entangle him AND the snares of death confront him, i.e. he is fearful of dying, of going to Sheol.)
Psalm 30:3 O Lord, you have brought up my soul from Sheol; you restored me to life from among those who go down to the pit. (Bringing up from Sheol is synonymous with restoring to life. Being brought up from Sheol would be the opposite of going to Sheol; if going to Sheol entails dying, then being brought up from Sheol entails the opposite of dying, i.e. being resurrected. Here, we see that that is the case, as the definition of "resurrect" is "restore (a dead person) to life".)
As you can see, "going to Sheol" is synonymous with becoming dead. Hence, Sheol is figurative of the state of the dead. Going to Sheol is one and the same as dying(becoming nonexistent), and being in Sheol is one and the same as being dead(being nonexistent). Let's see how our deductive argument changes if we think in terms of Sheol being a metaphor for the state of the dead.
P1: Man is man because of the unity between the physical and the spiritual(body + spirit).
P2: When man becomes dead, the unity between body and spirit is annihilated.
P3: When something is annihilated, it ceases to exist, i.e. becomes nonexistent.
P4: Man figuratively "goes to Sheol" upon death.
P5: Sheol is not a place, but a metaphor for the state of the dead.
C1: Man(who IS the unity between body and spirit) becomes annihilated upon death(because the unity that made man exist has been annihilated). (Follows deductively from P1 and P2).
C2: Man becomes nonexistent upon death; the state of a dead person is nonexistence. (Follows deductively from P3 and C1)
C3: When man "goes to Sheol", he is undergoing death. When man IS in Sheol, he is dead. (Follows deductively from P4 and P5).
This is a valid argument, and we don't get nonsensical conclusions. Everything comes together in perfect harmony. Thus, your fourth option, we go nowhere, Ecclesiastes 9:10 is just being metaphorical is correct!
Hope this helps, have a good day! :)
