I apologize up front for not being able to provide a reproducible example
of my problem.
We have built a web app in sqlalchemy for read-only access of our
production database. We are in the process of migrating from one database
architecture (Sybase) to another (Postgres). The goal is to have this
read-only web app be able to query both database styles by switching the
dialect. And so far this has been working spectacularly.
However, I have run into a stubborn error recently. Our system is
essentially a set of query forms, result summaries, and detail pages for
various tables in our database (and their associated relationships). When I
run tests for our whole system, which exercise every page and summary, I
get an error in one of them.
This particular summary is ordering model objects by a column_property.
Because of how our schema is setup, we use these column_properties often to
eager load a single column from a one-to-one relationship (or petal table).
The error is that sometimes it does the order by with a subquery, rather
than using the anonymous alias (e.g. anon_1, anon_2).
Here is a rough (abbreviated) example of the query:
SELECT (SELECT mrk_status.status
FROM mrk_status
WHERE mrk_status._marker_status_key = mrk_marker._marker_status_key) AS
anon_1,
mrk_marker._marker_key AS mrk_marker__marker_key,
mrk_marker._status_key AS mrk_marker__status_key,
mrk_marker._marker_type_key AS mrk_marker__marker_type_key,
etc....
FROM mrk_marker
WHERE mrk_marker.symbol = ?
ORDER BY (SELECT mrk_status.status
FROM mrk_status
WHERE mrk_status._marker_status_key = mrk_marker._marker_status_key),
mrk_marker.symbol
See, it should be ordering by "anon_1, mrk_marker.symbol". And most of the
time it does!
The best I could figure out is that after running one of our other query
summaries, one which is not related to this table above, that is when the
order by clause starts failing. It's as if something in the database engine
state gets messed up (sorry, I don't know much about the internals).
By the way, this happens when I test against either database type, Sybase
or Postgres. However, this is only invalid SQL syntax in Sybase (at least
our version).
Any ideas at all on how to further diagnose this? Is it a known issue? I
don't understand how sometimes it will use the "anon_1" in the order by,
but other times it does a subquery. The rest of the query is always the
same, since it comes from a test suite.
Thanks,
-Kevin
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
