update_all behavior is different between mysql and the other databases #13496

Open
such opened this Issue Dec 26, 2013 · 16 comments

Comments

Projects
None yet

such commented Dec 26, 2013

I'm trying to copy one column from one table to another. It does not work with Postgresql.

I'm not sure if this is a bug or if this works by luck in MySQL but here is my case:

I execute

ModelA.join(:modelB).update_all('modelA.column = modelB.column')

That fails with:

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "modelBs"
LINE 1: ..." SET modelAs.column = modelBs...
                                                         ^
UPDATE "modelAs" SET modelAs.column = modelBs.column WHERE "modelAs"."id" IN (
  SELECT "modelAs"."id" FROM "modelAs" INNER JOIN "modelBs" ON "modelBs"."id" = "modelAs"."modelB_id"
)

It succeeds with MySQL, the following SQL query being generated:

UPDATE "modelAs" INNER JOIN "modelBs" ON "modelBs"."id" = "modelAs"."modelB_id" SET modelAs.column = modelBs.column

Thanks to AbstractMysqlAdapter redefining join_for_update from the DatabaseStatements module.

For this to work with Postgresql, we would need to generate:

UPDATE "modelAs" SET modelAs.column = modelBs.column FROM ModelAs, ModelBs WHERE modelBs"."id" = "modelAs"."modelB_id"
Member

robin850 commented Dec 26, 2013

Hello,

Thanks for reporting but could you provide an executable gist to showcase the problem please ?

such commented Dec 26, 2013

Here it is: https://gist.github.com/such/8136555

Changing the adapter to mysql2 allows the test to pass (even though it doesn't demonstrate that the update is actually done then... but it is !)

Member

robin850 commented Dec 26, 2013

(Thanks for the gist ; as far as I can see, it fails as well on SQLite)

So, what is an equivalent syntax we can use instead?

maxrossello referenced this issue in maxrossello/redmine_app_timesheets Jan 27, 2014

Closed

Migration Errors with postgresql #6

such added the stale label May 1, 2014

Owner

rafaelfranca commented May 1, 2014

This issue has been automatically marked as stale because it has not been commented on for at least
three months.

The resources of the Rails team are limited, and so we are asking for your help.

If you can still reproduce this error on the 4-1-stable, 4-0-stable branches or on master,
please reply with all of the information you have about it in order to keep the issue open.

Thank you for all your contributions.

robin850 removed the stale label May 3, 2014

Member

robin850 commented May 3, 2014

This is still a problem on 4-1-stable and current master.

Contributor

joseluistorres commented Jun 9, 2014

Actually the UPDATE with JOIN in PostgreSQL will be like this:

http://www.postgresqltutorial.com/postgresql-update/#crayon-5394fa6c719bb531006798

UPDATE posts
 SET title = authors.name 
FROM authors
WHERE
 posts.author_id = authors.id;

working on the fix...
cc @robin850 @such

Contributor

joseluistorres commented Jun 10, 2014

Ok it also fails with SQLite

  1) Error:
RelationTest#test_update_all_with_joins:
ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: authors.name: 
UPDATE "posts" SET title = authors.name 
WHERE "posts"."id" IN (SELECT "posts"."id" FROM "posts" 
INNER JOIN "authors" ON "authors"."id" = "posts"."author_id")

and something like this will fix it:

UPDATE "posts" SET title = (SELECT authors.name 
FROM "authors" where "authors"."id" = "posts"."author_id") 
WHERE EXISTS (
SELECT authors.name FROM "authors" where "authors"."id" = "posts"."author_id")

senny removed the PostgreSQL label Jul 25, 2014

rails-bot added the stale label Nov 19, 2014

This issue has been automatically marked as stale because it has not been commented on for at least
three months.

The resources of the Rails team are limited, and so we are asking for your help.

If you can still reproduce this error on the 4-1-stable, 4-0-stable branches or on master,
please reply with all of the information you have about it in order to keep the issue open.

