Copilot commented on code in PR #9401:
URL: https://github.com/apache/cloudstack/pull/9401#discussion_r4537761109
##########
engine/schema/src/main/resources/META-INF/db/views/cloud.host_view.sql:
##########
@@ -110,8 +110,9 @@ FROM
`cloud`.`ha_config` ON ha_config.resource_id=host.id
and ha_config.resource_type='Host'
LEFT JOIN
- `cloud`.`last_annotation_view` ON `last_annotation_view`.`entity_uuid` =
`host`.`uuid`
+ (SELECT `annotations`.`entity_uuid`, `annotations`.`user_uuid`,
MAX(`annotations`.`created`) AS created, `annotations`.`annotation`
+ FROM `cloud`.`annotations` WHERE `annotations`.`removed` IS NULL GROUP BY
`annotations`.`entity_uuid`) AS last_annotated ON last_annotated.entity_uuid =
host.uuid
Review Comment:
The derived table groups by `entity_uuid` but selects non-aggregated columns
(`user_uuid`, `annotation`). This can return an arbitrary
`annotation`/`user_uuid` that does not match the row with `MAX(created)`, and
will fail in environments with `ONLY_FULL_GROUP_BY` enabled. To keep semantics
correct and deterministic, compute `(entity_uuid, max_created)` in one subquery
and then join back to `cloud.annotations` to fetch the corresponding
`annotation` and `user_uuid` (optionally with a tie-break strategy if multiple
rows share the same `created`).
##########
engine/schema/src/main/resources/META-INF/db/views/cloud.host_view.sql:
##########
@@ -72,8 +72,8 @@ SELECT
ha_config.enabled AS `ha_enabled`,
ha_config.ha_state AS `ha_state`,
ha_config.provider AS `ha_provider`,
- `last_annotation_view`.`annotation` AS `annotation`,
- `last_annotation_view`.`created` AS `last_annotated`,
+ last_annotated.annotation AS `annotation`,
+ last_annotated.created AS `last_annotated`,
`user`.`username` AS `username`
Review Comment:
The derived table groups by `entity_uuid` but selects non-aggregated columns
(`user_uuid`, `annotation`). This can return an arbitrary
`annotation`/`user_uuid` that does not match the row with `MAX(created)`, and
will fail in environments with `ONLY_FULL_GROUP_BY` enabled. To keep semantics
correct and deterministic, compute `(entity_uuid, max_created)` in one subquery
and then join back to `cloud.annotations` to fetch the corresponding
`annotation` and `user_uuid` (optionally with a tie-break strategy if multiple
rows share the same `created`).
##########
engine/schema/src/main/resources/META-INF/db/schema-41720to41800.sql:
##########
@@ -499,82 +191,18 @@ CREATE TABLE IF NOT EXISTS
`cloud`.`autoscale_vmgroup_statistics` (
INDEX `i_autoscale_vmgroup_statistics__counter_id`(`counter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-
--- Update Network offering view with supports_vm_autoscaling
-DROP VIEW IF EXISTS `cloud`.`network_offering_view`;
-CREATE VIEW `cloud`.`network_offering_view` AS
- SELECT
- `network_offerings`.`id` AS `id`,
- `network_offerings`.`uuid` AS `uuid`,
- `network_offerings`.`name` AS `name`,
- `network_offerings`.`unique_name` AS `unique_name`,
- `network_offerings`.`display_text` AS `display_text`,
- `network_offerings`.`nw_rate` AS `nw_rate`,
- `network_offerings`.`mc_rate` AS `mc_rate`,
- `network_offerings`.`traffic_type` AS `traffic_type`,
- `network_offerings`.`tags` AS `tags`,
- `network_offerings`.`system_only` AS `system_only`,
- `network_offerings`.`specify_vlan` AS `specify_vlan`,
- `network_offerings`.`service_offering_id` AS `service_offering_id`,
- `network_offerings`.`conserve_mode` AS `conserve_mode`,
- `network_offerings`.`created` AS `created`,
- `network_offerings`.`removed` AS `removed`,
- `network_offerings`.`default` AS `default`,
- `network_offerings`.`availability` AS `availability`,
- `network_offerings`.`dedicated_lb_service` AS `dedicated_lb_service`,
- `network_offerings`.`shared_source_nat_service` AS
`shared_source_nat_service`,
- `network_offerings`.`sort_key` AS `sort_key`,
- `network_offerings`.`redundant_router_service` AS
`redundant_router_service`,
- `network_offerings`.`state` AS `state`,
- `network_offerings`.`guest_type` AS `guest_type`,
- `network_offerings`.`elastic_ip_service` AS `elastic_ip_service`,
- `network_offerings`.`eip_associate_public_ip` AS
`eip_associate_public_ip`,
- `network_offerings`.`elastic_lb_service` AS `elastic_lb_service`,
- `network_offerings`.`specify_ip_ranges` AS `specify_ip_ranges`,
- `network_offerings`.`inline` AS `inline`,
- `network_offerings`.`is_persistent` AS `is_persistent`,
- `network_offerings`.`internal_lb` AS `internal_lb`,
- `network_offerings`.`public_lb` AS `public_lb`,
- `network_offerings`.`egress_default_policy` AS `egress_default_policy`,
- `network_offerings`.`concurrent_connections` AS
`concurrent_connections`,
- `network_offerings`.`keep_alive_enabled` AS `keep_alive_enabled`,
- `network_offerings`.`supports_streched_l2` AS `supports_streched_l2`,
- `network_offerings`.`supports_public_access` AS
`supports_public_access`,
- `network_offerings`.`supports_vm_autoscaling` AS
`supports_vm_autoscaling`,
- `network_offerings`.`for_vpc` AS `for_vpc`,
- `network_offerings`.`service_package_id` AS `service_package_id`,
- GROUP_CONCAT(DISTINCT(domain.id)) AS domain_id,
- GROUP_CONCAT(DISTINCT(domain.uuid)) AS domain_uuid,
- GROUP_CONCAT(DISTINCT(domain.name)) AS domain_name,
- GROUP_CONCAT(DISTINCT(domain.path)) AS domain_path,
- GROUP_CONCAT(DISTINCT(zone.id)) AS zone_id,
- GROUP_CONCAT(DISTINCT(zone.uuid)) AS zone_uuid,
- GROUP_CONCAT(DISTINCT(zone.name)) AS zone_name,
- `offering_details`.value AS internet_protocol
- FROM
- `cloud`.`network_offerings`
- LEFT JOIN
- `cloud`.`network_offering_details` AS `domain_details` ON
`domain_details`.`network_offering_id` = `network_offerings`.`id` AND
`domain_details`.`name`='domainid'
- LEFT JOIN
- `cloud`.`domain` AS `domain` ON FIND_IN_SET(`domain`.`id`,
`domain_details`.`value`)
- LEFT JOIN
- `cloud`.`network_offering_details` AS `zone_details` ON
`zone_details`.`network_offering_id` = `network_offerings`.`id` AND
`zone_details`.`name`='zoneid'
- LEFT JOIN
- `cloud`.`data_center` AS `zone` ON FIND_IN_SET(`zone`.`id`,
`zone_details`.`value`)
- LEFT JOIN
- `cloud`.`network_offering_details` AS `offering_details` ON
`offering_details`.`network_offering_id` = `network_offerings`.`id` AND
`offering_details`.`name`='internetProtocol'
- GROUP BY
- `network_offerings`.`id`;
-
-- Update column 'supports_vm_autoscaling' to 1 if network offerings support Lb
UPDATE `cloud`.`network_offerings`
JOIN `cloud`.`ntwk_offering_service_map`
ON network_offerings.id = ntwk_offering_service_map.network_offering_id
+JOIN (
+ SELECT COUNT(id) AS count FROM `cloud`.`network_offerings` WHERE
supports_vm_autoscaling = 1
+) AS cnt
SET network_offerings.supports_vm_autoscaling = 1
WHERE ntwk_offering_service_map.service = 'Lb'
AND ntwk_offering_service_map.provider IN ('VirtualRouter',
'VpcVirtualRouter', 'Netscaler')
AND network_offerings.removed IS NULL
- AND (SELECT COUNT(id) AS count FROM `network_offering_view` WHERE
supports_vm_autoscaling = 1) = 0;
+ AND cnt.count = 0;
Review Comment:
The derived table exposes a column named `count` (`cnt.count`), which is
easy to confuse with the `COUNT()` function and can be awkward in some SQL
tooling. Rename the alias to something descriptive (e.g., `enabled_count`) and
reference it consistently (optionally quoting with backticks) to make the
intent clearer.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]