sql: support correlated subqueries in UPDATE

konung commented 5 years ago

Summary

Correlated subqueries in update statements don't work. Filing this example as requested here: https://www.cockroachlabs.com/docs/stable/subqueries.html#correlated-subqueries (in the Note section) Related to #3288

Use Case

I need to create effecient counter cache on the tables with millions of row. It's possible to do this within application itself, but highly ineffecient and slow. Running a simple updates statement ( as listed below) is way faster. This is common pattern in migrations in Rails/Sinatra for creating counter cache on related models.

Steps to reproduce

Using sample Startrek data set.
Add a new column to episodes: quotes_count
Update said column from subquery result

ALTER TABLE public.episodes ADD quotes_count int NULL;
CREATE INDEX episodes_quotes_count_idx ON public.episodes (quotes_count);

Actual statement in question

UPDATE
  episodes
SET
  quotes_count =
  (SELECT
    COUNT(quotes.quote) AS quotes_count
  FROM
    quotes
  WHERE quotes.episode = episodes.id)

Expected Result

This works in MariaDB, MySQL and Postgres.

Actual Result

Doesn't seem to be supported yet?

Returns this error


SQL Error [42P01]: ERROR: no data source matches prefix: episodes
  ERROR: no data source matches prefix: episodes
  ERROR: no data source matches prefix: episodes

I'm just trying out CDB coming from MariaDB/PostgreSQL, and I might be doing it wrong in CDb context, if so, what the supported SQL in Cockroach DB?

Thank you

knz commented 5 years ago

Thank you, we are aware of this and are working on it.

knz commented 5 years ago

cc @andy-kimball you may want to add this to your test suite.

konung commented 5 years ago

@knz Yep, I figured as much. I just reported it , because in the docs there is a request for more examples. Hope this helps :)

andy-kimball commented 5 years ago

Correlated subqueries in UPDATE is now working by default in the latest master branch, after merging #34522.

cockroachdb / cockroach