Note: The original error message is the one thrown on our production Foreman with PostgreSQL backend and the following data (stacktrace and this query) is from my local dev environment with a SQLite database.
That’s the actual query shown when using a blank Foreman 1.24 instance with SQLite backend (local dev environment):
SQLite3::SQLException: sub-select returns 59 columns - expected 1: SELECT DISTINCT “hosts”.“id” FROM “hosts” LEFT OUTER JOIN “compute_resources” ON “compute_resources”.“id” = “hosts”.“compute_resource_id” AND (1=0) LEFT OUTER JOIN “fact_values” ON “fact_values”.“host_id” = “hosts”.“id” AND “fact_values”.“fact_name_id” = ? LEFT OUTER JOIN “hostgroups” ON “hostgroups”.“id” = “hosts”.“hostgroup_id” AND (1=0) LEFT OUTER JOIN “operatingsystems” ON “operatingsystems”.“id” = “hosts”.“operatingsystem_id” LEFT OUTER JOIN “environments” ON “environments”.“id” = “hosts”.“environment_id” AND (environments.id IN (1)) LEFT OUTER JOIN “models” ON “models”.“id” = “hosts”.“model_id” LEFT OUTER JOIN “host_status” ON “host_status”.“host_id” = “hosts”.“id” LEFT OUTER JOIN “tokens” ON “tokens”.“host_id” = “hosts”.“id” AND “tokens”.“type” IN (‘Token::Build’) WHERE “hosts”.“type” IN (‘Host::Managed’) AND “hosts”.“organization_id” = ? AND “hosts”.“location_id” = ? AND (((“compute_resources”.“name” = ‘Test’) AND (“hosts”.“organization_id” = ‘1’) AND (“hosts”.“location_id” = ‘2’))) AND ((hosts.id in (SELECT “hosts”.“id”, “hosts”.“id” AS t0_r0, “hosts”.“name” AS t0_r1, “hosts”.“last_compile” AS t0_r2, “hosts”.“last_report” AS t0_r3, “hosts”.“updated_at” AS t0_r4, “hosts”.“created_at” AS t0_r5, “hosts”.“root_pass” AS t0_r6, “hosts”.“architecture_id” AS t0_r7, “hosts”.“operatingsystem_id” AS t0_r8, “hosts”.“environment_id” AS t0_r9, “hosts”.“ptable_id” AS t0_r10, “hosts”.“medium_id” AS t0_r11, “hosts”.“build” AS t0_r12, “hosts”.“comment” AS t0_r13, “hosts”.“disk” AS t0_r14, “hosts”.“installed_at” AS t0_r15, “hosts”.“model_id” AS t0_r16, “hosts”.“hostgroup_id” AS t0_r17, “hosts”.“owner_id” AS t0_r18, “hosts”.“owner_type” AS t0_r19, “hosts”.“enabled” AS t0_r20, “hosts”.“puppet_ca_proxy_id” AS t0_r21, “hosts”.“managed” AS t0_r22, “hosts”.“use_image” AS t0_r23, “hosts”.“image_file” AS t0_r24, “hosts”.“uuid” AS t0_r25, “hosts”.“compute_resource_id” AS t0_r26, “hosts”.“puppet_proxy_id” AS t0_r27, “hosts”.“certname” AS t0_r28, “hosts”.“image_id” AS t0_r29, “hosts”.“organization_id” AS t0_r30, “hosts”.“location_id” AS t0_r31, “hosts”.“type” AS t0_r32, “hosts”.“otp” AS t0_r33, “hosts”.“realm_id” AS t0_r34, “hosts”.“compute_profile_id” AS t0_r35, “hosts”.“provision_method” AS t0_r36, “hosts”.“grub_pass” AS t0_r37, “hosts”.“expired_on” AS t0_r38, “hosts”.“global_status” AS t0_r39, “hosts”.“lookup_value_matcher” AS t0_r40, “hosts”.“pxe_loader” AS t0_r41, “hosts”.“initiated_at” AS t0_r42, “hosts”.“build_errors” AS t0_r43, “compute_resources”.“id” AS t1_r0, “compute_resources”.“name” AS t1_r1, “compute_resources”.“description” AS t1_r2, “compute_resources”.“url” AS t1_r3, “compute_resources”.“user” AS t1_r4, “compute_resources”.“password” AS t1_r5, “compute_resources”.“uuid” AS t1_r6, “compute_resources”.“type” AS t1_r7, “compute_resources”.“created_at” AS t1_r8, “compute_resources”.“updated_at” AS t1_r9, “compute_resources”.“attrs” AS t1_r10, “compute_resources”.“caching_enabled” AS t1_r11, “compute_resources”.“domain” AS t1_r12, “compute_resources”.“http_proxy_id” AS t1_r13 FROM “hosts” INNER JOIN “fact_values” ON “fact_values”.“host_id” = “hosts”.“id” INNER JOIN “fact_names” ON “fact_names”.“id” = “fact_values”.“fact_name_id” LEFT OUTER JOIN “compute_resources” ON “compute_resources”.“id” = “hosts”.“compute_resource_id” AND (1=0) WHERE “hosts”.“type” IN (‘Host::Managed’) AND “hosts”.“organization_id” = 1 AND “hosts”.“location_id” = 2 AND (((“compute_resources”.“name” = ‘Test’) AND (“hosts”.“organization_id” = ‘1’) AND (“hosts”.“location_id” = ‘2’))) AND (fact_names.name = ‘app_teir’) AND (fact_values.value = ‘test’)))) ORDER BY “hosts”.“name” ASC LIMIT ? OFFSET ?