lib/mysql-functions: make mysql_join_binary_packages_repositories and mysql_join_repositories_binary_packages obsolete

16 files changed, 137 insertions, 69 deletions

diff --git a/bin/bootstrap-mysql b/bin/bootstrap-mysql
index f05ffe8..f2a7b91 100755
--- a/bin/bootstrap-mysql
+++ b/bin/bootstrap-mysql
@@ -37,7 +37,8 @@ fi
   printf ' FROM `binary_packages`'
   mysql_join_binary_packages_build_assignments
   printf ' AND `build_assignments`.`is_broken`'
-  mysql_join_binary_packages_repositories
+  mysql_join_binary_packages_binary_packages_in_repositories
+  mysql_join_binary_packages_in_repositories_repositories
   printf ' AND `repositories`.`name`="build-list";\n'
   printf 'REPEAT\n'
   printf 'INSERT IGNORE INTO `broken_packages_and_dependencies_old` (`id`)'
@@ -51,7 +52,8 @@ fi
   printf ' AND `dependency_types`.`relevant_for_building`'
   mysql_join_dependencies_install_target_providers
   mysql_join_install_target_providers_binary_packages '' 'new_bp'
-  mysql_join_binary_packages_repositories 'new_bp' 'new_repo'
+  mysql_join_binary_packages_binary_packages_in_repositories 'new_bp' 'new_bpir'
+  mysql_join_binary_packages_in_repositories_repositories 'new_bpir' 'new_repo'
   printf ' AND `new_repo`.`name` IN ("build-list","deletion-list");\n'
   printf 'UNTIL ROW_COUNT()=0\n'
   printf 'END REPEAT;\n'
@@ -71,7 +73,8 @@ fi
   printf 'INSERT IGNORE INTO `relevant_binary_packages` (`id`)'
   printf ' SELECT `binary_packages`.`id`'
   printf ' FROM `binary_packages`'
-  mysql_join_binary_packages_repositories
+  mysql_join_binary_packages_binary_packages_in_repositories
+  mysql_join_binary_packages_in_repositories_repositories
   printf ' AND `repositories`.`name`="build-list"'
   mysql_join_binary_packages_build_assignments
   mysql_join_build_assignments_package_sources
@@ -83,7 +86,8 @@ fi
   printf ' SELECT `install_target_providers`.`package`'
   printf ' FROM `relevant_binary_packages_copy`'
   printf ' JOIN `binary_packages` ON `relevant_binary_packages_copy`.`id`=`binary_packages`.`id`'
-  mysql_join_binary_packages_repositories
+  mysql_join_binary_packages_binary_packages_in_repositories
+  mysql_join_binary_packages_in_repositories_repositories
   printf ' AND `repositories`.`name`="build-list"'
   mysql_join_binary_packages_dependencies
   mysql_join_dependencies_dependency_types
@@ -144,8 +148,10 @@ fi
   mysql_join_package_sources_build_assignments 'b_ps' 'b_ba'
   mysql_join_build_assignments_binary_packages 'a_ba' 'a_bp'
   mysql_join_build_assignments_binary_packages 'b_ba' 'b_bp'
-  mysql_join_binary_packages_repositories 'a_bp' 'a_r'
-  mysql_join_binary_packages_repositories 'b_bp' 'b_r'
+  mysql_join_binary_packages_binary_packages_in_repositories 'a_bp' 'a_bpir'
+  mysql_join_binary_packages_in_repositories_repositories 'a_bpir' 'a_r'
+  mysql_join_binary_packages_binary_packages_in_repositories 'b_bp' 'b_bpir'
+  mysql_join_binary_packages_in_repositories_repositories 'b_bpir' 'b_r'
   mysql_join_repositories_repository_stabilities 'a_r' 'a_rs'
   mysql_join_repositories_repository_stabilities 'b_r' 'b_rs'
   printf ' WHERE `a_rs`.`name` = `from_stability`'
@@ -154,7 +160,8 @@ fi
   printf 'INSERT IGNORE INTO `moveable_binary_packages` (`id`,`to_repository`)'
   printf ' SELECT `binary_packages`.`id`,`repository_moves`.`to_repository`'
   printf ' FROM `binary_packages`'
-  mysql_join_binary_packages_repositories
+  mysql_join_binary_packages_binary_packages_in_repositories
+  mysql_join_binary_packages_in_repositories_repositories
   mysql_join_repositories_repository_stabilities
   mysql_join_binary_packages_build_assignments
   mysql_join_build_assignments_package_sources
@@ -177,7 +184,8 @@ fi
   printf ' SELECT `r_bp`.`id`,`m_bp`.`id`'
   printf ' FROM `moveable_binary_packages`'
   printf ' JOIN `binary_packages` AS `m_bp` ON `m_bp`.`id`=`moveable_binary_packages`.`id`'
