Using UNION in a subquery leads to a syntax error in Sqlite

[hakuya]

Hi, I'm attempting to use a UNION in a subquery, and that is leading to a syntax error when executing the query against a Sqlite backend. Based on attempting to understand the issue, I believe it is a bug in the ORM similar to this other project.

gocraft/dbr#167

Apparently MySQL and Postgres tolerate (SELECT ...) UNION (SELECT ...), but this is not correct syntax. There should be no parenthesis around the individual selects around a union. It should be SELECT ... UNION SELECT ....

The code I'm running is,

        val files = this.session
                .from( Objects )
                .select( Objects.object_id )
                .where { Objects.object_type eq TYPE_FILE }

        val albums = this.session
                .from( Objects )
                .select( Objects.object_id )
                .where { Objects.object_type eq TYPE_ALBUM }

        val all_children = this.session
                .from( Relations )
                .select( Relations.child_id )
                .where { Relations.parent_id inList albums }
    
        val free_files = files.where { Objects.object_id notInList all_children }

        val select_ids = free_files.union( albums )

        val query = this.session
                .from( Objects )
                .select()
                .where { Objects.object_id inList select_ids }
                .orderBy( randOrder() )

Which yeilds the following query:

SELECT *
FROM objects
WHERE objects.object_id IN (
  (
    SELECT objects.object_id AS objects_object_id
    FROM objects WHERE objects.object_id NOT IN (
      SELECT relations.child_id AS relations_child_id
      FROM relations
      WHERE relations.parent_id IN (
        SELECT objects.object_id AS objects_object_id
        FROM objects WHERE objects.object_type = ?
      )
    )
  )
  UNION (
    SELECT objects.object_id AS objects_object_id
    FROM objects
    WHERE objects.object_type = ?
  )
)
ORDER BY RANDOM();

Both when executed via the code, and directly into sqlite3 this query fails with,
Error: near "UNION": syntax error

Manually removing the extra parenthesis such that the query reads as follows, works.

SELECT *
FROM objects
WHERE objects.object_id IN (
    SELECT objects.object_id AS objects_object_id
    FROM objects WHERE objects.object_id NOT IN (
      SELECT relations.child_id AS relations_child_id
      FROM relations
      WHERE relations.parent_id IN (
        SELECT objects.object_id AS objects_object_id
        FROM objects WHERE objects.object_type = ?
      )
    )
  UNION
    SELECT objects.object_id AS objects_object_id
    FROM objects
    WHERE objects.object_type = ?
)
ORDER BY RANDOM();

[vincentlauvlwj]

Thank you, will fix in next version