1. Michael Bayer
  2. SQLSoup
  3. Issues
Issue #4 wontfix

Duplicate aliases in a join using with_label'd tables

Sakartu
created an issue

I'm trying to count the number of rows in a join in which with_labels has been used to deal with identical column names:

engine.join(engine.with_labels(engine.phpbb_posts), engine.with_labels(engine.phpbb_users), engine.phpbb_posts.user_id == engine.phpbb_users.user_id).count()

Unfortunately, this gives me the following error:

OperationalError: (OperationalError) (1066, "Not unique table/alias: 'foo'") 'SELECT count(*) AS count_1 \nFROM (SELECT foo.phpbb_posts_post_id AS foo_phpbb_posts_post_id, foo.phpbb_posts_topic_id AS foo_phpbb_posts_topic_id, foo.phpbb_posts_forum_id AS foo_phpbb_posts_forum_id, foo.phpbb_posts_poster_id AS foo_phpbb_posts_poster_id, foo.phpbb_posts_icon_id AS foo_phpbb_posts_icon_id, foo.phpbb_posts_poster_ip AS foo_phpbb_posts_poster_ip, foo.phpbb_posts_post_time AS foo_phpbb_posts_post_time, foo.phpbb_posts_post_approved AS foo_phpbb_posts_post_approved, foo.phpbb_posts_post_reported AS foo_phpbb_posts_post_reported, foo.phpbb_posts_enable_bbcode AS foo_phpbb_posts_enable_bbcode, foo.phpbb_posts_enable_smilies AS foo_phpbb_posts_enable_smilies, foo.phpbb_posts_enable_magic_url AS foo_phpbb_posts_enable_magic_url, foo.phpbb_posts_enable_sig AS foo_phpbb_posts_enable_sig, foo.phpbb_posts_post_username AS foo_phpbb_posts_post_username, foo.phpbb_posts_post_subject AS foo_phpbb_posts_post_subject, foo.phpbb_posts_post_text AS foo_phpbb_posts_post_text, foo.phpbb_posts_post_checksum AS foo_phpbb_posts_post_checksum, foo.phpbb_posts_post_attachment AS foo_phpbb_posts_post_attachment, foo.phpbb_posts_bbcode_bitfield AS foo_phpbb_posts_bbcode_bitfield, foo.phpbb_posts_bbcode_uid AS foo_phpbb_posts_bbcode_uid, foo.phpbb_posts_post_postcount AS foo_phpbb_posts_post_postcount, foo.phpbb_posts_post_edit_time AS foo_phpbb_posts_post_edit_time, foo.phpbb_posts_post_edit_reason AS foo_phpbb_posts_post_edit_reason, foo.phpbb_posts_post_edit_user AS foo_phpbb_posts_post_edit_user, foo.phpbb_posts_post_edit_count AS foo_phpbb_posts_post_edit_count, foo.phpbb_posts_post_edit_locked AS foo_phpbb_posts_post_edit_locked, foo.phpbb_posts_user_id AS foo_phpbb_posts_user_id, foo.phpbb_users_user_id AS foo_phpbb_users_user_id, foo.phpbb_users_user_type AS foo_phpbb_users_user_type, foo.phpbb_users_group_id AS foo_phpbb_users_group_id, foo.phpbb_users_user_permissions AS foo_phpbb_users_user_permissions, foo.phpbb_users_user_perm_from AS foo_phpbb_users_user_perm_from, foo.phpbb_users_user_ip AS foo_phpbb_users_user_ip, foo.phpbb_users_user_regdate AS foo_phpbb_users_user_regdate, foo.phpbb_users_username AS foo_phpbb_users_username, foo.phpbb_users_username_clean AS foo_phpbb_users_username_clean, foo.phpbb_users_user_password AS foo_phpbb_users_user_password, foo.phpbb_users_user_passchg AS foo_phpbb_users_user_passchg, foo.phpbb_users_user_pass_convert AS foo_phpbb_users_user_pass_convert, foo.phpbb_users_user_email AS foo_phpbb_users_user_email, foo.phpbb_users_user_email_hash AS foo_phpbb_users_user_email_hash, foo.phpbb_users_user_birthday AS foo_phpbb_users_user_birthday, foo.phpbb_users_user_lastvisit AS foo_phpbb_users_user_lastvisit, foo.phpbb_users_user_lastmark AS foo_phpbb_users_user_lastmark, foo.phpbb_users_user_lastpost_time AS foo_phpbb_users_user_lastpost_time, foo.phpbb_users_user_lastpage AS foo_phpbb_users_user_lastpage, foo.phpbb_users_user_last_confirm_key AS foo_phpbb_users_user_last_confirm_key, foo.phpbb_users_user_last_search AS foo_phpbb_users_user_last_search, foo.phpbb_users_user_warnings AS foo_phpbb_users_user_warnings, foo.phpbb_users_user_last_warning AS foo_phpbb_users_user_last_warning, foo.phpbb_users_user_login_attempts AS foo_phpbb_users_user_login_attempts, foo.phpbb_users_user_inactive_reason AS foo_phpbb_users_user_inactive_reason, foo.phpbb_users_user_inactive_time AS foo_phpbb_users_user_inactive_time, foo.phpbb_users_user_posts AS foo_phpbb_users_user_posts, foo.phpbb_users_user_lang AS foo_phpbb_users_user_lang, foo.phpbb_users_user_timezone AS foo_phpbb_users_user_timezone, foo.phpbb_users_user_dst AS foo_phpbb_users_user_dst, foo.phpbb_users_user_dateformat AS foo_phpbb_users_user_dateformat, foo.phpbb_users_user_style AS foo_phpbb_users_user_style, foo.phpbb_users_user_rank AS foo_phpbb_users_user_rank, foo.phpbb_users_user_colour AS foo_phpbb_users_user_colour, foo.phpbb_users_user_new_privmsg AS foo_phpbb_users_user_new_privmsg, foo.phpbb_users_user_unread_privmsg AS foo_phpbb_users_user_unread_privmsg, foo.phpbb_users_user_last_privmsg AS foo_phpbb_users_user_last_privmsg, foo.phpbb_users_user_message_rules AS foo_phpbb_users_user_message_rules, foo.phpbb_users_user_full_folder AS foo_phpbb_users_user_full_folder, foo.phpbb_users_user_emailtime AS foo_phpbb_users_user_emailtime, foo.phpbb_users_user_topic_show_days AS foo_phpbb_users_user_topic_show_days, foo.phpbb_users_user_topic_sortby_type AS foo_phpbb_users_user_topic_sortby_type, foo.phpbb_users_user_topic_sortby_dir AS foo_phpbb_users_user_topic_sortby_dir, foo.phpbb_users_user_post_show_days AS foo_phpbb_users_user_post_show_days, foo.phpbb_users_user_post_sortby_type AS foo_phpbb_users_user_post_sortby_type, foo.phpbb_users_user_post_sortby_dir AS foo_phpbb_users_user_post_sortby_dir, foo.phpbb_users_user_notify AS foo_phpbb_users_user_notify, foo.phpbb_users_user_notify_pm AS foo_phpbb_users_user_notify_pm, foo.phpbb_users_user_notify_type AS foo_phpbb_users_user_notify_type, foo.phpbb_users_user_allow_pm AS foo_phpbb_users_user_allow_pm, foo.phpbb_users_user_allow_viewonline AS foo_phpbb_users_user_allow_viewonline, foo.phpbb_users_user_allow_viewemail AS foo_phpbb_users_user_allow_viewemail, foo.phpbb_users_user_allow_massemail AS foo_phpbb_users_user_allow_massemail, foo.phpbb_users_user_options AS foo_phpbb_users_user_options, foo.phpbb_users_user_avatar AS foo_phpbb_users_user_avatar, foo.phpbb_users_user_avatar_type AS foo_phpbb_users_user_avatar_type, foo.phpbb_users_user_avatar_width AS foo_phpbb_users_user_avatar_width, foo.phpbb_users_user_avatar_height AS foo_phpbb_users_user_avatar_height, foo.phpbb_users_user_sig AS foo_phpbb_users_user_sig, foo.phpbb_users_user_sig_bbcode_uid AS foo_phpbb_users_user_sig_bbcode_uid, foo.phpbb_users_user_sig_bbcode_bitfield AS foo_phpbb_users_user_sig_bbcode_bitfield, foo.phpbb_users_user_from AS foo_phpbb_users_user_from, foo.phpbb_users_user_icq AS foo_phpbb_users_user_icq, foo.phpbb_users_user_aim AS foo_phpbb_users_user_aim, foo.phpbb_users_user_yim AS foo_phpbb_users_user_yim, foo.phpbb_users_user_msnm AS foo_phpbb_users_user_msnm, foo.phpbb_users_user_jabber AS foo_phpbb_users_user_jabber, foo.phpbb_users_user_website AS foo_phpbb_users_user_website, foo.phpbb_users_user_occ AS foo_phpbb_users_user_occ, foo.phpbb_users_user_interests AS foo_phpbb_users_user_interests, foo.phpbb_users_user_actkey AS foo_phpbb_users_user_actkey, foo.phpbb_users_user_newpasswd AS foo_phpbb_users_user_newpasswd, foo.phpbb_users_user_form_salt AS foo_phpbb_users_user_form_salt, foo.phpbb_users_user_new AS foo_phpbb_users_user_new, foo.phpbb_users_user_reminded AS foo_phpbb_users_user_reminded, foo.phpbb_users_user_reminded_time AS foo_phpbb_users_user_reminded_time \nFROM (SELECT phpbb_posts.post_id AS phpbb_posts_post_id, phpbb_posts.topic_id AS phpbb_posts_topic_id, phpbb_posts.forum_id AS phpbb_posts_forum_id, phpbb_posts.poster_id AS phpbb_posts_poster_id, phpbb_posts.icon_id AS phpbb_posts_icon_id, phpbb_posts.poster_ip AS phpbb_posts_poster_ip, phpbb_posts.post_time AS phpbb_posts_post_time, phpbb_posts.post_approved AS phpbb_posts_post_approved, phpbb_posts.post_reported AS phpbb_posts_post_reported, phpbb_posts.enable_bbcode AS phpbb_posts_enable_bbcode, phpbb_posts.enable_smilies AS phpbb_posts_enable_smilies, phpbb_posts.enable_magic_url AS phpbb_posts_enable_magic_url, phpbb_posts.enable_sig AS phpbb_posts_enable_sig, phpbb_posts.post_username AS phpbb_posts_post_username, phpbb_posts.post_subject AS phpbb_posts_post_subject, phpbb_posts.post_text AS phpbb_posts_post_text, phpbb_posts.post_checksum AS phpbb_posts_post_checksum, phpbb_posts.post_attachment AS phpbb_posts_post_attachment, phpbb_posts.bbcode_bitfield AS phpbb_posts_bbcode_bitfield, phpbb_posts.bbcode_uid AS phpbb_posts_bbcode_uid, phpbb_posts.post_postcount AS phpbb_posts_post_postcount, phpbb_posts.post_edit_time AS phpbb_posts_post_edit_time, phpbb_posts.post_edit_reason AS phpbb_posts_post_edit_reason, phpbb_posts.post_edit_user AS phpbb_posts_post_edit_user, phpbb_posts.post_edit_count AS phpbb_posts_post_edit_count, phpbb_posts.post_edit_locked AS phpbb_posts_post_edit_locked, phpbb_posts.user_id AS phpbb_posts_user_id \nFROM phpbb_posts) AS foo INNER JOIN (SELECT phpbb_users.user_id AS phpbb_users_user_id, phpbb_users.user_type AS phpbb_users_user_type, phpbb_users.group_id AS phpbb_users_group_id, phpbb_users.user_permissions AS phpbb_users_user_permissions, phpbb_users.user_perm_from AS phpbb_users_user_perm_from, phpbb_users.user_ip AS phpbb_users_user_ip, phpbb_users.user_regdate AS phpbb_users_user_regdate, phpbb_users.username AS phpbb_users_username, phpbb_users.username_clean AS phpbb_users_username_clean, phpbb_users.user_password AS phpbb_users_user_password, phpbb_users.user_passchg AS phpbb_users_user_passchg, phpbb_users.user_pass_convert AS phpbb_users_user_pass_convert, phpbb_users.user_email AS phpbb_users_user_email, phpbb_users.user_email_hash AS phpbb_users_user_email_hash, phpbb_users.user_birthday AS phpbb_users_user_birthday, phpbb_users.user_lastvisit AS phpbb_users_user_lastvisit, phpbb_users.user_lastmark AS phpbb_users_user_lastmark, phpbb_users.user_lastpost_time AS phpbb_users_user_lastpost_time, phpbb_users.user_lastpage AS phpbb_users_user_lastpage, phpbb_users.user_last_confirm_key AS phpbb_users_user_last_confirm_key, phpbb_users.user_last_search AS phpbb_users_user_last_search, phpbb_users.user_warnings AS phpbb_users_user_warnings, phpbb_users.user_last_warning AS phpbb_users_user_last_warning, phpbb_users.user_login_attempts AS phpbb_users_user_login_attempts, phpbb_users.user_inactive_reason AS phpbb_users_user_inactive_reason, phpbb_users.user_inactive_time AS phpbb_users_user_inactive_time, phpbb_users.user_posts AS phpbb_users_user_posts, phpbb_users.user_lang AS phpbb_users_user_lang, phpbb_users.user_timezone AS phpbb_users_user_timezone, phpbb_users.user_dst AS phpbb_users_user_dst, phpbb_users.user_dateformat AS phpbb_users_user_dateformat, phpbb_users.user_style AS phpbb_users_user_style, phpbb_users.user_rank AS phpbb_users_user_rank, phpbb_users.user_colour AS phpbb_users_user_colour, phpbb_users.user_new_privmsg AS phpbb_users_user_new_privmsg, phpbb_users.user_unread_privmsg AS phpbb_users_user_unread_privmsg, phpbb_users.user_last_privmsg AS phpbb_users_user_last_privmsg, phpbb_users.user_message_rules AS phpbb_users_user_message_rules, phpbb_users.user_full_folder AS phpbb_users_user_full_folder, phpbb_users.user_emailtime AS phpbb_users_user_emailtime, phpbb_users.user_topic_show_days AS phpbb_users_user_topic_show_days, phpbb_users.user_topic_sortby_type AS phpbb_users_user_topic_sortby_type, phpbb_users.user_topic_sortby_dir AS phpbb_users_user_topic_sortby_dir, phpbb_users.user_post_show_days AS phpbb_users_user_post_show_days, phpbb_users.user_post_sortby_type AS phpbb_users_user_post_sortby_type, phpbb_users.user_post_sortby_dir AS phpbb_users_user_post_sortby_dir, phpbb_users.user_notify AS phpbb_users_user_notify, phpbb_users.user_notify_pm AS phpbb_users_user_notify_pm, phpbb_users.user_notify_type AS phpbb_users_user_notify_type, phpbb_users.user_allow_pm AS phpbb_users_user_allow_pm, phpbb_users.user_allow_viewonline AS phpbb_users_user_allow_viewonline, phpbb_users.user_allow_viewemail AS phpbb_users_user_allow_viewemail, phpbb_users.user_allow_massemail AS phpbb_users_user_allow_massemail, phpbb_users.user_options AS phpbb_users_user_options, phpbb_users.user_avatar AS phpbb_users_user_avatar, phpbb_users.user_avatar_type AS phpbb_users_user_avatar_type, phpbb_users.user_avatar_width AS phpbb_users_user_avatar_width, phpbb_users.user_avatar_height AS phpbb_users_user_avatar_height, phpbb_users.user_sig AS phpbb_users_user_sig, phpbb_users.user_sig_bbcode_uid AS phpbb_users_user_sig_bbcode_uid, phpbb_users.user_sig_bbcode_bitfield AS phpbb_users_user_sig_bbcode_bitfield, phpbb_users.user_from AS phpbb_users_user_from, phpbb_users.user_icq AS phpbb_users_user_icq, phpbb_users.user_aim AS phpbb_users_user_aim, phpbb_users.user_yim AS phpbb_users_user_yim, phpbb_users.user_msnm AS phpbb_users_user_msnm, phpbb_users.user_jabber AS phpbb_users_user_jabber, phpbb_users.user_website AS phpbb_users_user_website, phpbb_users.user_occ AS phpbb_users_user_occ, phpbb_users.user_interests AS phpbb_users_user_interests, phpbb_users.user_actkey AS phpbb_users_user_actkey, phpbb_users.user_newpasswd AS phpbb_users_user_newpasswd, phpbb_users.user_form_salt AS phpbb_users_user_form_salt, phpbb_users.user_new AS phpbb_users_user_new, phpbb_users.user_reminded AS phpbb_users_user_reminded, phpbb_users.user_reminded_time AS phpbb_users_user_reminded_time \nFROM phpbb_users) AS foo ON phpbb_posts.user_id = phpbb_users.user_id) AS anon_1' ()

As can be seen in the error, both the left part of the join and the right part of the join are given the alias 'foo', which I think is responsible for the error; duplicate aliases.

Any idea how this could be fixed?

Comments (2)

  1. Michael Bayer repo owner

    well that's not really how join() works, it should be query(left).join(right, onclause), there's no query(left).join(left, right, onclause) so that's just confusing it. Also using with_labels() like that is not working at all with those huge subqueries, with_labels() is an entirely misleading method (but then again, SQLSoup as a whole at this point is pretty misleading).

    there's no bug here, but perhaps you want to A. check out automap at http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html which I wrote to replace SQLSoup which is very, very ancient, and B. for query issues like this please ask on the sqlalchemy mailing list at https://groups.google.com/group/sqlalchemy. thanks!

  2. Log in to comment