-  mysql_join_binary_packages_repositories 'm_bp' 'm_r'
+  mysql_join_binary_packages_binary_packages_in_repositories 'm_bp' 'm_bpir'
+  mysql_join_binary_packages_in_repositories_repositories 'm_bpir' 'm_r'
   mysql_join_binary_packages_build_assignments 'm_bp' 'm_ba'
   mysql_join_build_assignments_package_sources 'm_ba' 'm_ps'
   mysql_join_package_sources_upstream_repositories 'm_ps' 'm_ur'
@@ -185,7 +193,8 @@ fi
   printf ' AND `repository_moves`.`from_repository`=`m_r`.`id`'
   # in its target repository
   printf ' JOIN `repositories` AS `r_r` ON `repository_moves`.`to_repository`=`r_r`.`id`'
-  mysql_join_repositories_binary_packages 'r_r' 'r_bp'
+  mysql_join_repositories_binary_packages_in_repositories 'r_r' 'r_bpir'
+  mysql_join_binary_packages_in_repositories_binary_packages 'r_bpir' 'r_bp'
   # all packages with identical names
   printf ' AND `r_bp`.`pkgname`=`m_bp`.`pkgname`;\n'
   # now we delete all unreplaceable and unmoveable packages from the respective
@@ -220,7 +229,8 @@ fi
   printf ' WHERE NOT EXISTS ('
     printf 'SELECT 1 FROM `install_target_providers`'
     mysql_join_install_target_providers_binary_packages '' 'prov_bp'
-    mysql_join_binary_packages_repositories 'prov_bp' 'prov_r'
+    mysql_join_binary_packages_binary_packages_in_repositories 'prov_bp' 'prov_bpir'
+    mysql_join_binary_packages_in_repositories_repositories 'prov_bpir' 'prov_r'
     printf ' JOIN `repository_stability_relations` ON `prov_r`.`stability`=`repository_stability_relations`.`more_stable`'
     printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
     printf ' AND `target_repositories`.`stability`=`repository_stability_relations`.`less_stable`'
@@ -278,8 +288,10 @@ fi
   printf ' AND `dependency_types`.`relevant_for_binary_packages`'
   mysql_join_dependencies_binary_packages '' 'req_bp'
   # we need to check wether req_bp's dependency is (un)critical
-  mysql_join_binary_packages_repositories 'repl_bp' 'repl_r'
-  mysql_join_binary_packages_repositories 'req_bp' 'req_r'
+  mysql_join_binary_packages_binary_packages_in_repositories 'repl_bp' 'repl_bpir'
+  mysql_join_binary_packages_in_repositories_repositories 'repl_bpir' 'repl_r'
+  mysql_join_binary_packages_binary_packages_in_repositories 'req_bp' 'req_bpir'
+  mysql_join_binary_packages_in_repositories_repositories 'req_bpir' 'req_r'
   # dependent package is "less stable" than dependency
   printf ' JOIN `repository_stability_relations` AS `repl_rr`'
   printf ' ON `repl_rr`.`more_stable`=`repl_r`.`stability`'
@@ -299,7 +311,8 @@ fi
     printf ' FROM `binary_packages` AS `subst_bp`'
     mysql_join_binary_packages_install_target_providers 'subst_bp' 'subst_itp'
     # ... in a repository ...
-    mysql_join_binary_packages_repositories 'subst_bp' 'subst_r'
+    mysql_join_binary_packages_binary_packages_in_repositories 'subst_bp' 'subst_bpir'
+    mysql_join_binary_packages_in_repositories_repositories 'subst_bpir' 'subst_r'
     # ... more stable ...
     printf ' JOIN `repository_stability_relations` AS `subst_rr`'
     printf ' ON `subst_rr`.`more_stable`=`subst_r`.`stability`'
diff --git a/bin/build-master-status b/bin/build-master-status
index c019a37..f1f4ed9 100755
--- a/bin/build-master-status
+++ b/bin/build-master-status
@@ -142,37 +142,43 @@ if [ ! -s "${work_dir}/build-master-sanity" ]; then
     # stable_packages_count
     printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM'
     printf ' `binary_packages`'
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     mysql_join_repositories_repository_stabilities
     printf ' WHERE `repository_stabilities`.`name`="stable"),'
     # pending_tasks_count
     printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM'
     printf ' `build_assignments`'
     mysql_join_build_assignments_binary_packages
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     printf ' WHERE `repositories`.`name`="build-list"),'
     # pending_packages_count
     printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM'
     printf ' `binary_packages`'
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     printf ' WHERE `repositories`.`name`="build-list"),'
     # staging_packages_count
     printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM'
     printf ' `binary_packages`'
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     mysql_join_repositories_repository_stabilities
     printf ' WHERE `repository_stabilities`.`name`="staging"),'
     # testing_packages_count
     printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM'
     printf ' `binary_packages`'
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     mysql_join_repositories_repository_stabilities
     printf ' WHERE `repository_stabilities`.`name`="testing"'
     printf ' AND NOT `binary_packages`.`is_tested`),'
     # tested_packages_count
     printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM'
     printf ' `binary_packages`'
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     mysql_join_repositories_repository_stabilities
     printf ' WHERE `repository_stabilities`.`name`="testing"'
     printf ' AND `binary_packages`.`is_tested`),'
