[Sqlalchemy-tickets] [sqlalchemy] #2779: Join order determinism in polymorphic loads

[sqlalchemy <mi...@zz...>]

#2779: Join order determinism in polymorphic loads
-------------------------+-----------------------------------------
 Reporter:  aelaguiz     |           Owner:  zzzeek
     Type:  defect       |          Status:  new
 Priority:  medium       |       Milestone:
Component:  cextensions  |        Severity:  no triage selected yet
 Keywords:               |  Progress State:  awaiting triage
-------------------------+-----------------------------------------
 Run 1:

 SELECT entities.id AS entities_id, entities.parent_id AS
 entities_parent_id, entities.type_id AS entities_type_id, entities.name AS
 entities_name, entities.created_at AS entities_created_at,
 entities.hotness AS entities_hotness, entities.hotness_dirty AS
 entities_hotness_dirty, entities.modified_at AS entities_modified_at,
 entities.up_votes AS entities_up_votes, entities.down_votes AS
 entities_down_votes, categories.id AS categories_id, videos.id AS
 videos_id, videos.video_type_id AS videos_video_type_id, videos.poster_id
 AS videos_poster_id, videos.key AS videos_key, clubs.id AS clubs_id,
 brands.id AS brands_id, pcp_points.id AS pcp_points_id,
 pcp_points.poster_id AS pcp_points_poster_id, pcp_points.subject_id AS
 pcp_points_subject_id, pcp_points.is_point AS pcp_points_is_point,
 pcp_points.body AS pcp_points_body, pcp_points.origin_url AS
 pcp_points_origin_url, tags.id AS tags_id, pcp_vote_targets.id AS
 pcp_vote_targets_id, stufff_images.id AS stufff_images_id,
 stufff_images.image_id AS stufff_images_image_id, stufff_images.poster_id
 AS stufff_images_poster_id, stufff_images.source AS stufff_images_source,
 link_posts.id AS link_posts_id, link_posts.url AS link_posts_url,
 link_posts.edited_at AS link_posts_edited_at, link_posts.poster_id AS
 link_posts_poster_id, users.id AS users_id, users.email AS users_email,
 users.username AS users_username, users.first_name AS users_first_name,
 users.last_name AS users_last_name, users.picture AS users_picture,
 users.origin_url AS users_origin_url, users.city AS users_city,
 users.state AS users_state, users.country AS users_country, users.gender
 AS users_gender, users.password AS users_password, users.bio AS users_bio,
 users.email_verified AS users_email_verified, users.email_validation AS
 users_email_validation, users.temp_password AS users_temp_password,
 users.temp_password_expiry AS users_temp_password_expiry, users.active AS
 users_active, users.admin AS users_admin, users.reputation AS
 users_reputation, text_posts.id AS text_posts_id, text_posts.body AS
 text_posts_body, text_posts.edited_at AS text_posts_edited_at,
 text_posts.poster_id AS text_posts_poster_id, products.id AS products_id,
 pcp_posts.id AS pcp_posts_id, pcp_posts.poster_id AS pcp_posts_poster_id,
 pcp_posts.subject_id_1 AS pcp_posts_subject_id_1, pcp_posts.subject_id_2
 AS pcp_posts_subject_id_2, pcp_posts.vote_id_1 AS pcp_posts_vote_id_1,
 pcp_posts.vote_id_2 AS pcp_posts_vote_id_2, pcp_posts.top_point_id AS
 pcp_posts_top_point_id, comments.id AS comments_id, comments.body AS
 comments_body, comments.poster_id AS comments_poster_id, anon_1.users_id
 AS anon_1_users_id, anon_1.entities_id AS anon_1_entities_id,
 anon_1.entities_parent_id AS anon_1_entities_parent_id,
 anon_1.entities_type_id AS anon_1_entities_type_id, anon_1.entities_name
 AS anon_1_entities_name, anon_1.entities_created_at AS
 anon_1_entities_created_at, anon_1.entities_hotness AS
 anon_1_entities_hotness, anon_1.entities_hotness_dirty AS
 anon_1_entities_hotness_dirty, anon_1.entities_modified_at AS
 anon_1_entities_modified_at, anon_1.entities_up_votes AS
 anon_1_entities_up_votes, anon_1.entities_down_votes AS
 anon_1_entities_down_votes, anon_1.users_email AS anon_1_users_email,
 anon_1.users_username AS anon_1_users_username, anon_1.users_first_name AS
 anon_1_users_first_name, anon_1.users_last_name AS anon_1_users_last_name,
 anon_1.users_picture AS anon_1_users_picture, anon_1.users_origin_url AS
 anon_1_users_origin_url, anon_1.users_city AS anon_1_users_city,
 anon_1.users_state AS anon_1_users_state, anon_1.users_country AS
 anon_1_users_country, anon_1.users_gender AS anon_1_users_gender,
 anon_1.users_password AS anon_1_users_password, anon_1.users_bio AS
 anon_1_users_bio, anon_1.users_email_verified AS
 anon_1_users_email_verified, anon_1.users_email_validation AS
 anon_1_users_email_validation, anon_1.users_temp_password AS
 anon_1_users_temp_password, anon_1.users_temp_password_expiry AS
 anon_1_users_temp_password_expiry, anon_1.users_active AS
 anon_1_users_active, anon_1.users_admin AS anon_1_users_admin,
 anon_1.users_reputation AS anon_1_users_reputation, anon_2.users_id AS
 anon_2_users_id, anon_2.entities_id AS anon_2_entities_id,
 anon_2.entities_parent_id AS anon_2_entities_parent_id,
 anon_2.entities_type_id AS anon_2_entities_type_id, anon_2.entities_name
 AS anon_2_entities_name, anon_2.entities_created_at AS
 anon_2_entities_created_at, anon_2.entities_hotness AS
 anon_2_entities_hotness, anon_2.entities_hotness_dirty AS
 anon_2_entities_hotness_dirty, anon_2.entities_modified_at AS
 anon_2_entities_modified_at, anon_2.entities_up_votes AS
 anon_2_entities_up_votes, anon_2.entities_down_votes AS
 anon_2_entities_down_votes, anon_2.users_email AS anon_2_users_email,
 anon_2.users_username AS anon_2_users_username, anon_2.users_first_name AS
 anon_2_users_first_name, anon_2.users_last_name AS anon_2_users_last_name,
 anon_2.users_picture AS anon_2_users_picture, anon_2.users_origin_url AS
 anon_2_users_origin_url, anon_2.users_city AS anon_2_users_city,
 anon_2.users_state AS anon_2_users_state, anon_2.users_country AS
 anon_2_users_country, anon_2.users_gender AS anon_2_users_gender,
 anon_2.users_password AS anon_2_users_password, anon_2.users_bio AS
 anon_2_users_bio, anon_2.users_email_verified AS
 anon_2_users_email_verified, anon_2.users_email_validation AS
 anon_2_users_email_validation, anon_2.users_temp_password AS
 anon_2_users_temp_password, anon_2.users_temp_password_expiry AS
 anon_2_users_temp_password_expiry, anon_2.users_active AS
 anon_2_users_active, anon_2.users_admin AS anon_2_users_admin,
 anon_2.users_reputation AS anon_2_users_reputation
 FROM associated_view, entities LEFT OUTER JOIN categories ON entities.id =
 categories.id LEFT OUTER JOIN videos ON entities.id = videos.id LEFT OUTER
 JOIN clubs ON entities.id = clubs.id LEFT OUTER JOIN brands ON entities.id
 = brands.id LEFT OUTER JOIN pcp_points ON pcp_points.id = entities.id LEFT
 OUTER JOIN tags ON entities.id = tags.id LEFT OUTER JOIN pcp_vote_targets
 ON entities.id = pcp_vote_targets.id LEFT OUTER JOIN stufff_images ON
 entities.id = stufff_images.id LEFT OUTER JOIN link_posts ON entities.id =
 link_posts.id LEFT OUTER JOIN users ON entities.id = users.id LEFT OUTER
 JOIN text_posts ON entities.id = text_posts.id LEFT OUTER JOIN products ON
 entities.id = products.id LEFT OUTER JOIN pcp_posts ON pcp_posts.id =
 entities.id LEFT OUTER JOIN comments ON entities.id = comments.id LEFT
 OUTER JOIN (SELECT entities.id AS entities_id, entities.parent_id AS
 entities_parent_id, entities.type_id AS entities_type_id, entities.name AS
 entities_name, entities.created_at AS entities_created_at,
 entities.hotness AS entities_hotness, entities.hotness_dirty AS
 entities_hotness_dirty, entities.modified_at AS entities_modified_at,
 entities.up_votes AS entities_up_votes, entities.down_votes AS
 entities_down_votes, users.id AS users_id, users.email AS users_email,
 users.username AS users_username, users.first_name AS users_first_name,
 users.last_name AS users_last_name, users.picture AS users_picture,
 users.origin_url AS users_origin_url, users.city AS users_city,
 users.state AS users_state, users.country AS users_country, users.gender
 AS users_gender, users.password AS users_password, users.bio AS users_bio,
 users.email_verified AS users_email_verified, users.email_validation AS
 users_email_validation, users.temp_password AS users_temp_password,
 users.temp_password_expiry AS users_temp_password_expiry, users.active AS
 users_active, users.admin AS users_admin, users.reputation AS
 users_reputation
 FROM entities JOIN users ON entities.id = users.id) AS anon_1 ON
 anon_1.users_id = pcp_points.poster_id LEFT OUTER JOIN (SELECT entities.id
 AS entities_id, entities.parent_id AS entities_parent_id, entities.type_id
 AS entities_type_id, entities.name AS entities_name, entities.created_at
 AS entities_created_at, entities.hotness AS entities_hotness,
 entities.hotness_dirty AS entities_hotness_dirty, entities.modified_at AS
 entities_modified_at, entities.up_votes AS entities_up_votes,
 entities.down_votes AS entities_down_votes, users.id AS users_id,
 users.email AS users_email, users.username AS users_username,
 users.first_name AS users_first_name, users.last_name AS users_last_name,
 users.picture AS users_picture, users.origin_url AS users_origin_url,
 users.city AS users_city, users.state AS users_state, users.country AS
 users_country, users.gender AS users_gender, users.password AS
 users_password, users.bio AS users_bio, users.email_verified AS
 users_email_verified, users.email_validation AS users_email_validation,
 users.temp_password AS users_temp_password, users.temp_password_expiry AS
 users_temp_password_expiry, users.active AS users_active, users.admin AS
 users_admin, users.reputation AS users_reputation
 FROM entities JOIN users ON entities.id = users.id) AS anon_2 ON
 anon_2.users_id = pcp_posts.poster_id
 WHERE :param_1 = associated_view.left_id AND pcp_posts.id =
 associated_view.right_id 375

 Run 2:

 SELECT entities.id AS entities_id, entities.parent_id AS
 entities_parent_id, entities.type_id AS entities_type_id, entities.name AS
 entities_name, entities.created_at AS entities_created_at,
 entities.hotness AS entities_hotness, entities.hotness_dirty AS
 entities_hotness_dirty, entities.modified_at AS entities_modified_at,
 entities.up_votes AS entities_up_votes, entities.down_votes AS
 entities_down_votes, videos.id AS videos_id, videos.video_type_id AS
 videos_video_type_id, videos.poster_id AS videos_poster_id, videos.key AS
 videos_key, clubs.id AS clubs_id, brands.id AS brands_id, categories.id AS
 categories_id, comments.id AS comments_id, comments.body AS comments_body,
 comments.poster_id AS comments_poster_id, tags.id AS tags_id,
 pcp_vote_targets.id AS pcp_vote_targets_id, pcp_posts.id AS pcp_posts_id,
 pcp_posts.poster_id AS pcp_posts_poster_id, pcp_posts.subject_id_1 AS
 pcp_posts_subject_id_1, pcp_posts.subject_id_2 AS pcp_posts_subject_id_2,
 pcp_posts.vote_id_1 AS pcp_posts_vote_id_1, pcp_posts.vote_id_2 AS
 pcp_posts_vote_id_2, pcp_posts.top_point_id AS pcp_posts_top_point_id,
 link_posts.id AS link_posts_id, link_posts.url AS link_posts_url,
 link_posts.edited_at AS link_posts_edited_at, link_posts.poster_id AS
 link_posts_poster_id, users.id AS users_id, users.email AS users_email,
 users.username AS users_username, users.first_name AS users_first_name,
 users.last_name AS users_last_name, users.picture AS users_picture,
 users.origin_url AS users_origin_url, users.city AS users_city,
 users.state AS users_state, users.country AS users_country, users.gender
 AS users_gender, users.password AS users_password, users.bio AS users_bio,
 users.email_verified AS users_email_verified, users.email_validation AS
 users_email_validation, users.temp_password AS users_temp_password,
 users.temp_password_expiry AS users_temp_password_expiry, users.active AS
 users_active, users.admin AS users_admin, users.reputation AS
 users_reputation, text_posts.id AS text_posts_id, text_posts.body AS
 text_posts_body, text_posts.edited_at AS text_posts_edited_at,
 text_posts.poster_id AS text_posts_poster_id, products.id AS products_id,
 stufff_images.id AS stufff_images_id, stufff_images.image_id AS
 stufff_images_image_id, stufff_images.poster_id AS
 stufff_images_poster_id, stufff_images.source AS stufff_images_source,
 pcp_points.id AS pcp_points_id, pcp_points.poster_id AS
 pcp_points_poster_id, pcp_points.subject_id AS pcp_points_subject_id,
 pcp_points.is_point AS pcp_points_is_point, pcp_points.body AS
 pcp_points_body, pcp_points.origin_url AS pcp_points_origin_url,
 anon_1.users_id AS anon_1_users_id, anon_1.entities_id AS
 anon_1_entities_id, anon_1.entities_parent_id AS
 anon_1_entities_parent_id, anon_1.entities_type_id AS
 anon_1_entities_type_id, anon_1.entities_name AS anon_1_entities_name,
 anon_1.entities_created_at AS anon_1_entities_created_at,
 anon_1.entities_hotness AS anon_1_entities_hotness,
 anon_1.entities_hotness_dirty AS anon_1_entities_hotness_dirty,
 anon_1.entities_modified_at AS anon_1_entities_modified_at,
 anon_1.entities_up_votes AS anon_1_entities_up_votes,
 anon_1.entities_down_votes AS anon_1_entities_down_votes,
 anon_1.users_email AS anon_1_users_email, anon_1.users_username AS
 anon_1_users_username, anon_1.users_first_name AS anon_1_users_first_name,
 anon_1.users_last_name AS anon_1_users_last_name, anon_1.users_picture AS
 anon_1_users_picture, anon_1.users_origin_url AS anon_1_users_origin_url,
 anon_1.users_city AS anon_1_users_city, anon_1.users_state AS
 anon_1_users_state, anon_1.users_country AS anon_1_users_country,
 anon_1.users_gender AS anon_1_users_gender, anon_1.users_password AS
 anon_1_users_password, anon_1.users_bio AS anon_1_users_bio,
 anon_1.users_email_verified AS anon_1_users_email_verified,
 anon_1.users_email_validation AS anon_1_users_email_validation,
 anon_1.users_temp_password AS anon_1_users_temp_password,
 anon_1.users_temp_password_expiry AS anon_1_users_temp_password_expiry,
 anon_1.users_active AS anon_1_users_active, anon_1.users_admin AS
 anon_1_users_admin, anon_1.users_reputation AS anon_1_users_reputation,
 anon_2.users_id AS anon_2_users_id, anon_2.entities_id AS
 anon_2_entities_id, anon_2.entities_parent_id AS
 anon_2_entities_parent_id, anon_2.entities_type_id AS
 anon_2_entities_type_id, anon_2.entities_name AS anon_2_entities_name,
 anon_2.entities_created_at AS anon_2_entities_created_at,
 anon_2.entities_hotness AS anon_2_entities_hotness,
 anon_2.entities_hotness_dirty AS anon_2_entities_hotness_dirty,
 anon_2.entities_modified_at AS anon_2_entities_modified_at,
 anon_2.entities_up_votes AS anon_2_entities_up_votes,
 anon_2.entities_down_votes AS anon_2_entities_down_votes,
 anon_2.users_email AS anon_2_users_email, anon_2.users_username AS
 anon_2_users_username, anon_2.users_first_name AS anon_2_users_first_name,
 anon_2.users_last_name AS anon_2_users_last_name, anon_2.users_picture AS
 anon_2_users_picture, anon_2.users_origin_url AS anon_2_users_origin_url,
 anon_2.users_city AS anon_2_users_city, anon_2.users_state AS
 anon_2_users_state, anon_2.users_country AS anon_2_users_country,
 anon_2.users_gender AS anon_2_users_gender, anon_2.users_password AS
 anon_2_users_password, anon_2.users_bio AS anon_2_users_bio,
 anon_2.users_email_verified AS anon_2_users_email_verified,
 anon_2.users_email_validation AS anon_2_users_email_validation,
 anon_2.users_temp_password AS anon_2_users_temp_password,
 anon_2.users_temp_password_expiry AS anon_2_users_temp_password_expiry,
 anon_2.users_active AS anon_2_users_active, anon_2.users_admin AS
 anon_2_users_admin, anon_2.users_reputation AS anon_2_users_reputation
 FROM associated_view, entities LEFT OUTER JOIN videos ON entities.id =
 videos.id LEFT OUTER JOIN clubs ON entities.id = clubs.id LEFT OUTER JOIN
 brands ON entities.id = brands.id LEFT OUTER JOIN categories ON
 entities.id = categories.id LEFT OUTER JOIN comments ON entities.id =
 comments.id LEFT OUTER JOIN tags ON entities.id = tags.id LEFT OUTER JOIN
 pcp_vote_targets ON entities.id = pcp_vote_targets.id LEFT OUTER JOIN
 pcp_posts ON pcp_posts.id = entities.id LEFT OUTER JOIN link_posts ON
 entities.id = link_posts.id LEFT OUTER JOIN users ON entities.id =
 users.id LEFT OUTER JOIN text_posts ON entities.id = text_posts.id LEFT
 OUTER JOIN products ON entities.id = products.id LEFT OUTER JOIN
 stufff_images ON entities.id = stufff_images.id LEFT OUTER JOIN pcp_points
 ON pcp_points.id = entities.id LEFT OUTER JOIN (SELECT entities.id AS
 entities_id, entities.parent_id AS entities_parent_id, entities.type_id AS
 entities_type_id, entities.name AS entities_name, entities.created_at AS
 entities_created_at, entities.hotness AS entities_hotness,
 entities.hotness_dirty AS entities_hotness_dirty, entities.modified_at AS
 entities_modified_at, entities.up_votes AS entities_up_votes,
 entities.down_votes AS entities_down_votes, users.id AS users_id,
 users.email AS users_email, users.username AS users_username,
 users.first_name AS users_first_name, users.last_name AS users_last_name,
 users.picture AS users_picture, users.origin_url AS users_origin_url,
 users.city AS users_city, users.state AS users_state, users.country AS
 users_country, users.gender AS users_gender, users.password AS
 users_password, users.bio AS users_bio, users.email_verified AS
 users_email_verified, users.email_validation AS users_email_validation,
 users.temp_password AS users_temp_password, users.temp_password_expiry AS
 users_temp_password_expiry, users.active AS users_active, users.admin AS
 users_admin, users.reputation AS users_reputation
 FROM entities JOIN users ON entities.id = users.id) AS anon_1 ON
 anon_1.users_id = pcp_posts.poster_id LEFT OUTER JOIN (SELECT entities.id
 AS entities_id, entities.parent_id AS entities_parent_id, entities.type_id
 AS entities_type_id, entities.name AS entities_name, entities.created_at
 AS entities_created_at, entities.hotness AS entities_hotness,
 entities.hotness_dirty AS entities_hotness_dirty, entities.modified_at AS
 entities_modified_at, entities.up_votes AS entities_up_votes,
 entities.down_votes AS entities_down_votes, users.id AS users_id,
 users.email AS users_email, users.username AS users_username,
 users.first_name AS users_first_name, users.last_name AS users_last_name,
 users.picture AS users_picture, users.origin_url AS users_origin_url,
 users.city AS users_city, users.state AS users_state, users.country AS
 users_country, users.gender AS users_gender, users.password AS
 users_password, users.bio AS users_bio, users.email_verified AS
 users_email_verified, users.email_validation AS users_email_validation,
 users.temp_password AS users_temp_password, users.temp_password_expiry AS
 users_temp_password_expiry, users.active AS users_active, users.admin AS
 users_admin, users.reputation AS users_reputation
 FROM entities JOIN users ON entities.id = users.id) AS anon_2 ON
 anon_2.users_id = pcp_points.poster_id
 WHERE :param_1 = associated_view.left_id AND pcp_posts.id =
 associated_view.right_id 375

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2779>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python