IN queries with empty list ends up with "AND 0" instead of "IN ()`

[sescobb27]

Hi there, i was investigating why some of our delete mutations in clickhouse had AND 0 in the query resulting in a no-op which is ok, but we were concerned this was a fragile think that may end up deleting an account's all data from a table, after some research we found out that, when we do a pre-filter on a table for data existence, and perform the delete on that existing table for to save mutations, if the resulting data is empty [] ecto clickhouse replace the list comparison with an AND 0

defmodule EventHistory do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key false
  schema "event_history" do
    field :account_id, :string
    field :user_id, :string
  end
end


account_id = "123"
user_list = ["user1", "user2", "user3"]

# this will return empty list []
users_with_history_events = CHRepo.all(
    from eh in EventHistory,
      where: eh.account_id == ^account_id and eh.user_id in ^user_list,
      distinct: eh.user_id,
      select: eh.user_id
  )
  
event_history_del =
  from eh in EventHistory,
    where: eh.account_id == ^account_id and eh.user_id in ^users_with_history_events

CRepo.delete_all(event_history_del)

> 09:11:19.017 [debug] - QUERY OK source="event_history" db=38.8ms queue=0.1ms idle=1327.5ms
DELETE FROM "event_history" WHERE (("account_id" = {$0:String}) AND (0)) ["123"]
{0, nil}

What was expected?

DELETE FROM "event_history" WHERE (("account_id" = {$0:String}) AND ("user_id" IN ())) ["123"]

[ruslandoga]

👋 @sescobb27

Thank you for the report!

I agree, ecto_ch shouldn't be rewriting queries like this. I probably copied it from somewhere as an "optimization" without giving it too much thought.

[ruslandoga]

Hm, it seems like in () doesn't work in ClickHouse, at least in the following query:

:) select 1 in ();
-- Received exception from server (version 25.6.3):
-- Code: 1. DB::Exception: Received from localhost:9000. DB::Exception: Function 'in' is supported only if second argument is constant or table expression. (UNSUPPORTED_METHOD)

Maybe that was the reason for replacing x in () with 0 ...