Support subquery and `FROM` in `UPDATE`

risingwavelabs / risingwave

Best-in-class stream processing, analytics, and management. Perform continuous analytics, or build event-driven applications, real-time ETL pipelines, and feature stores in minutes. Unified streaming and batch. PostgreSQL compatible.

https://go.risingwave.com/slack

Apache License 2.0

6.78k stars 561 forks source link

Support subquery and `FROM` in `UPDATE` #2672

Open BugenZhao opened 2 years ago

BugenZhao commented 2 years ago

Unnesting.

UPDATE employees SET sales_count = sales_count + 1 WHERE id =
(SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');

Subquery on the right side of multi-assignment.

-- Update contact names in an accounts table to match the currently assigned salesmen:
UPDATE accounts SET (contact_first_name, contact_last_name) =
  (SELECT first_name, last_name FROM salesmen
   WHERE salesmen.id = accounts.sales_id);

UPDATE ... SET ... FROM.

-- A similar result could be accomplished with a join:
UPDATE accounts SET contact_first_name = first_name,
                  contact_last_name = last_name
FROM salesmen WHERE salesmen.id = accounts.sales_id;

Related PR: #2602

likzn commented 2 years ago

@BugenZhao Hi,I want try this. May i ask, should we update src/sqlparser which mentioned in #2602 firstly? If sure, how to update.Directly copy paste? And is the repo andygrove / sqlparser-rs or sqlparser-rs / sqlparser-rs?

BugenZhao commented 2 years ago

@BugenZhao Hi,I want try this. May i ask, should we update src/sqlparser which mentioned in #2602 firstly? If sure, how to update.Directly copy paste? And is the repo andygrove / sqlparser-rs or sqlparser-rs / sqlparser-rs?

Cool! cc @neverchanje Do we have a plan for syncing sqlparser from the upstream? Besides, this may not just be simply parser stuff. We may need to do some work on the optimizer as well. :)

CAJan93 commented 1 year ago

Cool! cc @neverchanje Do we have a plan for syncing sqlparser from the upstream? [...]

I think having a plan how to incorporate changes from sqlparser-rs into our project would make sense. I did another bug recently (see https://github.com/risingwavelabs/risingwave/pull/5673), which turned out to be already fixed in upstream. Do you think it makes sense to use the parser as a git submodule?

xiangjinwu commented 1 year ago

There was a discussion on slack and the conclusion is to diverge from upstream: https://risingwave-labs.slack.com/archives/C034U0NH5ND/p1661152590242349?thread_ts=1661152070.390169&cid=C034U0NH5ND

I personally suggest that we can diverge from their branch and evolve it based on our own needs. sqlparser-rs is not really a community-oriented project. It supports too many dialects while none of them is well supported. Thus none of the database communities can benefit from this project other than forking a stand-alone branch. That’s what MZ is doing. MZ’s parser was originally forked from sqlparser-rs but now its code is way different from the upstream.