Ecto returning Decimal for MyXQL where it returns an integer for Postgres

Questions / Help
, ,
1

We’re in the process of moving our codebase from Postgres to MySQL (using PlanetScale) and we’re noticing a number of our aggregate queries are now trying to calculate using the Decimal<> type, instead of integers.

For example, the following query treats the column types differently depending on which adapter we’re using.

SomeTable
|> select([t], sum(t.int_col_one) - sum(i.int_col_two))
|> Repo.one()

Using Postgres this will subtract two integers.

Using MySQL (using MyXQL) it will attempt to subtract from a decimal.

Error thrown from the query

bad argument in arithmetic expression: Decimal.new("7401") - 7401

:erlang.-(Decimal.new("7401"), 7401)

We have also observed sum and avg aggregates also return decimal types now (all columns are integers) where as previously we would get an integer back when using Postgres.

I haven’t found much info on this so any pointers or solutions would be appreciated.

2

This appears to be documented behavior of the sum and avg aggregates in MySQL:

The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE).

https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions.html

Having survived many data migration projects my best advice is that different database vendors do similar things differently: never take for granted that similar operations are in fact the same operation across database products.

3

Yeah, even postgres returns a numeric for sum(bigint) -> numeric vs. sum(integer) -> bigint.

I also want to explicitly highlight: this is not ectos doing. Ecto just builds a query for the database to execute and transform any data getting back from the db. It’s not meant to abstract any differences between databases. It just allows you to work with different databases using the same tools.

1 Like