Details
Description
I'm not sure this is a bug or not, because there is no such configuration like auto round points.
I thought it connect to div_precision_increment. but seems not
all test configuration is same
MariaDB [(none)]> show variables like '%prec%';
|
+-------------------------+-------+
|
| Variable_name | Value |
|
+-------------------------+-------+
|
| div_precision_increment | 4 |
|
+-------------------------+-------+
|
MariaDB 10.3.24 && 10.4.14
MariaDB [(none)]> select (55/23244*1000);
|
+-----------------+
|
| (55/23244*1000) |
|
+-----------------+
|
| 2.4000 |
|
+-----------------+
|
|
|
select cast( (55/23244*1000) as decimal(5,4));
|
+----------------------------------------+
|
| cast( (55/23244*1000) as decimal(5,4)) |
|
+----------------------------------------+
|
| 2.4000 |
|
+----------------------------------------+
|
|
|
MariaDB [(none)]> set div_precision_increment=8;
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [(none)]> select cast( (55/23244*1000) as decimal(5,4));
|
+----------------------------------------+
|
| cast( (55/23244*1000) as decimal(5,4)) |
|
+----------------------------------------+
|
| 2.3662 |
|
+----------------------------------------+
|
|
|
MariaDB [(none)]> select (55/23244*1000);
|
+-----------------+
|
| (55/23244*1000) |
|
+-----------------+
|
| 2.36620000 |
|
+-----------------+
|
MariaDB 5.5 ~ 10.3.22
MariaDB [(none)]> show variables like '%prec%';
|
+-------------------------+-------+
|
| Variable_name | Value |
|
+-------------------------+-------+
|
| div_precision_increment | 4 |
|
+-------------------------+-------+
|
|
|
MariaDB [(none)]> select (55/23244*1000);
|
+-----------------+
|
| (55/23244*1000) |
|
+-----------------+
|
| 2.3662 |
|
+-----------------+
|
|
|
MariaDB [(none)]> select cast( (55/23244*1000) as decimal(5,4));
|
+----------------------------------------+
|
| cast( (55/23244*1000) as decimal(5,4)) |
|
+----------------------------------------+
|
| 2.3662 |
|
+----------------------------------------+
|
Attachments
Issue Links
- duplicates
-
MDEV-23762 Strange type-inference/rounding problem since >10.3.23
-
- Closed
-
-
-
- Closed
-
- is caused by
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
select (cost/burst)*impression from sometable is actually matter.
div_precision_increment = 8 will provide 24 and some server will provide 23.66.
new versions of mariadb 10.3.24~ and 10.3.22 provides different data.
so solution is using div_precision_increment=32 ?
that depends on with what precision you want the division to be performed.
I don't quite understand, your example above shows that 55/23244*1000 returns 2.36620000 if div_precision_increment = 8.
By the way, if you'd calculate cost*impression/burst it will quite possibly avoid the whole issue altogether.
Metric formula is rely on their own recipes.
In previous version, div_precision_increment seems control on delivered (final) results only,
The newer version seems limits the maximum computational decimal point without any notification on releases notes.
It seems serious changes
div_precision_increment
Description: The precision of the result of the decimal division will be the larger than the precision of the dividend by that number. By default it's 4, so SELECT 2/15 would return 0.1333 and SELECT 2.0/15 would return 0.13333. After setting div_precision_increment to 6, for example, the same operation would return 0.133333 and 0.1333333 respectively.
select 1/7*10000000;
new version - 1429000.0000 ( limit at 4th precisions by default )
old version & mysql & others - 1428571.4200 (unlimited precision for calculation, only control output result)
when if it's not a bug, then need to notify and change the description.
This change was intentional, done to fix MDEV-19232 (where, indeed, division result was properly rounded only when stored in the subquery cache, so enabling the subquery cache caused the comparison to fail).
greenman, could you clarify the description of div_precision_increment and note this change in release notes please?
Hello,
This issue raised from computation of column to column in the table.
When columnstore with cdc adapters using SBR. it causes different value to store.
Also, using SBR with different settings of div_precision_increment causes may lead to store wrong value between replication nodes.
create table a ( val1 int unsigned, val2 int unsigned, val3 decimal(16,8)); |
insert into a (val1,val2) values (1,7); |
select val1/val2 from a; |
+-----------+ |
| val1/val2 |
|
+-----------+ |
| 0.1429 |
|
+-----------+ |
|
|
update a set val3=val1/val2; |
|
|
select * from a; |
+------+------+------------+ |
| val1 | val2 | val3 |
|
+------+------+------------+ |
| 1 | 7 | 0.14290000 |
|
+------+------+------------+ |
Previously intermediate calculation or integer calculation also treat as Decimal. (select 1/7 seems using div_precision_increment = 8, so you seems this is a bug)
But now, integer is treat as integer and following div_precision_increment . Also this setting depends on session & global.
need to inspect that value is stream into session environment on the replications
I'll create cdc adapter issue and replication issues also (need more inspection on my side)
select (55/23244*1000) |
is not about using subquery but in this case the query output is affected by a fix for subquery. I think that fix in MDEV-19232 is not appropriate yet and giving side effect.
A rework on MDEV-19232 would be appreciated. Maybe the round should only be applied when comparing the result, rather than applied directly on the values stored in subquery cache.
brian.t, it wasn't so much a side effect, but an intended effect. For example, (this is before MDEV-19232):
MariaDB [test]> select 1/3*1000, (select 1/3)*1000, x*1000 from (select 1/3 x) x; |
+----------+-------------------+----------+ |
| 1/3*1000 | (select 1/3)*1000 | x*1000 | |
+----------+-------------------+----------+ |
| 333.3333 | 333.3333 | 333.3000 |
|
+----------+-------------------+----------+ |
This shows that moving part of the expression into a subquery sometimes changes the result.
Subquery in the FROM clause returns a different result. But if it'll be merged (which 10.4 cannot do, but future MariaDB versions will be able to), it will behave as the second column. And the second column might change the result depending on the subquery cache. If the intermediate expression can be of the invalid data type, then the result will change when this intermediate expression result is stored somewhere, which is an optimizer decision made at run-time.
Anyway, we're now trying to come up with a solution that will restore the original higher-precision behavior without bringing back all related bugs.
This doesn't look like a bug. The intermediate result is
MariaDB [(none)]> select 55/23244;+----------+| 55/23244 |+----------+| 0.0024 |+----------+That is, both argument have zero digits after the dot, plus div_precision_increment=4, so you get 4 digits after the dot. Looks correct.
Earlier versions ignored div_precision_increment in intermediate calculations and this was a bug, as the result was unpredictable, depending on how exactly the optimizer would decide to evaluate the query and where it (or you) would decide to store the intermediate results.