Materialize/test/sqllogictest/transform/demand.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

mode cockroach

statement ok
CREATE TABLE t (a int);

statement ok
INSERT INTO t (a) VALUES (0), (1), (2), (3);

# Test that demand analysis does not result in a 1/0 error for column
# that is not demanded as a join output (column a from t).
# The output should have NO errors.
query I
select x from (select x, 1/a from (select 2 as x), t);
----
2
2
2
2

# A `dummy` used to occur in the following plan before putting an extra call to `ProjectionPushdown` after the `Demand`
# call in the physical optimizer, because at the time of the first `Demand` call, the column that is later dummied in
# the second call is still being used in a join constraint, but this join is then eliminated by `RedundantJoin`.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select
  case when (((false)
          and (true))
        and ((numrange(0,0)) -|- (case when (cast(null as mz_aclitem)) = (cast(null as mz_aclitem)) then numrange(0,0) else numrange(0,0) end
            )))
      and (((10::uint8) & (case when (TIMESTAMPTZ '2023-01-01 01:23:45+06') >= ((TIMESTAMPTZ '95143-12-31 23:59:59+06' + INTERVAL '167 MILLENNIUM')) then 2::uint8 else 2::uint8 end
            )) < (pg_catalog.mod(
          CAST(null::uint8 as uint8),
          CAST(null::uint8 as uint8)))) then mz_catalog.kafka_murmur2(
      CAST(cast('\xDEADBEEF' as bytea) as bytea)) else mz_catalog.kafka_murmur2(
      CAST(cast('\xDEADBEEF' as bytea) as bytea)) end
     as c0,
  (mz_unsafe.mz_avg_promotion(
      CAST(0::uint4 as uint4))) / (null::numeric) as c1,
  mz_catalog.try_parse_monotonic_iso8601_timestamp(
    CAST(pg_catalog.obj_description(
      CAST(mz_internal.aclitem_grantee(
        CAST(cast(null as aclitem) as aclitem)) as oid),
      CAST((('[]'::jsonb) -> (pg_catalog.session_user())) ->> (pg_catalog.pg_get_viewdef(
          CAST(case when ('{}'::map[text=>text]) ?| (array['a', 'b', null, '']::text[]) then null::oid else null::oid end
             as oid),
          CAST(true as bool))) as text)) as text)) as c2,
  '2024-12-18 12:54:29.994+00'::timestamptz as c3
from
  (select distinct
        mz_catalog.map_agg(
          CAST(cast(coalesce(null::text,
            null::text) as text) as text),
          null) as c0,
        mz_catalog.mz_environment_id() as c1,
        (mz_catalog.mz_environment_id()) || ((null::uint4) + (4294967295::uint4)) as c2,
        pg_catalog.tstzrange(
          CAST((INTERVAL '2147483647 MONTHS') + (TIMESTAMPTZ '2023-01-01 01:23:45+06') as timestamptz),
          CAST(TIMESTAMPTZ '2023-01-01 01:23:45+06' as timestamptz)) as c3,
        pg_catalog.version() as c4
      from
        (select
              36 as c0,
              33 as c1
            from
              (select
                    4 as c0
                  from
                    "mz_catalog"."mz_columns" as ref_2
                  where (false) <> (true)
                  limit coalesce(13, 72)) as subq_0
            where (true) = (true)
            limit coalesce(82, 50)) as subq_1
      where (pg_catalog.mod(
          CAST(case when ((TIMESTAMPTZ '0001-01-01 00:00:00+06' - INTERVAL '4713 YEARS')) >= ((TIMESTAMPTZ '95143-12-31 23:59:59+06' + INTERVAL '167 MILLENNIUM')) then null::int2 else null::int2 end
             as int2),
          CAST(10::int2 as int2))) > (null::int2)
      limit coalesce(90, 42)) as subq_2
where true
limit coalesce(43, 120);
----
Explained Query:
  Finish limit=43 output=[#0..=#3]
    With
      cte l0 =
        Distinct project=[] // { arity: 0 }
          TopK limit=13 // { arity: 0 }
            Filter error("timestamp out of range") // { arity: 0 }
              Project () // { arity: 0 }
                ReadIndex on=mz_columns mz_columns_ind=[*** full scan ***] // { arity: 8 }
    Return // { arity: 4 }
      Map (833564499, null, null, 2024-12-18 12:54:29.994 UTC) // { arity: 4 }
        TopK limit=90 // { arity: 0 }
          Union // { arity: 0 }
            Get l0 // { arity: 0 }
            Negate // { arity: 0 }
              Get l0 // { arity: 0 }
            Constant // { arity: 0 }
              - ()

Used Indexes:
  - mz_catalog.mz_columns_ind (*** full scan ***)

Target cluster: mz_catalog_server

EOF

## -------------------- Tests for WITH MUTUALLY RECURSIVE --------------------

# Demand creates the `#0 + #0` from `#0 + #2`.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(key int, a int) AS (
    SELECT * FROM c2
    UNION
    SELECT a, a FROM t
  ),
  c1(key int, a int) AS (
    SELECT key + 1, -a FROM c0
  ),
  c2(key int, a int) AS (
    SELECT c0.key + c1.key, c0.a + c1.a
    FROM c0, c1
    WHERE c0.key = c1.key
  )
SELECT * FROM c2;
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Distinct project=[#0{a}, #1{a}] // { arity: 2 }
        Union // { arity: 2 }
          Get l1 // { arity: 2 }
          Project (#0{a}, #0{a}) // { arity: 2 }
            Filter (#0{a}) IS NOT NULL // { arity: 1 }
              ReadStorage materialize.public.t // { arity: 1 }
    cte l1 =
      Project (#4, #5) // { arity: 2 }
        Map ((#0{a} + #0{a}), (#1{a} + #3{a})) // { arity: 6 }
          Join on=(#0{a} = #2{key}) type=differential // { arity: 4 }
            implementation
              %0:l0[#0{key}]K » %1:l0[#0{key}]K
            ArrangeBy keys=[[#0{a}]] // { arity: 2 }
              Get l0 // { arity: 2 }
            ArrangeBy keys=[[#0{key}]] // { arity: 2 }
              Project (#2, #3) // { arity: 2 }
                Map ((#0{a} + 1), -(#1{a})) // { arity: 4 }
                  Get l0 // { arity: 2 }
  Return // { arity: 2 }
    Get l1 // { arity: 2 }

Source materialize.public.t
  filter=((#0{a}) IS NOT NULL)

Target cluster: quickstart

EOF

query II
WITH MUTUALLY RECURSIVE
  c0(key int, a int) AS (
    SELECT * FROM c2
    UNION
    SELECT a, a FROM t
  ),
  c1(key int, a int) AS (
    SELECT key + 1, -a FROM c0
  ),
  c2(key int, a int) AS (
    SELECT c0.key + c1.key, c0.a + c1.a
    FROM c0, c1
    WHERE c0.key = c1.key
  )
SELECT * FROM c2
ORDER BY 1, 2;
----
2  1
4  0
4  1
6  1
6  2
8  -3
8  -2