tds_ecto is unable to build align parameter names correctly when used WHERE ? IN (?) AND ? = ?

[mjaric]

Environment

• Elixir version (elixir -v): 1.4.0
• Database and version (PostgreSQL 9.4, MongoDB 3.2, etc.): MSSQL any version
• Ecto version (mix deps): 2+
• Database adapter and version (mix deps): tds_ecto 2.0.0-alpha3
• Operating system: Windows, Linux, MacOS

Current behavior

When building query with ecto, eg:

ids = [1, 23, 43]
is_enabled = true
q = from(m in Model, where: m.id in ^ids and m.is_enabled== ^is_enabled)

Query is built as

SELECT m0.[id], m0.[is_enabled] FROM [Model] as m0 WHERE m0.[id] IN (@1, @2, @3) AND m0.[is_enabled] = @2

...and executed with parameters [1, 23, 43, true] which assigns wrong parameter value to last boolean expression.

Expected behavior

Expecting query to be prepared as

SELECT m0.[id], m0.[is_enabled] FROM [Model] as m0 WHERE m0.[id] IN (@1, @2, @3) AND m0.[is_enabled] = @4

...and executed with parameters [1, 23, 43, true]

NOTE

I'm working on tds_ecto repository for some time now. So far it is in good shape but issues like this is not possible to solve there. Expression index is not possible to track since when I try to build expression for both boolean expressions Index in second is always equal to 2. I've used length indefp expr({:in...}, sources, query) do... function to build "in" parameter names, since parameter lists are flatten anyways into single list when they are passed to adapter query function. Unfortunately MSSQL do not support array type so this cannot be solved as for postgresql with array parameter. The only way it can work is as I mentioned in "expected" section above. BTW, same issue is in MSSQL ODBC adapter, issue is reported here

[josevalim]

You don't need to use array types like Postgres. MySQL has the same limitation and it works there. Have you investigate MySQL?

It is also worth pointing out that Ecto had a bug related to this and Ecto 2.2.5 was released this morning with a fix. Have you tried it?

[mjaric]

I will pull new version and check it

[mjaric]

Yes, latest version works. Thanks!!!