Wrong result when joining two derived tables over the same view

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.

	WITH

		twater_daily_raw AS (

			SELECT DATE_FORMAT(`timestamp`, '%Y-%m-%d') 'date',

			EXTRACT(YEAR FROM `timestamp`) 'year',

			EXTRACT(MONTH FROM `timestamp`) 'month',

			EXTRACT(DAY FROM `timestamp`) 'day',

			`flat`, `type`,

			(SELECT `value` FROM water sw WHERE sw.`timestamp` = MAX(w.`timestamp`) AND sw.`flat` = w.`flat` AND sw.`type` = w.`type`) 'total',

			MAX(timestamp) 'last'

			FROM `test2` w

			GROUP BY `date`, `flat`, `type`

			ORDER by `date`, `flat`, `type`

		),

		twater_daily_usage AS (

			SELECT *,

			(`total` - IFNULL(LAG(`total`, 1) OVER (partition by `type`, `flat` ORDER by `type`, `flat`, `date`),0)) 'usage'

			FROM `twater_daily_raw`

            ORDER by `date`, `flat`, `type`

		),

		water_daily_combined AS (

			SELECT wc.`date`, wh.`date`'date2', wc.year, wc.month, wc.day, wc.flat,

					wc.`usage` 'cold', wh.`usage` 'hot',

					wh.total 'hot_total', wc.total 'cold_total'

			FROM twater_daily_usage wc, twater_daily_usage wh

			WHERE wc.`date` = wh.`date` AND wc.`flat` = wh.`flat` AND wh.`type` = 'WATER_HOT' AND wc.`type` = 'WATER_COLD'

		)

SELECT * FROM water_daily_combined wdc

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.

.