internal error: aggregate function is not allowed in this context in `buildAggregateFunction()`->`endAggFunc()`

[ycybfhb]

Describe the problem

internal error: aggregate function is not allowed in this context in buildAggregateFunction()->endAggFunc()

To Reproduce

SQL to init database

[init.sql.txt](https://github.com/user-attachments/files/16941629/init.sql.txt)

SQL that causes error

[error.sql.txt](https://github.com/user-attachments/files/16943136/error.sql.txt)

Expected behavior

Expect no crashes

Additional data / screenshots

Here is the crash stack trace:

Hook early check failed: Database execute error: XX000, ERROR: internal error: aggregate function is not allowed in this context
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:723: endAggFunc()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/groupby.go:746: buildAggregateFunction()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:1311: replaceAggregate()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:1092: VisitPre()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/walk.go:834: WalkExpr()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:433: walkExprTree()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:472: resolveType()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/project.go:179: analyzeSelectList()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/project.go:95: analyzeProjectionList()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1255: buildSelectClause()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1176: buildSelectStmtWithoutParens()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1128: func1()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/with.go:116: processWiths()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1127: buildSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:360: buildStmt()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/with.go:198: buildCTE()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/with.go:143: buildCTEs()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/with.go:113: processWiths()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1127: buildSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:360: buildStmt()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:299: buildStmtAtRootWithScope()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:280: buildStmtAtRoot()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/explain.go:31: buildExplain()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:393: buildStmt()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:299: buildStmtAtRootWithScope()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:280: buildStmtAtRoot()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:259: Build()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:599: buildExecMemo()
github.com/cockroachdb/cockroach/pkg/sql/plan_opt.go:250: makeOptimizerPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:2363: makeExecPlan()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1833: dispatchToExecutionEngine()
github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1166: execStmtInOpenState()
;
Error while executing the query
extra info: cost: 690ms

Environment:

• CockroachDB version: v24.1.0
• Server OS: Linux a002 6.5.0-18-generic #18~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Wed Feb 7 11:40:03 UTC 2 x86_64 x86_64 x86_64 GNU/Linux
• Client app: postgresql odbc/psql

docker compose file:

version: '3.5'

services:
  crdb1:
    image: cockroachdb/cockroach:v24.1.0
    command: "start --advertise-addr=crdb1:26357 --http-addr=crdb1:8080 --listen-addr=crdb1:26357 --sql-addr=crdb1:26257 --insecure --join=crdb1:26357,crdb2:26357,crdb3:26357"
    volumes:
      - "crdb1_data:/cockroach/cockroach-data"
    networks:
      crnet:
        ipv4_address: 10.0.5.11

  crdb2:
    image: cockroachdb/cockroach:v24.1.0
    command: "start --advertise-addr=crdb2:26357 --http-addr=crdb2:8080 --listen-addr=crdb2:26357 --sql-addr=crdb2:26257 --insecure --join=crdb1:26357,crdb2:26357,crdb3:26357"
    volumes:
      - "crdb2_data:/cockroach/cockroach-data"
    networks:
      crnet:
        ipv4_address: 10.0.5.12

  crdb3:
    image: cockroachdb/cockroach:v24.1.0
    command: "start --advertise-addr=crdb3:26357 --http-addr=crdb3:8080 --listen-addr=crdb3:26357 --sql-addr=crdb3:26257 --insecure --join=crdb1:26357,crdb2:26357,crdb3:26357"
    volumes:
      - "crdb3_data:/cockroach/cockroach-data"
    networks:
      crnet:
        ipv4_address: 10.0.5.13

  crdb_single:
    image: cockroachdb/cockroach:v24.1.0

    command: start-single-node --insecure
    volumes:
      - "crdb_single_data:/cockroach/cockroach-data"
    networks:
      crnet:
        ipv4_address: 10.0.5.21

volumes:
  crdb1_data:
  crdb2_data:
  crdb3_data:
  crdb_single_data:

networks:
  crnet:
    driver: bridge
    ipam:
      config:
        - subnet: 10.0.5.0/24
          gateway: 10.0.5.254

about us

We are the BASS team from the School of Cyber Science and Technology at Beihang University. Our main focus is on system software security, operating systems, and program analysis research, as well as the development of automated program testing frameworks for detecting software defects. Using our self-developed database vulnerability testing tool, we have identified the above-mentioned possible vulnerabilities that may lead to database crashes.

Jira issue: CRDB-42035

[blathers-crl[bot]]

Hi @ycybfhb, please add branch-* labels to identify which branch(es) this C-bug affects.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[blathers-crl[bot]]

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-queries (found keywords: Optimizer,plan)

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.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[mgartner]

Reduce reproduction:

CREATE TABLE ab (
  a INT PRIMARY KEY,
  b STRING
);

CREATE TABLE c (
  c INT PRIMARY KEY
);

SELECT pg_catalog.bool_or(
  (
    SELECT pg_catalog.shobj_description(0, b)::BOOL
    FROM ab
  )
)
FROM c;