search

composite-primary-keys / composite_primary_keys

Composite Primary Keys support for Active Record
1.03k stars 350 forks source link

After activerecord update to 5.2.4, relationship error appears #503

Closed victoreduardo closed 4 years ago

victoreduardo commented 4 years ago

Yesterday, 27/11/2019, rails update to 5.2.4. Then, appear error composite_primary_keys when querry is construct. See below.

PG::UndefinedColumn: ERROR: column tbcadpessoal.[:cdg_orgaoorigem, :cdg_ordem] does not exist LINE 2: ...paciente" INNER JOIN "usistema"."tbcadpessoal" ON "usistema"... ^

Apparently, when composite_primary_keys is building a query, it is not building the relationships between keys correctly.

The relationship is this:

belongs_to :recursos_humanos_tb_cad_dependente, class_name: 'RecursosHumanos::TbCadDependente', foreign_key: [:cdg_ordem, :id_dependente], primary_key: [:cdg_ordem, :id_dependente]

I use postgresql 10 and ruby 2.5.3.

codeodor commented 4 years ago

What version of CPK are you running?

victoreduardo commented 4 years ago

It's 11.2.0

codeodor commented 4 years ago

What happens if you leave off the primary_key in the belongs_to definition?

victoreduardo commented 4 years ago

The same mistake. When I fix the rails version to 5.2.3, everything is back to normal.

xiexie11 commented 4 years ago

I have the same issue with Oracle 12c, CPK 11.2.0, Rails 5.2.4, Ruby 2.6.3. Rails 5.2.3works fine.

codeodor commented 4 years ago

@cfis I was hoping to narrow it down with my questions, but I'm not sure if I did. I don't have a moment to take a deeper look right now, so I'm tagging you in case you might.

JasonPoll commented 4 years ago

Experiencing the same problem. Seems as though they changes something in AR 5.2.4 that breaks CPK's query-building. Sometimes.

Postgres 11.4 CPK 11.2.0 Rails 5.2.4 (and another project using ActiveRecord 5.2.4 stand-alone)

Example from one of my projects, I've an association defined as: 

has_many :monitored_things, class_name:  'Ndm::MonitoredThing', foreign_key: %i(site_id gateway_device_id)
[1] pry(main)> gw = Ndm::SiteGatewayDevice.last
=> #<Ndm::SiteGatewayDevice:0x00007fc158d39c78
# ....attributes redacted for brevity

# this works, the generated query is correct:
[2] pry(main)> gw.monitored_things
  Ndm::MonitoredThing Load (3.4ms)  SELECT "monitored_things".* FROM "monitored_things" WHERE "monitored_things"."site_id" = $1 AND "monitored_things"."gateway_device_id" = $2  [["site_id", "zd394060899893"], ["gateway_device_id", "lm8802"]]
