joined tables with `queryUnion` produces SQL error

[CSchank]

I have the following queries:

queryAllPublishedToGroups :: _ => _ -- something like [Id Group] -> JoinQueryBuilderWrapper joinRegister ModulePublish 
queryAllPublishedToGroups groupIds =
    query @ModulePublish
        |> innerJoin @ModuleGroupPublish (#id, #modulePublishId)
        |> filterWhereInJoinedTable @ModuleGroupPublish (#groupId, groupIds)

queryAllPublishedToUser :: _ => _ -- something like JoinQueryBuilderWrapper joinRegister ModulePublish 
queryAllPublishedToUser =
    query @ModulePublish
        |> innerJoin @ModuleUserPublish (#id, #modulePublishId)
        |> filterWhereJoinedTable @ModuleUserPublish (#userId, currentUserId)

queryAllPublishedToPublic :: _ => _ -- something like JoinQueryBuilderWrapper joinRegister ModulePublish 
queryAllPublishedToPublic =
    query @ModulePublish
        |> filterWhere (#publishExtent, PublishPublic)

In my action they're combined together like:

modulePublishes <- 
           queryAllPublishedToGroups userGroupIds
        |> queryUnion queryAllPublishedToUser
        |> queryUnion queryAllPublishedToPublic
        |> filterWhere (#publishType, AbilityToImport)
        |> fetch
        >>= collectionFetchRelated #snapshotId

And then I run this action, I get:

SqlError {sqlState = "42P01", sqlExecStatus = FatalError, sqlErrorMsg = "missing FROM-clause entry for table \"module_user_publishes\"", sqlErrorDetail = "", sqlErrorHint = ""}

It goes without saying that this has no type errors and compiles; the error shows up only when I run the action. Also, sorry I didn't have a chance to properly figure out the types, I just let GHC do the work for me :)

For now I guess I'll do separate queries and merge them in Haskell land, though I'd like to do it more efficiently obviously.

[CSchank]

@hllizi I believe you worked on joins, so I'm tagging you here as well. :)

[hllizi]

Hi, I just submitted a pull request, but it's not yet a solution that will make your code work, it will just change the error message. Union query builders do not yet support complex queries. The mistake was not to treat join queries as complex.

[CSchank]

Okay, well that's better than nothing for now. I take it it's going to take significant work to make things work properly?

[hllizi]

I really don't know yet, because SQL does such an amazing job at making thinking about it really hard work. It might actually be sufficient to just set the join list of the new query builder to the concatenation of those of the component builders (I suspect it would), but without having a second look I wouldn't bet any money on it. My knowledge of SQL also isn't to intimate yet, to be honest, so I'm prepared for all sorts of trip wires and booby traps.

[mpscholten]

Likely to be on the safe side we could implement the missing error "buildQuery: Union of complex queries not supported yet" branch :) We need to add an UNION operator and it would likely work. Right now the UnionQueryBuilder is only adding OR operators instead of doing a true sql UNION :)