WHERE ... IN with tuples support

[yzaoui]

Apologies if this already exists but I could not find it in the library. Does/will Exposed support something like:
SELECT * FROM table WHERE (x,y) IN ((1, 2), (5, 8), (7, 2))
Alternatively, I tried to figure out a way to append an arbitrary amount of AND/ORs to replicate this behavior, but the way AndOp and OrOp are composed doesn't seem to support that.
It looks like this is part of the SQL standard but I can't find a source to prove it.

[Tapac]

No, such cases are not supported in Exposed and I'm not sure that this is a standard SQL expression.
You can try to use custom functions/expressions like this:

class PairExpression<L,R>(val first: Expression<L>, val second: Expression<R>) : Expression<Pair<L,R>>() {
    override fun toSQL(queryBuilder: QueryBuilder): String =
            "(${first.toSQL(queryBuilder)}, ${second.toSQL(queryBuilder)})"
}

fun <L,R> PairExpression<L, R>.inList(list: List<PairExpression<L,R>>) = object : Op<Boolean>() {
    override fun toSQL(queryBuilder: QueryBuilder): String {
        return when(list.size) {
            0 -> "FALSE"
            1 -> "${this.toSQL(queryBuilder)} = ${list[0].toSQL(queryBuilder)}"
            else -> list.joinToString(",", prefix = "${this.toSQL(queryBuilder)} in (", postfix = ")") {
                it.toSQL(queryBuilder)
            }
        }
    }
}

infix fun <L,R> Expression<L>.to(exp2: Expression<R>) = PairExpression(this, exp2)

fun main(args: Array<String>) {
    val FooTable = object : Table() {
        val x = integer("x")
        val y = integer("y")
    }
    
    FooTable.select { 
        (FooTable.x to FooTable.y).inList(listOf(intParam(1) to intParam(2))) 
    }
}

I didn't test this on real databases and can't guarantee that everything works fine. Also you should understand that this approach doesn't use prepared statement parameters and uses plain sql which can cause to SQL-injection if you'll put string values from client-side.

[yzaoui]

I found a reference to the 1992 SQL standard, and if you look at the grammar for <where clause>, you will see that it eventually expands like

WHERE <search condition>
<search condition> => <boolean term>
<boolean term> => <boolean factor>
<boolean factor> => <boolean test>
<boolean test> => <boolean primary>
<boolean primary> => <predicate>
<predicate> => <in predicate>
<in predicate> => <row value constructor> [NOT] IN <in predicate value>

Expanding <row value constructor>:

<left paren> <row value constructor list> <right paren>
<row value constructor list> => <row value constructor element> [ { <comma> <row value constructor element> }... ]
<row value constructor element> => <value expression>

Where <value expression> eventually expands to column references.

Similarly, <in predicate value> can lead to a list of <value expression>, or a list of lists of <value expression>
So I think this means WHERE (col1, col2) IN ((2, 3), (4, 2)) is part of the 1992 standard, unless I missed some syntax rule restricting this situation.
And thank you for your code, I ended up doing something similar with constructing manual SQL, but it would be nice to not have to rely on pairs, if I ever want to compare 3 columns in the near future for instance. It's good enough for now at least.

[Tapac]

I'm not sure that it's possible to define such in expression in Kotlin and keep type-safety. Also I afraid that not all DBMS cover that part of SQL standard.

[yzaoui]

That's fair, I can't really imagine how such an arbitrarily-typed expression could be implemented in a typed manner either. Oh well, carefully-written raw SQL is good enough. Thanks!