optbuilder: internal error: comparison overload not found (eq, unknown, unknown) in BuildSharedProps()

[ycybfhb]

Describe the problem

internal error: comparison overload not found (eq, unknown, unknown) in BuildSharedProps()

To Reproduce

SQL to init database

init.sql.txt

SQL that causes error

select  
    subq_0.c5 as c0, 
    subq_0.c3 as c1, 
    case when (EXISTS (
        select  
            ref_10.c_bxpz2g5 as c0, 
            subq_0.c4 as c1, 
            ref_10.c_sc9oe0qu as c2, 
            ref_9.c_hl3s8ojsmc as c3, 
            subq_0.c0 as c4, 
            subq_0.c2 as c5, 
            ref_10.c_jt8ydrx7 as c6, 
            ref_9.c_veh as c7, 
            ref_9.c_veh as c8, 
            ref_10.c_l1 as c9
          from 
            (t_bnqfnwa as ref_9
              left outer join t_y as ref_10
              on (ref_9.c_ibhkt4ifda = ref_10.c_tq ))
          where ((cast((cast(null as "varbit") < cast(null as "varbit")) as bool))) 
            and ((cast((cast(cast(null as "box2d") as "box2d") ~ cast(cast(null as "box2d") as "box2d")) as bool)))
          limit 81)) then subq_0.c4 else case when ((cast((cast(cast(null as "numeric") as "numeric") <= cast(cast(null as "numeric") as "numeric")) as bool))) 
          or ((subq_0.c5 <> ( 
            select  
                  subq_0.c5 as c0
                from 
                  t_ow0g_ as ref_11
                where (ref_11.c_deesne_t in (
                  select  
                      ref_12.c6 as c0
                    from 
                      t_rca as ref_12
                    where false))
              union
              (
              select  
                  subq_0.c5 as c0
                from 
                  t_bnqfnwa as ref_13
                where (cast((cast((select c3 from t_rca order by c3 limit 1 offset 3)
                       as int8) != cast(ref_13.c_veh as int8)) as bool))
              )
               limit 1))) then subq_0.c3 else (select c11 from t_rca order by c11 limit 1 offset 91)
           end
         end
       as c2, 
    subq_0.c0 as c3, 
    subq_0.c5 as c4, 
    -924636053 as c5, 
    subq_0.c0 as c6
  from 
    (select  
          ref_0.c_f53cae_5zm as c0, 
          ref_1.c_gdyk as c1, 
          ref_0.c_tq as c2, 
          ref_1.c_vj_t3epb as c3, 
          ref_0.c_as7 as c4, 
          pg_catalog.unknownrecv(
            cast(cast(null as pg_description) as pg_description)) as c5, 
          ref_0.c_ui_l4yp as c6, 
          ref_0.c_ywed as c7
        from 
          (t_y as ref_0
            full outer join t_ow0g_ as ref_1
            on ((cast((cast(cast(null as tsvector) as tsvector) IS NOT DISTINCT FROM cast(cast(null as tsvector) as tsvector)) as bool))))
        where (cast((cast(cast(null as oid) as oid) > cast(cast(null as oid) as oid)) as bool))
        limit 102) as subq_0
  where (EXISTS (
    select  
        ref_2.c5 as c0, 
        ref_2.c7 as c1, 
        case when (ref_2.c0 in (
            select  
                  ref_7.c_qokd as c0
                from 
                  t_y as ref_7
                where true
              union all
              (
              select distinct 
                  ref_8.c_veh as c0
                from 
                  t_bnqfnwa as ref_8
                where (164585496 not in (
                  ref_8.c_ts7n, ref_8.c_veh, ref_8.c_rqlsk_aj, ref_8.c_ts7n))
              ))) then pg_catalog.st_ashexewkb(
            cast(cast(null as "geography") as "geography")) else 'pfs8' end
           as c2, 
        case when (ref_3.c_bxpz2g5 is NULL) then cast(nullif(
            subq_0.c1, 
            ref_2.c1
            ) as text) else ref_2.c6 end
           as c3, 
        ref_3.c_ui_l4yp as c4, 
        ref_3.c_p6f922bie as c5, 
        subq_0.c3 as c6
      from 
        (t_rca as ref_2
          cross join t_y as ref_3
          )
      where (subq_0.c5 not in (
        select distinct 
              cast(null as unknown) as c0
            from 
              t_ow0g_ as ref_4
            where (cast((ref_4.c_deesne_t IS NOT DISTINCT FROM ref_4.c_deesne_t) as bool))
          union
          (
          select  
              cast(null as unknown) as c0
            from 
              (t_rca as ref_5
                cross join t_ow0g_ as ref_6
                )
            where true
          )))
      order by c0, c1, c2, c3, c4, c5, c6 asc))
