Broken Update behavior on sequel version 5.56.0

[ciaoben]

Complete Description of Issue

I have a simple relation between two Models: Customer has one Manager. If I run this code:

customer.managers_dataset.where(status: 'enabled').update_sql(status: 'disabled')

With version 4.40 I would get this query:

"UPDATE `users` INNER JOIN `managers` ON (`managers`.`id` = `users`.`id`) SET `status` = 'disabled' WHERE ((`managers`.`customer_id` = 145) AND (`status` = 'enabled'))"

With version 5.56:

UPDATE (SELECT `users`.`id`, `users`.`type`, `users`.`otp`, `users`.`email_address`, `users`.`timezone`, `users`.`password_hash`, `users`.`otp_secret`, `users`.`api_token`, `users`.`change_password_token`, `users`.`expire_date_change_password_token`, `users`.`session_token`, `users`.`session_ip`, `users`.`allow_nets`, `users`.`login_mail_notification`, `users`.`session_token_expire`, `users`.`created_at`, `users`.`updated_at`, `users`.`language`, `users`.`qbvault_session_token_expire`, `users`.`qbvault_session_token`, `users`.`qbvault_session_ip`, `users`.`qbvault_last_auth`, `users`.`whmcs_password`, `managers`.`customer_id`, `managers`.`creator_id`, `managers`.`status`, `managers`.`status_detail`, `managers`.`firstname`, `managers`.`lastname`, `managers`.`company`, `managers`.`code`, `managers`.`max_domains`, `managers`.`max_email_accounts`, `managers`.`max_email_accounts_archive`, `managers`.`catch_all`, `managers`.`max_email_quota`, `managers`.`max_email_accounts_25gb`, `managers`.`notification_url`, `managers`.`notification_url_actions`, `managers`.`notification_email`, `managers`.`notification_email_actions`, `managers`.`max_email_accounts_50gb`, `managers`.`max_email_accounts_100gb` FROM `users` INNER JOIN `managers` ON (`managers`.`id` = `users`.`id`)) AS `users` SET `status` = 'disabled' WHERE ((`users`.`customer_id` = 145) AND (`status` = 'enabled'))

It tries to update on a join, and my mariadb version 10.3.38 complains:

 The target table users of the UPDATE is not updatable

I think mariadb doesn't not like update on joins. Is the new behavior wanted? Is there a way to avoid it or make it work in this scenario?

Thanks

Simplest Possible Self-Contained Example Showing the Bug

No response

Full Backtrace of Exception (if any)

No response

SQL Log (if any)

No response

Ruby Version

2.3

Sequel Version

5.56

[ciaoben]

As a driver I am on mysql2 0.5.5 (last version)

[jeremyevans]

This is expected in Sequel 5. Looks like you are using class table inheritance, and in Sequel 5, that makes the model dataset select from a subquery, and you cannot update a subquery. You can switch to:

DB[:users].join(:managers, :id=>:id).
  where{{managers[:customer_id]=>customer.id, :status=>'enabled'}}.
  update(:status=>:disabled)