Fixed precision decimals are not outputted correctly.

[mikeerickson]

When executing the following (compare results in AVG column)

select G, AB, H, REPLACE(ROUND((H/AB),3),'0.','.') as AVG from Batting limit 25;

It produces the following in mycli

When using same command in mysql cli

[meeuw]

Hi @mikeerickson I see what you're trying to achieve here and my best guess is that this is a bug in our tabulate library. There is some code in it to detect python Decimal object and then handle it as a string (and prevent the adding/removal of leading/trailing zero's). I will have a look at it soon.

You can see it doesn't get triggered with a Dutch locale (which tabulate doesn't understand)

mysql root@172.17.0.2:(none)> select format(.1, 3, 'en_US')
+--------------------------+
|   format(.1, 3, 'en_US') |
|--------------------------|
|                      0.1 |
+--------------------------+
1 row in set
Time: 0.002s
mysql root@172.17.0.2:(none)> select format(.1, 3, 'nl_NL')
+--------------------------+
| format(.1, 3, 'nl_NL')   |
|--------------------------|
| 0,100                    |
+--------------------------+
1 row in set
Time: 0.002s

[tsroten]

In our tabulate library, it converts columns where all numbers have decimals into floats (which removes any exact precision).

The only solutions I can think of are:

1. Modify tabulate to look at the precision, and then format it accordingly on output. Doesn't work in the example above where there is no 0 in the beginning.
2. Remove the float conversion code from tabulate so that it treats floats as strings (meaning they will be outputted as mysql returns them).

I'm thinking number 2 might be the way to go. We'd just need to do some tests to make sure it doesn't have any negative effects (I don't think it will).

@meeuw If you can't get to it, let me know and I'll take a stab at it.

[meeuw]

@tsroten I was thinking about getting pymycli to output a Python Decimal object, that seems to be handled like a string in tabulate.
https://github.com/dbcli/mycli/blob/master/mycli/packages/tabulate.py#L338

But I need to dig into this, not sure if that works.

[tsroten]

@meeuw Ah, neat idea.

It looks like PyMySQL automatically converts decimal fields into Decimal objects, but in this example it is not a decimal field, it is the result of a mysql function. We'd have to have some way of autodetecting the type (which would include checking every value in a given column). That might be resource-intensive.

I'll wait and see what you come up with :)

[meeuw]

I've created a fix but it doesn't solve it all..

CREATE TABLE `Batting` (
`G` int(10) DEFAULT NULL,
`AB` int(10) DEFAULT NULL,
`H` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `Batting` VALUES (1,4,0),(25,118,32),(29,137,40),(27,133,44),(25,120,39),(12,49,11),(1,4,1),(31,157,63),(1,5,1),(18,86,13),(22,89,27),(1,3,0),(10,36,7),(3,15,6),(20,94,33),(29,128,32),(1,4,0),(1,4,1),(17,73,17),(1,2,0),(25,106,28),(29,152,46),(30,134,30),(3,14,1),(12,63,15);

You'll have to cast the output to a MySQL DECIMAL:

select G, AB, H, cast(H/AB as decimal(3,3)) as AVG from Batting;

But you can't remove the prefixed 0

+-----+------+-----+-------+
|   G |   AB |   H | AVG   |
|-----+------+-----+-------|
|   1 |    4 |   0 | 0.000 |
|  25 |  118 |  32 | 0.271 |
|  29 |  137 |  40 | 0.292 |
|  27 |  133 |  44 | 0.331 |
|  25 |  120 |  39 | 0.325 |
|  12 |   49 |  11 | 0.224 |
|   1 |    4 |   1 | 0.250 |
|  31 |  157 |  63 | 0.401 |
|   1 |    5 |   1 | 0.200 |
|  18 |   86 |  13 | 0.151 |
|  22 |   89 |  27 | 0.303 |
|   1 |    3 |   0 | 0.000 |
|  10 |   36 |   7 | 0.194 |
|   3 |   15 |   6 | 0.400 |
|  20 |   94 |  33 | 0.351 |
|  29 |  128 |  32 | 0.250 |
|   1 |    4 |   0 | 0.000 |
|   1 |    4 |   1 | 0.250 |
|  17 |   73 |  17 | 0.233 |
|   1 |    2 |   0 | 0.000 |
|  25 |  106 |  28 | 0.264 |
|  29 |  152 |  46 | 0.303 |
|  30 |  134 |  30 | 0.224 |
|   3 |   14 |   1 | 0.071 |
|  12 |   63 |  15 | 0.238 |
+-----+------+-----+-------+
25 rows in set

I'll check if I can get tabulate to leave CHAR fields alone and don't treat them like a number.

[tsroten]

@meeuw Now that both #380 and #388 are merged, this is fixed, right?

Let's leave the issue open until the next release, I just wanted to confirm.

[meeuw]

👍 the query from @mikeerickson gives the same output as official mysql client.

[tsroten]

@mikeerickson This is fixed as of version 1.11.0. If you installed via pip, you can upgrade by running pip install --upgrade mycli.

Thanks for reporting this!