[MDEV-23762] Strange type-inference/rounding problem since >10.3.23 Created: 2020-09-18  Updated: 2020-09-19  Resolved: 2020-09-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.46, 10.3.24, 10.4.14, 10.5.5
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Robin Parker Assignee: Varun Gupta (Inactive)
Resolution: Duplicate Votes: 0
Labels: regression
Environment:

CentOS7

Issue Links:
Duplicate
is duplicated by MDEV-23702 calculating(auto rounding) issue Closed
Problem/Incident
is caused by MDEV-19232 Floating point precision / value comp... Closed

 Description   

Hi,
we have some problems with "POW()" or the inference of the term: (1/(1+(0.0275/12)).
Since upgrading to any version above 10.3.23, the statement 

SELECT POW((1/(1+(0.0275/12))), 59)

yield complete interesting (and wrong) results.

SELECT VERSION(), POW((1/(1+(0.0275/12))), 59), POW((1/(1+(CAST(0.0275/12 as double)))), 59), POW((1.0/(1.0+(0.0275/12.0))), 59)

produces:

# VERSION(), POW((1/(1+(0.0275/12))), 59), POW((1/(1+(CAST(0.0275/12 as double)))), 59), POW((1.0/(1.0+(0.0275/12.0))), 59)
 
'10.3.23-MariaDB', '0.8736690781345217', '0.8736690781345217', '0.8736690438487815'
 
'10.5.5-MariaDB', '0.8729680736160053', '0.8736688724200776', '0.8734844622108874'

Can you explain/confirm/fix this issue? The first Column was our usecase and differs a lot between these versions!

Greetings,
Robin

 Comments   
Comment by Daniel Black [ 2020-09-19 ]

10.3.24

 
mysql -S /tmp/build-mariadb-server-10.3.sock --column-type-info
 
MariaDB [(none)]> select version(), 0.0275/12, 1+(0.0275/12), 1/(1+(0.0275/12)),POW((1/(1+(0.0275/12))), 59);
 
 
 
 
 
Field   1:  `version()`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       VAR_STRING
 
Collation:  utf8_general_ci (33)
 
Length:     45
 
Max_length: 15
 
Decimals:   39
 
Flags:      NOT_NULL 
 
 
Field   2:  `0.0275/12`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       NEWDECIMAL
 
Collation:  binary (63)
 
Length:     11
 
Max_length: 10
 
Decimals:   8
 
Flags:      BINARY NUM 
 
 
Field   3:  `1+(0.0275/12)`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       NEWDECIMAL
 
Collation:  binary (63)
 
Length:     12
 
Max_length: 10
 
Decimals:   8
 
Flags:      BINARY NUM 
 
 
Field   4:  `1/(1+(0.0275/12))`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       NEWDECIMAL
 
Collation:  binary (63)
 
Length:     15
 
Max_length: 6
 
Decimals:   4
 
Flags:      BINARY NUM 
 
 
Field   5:  `POW((1/(1+(0.0275/12))), 59)`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       DOUBLE
 
Collation:  binary (63)
 
Length:     23
 
Max_length: 18
 
Decimals:   31
 
Flags:      BINARY NUM 
 
 
 
 
+-----------------+------------+---------------+-------------------+------------------------------+
 
| version()       | 0.0275/12  | 1+(0.0275/12) | 1/(1+(0.0275/12)) | POW((1/(1+(0.0275/12))), 59) |
 
+-----------------+------------+---------------+-------------------+------------------------------+
 
| 10.3.24-MariaDB | 0.00229167 |    1.00229167 |            0.9977 |           0.8729680736160053 |
 
+-----------------+------------+---------------+-------------------+------------------------------+

mariadb-10.3.23

 
MariaDB [(none)]> select version(), 0.0275/12, 1+(0.0275/12), 1/(1+(0.0275/12)),POW((1/(1+(0.0275/12))), 59);
 
 
 
Field   1:  `version()`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       VAR_STRING
 
Collation:  utf8_general_ci (33)
 
Length:     45
 
Max_length: 15
 
Decimals:   39
 
Flags:      NOT_NULL 
 
 
Field   2:  `0.0275/12`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       NEWDECIMAL
 
Collation:  binary (63)
 
Length:     11
 
Max_length: 10
 
Decimals:   8
 
Flags:      BINARY NUM 
 
 
Field   3:  `1+(0.0275/12)`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       NEWDECIMAL
 
Collation:  binary (63)
 
Length:     12
 
Max_length: 10
 
Decimals:   8
 
Flags:      BINARY NUM 
 
 
Field   4:  `1/(1+(0.0275/12))`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       NEWDECIMAL
 
Collation:  binary (63)
 
Length:     15
 
Max_length: 6
 
Decimals:   4
 
Flags:      BINARY NUM 
 
 
Field   5:  `POW((1/(1+(0.0275/12))), 59)`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       DOUBLE
 
Collation:  binary (63)
 
Length:     23
 
Max_length: 18
 
Decimals:   31
 
Flags:      BINARY NUM 
 
 
 
 
+-----------------+------------+---------------+-------------------+------------------------------+
 
| version()       | 0.0275/12  | 1+(0.0275/12) | 1/(1+(0.0275/12)) | POW((1/(1+(0.0275/12))), 59) |
 
+-----------------+------------+---------------+-------------------+------------------------------+
 
| 10.3.23-MariaDB | 0.00229167 |    1.00229167 |            0.9977 |           0.8736690781345217 |
 
+-----------------+------------+---------------+-------------------+------------------------------+

sub expression difference - none

 
MariaDB [(none)]> select version(),1/(1+(0.0275/12));
 
Field   1:  `version()`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       VAR_STRING
 
Collation:  utf8_general_ci (33)
 
Length:     45
 
Max_length: 15
 
Decimals:   39
 
Flags:      NOT_NULL 
 
 
Field   2:  `1/(1+(0.0275/12))`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       NEWDECIMAL
 
Collation:  binary (63)
 
Length:     15
 
Max_length: 6
 
Decimals:   4
 
Flags:      BINARY NUM 
 
 
 
 
+-----------------+-------------------+
 
| version()       | 1/(1+(0.0275/12)) |
 
+-----------------+-------------------+
 
| 10.3.23-MariaDB |            0.9977 |
 
+-----------------+-------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [(none)]> shutdown;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [(none)]> select version(),1/(1+(0.0275/12));
 
ERROR 2006 (HY000): MySQL server has gone away
 
No connection. Trying to reconnect...
 
Connection id:    8
 
Current database: *** NONE ***
 
 
 
Field   1:  `version()`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       VAR_STRING
 
Collation:  utf8_general_ci (33)
 
Length:     45
 
Max_length: 15
 
Decimals:   39
 
Flags:      NOT_NULL 
 
 
Field   2:  `1/(1+(0.0275/12))`
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       NEWDECIMAL
 
Collation:  binary (63)
 
Length:     15
 
Max_length: 6
 
Decimals:   4
 
Flags:      BINARY NUM 
 
 
 
 
+-----------------+-------------------+
 
| version()       | 1/(1+(0.0275/12)) |
 
+-----------------+-------------------+
 
| 10.3.24-MariaDB |            0.9977 |
 
+-----------------+-------------------+
 
1 row in set (0.001 sec)

So its something in POW.

bisecting

 
[dan@linux mariadb-server-10.3]$ git bisect good  mariadb-10.3.23
 
[dan@linux mariadb-server-10.3]$ git bisect bad  mariadb-10.3.24
 
Bisecting: 224 revisions left to test after this (roughly 8 steps)
 
[838a1046b247e0c70089d3b5cf609c0a40fa3e4b] MDEV-20377: Fix cmake -DPLUGIN_PERFSCHEMA=NO
 
(segv)
 
[dan@linux mariadb-server-10.3]$ git bisect skip
 
Bisecting: 224 revisions left to test after this (roughly 8 steps)
 
[97f7d4a9b4da77cb79699a0ea873e4a0e628e8a3] MDEV-22726: Add check that one can't change general or slow log to a transactional engine
 
 
 
+------------------------------+
 
| POW((1/(1+(0.0275/12))), 59) |
 
+------------------------------+
 
|           0.8736690781345217 |
 
+------------------------------+
 
good
 
[dan@linux mariadb-server-10.3]$ git bisect good
 
Bisecting: 124 revisions left to test after this (roughly 7 steps)
 
[ef7cb0a0b5108b74c23bf6190f7df2cbfe2996a6] Merge branch '10.1' into 10.2
 
+------------------------------+
 
| POW((1/(1+(0.0275/12))), 59) |
 
+------------------------------+
 
|           0.8729680736160053 |
 
+------------------------------+
 
bad
 
[dan@linux mariadb-server-10.3]$ git bisect bad
 
Bisecting: 62 revisions left to test after this (roughly 6 steps)
 
warning: unable to rmdir 'libmariadb': Directory not empty
 
warning: unable to rmdir 'storage/rocksdb/rocksdb': Directory not empty
 
[4860fe244bd1f4a6f3b803648d651e49e7b92a2c] XtraDB 5.6.49-89.0
 
 
 
+------------------------------+
 
| POW((1/(1+(0.0275/12))), 59) |
 
+------------------------------+
 
|           0.8729680736160053 |
 
+------------------------------+
 
bad
 
[dan@linux mariadb-server-10.3]$ git bisect bad
 
Bisecting: 31 revisions left to test after this (roughly 5 steps)
 
[142f85142ad5a5ad5bfea057e13916e9b6609520] Update the InnoDB version number to 5.6.49
 
+------------------------------+
 
| POW((1/(1+(0.0275/12))), 59) |
 
+------------------------------+
 
|           0.8736690781345217 |
 
+------------------------------+
 
good
 
[dan@linux mariadb-server-10.3]$ git bisect good
 
Bisecting: 15 revisions left to test after this (roughly 4 steps)
 
[4b97f14a3d692721a089afe8050a00fcfc230816] mysql_install_db: help lists --defaults-file twice
 
+------------------------------+
 
| POW((1/(1+(0.0275/12))), 59) |
 
+------------------------------+
 
|           0.8729680736160053 |
 
+------------------------------+
 
bad
 
[dan@linux mariadb-server-10.3]$ git bisect bad
 
Bisecting: 7 revisions left to test after this (roughly 3 steps)
 
[98e2c17e9e20898bc8c1e58c57e0666502ec447e] Cleanup: Remove fil_check_adress_in_tablespace()
 
 
 
+------------------------------+
 
| POW((1/(1+(0.0275/12))), 59) |
 
+------------------------------+
 
|           0.8736690781345217 |
 
+------------------------------+
 
good
 
[dan@linux mariadb-server-10.3]$ git bisect good
 
Bisecting: 3 revisions left to test after this (roughly 2 steps)
 
[52ccedd6dd50e8e5cd8ca9b1be84bc5e87c20950] MDEV-23268 SIGSEGV on srv_monitor_event if InnoDB is read-only
 
+------------------------------+
 
| POW((1/(1+(0.0275/12))), 59) |
 
+------------------------------+
 
|           0.8729680736160053 |
 
+------------------------------+
 
bad
 
Bisecting: 1 revision left to test after this (roughly 1 step)
 
[62d73df6b270cc94ba577e96d3bf325170f306fe] MDEV-19232: Floating point precision / value comparison problem
 
+------------------------------+
 
| POW((1/(1+(0.0275/12))), 59) |
 
+------------------------------+
 
|           0.8729680736160053 |
 
+------------------------------+
 
bad
 
[dan@linux mariadb-server-10.3]$ git bisect bad
 
Bisecting: 0 revisions left to test after this (roughly 0 steps)
 
[57ec42bc321dee796ce8e711a4499cd665513009] MDEV-23190 InnoDB data file extension is not crash-safe
 
+------------------------------+
 
| POW((1/(1+(0.0275/12))), 59) |
 
+------------------------------+
 
|           0.8736690781345217 |
 
+------------------------------+
 
good
 
[dan@linux mariadb-server-10.3]$ git bisect good
 
62d73df6b270cc94ba577e96d3bf325170f306fe is the first bad commit
 
commit 62d73df6b270cc94ba577e96d3bf325170f306fe
 
Author: Varun Gupta <varun.gupta@mariadb.com>
 
Date:   Wed Jul 22 14:44:25 2020 +0530
 
 
 
    MDEV-19232: Floating point precision / value comparison problem
 
    
    The issue occurs when the subquery_cache is enabled.
 
    When there is a cache miss the division was leading to a value with scale 9.
 
    In the case of cache hit the value returned was of scale 9 and due to the different
 
    values for the scales the where condition evaluated to FALSE, hence the output
 
    was incomplete.
 
    
    To fix this problem we need to round up the decimal to the limit mentioned in
 
    Item::decimals. This would make sure the values are compared with the same
 
    scale.
 
 
 
 mysql-test/r/func_group.result                     |  4 ++--
 
 mysql-test/r/parser_precedence.result              |  2 +-
 
 mysql-test/r/subselect4.result                     | 24 ++++++++++++++++++++
 
 mysql-test/r/type_newdecimal.result                |  5 -----
 
 mysql-test/r/type_ranges.result                    |  6 ++---
 
 .../sys_vars/r/div_precision_increment_func.result | 16 ++++++-------
 
 mysql-test/suite/vcol/r/not_supported.result       |  2 +-
 
 mysql-test/t/subselect4.test                       | 26 ++++++++++++++++++++++
 
 sql/item_func.cc                                   |  2 ++
 
 .../mysql-test/tokudb/r/type_newdecimal.result     |  5 -----
 
 .../tokudb/mysql-test/tokudb/r/type_ranges.result  |  6 ++---
 
 11 files changed, 68 insertions(+), 30 deletions(-)
 
 
 
[dan@linux mariadb-server-10.3]$ git checkout origin/10.1
 
Previous HEAD position was 57ec42bc321 MDEV-23190 InnoDB data file extension is not crash-safe
 
HEAD is now at 873cc1e77a7 MDEV-21839: Handle crazy offset to SHOW BINLOG EVENTS
 
 
 
[dan@linux mariadb-server-10.3]$ git revert 62d73df6b270cc94ba577e96d3bf325170f306fe
 
Auto-merging sql/item_func.cc
 
Auto-merging mysql-test/t/subselect4.test
 
CONFLICT (content): Merge conflict in mysql-test/t/subselect4.test
 
Auto-merging mysql-test/r/type_newdecimal.result
 
Auto-merging mysql-test/r/subselect4.result
 
CONFLICT (content): Merge conflict in mysql-test/r/subselect4.result
 
error: could not revert 62d73df6b27... MDEV-19232: Floating point precision / value comparison problem
 
hint: after resolving the conflicts, mark the corrected paths
 
hint: with 'git add <paths>' or 'git rm <paths>'
 
hint: and commit the result with 'git commit'
 
 
 
+------------------------------+
 
| POW((1/(1+(0.0275/12))), 59) |
 
+------------------------------+
 
|           0.8736690781345217 |
 
+------------------------------+
 
 
 
good

Commit to blame - 62d73df6b270cc94ba577e96d3bf325170f306fe

Comment by Daniel Black [ 2020-09-19 ]

postgres-12.4

 
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2373d2ac6cba2ad3ed0bf7c76a04e2d2
 
    SELECT VERSION(), POW((1/(1+(0.0275/12))), 59),POW((1.0/(1.0+(0.0275/12.0))), 59)
 
 
 
version                                                                                                |                    pow |                    pow
 
:----------------------------------------------------------------------------------------------------- | ---------------------: | ---------------------:
 
PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit | 0.87366904384877861658 | 0.87366904384877861658


>     SELECT  POWER((1/(1+(0.0275/12))), 59),POWER((1.0/(1.0+(0.0275/12.0))), 59)
 
> 
> <pre>
 
> (No column name) | (No column name)
 
> ---------------: | ---------------:
 
>     0.8736724712 |   0.873669386657
 
> </pre>
 
 
 
*db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2019l&fiddle=bee231b2eb5fb19f5c7d9b4f44c9bfae)*

