I found an interesting bug introduced between 10.3.9 which was working fine and 10.3.34 which is already broken. I tested it up to 10.8.4 and it is still broken.
I have a table of water consumption measurements (attached) in regular times for hot and cold water. And I need to convert it to a daily water consumption table. I'm using this.
In the first step, I get the biggest value from every day, in the second step I evaluate usage by using over partition by and in the last step, I simply join twice the result table from the previous step to get cold and hot data to one line.
You can see result in result.png image, the data from second table are always missing.
It was working just fine for a long time, since I upgraded Mariad DB from 10.3.9 to 10.3.34 , because I upgraded Raspbian OS on my mini server. But the same issue is in the latest 10.8.4 on windows.
The more interesting is that if you will repeat the same question, again and again, it will show sometimes some data in the, maybe not is this small test data set. But I have a thousand lines there.
I did not find what is the reason, it is a combination of several factors.
When I replace "OVER partition by" by something like "rand()" it starts working.
But if I use the exact same "OVER partition by" but with a different table in the previous step it is also working.