Andrew wrote: > > On Sep 29, 6:14 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote: >> Andrew wrote: >> >> > This is very confusing; I have an ORM generated SQL statement that is >> > joining on a specific id. However, when I run it, for some reason, >> > the specific id (that was joined on) is occasionally None! However, >> > when I run the generated SQL copied from the server's debug log in >> > SQLDeveloper, I get all the IDs correctly. > > Sure, I expected as much, but since the query is complex, I needed > some time to "anonymize it" :) > > The query's ORM is as follows: > > query = meta.Session.query( > ss.c.session_sid, > au, s, iyo, iy, i, hds > )\ > .filter( ss.c.start_date > func.to_date(start_date, 'YYYY-MM- > DD') )\ > .filter( au.c.user_sid == ss.c.user_sid )\ > .filter( au.c.handle == handle )\ > .filter( s.c.user_sid == ss.c.user_sid )\ > .filter( iyo.c.year_sid == s.c.staff_sid)\ > .filter( iy.c.year_sid == iyo.c.year_sid)\ > .filter( iy.c.is_current == 1 )\ > .filter( i.c.sid == iy.c.sid)\ > .filter( "ssd.session_sid(+) = ss.session_sid")\ > .filter( "hds.session_sid(+) = ss.session_sid")\ > .order_by( desc(ss.c.start_date) )\ > .distinct() > > So when I run this and just do a simple loop through the data, > ss.session_sid is appearing as None about 5% of the time. However, > when I copy and paste the generated SQL from the server logs, it is > able to select all of the session_sid's > > And to answer your question, as you can see, yes, there *is* an outer > join on the column that is not being displayed. Is there a problem > with the join? Using the outerjoin() function isn't an option because > this query has been specifically optimized for Oracle--and rewriting > it with the joins adds almost 200% more time to the query. > > As always, your help is always greatly appreeciated!
first of all, SQLA does generate (+) = if you set use_ansi=False in your oracle create_engine, and then use outerjoin. Its obviously not a widely used feature and I'd be curious if it holds up with all your queries (it holds up under the various tests we've constructed for it). secondly, the query object will run a uniqueness function on the rows returned but that shouldn't be affecting much here. If you turn on echo='debug', you'll see the full SQL issued and every row returned. If that dump of rows is exactly as you'd expect, and then the ORM result is inserting Nones, that would be an issue - but there is nothing I'm aware of which is capable of that. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