Oracle 18c

 
>     SELECT  POWER((1/(1+(0.0275/12))), 59),POWER((1.0/(1.0+(0.0275/12.0))), 59) from dual
 
> 
> <pre>
 
>             POWER((1/(1+(0.0275/12))),59) |       POWER((1.0/(1.0+(0.0275/12.0))),59)
 
> ----------------------------------------: | ----------------------------------------:
 
> .8736690438487786167477591439566707207701 | .8736690438487786167477591439566707207701
 
> </pre>
 
 
 
*db<>fiddle [here](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=fadcbb1e062636018644f44aad5e4164)*

mysql-8.0.21

 
>     SELECT version(),POW((1/(1+(0.0275/12))), 59),POW((1.0/(1.0+(0.0275/12.0))), 59)
 
> 
> <pre>
 
> version() | POW((1/(1+(0.0275/12))), 59) | POW((1.0/(1.0+(0.0275/12.0))), 59)
 
> :-------- | ---------------------------: | ---------------------------------:
 
> 8.0.21    |           0.8736690781345217 |                 0.8736690438487815
 
> </pre>
 
 
 
*db<>fiddle [here](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2377c59b8a597041ebc6e1ee4ebbd901)*

mysql-5.5.62

 
<!-- -->
 
>     SELECT version(),POW((1/(1+(0.0275/12))), 59),POW((1.0/(1.0+(0.0275/12.0))), 59)
 
