|
I ran this test and got different results than above
select (count(*)/72143371)*100,(6001215/72143371)*100,(count(*)*100)/72143371,(6001215*100)/72143371 from lineitem;
|
+-------------------------+------------------------+-------------------------+------------------------+
|
| (count(*)/72143371)*100 | (6001215/72143371)*100 | (count(*)*100)/72143371 | (6001215*100)/72143371 |
|
+-------------------------+------------------------+-------------------------+------------------------+
|
| 8.3200 | 8.3185 | 8.3185 | 8.3185 |
|
+-------------------------+------------------------+-------------------------+------------------------+
|
|
|
|
For develop-1.4
select (count(*)/72143371)*100,(6001215/72143371)*100,(count(*)*100)/72143371,(6001215*100)/72143371 from lineitem;
|
+-------------------------+------------------------+-------------------------+------------------------+
|
| (count(*)/72143371)*100 | (6001215/72143371)*100 | (count(*)*100)/72143371 | (6001215*100)/72143371 |
|
+-------------------------+------------------------+-------------------------+------------------------+
|
| 8.3200 | 8.3185 | 8.3185 | 8.3185 |
|
+-------------------------+------------------------+-------------------------+------------------------+
|
|
So whatever is happening is happening on the latest 1.4 as well
|
|
We're dealing with 4 cases here:
1) (count(*)/72143371)*100 This is executed in engine because of the existence of DB data. count(*) is 6001215. Since we have parenthesis, it executes (6001215/72143371) first. This evaluates to 0.083184565911121619197112885685241324. But it's assigned to a decimal (18,4), yielding 0.0832
|
|
2) (6001215/72143371)*100 Since this is all constants, It is evaluated in the server. We have no control over it.
|
|
3) (count(*)*100)/72143371 Evaluates in the engine, but the multiply executed before the divide allows for less truncation.
|
|
4) (6001215*100)/72143371 Evaluates in the server
|
|
|
I upgraded to the latest server build and got:
MariaDB [tpch1]> select (count(*)/72143371)*100,(6001215/72143371)*100,(count(*)*100)/72143371,(6001215*100)/72143371 from lineitem;
|
+-------------------------+------------------------+-------------------------+------------------------+
|
| (count(*)/72143371)*100 | (6001215/72143371)*100 | (count(*)*100)/72143371 | (6001215*100)/72143371 |
|
+-------------------------+------------------------+-------------------------+------------------------+
|
| 8.3200 | 8.3200 | 8.3185 | 8.3185 |
|
+-------------------------+------------------------+-------------------------+------------------------+
|
|
See comment above. For the case of the second columnm, this calculation is performed in server, not in the engine. There's noting the engine team can do.
|