Beware of ROW_NUMBER without ORDER BY
Haven’t posted all summer, but this bug pulled me straight out of the shadows.
I recently faced a mystery that pushed me to the edge of despair.
It seemed like a simple issue at first glance. A report kept changing completely at random.
I spent a good few days chasing it across multiple weeks . Imagine juggling several tables with time travel, all joined together. Trying to catch the bug.
I started to wonder if time travel even worked correctly. I wasn’t able to reproduce previous states, even when all the inputs had data from that exact point in time.
I began to question if I’d make it. The culprit?
Take this as a cautionary tale against using ROW_NUMBER() OVER(PARTITION BY …) without an accompanying ORDER BY.
Tucked into a table somewhere, it haunted me and wreaked havoc. I don’t know if there’s a real use case for it like that — but expect surprises.
Enjoyed this? Here are some related articles you might find useful: