1

ddl:

CREATE TABLE `tests` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `num` decimal(40,20) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

sql:

query=UPDATE `tests` SET `num` = ? WHERE `tests`.`id` = ? args=\[1.11111111111111111 1\]

query=UPDATE `tests` SET `num` = COALESCE(`tests`.`num`, 0) + ? WHERE `tests`.`id` = ? args=\[1.11111111111111111 2\]

result:

mysql5.7:

id=1, num=1.11111111111111111000
id=2, num=**1.11111111111111120000**  why?

mysql8.0:

id=1, num=1.11111111111111111000
id=2, num=1.11111111111111111000

Why can the same statement get different results?

code:


    c, err := ent.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test?charset=utf8", ent.Debug())
    if err != nil {
        return
    }
    // c.Test.Create().SetName("a").SetNum(decimal.Zero).Exec(context.Background())
    // c.Test.Create().SetName("a").SetNum(decimal.Zero).Exec(context.Background())
    val, _ := decimal.NewFromString("1.11111111111111111")
    c.Test.Update().SetNum(val).Where(test.ID(1)).Exec(context.Background())
    c.Test.Update().AddNum(val).Where(test.ID(2)).Exec(context.Background())
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
solitary
  • 11
  • 2
  • are you wanting 5.7 to be working the same as 8? what is your goal here? the literal answer to your "Why can the same statement get different results?" is "because newer versions get improvements" (as well as sometimes introduce bugs) – ysth Jun 08 '23 at 17:32
  • It is expected that 5.7 should get the same result as 8.0, and I would like to know why mysql5.7 caused such a result. – solitary Jun 09 '23 at 01:59

0 Answers0