sql: UPDATE ... FROM ... ORDER BY and DELETE ... USING returns errors when the columns in the ORDER BY are from the table in the FROM clause.

[faizaanmadhani]

Describe the problem

In UPDATE ... FROM ... ORDER BY ... statements where the columns specified in the ORDER BY clause come from the FROM table, cockroach returns an error to the user which stating error (42P10): SELECT DISTINCT ON expressions must match initial ORDER BY expressions. This is possibly because columns in the FROM clause are not in scope of the ORDER BY clause of an UPDATE FROM statement.

To Reproduce

Currently, we do not test ORDER BY clauses in UPDATE ... FROM statements, though we support it as shown in the docs. Adding an optbuilder test to pkg/sql/opt/optbuilder/testdata/update_from: UPDATE abc SET b = other.d FROM dec AS other WHERE abc.a = other.k ORDER BY other.k DESC LIMIT 2 returns this error:

error (42P10): SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Note that the tables used in this test are already present in the optbuilder test file, and are:

CREATE TABLE abc (a int primary key, b int, c int)
CREATE TABLE dec (k INT PRIMARY KEY, d DECIMAL(10, 2))

Expected behavior
UPDATE ... FROM should allow a user to ORDER BY by the contents of the FROM table.

Jira issue: CRDB-20455

[mgartner]

This will affect DELETE .. USING initially as well.

[mgartner]

@faizaanmadhani and I looked into this a bit, and we discovered that this error occurs whenever the ORDER BY clause references a column that is not a PK column of the target table. For example, it will occur in both UPDATES below:

CREATE TABLE ab (a INT PRIMARY KEY, b INT)
CREATE TABLE cd (c INT PRIMARY KEY, d INT)

UPDATE ab SET b = 1 WHERE true ORDER BY b LIMIT 1
UPDATE ab SET b = 1 FROM cd WHERE b = d ORDER BY c LIMIT 1

[mgartner]

I remember thinking that ORDER BY in UPDATE and DELETE statements was ambiguous, or created some sort of fundamental problem with the FROM or USING joins. I don't remember exactly, though. I think this oddity is likely the reason that Postgres does not support ORDER BY in UPDATE and DELETE.