"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?

[spinda]

It does seem surprising that the code type-checks but leads to the generation of invalid SQL. Kysely uses the type system to protect you from accidentally generating invalid SQL in other ways. However, there doesn't seem to be a way to (ergonomically) express a "non-empty array" type in TypeScript.

[lithdew]

@spinda [unknown, …unknown[]] represents a non-empty tuple type - casting to it needs to be done carefully though.

[voho]

Just a question, if I may. How does this behave when IN is a subquery and that subquery returns an empty set of rows? Is there any way to solve that case easily, without rewriting the query?

[koskimas]

An empty result set from a subquery should work. It's just an empty tuple literal () that's not valid SQL.

[koskimas]

Nowadays we DO have special handling in eb.and and eb.or. They create a 1 = 1 and 1 = 0 expressions when an empty array is passed to them. Maybe we should add a special case for in and not in and empty array too.

[austinwoon]

Nowadays we DO have special handling in eb.and and eb.or. They create a 1 = 1 and 1 = 0 expressions when an empty array is passed to them. Maybe we should add a special case for in and not in and empty array too.

@koskimas I've just updated the plugin in #925 to address @igalklebanov 's comments. This should help address in and not in cases.