there's logic which is removing the order_by's from the selects, and
in that case this is what's blowing away the parenthesis as well.
Some databases don't even allow ORDER BY inside of the queries used in
a UNION since in the absense of LIMIT/OFFSET, which also is not
standard SQL, they have no effect.
However I dont think its good form for SQLA to be whacking the ORDER
BY from the unions if that is in fact what was requested. So this
behavior is changed in the 0.5 series in r5425. As far as the 0.4
series, we're only supporting critical bugfixes there and I'd like to
avoid any behavioral changes (0.4 is also on a more conservative
release schedule). If you're truly stuck with 0.4, you can use
select.order_by(...).alias().select() to get an equivalent query which
is insulated from changes (and is probably more compatible across
databases), or to get exactly the same SQL here's a safe monkeypatch
approach:
from sqlalchemy import *
s = select(["x", "y"]).select_from("table")
def frozen_order_by(s):
s = s.self_group()
s.order_by = lambda *args: s
return s
qlist = [s.limit(10).order_by('x'), s.limit(10).order_by('x')]
print union_all(*[frozen_order_by(q) for q in
qlist]).order_by("foo").limit(10)
frozen_order_by() calls self_group() thereby generating a new select()
so that the original is unchanged.
On Dec 5, 2008, at 5:08 PM, Bo Shi wrote:
>
> Oh, check this out:
>
> (SA 0.4.7)
>
>>>> from sqlalchemy import *
>>>> s = select(["x", "y"]).select_from("table")
>>>> qlist = [s.limit(10).order_by('x').self_group(),
>>>> s.limit(10).order_by('x').self_group()]
>
>>>> print union_all(*qlist).order_by("foo").limit(10)
> SELECT x, y
> FROM table
> LIMIT 10 UNION ALL SELECT x, y
> FROM table
> LIMIT 10 ORDER BY foo
> LIMIT 10
>
>>>> for q in qlist:
> ... print q
> ...
> (SELECT x, y
> FROM table ORDER BY x
> LIMIT 10)
> (SELECT x, y
> FROM table ORDER BY x
> LIMIT 10)
>
>
>
> On Fri, Dec 5, 2008 at 4:55 PM, Bo Shi <[EMAIL PROTECTED]> wrote:
>> I had to upgrade to 0.4.7 from 0.4.2, but your sample query works,
>> however, my application of it does not.
>>
>> Sorry I'm being so light on details, I'll try to reproduce with a
>> complete sample versus using snippets of production code.
>>
>> Each select statement is generated like so:
>>
>> sel = select(pre_select + selectlist,
>> from_obj=join_datatables(tables))
>>
>> I apply self_group() using a list comprehension and if I print each
>> select statement in the list individually, the parentheses show up.
>>
>> If I then union_all(*querylist) and print that, the parentheses
>> disappear.
>>
>> Weird.
>>
>> I should note that the individual selects have filters, an order by
>> clause and a limit, but the following works fine so I would not
>> expect
>> that to be a problem.
>>
>>>>> from sqlalchemy import *
>>>>> s = select(["x", "y"]).select_from("table")
>>>>> print s.self_group()
>> (SELECT x, y
>> FROM table)
>>>>> print union_all(s.self_group(), s.self_group()).order_by("foo")
>> (SELECT x, y
>> FROM table) UNION ALL (SELECT x, y
>> FROM table) ORDER BY foo
>>>>> print union_all(s.limit(10).self_group(),
>>>>> s.limit(10).self_group()).order_by("foo").limit(10)
>> (SELECT x, y
>> FROM table
>> LIMIT 10) UNION ALL (SELECT x, y
>> FROM table
>> LIMIT 10) ORDER BY foo
>> LIMIT 10
>>>>> import sqlalchemy as sa
>>>>> sa.__version__
>> '0.4.7'
>>
>>
>> On Fri, Dec 5, 2008 at 4:23 PM, Michael Bayer <[EMAIL PROTECTED]
>> > wrote:
>>>
>>>
>>>>>> from sqlalchemy import *
>>>>>> s = select(["x", "y"]).select_from("table")
>>>>>> print union_all(s.self_group(), s.self_group()).order_by("foo")
>>> (SELECT x, y
>>> FROM table) UNION ALL (SELECT x, y
>>> FROM table) ORDER BY foo
>>>
>>>
>>> On Dec 5, 2008, at 4:17 PM, Bo Shi wrote:
>>>
>>>>
>>>> Thanks for the quick response!
>>>>
>>>> The following does *not* work. Am I making the call incorrectly?
>>>>
>>>> sel = union_all(*[q.self_group() for q in querylist])
>>>>
>>>>
>>>> On Fri, Dec 5, 2008 at 4:08 PM, Michael Bayer <[EMAIL PROTECTED]
>>>>> wrote:
>>>>>
>>>>> try calling self_group() on each select object.
>>>>>
>>>>> On Dec 5, 2008, at 3:55 PM, Bo Shi wrote:
>>>>>
>>>>>>
>>>>>> Hi all,
>>>>>>
>>>>>> There appear to be some nuances to using order by statements with
>>>>>> set
>>>>>> operations like unions in MySQL but the following is allowed*:
>>>>>>
>>>>>> (SELECT a,b from DBA.tbl ORDER BY b LIMIT 5)
>>>>>> UNION ALL
>>>>>> (SELECT a,b from DBB.tbl ORDER BY b LIMIT 5)
>>>>>> ORDER BY b
>>>>>>
>>>>>> When I attempt to generate such a statement with:
>>>>>>
>>>>>> union_all(*list_of_select_objs),
>>>>>>
>>>>>> The SQL generated lacks parentheses around the SELECT
>>>>>> statements (in
>>>>>> addition to dropping the order by clauses, but that appears to be
>>>>>> expected behavior). Is there a way to put the parentheses in?
>>>>>>
>>>>>>
>>>>>> *just an example, the query i've written is meaningless/
>>>>>> useless :-)
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Bo Shi
>>>>>> 207-469-8264
>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Bo Shi
>>>> 207-469-8264
>>>>
>>>>>
>>>
>>>
>>>>>
>>>
>>
>>
>>
>> --
>> Bo Shi
>> 207-469-8264
>>
>
>
>
> --
> Bo Shi
> 207-469-8264
>
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
