Open Stack

Hi,

Well i think i figured it out. Following Xena deployment instructions mariadb was installed in version 10.6.5 and it 
seems to be some kind of bug in this version. Upgrading to 10.6.8 fixed this particular issue for me.

I've checked some older and newer versions (10.5.6, 10.8.3) and problematic query behaves there like in 10.6.8.

Here's how I've done my tests if someone is interested:

% docker run --rm --detach --name mariadb-10.6.5 --env MYSQL_ROOT_PASSWORD=test mariadb:10.6.5
% docker run --rm --detach --name mariadb-10.6.8 --env MYSQL_ROOT_PASSWORD=test mariadb:10.6.8

% docker exec -i mariadb-10.6.5 mysql -u root -ptest < tables_dump.sql
% docker exec -i mariadb-10.6.8 mysql -u root -ptest < tables_dump.sql

% docker exec -i mariadb-10.6.5 mysql -u root -ptest -t test < test.sql
+----+--------------------------------------+------------+-------------------+---------+----------+------------------+-------+
| id | uuid                                 | generation | resource_class_id | total   | reserved | allocation_ratio | 
used  |
+----+--------------------------------------+------------+-------------------+---------+----------+------------------+-------+
|  5 | 16f620c0-8c6f-4984-8d58-e2c00d1b32da |         50 |                 0 |     128 |        0 |                2 | 
13318 |
|  5 | 16f620c0-8c6f-4984-8d58-e2c00d1b32da |         50 |                 1 | 1031723 |     2048 |                1 | 
NULL |
|  5 | 16f620c0-8c6f-4984-8d58-e2c00d1b32da |         50 |                 2 |  901965 |        2 |                1 | 
NULL |
+----+--------------------------------------+------------+-------------------+---------+----------+------------------+-------+

% docker exec -i mariadb-10.6.8 mysql -u root -ptest -t test < test.sql
+----+--------------------------------------+------------+-------------------+---------+----------+------------------+-------+
| id | uuid                                 | generation | resource_class_id | total   | reserved | allocation_ratio | 
used  |
+----+--------------------------------------+------------+-------------------+---------+----------+------------------+-------+
|  5 | 16f620c0-8c6f-4984-8d58-e2c00d1b32da |         50 |                 0 |     128 |        0 |                2 | 
    5 |
|  5 | 16f620c0-8c6f-4984-8d58-e2c00d1b32da |         50 |                 1 | 1031723 |     2048 |                1 | 
13312 |
|  5 | 16f620c0-8c6f-4984-8d58-e2c00d1b32da |         50 |                 2 |  901965 |        2 |                1 | 
    1 |
+----+--------------------------------------+------------+-------------------+---------+----------+------------------+-------+

% cat tables_dump.sql
create database test;
connect test;