> 
> <pre>
 
> version() | POW((1/(1+(0.0275/12))), 59) | POW((1.0/(1.0+(0.0275/12.0))), 59)
 
> :-------- | ---------------------------: | ---------------------------------:
 
> 5.5.62    |           0.8736690781345217 |                 0.8736690438487815
 
> </pre>
 
 
 
*db<>fiddle [here](https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=2377c59b8a597041ebc6e1ee4ebbd901)*

Comment by Sergei Golubchik [ 2020-09-19 ]

Looks like another instance of MDEV-23702

Comment by Robin Parker [ 2020-09-19 ]

So guys...

1. Nice, that it's anywhere documented, that there was work done on this kind of internals (aka Changelog).
2. Bug is already known - no notice, no big red "attention when upgrading"-box and no fix? The other tickets got closed with "not a bug"?
3. After a lot of other problems while on the 10.x-Stream and upgrading about four times a year (e.g. "mysql.index_stats wrong column definition") it's a disqualification of MariaDB if you can't rely on maths, you've written years ago?
I already tried a lot of "sql-mode"-switches to figure out, it there's anything regaring legacy-modes, but this didn't help either.

Is there a chance this problem get fixed? When no, we have to do a vendor switch, because this was only the most-impact problem, but there a lot of rounding-inaccuracies in comparison of 10.3.23.

