Bug with IN clause, noticed in version 0.5.4

[vasumur]

While using the in clause for passing a bunch of ids, it was working fine in 0.5.1 version but in 0.5.4 I am seeing the bind variables are created improperly.

Please look at the below example where the (i0.ssi_id1 IN (:2) has :2 instead of :1

SELECT i0.rel_id,
       i0.ssi_id1,
       i0.ssi_id2,
       i0.item_rel_type_id
FROM item_rel i0
WHERE (i0.ssi_id1 IN (:2) AND (i0.item_rel_type_id = :2))
AND   ((i0.end_date IS NULL) OR ((i0.start_date <:3) AND (i0.end_date >:4)))

[vstavskyi]

Which version of ecto_sql in each case?

[vstavskyi]

new test

  test "in expression" do
    query = "comments" |> where([c], c.post_id in ^[1, 2, 3]) |> select([c], c.x) |> plan()
    assert all(query) ==
           ~s{SELECT c0.x FROM comments c0 } <>
           ~s{WHERE (c0.post_id IN (:1,:2,:3))}

    query = "comments" |> where([c], c.post_id in [^1, ^2, ^3]) |> select([c], c.x) |> plan()
    assert all(query) ==
           ~s{SELECT c0.x FROM comments c0 } <>
           ~s{WHERE (c0.post_id IN (:1,:2,:3))}
  end

test failed with in ^[1, 2, 3] (left: "SELECT c0.x FROM comments c0 WHERE (c0.post_id IN (:2,:3,:4))")

[vasumur]

@vstavskyi

Appreciate your time looking into this. Ecto SQL version is 3.10 in both the cases.

Here are the details, hope this helps. In the Test 1, I am using the latest 0.5.4 version and as you can see from the results for id "105" where as in Test 2 with 0.5.1 version I am getting it.

It appears that the first variable passed in as input is not taken at all in the new version. I don't see any differences between the output query in both the versions though.

Thanks.... Muralee

Test 1 :

  {:ecto_sql, "~> 3.10.1"},
  {:jamdb_oracle, "0.5.4"},

SELECT i0.rel_id, i0.ssi_id1, i0.ssi_id2, i0.item_type_1, i0.item_type_2, i0.item_rel_type_id, i0.start_date, i0.end_date, i0.comments, i0.create_date, i0.create_user, i0.update_date, i0.update_user FROM item_rel i0 WHERE (i0.ssi_id1 IN (:2,:3,:4) AND (i0.item_rel_type_id = :4)) AND ((i0.end_date IS NULL) OR ((i0.start_date < :5) AND (i0.end_date > :6))) 
[105, 106, 108, 1, ~N[2023-06-30 13:31:00], ~N[2023-06-30 13:31:00]]

%{
  106 => [
    %SsiV2.Model.ItemRel{
      __meta__: #Ecto.Schema.Metadata<:loaded, "item_rel">,
      rel_id: 50337,
      ssi_id1: 106,
      ssi_id2: 18701,
      item_type_1: 1,
      item_type_2: 13,
      item_rel_type_id: 1,
      start_date: nil,
      end_date: nil,
      comments: nil,
      create_date: nil,
      create_user: nil,
      update_date: ~N[2013-08-16 10:57:48],
      update_user: "2Q13MMD_UPDATE"
    },
    %SsiV2.Model.ItemRel{
      __meta__: #Ecto.Schema.Metadata<:loaded, "item_rel">,
      rel_id: 50338,
      ssi_id1: 106,
      ssi_id2: 18703,
      item_type_1: 1,
      item_type_2: 13,
      item_rel_type_id: 1,
      start_date: nil,
      end_date: nil,
      comments: nil,
      create_date: nil,
      create_user: nil,
      update_date: nil,
      update_user: nil
    }
  ],
  108 => [
    %SsiV2.Model.ItemRel{
      __meta__: #Ecto.Schema.Metadata<:loaded, "item_rel">,
      rel_id: 1240426,
      ssi_id1: 108,
      ssi_id2: 2009685,
      item_type_1: 1,
      item_type_2: 13,
      item_rel_type_id: 1,
      start_date: ~N[2008-02-16 10:03:19],
      end_date: nil,
      comments: nil,
      create_date: ~N[2008-02-16 10:03:19],
      create_user: "4Q07MMD_UPDATE",
      update_date: ~N[2011-01-24 14:44:10],
      update_user: "CLEANUP_GSNYDER"
    },
    %SsiV2.Model.ItemRel{
      __meta__: #Ecto.Schema.Metadata<:loaded, "item_rel">,
      rel_id: 1441181,
      ssi_id1: 108,
      ssi_id2: 2546975,
      item_type_1: 1,
      item_type_2: 13,
      item_rel_type_id: 1,
      start_date: ~N[2009-07-16 20:05:00],
      end_date: nil,
      comments: nil,
      create_date: ~N[2009-07-16 20:05:00],
      create_user: "2Q09MMD_UPDATE",
      update_date: ~N[2011-01-24 14:44:10],
      update_user: "CLEANUP_GSNYDER"
    }
  ]
}

Test 2 :

  {:ecto_sql, "~> 3.10"},
  {:jamdb_oracle, "0.5.1"},

SELECT i0.rel_id, i0.ssi_id1, i0.ssi_id2, i0.item_type_1, i0.item_type_2, i0.item_rel_type_id, i0.start_date, i0.end_date, i0.comments, i0.create_date, i0.create_user, i0.update_date, i0.update_user FROM item_rel i0 WHERE (i0.ssi_id1 IN (:2,:3,:4) AND (i0.item_rel_type_id = :4)) AND ((i0.end_date IS NULL) OR ((i0.start_date < :5) AND (i0.end_date > :6))) 
[105, 106, 108, 1, ~N[2023-06-30 13:25:22], ~N[2023-06-30 13:25:22]]

%{
 105 => [
    %SsiV2.Model.ItemRel{
      __meta__: #Ecto.Schema.Metadata<:loaded, "item_rel">,
      rel_id: 50336,
      ssi_id1: 105,
      ssi_id2: 18699,
      item_type_1: 1,
      item_type_2: 13,
      item_rel_type_id: 1,
      start_date: nil,
      end_date: nil,
      comments: nil,
      create_date: nil,
      create_user: nil,
      update_date: ~N[2013-08-16 10:57:48],
      update_user: "2Q13MMD_UPDATE"
    }
  ],
  106 => [
    %SsiV2.Model.ItemRel{
      __meta__: #Ecto.Schema.Metadata<:loaded, "item_rel">,
      rel_id: 50337,
      ssi_id1: 106,
      ssi_id2: 18701,
      item_type_1: 1,
      item_type_2: 13,
      item_rel_type_id: 1,
      start_date: nil,
      end_date: nil,
      comments: nil,
      create_date: nil,
      create_user: nil,
      update_date: ~N[2013-08-16 10:57:48],
      update_user: "2Q13MMD_UPDATE"
    },
    %SsiV2.Model.ItemRel{
      __meta__: #Ecto.Schema.Metadata<:loaded, "item_rel">,
      rel_id: 50338,
      ssi_id1: 106,
      ssi_id2: 18703,
      item_type_1: 1,
      item_type_2: 13,
      item_rel_type_id: 1,
      start_date: nil,
      end_date: nil,
      comments: nil,
      create_date: nil,
      create_user: nil,
      update_date: nil,
      update_user: nil
    }
  ],
  108 => [
    %SsiV2.Model.ItemRel{
      __meta__: #Ecto.Schema.Metadata<:loaded, "item_rel">,
      rel_id: 1240426,
      ssi_id1: 108,
      ssi_id2: 2009685,
      item_type_1: 1,
      item_type_2: 13,
      item_rel_type_id: 1,
      start_date: ~N[2008-02-16 10:03:19],
      end_date: nil,
      comments: nil,
      create_date: ~N[2008-02-16 10:03:19],
      create_user: "4Q07MMD_UPDATE",
      update_date: ~N[2011-01-24 14:44:10],
      update_user: "CLEANUP_GSNYDER"
    },
    %SsiV2.Model.ItemRel{
      __meta__: #Ecto.Schema.Metadata<:loaded, "item_rel">,
      rel_id: 1441181,
      ssi_id1: 108,
      ssi_id2: 2546975,
      item_type_1: 1,
      item_type_2: 13,
      item_rel_type_id: 1,
      start_date: ~N[2009-07-16 20:05:00],
      end_date: nil,
      comments: nil,
      create_date: ~N[2009-07-16 20:05:00],
      create_user: "2Q09MMD_UPDATE",
      update_date: ~N[2011-01-24 14:44:10],
      update_user: "CLEANUP_GSNYDER"
    }
  ]
}

[vstavskyi]

fix in operator #141

[vasumur]

Thanks @vstavskyi . Closing this one.