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
And one more observation 10.3.34 keep original order according ORDER BY from first step (twater_daily_raw)
But 10.3.34 is already not, this is because I added "ORDER by `date`, `flat`, `type`" there, but it doesn't help with original problem.
Could you please add SHOW CREATE TABLE test2; and the table water from the query - is it test2 or is it smth else?
MariaDB [test]> 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 test2 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;
|
+----------+----------+------+-------+------+------+--------------------+---------------------+-----------+------------+
|
| date | date2 | year | month | day | flat | cold | hot | hot_total | cold_total |
|
+----------+----------+------+-------+------+------+--------------------+---------------------+-----------+------------+
|
| 22-08-10 | 22-08-10 | 2022 | 8 | 10 | 20 | 287.415 | 158.721 | 158.721 | 287.415 |
|
| 22-08-11 | 22-08-11 | 2022 | 8 | 11 | 20 | 0.2259999999999991 | 0.15500000000000114 | 158.876 | 287.641 |
|
+----------+----------+------+-------+------+------+--------------------+---------------------+-----------+------------+
|
2 rows in set (0,004 sec)
|
|
|
MariaDB [test]> show create table test2;
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| test2 | CREATE TABLE `test2` (
|
`timestamp` datetime DEFAULT NULL,
|
`flat` int(11) DEFAULT NULL,
|
`rssi` int(11) DEFAULT NULL,
|
`type` varchar(300) DEFAULT NULL,
|
`value` double DEFAULT NULL,
|
`error_flags` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0,000 sec)
|
|
|
MariaDB [test]> select version();
|
+----------------+
|
| version() |
|
+----------------+
|
| 10.8.4-MariaDB |
|
+----------------+
|
1 row in set (0,000 sec)
|
Sorry. I thought that test2.sql.gz contains also create table 
This is it:
CREATE TABLE `test2` ( |
`timestamp` timestamp NOT NULL DEFAULT current_timestamp(), |
`flat` tinyint(4) unsigned NOT NULL, |
`rssi` tinyint(4) NOT NULL, |
`type` enum(''WATER_COLD'',''WATER_HOT'') COLLATE utf8mb3_czech_ci NOT NULL, |
`value` decimal(9,3) unsigned NOT NULL, |
`error_flags` smallint(5) unsigned NOT NULL, |
PRIMARY KEY (`timestamp`,`flat`,`type`) |
) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_czech_ci PAGE_CHECKSUM=1 ROW_FORMAT=PAGE |
I just tested it with your Create Table and it really works in this setup.
Thank you very much, now I could repeat it, with InnoDB/Aria/Myisam/ on 10.3-10.10
If I drop the index, then results are correct.
CREATE TABLE `test2` ( |
`timestamp` timestamp NOT NULL DEFAULT current_timestamp(), |
`flat` tinyint(4) unsigned NOT NULL, |
`rssi` tinyint(4) NOT NULL, |
`type` enum('WATER_COLD','WATER_HOT') NOT NULL, |
`value` decimal(9,3) unsigned NOT NULL, |
`error_flags` smallint(5) unsigned NOT NULL, |
PRIMARY KEY (`timestamp`,`flat`,`type`) |
) ;
|
|
|
INSERT INTO `test2` VALUES |
('2022-08-10 00:02:42', 20, -77, 'WATER_COLD', 287.211, 0), |
('2022-08-10 00:04:30', 20, -68, 'WATER_HOT', 158.653, 0), |
('2022-08-10 00:07:44', 20, -79, 'WATER_COLD', 287.211, 0), |
('2022-08-10 00:09:28', 20, -68, 'WATER_HOT', 158.653, 0), |
('2022-08-10 00:12:45', 20, -79, 'WATER_COLD', 287.211, 0), |
('2022-08-11 11:02:55', 20, -65, 'WATER_HOT', 158.794, 0), |
('2022-08-11 11:07:57', 20, -67, 'WATER_COLD', 287.474, 0), |
('2022-08-11 11:08:05', 20, -65, 'WATER_HOT', 158.795, 0), |
('2022-08-11 11:13:04', 20, -65, 'WATER_COLD', 287.474, 0), |
('2022-08-11 23:59:16', 20, -71, 'WATER_COLD', 287.641, 0); |
|
|
|
|
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 test2 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 wh.`usage` 'hot', |
wh.total 'hot_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; |
MariaDB [test]> CREATE TABLE `test2` (
|
-> `timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
|
-> `flat` tinyint(4) unsigned NOT NULL,
|
-> `rssi` tinyint(4) NOT NULL,
|
-> `type` enum('WATER_COLD','WATER_HOT') NOT NULL,
|
-> `value` decimal(9,3) unsigned NOT NULL,
|
-> `error_flags` smallint(5) unsigned NOT NULL,
|
-> PRIMARY KEY (`timestamp`,`flat`,`type`)
|
-> ) ENGINE=Aria;
|
Query OK, 0 rows affected (0.044 sec)
|
|
|
MariaDB [test]> INSERT INTO `test2` VALUES
|
-> ('2022-08-10 00:02:42', 20, -77, 'WATER_COLD', 287.211, 0),
|
-> ('2022-08-10 00:04:30', 20, -68, 'WATER_HOT', 158.653, 0),
|
-> ('2022-08-10 00:07:44', 20, -79, 'WATER_COLD', 287.211, 0),
|
-> ('2022-08-10 00:09:28', 20, -68, 'WATER_HOT', 158.653, 0),
|
-> ('2022-08-10 00:12:45', 20, -79, 'WATER_COLD', 287.211, 0),
|
-> ('2022-08-11 11:02:55', 20, -65, 'WATER_HOT', 158.794, 0),
|
-> ('2022-08-11 11:07:57', 20, -67, 'WATER_COLD', 287.474, 0),
|
-> ('2022-08-11 11:08:05', 20, -65, 'WATER_HOT', 158.795, 0),
|
-> ('2022-08-11 11:13:04', 20, -65, 'WATER_COLD', 287.474, 0),
|
-> ('2022-08-11 23:59:16', 20, -71, 'WATER_COLD', 287.641, 0);
|
Query OK, 10 rows affected (0.012 sec)
|
Records: 10 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> 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 test2 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 wh.`usage` 'hot',
|
-> wh.total 'hot_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;
|
+------+-----------+
|
| hot | hot_total |
|
+------+-----------+
|
| NULL | NULL |
|
| NULL | NULL |
|
+------+-----------+
|
2 rows in set (0.011 sec)
|
|
|
MariaDB [test]> explain extended 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 test2 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 wh.`usage` 'hot', wh.total 'hot_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;
|
+------+--------------------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-----------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-----------------------------------------------------------+
|
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
|
| 1 | PRIMARY | <derived6> | ref | key0 | key0 | 12 | wc.date,wc.flat | 2 | 100.00 | Using where |
|
| 6 | DERIVED | <derived7> | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where; Using temporary; Using filesort |
|
| 7 | DERIVED | w | index | NULL | PRIMARY | 6 | NULL | 10 | 100.00 | Using where; Using index; Using temporary; Using filesort |
|
| 8 | DEPENDENT SUBQUERY | sw | eq_ref | PRIMARY | PRIMARY | 6 | func,test.w.flat,test.w.type | 1 | 100.00 | Using index condition |
|
| 4 | DERIVED | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where; Using temporary; Using filesort |
|
| 2 | DERIVED | w | index | NULL | PRIMARY | 6 | NULL | 10 | 100.00 | Using where; Using index; Using temporary; Using filesort |
|
| 3 | DEPENDENT SUBQUERY | sw | eq_ref | PRIMARY | PRIMARY | 6 | func,func,func | 1 | 100.00 | Using index condition |
|
+------+--------------------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-----------------------------------------------------------+
|
8 rows in set, 9 warnings (0.001 sec)
|
|
|
Note (Code 1276): Field or reference 'test.w.timestamp' of SELECT #3 was resolved in SELECT #2
|
Note (Code 1981): Aggregate function 'max()' of SELECT #3 belongs to SELECT #2
|
Note (Code 1276): Field or reference 'test.w.flat' of SELECT #3 was resolved in SELECT #2
|
Note (Code 1276): Field or reference 'test.w.type' of SELECT #3 was resolved in SELECT #2
|
Note (Code 1276): Field or reference 'test.w.timestamp' of SELECT #8 was resolved in SELECT #7
|
Note (Code 1981): Aggregate function 'max()' of SELECT #8 belongs to SELECT #7
|
Note (Code 1276): Field or reference 'test.w.flat' of SELECT #8 was resolved in SELECT #7
|
Note (Code 1276): Field or reference 'test.w.type' of SELECT #8 was resolved in SELECT #7
|
Note (Code 1003): with twater_daily_raw as (/* select#2 */ select date_format(`test`.`w`.`timestamp`,'%y-%m-%d') AS `date`,extract(year from `test`.`w`.`timestamp`) AS `year`,extract(month from `test`.`w`.`timestamp`) AS `month`,extract(day from `test`.`w`.`timestamp`) AS `day`,`test`.`w`.`flat` AS `flat`,`test`.`w`.`type` AS `type`,<expr_cache><`test`.`w`.`timestamp`,max(`test`.`w`.`timestamp`),max(`test`.`w`.`timestamp`),`test`.`w`.`flat`,`test`.`w`.`type`>((/* select#3 */ select `test`.`sw`.`value` from `test`.`test2` `sw` where `test`.`sw`.`timestamp` = max(`test`.`w`.`timestamp`) and `test`.`sw`.`flat` = `test`.`w`.`flat` and `test`.`sw`.`type` = `test`.`w`.`type`)) AS `total`,max(`test`.`w`.`timestamp`) AS `last` from `test`.`test2` `w` where `test`.`w`.`type` = 'water_cold' group by date_format(`test`.`w`.`timestamp`,'%y-%m-%d'),`test`.`w`.`flat` order by date_format(`test`.`w`.`timestamp`,'%y-%m-%d'),`test`.`w`.`flat`), twater_daily_usage as (/* select#4 */ select `twater_daily_raw`.`date` AS `date`,`twater_daily_raw`.`year` AS `year`,`twater_daily_raw`.`month` AS `month`,`twater_daily_raw`.`day` AS `day`,`twater_daily_raw`.`flat` AS `flat`,`twater_daily_raw`.`type` AS `type`,`twater_daily_raw`.`total` AS `total`,`twater_daily_raw`.`last` AS `last`,`twater_daily_raw`.`total` - ifnull(lag(`twater_daily_raw`.`total`,1) over ( partition by `twater_daily_raw`.`type`,`twater_daily_raw`.`flat` order by `twater_daily_raw`.`type`,`twater_daily_raw`.`flat`,`twater_daily_raw`.`date`),0) AS `usage` from `twater_daily_raw` where `twater_daily_raw`.`type` = 'water_cold' order by `twater_daily_raw`.`date`,`twater_daily_raw`.`flat`), water_daily_combined as (/* select#5 */ select `wh`.`usage` AS `hot`,`wh`.`total` AS `hot_total` from `twater_daily_usage` `wc` join `twater_daily_usage` `wh` where `wh`.`date` = `wc`.`date` and `wh`.`flat` = `wc`.`flat` and `wh`.`type` = 'water_hot' and `wc`.`type` = 'water_cold')/* select#1 */ select `wh`.`usage` AS `hot`,`wh`.`total` AS `hot_total` from `twater_daily_usage` `wc` join `twater_daily_usage` `wh` where `wh`.`date` = `wc`.`date` and `wh`.`flat` = `wc`.`flat` and `wh`.`type` = 'water_hot' and `wc`.`type` = 'water_cold'
|
|
|
MariaDB [test]> alter table test2 drop index `primary`;
|
Query OK, 10 rows affected (0.125 sec)
|
Records: 10 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> 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 test2 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 wh.`usage` 'hot', wh.total 'hot_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;
|
+---------+-----------+
|
| hot | hot_total |
|
+---------+-----------+
|
| 158.653 | 158.653 |
|
| 0.142 | 158.795 |
|
+---------+-----------+
|
2 rows in set (0.001 sec)
|
|
|
|
|
MariaDB [test]> explain extended 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 test2 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 wh.`usage` 'hot', wh.total 'hot_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;
|
+------+--------------------+------------+------+---------------+------+---------+-----------------+------+----------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+------------+------+---------------+------+---------+-----------------+------+----------+----------------------------------------------+
|
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
|
| 1 | PRIMARY | <derived6> | ref | key0 | key0 | 12 | wc.date,wc.flat | 2 | 100.00 | Using where |
|
| 6 | DERIVED | <derived7> | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where; Using temporary; Using filesort |
|
| 7 | DERIVED | w | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where; Using temporary; Using filesort |
|
| 8 | DEPENDENT SUBQUERY | sw | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
|
| 4 | DERIVED | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where; Using temporary; Using filesort |
|
| 2 | DERIVED | w | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where; Using temporary; Using filesort |
|
| 3 | DEPENDENT SUBQUERY | sw | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
|
+------+--------------------+------------+------+---------------+------+---------+-----------------+------+----------+----------------------------------------------+
|
8 rows in set, 9 warnings (0.001 sec)
|
|
|
Note (Code 1276): Field or reference 'test.w.timestamp' of SELECT #3 was resolved in SELECT #2
|
Note (Code 1981): Aggregate function 'max()' of SELECT #3 belongs to SELECT #2
|
Note (Code 1276): Field or reference 'test.w.flat' of SELECT #3 was resolved in SELECT #2
|
Note (Code 1276): Field or reference 'test.w.type' of SELECT #3 was resolved in SELECT #2
|
Note (Code 1276): Field or reference 'test.w.timestamp' of SELECT #8 was resolved in SELECT #7
|
Note (Code 1981): Aggregate function 'max()' of SELECT #8 belongs to SELECT #7
|
Note (Code 1276): Field or reference 'test.w.flat' of SELECT #8 was resolved in SELECT #7
|
Note (Code 1276): Field or reference 'test.w.type' of SELECT #8 was resolved in SELECT #7
|
Note (Code 1003): with twater_daily_raw as (/* select#2 */ select date_format(`test`.`w`.`timestamp`,'%y-%m-%d') AS `date`,extract(year from `test`.`w`.`timestamp`) AS `year`,extract(month from `test`.`w`.`timestamp`) AS `month`,extract(day from `test`.`w`.`timestamp`) AS `day`,`test`.`w`.`flat` AS `flat`,`test`.`w`.`type` AS `type`,<expr_cache><`test`.`w`.`timestamp`,max(`test`.`w`.`timestamp`),max(`test`.`w`.`timestamp`),`test`.`w`.`flat`,`test`.`w`.`type`>((/* select#3 */ select `test`.`sw`.`value` from `test`.`test2` `sw` where `test`.`sw`.`timestamp` = max(`test`.`w`.`timestamp`) and `test`.`sw`.`flat` = `test`.`w`.`flat` and `test`.`sw`.`type` = `test`.`w`.`type`)) AS `total`,max(`test`.`w`.`timestamp`) AS `last` from `test`.`test2` `w` where `test`.`w`.`type` = 'water_cold' group by date_format(`test`.`w`.`timestamp`,'%y-%m-%d'),`test`.`w`.`flat` order by date_format(`test`.`w`.`timestamp`,'%y-%m-%d'),`test`.`w`.`flat`), twater_daily_usage as (/* select#4 */ select `twater_daily_raw`.`date` AS `date`,`twater_daily_raw`.`year` AS `year`,`twater_daily_raw`.`month` AS `month`,`twater_daily_raw`.`day` AS `day`,`twater_daily_raw`.`flat` AS `flat`,`twater_daily_raw`.`type` AS `type`,`twater_daily_raw`.`total` AS `total`,`twater_daily_raw`.`last` AS `last`,`twater_daily_raw`.`total` - ifnull(lag(`twater_daily_raw`.`total`,1) over ( partition by `twater_daily_raw`.`type`,`twater_daily_raw`.`flat` order by `twater_daily_raw`.`type`,`twater_daily_raw`.`flat`,`twater_daily_raw`.`date`),0) AS `usage` from `twater_daily_raw` where `twater_daily_raw`.`type` = 'water_cold' order by `twater_daily_raw`.`date`,`twater_daily_raw`.`flat`), water_daily_combined as (/* select#5 */ select `wh`.`usage` AS `hot`,`wh`.`total` AS `hot_total` from `twater_daily_usage` `wc` join `twater_daily_usage` `wh` where `wh`.`date` = `wc`.`date` and `wh`.`flat` = `wc`.`flat` and `wh`.`type` = 'water_hot' and `wc`.`type` = 'water_cold')/* select#1 */ select `wh`.`usage` AS `hot`,`wh`.`total` AS `hot_total` from `twater_daily_usage` `wc` join `twater_daily_usage` `wh` where `wh`.`date` = `wc`.`date` and `wh`.`flat` = `wc`.`flat` and `wh`.`type` = 'water_hot' and `wc`.`type` = 'water_cold'
|
Is there any way how for tmp workaround this issue, please?
When I remove the index I will have possible duplicates in the data set.
I tried to use "IGNORE INDEX" but it didn't help.
I also tried to disable all optimizations and it also did not help
SET SESSION optimizer_switch='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=on,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=on'; |
tprochazka I tried with primary KEY a (`flat`,`type`,`timestamp`) - seems to work this way
It is magic. Your suggestion really helps on this small data set, but on a real table with 15 million records sadly not.
introduced by
commit 5c89386fdbd
|
Author: Sergei Petrunia <sergey@mariadb.com>
|
Date: Mon Feb 7 12:10:18 2022 +0300
|
|
|
MDEV-17785: Window functions not working in ONLY_FULL_GROUP_BY mode
|
|
(Backport Varun Gupta's patch + edit the commit comment)
|
|
Name resolution code produced errors for valid queries with window
|
functions (but not for queries which used aggregate functions as
|
window functions).
|
|
Name resolution code worked incorrectly, because window function
|
objects had is_window_func_sum_expr()=false. This was so, because
|
mark_as_window_func_sum_expr() was only called for aggregate functions
|
used as window functions.
|
|
The fix is to call it for any window function.
|
The following test case already returns a wrong result set:
CREATE TABLE t1 ( |
timest timestamp NOT NULL DEFAULT current_timestamp(), |
flat tinyint(4) unsigned NOT NULL, |
rssi tinyint(4) NOT NULL, |
type enum('WATER_COLD','WATER_HOT') NOT NULL, |
value decimal(9,3) unsigned NOT NULL, |
error_flags smallint(5) unsigned NOT NULL, |
PRIMARY KEY (timest,flat,type) |
) ENGINE=ARIA;
|
|
|
INSERT INTO t1 VALUES |
('2022-08-10 00:04:30', 20, -68, 'WATER_HOT', 158.653, 0), |
('2022-08-11 23:59:16', 20, -71, 'WATER_COLD', 287.641, 0); |
|
|
create view v1 as ( |
select |
flat,
|
type,
|
(select value from t1 sw |
where sw.timest = max(w.timest) and sw.flat = w.flat and sw.type = w.type) as total |
from t1 w |
group by flat, type |
);
|
|
|
select wh.total 'hot_total', wc.total 'cold_total' |
from
|
(
|
select *, |
sum(total) over (partition by type order by type) as u |
from v1 |
where type = 'water_cold' |
) as wc |
join
|
(
|
select * |
from v1 |
where type = 'water_hot' |
) as wh |
on wc.flat = wh.flat; |
|
|
drop table t1; |
MariaDB [test]> select wh.total 'hot_total', wc.total 'cold_total' from ( select *, sum(total) over (partition by type order by type) as u from v1 where type = 'water_cold' ) as wc join ( select * from v1 where type = 'water_hot' ) as wh on wc.flat = wh.flat;
|
+-----------+------------+
|
| hot_total | cold_total |
|
+-----------+------------+
|
| NULL | 287.641 |
|
+-----------+------------+
|
Here's a much simplified test demonstrating the problem:
CREATE TABLE t1 ( |
tst int NOT NULL, |
flat tinyint unsigned NOT NULL, |
type enum('COLD','HOT') NOT NULL, |
value int NOT NULL, |
PRIMARY KEY (tst,flat,type) |
) ENGINE=ARIA;
|
|
|
INSERT INTO t1 VALUES |
(1, 20, 'HOT', 100), |
(2, 20, 'COLD', 200); |
|
|
create view v1 as ( |
select |
flat,
|
type,
|
(select value from t1 sw |
where sw.tst = max(w.tst) and sw.flat = w.flat and sw.type = w.type) as total |
from t1 w |
group by flat, type |
);
|
|
|
select wh.total 'hot_total', wc.total 'cold_total' |
from
|
(
|
select *, |
sum(total) over (partition by type order by type) as u |
from v1 |
where type = 'COLD' |
) as wc |
join
|
(
|
select * |
from v1 |
where type = 'HOT' |
) as wh |
on wc.flat = wh.flat; |
|
|
drop table t1; |
MariaDB [test]> select wh.total 'hot_total', wc.total 'cold_total'
|
-> from
|
-> (
|
-> select *,
|
-> sum(total) over (partition by type order by type) as u
|
-> from v1
|
-> where type = 'COLD'
|
-> ) as wc
|
-> join
|
-> (
|
-> select *
|
-> from v1
|
-> where type = 'HOT'
|
-> ) as wh
|
-> on wc.flat = wh.flat;
|
+-----------+------------+
|
| hot_total | cold_total |
|
+-----------+------------+
|
| NULL | 200 |
|
+-----------+------------+
|
Let's use a table with simpler data types of the columns and a simpler data set in order to simplify analysis of the problem with debugger.
CREATE TABLE t2 ( |
tst int NOT NULL, |
flat tinyint unsigned NOT NULL, |
type tinyint unsigned NOT NULL, |
val int NOT NULL, |
PRIMARY KEY (tst,flat,type) |
) ENGINE=ARIA;
|
|
|
INSERT INTO t2 VALUES |
(5, 20, 2, 100),
|
(7, 20, 2, 150),
|
(9, 20, 1, 200);
|
|
|
CREATE VIEW v2 AS ( |
SELECT |
flat,
|
type,
|
( SELECT val FROM t2 sw |
WHERE sw.tst = MAX(w.tst) AND sw.flat = w.flat AND sw.type = w.type) |
AS total |
FROM t2 w |
GROUP BY flat, type |
);
|
Let's also use set function COUNT instead of SUM in the query:
SELECT w2.total AS w2_total, w1.total AS w1_total |
FROM
|
(
|
SELECT flat, type, total, |
COUNT(total) OVER (PARTITION BY type ORDER BY type) AS u |
FROM v2 |
WHERE type = 1 |
) AS w1 |
JOIN
|
(
|
SELECT flat, type, total |
FROM v2 |
WHERE type = 2 |
) AS w2 |
ON w1.flat = w2.flat; |
With this test case we have a similar wrong result from the executed query:
MariaDB [test]> SELECT w2.total AS w2_total, w1.total AS w1_total
|
-> FROM
|
-> (
|
-> SELECT flat, type, total,
|
-> COUNT(total) OVER (PARTITION BY type ORDER BY type) AS u
|
-> FROM v2
|
-> WHERE type = 1
|
-> ) AS w1
|
-> JOIN
|
-> (
|
-> SELECT flat, type, total
|
-> FROM v2
|
-> WHERE type = 2
|
-> ) AS w2
|
-> ON w1.flat = w2.flat;
|
+----------+----------+
|
| w2_total | w1_total |
|
+----------+----------+
|
| NULL | 200 |
|
+----------+----------+
|
The output from EXPLAIN EXTENDED for the query is as follows:
MariaDB [test]> EXPLAIN EXTENDED
|
-> SELECT w2.total AS w2_total, w1.total AS w1_total
|
-> FROM
|
-> (
|
-> SELECT flat, type, total,
|
-> COUNT(total) OVER (PARTITION BY type ORDER BY type) AS u
|
-> FROM v2
|
-> WHERE type = 1
|
-> ) AS w1
|
-> JOIN
|
-> (
|
-> SELECT flat, type, total
|
-> FROM v2
|
-> WHERE type = 2
|
-> ) AS w2
|
-> ON w1.flat = w2.flat;
|
+------+--------------------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-----------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-----------------------------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
|
| 1 | PRIMARY | <derived6> | ref | key0 | key0 | 1 | w1.flat | 2 | 100.00 | Using where |
|
| 6 | DERIVED | w | index | NULL | PRIMARY | 6 | NULL | 3 | 100.00 | Using where; Using index; Using temporary; Using filesort |
|
| 7 | DEPENDENT SUBQUERY | sw | eq_ref | PRIMARY | PRIMARY | 6 | func,test.w.flat,test.w.type | 1 | 100.00 | Using index condition |
|
| 2 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using temporary |
|
| 4 | DERIVED | w | index | NULL | PRIMARY | 6 | NULL | 3 | 100.00 | Using where; Using index; Using temporary; Using filesort |
|
| 5 | DEPENDENT SUBQUERY | sw | eq_ref | PRIMARY | PRIMARY | 6 | func,func,func | 1 | 100.00 | Using index condition |
|
+------+--------------------+------------+--------+---------------+---------+---------+------------------------------+------+----------+-----------------------------------------------------------+
|
7 rows in set, 9 warnings (0.002 sec)
|
|
|
MariaDB [test]> show warnings;
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1276 | Field or reference 'test.w.tst' of SELECT #5 was resolved in SELECT #4 |
|
| Note | 1981 | Aggregate function 'max()' of SELECT #5 belongs to SELECT #4 |
|
| Note | 1276 | Field or reference 'test.w.flat' of SELECT #5 was resolved in SELECT #4 |
|
| Note | 1276 | Field or reference 'test.w.type' of SELECT #5 was resolved in SELECT #4 |
|
| Note | 1276 | Field or reference 'test.w.tst' of SELECT #7 was resolved in SELECT #6 |
|
| Note | 1981 | Aggregate function 'max()' of SELECT #7 belongs to SELECT #6 |
|
| Note | 1276 | Field or reference 'test.w.flat' of SELECT #7 was resolved in SELECT #6 |
|
| Note | 1276 | Field or reference 'test.w.type' of SELECT #7 was resolved in SELECT #6 |
|
| Note | 1003 | /* select#1 */ select `v2`.`total` AS `w2_total`,`w1`.`total` AS `w1_total` from (/* select#2 */ select `v2`.`flat` AS `flat`,`v2`.`type` AS `type`,`v2`.`total` AS `total`,count(`v2`.`total`) over ( partition by `v2`.`type` order by `v2`.`type`) AS `u` from `test`.`v2` where `v2`.`type` = 1) `w1` join `test`.`v2` where `v2`.`flat` = `w1`.`flat` and `v2`.`type` = 2 |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
9 rows in set (0.000 sec)
|
If we remove the window function from the query the returned result is correct:
MariaDB [test]> SELECT w2.total AS w2_total, w1.total AS w1_total
|
-> FROM
|
-> (
|
-> SELECT flat, type, total
|
-> FROM v2
|
-> WHERE type = 1
|
-> ) AS w1
|
-> JOIN
|
-> (
|
-> SELECT flat, type, total
|
-> FROM v2
|
-> WHERE type = 2
|
-> ) AS w2
|
-> ON w1.flat = w2.flat;
|
+----------+----------+
|
| w2_total | w1_total |
|
+----------+----------+
|
| 150 | 200 |
|
+----------+----------+
|
1 row in set (0.002 sec)
|
The output fom EXPLAIN EXTENDED for this query is:
MariaDB [test]> EXPLAIN EXTENDED
|
-> SELECT w2.total AS w2_total, w1.total AS w1_total
|
-> FROM
|
-> (
|
-> SELECT flat, type, total
|
-> FROM v2
|
-> WHERE type = 1
|
-> ) AS w1
|
-> JOIN
|
-> (
|
-> SELECT flat, type, total
|
-> FROM v2
|
-> WHERE type = 2
|
-> ) AS w2
|
-> ON w1.flat = w2.flat;
|
+------+--------------------+------------+--------+---------------+---------+---------+----------------+------+----------+-----------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+------------+--------+---------------+---------+---------+----------------+------+----------+-----------------------------------------------------------+
|
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
|
| 1 | PRIMARY | <derived6> | ref | key0 | key0 | 1 | v2.flat | 2 | 100.00 | Using where |
|
| 6 | DERIVED | w | index | NULL | PRIMARY | 6 | NULL | 3 | 100.00 | Using where; Using index; Using temporary; Using filesort |
|
| 7 | DEPENDENT SUBQUERY | sw | eq_ref | PRIMARY | PRIMARY | 6 | func,func,func | 1 | 100.00 | Using index condition |
|
| 4 | DERIVED | w | index | NULL | PRIMARY | 6 | NULL | 3 | 100.00 | Using where; Using index; Using temporary; Using filesort |
|
| 5 | DEPENDENT SUBQUERY | sw | eq_ref | PRIMARY | PRIMARY | 6 | func,func,func | 1 | 100.00 | Using index condition |
|
+------+--------------------+------------+--------+---------------+---------+---------+----------------+------+----------+-----------------------------------------------------------+
|
6 rows in set, 9 warnings (0.002 sec)
|
|
|
MariaDB [test]> show warnings;
|
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1276 | Field or reference 'test.w.tst' of SELECT #5 was resolved in SELECT #4 |
|
| Note | 1981 | Aggregate function 'max()' of SELECT #5 belongs to SELECT #4 |
|
| Note | 1276 | Field or reference 'test.w.flat' of SELECT #5 was resolved in SELECT #4 |
|
| Note | 1276 | Field or reference 'test.w.type' of SELECT #5 was resolved in SELECT #4 |
|
| Note | 1276 | Field or reference 'test.w.tst' of SELECT #7 was resolved in SELECT #6 |
|
| Note | 1981 | Aggregate function 'max()' of SELECT #7 belongs to SELECT #6 |
|
| Note | 1276 | Field or reference 'test.w.flat' of SELECT #7 was resolved in SELECT #6 |
|
| Note | 1276 | Field or reference 'test.w.type' of SELECT #7 was resolved in SELECT #6 |
|
| Note | 1003 | /* select#1 */ select `v2`.`total` AS `w2_total`,`v2`.`total` AS `w1_total` from `test`.`v2` join `test`.`v2` where `v2`.`flat` = `v2`.`flat` and `v2`.`type` = 2 and `v2`.`type` = 1 |
|
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
9 rows in set (0.001 sec)
|
If we compare the sub-plans for the derived tables w1 and w2 from the last query we see that they are identical while such sub-plans for the previous query are different. For the query with the window function we have:
| 4 | DERIVED | w | index | NULL | PRIMARY | 6 | NULL | 3 | 100.00 | Using where; Using index; Using temporary; Using filesort |
|
|
|
| 5 | DEPENDENT SUBQUERY | sw | eq_ref | PRIMARY | PRIMARY | 6 | func,func,func | 1 | 100.00 | Using index condition |
|
for w1 and
| 6 | DERIVED | w | index | NULL | PRIMARY | 6 | NULL | 3 | 100.00 | Using where; Using index; Using temporary; Using filesort |
|
|
|
| 7 | DEPENDENT SUBQUERY | sw | eq_ref | PRIMARY | PRIMARY | 6 | func,test.w.flat,test.w.type | 1 | 100.00 | Using index condition |
|
for w2.
For this query we also see an extra line in the plan:
| 2 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using temporary |
|
This line is due to the fact that the optimizer plans to create a column for the window function in the temporary table for w1 though this column is not used outside. Yet this defect has to be fixed in a separate task.
Let's compare how ref objects are created for sub-plans of derived tables w1 and w2 when the query with window function is executed. For w1 we use ref objects func,func,func when accessing sw. For w2 we use ref objects func,test.w.flat,test.w.type when accessing sw. Ref objects are created in create_ref_for_key() when
the function get_store_key() is called for each key used to access the table to be joined. Let's set a breakpoint at the call of get_store_key():
*ref_key++= get_store_key(thd,
|
keyuse,join->const_table_map,
|
&keyinfo->key_part[i],
|
_breakpoint-_> key_buff, maybe_null);
|
As we want to figure out why we have func when creating the second ref object for w1 and test.w.flat when creating the second ref object for w2 we are interested in the second calls of get_store_key() for w1 and w2 correspondingly. We see that for w1 the ref object is created by a constructor of the class store_key_item (that's why we see func in the EXPLAIN output here) while for the w2 the corresponding ref object is created by a constructor of the class store_key_field. Both w1 and w2 use the view v2 defined with a grouping query. The result of grouping is materialized in a temporary table. For each group a record in this table is created while looking through the base table w. At the end of this process the temporary table contains all records needed for w1/w2 while the record buffer for w contains the values of the last record record read from w. This is the record:
(9, 20, 1, 200);
|
When the records of w2 iare formed the values for fields 'flat' and 'type' are read from this record rather then from records of the temporary table. As a result when filling the records for w2 we use the key with 'type'=1 and get the corresponding record from the temporary table that is later is filtered out by the condition 'type'=2. So we get an empty result table for w2. As opposed to this when records of w1 are formed the values for fields 'flat' and 'type' are read from the temporary table and we have the correct result set for w1.
Let's figure out why the constructor store_key_field to build the second and the third ref objects to access table w in the sub-plan for w2. This constructor is chosen if the following condition from the function get_store_key is true:
(keyuse->val->type() == Item::FIELD_ITEM ||
|
(keyuse->val->type() == Item::REF_ITEM &&
|
((((Item_ref*)keyuse->val)->ref_type() == Item_ref::OUTER_REF &&
|
(*(Item_ref**)((Item_ref*)keyuse->val)->ref)->ref_type() ==
|
Item_ref::DIRECT_REF) ||
|
((Item_ref*)keyuse->val)->ref_type() == Item_ref::VIEW_REF) &&
|
keyuse->val->real_item()->type() == Item::FIELD_ITEM))
|
We see that when building the second and the third ref objects in the plan for w2
(gdb) p keyuse->val->type()
|
$12 = Item::REF_ITEM
|
(gdb) p ((Item_ref*)keyuse->val)->ref_type()
|
$11 = Item_ref::OUTER_REF
|
(gdb) p (*(Item_ref**)((Item_ref*)keyuse->val)->ref)->ref_type()
|
$13 = Item_ref::DIRECT_REF
|
while when building the second and the third ref objects in the plan for w2 we have:
(gdp) p keyuse->val->type()
|
$17 = Item::REF_ITEM
|
(gdb) p ((Item_ref*)keyuse->val)->ref_type()
|
$18 = Item_ref::OUTER_REF
|
(gdb) p (*(Item_ref**)((Item_ref*)keyuse->val)->ref)->ref_type()
|
$19 = Item_ref::REF
|
So we need to check how the objects of the class Item_outer_ref are fixed.
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:
SELECT val FROM t2 sw |
WHERE sw.tst = MAX(w.tst) AND sw.flat = w.flat AND sw.type = w.type |
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.
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.
This fix is pushed into 10.5. It has to be merged upstream as it is.
When you print the table from the previous step by SELECT * FROM twater_daily_usage it is completely fine.
When you create the normal table with that data and apply the next JOIN selection to it, there will be normal values instead of null for hot columns.