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