@@ -180,7 +186,8 @@ if [ ! -s "${work_dir}/build-master-sanity" ]; then
     printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM'
     printf ' `build_assignments`'
     mysql_join_build_assignments_binary_packages
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     printf ' WHERE `repositories`.`name`="build-list"'
     printf ' AND `build_assignments`.`is_broken`),'
     # dependency_loops_count
@@ -194,20 +201,23 @@ if [ ! -s "${work_dir}/build-master-sanity" ]; then
     printf ' `build_slaves`'
     mysql_join_build_slaves_build_assignments
     mysql_join_build_assignments_binary_packages
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     printf ' WHERE `repositories`.`name`="build-list"),'
     # blocked_tasks_count
     printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM'
     printf ' `build_assignments`'
     mysql_join_build_assignments_binary_packages
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     printf ' WHERE `repositories`.`name`="build-list"'
     printf ' AND `build_assignments`.`is_blocked` IS NOT NULL),'
     # next_tasks_count
     printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM'
     printf ' `build_assignments`'
     mysql_join_build_assignments_binary_packages
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     printf ' WHERE `repositories`.`name`="build-list"'
     printf ' AND ('
       printf 'NOT EXISTS ('
@@ -216,7 +226,8 @@ if [ ! -s "${work_dir}/build-master-sanity" ]; then
         printf ' AND `dependency_types`.`relevant_for_building`'
         mysql_join_dependencies_install_target_providers
         mysql_join_install_target_providers_binary_packages '' 'prov_bp'
-        mysql_join_binary_packages_repositories 'prov_bp' 'prov_r'
+        mysql_join_binary_packages_binary_packages_in_repositories 'prov_bp' 'prov_bpir'
+        mysql_join_binary_packages_in_repositories_repositories 'prov_bpir' 'prov_r'
         printf ' WHERE `prov_r`.`name`="build-list"'
         printf ' AND `dependencies`.`dependent`=`binary_packages`.`id`'
       printf ') OR EXISTS ('
