sub_select is not properly parenthesized under some conditions

[tysonzero]

Specifically the following fails with syntax error at or near "SELECT", because the inner sub_select is not properly enclosed in parenthesis.

aggrPrevDate :: MonadIO m => ReaderT SqlBackend m ()
aggrPrevDate = insertSelect . from $ \date -> do
    groupBy $ date ^. DateDate
    orderBy [asc $ date ^. DateDate]
    pure $ PrevDate <# (date ^. DateDate) <&> sub_select (getPrevDate date)
  where
    getPrevDate date = from $ \d -> do
        where_ $ d ^. DateDate <. date ^. DateDate
        pure . max_ $ d ^. DateDate

This is with:

esqueleto 2.5.3
persistent 2.7.1
persistent-postgresql 2.6.2.1

[tysonzero]

Any update on this / any workarounds?

[bitemyapp]

Patches warmly welcomed!

[tysonzero]

I can try and look into it whenever I next have some free time. It's just going to take me a long time to understand the internals of esqueleto, so I was hoping it would be a quick fix for someone who already understood the library.

[parsonsmatt]

Opened #126 with a failing test case.

[parsonsmatt]

In the interim, we're solving this in our work codebase with:

-- | The extra parens are needed when the argument is a `SELECT` statement.
unsafeSqlFunctionWithExtraParens
  :: UnsafeSqlFunctionArgument a
  => TLB.Builder
  -> a
  -> SqlExpr (Value b)
unsafeSqlFunctionWithExtraParens name' arg =
  ERaw Never $ \info ->
    let (argsTLB, argsVals) =
          uncommas' $ map (\(ERaw _ f) -> f info) $ toArgList arg
    in (name' <> parens (parens argsTLB), argsVals)

-- | If the argument is a 'sub_select' then you have to use this, otherwise it
-- goes boom.
sumSelect :: (Num a, Num b) => SqlExpr (Value a) -> SqlExpr (Value (Maybe b))
sumSelect = unsafeSqlFunctionWithExtraParens "SUM"

[hdgarrood]

I think this might be due to the parens field of ERaw being ignored in makeWhere:

makeWhere :: IdentInfo -> WhereClause -> (TLB.Builder, [PersistValue])
makeWhere _    NoWhere                       = mempty
makeWhere info (Where v) = first ("\nWHERE " <>) $ x info
  where
    x =
        case v of
            ERaw _ f             -> f
            EAliasedValue i _    -> aliasedValueIdentToRawSql i
            EValueReference i i' -> valueReferenceToRawSql i i'
            ECompositeKey _      -> throw (CompositeKeyError -> EsqueletoError
CompositeKeyErr MakeWhereError)

[belevy]

@hdgarrood that sounds plausible

[hdgarrood]

Having looked a little bit more closely I think the issue I'm running into might be different from this one.

[belevy]

It should be possible to avoid sub_select through the use of lateral joins (or just regular subquery joins if you don't need to correlate the subquery)

EDIT: oh didn't notice that this issue is in the insertSelect. Don't think .Experimental works with that.