=> [#<Ndm::MonitoredThing:0x00007fc15e053cd8
#...lots of monitored-things.....again, redacted for brevity.

# however, this explodes: 
[3] pry(main)> gw = Ndm::SiteGatewayDevice.joins(:monitored_things).last
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column monitored_things.[:site_id, :gateway_device_id] does not exist
LINE 1: ...ateway_devices" INNER JOIN "monitored_things" ON "monitored...
                                                             ^
: SELECT  "site_gateway_devices".* FROM "site_gateway_devices" INNER JOIN "monitored_things" ON "monitored_things"."[:site_id, :gateway_device_id]" = "site_gateway_devices"."site_id,id" ORDER BY "site_gateway_devices"."site_id" DESC, "site_gateway_devices"."id" DESC LIMIT $1
from /Users/jtpoll/.rbenv/versions/2.6.4/lib/ruby/gems/2.6.0/gems/activerecord-5.2.4/lib/active_record/connection_adapters/postgresql_adapter.rb:677:in `async_prepare'
Caused by PG::UndefinedColumn: ERROR:  column monitored_things.[:site_id, :gateway_device_id] does not exist
LINE 1: ...ateway_devices" INNER JOIN "monitored_things" ON "monitored...
                                                             ^
from /Users/jtpoll/.rbenv/versions/2.6.4/lib/ruby/gems/2.6.0/gems/activerecord-5.2.4/lib/active_record/connection_adapters/postgresql_adapter.rb:677:in `async_prepare'

Rolling back to Rails/ActiveRecord 5.2.3 corrects the issue.

JasonPoll commented 4 years ago

Can confirm this issue still exists with ActiveRecord 5.2.4.1.

kingdonb commented 4 years ago

Thanks for this thread,

I am upgrading from 5.2.0 to later 5.2 releases, stopped at 5.2.3 because I seemed to hit this issue, too. So I locked my composite-primary-keys gem at 11.0.2, which has this issue with Rails versions >5.2.0

NoMethodError (private method `aliases' called for #<ActiveRecord::Associations::JoinDependency:0x00007ffc6e2c90f8>):

After reading a little bit of the release history here, I see that was addressed in 11.2, so, upgrading composite keys gem to 11.2.0 lets me upgrade to 5.2.3 (thanks for that!)

Upgrading past that, to 5.2.4.1, I'm using the OCI adapter for Oracle, I get:

OCIError: ORA-00972: identifier is too long

The join that my query generated looks like it's in error, 

...
AS t6_r4 FROM "NDHRPYADMIN"."PZRAFFI" LEFT OUTER JOIN "NDHRPYADMIN"."PZRAEXP" ON "NDHRPYADMIN"."PZRAEXP"."[:pzraexp_pidm, :pzraexp_rels_code]" = "NDHRPYADMIN"."PZRAFFI"."pzraffi_pidm,pzraffi_rels_code" 
LEFT OUTER JOIN "NDHRPYADMIN"."PZRAEED" ON "NDHRPYADMIN"."PZRAEED"."[:pzraeed_pidm, :pzraeed_aexp_seqno]" = "NDHRPYADMIN"."PZRAEXP"."pzraexp_pidm,pzraexp_seqno" 
LEFT OUTER JOIN "NDHRPYADMIN"."PZRASPN" ON "NDHRPYADMIN"."PZRASPN"."[:pzraspn_pidm, :pzraspn_aexp_seqno]" = "NDHRPYADMIN"."PZRAEXP"."pzraexp_pidm,pzraexp_seqno" 
LEFT OUTER JOIN "NDHRPYADMIN"."PZVEXCP" ON "NDHRPYADMIN"."PZVEXCP"."PZVEXCP_CODE" = "NDHRPYADMIN"."PZRAEXP"."PZRAEXP_EXCP_CODE" 
LEFT OUTER JOIN "NDHRPYADMIN"."PZVRELS" ON "NDHRPYADMIN"."PZVRELS"."PZVRELS_CODE" = "NDHRPYADMIN"."PZRAEXP"."PZRAEXP_RELS_CODE" 
LEFT OUTER JOIN "NDHRPYADMIN"."PZVRELS" "RELS_NDHRPYADMIN_PZRAFFI" ON "RELS_NDHRPYADMIN_PZRAFFI"."PZVRELS_CODE" = "NDHRPYADMIN"."PZRAFFI"."PZRAFFI_RELS_CODE" WHERE "NDHRPYADMIN"."PZRAFFI"."PZRAFFI_PIDM" = :a1):

For comparison, the join generated in the previous ActiveRecord version does not have any erroneous arrays of symbols or comma-separated names in it:

FROM "NDHRPYADMIN"."PZRAFFI" LEFT OUTER JOIN "NDHRPYADMIN"."PZRAEXP" ON "NDHRPYADMIN"."PZRAEXP"."PZRAEXP_PIDM" = "NDHRPYADMIN"."PZRAFFI"."PZRAFFI_PIDM" AND "NDHRPYADMIN"."PZRAEXP"."PZRAEXP_RELS_CODE" = "NDHRPYADMIN"."PZRAFFI"."PZRAFFI_RELS_CODE" 
LEFT OUTER JOIN "NDHRPYADMIN"."PZRAEED" ON "NDHRPYADMIN"."PZRAEED"."PZRAEED_PIDM" = "NDHRPYADMIN"."PZRAEXP"."PZRAEXP_PIDM" AND "NDHRPYADMIN"."PZRAEED"."PZRAEED_AEXP_SEQNO" = "NDHRPYADMIN"."PZRAEXP"."PZRAEXP_SEQNO" 
LEFT OUTER JOIN "NDHRPYADMIN"."PZRASPN" ON "NDHRPYADMIN"."PZRASPN"."PZRASPN_PIDM" = "NDHRPYADMIN"."PZRAEXP"."PZRAEXP_PIDM" AND "NDHRPYADMIN"."PZRASPN"."PZRASPN_AEXP_SEQNO" = "NDHRPYADMIN"."PZRAEXP"."PZRAEXP_SEQNO" 
LEFT OUTER JOIN "NDHRPYADMIN"."PZVEXCP" ON "NDHRPYADMIN"."PZVEXCP"."PZVEXCP_CODE" = "NDHRPYADMIN"."PZRAEXP"."PZRAEXP_EXCP_CODE" 
LEFT OUTER JOIN "NDHRPYADMIN"."PZVRELS" ON "NDHRPYADMIN"."PZVRELS"."PZVRELS_CODE" = "NDHRPYADMIN"."PZRAEXP"."PZRAEXP_RELS_CODE" 
LEFT OUTER JOIN "NDHRPYADMIN"."PZVRELS" "RELS_NDHRPYADMIN_PZRAFFI" ON "RELS_NDHRPYADMIN_PZRAFFI"."PZVRELS_CODE" = "NDHRPYADMIN"."PZRAFFI"."PZRAFFI_RELS_CODE" WHERE "NDHRPYADMIN"."PZRAFFI"."PZRAFFI_PIDM" = :a1

I think I can trace this down if I have time, not sure if it's exactly the same error as others have reported, but it looks close enough to me that it seems likely to be the same issue.

(Edit: my suspicion, though, is that this issue does not affect the Rails 6 version, and it will be easier to just upgrade than to trace the specific changes in Rails 5.2.4 which triggered this issue. Haven't tried going down either road yet.)

cfis commented 4 years ago

If someone could reproduce using the test data in cpk that would be really helpful. Or maybe an existing test is now failing?

kingdonb commented 4 years ago

I have this on my to-do list for tomorrow, (the blocker is that I didn't have an Oracle database that I can run the test cases against.)

It seems clear that I should be able to reproduce it with a test case though, if there isn't an existing test case that captures the issue.

kingdonb commented 4 years ago

It is for sure some tests are failing now, some (less) tests were failing on the previous version of ActiveRecord 5.2.3. I used Oracle 18.4.0 XE which can be deployed in a Docker container, that's handy!

with AR 5.2.3: https://gist.github.com/kingdonb/b90f2202582da42b72e8fda945e0ba4e

with AR 5.2.4.1: https://gist.github.com/kingdonb/cb702aa7e21d3e08f7f91e54cee81d8d

The punchline from each gist, respectively, reads:

An example of a test that fails in a manner that looks pretty consistent with the errors I saw in my specific project:

  7) Error:
TestAssociations#test_has_many_with_primary_key_with_associations:
ActiveRecord::StatementInvalid: OCIError: ORA-00904: "MEMBERSHIPS"."user_id,group_id": invalid identifier: SELECT "MEMBERSHIPS"."USER_ID" AS t0_r0, "MEMBERSHIPS"."GROUP_ID" AS t0_r1, "MEMBERSHIP_STATUSES"."ID" AS t1_r0, "MEMBERSHIP_STATUSES"."USER_ID" AS t1_r1, "MEMBERSHIP_STATUSES"."GROUP_ID" AS t1_r2, "MEMBERSHIP_STATUSES"."STATUS" AS t1_r3, "MEMBERSHIPS"."USER_ID" AS t0_r0, "MEMBERSHIPS"."GROUP_ID" AS t0_r1, "MEMBERSHIP_STATUSES"."ID" AS t1_r0, "MEMBERSHIP_STATUSES"."USER_ID" AS t1_r1, "MEMBERSHIP_STATUSES"."GROUP_ID" AS t1_r2, "MEMBERSHIP_STATUSES"."STATUS" AS t1_r3 FROM "MEMBERSHIPS" LEFT OUTER JOIN "MEMBERSHIP_STATUSES" ON "MEMBERSHIP_STATUSES"."[:user_id, :group_id]" = "MEMBERSHIPS"."user_id,group_id" WHERE (membership_statuses.status = 'Active')
    stmt.c:267:in oci8lib_260.bundle
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/ruby-oci8-2.2.8/lib/oci8/cursor.rb:131:in `exec'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-oracle_enhanced-adapter-5.2.8/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:140:in `exec'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-oracle_enhanced-adapter-5.2.8/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:41:in `block in exec_query'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-5.2.4.1/lib/active_record/connection_adapters/abstract_adapter.rb:581:in `block (2 levels) in log'
    /usr/local/Cellar/ruby/2.6.5/lib/ruby/2.6.0/monitor.rb:235:in `mon_synchronize'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-5.2.4.1/lib/active_record/connection_adapters/abstract_adapter.rb:580:in `block in log'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activesupport-5.2.4.1/lib/active_support/notifications/instrumenter.rb:23:in `instrument'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-5.2.4.1/lib/active_record/connection_adapters/abstract_adapter.rb:571:in `log'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-oracle_enhanced-adapter-5.2.8/lib/active_record/connection_adapters/oracle_enhanced/dbms_output.rb:36:in `log'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-oracle_enhanced-adapter-5.2.8/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:24:in `exec_query'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-5.2.4.1/lib/active_record/connection_adapters/abstract/database_statements.rb:478:in `select'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-5.2.4.1/lib/active_record/connection_adapters/abstract/database_statements.rb:70:in `select_all'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-5.2.4.1/lib/active_record/connection_adapters/abstract/query_cache.rb:106:in `select_all'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-5.2.4.1/lib/active_record/relation.rb:555:in `block (2 levels) in exec_queries'
    /Users/kbarret8/Desktop/devel/ruby/composite_primary_keys/lib/composite_primary_keys/relation/finder_methods.rb:22:in `apply_join_dependency'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-5.2.4.1/lib/active_record/relation.rb:550:in `block in exec_queries'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-5.2.4.1/lib/active_record/relation.rb:584:in `skip_query_cache_if_necessary'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-5.2.4.1/lib/active_record/relation.rb:547:in `exec_queries'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-5.2.4.1/lib/active_record/relation.rb:422:in `load'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-5.2.4.1/lib/active_record/relation.rb:200:in `records'
    /Users/kbarret8/.rvm/gems/ruby-2.2.10/gems/activerecord-5.2.4.1/lib/active_record/relation/delegation.rb:71:in `length'
    /Users/kbarret8/Desktop/devel/ruby/composite_primary_keys/test/test_associations.rb:328:in `test_has_many_with_primary_key_with_associations'

Also, in case it is helpful for bisecting or any other reason, the results are about the same on 5.2.4 and 5.2.4.1. There appear to be some non-deterministic failures as I'm getting slightly different results on repeated executions, but the 7 errors remain consistent on 5.2.4 and 5.2.4.1.

For posterity version that was tested of composite_primary_keys of course is the latest in the tag for 5.2.x rails compatibility, v11.2.0

kingdonb commented 4 years ago

The situation according to tests seems to be slightly worse on later versions,

against the master branch (with the oracle adapters enabled, somehow they have become disabled in the master branch and even in the release... maybe nobody had the means to test)

Most of the errors seem to be, as I guess is already reported in #497, 

ActiveRecord::StatementInvalid: OCIError: ORA-00920: invalid relational operator

(I'll follow up there, since that's a separate issue, sorry for crossing)

cfis commented 4 years ago

Thanks @kingdonb for digging in. Its never much fun setting up Oracle on my local machine, but I'll see what I can do. Or maybe you're right and docker is the way to go.

marcocarvalho commented 4 years ago

+1 (but with postgres)

timfsw commented 4 years ago

+1 (but with mysql) & rails 5.2.4.2

Our tests failed with Mysql2::Error: Unknown column table_name.[:column_1, :column_2] ' in 'on clause'

cfis commented 4 years ago

Hopefully this is fixed now thanks to @Slike9 (#512).