Apparently valid SQL query gives wrong result (nested WHERE)

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:

The output is incorrect and like this:

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.:

The output then is correct and like this:

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.

Environment

CentOS release 6.7 with MariaDB-server-10.1.8-1.el6.x86_64

Assignee

Sergei Petrunia

Reporter

Thomas Moroder

Labels

None

Components

Sprint

None

Fix versions

Affects versions

Priority

Major
Configure