CREATE TABLE `allocations` (
   `created_at` datetime DEFAULT NULL,
   `updated_at` datetime DEFAULT NULL,
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `resource_provider_id` int(11) NOT NULL,
   `consumer_id` varchar(36) NOT NULL,
   `resource_class_id` int(11) NOT NULL,
   `used` int(11) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `allocations_resource_provider_class_used_idx` (`resource_provider_id`,`resource_class_id`,`used`),
   KEY `allocations_resource_class_id_idx` (`resource_class_id`),
   KEY `allocations_consumer_id_idx` (`consumer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=547 DEFAULT CHARSET=utf8mb3;

CREATE TABLE `inventories` (
   `created_at` datetime DEFAULT NULL,
   `updated_at` datetime DEFAULT NULL,
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `resource_provider_id` int(11) NOT NULL,
   `resource_class_id` int(11) NOT NULL,
   `total` int(11) NOT NULL,
   `reserved` int(11) NOT NULL,
   `min_unit` int(11) NOT NULL,
   `max_unit` int(11) NOT NULL,
   `step_size` int(11) NOT NULL,
   `allocation_ratio` float NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `uniq_inventories0resource_provider_resource_class` (`resource_provider_id`,`resource_class_id`),
   KEY `inventories_resource_class_id_idx` (`resource_class_id`),
   KEY `inventories_resource_provider_id_idx` (`resource_provider_id`),
   KEY `inventories_resource_provider_resource_class_idx` (`resource_provider_id`,`resource_class_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb3;

CREATE TABLE `resource_providers` (
   `created_at` datetime DEFAULT NULL,
   `updated_at` datetime DEFAULT NULL,
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `uuid` varchar(36) NOT NULL,
   `name` varchar(200) DEFAULT NULL,
   `generation` int(11) DEFAULT NULL,
   `root_provider_id` int(11) DEFAULT NULL,
   `parent_provider_id` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `uniq_resource_providers0uuid` (`uuid`),
   UNIQUE KEY `uniq_resource_providers0name` (`name`),
   KEY `resource_providers_name_idx` (`name`),
   KEY `resource_providers_parent_provider_id_idx` (`parent_provider_id`),
   KEY `resource_providers_root_provider_id_idx` (`root_provider_id`),
   KEY `resource_providers_uuid_idx` (`uuid`),
   CONSTRAINT `resource_providers_ibfk_1` FOREIGN KEY (`parent_provider_id`) REFERENCES `resource_providers` (`id`),
   CONSTRAINT `resource_providers_ibfk_2` FOREIGN KEY (`root_provider_id`) REFERENCES `resource_providers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3;

INSERT INTO `allocations` VALUES ('2022-07-07 
23:08:10',NULL,329,5,'b6da8a02-a96c-464e-a6c4-19c96c83dd44',1,12288),('2022-07-07 
23:08:10',NULL,332,5,'b6da8a02-a96c-464e-a6c4-19c96c83dd44',0,4),('2022-07-08 
06:26:28',NULL,335,4,'aec7aaea-10df-451b-b2ce-847099ee0110',1,2048),('2022-07-08 
06:26:28',NULL,338,4,'aec7aaea-10df-451b-b2ce-847099ee0110',0,2),('2022-07-12 
08:53:21',NULL,400,1,'29cf1131-1bb3-4f06-b339-930a4bb055d4',1,16384),('2022-07-12 
08:53:21',NULL,403,1,'29cf1131-1bb3-4f06-b339-930a4bb055d4',0,2),('2022-07-14 
08:24:27',NULL,538,5,'9681447d-57ec-45c7-af48-63be3c7201da',2,1),('2022-07-14 
08:24:27',NULL,541,5,'9681447d-57ec-45c7-af48-63be3c7201da',1,1024),('2022-07-14 
08:24:27',NULL,544,5,'9681447d-57ec-45c7-af48-63be3c7201da',0,1);
INSERT INTO `resource_providers` VALUES ('2022-07-04 11:59:49','2022-07-13 
13:03:08',1,'6ac81bb4-50ef-4784-8a64-9031afeaaa9d','p-os-compute01.openstack.local',50,1,NULL),('2022-07-04 
12:00:49','2022-07-13 
13:03:07',4,'a324b3b9-f8c8-4279-bf63-a27163fcf792','g-os-compute01.openstack.local',42,4,NULL),('2022-07-04 
12:03:57','2022-07-14 08:24:27',5,'16f620c0-8c6f-4984-8d58-e2c00d1b32da','t-os-compute01.openstack.local',50,5,NULL);
INSERT INTO `inventories` VALUES ('2022-07-04 11:59:50','2022-07-11 09:24:04',1,1,0,128,0,1,128,1,2),('2022-07-04 
11:59:50','2022-07-11 09:24:04',4,1,1,1031723,2048,1,1031723,1,1),('2022-07-04 11:59:50','2022-07-11 
09:24:04',7,1,2,901965,2,1,901965,1,1),('2022-07-04 12:01:53','2022-07-04 14:59:53',10,4,0,128,0,1,128,1,2),('2022-07-04 
12:01:53','2022-07-04 14:59:53',13,4,1,1031723,2048,1,1031723,1,1),('2022-07-04 12:01:53','2022-07-04 
14:59:53',16,4,2,901965,2,1,901965,1,1),('2022-07-04 12:03:57','2022-07-14 
07:16:08',17,5,0,128,0,1,128,1,2),('2022-07-04 12:03:57','2022-07-14 
07:09:11',20,5,1,1031723,2048,1,1031723,1,1),('2022-07-04 12:03:57','2022-07-14 07:09:11',23,5,2,901965,2,1,901965,1,1);

% cat test.sql
SELECT
     rp.id, rp.uuid, rp.generation, inv.resource_class_id, inv.total, inv.reserved, inv.allocation_ratio, allocs.used
FROM
     resource_providers AS rp
     JOIN inventories AS inv ON rp.id = inv.resource_provider_id
     LEFT JOIN (
         SELECT
             resource_provider_id, resource_class_id, SUM(used) AS used
         FROM
             allocations
         WHERE
             resource_class_id IN (0, 1, 2)
             AND resource_provider_id IN (5)
         GROUP BY
             resource_provider_id, resource_class_id
     ) AS allocs ON
         inv.resource_provider_id = allocs.resource_provider_id
         AND inv.resource_class_id = allocs.resource_class_id
WHERE
     rp.id IN (5)
     AND inv.resource_class_id IN (0,1,2)
;

Regards,
Przemyslaw Basa