sql: query with complicated subquery hangs

[giangpham712]

Describe the problem

Please describe the issue you observed, and any steps we can take to reproduce it:

To Reproduce

Create a new table with the following SQL

CREATE TABLE "Customers" (
	"CustomerID" char (5) NOT NULL ,
	"CompanyName" varchar (40) NOT NULL ,
	"ContactName" varchar (30) NULL ,
	"ContactTitle" varchar (30) NULL ,
	"Address" varchar (60) NULL ,
	"City" varchar (15) NULL ,
	"Region" varchar (15) NULL ,
	"PostalCode" varchar (10) NULL ,
	"Country" varchar (15) NULL ,
	"Phone" varchar (24) NULL ,
	"Fax" varchar (24) NULL ,
	CONSTRAINT "PK_Customers" PRIMARY KEY
	(
		"CustomerID"
	)
)

Populate some data:

INSERT INTO "Customers" VALUES('ALFKI','Alfreds Futterkiste','Maria Anders','Sales Representative','Obere Str. 57','Berlin',NULL,'12209','Germany','030-0074321','030-0076545');
INSERT INTO "Customers" VALUES('ANATR','Ana Trujillo Emparedados y helados','Ana Trujillo','Owner','Avda. de la Constitución 2222','México D.F.',NULL,'05021','Mexico','(5) 555-4729','(5) 555-3745');
INSERT INTO "Customers" VALUES('ANTON','Antonio Moreno Taquería','Antonio Moreno','Owner','Mataderos  2312','México D.F.',NULL,'05023','Mexico','(5) 555-3932',NULL);
INSERT INTO "Customers" VALUES('AROUT','Around the Horn','Thomas Hardy','Sales Representative','120 Hanover Sq.','London',NULL,'WA1 1DP','UK','(171) 555-7788','(171) 555-6750');
INSERT INTO "Customers" VALUES('BERGS','Berglunds snabbköp','Christina Berglund','Order Administrator','Berguvsvägen  8','Luleå',NULL,'S-958 22','Sweden','0921-12 34 65','0921-12 34 67');
INSERT INTO "Customers" VALUES('BLAUS','Blauer See Delikatessen','Hanna Moos','Sales Representative','Forsterstr. 57','Mannheim',NULL,'68306','Germany','0621-08460','0621-08924');
INSERT INTO "Customers" VALUES('BLONP','Blondesddsl père et fils','Frédérique Citeaux','Marketing Manager','24, place Kléber','Strasbourg',NULL,'67000','France','88.60.15.31','88.60.15.32');
INSERT INTO "Customers" VALUES('BOLID','Bólido Comidas preparadas','Martín Sommer','Owner','C/ Araquil, 67','Madrid',NULL,'28023','Spain','(91) 555 22 82','(91) 555 91 99');
INSERT INTO "Customers" VALUES('BONAP','Bon app''','Laurence Lebihan','Owner','12, rue des Bouchers','Marseille',NULL,'13008','France','91.24.45.40','91.24.45.41');
INSERT INTO "Customers" VALUES('BOTTM','Bottom-Dollar Markets','Elizabeth Lincoln','Accounting Manager','23 Tsawassen Blvd.','Tsawassen','BC','T2F 8M4','Canada','(604) 555-4729','(604) 555-3745');

Run the following query:

SELECT NOT EXISTS (SELECT *
 FROM "Customers" AS c
   WHERE NOT (EXISTS (SELECT *
 FROM "Customers" AS c0
   WHERE EXISTS (SELECT *
 FROM "Customers" AS c1
   WHERE c."CustomerID" = c1."CustomerID"))));

The query hangs. There seems to be a deadlock.

Additional context
What was the impact?

This issue affects efcore.pg tests

@fqazi @rafiss

Jira issue: CRDB-30396

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

[yuzefovich]

This is a dup of #105882 which has been fixed on master (and I'll push the backports over the finish line).