Bug #10819

PGError: ERROR: missing FROM-clause entry for table "hosts_reports" LINE 1

Added by Kal McFate almost 3 years ago. Updated about 1 year ago.

Status:New
Priority:Normal
Assigned To:-
Category:Authorization
Target version:-
Difficulty: Bugzilla link:1425752
Found in release:1.8.1 Pull request:
Story points-
Velocity based estimate-

Description

The fix for [[http://projects.theforeman.org/issues/8817]] has caused other errors. I have roles set up with two filters, view_hosts and view_reports both set to 'environment ~ prod`. Reversing [[http://projects.theforeman.org/projects/foreman/repository/revisions/54592c2f126680db8980f3909ad085124c5983d1]] fixes the issue.

ActiveRecord::StatementInvalid
PGError: ERROR: missing FROM-clause entry for table "hosts_reports" LINE 1: ...1_r4, "environments"."hostgroups_count" AS t1_r5, "hosts_rep... ^ : SELECT "reports"."id" AS t0_r0, "reports"."host_id" AS t0_r1, "reports"."reported_at" AS t0_r2, "reports"."created_at" AS t0_r3, "reports"."updated_at" AS t0_r4, "reports"."status" AS t0_r5, "reports"."metrics" AS t0_r6, "environments"."id" AS t1_r0, "environments"."name" AS t1_r1, "environments"."created_at" AS t1_r2, "environments"."updated_at" AS t1_r3, "environments"."hosts_count" AS t1_r4, "environments"."hostgroups_count" AS t1_r5, "hosts_reports"."id" AS t2_r0, "hosts_reports"."name" AS t2_r1, "hosts_reports"."last_compile" AS t2_r2, "hosts_reports"."last_freshcheck" AS t2_r3, "hosts_reports"."last_report" AS t2_r4, "hosts_reports"."updated_at" AS t2_r5, "hosts_reports"."source_file_id" AS t2_r6, "hosts_reports"."created_at" AS t2_r7, "hosts_reports"."root_pass" AS t2_r8, "hosts_reports"."serial" AS t2_r9, "hosts_reports"."puppet_status" AS t2_r10, "hosts_reports"."architecture_id" AS t2_r11, "hosts_reports"."operatingsystem_id" AS t2_r12, "hosts_reports"."environment_id" AS t2_r13, "hosts_reports"."ptable_id" AS t2_r14, "hosts_reports"."medium_id" AS t2_r15, "hosts_reports"."build" AS t2_r16, "hosts_reports"."comment" AS t2_r17, "hosts_reports"."disk" AS t2_r18, "hosts_reports"."installed_at" AS t2_r19, "hosts_reports"."model_id" AS t2_r20, "hosts_reports"."hostgroup_id" AS t2_r21, "hosts_reports"."owner_id" AS t2_r22, "hosts_reports"."owner_type" AS t2_r23, "hosts_reports"."enabled" AS t2_r24, "hosts_reports"."puppet_ca_proxy_id" AS t2_r25, "hosts_reports"."managed" AS t2_r26, "hosts_reports"."use_image" AS t2_r27, "hosts_reports"."image_file" AS t2_r28, "hosts_reports"."uuid" AS t2_r29, "hosts_reports"."compute_resource_id" AS t2_r30, "hosts_reports"."puppet_proxy_id" AS t2_r31, "hosts_reports"."certname" AS t2_r32, "hosts_reports"."image_id" AS t2_r33, "hosts_reports"."organization_id" AS t2_r34, "hosts_reports"."location_id" AS t2_r35, "hosts_reports"."type" AS t2_r36, "hosts_reports"."otp" AS t2_r37, "hosts_reports"."realm_id" AS t2_r38, "hosts_reports"."compute_profile_id" AS t2_r39, "hosts_reports"."provision_method" AS t2_r40, "hosts_reports"."grub_pass" AS t2_r41, "hosts_reports"."discovery_rule_id" AS t2_r42 FROM "reports" INNER JOIN "hosts" ON "hosts"."id" = "reports"."host_id" AND "hosts"."type" IN ('Host::Managed') INNER JOIN "environments" ON "environments"."id" = "hosts"."environment_id" LEFT OUTER JOIN "hosts" "hosts_reports_join" ON "hosts_reports_join"."id" = "reports"."host_id" AND "hosts_reports_join"."type" IN ('Host::Managed') LEFT OUTER JOIN "environments" "environments_reports" ON "environments_reports"."id" = "hosts_reports_join"."environment_id" WHERE (("environments"."name" ILIKE '%prod_%')) AND (("hosts"."name" = 'www01.production.com')) ORDER BY "reports"."reported_at" DESC NULLS LAST LIMIT 50 OFFSET 0
app/views/reports/_list.html.erb:18:in `_app_views_reports__list_html_erb__4589163754694552186_97940720'
app/views/reports/index.html.erb:3:in `_app_views_reports_index_html_erb__4135506232255663823_93278300'
app/controllers/concerns/application_shared.rb:13:in `set_timezone'
app/models/concerns/foreman/thread_session.rb:32:in `clear_thread'
lib/middleware/catch_json_parse_errors.rb:9:in `call'


Related issues

Related to Foreman - Bug #8817: PGError: ERROR: missing FROM-clause entry for table "host... Closed 12/31/2014
Duplicated by Foreman - Bug #12916: PG::UndefinedTable error when searching for reports Duplicate 12/22/2015
Duplicated by Foreman - Bug #15228: /config_reports fails when a config_reports filter search... Duplicate 05/30/2016
Duplicated by Foreman - Bug #17365: Filter on ConfigReports with like-search on hostgroup fai... Duplicate 11/16/2016
Duplicated by Foreman - Bug #18622: Can't view host config reports if permission is limited t... Duplicate 02/22/2017

History

#1 Updated by Kal McFate almost 3 years ago

  • Related to Bug #8817: PGError: ERROR: missing FROM-clause entry for table "hostgroups" added

#3 Updated by Dominic Cleal almost 3 years ago

  • Category set to Authorization
  • Release set to 1.8.3

#4 Updated by Dominic Cleal over 2 years ago

  • Release deleted (1.8.3)

#5 Updated by Dominic Cleal almost 2 years ago

  • Duplicated by Bug #12916: PG::UndefinedTable error when searching for reports added

#6 Updated by Dominic Cleal almost 2 years ago

  • Duplicated by Bug #15228: /config_reports fails when a config_reports filter search is limited. added

#7 Updated by Alex Fisher over 1 year ago

I've also just hit this in 1.11.

Happy to test any fix you might have. ;)

Thanks,
Alex

#8 Updated by Dominic Cleal over 1 year ago

  • Duplicated by Bug #17365: Filter on ConfigReports with like-search on hostgroup fails with PG::Error: ERROR: missing FROM-clause entry for table "hosts_reports" added

#9 Updated by Dominic Cleal about 1 year ago

  • Duplicated by Bug #18622: Can't view host config reports if permission is limited to a hostgroup added

#10 Updated by Marek Hulán about 1 year ago

  • Bugzilla link set to 1425752

Also available in: Atom PDF