blathers-crl[bot]
commented
2 months ago 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.
Closed ycybfhb closed 1 month ago
blathers-crl[bot]
commented
2 months ago 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]
commented
2 months ago 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:
If we have not gotten back to your issue within a few business days, you can try the following:
:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.
mgartner
commented
1 month ago @ycybfhb do you have a minimized reproduction available?
mgartner
commented
1 month ago I've reduced it a bit to:
CREATE TABLE t_y (
c_sc9oe0qu INT4,
c_b3l3rlrs INT4,
c_qokd INT4,
c_t BOOL,
c_t9r1kl9y6m INT4,
c_g9f9abs2 FLOAT4,
c_jqnxje42v INT4,
c_m50a STRING,
c_as7 FLOAT4,
c_l1 STRING
);
CREATE TABLE t_ow0g_ (c_i9hbg1 INT4, c_deesne_t STRING);
CREATE TABLE t_bnqfnwa (
c_rqlsk_aj INT4, c_veh INT4, c_ibhkt4ifda BOOL, c_hl3s8ojsmc BOOL, c_ts7n INT4, c_uuw5d832 STRING
);
CREATE TABLE public.t_rca (c5 INT4);
SELECT subq_0.c0,
CAST(
COALESCE(
CASE
WHEN (subq_0.c2 BETWEEN (SELECT NULL) AND ref_2.c_veh) THEN subq_1.c2
ELSE (SELECT NULL)
END,
subq_1.c0
) AS FLOAT4
),
CASE
WHEN (CAST((CAST(CAST(NULL AS OID) AS OID) <= CAST(CAST(NULL AS OID) AS OID)) AS BOOL))
THEN subq_1.c7
ELSE subq_1.c0
END,
ref_2.c_uuw5d832,
pg_catalog.json_strip_nulls(NULL),
(SELECT NULL),
subq_1.c3,
subq_1.c7,
ref_2.c_veh,
CASE
WHEN (
CASE
WHEN 0 BETWEEN (SELECT NULL) AND subq_1.c8 OR ((subq_1.c1 IS NULL)) THEN 0
ELSE subq_1.c8
END
IN (SELECT NULL)
)
THEN subq_1.c5
ELSE CASE
WHEN (
EXISTS(
SELECT NULL
FROM t_rca AS ref_7
WHERE EXISTS(
SELECT NULL
FROM t_y AS ref_8
WHERE subq_1.c4 BETWEEN subq_0.c4 AND ref_7.c5
OR (
CAST(
(
CAST(subq_1.c6 AS STRING) NOT LIKE CAST(ref_8.c_m50a AS STRING)
) AS BOOL
)
AND ref_2.c_hl3s8ojsmc
NOT IN (
SELECT CAST(
(
CAST(ref_9.c_uuw5d832 AS STRING)
!~ CAST(ref_9.c_uuw5d832 AS STRING)
) AS BOOL
)
FROM t_bnqfnwa AS ref_9
)
)
)
)
)
THEN subq_1.c8
ELSE subq_1.c8
END
END
FROM (
(
SELECT ref_0.c_ts7n AS c0,
ref_0.c_ibhkt4ifda,
ref_0.c_rqlsk_aj AS c2,
ref_0.c_rqlsk_aj,
1680898171 AS c4
FROM t_bnqfnwa AS ref_0
WHERE CAST(
(
CAST(CAST(NULL AS VOID) AS VOID)
IS DISTINCT FROM CAST(CAST(NULL AS UNKNOWN) AS UNKNOWN)
) AS BOOL
)
) AS subq_0
LEFT JOIN (
SELECT ref_1.c_g9f9abs2 AS c0,
ref_1.c_as7 AS c1,
ref_1.c_g9f9abs2 AS c2,
ref_1.c_l1 AS c3,
ref_1.c_b3l3rlrs AS c4,
ref_1.c_t9r1kl9y6m AS c5,
ref_1.c_m50a AS c6,
ref_1.c_as7 AS c7,
ref_1.c_qokd AS c8,
ref_1.c_t
FROM t_y AS ref_1
WHERE (
CAST(
(
CAST(CAST(NULL AS JSONB) AS JSONB) < CAST(CAST(NULL AS JSONB) AS JSONB)
) AS BOOL
)
OR ref_1.c_t9r1kl9y6m
NOT IN (
ref_1.c_t9r1kl9y6m,
CAST(NULL AS INT4),
ref_1.c_b3l3rlrs,
ref_1.c_sc9oe0qu,
ref_1.c_jqnxje42v
)
)
AND CAST(
(
CAST(CAST(NULL AS TIMESTAMPTZ) AS TIMESTAMPTZ)
> CAST(CAST(NULL AS DATE) AS DATE)
) AS BOOL
)
) AS subq_1 ON false
)
INNER JOIN t_bnqfnwa AS ref_2 ON true
UNION (
SELECT subq_2.c4,
subq_2.c1,
CASE
WHEN (
CAST(
(
CAST(subq_2.c8 AS INT8)
IS DISTINCT FROM CAST(CAST(NULL AS DECIMAL) AS DECIMAL)
) AS BOOL
)
)
THEN subq_2.c1
ELSE subq_2.c1
END,
CASE
WHEN (
CAST(
(
CAST(CAST(NULL AS RECORD) AS RECORD) != CAST(CAST(NULL AS RECORD) AS RECORD)
) AS BOOL
)
)
THEN subq_2.c0
ELSE (SELECT NULL)
END,
CAST(NULL AS JSONB),
CAST(COALESCE(CAST(subq_2.c8 AS INT8), CAST(subq_2.c4 AS INT8)) AS INT8),
subq_2.c6,
(SELECT NULL),
subq_2.c8,
subq_2.c8
FROM (
SELECT ref_10.c_deesne_t AS c0,
(SELECT NULL) AS c1,
0 AS c4,
ref_10.c_deesne_t AS c6,
ref_10.c_i9hbg1 AS c8
FROM t_ow0g_ AS ref_10
WHERE CAST(
(
CAST(CAST(NULL AS TIMESTAMP) AS TIMESTAMP)
= CAST(CAST(NULL AS TIMESTAMPTZ) AS TIMESTAMPTZ)
) AS BOOL
)
) AS subq_2
);Further reduced:
CREATE TABLE t (b BOOL, s STRING);
SELECT EXISTS(
SELECT 1
FROM t
WHERE EXISTS(
SELECT
FROM t AS t2
WHERE t2.b OR (t2.s = t2.s AND t1.b IN (SELECT b FROM t))
)
)
FROM t AS t1
UNION
SELECT true FROM t;I believe the problem is with the HoistJoinSubquery rule, which incorrectly produces a Project with columns from the left and right sides of semi- and anti-joins:
AFAICT this has been a bug for a long time. I think we might not have caught it simply because this rule fires rarely—other hoist rules are likely to fire before it has a chance to.
DrewKimball
commented
1 month ago We've addressed that sort of thing in other rules by adding a Project like this: https://github.com/cockroachdb/cockroach/blob/2cafdb3f7e13f5c76ad34aae6783eb40ddf5c5ab/pkg/sql/opt/norm/rules/decorrelate.opt#L383-L384
mgartner
commented
1 month ago We've addressed that sort of thing in other rules by adding a Project like this:
The HoistJoinSubquery rule is mostly written in a custom Go function, and it already was constructing a Project, just with the wrong columns. So the solution in #130981 is similar in principle, though not exactly the same.
Describe the problem
internal error: lists in SetPrivate are not all the same length in
buildSetProps()To Reproduce
SQL to init database
[init.sql.txt](https://github.com/user-attachments/files/16941629/init.sql.txt)SQL that causes error
```sql select subq_0.c0 as c0, cast(coalesce( case when (subq_0.c2 between (select c_rqlsk_aj from t_bnqfnwa order by c_rqlsk_aj limit 1 offset 6) and ref_2.c_veh) then subq_1.c2 else (select c11 from t_rca order by c11 limit 1 offset 13) end , subq_1.c0 ) as float4) as c1, case when (cast((cast(cast(null as oid) as oid) <= cast(cast(null as oid) as oid)) as bool)) then subq_1.c7 else subq_1.c0 end as c2, ref_2.c_uuw5d832 as c3, pg_catalog.json_strip_nulls( cast(cast(null as jsonb) as jsonb)) as c4, (select c4 from t_rca order by c4 limit 1 offset 3) as c5, subq_1.c3 as c6, subq_1.c7 as c7, ref_2.c_veh as c8, case when (case when ((1425492021 between (select c_i9hbg1 from t_ow0g_ order by c_i9hbg1 limit 1 offset 1) and subq_1.c8)) or ((subq_1.c1 is NULL)) then -1711630245 else subq_1.c8 end in ( select ref_6.c5 as c0 from t_rca as ref_6 where true order by c0 desc)) then subq_1.c5 else case when (EXISTS ( select ref_7.c4 as c0, ref_7.c9 as c1, ref_7.c2 as c2, (select c_p6f922bie from t_y order by c_p6f922bie limit 1 offset 5) as c3, ref_7.c7 as c4, ref_2.c_hl3s8ojsmc as c5, ref_7.c7 as c6, 411099603 as c7, ref_7.c1 as c8, ref_2.c_uuw5d832 as c9, subq_0.c2 as c10, ref_2.c_d38l4v64 as c11 from t_rca as ref_7 where (EXISTS ( select ref_8.c_jqnxje42v as c0, ref_8.c_hdnu9xzja as c1, ref_8.c_tq as c2, ref_8.c_ywed as c3, subq_0.c4 as c4, ref_8.c_ywed as c5, (select c_n5 from t_ow0g_ order by c_n5 limit 1 offset 3) as c6, ref_8.c_m50a as c7, subq_0.c0 as c8 from t_y as ref_8 where ((subq_1.c4 between subq_0.c4 and ref_7.c5)) or (((cast((cast(subq_1.c6 as text) NOT LIKE cast(ref_8.c_m50a as text)) as bool))) and ((ref_2.c_hl3s8ojsmc not in ( select (cast((cast(ref_9.c_uuw5d832 as text) !~ cast(ref_9.c_uuw5d832 as text)) as bool)) as c0 from t_bnqfnwa as ref_9 where (ref_9.c_uuw5d832 not like 'ylc%%3lc'))))) order by c0, c1, c2, c3, c4, c5, c6, c7, c8 desc)) limit 42)) then subq_1.c8 else subq_1.c8 end end as c9 from (((select ref_0.c_ts7n as c0, ref_0.c_ibhkt4ifda as c1, ref_0.c_rqlsk_aj as c2, ref_0.c_rqlsk_aj as c3, 1680898171 as c4 from t_bnqfnwa as ref_0 where (cast((cast(cast(null as void) as void) IS DISTINCT FROM cast(cast(null as unknown) as unknown)) as bool))) as subq_0 left outer join (select ref_1.c_g9f9abs2 as c0, ref_1.c_as7 as c1, ref_1.c_g9f9abs2 as c2, ref_1.c_l1 as c3, ref_1.c_b3l3rlrs as c4, ref_1.c_t9r1kl9y6m as c5, ref_1.c_m50a as c6, ref_1.c_as7 as c7, ref_1.c_qokd as c8, ref_1.c_t as c9 from t_y as ref_1 where (((cast((cast(cast(null as jsonb) as jsonb) < cast(cast(null as jsonb) as jsonb)) as bool))) or ((ref_1.c_t9r1kl9y6m not in ( ref_1.c_t9r1kl9y6m, cast(null as int4), ref_1.c_b3l3rlrs, ref_1.c_sc9oe0qu, ref_1.c_jqnxje42v)))) and ((cast((cast(cast(null as "timestamptz") as "timestamptz") > cast(cast(null as date) as date)) as bool)))) as subq_1 on (false)) inner join t_bnqfnwa as ref_2 on ((subq_0.c2 <> ( select subq_0.c0 as c0 from t_rca as ref_3 where (cast((cast(cast(null as "timestamptz") as "timestamptz") <= cast(cast(null as "timestamp") as "timestamp")) as bool)) union ( select subq_0.c2 as c0 from t_ow0g_ as ref_4 where (EXISTS ( select ref_2.c_d38l4v64 as c0, ref_4.c_gdyk as c1, ref_5.c_ui_l4yp as c2, ref_5.c_jqnxje42v as c3, (select c_ui_l4yp from t_y order by c_ui_l4yp limit 1 offset 3) as c4, ref_5.c_m50a as c5, ref_5.c_jt8ydrx7 as c6, ref_5.c_b3l3rlrs as c7, ref_5.c_b3l3rlrs as c8, (select c_gdyk from t_ow0g_ order by c_gdyk limit 1 offset 1) as c9, ref_5.c_g9f9abs2 as c10, ref_5.c_m50a as c11 from t_y as ref_5 where (ref_5.c_p6f922bie is not NULL) order by c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11 asc)) ) limit 1)))) where (cast((cast(cast(null as void) as void) IS DISTINCT FROM cast(cast(null as unknown) as unknown)) as bool)) union ( select subq_2.c4 as c0, subq_2.c1 as c1, case when (cast((cast(subq_2.c8 as int8) IS DISTINCT FROM cast(cast(null as "numeric") as "numeric")) as bool)) then subq_2.c1 else subq_2.c1 end as c2, case when (cast((cast(cast(null as record) as record) != cast(cast(null as record) as record)) as bool)) then subq_2.c0 else (select c_ca from t_y order by c_ca limit 1 offset 2) end as c3, cast(null as jsonb) as c4, cast(coalesce( cast(subq_2.c8 as int8), cast(subq_2.c4 as int8) ) as int8) as c5, subq_2.c6 as c6, (select c_vj_t3epb from t_ow0g_ order by c_vj_t3epb limit 1 offset 6) as c7, subq_2.c8 as c8, subq_2.c8 as c9 from (select ref_10.c_deesne_t as c0, (select c11 from t_rca order by c11 limit 1 offset 1) as c1, ref_10.c_gdyk as c2, ref_10.c_deesne_t as c3, -1603328148 as c4, ref_10.c_ie as c5, ref_10.c_deesne_t as c6, ref_10.c__sm6t8__m as c7, ref_10.c_i9hbg1 as c8, pg_catalog.set_config( cast(ref_10.c_gdyk as text), cast(ref_10.c_gdyk as text), cast((ref_10.c_gdyk like '%wwnhf') as bool)) as c9, ref_10.c__sm6t8__m as c10 from t_ow0g_ as ref_10 where (cast((cast(cast(null as "timestamp") as "timestamp") = cast(cast(null as "timestamptz") as "timestamptz")) as bool)) order by c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 asc) as subq_2 where (EXISTS ( select ref_11.c_ie as c0, ref_11.c_deesne_t as c1, ref_11.c_gdyk as c2, subq_2.c4 as c3, ref_11.c_deesne_t as c4, ref_11.c_gdyk as c5, ref_11.c_deesne_t as c6, pg_catalog.asind( cast(case when (cast((cast(null as "box2d") IS DISTINCT FROM cast(null as "box2d")) as bool)) then cast(null as float8) else 66.73 end as float8)) as c7, ref_11.c_ie as c8, subq_2.c1 as c9, ref_11.c_i9hbg1 as c10, ref_11.c_deesne_t as c11 from t_ow0g_ as ref_11 where true)) ) limit 137; ```Expected behavior
Expect no crashes
Additional data / screenshots
Here is the crash stack trace:
Environment:
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/Linuxdocker compose file:
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-42032