On Jan 2, 10:41 pm, Michael Bayer <[email protected]> wrote:
>
> It would help if you could illustrate with accurate code - the UNION
> above does not have consistent numbers of columns in each select() and
> I think what you're trying to do is reverse f_1 and f_2 in the second
> select() statement which is also not illustrated above.
>
> I am able to reproduce your results with the script at the end of this
> email.
Thanks for taking the time to create a coherent example that works
standalone.
I didn't know how to do this as I'm only familiar with using
SQLAlchemy via Elixir and TurboGears.
>
> Its a fun little problem for which I haven't thought of a solution as
> of yet. your union delivers both f_1 and f_2 from its second and
> third columns.
What I am trying to do is select columns labelled 'f_1' and 'f_2' in
the 2nd and 3rd columns, i.e.
>>> u = union(
MyE.__table__.select(),
select([MyE.id, MyE.f_2.label('f_1'), MyE.f_1.label
('f_2'), MyE.date])
)
This gives:
>>> print u.corresponding_column(MyE.__table__.c.f_2)
f_1
Which is the cause of the problem?
The SQL for this also leaves out the f_1 column in the outer select:
>>> print sess.query(MyE).select_from(u).order_by(MyE.date).all()
SELECT anon_1.id AS anon_1_id, anon_1.f_1 AS anon_1_f_1, anon_1.date
AS anon_1_date
FROM (SELECT mine.id AS id, mine.f_1 AS f_1, mine.f_2 AS f_2,
mine.date AS date
FROM mine UNION SELECT mine.id AS id, mine.f_2 AS f_1, mine.f_1 AS
f_2, mine.date AS date
FROM mine) AS anon_1 ORDER BY anon_1.date
Strangely, if I reorder the columns in the second half of the union:
u = union(
MyE.__table__.select(),
select([MyE.id, MyE.f_1.label('f_2'), MyE.f_2.label
('f_1'), MyE.date])
)
I get the correct:
>>> print u.corresponding_column(MyE.__table__.c.f_2)
f_2
And the correct outer SELECT:
SELECT anon_1.id AS anon_1_id, anon_1.f_1 AS anon_1_f_1, anon_1.f_2 AS
anon_1_f_2, anon_1.date AS anon_1_date
But the inner UNION doesn't work (postgres at least), as it seems to
compute the union based on column position, rather than column label.
> If you ask the union, hey which one of your columns
> corresponds to table.c.f_1 , the answer is ambiguous since its both.
> I could change corresponding_column to raise an error for this
> ambiguity, but that wouldnt fix your problem. The thing you "expect"
> here is that the columns in the *first* select of the union should
> take precedence over the subsequent ones. A naive change in that
> regard breaks tests which do in fact place columns in subsequent
> selects which need to be counted so something else will have to be
> figured out. probably some extra guesswork in
> CompoundSelect._populate_column_collection to detect this kind of thing.
>
At the moment I'm replacing the outer SELECT with a SELECT * which
gives the intended results.
> Why do all the really intricate problems seem to come from the elixir
> camp ? :)
Now I feel subversive ;)
Eoghan
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
