Funnily enough I ran into this particular question regarding Peewee ORM
and, googling "Postgresql UNION parentheses", I found this post. I ran into
the same thing where PG likes the parentheses, but SQLite doesn't. I know
this is a very old post, but out of curiosity, how did you end up resolving
this?
On Thursday, July 5, 2012 at 4:35:51 PM UTC-5, Michael Bayer wrote:
>
>
> On Jul 5, 2012, at 4:57 PM, Sergey Shepelev wrote:
>
> Problem: sa.union_all(*qs).order_by('y') will generate invalid SQL like
> this:
>
> SELECT ... ORDER BY x LIMIT 10 UNION ALL SELECT ... ORDER BY x LIMIT 10
> ORDER BY y
>
> Order in inner queries could be required for LIMIT or DISTINCT ON (field).
>
> Solution: sqlalchemy should put all inner queries that contain ORDER BY or
> LIMIT clauses into parenthesis, like this:
>
> SELECT ... UNION ALL (SELECT ... ORDER BY x LIMIT 10) ORDER BY y
>
>
> Test:
> def test_with_order(self):
> q = sa.select([2]).order_by('1')
> union = sa.union(q, q).limit(3)
> db.postgresql.execute(union)
> self.assertEqual(str(union), "(SELECT 2 ORDER BY 1) UNION (SELECT
> 2 ORDER BY 1) LIMIT 3")
>
> Monkey patch at https://gist.github.com/3056292
>
>
> This is tricky because the SQL expression language doesn't like to make
> guesses like that, that is, decides about the structure of the query based
> on things inside of select() constructs. The ORM does that kind of thing
> more acceptably, but it isn't covering this case either at the moment.
>
> The above SQL is not accepted on some backends - that is, it fails on
> SQLite at least. Odd structures like that tend to not work on several of
> the more quirky backends, common culprits are Oracle, Firebird, SQL Server,
> older MySQL versions.
>
> So to make this "magic" here is to make the select() construct behave
> differently on the PG backend, or maybe just not on the SQLite backend,
> but this is tricky to decide without knowing the true compatibility here
> and what's going to break for whom if we make a change like that - the SQL
> expression language tends to want you to manually apply structural
> conventions right now (such as, if you say select().select(), there's no
> "alias" generated automatically, things like that).
>
> I would have expected alias() to work here, but it doesn't. so it's on
> deck to work on why that is at some point.
>
> The point is right now you can force the parens using self_group(), and
> I'd be more comfortable if you stuck with that for this particular case
> right now, though I may change my mind about handling this case in the
> future:
>
> q = select([2]).limit(5).order_by('1').self_group()
> union = union(q, q).limit(3)
> print union
>
> i put up http://www.sqlalchemy.org/trac/ticket/2528, where the goal is to
> test the compatibility of this kind of query across the most common
> backends and see what the best approach might be. I'd probably go as far
> as alias() rending parenthesis, though (that is, self_group() will likely
> remain exactly what you want here).
>
>
>
>
>
>
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
