so I am curious if I am misunderstanding something here. Will the boosters on the Artemis flight not explode for some reason when they hit the water?

It doesn't matter what we think. The only thing that matters is in the two cases where shuttle SRBs were lost (STS-4 and STS-51-L) would they have been a Yes or a No.

An additional thought.

This market will resolve to "Yes" if the booster for the Artemis II mission explodes at any point during the test from the start of fueling operations to 60 minutes after it makes contact with Earth upon landing. Otherwise, this market will resolve to “No”.

For the shuttle program the SRBs were fueled in Utah, I assume the same for these boosters. So they are already on the clock even before they reach the pad. In 2007 there was train derailment that impacted some SRB segments, you need to know if that would have been a Yes or a No?

Yet, I have noticed on Polymarket there is a market for whether the boosters on the Artemis II test flight will explode, which seems to be priced unexpectedly low.

You can't accurately evaluate the market until you understand what would have happened in these 3 cases. If these aren't considered "explosions" according to the bookie, then there never were in the shuttle program.