Bug 988848

When searching systems for:

CPU/Flag contains aes
CPU/Flag contains pclmulqdq

no results are returned even though there should be many. The SQL produced is as follows:

2013-07-29 08:59:01,570 sqlalchemy.engine.base.Engine.0x...07d0 INFO SELECT count(1) AS count_1 
FROM (SELECT DISTINCT system.id AS system_id 
FROM system LEFT OUTER JOIN tg_user AS tg_user_1 ON system.user_id = tg_user_1.user_id LEFT OUTER JOIN system_arch_map AS system_arch_map_1 ON system.id = system_arch_map_1.system_id LEFT OUTER JOIN arch ON arch.id = system_arch_map_1.arch_id LEFT OUTER JOIN cpu ON system.id = cpu.system_id LEFT OUTER JOIN cpu_flag ON cpu.id = cpu_flag.cpu_id 
WHERE cpu_flag.flag LIKE %s AND cpu_flag.flag LIKE %s) AS anon_1
2013-07-29 08:59:01,571 sqlalchemy.engine.base.Engine.0x...07d0 INFO ('%aes%', '%pclmulqdq%')
2013-07-29 08:59:01,577 sqlalchemy.engine.base.Engine.0x...07d0 INFO SELECT DISTINCT system.status AS system_status, system.id AS system_id, system.fqdn AS system_fqdn, system.serial AS system_serial, system.date_added AS system_date_added, system.date_modified AS system_date_modified, system.date_lastcheckin AS system_date_lastcheckin, system.location AS system_location, system.vendor AS system_vendor, system.model AS system_model, system.lender AS system_lender, system.owner_id AS system_owner_id, system.user_id AS system_user_id, system.type AS system_type, system.status_reason AS system_status_reason, system.shared AS system_shared, system.private AS system_private, system.deleted AS system_deleted, system.memory AS system_memory, system.checksum AS system_checksum, system.lab_controller_id AS system_lab_controller_id, system.mac_address AS system_mac_address, system.loan_id AS system_loan_id, system.loan_comment AS system_loan_comment, system.release_action AS system_release_action, system.reprovision_distro_tree_id AS system_reprovision_distro_tree_id, system.hypervisor_id AS system_hypervisor_id, system.kernel_type_id AS system_kernel_type_id, tg_user_1.password AS tg_user_1_password, tg_user_1.root_password AS tg_user_1_root_password, tg_user_1.user_id AS tg_user_1_user_id, tg_user_1.user_name AS tg_user_1_user_name, tg_user_1.email_address AS tg_user_1_email_address, tg_user_1.display_name AS tg_user_1_display_name, tg_user_1.rootpw_changed AS tg_user_1_rootpw_changed, tg_user_1.created AS tg_user_1_created, tg_user_1.disabled AS tg_user_1_disabled, tg_user_1.removed AS tg_user_1_removed 
FROM system LEFT OUTER JOIN tg_user AS tg_user_2 ON system.user_id = tg_user_2.user_id LEFT OUTER JOIN system_arch_map AS system_arch_map_1 ON system.id = system_arch_map_1.system_id LEFT OUTER JOIN arch ON arch.id = system_arch_map_1.arch_id LEFT OUTER JOIN cpu ON system.id = cpu.system_id LEFT OUTER JOIN cpu_flag ON cpu.id = cpu_flag.cpu_id LEFT OUTER JOIN tg_user AS tg_user_1 ON system.user_id = tg_user_1.user_id 
WHERE cpu_flag.flag LIKE %s AND cpu_flag.flag LIKE %s ORDER BY system.fqdn ASC 
 LIMIT 0, 20
2013-07-29 08:59:01,577 sqlalchemy.engine.base.Engine.0x...07d0 INFO ('%aes%', '%pclmulqdq%')

The cpu_flag table is being joined only once but it should be joined twice under two aliases.

Presumably a regression in 0.13.2 caused by the fix for bug 972397.

972397 should only affect how sorting is done (i.e when you click on a column header).

I downgraded to TurboGears-1.1.3-2.el6eng.2 on my devbox and the problem is still there.

Although tbh I'm not sure how this ever would have worked. Cpu/Flag is not of type AliasedColumn in search_utility.py, so you can join flag to Cpu as many times as you like and sqlalchemy will only join it once.

We should go through and have a look 1:N type relationships and adjust their search column type to AliasedColumn and that should fix this.

http://gerrit.beaker-project.org/#/c/2173/

Beaker 0.15 has been released.

This change has been nominated to be back ported to the 0.14 branch, to be released as part of the next maintenance release 0.14.2.

Adjusting target milestone to make the changes backported to 0.14.2 easier to identify. 0.15.0 has enough significant regressions that it shouldn't be used, so the change means that 0.15.1 can be effectively reidentified as the union of that tag and the 0.14.2 target milestone.

Closing as addressed in Beaker 0.14.2.