Feature Request: inQuery_

[ProofOfKeags]

Sometimes it is desirable to be able to take queries that return a list of values and be able to use them in an "in" clause.

Example:

SELECT col1, col2 FROM tbl1 WHERE tbl1.col3 IN 
    (SELECT DISTINCT col3 FROM tbl1 WHERE cond1 AND cond2 LIMIT n)
FOR UPDATE SKIP LOCKED

Currently, in_ requires you to have a list of QGenExprs, It would be nice if a query could be placed on the right side of an in_ like combinator instead of a haskell list.

I was hoping subselect_ could fulfill my desires here, but it seems to only to take one argument, so I can't tell if it can be used for the above.

Is this something that would be possible?

[ProofOfKeags]

This helps to be able to defeat the mutual exclusion between DISTINCT and FOR UPDATE

[kmicklas]

I was surprised to find out this is actually in SQL 92 so we should definitely add it. Very strangely it actually looks like you can even use in with a subquery returning multiple columns, even though ANSI SQL neither supports multiple column expression subqueries, nor in on a list of tuples.

I'll try to have something out for this some time in the next week.

[runeksvendsen]

~~@kmicklas have you had the opportunity to look at this?~~

~~I'm trying to translate the following SQL query to Beam. But I'm unable to since in_ doesn't support comparing tuples.~~

SELECT
    p.id
FROM
    paths AS p JOIN path_parts pp
        ON P.id = pp.path__id
WHERE
  (pp.index, pp.currency__symbol, pp.venue__name)
      IN ( (0, 'EUR', 'coinbase')
         , (1, 'BTC', 'bitfinex')
         , (2, 'USD', 'coinbase')
         )

EDIT: This is a separate issue. Please disregard.

[runeksvendsen]

@ProofOfKeags using SQL = ANY should give the same result as using IN. So unknownAs_ False (x ==*. anyOf_ xQuery) should be equivalent to x `in_` xQuery.