Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8988

Apparently valid SQL query gives wrong result (nested WHERE)

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1.7, 10.1.8, 5.5, 10.0, 10.1
    • 10.1.12
    • Optimizer
    • None
    • CentOS release 6.7 with MariaDB-server-10.1.8-1.el6.x86_64
    • 5.5.47-1, 5.5.48-0

    Description

      The CPU and memory total capacity as reported by the API and as visible on the CS dashboard are shown as zero. This is due to the following query failing to get correct results when the database is run on MariaDB 10.1:

      SELECT sum(capacity.used_capacity), sum(capacity.reserved_capacity), (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id))else sum(total_capacity) end),((sum(capacity.used_capacity) + sum(capacity.reserved_capacity)) / ( case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) else sum(total_capacity) end)) percent,capacity.capacity_type, capacity.data_center_id, pod_id, cluster_id FROM `cloud`.`op_host_capacity` capacity WHERE  total_capacity > 0 AND data_center_id is not null AND capacity_state='Enabled'  AND capacity.data_center_id = ? AND capacity.cluster_id = ? AND capacity.capacity_type = ? GROUP BY data_center_id, pod_id, cluster_id, capacity_type

      The output is incorrect and like this:

      +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+--------+------------+
      | sum(capacity.used_capacity) | sum(capacity.reserved_capacity) | (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=capacity.cluster_id)) when '0' then (sum(total_capacity) * (select value fro | percent | capacity_type | data_center_id | pod_id | cluster_id |
      +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+--------+------------+
      |                404750336000 |                               0 |                                                                                                                                                                                                                                                             NULL |    NULL |             0 |              3 |      3 |          3 |
      +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+--------+------------+

      After some analysis this seems due to the value of "capacity.cluster_id" as set in the final WHERE-clause not being used in the (nested) previous WHERE clauses like in:

      cluster_details.cluster_id=capacity.cluster_id

      If I manually update the query by specifying the cluster_id-number it works correctly, e.g.:

      SELECT sum(capacity.used_capacity), sum(capacity.reserved_capacity), (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=3)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=3))else sum(total_capacity) end),((sum(capacity.used_capacity) + sum(capacity.reserved_capacity)) / ( case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=3)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'memoryOvercommitRatio' AND cluster_details.cluster_id=3)) else sum(total_capacity) end)) percent,capacity.capacity_type, capacity.data_center_id, pod_id, cluster_id FROM `cloud`.`op_host_capacity` capacity WHERE  total_capacity > 0 AND data_center_id is not null AND capacity_state='Enabled'  AND capacity.data_center_id = 3 AND capacity.cluster_id = 3 AND capacity.capacity_type = 0 GROUP BY data_center_id, pod_id, cluster_id, capacity_type

      The output then is correct and like this:

      +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------+----------------+--------+------------+
      | sum(capacity.used_capacity) | sum(capacity.reserved_capacity) | (case capacity_type when 1 then (sum(total_capacity) * (select value from `cloud`.`cluster_details` where cluster_details.name= 'cpuOvercommitRatio' AND cluster_details.cluster_id=3)) when '0' then (sum(total_capacity) * (select value from `cloud`.`cluster | percent             | capacity_type | data_center_id | pod_id | cluster_id |
      +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------+----------------+--------+------------+
      |                404750336000 |                               0 |                                                                                                                                                                                                                                                     810895474688 | 0.49913996147990797 |             0 |              3 |      3 |          3 |
      +-----------------------------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------+----------------+--------+------------+
      1 row in set (0.00 sec)

      I don't know if this is an issue with MariaDB or correct SQL to use; the query works on older MySQL versions.

      As a workaround an older MySQL-version can be used. If this needs fixing, then the CS management server could specify the cluster_id during the query in all the required places.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            moroder Thomas Moroder
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.