Manageiq-ui-classic: Report data request fails on invalid SQL query when sorting

[skateman]

Steps to reproduce:

1. Open VMs and Templates
2. Select the table view on the right
3. Sort by compliance

[----] F, [2019-03-26T10:24:00.686401 #12006:2aca7c924444] FATAL -- : Error caught: [ActiveRecord::StatementInvalid] PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ...viders::Vmware::InfraManager::Template') ORDER BY (SELECT  "...
                                                             ^
: SELECT  DISTINCT "vms"."id", (SELECT  "compliances"."compliant" FROM "compliances" WHERE "compliances"."resource_id" = "vms"."id" ORDER BY "compliances"."timestamp" LIMIT 1) AS alias_0 FROM "vms" LEFT OUTER JOIN "hosts" ON "hosts"."id" = "vms"."host_id" LEFT OUTER JOIN "storages" ON "storages"."id" = "vms"."storage_id" LEFT OUTER JOIN "ext_management_systems" ON "ext_management_systems"."id" = "vms"."ems_id" LEFT OUTER JOIN "snapshots" ON "snapshots"."vm_or_template_id" = "vms"."id" LEFT OUTER JOIN "operating_systems" ON "operating_systems"."vm_or_template_id" = "vms"."id" LEFT OUTER JOIN "hardwares" ON "hardwares"."vm_or_template_id" = "vms"."id" LEFT OUTER JOIN "taggings" ON "taggings"."taggable_id" = "vms"."id" AND "taggings"."taggable_type" = $1 LEFT OUTER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE "vms"."type" IN ('ManageIQ::Providers::Redhat::InfraManager::Vm', 'ManageIQ::Providers::Kubevirt::InfraManager::Vm', 'ManageIQ::Providers::Microsoft::InfraManager::Vm', 'ManageIQ::Providers::Vmware::InfraManager::Vm', 'VmXen', 'ManageIQ::Providers::Redhat::InfraManager::Template', 'TemplateXen', 'ManageIQ::Providers::Kubevirt::InfraManager::Template', 'ManageIQ::Providers::Openstack::InfraManager::Template', 'ManageIQ::Providers::Microsoft::InfraManager::Template', 'ManageIQ::Providers::Vmware::InfraManager::Template') ORDER BY (SELECT  "compliances"."compliant" FROM "compliances" WHERE "compliances"."resource_id" = "vms"."id" ORDER BY "compliances"."timestamp" DESC LIMIT 1) DESC LIMIT $2 OFFSET $3
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/connection_adapters/postgresql_adapter.rb:600:in `async_exec'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/connection_adapters/postgresql_adapter.rb:600:in `block in exec_no_cache'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/connection_adapters/abstract_adapter.rb:590:in `block in log'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activesupport-5.0.7.2/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/connection_adapters/abstract_adapter.rb:583:in `log'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/connection_adapters/postgresql_adapter.rb:600:in `exec_no_cache'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/connection_adapters/postgresql_adapter.rb:589:in `execute_and_clear'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:103:in `exec_query'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/connection_adapters/abstract/database_statements.rb:373:in `select'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/connection_adapters/abstract/database_statements.rb:41:in `select_all'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/connection_adapters/abstract/query_cache.rb:93:in `block in select_all'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/connection_adapters/abstract/query_cache.rb:114:in `cache_sql'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/connection_adapters/abstract/query_cache.rb:93:in `select_all'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/relation/finder_methods.rb:436:in `limited_ids_for'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/relation/finder_methods.rb:422:in `apply_join_dependency'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/relation/finder_methods.rb:382:in `find_with_associations'
/home/skateman/Repositories/ManageIQ/manageiq/lib/extensions/ar_virtual.rb:739:in `find_with_associations'
/home/skateman/Repositories/ManageIQ/manageiq/lib/extensions/ar_virtual.rb:741:in `find_with_associations'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/relation.rb:706:in `exec_queries'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/relation.rb:583:in `load'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/relation.rb:260:in `records'
/home/skateman/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/activerecord-5.0.7.2/lib/active_record/relation.rb:256:in `to_a'
/home/skateman/Repositories/ManageIQ/manageiq/app/models/miq_report/generator.rb:383:in `build_table'
/home/skateman/Repositories/ManageIQ/manageiq/app/models/miq_report/search.rb:123:in `paged_view_search'
/home/skateman/Repositories/ManageIQ/manageiq-ui-classic/app/controllers/application_controller.rb:1468:in `get_view'
/home/skateman/Repositories/ManageIQ/manageiq-ui-classic/app/controllers/application_controller.rb:430:in `report_data'

[lpichler]

@miq-bot assign @lpichler

[miq-bot]

@lpichler 'lpichler' is an invalid assignee, ignoring...

[skateman]

@kbrock this might be your doing

[lpichler]

@kbrock , maybe you will know more - there is what I found:

content of search_options[:order] from this place :

[
    [0] #<Arel::Nodes::Ascending:0x00007f9c847e7ea8 @expr="(SELECT  \"compliances\".\"compliant\" FROM \"compliances\" WHERE \"compliances\".\"resource_id\" = \"vms\".\"id\" ORDER BY \"compliances\".\"timestamp\" DESC LIMIT 1)">
]

I suspect that something is wrong here

c is "last_compliance_status"
and then association_column(c) returns

[sql_col, sql_type]
[
    [0] "(SELECT  \"compliances\".\"compliant\" FROM \"compliances\" WHERE \"compliances\".\"resource_id\" = \"vms\".\"id\" ORDER BY \"compliances\".\"timestamp\" DESC LIMIT 1)",
    [1] :boolean
]

[kbrock]

@nicklamuro looks like the virtual attribute with a limit and a different column sort has reared its head. This works in sqlite3 but not pg

[kbrock]

wow - this one is great. turns out the order by clause is in the select clause, just with an alias (AS alias_0). Will need to get the alias into the order clause

[kbrock]

@miq-bot assign kbrock

[miq-bot]

@kbrock 'kbrock' is an invalid assignee, ignoring...

[kbrock]

ok, so we modified this column to be in arel, and we modified virtual attributes to use the includes more. This probably introduced a distinct into the query, and that is when the underlying rails bug showed up.

When a sql query has a distinct or group by, the columns used in an order by clause needs to be in the select clause. Rails adds this automatically.

https://github.com/rails/rails/blob/2c3332cc4c0fa77dbe2e13e8a792f80fbd8f4ad3/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb#L562

The code that takes the order by column/clause and converts it into a select has some simplified logic, and this breaks when the column is a clause that has a DESC in it. The code assumes the DESC is from the order itself, and not from the sub select in the order.

I'm looking into patching this method

[chessbyte]

@skateman @kbrock was this issue fixed with https://github.com/ManageIQ/manageiq/pull/18909 ?

[kbrock]

Yes, this was fixed by 18909