losing decimal precision when updating table

[caeret]

Issue description

losing decimal precision when updating table, no matter the argument is type float64 or string.

Example code

CREATE TABLE `demo` (
  id int not null primary key,
  amount decimal(27,18) not null
);
INSERT INTO `demo` values (1, 0);

package main

import (
	"database/sql"
	"fmt"
	_ "mysql"
)

func main() {
	db, err := sql.Open("mysql", "root:knight+2019@tcp(192.168.35.100:3306)/test")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	for i := 0; i < 3; i++ {
		query(db, i, "1.123")
	}
}

func query(db *sql.DB, i int, v string) {
	_, err := db.Exec("UPDATE `demo` SET `amount` = `amount` + ?", v)
	if err != nil {
		panic(err)
	}

	row := db.QueryRow("select amount from demo")
	var amount string
	err = row.Scan(&amount)
	if err != nil {
		panic(err)
	}
	fmt.Println("i =", i, "and amount is", amount)
}

Error log

i = 0 and amount is 1.123000000000000000
i = 1 and amount is 2.246000000000000000
i = 2 and amount is 3.36899999999999980

Configuration

Driver version (or git SHA):
master

Go version: run go version in your console
go version go1.12.4 linux/amd64

Server version: E.g. MySQL 5.6, MariaDB 10.0.20
MySQL 5.7.25

Server OS: E.g. Debian 8.1 (Jessie), Windows 10
Ubuntu 16.04

[methane]

It is very unlikely driver's issue.
To ask question about MySQL behavior, use Stack Overflow or MySQL Slack.

[methane]

mysql> CREATE TABLE `demo` (
    ->   id int not null primary key,
    ->   amount decimal(27,18) not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `demo` values (1, 0);
Query OK, 1 row affected (0.01 sec)

mysql> UPDATE `demo` SET `amount` = `amount` + "1.123";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select amount from demo;
+----------------------+
| amount               |
+----------------------+
| 1.123000000000000000 |
+----------------------+
1 row in set (0.00 sec)

mysql> UPDATE `demo` SET `amount` = `amount` + "1.123";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select amount from demo;
+----------------------+
| amount               |
+----------------------+
| 2.246000000000000000 |
+----------------------+
1 row in set (0.00 sec)

mysql> UPDATE `demo` SET `amount` = `amount` + "1.123";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select amount from demo;
+----------------------+
| amount               |
+----------------------+
| 3.368999999999999800 |
+----------------------+
1 row in set (0.00 sec)

[caeret]

@methane
but if you change the updating sql to bellow, everything will be ok.

UPDATE `demo` SET `amount` = `amount` + 1.123;

[methane]

That's because you didn't use decimal type. When you use string, "1.123" is used.
When you use float, the number is not "exact value" (this is MySQL term) already.

I don't use decimal types so I can not recommend any library. Ask it on community, not here.

[caeret]

#986 This PR works if the argument type is changed from string to mysql.Decimal.
But I think it may not be the best solution.

[hisunwei]

Change another way to replay this issue, 

MySQL [example]> update demo set amount = 0;
Query OK, 1 row affected (0.246 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [example]> select amount+'1.123'+'1.123'+'1.123' from demo;
+--------------------------------+
| amount+'1.123'+'1.123'+'1.123' |
+--------------------------------+
|             3.3689999999999998 |
+--------------------------------+
1 row in set (0.229 sec)

MySQL [example]> select amount+cast("1.123" as decimal(27, 18)) + cast("1.123" as decimal(27, 18)) + cast("1.123" as decimal(27, 18))  from demo;
+---------------------------------------------------------------------------------------------------------------+
| amount+cast("1.123" as decimal(27, 18)) + cast("1.123" as decimal(27, 18)) + cast("1.123" as decimal(27, 18)) |
+---------------------------------------------------------------------------------------------------------------+
|                                                                                          3.369000000000000000 |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.221 sec)

You should cast  the string as decimal  @caeret 

MySQL [example]> update demo set amount = 0;
Query OK, 1 row affected (0.567 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [example]>
MySQL [example]> UPDATE `demo` SET `amount` = `amount` + cast("1.123" as decimal(27, 18));
Query OK, 1 row affected (0.525 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [example]> UPDATE `demo` SET `amount` = `amount` + cast("1.123" as decimal(27, 18));
Query OK, 1 row affected (0.226 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [example]> UPDATE `demo` SET `amount` = `amount` + cast("1.123" as decimal(27, 18));
Query OK, 1 row affected (0.232 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [example]> select * from demo;
+----+----------------------+
| id | amount               |
+----+----------------------+
|  1 | 3.369000000000000000 |
+----+----------------------+
1 row in set (0.285 sec)