Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
-
None
-
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