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]

Reply via email to