[MDEV-22095] Getting wrong result in calculation using variables from derived table Created: 2020-03-31  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Variables
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.4

Type: Bug Priority: Minor
Reporter: Alexander Griehl Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None
Environment:

eg. 10.2.22-MariaDB-log openSUSE package on SUSE Linux Enterprise Server 15 (x86_64) patchlevel 1


 Description   

Doing some calculations to get weighted percentiles I ran into following issue.
I was able to tear it down to following expression:
SELECT 0.1*(@num-1.0) FROM (SELECT @num:=62) AS data;
The expected result is of course 6.1.
But executing via the cli client:
mysql -NBe "SELECT 0.1*(@num-1.0) FROM (SELECT @num:=62) AS data;"
6.1000000000000005

Doing the same on mysql 5.5.62 returns 6.10.

Here some outputs from MariaDB from some workarounds:
1) Preinit the variable:
mysql -NBe "SET @num:=0; SELECT 0.1*(@num-1.0) FROM (SELECT @num:=62) AS data;"
6.10
2) Execute it twice in a session
mysql -NBe "SELECT 0.1*(@num-1.0) FROM (SELECT @num:=62) AS data; SELECT 0.1*(@num-1.0) FROM (SELECT @num:=62) AS data;"
6.1000000000000005
6.10
3) Convert @num to integer
mysql -NBe "SELECT 0.1*(@num DIV 1-1.0) FROM (SELECT @num:=62) AS data;"
6.10

btw.

If you do not use a variable mariadb returns the expected result:
mysql -NBe "SELECT 0.1*(num-1.0) FROM (SELECT 62 AS num) AS data;"
6.10

 Comments   
Comment by Alice Sherepa [ 2020-03-31 ]

Thanks for the report! There is some inconsistency. Repeatable on 5.5-10.4, also on Mysql 5.6-8.0

MariaDB [test]> set @num=0;
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [test]> SELECT 0.1*(@num-1.0) FROM (SELECT @num:=62) AS data;
 
+----------------+
 
| 0.1*(@num-1.0) |
 
+----------------+
 
|           6.10 |
 
+----------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> set @num='';
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [test]> SELECT 0.1*(@num-1.0) FROM (SELECT @num:=62) AS data;
 
+--------------------+
 
| 0.1*(@num-1.0)     |
 
+--------------------+
 
| 6.1000000000000005 |
 
+--------------------+
 
1 row in set (0.002 sec)

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