"IN" on empty array causes invalid SQL

[eran-pinhas]

Not sure if it's by design or a bug but...

Trying to run db.selectFrom('person').selectAll().where('id', 'in', []) causes an invalid SQL (see image below👇 the () part is invalid).

Now obviously, this query doesn't make a lot of sense, and you can simply assume there would be 0 rows returned, but IMO ot. makes sense when in the TypeScript context, and also SQL generating library that creates an invalid SQL is a bit weird. The ways I think we can overcome this "issue" really depends on the philosophy Kysely is based on:

• Leave it as is (Although I would suggest updating the docs to explain this non-trivial case)
• Throwing a runtime error
• replacing it with some sort of false statement (like IN (null) which should be equivalent)

Let me know what you guys think...

[koskimas]

It is invalid SQL. Kysely builds exactly what you ask it to build. We never add anything unexpected to the SQL.

[eran-pinhas]

I know, that's what I'm saying. But don't you think a library for SQL generation should create only valid SQLs?

I do agree that the third option (the (null) one) is a bit interventive. but what about the first and second solutions?

• Updating the docs to explain this non-trivial case
• Throwing a runtime error

[eran-pinhas]

@koskimas

[koskimas]

Kysely should build what you ask it to build. You already get a runtime error. Feel free to create a PR that documents this 👍

[eran-pinhas]

I'm sorry fella but with your tone I really don't feel like it. Good luck

@koskimas

[koskimas]

You're asking me to build something for you for free after you're already using something I built for you for free. I don't care if you don't create the PR, but if you want it you need to write it.

[igalklebanov]

Just wanted to jump in and say there was nothing wrong with @koskimas' tone here. ❤️

[alessiocancian]

It is invalid SQL. Kysely builds exactly what you ask it to build. We never add anything unexpected to the SQL.

typeorm/typeorm#2195 (comment)

This is handled by the majority of ORMs and even query builders, so not handling it might be more unexpected...

[igalklebanov]

Kysely differs in design philosophy from other libraries out there.

No magic. Explicit. Unopinionated. WYSIWYG (what you see is what you get).

It is what it is.

@koskimas maybe we could offer a plugin that adds a null literal to these lists?