bug: internal error: entered unreachable code: CorrelatedInputRef CorrelatedInputRef { index: 3, correlated_id: 1 } has not been decorrelated'

[kwannoel]

Describe the bug

No response

To Reproduce

ddl

CREATE TABLE supplier (s_suppkey INT, s_name CHARACTER VARYING, s_address CHARACTER VARYING, s_nationkey INT, s_phone CHARACTER VARYING, s_acctbal NUMERIC, s_comment CHARACTER VARYING, PRIMARY KEY (s_suppkey));CREATE TABLE part (p_partkey INT, p_name CHARACTER VARYING, p_mfgr CHARACTER VARYING, p_brand CHARACTER VARYING, p_type CHARACTER VARYING, p_size INT, p_container CHARACTER VARYING, p_retailprice NUMERIC, p_comment CHARACTER VARYING, PRIMARY KEY (p_partkey));CREATE TABLE partsupp (ps_partkey INT, ps_suppkey INT, ps_availqty INT, ps_supplycost NUMERIC, ps_comment CHARACTER VARYING, PRIMARY KEY (ps_partkey, ps_suppkey));CREATE TABLE customer (c_custkey INT, c_name CHARACTER VARYING, c_address CHARACTER VARYING, c_nationkey INT, c_phone CHARACTER VARYING, c_acctbal NUMERIC, c_mktsegment CHARACTER VARYING, c_comment CHARACTER VARYING, PRIMARY KEY (c_custkey));CREATE TABLE orders (o_orderkey BIGINT, o_custkey INT, o_orderstatus CHARACTER VARYING, o_totalprice NUMERIC, o_orderdate DATE, o_orderpriority CHARACTER VARYING, o_clerk CHARACTER VARYING, o_shippriority INT, o_comment CHARACTER VARYING, PRIMARY KEY (o_orderkey));CREATE TABLE lineitem (l_orderkey BIGINT, l_partkey INT, l_suppkey INT, l_linenumber INT, l_quantity NUMERIC, l_extendedprice NUMERIC, l_discount NUMERIC, l_tax NUMERIC, l_returnflag CHARACTER VARYING, l_linestatus CHARACTER VARYING, l_shipdate DATE, l_commitdate DATE, l_receiptdate DATE, l_shipinstruct CHARACTER VARYING, l_shipmode CHARACTER VARYING, l_comment CHARACTER VARYING, PRIMARY KEY (l_orderkey, l_linenumber));CREATE TABLE nation (n_nationkey INT, n_name CHARACTER VARYING, n_regionkey INT, n_comment CHARACTER VARYING, PRIMARY KEY (n_nationkey));CREATE TABLE region (r_regionkey INT, r_name CHARACTER VARYING, r_comment CHARACTER VARYING, PRIMARY KEY (r_regionkey));CREATE TABLE person (id BIGINT, name CHARACTER VARYING, email_address CHARACTER VARYING, credit_card CHARACTER VARYING, city CHARACTER VARYING, state CHARACTER VARYING, date_time TIMESTAMP, extra CHARACTER VARYING, PRIMARY KEY (id));CREATE TABLE auction (id BIGINT, item_name CHARACTER VARYING, description CHARACTER VARYING, initial_bid BIGINT, reserve BIGINT, date_time TIMESTAMP, expires TIMESTAMP, seller BIGINT, category BIGINT, extra CHARACTER VARYING, PRIMARY KEY (id));CREATE TABLE bid (auction BIGINT, bidder BIGINT, price BIGINT, channel CHARACTER VARYING, url CHARACTER VARYING, date_time TIMESTAMP, extra CHARACTER VARYING);CREATE TABLE alltypes1 (c1 BOOLEAN, c2 SMALLINT, c3 INT, c4 BIGINT, c5 REAL, c6 DOUBLE, c7 NUMERIC, c8 DATE, c9 CHARACTER VARYING, c10 TIME, c11 TIMESTAMP, c13 INTERVAL, c14 STRUCT<a INT>, c15 INT[], c16 CHARACTER VARYING[]);CREATE TABLE alltypes2 (c1 BOOLEAN, c2 SMALLINT, c3 INT, c4 BIGINT, c5 REAL, c6 DOUBLE, c7 NUMERIC, c8 DATE, c9 CHARACTER VARYING, c10 TIME, c11 TIMESTAMP, c13 INTERVAL, c14 STRUCT<a INT>, c15 INT[], c16 CHARACTER VARYING[]);CREATE MATERIALIZED VIEW m0 AS SELECT (INTERVAL '86400') AS col_0, (SMALLINT '30287') & (SMALLINT '1') AS col_1, ARRAY[false, true, true, true] NOT IN (SELECT ARRAY[true, true, false, false, false, true, false, true, true, false, true, true, true, false, false, false, false, true, true, true, false, true, false, true, false, true, false, false, false, false, false, false, true, false, false, true, false, false, true, false, true, true, true, false, false, false, false, false, true, true, false, false, true, true, false, true, false, false, true, true, true, false] AS col_0 FROM auction AS t_1 GROUP BY t_1.id, t_1.expires, t_1.item_name, t_1.category, t_1.seller HAVING true) AS col_2 FROM lineitem AS t_0 GROUP BY t_0.l_tax, t_0.l_orderkey, t_0.l_shipdate, t_0.l_shipinstruct, t_0.l_shipmode, t_0.l_comment, t_0.l_receiptdate, t_0.l_quantity, t_0.l_linestatus, t_0.l_suppkey, t_0.l_partkey, t_0.l_returnflag, t_0.l_linenumber, t_0.l_discount;CREATE MATERIALIZED VIEW m1 AS SELECT t_0.n_regionkey AS col_0, (SMALLINT '-32768') AS col_1, TIME '08:13:19' AS col_2 FROM nation AS t_0 GROUP BY t_0.n_regionkey, t_0.n_name, t_0.n_comment HAVING true;CREATE MATERIALIZED VIEW m2 AS SELECT (INT '1987388134') + (SMALLINT '10467') AS col_0, (INT '1480684184') AS col_1, (INT '550518915') < (FLOAT '0') AS col_2, (INTERVAL '-945739') AS col_3 FROM auction AS t_0 GROUP BY t_0.date_time, t_0.initial_bid, t_0.expires, t_0.seller, t_0.description;CREATE MATERIALIZED VIEW m3 AS SELECT (FLOAT '87248334') AS col_0, (REAL '-2147483648') AS col_1 FROM m1 AS t_0 GROUP BY t_0.col_1;CREATE MATERIALIZED VIEW m4 AS SELECT t_0.extra AS col_0 FROM bid AS t_0 GROUP BY t_0.extra, t_0.bidder HAVING true;CREATE MATERIALIZED VIEW m5 AS SELECT t_0.c2 AS col_0, TIME '00:31:12' AS col_1, NULL AS col_2, t_0.c10 AS col_3 FROM alltypes2 AS t_0 GROUP BY t_0.c7, t_0.c9, t_0.c15, t_0.c10, t_0.c3, t_0.c6, t_0.c8, t_0.c13, t_0.c14, t_0.c11, t_0.c1, t_0.c16, t_0.c5, t_0.c4, t_0.c2;CREATE MATERIALIZED VIEW m6 AS SELECT false AS col_0, (SMALLINT '26751') AS col_1, (SMALLINT '1') AS col_2, (INT '1020799083') AS col_3 FROM m1 AS t_0 GROUP BY t_0.col_2 HAVING true;CREATE MATERIALIZED VIEW m7 AS SELECT (FLOAT '1687928200') AS col_0, (0) AS col_1 FROM auction AS t_0 GROUP BY t_0.date_time, t_0.category, t_0.initial_bid, t_0.extra, t_0.id, t_0.reserve;CREATE MATERIALIZED VIEW m8 AS SELECT (INTERVAL '0') AS col_0 FROM bid AS t_0 GROUP BY t_0.price, t_0.url;CREATE MATERIALIZED VIEW m9 AS SELECT TIME '23:32:12' AS col_0 FROM tumble(auction, auction.expires, INTERVAL '166850') AS tumble_0 GROUP BY tumble_0.category, tumble_0.reserve HAVING (INTERVAL '0') >= TIME '00:32:11';

query

SET 
  QUERY_MODE TO DISTRIBUTED;
SELECT 
  1 AS col_1 
FROM 
  orders AS t_52 -- here
WHERE 
  EXISTS (
    SELECT 
      1 
    FROM 
      m8 AS t_70 -- here
    HAVING 
      max(DISTINCT false) FILTER(
        WHERE 
          (t_70.col_0 / t_52.o_totalprice) < (INTERVAL '0') -- here
          )
  );

Expected behavior

No response

Additional context

No response

[xiangjinwu]

Known issue #4762 that has not been fixed for a long time due to low usage of correlated column as part of filter 🥵

[xiangjinwu]

Need more fix than #4762, see the linked PR for details.