DISTINCT ON query fails when specifying nulls order

[soupi]

Describe the problem

When trying to run a query with a DISTINCT ON clause and ORDER BY containing ASC NULLS LAST or DESC NULLS FIRST, the query fails with the following error:

SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Note that the query works when specifying ASC, DESC, ASC NULLS FIRST or DESC NULLS LAST.

To Reproduce

DDL:

create table author (id int primary key, name text, genre text);

 insert into author values 
   (1, 'Alice', 'Action'),
   (2, 'Bob', 'Biography'),
   (3, 'Carol', 'Crime'),
   (4, 'Dave', 'Action'),
   (5, 'Eve', 'Crime'),
   (6, 'Bart', null);

SQL:

SELECT
  DISTINCT ON ("genre") *
FROM
  "public"."author"
ORDER BY
  "genre" ASC NULLS LAST,
  "id" ASC NULLS LAST;

Note that the query doesn't work with DESC NULLS FIRST either, but does work when specifying ASC, DESC, ASC NULLS FIRST or DESC NULLS LAST.

Expected behavior

Query succeeds with the following results:

 id | name  |   genre
----+-------+-----------
  1 | Alice | Action
  2 | Bob   | Biography
  3 | Carol | Crime
  6 | Bart  |
(4 rows)

Environment:

• CockroachDB version: arm64-v22.2.0-beta.4-106-g153c346d06
• Server OS: MacOS arm64
• Client app: psql

Jira issue: CRDB-20934
Epic CRDB-11916

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}