Comment by Sergei Golubchik [ 2020-09-19 ]

This is not a bug. The bug was that div_precision_increment did not behave as documented, it was ignored in some cases. Making it finally to work as it was supposed to — this is not a bug.

But accepting the fact that such a change in results is, at best, unexpected and unwelcome, the other ticket MDEV-23702 was reopened. We'll need to redefine how div_precision_increment works, change the documentation, and change the code to match the new definition. So yes, there is a chance to get the old behavior back, this time intentionally.

As a workaround you can increase div_precision_increment.

Comment by Robin Parker [ 2020-09-19 ]

Increased to 38, but result is not thaaaat accurate - but should be okay for our cases....

Thanks.

Comment by Daniel Black [ 2020-09-19 ]

other references of significantly different precision.

firebird-3.0

 
>     SELECT POWer((1/(1+(0.0275/12))), 59),POWer((1.0/(1.0+(0.0275/12.0))), 59) from  RDB$DATABASE;
 
> 
> <pre>
 
> POWER            | POWER           
> :--------------- | :---------------
 
> 0.87814549278163 | 0.87374276910265
 
> </pre>
 
 
 
*db<>fiddle [here](https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=cb61151548a9aeb0fdd1ffccfd996f05)*

DB2 v11.1.4.4

 
>     SELECT POWER((1/(1+(0.0275/12))), 59),POWER((1.0/(1.0+(0.0275/12.0))), 59) from  sysibm.sysdummy1
 
