MySQL Bugs: #115470: The precision of avg () is lost when multiple tables are joined.

Bug #115470	The precision of avg () is lost when multiple tables are joined.
Submitted:	1 Jul 2024 3:25	Modified:	2 Aug 2024 11:53
Reporter:	Sakurajima Mai	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.35	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The precision of avg () is lost when multiple tables are joined.

create table t2(c0 int primary key auto_increment) secondary_engine = hermes;

create table t1(c0 int primary key auto_increment) secondary_engine = hermes;

insert into t1 values(3),(4);
insert into t1 values(5),(6);
insert into t1 values(7),(8);

insert into t2 values(1),(2);
insert into t2 values(5),(6);
insert into t2 values(7),(8);

select AVG('1111110.00000002') from t1 join t2;

mysql> select AVG('1111110.00000002') from t1 join t2;
+-------------------------+
| AVG('1111110.00000002') |
+-------------------------+
|      1111110.0000000198 |
+-------------------------+
1 row in set (0.00 sec)

How to repeat:
create table t2(c0 int primary key auto_increment) secondary_engine = hermes;
create table t1(c0 int primary key auto_increment) secondary_engine = hermes;

insert into t1 values(3),(4);
insert into t1 values(5),(6);
insert into t1 values(7),(8);

insert into t2 values(1),(2);
insert into t2 values(5),(6);
insert into t2 values(7),(8);

select AVG('1111110.00000002') from t1 join t2;

Suggested fix:
When multiple tables are joined, the precision of avg () should not be lost.

Hi Mr. Mai,

Thank you very much for your bug report.

However, MySQL does not have a storage engine named hermes.

You are probably on the wrong forum.

Unsupported.

There is no loss of precision here.
There is an approximation when converting the string value into a double precision floating point value, but this is inherent in the nature of such data types.
Not a bug.

Thank you, Roy ......