[MDEV-29307] Not working JOIN on virtually created tables Created: 2022-08-15  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.2.43, 10.3.34, 10.4.24, 10.5.15, 10.6.7, 10.7.3, 10.8.2
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Tomáš Procházka Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows/Linux

Attachments: PNG File result.png     File test2.sql.gz    

 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.

.

 Comments   
Comment by Tomáš Procházka [ 2022-08-15 ]

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.

Comment by Tomáš Procházka [ 2022-08-15 ]

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.

Comment by Alice Sherepa [ 2022-08-16 ]

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)

Comment by Tomáš Procházka [ 2022-08-23 ]

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

Comment by Tomáš Procházka [ 2022-08-23 ]

I just tested it with your Create Table and it really works in this setup.

Comment by Alice Sherepa [ 2022-08-24 ]

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'

Comment by Tomáš Procházka [ 2022-08-25 ]

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';

Comment by Alice Sherepa [ 2022-08-25 ]

tprochazka I tried with primary KEY a (`flat`,`type`,`timestamp`) - seems to work this way

Comment by Tomáš Procházka [ 2022-08-26 ]

It is magic. Your suggestion really helps on this small data set, but on a real table with 15 million records sadly not.

Comment by Sergei Golubchik [ 2022-08-26 ]

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.

Generated at Thu Feb 08 10:07:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.