union
(
select  
    pg_catalog.unknownrecv(
      cast(case when ((select c_hl3s8ojsmc from t_bnqfnwa order by c_hl3s8ojsmc limit 1 offset 5)
             is not NULL) then cast(null as pg_subscription) else cast(null as pg_subscription) end
         as pg_subscription)) as c0, 
    (select c_vj_t3epb from t_ow0g_ order by c_vj_t3epb limit 1 offset 6)
       as c1, 
    82.81 as c2, 
    (cast((cast(cast(null as "numeric") as "numeric") ^ cast(cast(null as "numeric") as "numeric")) as "numeric") is not NULL) as c3, 
    cast(null as unknown) as c4, 
    ref_14.c_rqlsk_aj as c5, 
    (cast((cast(case when (ref_14.c_rqlsk_aj < ( 
            select distinct 
                ref_15.c_n5 as c0
              from 
                t_ow0g_ as ref_15
              where (cast((cast(ref_15.c_deesne_t as text) >= cast(ref_15.c_deesne_t as text)) as bool))
              limit 1)) then cast(null as uuid) else cast(null as uuid) end
           as uuid) >= cast(cast(null as uuid) as uuid)) as bool)) as c6
  from 
    t_bnqfnwa as ref_14
  where (cast((cast(null as inet) <= cast(null as inet)) as bool))
);

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: comparison overload not found (eq, unknown, unknown)
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/opt/memo/logical_props_builder.go:1764: BuildSharedProps()
github.com/cockroachdb/cockroach/pkg/sql/opt/memo/logical_props_builder.go:1818: BuildSharedProps()
github.com/cockroachdb/cockroach/pkg/sql/opt/memo/logical_props_builder.go:1611: buildFiltersItemProps()
github.com/cockroachdb/cockroach/bazel-out/k8-opt/bin/pkg/sql/opt/memo/expr.og.go:9797: PopulateProps()
github.com/cockroachdb/cockroach/bazel-out/k8-opt/bin/pkg/sql/opt/norm/factory.og.go:13140: ConstructFiltersItem()
github.com/cockroachdb/cockroach/bazel-out/k8-opt/bin/pkg/sql/opt/norm/factory.og.go:818: ConstructSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1376: buildWhere()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1247: 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:363: buildStmt()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/subquery.go:226: buildSubquery()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/subquery.go:105: TypeCheck()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/type_check.go:1640: TypeCheck()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/type_check.go:340: TypeCheck()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/type_check.go:350: TypeCheckAndRequire()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:492: resolveAndRequireType()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/util.go:460: resolveAndBuildScalar()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1367: buildWhere()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1247: 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/union.go:30: buildUnionClause()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1180: 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()
;
Error while executing the query
extra info: cost: 565ms

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-42033

[blathers-crl]

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

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

[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: pg_)

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]

Initial reduction:

CREATE TABLE t_ow0g_ (c_deesne_t STRING);

 SELECT 1
   FROM (SELECT pg_catalog.unknownrecv(NULL) AS c5) AS subq_0
  WHERE EXISTS(
          SELECT NULL WHERE subq_0.c5 NOT IN (SELECT NULL FROM t_ow0g_ UNION (SELECT NULL))
        )
 UNION (SELECT NULL);

Here's the stack on master:

ERROR: internal error: comparison overload not found (eq, unknown, unknown)
SQLSTATE: XX000
DETAIL: stack trace:
github.com/cockroachdb/cockroach/pkg/sql/opt/memo/logical_props_builder.go:1772: BuildSharedProps()
github.com/cockroachdb/cockroach/pkg/sql/opt/memo/logical_props_builder.go:1826: BuildSharedProps()
github.com/cockroachdb/cockroach/pkg/sql/opt/memo/logical_props_builder.go:1619: buildFiltersItemProps()
github.com/cockroachdb/cockroach/bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/opt/memo/expr.og.go:9803: PopulateProps()
github.com/cockroachdb/cockroach/bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/opt/norm/factory.og.go:13246: ConstructFiltersItem()
github.com/cockroachdb/cockroach/bazel-out/darwin_arm64-fastbuild/bin/pkg/sql/opt/norm/factory.og.go:833: ConstructSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1369: buildWhere()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1240: buildSelectClause()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1169: buildSelectStmtWithoutParens()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1121: func1()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/with.go:116: processWiths()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1120: buildSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:359: buildStmt()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/subquery.go:226: buildSubquery()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/subquery.go:105: TypeCheck()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/type_check.go:350: TypeCheck()
github.com/cockroachdb/cockroach/pkg/sql/sem/tree/type_check.go:360: TypeCheckAndRequire()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/scope.go:492: resolveAndRequireType()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/util.go:460: resolveAndBuildScalar()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1360: buildWhere()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1240: buildSelectClause()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1169: buildSelectStmtWithoutParens()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1121: func1()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/with.go:116: processWiths()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1120: buildSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:356: buildStmt()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/union.go:30: buildUnionClause()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1173: buildSelectStmtWithoutParens()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1121: func1()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/with.go:116: processWiths()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/select.go:1120: buildSelect()
github.com/cockroachdb/cockroach/pkg/sql/opt/optbuilder/builder.go:356: buildStmt()

Looks like this has been around since v20.2. We've also seen similar bug reports. Marking as P-3.