Encoding issues with prepared statements and database with utf8_unicode_ci collation

[Wijnand]

All our databases have utf8_unicode_ci collation and since the upgrade to Ecto2.0 we are having a lot of errors in our queries. I traced it down to mariaex but I am not sure what is wrong here.

I created a gist which illustrates the issue. The query in this gist is very bogus but it should not fail with this error. https://gist.github.com/Wijnand/7ac718d56a440a4bfe0920fad35e3a00

The collation_connection is set to the correct collation so I have no idea why the resulting query fails with: Illegal mix of collations (utf8_unicode_ci,COERCIBLE) and (utf8_general_ci,COERCIBLE) for operation '<'

In the gist I also have a ruby example where I do the exact same thing and this one succeeds. This also wasn't an issue when we were running mariaex 0.6.5.

[squareduck]

This change introduced the issue
f30200c

Maybe we can find a compromise to satisfy both use cases.

[squareduck]

So the option is already there: b1df978

Default is :field_type_var_string and we need :field_type_blob

Need to specify binary encoding in query options or in start_link:

{:ok, conn} = Mariaex.start_link(username: "root", database: "mariaex_collation_test", binary_as: :field_type_blob)

[liveforeverx]

@Wijnand

Year, this is strange, depending on type. Did it help binary_as: :field_type_blob?

[Wijnand]

Yes, that does help so it is possible to work around this (thanks @squareduck for your investigation).

However I do feel that option should not be the real fix in this case.

[liveforeverx]

@Wijnand I do not know yet, if there exists better solution yet, because some database configuration need this and some other configuration (I have added documentation for it and marked as important in README).

I would like propose to close this issue now.

And, if you still feel, that there should be better solution, we can reopen new issue with name, like Encoding configuration for different database encoding configuration (or something similar, describing the core problem of configuration types).