Sum returns ERROR 42804 (datatype_mismatch)

[bigardone]

👋🏼 Hi there! I'm following the steps in the docs for aggregating money values, but after following the steps, I get the following error:

** (Postgrex.Error) ERROR 42804 (datatype_mismatch) returned record type does not match expected record type

Returned type character varying does not match expected type character(3) in column 1.
    (ecto_sql 3.10.2) lib/ecto/adapters/sql.ex:1047: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.10.2) lib/ecto/adapters/sql.ex:945: Ecto.Adapters.SQL.execute/6
    (ecto 3.10.3) lib/ecto/repo/queryable.ex:229: Ecto.Repo.Queryable.execute/4
    (ecto 3.10.3) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (ecto 3.10.3) lib/ecto/repo/queryable.ex:151: Ecto.Repo.Queryable.one/3
    iex:5: (file)

Here's the migration for the schema part:

# ...

    create table(:products) do
      # ...
      add(:price, :money_with_currency, null: false)
    end

# ...

Here there query I'm using:

     query = from(p in Product, select: %{amount: type(sum(p.price), p.price)})

    Repo.one(query)

Which creates the following query:

SELECT sum(p0."price")::money_with_currency FROM "products" AS p0 []

Other than running the migration to create the specific sum functions for money_with_currency, is there anything else I might be missing? By the way, I'm using PostgreSQL 15.

Thanks in advance :raised_hands:

[kipcole9]

Sorry for the inconvenience @bigardone. I believe I understand the cause. The key part of the error message is this:

Returned type character varying does not match expected type character(3) in column 1.

Which suggests that at some time you created the migration for the money_with_currency type. Then later upgraded to a newer version of ex_money_sql. And then you created the migration for the aggregate functions. Does that sound likely?

A few releases ago I realised that defining the money_with_currency type as a char(3) in Postgres was a bad idea. So I change it, and all the other sql functions, to use varchar.

Unfortunately that means in some cases you're going to potentially have the mismatch you are seeing. The data type is char(3) but the aggregate function is returning varchar and Postgres won't consider them the same.

Solution 1

The easiest but least satisfying solution is to hand edit the aggregate function migration files to change all references to varchar with char(3) - thereby matching the type in the money_with_currency migration. Then rollback the aggregate functions migration and then roll forward again.

Solution 2

Alternatively, if you're still in development:

1. Rollback all migrations.
2. Delete the migration files.
3. Regenerate the migration files again for both the money_with_currency type and the aggregate functions.
4. Migrate the database once more.

If any of that makes no sense at all, please do show me the money_sql generated migration files so I can investigate further.

[bigardone]

👋🏼 Hi @kipcole9, thank you very much for the response. What you suggest makes total sense. Now that we know what is happening, I'll try to find the best fix we can apply since we are already in production. I'll keep you updated and share the fix here as soon as I have it 🙌🏼

[kipcole9]

Im so sorry about this. I just haven't found a better way to automate at least notifying of the type mismatch at migration time.

I would recommend you hand-edit the migration that implements the aggregate functions. Then rollback that migration, if you can, and roll forward with the varchar to char(3). The only downside is that you won't be able to use digital token currencies (crypto currency) because their codes are longer than 3 chars.

Hopefully that is workable for you?

[bigardone]

It worked like a charm! Thank you so much for all your great libraries and for always being so friendly helping us solving issues :raised_hands:

[kipcole9]

Thank you so much for all your great libraries and for always being so friendly helping us solving issues 🙌

Thanks for the kind words, you are very welcome.

Since this error is an embarrassment (to me) I'm diving into ways to auto-detect the data type of the money_with_currency type and create the correct version of the aggregate functions. I'm aiming to get that done by end of the weekend.

[kipcole9]

I've published ex_money_sql version 1.10.0 with the following changelog entry:

Bug Fixes

• The mix tasks that generate database function migrations (money.gen.postgres.sum_function, money.gen.postgres.plus_operator and money.gen.postgres.min_max_functions) need to be aware of the type of the money_with_currency "amount" element in a Postgres database. In releases of ex_money_sql up to 1.7.1 the type was char(3). In later releases is changed to the more canonical varchar. In turn, the database functions need to know the type for the internal accumulator. It is possible, as illustrated in issue #36, to have generated the money_with_currency type as char(3) and then move to a later release of ex_money_sql. In which case the money database function migrations would fail because they were built with varchar accumulators. This release will detect the underlying type of the money_with_currency "amount" element and adjust the migration accordingly. Thanks to @bigardone for the report and motivation to get this done. Closes #36.

Enhancements

• Adds database functions for unary negation and the operation -. Thanks to @zachdaniel for the PR.

Thanks again from he collaboration and patience. Its good to get this done.

[bigardone]

😍 thank you again for the great work 🙌🏼