On Jan 2, 10:41 pm, Michael Bayer <mike...@zzzcomputing.com> 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 sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---