Details
Major Description
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.
.
Attachments
Activity
For this cross join of two derived tables first of which uses a window function we also get a wrong result set and it happens for the same reason.
MariaDB [test]> SELECT w2.total AS w2_total, w1.total AS w1_total, u
|
-> FROM
|
-> (
|
-> SELECT flat, type, total,
|
-> COUNT(total) OVER (PARTITION BY flat ORDER BY flat) AS u
|
-> FROM v2
|
-> ) AS w1
|
-> JOIN
|
-> (
|
-> SELECT flat, type, total
|
-> FROM v2
|
-> ) AS w2;
|
+----------+----------+---+
|
| w2_total | w1_total | u |
|
+----------+----------+---+
|
| 200 | 200 | 2 |
|
| 200 | 150 | 2 |
|
| NULL | 200 | 2 |
|
| NULL | 150 | 2 |
|
+----------+----------+---+
|
Rex, please review this fix, see the patch in an email in your mail-box.
Objects of the class Item_outer_ref are fixed in the function fix_inner_refs(). We see in the code of this function the following statement:
if (ref->in_sum_func){Item_sum *sum_func;if (ref->in_sum_func->nest_level > select->nest_level)direct_ref= TRUE;else{for (sum_func= ref->in_sum_func; sum_func &&sum_func->aggr_level >= select->nest_level;sum_func= sum_func->in_sum_func){if (sum_func->aggr_level == select->nest_level){direct_ref= TRUE;break;}}}}else if (ref->found_in_group_by)direct_ref= TRUE;Let's set a breakpoint at the first line of this statement.
For our test case objects of the class Item_outer_ref are created for field references w.tst, w.flat and w.type
from the following subquery used in the definition of the view v2:
When fix_inner_refs() is called at JOIN::prepare for the first instance of v2 used in w1 the Item_outer_ref object created for w.tst is fixed in such a way that ref field points to an Item_direct_ref object. Two other Item_outer_ref objects created for w.flat and w.type point to Item_ref objects after fix_inner_refs() finishes. This is because for the first Item_outer_ref object the field in_sum_func points to the embedding set function item max(w.tst) while for the other two Item_direct_ref objects this field points to 0.
When fix_inner_refs() is called at JOIN::prepare for the second instance of v2 used in w2 all the Item_outer_ref objects points to Item_ref objects after fix_inner_refs() finishes, because all the Item_outer_ref objects have values in the field in_sum_func not equal to 0.
The value of the field in_sum_func is set as the value of context flag THD::LEX::in_sum_func right after the creation of the Item_outer_ref object. The value of THD::LEX::in_sum_func is set to NULL when it's initialized and is set to point to the containing set function in Item_sum::init_sum_func_check(). This value is restored at the very end of Item_sum::check_sum_func(). However if the set function is used in a window function then we leave Item_sum::init_sum_func_check() without restoring the value of THD::LEX::in_sum_func and it remains the same as it was set in Item_sum::init_sum_func_check(). This ultimately leads to a wrong result set stored in w2.