Implicit conversion from data type varbinary to date is not allowed

[puruzio]

Repo.Update fails when the changeset includes a nil date value with an intent to update a date field to set to null. The error message states:

[2021-07-06 16:34:24.100] [debug] Line 1 (Error 8180): Statement(s) could not be prepared.

** (Tds.Error) Line 1 (Error 257): Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query.

    (ecto_sql 3.6.1) lib/ecto/adapters/sql.ex:749: Ecto.Adapters.SQL.raise_sql_call_error/1

    (ecto 3.6.1) lib/ecto/repo/schema.ex:717: Ecto.Repo.Schema.apply/4

    (ecto 3.6.1) lib/ecto/repo/schema.ex:426: anonymous fn/15 in Ecto.Repo.Schema.do_update/4

The changeset looks like

#Ecto.Changeset<
   action: nil,
   changes: %{date_completed: nil},
   errors: [],
   data: #MyApp.MyModel<>,
   valid?: true
>

[markquezada]

@puruzio did you ever figure out a fix? We're running into the same issue

[rschenk]

I did some digging into this and wrote a failing test for it. The issue is caused by Tds.Parameter.fix_data_type/1 which casts a nil parameter to a :binary type. This existing approach works for every commonly-used column type, except dates. (Weirdly, even datetimes work with the existing code, but not dates!)

A quick way to almost fix this is to change the function linked above from :binary to :string. This works for every commonly-used column type, except varbinary, which explodes with a similar error.

I should caveat the following by saying that I know almost nothing about the guts of the TDS protocol.

That being said, I believe the proper way to fix this is to introduce a :null parameter type. TDS has a null type (see Tds.Types@tds_data_type_null) and this is being decoded when it comes out of the database in Tds.Types.decode_data/2. There unfortunately is no corresponding logic to encode it back into the db.

I think we need to update Tds.Parameter.fix_data_type to introduce a null parameter type:

  def fix_data_type(%__MODULE__{type: nil, value: nil} = param) do
    %{param | type: :null}
  end

Then add that :null to the case statement in Tds.Types.encode_data_type and add a corresponding helper method to handle that case. The correct implementation of the latter is a bit beyond me since I don't actually know TDS.

Finally the real nitty gritty, Tds.Types.encode_data/3 will need to actually put that null type as bits on the wire, which I don't know how to do.

I can hopefully dig into this a little more when I get some time, or otherwise, perhaps someone who knows the intricacies of Ecto guts and the TDS protocol can use what I've sleuthed out and make this change (assuming my suggested change is on the right track).

[markquezada]

@moogle19 @mjaric Hi guys. Wondering if you could weigh in on the above as this is a major pain point for us right now. We'd be happy to create a PR to address this issue but need some guidance about the right way to solve it. Could you weigh in on the above and let us know if we're on the right track here?

[markquezada]

cc: @ewitchin 👆

[mjaric]

@markquezada or @rschenk could you please create simple repo that can I check? Thing is that ecto is layer above tds, and there are a lot of points where this could go wrong. Maybe fix could be done in Ecto.Tds.Adapter instead of in Tds driver.

[rschenk]

Hi @mjaric sorry for the delay, I've made a repo here: https://github.com/rschenk/ecto_tds_null_dates

[markquezada]

@mjaric just making sure you saw the above ☝️

[mjaric]

Tnx. I will check it

[mlooney]

Hi folks, any news on this issue? I bonked into it today. I was able to work around it by changing the column type to string, but i suspect i'm losing some storage there. any other workarounds that make more sense?

[rschenk]

@mlooney I've not heard any news myself. I made a repo illustrating the problem with a failing test, linked above, but have not heard about any progress towards fixing it