Multi-table JOIN with UPDATE creates questionable query

[rgalanakis]

I am not sure if this is a Sequel issue per-se, it's rather some surprising (to me) Postgres behavior, but I wanted to see your take on it.

Assuming a structure like this:

create_table(:commerce_cart_items) do
  primary_key :id
  integer :quantity
end
create_table(:commerce_checkout_items) do
  primary_key :id
  foreign_key :cart_item_id, :commerce_cart_items
  integer :immutable_quantity
  # Check constraint so either cart_item_id OR immutable_quantity can be set
end

cart_item = CartItem.create(quantity: 5)
checkout_item = CheckoutItem.create(cart_item_id: cart_item.id)

I want to, in a single statement, set the immutable_quantity and cart_item_id. So I wrote the Sequel to do so:

self.db.from(:commerce_checkout_items, :commerce_cart_items).
  where(checkout_id: self.id).
  update(cart_item_id: nil, immutable_quantity: Sequel[:commerce_cart_items][:quantity])

And this generates the SQL, which at first looked fine to me:

UPDATE "commerce_checkout_items" SET "cart_item_id" = NULL, "immutable_quantity" = "commerce_cart_items"."quantity" FROM "commerce_cart_items" WHERE  ("checkout_id" = 15)

The issue is that the immutable_quantity column gets set to the value of the first cart item row's quantity (ie SELECT quantity FROM commerce_cart_items). This was... surprising! And hard to find in unit tests since it requires additional data...

So the fix is adding the join condition to the where:

self.db.from(:commerce_checkout_items, :commerce_cart_items).
  where(checkout_id: self.id, Sequel[:commerce_cart_items][:id] => Sequel[:commerce_checkout_items][:cart_item_id]).
  update(cart_item_id: nil, immutable_quantity: Sequel[:commerce_cart_items][:quantity])

Which produces this SQL:

UPDATE "commerce_checkout_items" SET "cart_item_id" = NULL, "immutable_quantity" = "commerce_cart_items"."quantity" FROM "commerce_cart_items" WHERE (("checkout_id" = 15) AND ("commerce_cart_items"."id" = "commerce_checkout_items"."cart_item_id"))

I have read the Postgres UPDATE docs and the explanation is pretty subtle, but it is covered in the examples (https://www.postgresql.org/docs/current/sql-update.html).

There is not a ton of documentation/examples on using Sequel with UPDATE FROM, so I wanted to mention this caveat here. Some of the examples I found are even incorrect with the above bug.

I know this is not a Sequel bug, but I wanted to check if I'm perhaps using from/update incorrectly, or if there's some way to automatically add the correct join criteria that I added explicitly above (I do have these things modeled correctly with associations but I'm not sure if that matters here).

[jeremyevans]

The Sequel behavior is expected, and I think the PostgreSQL behavior is as well. When you select from multiple tables, SQL treats that as an implicit CROSS JOIN, so if you don't add your own filter conditions, you get the CROSS JOIN equivalent, and for UPDATE, that means it uses the first row returned by the CROSS JOIN.

This behavior could be documented better on the Sequel side, though. We could put it in doc/postgresql.rdoc, but the behavior isn't PostgreSQL specific, since Sequel supports multiple tables in update statements on 3 other databases (MSSQL, MySQL, and SQLite). Maybe it would be best to add implicit join examples to the Dataset#update and Dataset#delete documentation?

[rgalanakis]

Yeah, I think examples would be the most helpful here. Even reading the PG docs, the behavior was not at all apparent to me until I saw the examples (finding out multi-table select is a cross join is not easy, depending on the results of the search gods, nor are the implications obvious). Maybe adding it here as well/instead of delete/update- there's an example update using a proper condition (and you also mention the cross join): https://sequel.jeremyevans.net/rdoc/files/doc/querying_rdoc.html#label-From

[jeremyevans]

I added documentation for this in 6b0375a