Thank you for all your contributions.

Contributor

stormsilver commented Mar 2, 2015

I can confirm this is still happening on master as of today: https://gist.github.com/stormsilver/6245b43838e77f5c6636

I'm quite sure I stumbled upon the same bug probably.
I'm using Rails 4.1.4, ruby 2.2.1 and Postgresql 9.4.1

Steps

class User < ActiveRecord::Base
  belongs_to :group
end
class Group < ActiveRecord::Base
  has_many :users
end

Code wrote directly in rails console:

user_group = Group.where(name: 'User').first

User.eager_load(:group).references(:groups).where("groups.name = 'Contractor' OR groups.name = 'ContractorManager'").update_all(group_id: user_group.id)

This update_all statement translates into this query:

UPDATE "users" SET "group_id" = 5 WHERE "users"."id" IN (SELECT "users"."id" FROM "users"  WHERE "users"."enabled" = 't' AND (groups.name = 'Contractor' OR groups.name = 'ContractorManager')  ORDER BY email asc)

Which is wrong because the nested query is missing the join. When not using update_all I get:

SELECT "users"."id" AS t0_r0, "users"."email" AS t0_r1, "groups"."id" AS t1_r0, "groups"."name" AS t1_r1 FROM "users" LEFT OUTER JOIN "groups" ON "groups"."id" = "users"."group_id" WHERE "users"."enabled" = 't' AND (groups.name = 'Contractor' OR groups.name = 'ContractorManager')  ORDER BY email asc

Which is correct. I expect the code to work in both cases, it can obviously be worked around.

vnavkal commented May 12, 2015

+1, failing with PostgreSQL 9.3.5, Rails 4.1.5, and Ruby 2.2.2.

Soleone commented Oct 29, 2015

I just ran into a similar issue. I am only working with MySQL here (slightly related #522):

If I understand correctly update_all using joins only works without using a limit or offset with the mysql adapter.

Once you add a limit or offset the underlying query that gets generated for mysql uses an anonymous subquery and then you can no longer reference a column from the joined table.

Trying to update columns in table A with columns from table B. The following works fine:

> CardInteraction.joins(:card).update_all("card_interactions.handle = cards.handle")

UPDATE card_interactions
INNER JOIN cards ON cards.id = card_interactions.card_id
SET card_interactions.handle = cards.handle

=> 7

Now as soon as you introduce a limit or offset the query generator no longer uses a JOIN statement but instead a subquery:

Mysql2::Error: Unknown column 'cards.handle' in 'field list':

> CardInteraction.joins(:card).offset(1).update_all("card_interactions.handle = cards.handle")

UPDATE card_interactions
SET card_interactions.handle = cards.handle
WHERE card_interactions.id IN (
  SELECT id FROM (
    SELECT  card_interactions.id
    FROM card_interactions
    INNER JOIN cards ON cards.id = card_interactions.card_id
    WHERE LIMIT 1 OFFSET 1
  )
__active_record_temp)

The main issue is not necessarily that it creates subqueries but that the subqueries are anonymous and as such I no longer have access to cards.handle to pass into the update_all part.

Contributor

joseluistorres commented Oct 30, 2015

@Soleone I would suggest you use plain SQL there, I investigated this myself to see why it fails and it goes back to Arel, not a clear solution actually 😳

Soleone commented Oct 30, 2015

@joseluistorres okay thanks! for now i will probably do plain SQL.

unfortunately in the framework i'm using right now we expect relations to be passed around (e.g. CardInteraction.joins(:card) to automatically append limit and offset clauses to iterate through millions of rows on several sharded databases). would be cool if eventually activerelation would be able to support this, but not sure how monumental that task is.

denschub referenced this issue in diaspora/diaspora Mar 4, 2016

Merged

Link share visibilities directly with users #6723

geralddzx commented Nov 23, 2016 edited

I am running into the same issue. Is this fixed on any of the new versions? I'm considering importing my database to mysql to get around this.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment