Add Support for tuple in where clauses

[Swoorup]

Thanks for the great library. Another thing I've found is probably missing support for tuple query generation in where clauses?

        selectTask HydraReader.Read ctx {
          for a in draftItemTable do
            where (topic = topic
                   && (a.timestamp, a.sequence) >= (fromTimestamp, fromSequence)
                   && (a.timestamp, a.sequence) <= (toTimestamp, toSequence)
                   && (loadExecuted = true || isNotIn a.status [|draft_item_status.executed; draft_item_status.executing|])
                   )
            take limit
            mapArray (toDomainModel a)

should roughly translate to

        select * from draft_item
        where
          topic = @topic
          and (timestamp, sequence) >= (@fromTimestamp, @fromSequence)
          and (timestamp, sequence) <= (@toTimestamp, @toSequence)
          and (@loadExecuted = true or status not in ('executed', 'executing'))
        order by (timestamp, sequence)
        limit @limit

However at the moment, it gives:

System.InvalidOperationException: The binary operator GreaterThanOrEqual is not defined for the types 'System.Tuple`2[System.DateTime,System.Int64]' and 'System.Tuple`2[System.DateTime,System.Int64]'.
   at System.Linq.Expressions.Expression.GetUserDefinedBinaryOperatorOrThrow(ExpressionType binaryType, String name, Expression left, Expression right, Boolean liftToNull)
   at System.Linq.Expressions.Expression.GetComparisonOperator(ExpressionType binaryType, String opName, Expression left, Expression right, Boolean liftToNull)
   at System.Linq.Expressions.Expression.GreaterThanOrEqual(Expression left, Expression right, Boolean liftToNull, MethodInfo method)
   at System.Linq.Expressions.Expression.GreaterThanOrEqual(Expression left, Expression right)
   at Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext@367-3.Invoke(Tuple`2 tupledArg)
   at Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext(ConvEnv env, FSharpExpr inp) in D:\a\_work\1\s\src\FSharp.Core\Linq.fs:line 367
   at Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext(ConvEnv env, FSharpExpr inp) in D:\a\_work\1\s\src\FSharp.Core\Linq.fs:line 282
   at Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.ConvExprToLinqInContext(ConvEnv env, FSharpExpr inp) in 

[JordanMarr]

Is that valid in PostgreSQL? I tried this query using the SqlHydra.Npgsql test database but it gives a syntax error:

select e.*
from humanresources.employee e 
(jobtitle, maritalstatus) = ('Design Engineer', 'M')

However, this works:

select e.*
from humanresources.employee e 
where jobtitle = 'Design Engineer' and maritalstatus = 'M'

[Swoorup]

The first one is missing a where?

This should work

sample=# with t as (select 1 as f, 2 as s) select * from t where (t.f,t.s) = (1,2);
 f | s
---+---
 1 | 2
(1 row)     

It does work with string too. It is especially handy working with comparing multiple operators i.e less than/greater than, since the expanded form is bit lengthy.

[JordanMarr]

That was it. Still getting used to DBeaver which tries a little too hard to be helpful sometimes. I know SQL Server doesn't support tuples, but maybe some others do. I suppose this could be implemented and it could be up to the user to know if tuples are not supported. Or maybe it would be worth converting the tuples to AND queries for SQL Server.