The query is inaccurate because the decimal field of ApsaraDB RDS for MySQL is too large - ApsaraDB RDS

Disclaimer: This article may contain information about third-party products. Such information is for reference only. Alibaba Cloud does not make any guarantee, express or implied, with respect to the performance and reliability of third-party products, as well as potential impacts of operations on the products.

Symptom

When a decimal column is defined with a large precision (for example, decimal(24, 0)), queries that filter on that column return inaccurate results, even when matching data exists.

Cause

This is a known bug in MySQL. When the decimal field size is too large, the query cannot return accurate results.

Solution

Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security. Before making changes, we recommend that you create snapshots or enable RDS log backup. If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

Add an index to the decimal column.

Run the following SQL statement to add an index to the campaign_id column:

alter table mlgtestdecimal add index ind_decimal (`campaign_id`);

To reproduce the issue and verify the fix, use the following complete example:

create table mlgtestdecimal(`campaign_id` decimal(24, 0) NOT NULL, name VARCHAR(50));

insert into mlgtestdecimal(campaign_id, name)
VALUES('XXXXXXX','XXXXXX');

insert into mlgtestdecimal(campaign_id, name)
VALUES('XXXXXXX','XXXXXX');

SELECT *
from mlgtestdecimal
where campaign_id='XXXXXXX'
  and name='XXXXXX';

alter table mlgtestdecimal add index ind_decimal (`campaign_id`);

After adding the index, run the SELECT statement again. The query returns the expected rows.

Applies to

ApsaraDB RDS for MySQL