Cannot combine DELETE .. USING .. ORDER BY .. LIMIT

[lukaseder]

Describe the problem

I tried running a DELETE .. USING .. ORDER BY .. LIMIT query but it resulted in an error that seems to hint at an internal transformation flaw for the syntax combination:

SQL Error [42P10]: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

To Reproduce

create table t (i int);
create table u (i int);

delete from t
using u
where t.i = u.i
order by t.i
limit 1;

Remove the ORDER BY clause, and the problem goes away

Expected behavior

The query should run normally.

If applicable, add screenshots to help explain your problem.

Environment:

• CockroachDB version: CockroachDB CCL v23.2.4 (x86_64-pc-linux-gnu, built 2024/04/08 21:52:09, go1.21.8 X:nocoverageredesign)
• Server OS: Linux, using this tag: https://hub.docker.com/layers/cockroachdb/cockroach/v23.2.4/images/sha256-b60a1c3fd8a76abe828340e0d477efa14b8c826a2e45afaf60f6c4b4e06095f6?context=explore
• Client app: JDBC / Dbeaver

Jira issue: CRDB-38401

[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 dev-inf.}

[lukaseder]

I've reverse engineered this and this seems to work:

delete from t
using u
where t.i = u.i
order by t.rowid, t.i
limit 1;

But this risks to implement a different semantics, I think?

[lukaseder]

Also, the problem doesn't appear with explicit primary keys. This works:

create table t (i int primary key);
create table u (i int primary key);

delete from t
using u
where t.i = u.i
order by t.i
limit 1;

[michae2]

Thank you for filing the issue, @lukaseder.

I've reverse engineered this and this seems to work:

delete from t
using u
where t.i = u.i
order by t.rowid, t.i
limit 1;

But this risks to implement a different semantics, I think?

Yes, this has different semantics. This would delete the row with the lowest rowid rather than the row with the lowest i.

I think a rewrite like this could be a workaround:

DELETE FROM t WHERE t.rowid IN (
  SELECT t.rowid FROM t JOIN u ON t.i = u.i ORDER BY t.i LIMIT 1
);

[DrewKimball]

Closing as a dupe of #89817.