diff --git a/bin/check-bugtracker b/bin/check-bugtracker
index 53d8ffb..50927e9 100755
--- a/bin/check-bugtracker
+++ b/bin/check-bugtracker
@@ -31,7 +31,8 @@ bug_list=$(
   while read -r stability_id category; do
     for has_issues in '1:' '0:NOT '; do
       printf 'UPDATE `binary_packages`'
-      mysql_join_binary_packages_repositories
+      mysql_join_binary_packages_binary_packages_in_repositories
+      mysql_join_binary_packages_in_repositories_repositories
       printf ' SET `has_issues`=%s' \
         "${has_issues%:*}"
       printf ' WHERE `repositories`.`stability`=%s' \
diff --git a/bin/copy-to-build-support b/bin/copy-to-build-support
index 9253fd8..2838432 100755
--- a/bin/copy-to-build-support
+++ b/bin/copy-to-build-support
@@ -87,7 +87,8 @@ sed -n '
       {
         printf 'SELECT `binary_packages`.`id`,`repositories`.`name`'
         printf ' FROM `binary_packages`'
-        mysql_join_binary_packages_repositories
+        mysql_join_binary_packages_binary_packages_in_repositories
+        mysql_join_binary_packages_in_repositories_repositories
         mysql_join_binary_packages_architectures
         printf ' WHERE'
         printf ' `binary_packages`.`%s`=from_base64("%s") AND' \
diff --git a/bin/db-update b/bin/db-update
index 33a1436..f817833 100755
--- a/bin/db-update
+++ b/bin/db-update
@@ -149,7 +149,8 @@ for source_stability in 'testing' 'staging'; do
         sed 's/,$/;\n/'
       printf 'DELETE `moveable_binary_packages` FROM `moveable_binary_packages`'
       printf ' JOIN `binary_packages` ON `binary_packages`.`id`=`moveable_binary_packages`.`id`'
-      mysql_join_binary_packages_repositories
+      mysql_join_binary_packages_binary_packages_in_repositories
+      mysql_join_binary_packages_in_repositories_repositories
       mysql_join_repositories_repository_stabilities
       printf ' WHERE `repository_stabilities`.`name`!="%s";\n' \
         "${source_stability}"
@@ -177,7 +178,8 @@ for source_stability in 'testing' 'staging'; do
       printf 'INSERT IGNORE INTO `replaced_binary_packages` (`id`,`replaced_by`)'
       printf ' SELECT `binary_packages`.`id`,`subst_bp`.`id`'
       printf ' FROM `binary_packages`'
-      mysql_join_binary_packages_repositories
+      mysql_join_binary_packages_binary_packages_in_repositories
+      mysql_join_binary_packages_in_repositories_repositories
       printf ' AND `repositories`.`is_on_master_mirror`'
       mysql_join_binary_packages_build_assignments
       mysql_join_build_assignments_package_sources
@@ -187,7 +189,8 @@ for source_stability in 'testing' 'staging'; do
       printf ' JOIN `binary_packages` AS `subst_bp`'
       printf ' ON `binary_packages`.`pkgname`=`subst_bp`.`pkgname`'
       printf ' AND `repository_moves`.`from_repository`=`subst_bp`.`repository`'
-      mysql_join_binary_packages_repositories 'subst_bp' 'subst_r'
+      mysql_join_binary_packages_binary_packages_in_repositories 'subst_bp' 'subst_bpir'
+      mysql_join_binary_packages_in_repositories_repositories 'subst_bpir' 'subst_r'
       mysql_join_repositories_repository_stabilities 'subst_r' 'subst_rs'
       printf ' AND `subst_rs`.`name`="%s"' \
         "${source_stability}"
@@ -208,7 +211,8 @@ for source_stability in 'testing' 'staging'; do
       printf 'INSERT IGNORE INTO `moveable_binary_packages` (`id`,`to_repository`)'
       printf ' SELECT `binary_packages`.`id`,`repository_moves`.`to_repository`'
       printf ' FROM `binary_packages`'
-      mysql_join_binary_packages_repositories
+      mysql_join_binary_packages_binary_packages_in_repositories
+      mysql_join_binary_packages_in_repositories_repositories
       printf ' AND `repositories`.`is_on_master_mirror`'
       mysql_join_repositories_repository_stabilities
       printf ' AND `repository_stabilities`.`name`="%s"' \
@@ -253,7 +257,8 @@ for source_stability in 'testing' 'staging'; do
     printf ',`repositories`.`name`,`new_repo`.`name`'
     printf ' FROM `moveable_binary_packages`'
     printf ' JOIN `binary_packages` ON `moveable_binary_packages`.`id`=`binary_packages`.`id`'
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     mysql_join_binary_packages_architectures
     printf ' JOIN `repositories` AS `new_repo` ON `new_repo`.`id`=`moveable_binary_packages`.`to_repository`'
     printf ';\n'
@@ -266,7 +271,8 @@ for source_stability in 'testing' 'staging'; do
     printf ',`repositories`.`name`'
     printf ' FROM `replaced_binary_packages`'
     printf ' JOIN `binary_packages` ON `replaced_binary_packages`.`id`=`binary_packages`.`id`'
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     mysql_join_binary_packages_architectures
     printf ';\n'
   } | \
diff --git a/bin/delete-packages b/bin/delete-packages
index 116b12d..df51593 100755
--- a/bin/delete-packages
+++ b/bin/delete-packages
@@ -107,7 +107,8 @@ export TMPDIR="${tmp_dir}"
   printf 'INSERT IGNORE INTO `to_deletes`'
   printf ' SELECT DISTINCT `binary_packages`.`id`'
   printf ' FROM `binary_packages`'
-  mysql_join_binary_packages_repositories
+  mysql_join_binary_packages_binary_packages_in_repositories
+  mysql_join_binary_packages_in_repositories_repositories
   printf ' LEFT' # should not be necessary, but is formally more correct
   mysql_join_binary_packages_install_target_providers
   printf ' WHERE `binary_packages`.`is_to_be_deleted`'
@@ -120,7 +121,8 @@ export TMPDIR="${tmp_dir}"
     mysql_join_dependencies_binary_packages '' 'd_bp'
     # deliberately break dependencies of deletion-list packages
     printf ' AND NOT `d_bp`.`is_to_be_deleted`'
-    mysql_join_binary_packages_repositories 'd_bp' 'd_r'
+    mysql_join_binary_packages_binary_packages_in_repositories 'd_bp' 'd_bpir'
+    mysql_join_binary_packages_in_repositories_repositories 'd_bpir' 'd_r'
     mysql_join_repositories_repository_stabilities 'd_r' 'd_rs'
     # this is deliberately less restrict than `d_r`.`is_on_master_mirror`
     printf ' AND `d_rs`.`name` NOT IN ("forbidden","virtual")'
@@ -129,7 +131,8 @@ export TMPDIR="${tmp_dir}"
       printf 'SELECT 1 FROM `binary_packages` AS `s_bp`'
       mysql_join_binary_packages_install_target_providers 's_bp' 's_itp'
       printf ' AND NOT `s_bp`.`is_to_be_deleted`'
-      mysql_join_binary_packages_repositories 's_bp' 's_r'
+      mysql_join_binary_packages_binary_packages_in_repositories 's_bp' 's_bpir'
+      mysql_join_binary_packages_in_repositories_repositories 's_bpir' 's_r'
       printf ' AND `s_r`.`is_on_master_mirror`'
       printf ' JOIN `repository_stability_relations`'
         printf ' ON `repository_stability_relations`.`more_stable`=`s_r`.`stability`'
@@ -141,20 +144,23 @@ export TMPDIR="${tmp_dir}"
   printf 'SELECT DISTINCT "repo",`repositories`.`name`'
   printf ' FROM `to_deletes`'
   printf ' JOIN `binary_packages` ON `to_deletes`.`id`=`binary_packages`.`id`'
-  mysql_join_binary_packages_repositories
+  mysql_join_binary_packages_binary_packages_in_repositories
+  mysql_join_binary_packages_in_repositories_repositories
   printf ';\n'
 
   printf 'SELECT "package",`repositories`.`name`,`binary_packages`.`pkgname`'
   printf ' FROM `to_deletes`'
   printf ' JOIN `binary_packages` ON `to_deletes`.`id`=`binary_packages`.`id`'
-  mysql_join_binary_packages_repositories
+  mysql_join_binary_packages_binary_packages_in_repositories
+  mysql_join_binary_packages_in_repositories_repositories
   printf ';\n'
 
   printf 'SELECT "package-file",`repositories`.`name`,'
   mysql_package_name_query
   printf ' FROM `to_deletes`'
   printf ' JOIN `binary_packages` ON `to_deletes`.`id`=`binary_packages`.`id`'
-  mysql_join_binary_packages_repositories
+  mysql_join_binary_packages_binary_packages_in_repositories
+  mysql_join_binary_packages_in_repositories_repositories
   mysql_join_binary_packages_architectures
   printf ';\n'
 
@@ -254,7 +260,8 @@ fi
 
   printf 'UPDATE `binary_packages` '
   printf ' JOIN `to_deletes` ON `to_deletes`.`id`=`binary_packages`.`id`'
-  mysql_join_binary_packages_repositories
+  mysql_join_binary_packages_binary_packages_in_repositories
+  mysql_join_binary_packages_in_repositories_repositories
   mysql_join_binary_packages_architectures
   printf ' SET `repository`=('
     printf 'SELECT `repositories`.`id`'
diff --git a/bin/find-obsolete-packages b/bin/find-obsolete-packages
index a5372a9..34a1bb9 100755
--- a/bin/find-obsolete-packages
+++ b/bin/find-obsolete-packages
@@ -106,7 +106,8 @@ fi
   {
     printf 'SELECT DISTINCT `binary_packages`.`pkgname`'
     printf ' FROM `binary_packages`'
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     printf ' WHERE `repositories`.`is_on_master_mirror`'
     printf ' AND NOT `binary_packages`.`is_to_be_deleted`'
   } | \
diff --git a/bin/get-assignment b/bin/get-assignment
index ba80798..0bfe48f 100755
--- a/bin/get-assignment
+++ b/bin/get-assignment
@@ -33,7 +33,8 @@ hand_out_assignment() {
     mysql_join_upstream_repositories_package_sources
     mysql_join_package_sources_build_assignments
     mysql_join_build_assignments_binary_packages
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     printf ' WHERE `repositories`.`name`="build-list"'
     printf ' AND `build_assignments`.`id`=from_base64("%s")' \
       "$(printf '%s' "$1" | base64 -w0)"
@@ -97,7 +98,8 @@ currently_building=$(
     printf ' FROM `build_assignments`'
     mysql_join_build_assignments_build_slaves
     mysql_join_build_assignments_binary_packages
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     printf ' WHERE `build_slaves`.`id`=from_base64("%s")' \
       "$(printf '%s' "${slave_id}" | base64 -w0)"
     printf ' AND `repositories`.`name`="build-list"'
@@ -137,7 +139,8 @@ next_building=$(
       printf ' FROM `build_assignments`'
       mysql_join_build_assignments_package_sources
       mysql_join_build_assignments_binary_packages
-      mysql_join_binary_packages_repositories
+      mysql_join_binary_packages_binary_packages_in_repositories
+      mysql_join_binary_packages_in_repositories_repositories
       printf ' LEFT'
       mysql_join_build_assignments_failed_builds
       printf ' WHERE `repositories`.`name`="build-list"'
@@ -176,7 +179,8 @@ count_pending=$(
     printf 'SELECT count(*)'
     printf ' FROM `build_assignments`'
     mysql_join_build_assignments_binary_packages
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     printf ' WHERE `repositories`.`name`="build-list"'
     printf ' AND `build_assignments`.`is_blocked` IS NULL'
     printf ';\n'
diff --git a/bin/get-package-updates b/bin/get-package-updates
index cfc0f74..d97266e 100755
--- a/bin/get-package-updates
+++ b/bin/get-package-updates
@@ -232,11 +232,12 @@ trigger_rebuild_on_any_file_sed_expression=$(
     printf '`upstream_repositories`.`name`,"-",'
     printf 'IF(`architectures`.`name`="any","any","x86_64"))'
     printf ' FROM `binary_packages`'
+    mysql_join_binary_packages_binary_packages_in_repositories
     mysql_join_binary_packages_build_assignments
     mysql_join_build_assignments_architectures
     mysql_join_build_assignments_package_sources
     mysql_join_package_sources_upstream_repositories
-    printf ' WHERE `binary_packages`.`repository`=%s;\n' \
+    printf ' WHERE `binary_packages_in_repositories`.`repository`=%s;\n' \
       "${repository_ids__any_build_list}"
   } | \
     mysql_run_query | \
@@ -381,12 +382,13 @@ trigger_rebuild_on_any_file_sed_expression=$(
         printf ' FROM `binary_packages`'
         mysql_join_binary_packages_build_assignments
         mysql_join_build_assignments_package_sources
+        mysql_join_binary_packages_binary_packages_in_repositories
         printf ' WHERE `package_sources`.`pkgbase`=from_base64("%s")' \
           "$(
             printf '%s' "${package}" | \
               base64 -w0
           )"
-        printf ' AND `binary_packages`.`repository` IN (%s,%s);\n' \
+        printf ' AND `binary_packages_in_repositories`.`repository` IN (%s,%s);\n' \
           "${repository_ids__any_build_list}" \
           "${repository_ids__any_deletion_list}"
         # remove is-to-be-deleted marker from old binary packages
@@ -595,14 +597,15 @@ echo 'Done - mark decisions as final.'
   done
   # move binary_packages from "to-be-decided" to "build-list"
   printf 'UPDATE `binary_packages`'
-  mysql_join_binary_packages_repositories '' 'from_repo'
+  mysql_join_binary_packages_binary_packages_in_repositories '' 'from_bir'
   printf ' LEFT'
   mysql_join_binary_packages_binary_packages_in_repositories
   printf ' SET `binary_packages`.`repository`=%s,' \
     "${repository_ids__any_build_list}"
   printf '`binary_packages_in_repositories`.`repository`=%s' \
     "${repository_ids__any_build_list}"
-  printf ' WHERE `from_repo`.`name`="to-be-decided";\n'
+  printf ' WHERE `from_bir`.`id`=%s;\n' \
+    "${repository_ids__any_to_be_decided}"
 } | \
   mysql_run_query
 
diff --git a/bin/modify-package-state b/bin/modify-package-state
index d5a201f..b4425ec 100755
--- a/bin/modify-package-state
+++ b/bin/modify-package-state
@@ -149,7 +149,8 @@ while read -r a b; do
       # shellcheck disable=SC2016
       combiner=$(
         printf '`binary_packages`'
-        mysql_join_binary_packages_repositories
+        mysql_join_binary_packages_binary_packages_in_repositories
+        mysql_join_binary_packages_in_repositories_repositories
         mysql_join_repositories_repository_stabilities
         mysql_join_binary_packages_architectures
       )
@@ -177,7 +178,8 @@ while read -r a b; do
         mysql_join_build_assignments_package_sources
         mysql_join_package_sources_upstream_repositories
         mysql_join_build_assignments_binary_packages
-        mysql_join_binary_packages_repositories
+        mysql_join_binary_packages_binary_packages_in_repositories
+        mysql_join_binary_packages_in_repositories_repositories
         mysql_join_repositories_repository_stabilities
       )
       # shellcheck disable=SC2016
diff --git a/bin/prioritize-build-list b/bin/prioritize-build-list
index 187cf29..bbeb288 100755
--- a/bin/prioritize-build-list
+++ b/bin/prioritize-build-list
@@ -30,7 +30,8 @@ fi
   printf 'UPDATE `build_assignments`'
   mysql_join_build_assignments_package_sources
   mysql_join_build_assignments_binary_packages
-  mysql_join_binary_packages_repositories
+  mysql_join_binary_packages_binary_packages_in_repositories
+  mysql_join_binary_packages_in_repositories_repositories
   printf ' SET `build_assignments`.`priority`=('
     printf 'SELECT COALESCE(MAX(`all_priorities`.`priority`),0)+1'
     printf ' FROM ('
diff --git a/bin/return-assignment b/bin/return-assignment
index fdb7e1c..0ec7a2e 100755
--- a/bin/return-assignment
+++ b/bin/return-assignment
@@ -57,7 +57,8 @@ if [ "$5" = 'ERROR' ]; then
       mysql_join_build_assignments_package_sources
       mysql_join_package_sources_upstream_repositories
       mysql_join_build_assignments_binary_packages
-      mysql_join_binary_packages_repositories
+      mysql_join_binary_packages_binary_packages_in_repositories
+      mysql_join_binary_packages_in_repositories_repositories
       printf ' WHERE `build_slaves`.`id`=from_base64("%s")' \
         "$(
           # shellcheck disable=SC2154
@@ -244,7 +245,8 @@ infos=$(
     mysql_join_build_assignments_package_sources
     mysql_join_package_sources_upstream_repositories
     mysql_join_build_assignments_binary_packages
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     printf ' WHERE `build_slaves`.`id`=from_base64("%s")' \
       "$(
         # shellcheck disable=SC2154
@@ -408,7 +410,8 @@ packages=$(
   mysql_package_name_query
   printf ' FROM `binary_packages`'
   mysql_join_binary_packages_architectures
-  mysql_join_binary_packages_repositories
+  mysql_join_binary_packages_binary_packages_in_repositories
+  mysql_join_binary_packages_in_repositories_repositories
   printf ' WHERE `binary_packages`.`build_assignment`=from_base64("%s")' \
     "$(
       printf '%s' "${build_assignment_id}" | \
@@ -618,7 +621,8 @@ trigger_mirror_refreshs
   printf 'INSERT INTO `loops_to_delete`'
   printf ' SELECT `build_dependency_loops`.`loop` FROM `build_dependency_loops`'
   mysql_join_build_dependency_loops_binary_packages
-  mysql_join_binary_packages_repositories
+  mysql_join_binary_packages_binary_packages_in_repositories
+  mysql_join_binary_packages_in_repositories_repositories
   printf ' WHERE NOT `repositories`.`name` = "build-list";\n'
   printf 'COMMIT;\n'
   printf 'DELETE FROM `build_dependency_loops` WHERE EXISTS ('
diff --git a/bin/sanity-check b/bin/sanity-check
index bfe6400..bad927b 100755
--- a/bin/sanity-check
+++ b/bin/sanity-check
@@ -164,7 +164,8 @@ while [ $# -gt 0 ]; do
           mysql_join_build_assignments_architectures
           printf ' WHERE EXISTS('
             printf 'SELECT * FROM `binary_packages`'
-            mysql_join_binary_packages_repositories
+            mysql_join_binary_packages_binary_packages_in_repositories
+            mysql_join_binary_packages_in_repositories_repositories
             printf ' WHERE `repositories`.`name`="build-list"'
             printf ' AND `binary_packages`.`build_assignment`=`build_assignments`.`id`'
           printf ');\n'
@@ -186,10 +187,12 @@ while [ $# -gt 0 ]; do
         # shellcheck disable=SC2016
         {
           printf 'SELECT `a`.`pkgname` FROM `binary_packages` AS `a`'
-          mysql_join_binary_packages_repositories 'a' 'a_r'
+          mysql_join_binary_packages_binary_packages_in_repositories 'a' 'air'
+          mysql_join_binary_packages_in_repositories_repositories 'air' 'a_r'
           printf ' AND `a_r`.`name`="build-list"'
           printf ' JOIN `binary_packages` AS `b` ON `a`.`pkgname`=`b`.`pkgname`'
-          mysql_join_binary_packages_repositories 'b' 'b_r'
+          mysql_join_binary_packages_binary_packages_in_repositories 'b' 'bir'
+          mysql_join_binary_packages_in_repositories_repositories 'bir' 'b_r'
           printf ' AND `b_r`.`name`="deletion-list";\n'
         } | \
           mysql_run_query
@@ -366,7 +369,8 @@ while [ $# -gt 0 ]; do
             printf 'SELECT "mysql",CONCAT(`r_a`.`name`,"/",`repositories`.`name`,"/",'
             mysql_package_name_query
             printf ') FROM `binary_packages`'
-            mysql_join_binary_packages_repositories
+            mysql_join_binary_packages_binary_packages_in_repositories
+            mysql_join_binary_packages_in_repositories_repositories
             printf ' AND `repositories`.`is_on_master_mirror`'
             mysql_join_binary_packages_architectures
             mysql_join_repositories_architectures '' 'r_a'
diff --git a/bin/seed-build-list b/bin/seed-build-list
index 3a71429..6babca8 100755
--- a/bin/seed-build-list
+++ b/bin/seed-build-list
@@ -193,7 +193,8 @@ fi
   printf 'INSERT IGNORE INTO `ignore_packages` (`pkgname`)'
   printf ' SELECT DISTINCT `ignore_bin`.`pkgname`'
   printf ' FROM `binary_packages` AS `ignore_bin`'
-  mysql_join_binary_packages_repositories 'ignore_bin'
+  mysql_join_binary_packages_binary_packages_in_repositories 'ignore_bin'
+  mysql_join_binary_packages_in_repositories_repositories
   printf ' WHERE `repositories`.`name` IN ("build-list","deletion-list")'
   # packages with no not-to-be-deleted and at least on to-be-deleted version should be ignored
   printf ' OR ('
@@ -286,7 +287,8 @@ printf 'CREATE TEMPORARY TABLE `pkgbases` (`pkgbase` VARCHAR(64), `repository` V
     printf 'CREATE TEMPORARY TABLE `bin_ids` (`id` BIGINT, UNIQUE KEY (`id`));\n'
     printf 'INSERT IGNORE INTO `bin_ids`'
     printf ' SELECT `binary_packages`.`id` FROM `binary_packages`'
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     printf ' AND `repositories`.`is_on_master_mirror`'
     printf ' AND `repositories`.`name`!="build-support"'
     mysql_join_binary_packages_dependencies
@@ -295,7 +297,8 @@ printf 'CREATE TEMPORARY TABLE `pkgbases` (`pkgbase` VARCHAR(64), `repository` V
     # nothing "less stable" has the same name
     printf ' WHERE NOT EXISTS ('
       printf 'SELECT * FROM `binary_packages` AS `subst_bp`'
-      mysql_join_binary_packages_repositories 'subst_bp' 'subst_r'
+      mysql_join_binary_packages_binary_packages_in_repositories 'subst_bp' 'subst_bir'
+      mysql_join_binary_packages_in_repositories_repositories 'subst_bir' 'subst_r'
       printf ' AND `subst_r`.`name`!="build-support"'
       printf ' JOIN `repository_stability_relations` ON `repository_stability_relations`.`less_stable`=`subst_r`.`stability`'
       printf ' WHERE `repository_stability_relations`.`more_stable`=`repositories`.`stability`'
@@ -304,7 +307,8 @@ printf 'CREATE TEMPORARY TABLE `pkgbases` (`pkgbase` VARCHAR(64), `repository` V
     printf ') AND NOT EXISTS ('
       printf 'SELECT * FROM `install_target_providers`'
       mysql_join_install_target_providers_binary_packages '' 'subst_bp'
-      mysql_join_binary_packages_repositories 'subst_bp' 'subst_r'
+      mysql_join_binary_packages_binary_packages_in_repositories 'subst_bp' 'subst_bir'
+      mysql_join_binary_packages_in_repositories_repositories 'subst_bir' 'subst_r'
       printf ' AND `subst_r`.`name` NOT IN ("build-support","deletion-list","to-be-decided")'
       printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
     printf ')'
diff --git a/bin/show-dependencies b/bin/show-dependencies
index 7eb4ead..a413152 100755
--- a/bin/show-dependencies
+++ b/bin/show-dependencies
@@ -72,7 +72,8 @@ for target_package in "$@"; do
       printf ' FROM `repositories`'
       mysql_join_repositories_repository_stabilities
       printf ' AND `repository_stabilities`.`name` IN ("unbuilt","staging")'
-      mysql_join_repositories_binary_packages
+      mysql_join_repositories_binary_packages_in_repositories
+      mysql_join_binary_packages_in_repositories_binary_packages
       printf ';\n'
 
     else
@@ -113,7 +114,8 @@ for target_package in "$@"; do
     printf '`package_sources`.`pkgbase`'
     printf ' FROM `relevant_binary_packages`'
     printf ' JOIN `binary_packages` ON `relevant_binary_packages`.`id`=`binary_packages`.`id`'
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     mysql_join_repositories_repository_stabilities
     printf ' AND `repository_stabilities`.`name`="unbuilt"'
     mysql_join_binary_packages_build_assignments
@@ -127,7 +129,8 @@ for target_package in "$@"; do
     printf '`binary_packages`.`pkgname`'
     printf ' FROM `relevant_binary_packages`'
     printf ' JOIN `binary_packages` ON `relevant_binary_packages`.`id`=`binary_packages`.`id`'
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     mysql_join_repositories_repository_stabilities
     printf ';\n'
 
@@ -146,7 +149,8 @@ for target_package in "$@"; do
       'pkgname' 'binary_packages'
     printf ' FROM `relevant_binary_packages`'
     printf ' JOIN `binary_packages` ON `relevant_binary_packages`.`id`=`binary_packages`.`id`'
-    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_binary_packages_in_repositories
+    mysql_join_binary_packages_in_repositories_repositories
     mysql_join_repositories_repository_stabilities
     printf ' AND `repository_stabilities`.`name`="unbuilt"'
     mysql_join_binary_packages_build_assignments
diff --git a/bin/why-dont-you b/bin/why-dont-you
index 7117bed..e35e0bd 100755
--- a/bin/why-dont-you
+++ b/bin/why-dont-you
@@ -62,7 +62,8 @@ case "${action}" in
         printf ' FROM `package_sources` AS `tb_ps`'
         mysql_join_package_sources_build_assignments 'tb_ps' 'tb_ba'
         mysql_join_build_assignments_binary_packages 'tb_ba' 'tb_bin'
-        mysql_join_binary_packages_repositories 'tb_bin' 'tb_rep'
+        mysql_join_binary_packages_binary_packages_in_repositories 'tb_bin' 'tb_bir'
+        mysql_join_binary_packages_in_repositories_repositories 'tb_bir' 'tb_rep'
         printf ' WHERE `tb_rep`.`name`="build-list"'
         printf ') AS `to_build`'
       printf ' ON `to_build`.`pkgbase`=`pkgbases`.`pkgbase`'
@@ -74,7 +75,8 @@ case "${action}" in
         printf ' FROM `package_sources` AS `dep_ps`'
         mysql_join_package_sources_build_assignments 'dep_ps' 'dep_ba'
         mysql_join_build_assignments_binary_packages 'dep_ba' 'dep_bin'
-        mysql_join_binary_packages_repositories 'dep_bin' 'dep_rep'
+        mysql_join_binary_packages_binary_packages_in_repositories 'dep_bin' 'dep_bir'
+        mysql_join_binary_packages_in_repositories_repositories 'dep_bir' 'dep_rep'
         # now we have some (=3) additional joins,
         # because we are interested in dependency relations to `to_build`
         mysql_join_binary_packages_install_target_providers 'dep_bin'