Unexpected hang on subquery

[DerZc]

Describe the problem

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

The following program hangs on latest commit version:

USE test;
DROP DATABASE IF EXISTS database61 CASCADE;
CREATE DATABASE database61;
USE database61;

SET CLUSTER SETTING diagnostics.reporting.enabled    = false;
SET CLUSTER SETTING diagnostics.reporting.send_crash_reports = false;
SET CLUSTER SETTING sql.metrics.statement_details.plan_collection.enabled = 'off';
SET CLUSTER SETTING sql.stats.automatic_collection.enabled = 'off';
SET CLUSTER SETTING timeseries.storage.enabled = 'off';
set experimental_enable_hash_sharded_indexes='on';

CREATE TABLE t1 (c0 INT);

UPSERT INTO t1 (c0) VALUES(1);
SELECT (SELECT COUNT(t1a.rowid) FROM t1 AS t1a WHERE ((t1.rowid) IN (SELECT MAX(t1.rowid) FROM t1))) FROM t1;

To Reproduce

What did you do? Describe in your own words.

Expected behavior
A clear and concise description of what you expected to happen.
No hang.

Environment:

• CockroachDB version 180af61
• Server OS: Ubuntu 22.04
• Client app CLI

Jira issue: CRDB-29254

[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 have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: DROP DATABASE)
• @cockroachdb/sql-queries (found keywords: plan)
• @dhartunian (found keywords: metrics)

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

[michae2]

Thank you for finding this issue, @DerZc!

More information: looks like this only reproduces using the vectorized engine.

# michae2@michae2-crl-mbp ~crdb % cockroach demo -e "SET statement_timeout = '10s'; SET vectorize = on; CREATE TABLE t1 (c0 INT); UPSERT INTO t1 (c0) VALUES (1); SELECT (SELECT COUNT(t1a.rowid) FROM t1 AS t1a WHERE ((t1.rowid) IN (SELECT MAX(t1.rowid) FROM t1))) FROM t1;"
SET
SET
CREATE TABLE
INSERT 0 1
ERROR: this query requires additional disk space: apply-join-disk: disk budget exceeded: 1048576 bytes requested, 104857600 currently allocated, 104857600 bytes in budget
SQLSTATE: 53100
Failed running "demo"

# michae2@michae2-crl-mbp 1 ~crdb % cockroach demo -e "SET statement_timeout = '10s'; SET vectorize = off; CREATE TABLE t1 (c0 INT); UPSERT INTO t1 (c0) VALUES (1); SELECT (SELECT COUNT(t1a.rowid) FROM t1 AS t1a WHERE ((t1.rowid) IN (SELECT MAX(t1.rowid) FROM t1))) FROM t1;"
SET
SET
CREATE TABLE
INSERT 0 1
  count
---------
      1
(1 row)