> 
> <pre>
 
> [IBM][CLI Driver][DB2/LINUXX8664] SQL0419N  A decimal divide operation is not valid because the result would have a negative scale.  SQLSTATE=42911 SQLCODE=-419
 
> </pre>
 
 
 
<!-- -->
 
>     SELECT POWER((1.0/(1.0+(0.0275/12.0))), 59) from  sysibm.sysdummy1
 
> 
> <pre>
 
> |                 1 |
 
> | ----------------: |
 
> | 0.001996678111016 |
 
> </pre>
 
 
 
<!-- -->
 
>     select POWER((1/(1+(0.0275/12))), 59) from  sysibm.sysdummy1
 
> 
> <pre>
 
> [IBM][CLI Driver][DB2/LINUXX8664] SQL0419N  A decimal divide operation is not valid because the result would have a negative scale.  SQLSTATE=42911 SQLCODE=-419
 
> </pre>
 
 
 
*db<>fiddle [here](https://dbfiddle.uk/?rdbms=db2_11.1&fiddle=748dcb4e7ea3010ba96e930b928a1e9d)*

and sqlite didn't have an inbuilt POWER function.

Comment by Robin Parker [ 2020-09-19 ]

yeah I already know, but the wrong accuracy should be consistent accross versions or marked as "breaking change"?

Generated at Thu Feb 08 09:24:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.