Variable insertion misaligned with using WHERE IN

[ssomnoremac]

trafficstars

I have a query like so which I'm passing an array of ids and a string

Repo.all(
        from p in Punchcard,
          join: sm in assoc(p, :sm),
          join: application in assoc(p, :application),
          join: profile in assoc(application, :profile),
          where: sm.state_id in ^state_ids,
          where: profile.country_code == ^country_code,
          select: p
        )

If I pass a single value array, it works:

Expected Behavior

 QUERY OK source="Punchcard" db=144.2ms
SELECT P0."sm_id", P0."application_id", P0."check_in", P0."check_out", P0."amt_to_talent", P0."amt_to_hyr" FROM "Punchcard" AS P0 INNER JOIN "Sm" AS S1 ON (S1."sm_id" = P0."sm_id") INNER JOIN "Application" AS A2 ON (A2."sm_id" = P0."application_id") INNER JOIN "Profile" AS P3 ON (P3."sm_id" = A2."profile_id") 
WHERE ((S1."state_id" IN (?)) AND (P3."country_code" = ?)) ORDER BY P0."sm_id" DESC [4202, "US"]

The last part is most relevant. However, as soon as I pass a second item to the array the variable insertion seems to misalign

Actual Behavior

QUERY ERROR source="Punchcard" db=90.2ms queue=20.2ms
SELECT P0."sm_id", P0."application_id", P0."check_in", P0."check_out", P0."amt_to_talent", P0."amt_to_hyr" FROM "Punchcard" AS P0 INNER JOIN "Sm" AS S1 ON (S1."sm_id" = P0."sm_id") INNER JOIN "Application" AS A2 ON (A2."sm_id" = P0."application_id") INNER JOIN "Profile" AS P3 ON (P3."sm_id" = A2."profile_id") 
WHERE ((S1."state_id" IN (?,?)) AND (P3."country_code" = ?)) [4202, 4204, "US"]

My database thinks that "US" is being passed to the "state_id" IN(?,?) clause.

Conversion failed when converting the nvarchar value 'US' to data type int. | ODBC_CODE 22018 | SQL_SERVER_CODE 245

Unless I'm missing something, it seems not to be behaving correctly.

Possible Solution

it works to reverse the where clauses

WHERE (P3."country_code" = ?) AND (S1."state_id" IN (?,?,?)) ["US", 4202, 4204, 4303]

Your Environment

latest

[shdblowers]

Hi @ssomnoremac this is definitely a bug.

Will write some unit tests in mssqlex to reproduce it and see where I can go from there.

[shdblowers]

Hi @ssomnoremac I tried to replicate your issue with a test in mssqlex and it passed.

Can you take a look at the PR and confirm if the PR adequately tests your scenario:

https://github.com/findmypast-oss/mssqlex/pull/9

[ssomnoremac]

Thanks @shdblowers , those tests look good except for not having the joins. Don't know how that could change the behavior. I can try to apply the same WHERE IN to various other queries and let you know if I see the same error.

[akeemboatswain]

you get the same issue if you do something like this , the variables in the generated sql also get missaligned:

` test_values = [2,4,5,6]

more_test_values = [7,8,9,10]

Repo.all(

    from a in ItemA,

      join: b in ItemB, 

      on: a.id == b.id,

      where: a.test in ^test_values and a.other_vals in ^more_test_values,  

      select: a

    )`