And I think this was introduced sometime in v22.1 (though maybe I'm conflating this with the vectorized engine being used by default)?

# michae2@michae2-crl-mbp 1 ~/old_crdbs % ./cockroach-v21.2.17.darwin-10.9-amd64/cockroach demo -e "SET statement_timeout = '10s'; SET vectorize = experimental_always; CREATE TABLE t1 (c0 INT); UPSERT INTO t1 (c0) VALUES (1); SELECT (SELECT COUNT(t1a.rowid) FROM t1 AS t1a WHERE ((t1.rowid) IN (SELECT MAX(t1.rowid) FROM t1))) FROM t1;"
  count
---------
      1
(1 row)

# michae2@michae2-crl-mbp ~/old_crdbs % ./cockroach-v22.1.20.darwin-10.9-amd64/cockroach demo -e "SET statement_timeout = '10s'; SET vectorize = on; CREATE TABLE t1 (c0 INT); UPSERT INTO t1 (c0) VALUES (1); SELECT (SELECT COUNT(t1a.rowid) FROM t1 AS t1a WHERE ((t1.rowid) IN (SELECT MAX(t1.rowid) FROM t1))) FROM t1;"
ERROR: this query requires additional disk space: in-mem temp storage: disk budget exceeded: 1048576 bytes requested, 104857600 currently allocated, 104857600 bytes in budget
SQLSTATE: 53100
Failed running "demo"

[michae2]

The plan looks correct to me:

[email protected]:26257/demoapp/defaultdb> EXPLAIN (OPT, VERBOSE) SELECT (SELECT COUNT(t1a.rowid) FROM t1 AS t1a WHERE ((t1outer.rowid) IN (SELECT MAX(t1inner.rowid) FROM t1 AS t1inner))) FROM t1 AS t1outer;
                                                                      info
-------------------------------------------------------------------------------------------------------------------------------------------------
  project
   ├── columns: count:15
   ├── stats: [rows=1]
   ├── cost: 69.175
   ├── distribution: us-east1
   ├── prune: (15)
   ├── group-by (hash)
   │    ├── columns: t1outer.rowid:2 count:14
   │    ├── grouping columns: t1outer.rowid:2
   │    ├── stats: [rows=1, distinct(2)=1, null(2)=0]
   │    ├── cost: 69.135
   │    ├── key: (2)
   │    ├── fd: (2)-->(14)
   │    ├── distribution: us-east1
   │    ├── left-join-apply
   │    │    ├── columns: t1outer.rowid:2 canary:16
   │    │    ├── stats: [rows=1, distinct(2)=1, null(2)=0]
   │    │    ├── cost: 69.065
   │    │    ├── distribution: us-east1
   │    │    ├── prune: (16)
   │    │    ├── interesting orderings: (+2)
   │    │    ├── scan t1 [as=t1outer]
   │    │    │    ├── columns: t1outer.rowid:2
   │    │    │    ├── stats: [rows=1, distinct(2)=1, null(2)=0]
   │    │    │    │   histogram(2)=  0          1
   │    │    │    │                <--- 878440277775843329
   │    │    │    ├── cost: 30.12
   │    │    │    ├── key: (2)
   │    │    │    ├── distribution: us-east1
   │    │    │    ├── prune: (2)
   │    │    │    ├── interesting orderings: (+2)
   │    │    │    └── unfiltered-cols: (1-4)
   │    │    ├── project
   │    │    │    ├── columns: canary:16
   │    │    │    ├── outer: (2)
   │    │    │    ├── stats: [rows=1]
   │    │    │    ├── cost: 38.885
   │    │    │    ├── fd: ()-->(16)
   │    │    │    ├── distribution: us-east1
   │    │    │    ├── prune: (16)
   │    │    │    ├── semi-join (cross)
   │    │    │    │    ├── outer: (2)
   │    │    │    │    ├── stats: [rows=1, distinct(2)=1, null(2)=0]
   │    │    │    │    ├── cost: 38.845
   │    │    │    │    ├── distribution: us-east1
   │    │    │    │    ├── scan t1 [as=t1a]
   │    │    │    │    │    ├── stats: [rows=1]
   │    │    │    │    │    ├── cost: 29.625
   │    │    │    │    │    ├── distribution: us-east1
   │    │    │    │    │    └── unfiltered-cols: (5-8)
   │    │    │    │    ├── scalar-group-by
   │    │    │    │    │    ├── columns: max:13
   │    │    │    │    │    ├── cardinality: [1 - 1]
   │    │    │    │    │    ├── stats: [rows=1, distinct(13)=1, null(13)=0]
   │    │    │    │    │    ├── cost: 9.15
   │    │    │    │    │    ├── key: ()
   │    │    │    │    │    ├── fd: ()-->(13)
   │    │    │    │    │    ├── distribution: us-east1
   │    │    │    │    │    ├── prune: (13)
   │    │    │    │    │    ├── scan t1,rev [as=t1inner]
   │    │    │    │    │    │    ├── columns: t1inner.rowid:10
   │    │    │    │    │    │    ├── limit: 1(rev)
   │    │    │    │    │    │    ├── stats: [rows=1]
   │    │    │    │    │    │    ├── cost: 9.11
   │    │    │    │    │    │    ├── key: ()
   │    │    │    │    │    │    ├── fd: ()-->(10)
   │    │    │    │    │    │    └── distribution: us-east1
   │    │    │    │    │    └── aggregations
   │    │    │    │    │         └── const-agg [as=max:13, outer=(10)]
   │    │    │    │    │              └── t1inner.rowid:10
   │    │    │    │    └── filters
   │    │    │    │         └── t1outer.rowid:2 = max:13 [outer=(2,13), constraints=(/2: (/NULL - ]; /13: (/NULL - ]), fd=(2)==(13), (13)==(2)]
   │    │    │    └── projections
   │    │    │         └── true [as=canary:16]
   │    │    └── filters (true)
   │    └── aggregations
   │         └── count [as=count:14, outer=(16)]
   │              └── canary:16
   └── projections
        └── count:14 [as=count:15, outer=(14)]
(80 rows)

The tricky thing about this apply join is that it contains a semi-join where the filter depends on a column from outside the apply join. Maybe the column, t1outer.rowid, isn't being passed into the left-apply-join correctly? Somehow causing an infinite loop?

[DrewKimball]

Looks like it's because the left input of the cross-join has no columns - the vectorized implementation doesn't expect this, and doesn't advance its state when outputting a batch. That's what causes the infinite loop.