Materialize/test/sqllogictest/advent-of-code/2023/aoc_1205.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.

# https://github.com/MaterializeInc/advent-of-code-2023/blob/main/week1/aoc_1205.md

mode cockroach

statement ok
CREATE TABLE input (input TEXT);

statement ok
INSERT INTO input VALUES (
'seeds: 141812878 853583433 69532151 734372491 182396959 4723992392 8947211973 5 4238233746 414976297 3674819199 51868842
<EMPTY_LINE>
seed-to-soil map:
47738968 98357 182795944
7626588292 7848955494 927588242
34324971 6812781938 837374212
2166766538 726174459 6843311291
239754864 684391 555585341
418284536 8839949654 642749254
9423585483 8225843682 329442345
8712565737 9399753119 5
74289536 9359574415 717691786
<EMPTY_LINE>
soil-to-fertilizer map:
8986393527 4888517941 585279262
7613871113 9877446651 84269233
2423271674 7586366221 7659569
3622861142 916381775 74546981
3996339781 3244535459 98765225
586791635 8419253759 59179897
7576358959 6127297299 519542837
7876479671 6556651697 2721518
8412917365 865866259 75627132
8919467753 2818783878 5
4729257824 8988777624 275397538
2626168725 5 375375337
7963626731 8731755354 562155822
4484955989 2 31166388
6394952326 9434564563 344217764
5818263571 8292867479 46133627
6635767943 663896613 39649384
1179179943 58595967 326966131
461712455 2756694296 566766331
267854195 942359332 92191415
1676162127 2415173728 7527572
8235458679 517558444 8481131
2193675972 5252589743 48214883
4312181391 5588755717 5725572
451918428 7726618552 39256967
4734828421 378251365 56849366
7864169188 795712941 852726511
6498165846 819955597 825591722
6737759128 1943367748 153632389
81836871 3619315963 5999745237
5533126476 1625724578 11196339
814358688 9878499637 982477257
4386394269 3737453299 68828912
1 535278815 55
9455234351 63518366 96697897
298142454 9711262844 545237968
6548387663 5498764299 556695254
4421182848 3813437218 18532535
<EMPTY_LINE>
fertilizer-to-water map:
5188185212 713592736 572122634
3996516993 8368372653 29257663
7629744559 8723951436 36722366
517117434 6799613578 62912912
7444887598 1378359775 142239913
8946622323 461231638 98648353
895954325 5555555 419161656
1269235415 8199792497 62639226
277191562 35368196 783296989
2994552458 6347418557 828619684
7 245618496 85983123
599564274 6975655213 441448691
7312274117 4916534261 32238555
444977452 8543586232 53382156
682172835 2567878724 62974419
8912874438 5539912916 51886594
4257175799 1517431879 97672792
9481282796 996563263 8713257
8269679713 2281488697 77222754
2566468873 4747453236 37516457
9491212658 1459322382 56814988
142239541 25962654 99568155
3475998687 9 537527222
7223664422 4413723656 2795999379
5781744284 5851593946 52411132
2615616119 2198338791 93894182
725796923 6293593866 672373647
5291123587 1 45165371
<EMPTY_LINE>
water-to-light map:
9483826148 971239373 57981779
866572267 5355262121 658481698
6149496513 2245492751 89485475
7743881746 5949772182 31781539
915838939 73797715 9628255536
49687174 729495672 53487321
1774464825 4513912578 89436881
734978995 62382679 39621782
8579142462 9 79959373
3999434125 1722538882 315735941
9758171681 5256942648 79846743
676724382 222319917 43462458
9842566327 2265547154 99978196
3885113248 6341877917 53731483
7451763582 1 22747236
2222665944 8558249644 858131997
2222613482 1369627666 23224913
2122417343 892313426 54167146
9888331148 1128482359 94371883
55684927 195154981 59748169
8819525361 7614218117 73293139
241798649 664138941 854619722
9716684718 3664657195 253795579
3431191398 9414159725 8345323
4742633341 757873782 42893665
5976897431 6681469458 1532556
5 947566478 61872123
8793878654 8231529123 34617151
136924369 6845534171 1385592
538695647 894877235 85583498
3327525594 1748821684 434778753
<EMPTY_LINE>
light-to-temperature map:
6778296876 1588184826 9373785
5958282911 253795579 867819861
422679395 2761173364 193474551
253795579 4218627316 4
5652112248 987641937 721917256
2349984397 7942568282 71771239
8969933137 5781654773 394652
1898117348 8342374669 537694616
4219243162 8214944547 736996395
5912961492 8868498531 27622883
346958533 4173157962 51979735
1916631453 9117978288 18581458
2945679922 4497199716 5152578
9179535252 5162391577 549347823
<EMPTY_LINE>
temperature-to-humidity map:
7239471736 657945134 92565479
4691325348 8569173453 532754925
5919891224 6397216467 3372871
3822197491 2782624568 698583215
6125121482 639986598 98215788
6574396598 665121945 619454968
2412529992 2776226287 1626339
2272785524 417214458 621918434
5629812155 68588342 911161561
9324482921 2524842229 67458368
5932612196 2357165667 93374591
1274118925 4785728782 685125685
4352611699 7119321598 69449264
6387566129 5474547438 5352628
3928922836 7972535629 916644549
1662726326 6558668448 362791743
836597248 451582864 36438897
738226654 681268313 785154987
9563614959 8661586162 448483715
9984139884 5113486123 19999477
2755987212 2214749752 872249174
887416884 6486692479 38549881
<EMPTY_LINE>
humidity-to-location map:
858893435 311438446 562265762
2454586869 3679665416 225353643
9519322712 63232264 128625398
441771557 9 2371977356
5 8193625898 775413179
1952973915 6235154499 759143921
6572 58451 75391174
6941443142 589862612 261347316');

statement ok
UPDATE input SET input = replace(input, '<EMPTY_LINE>', '');

query I
WITH seeds AS (
    SELECT
        regexp_split_to_table(
            regexp_split_to_array(
                regexp_split_to_array(input, '\n')[1],
                ': '
            )[2],
            ' '
        )::bigint AS seed
    FROM
        input
),
seed_to_soil_lines AS (
    SELECT
        regexp_split_to_array(
            regexp_split_to_table(
                regexp_match(input, 'seed-to-soil map:\n([0-9 \n]*?)\n\n')[1],
                '\n'
            ),
            ' '
        )::bigint[] AS line
    FROM
        input
),
seed_to_soil AS (
    SELECT
        line[1] AS dst_base,
        line[2] AS src_base,
        line[3] AS len
    FROM
        seed_to_soil_lines
),
soil_to_fertilizer_lines AS (
    SELECT
        regexp_split_to_array(
            regexp_split_to_table(
                regexp_match(input, 'soil-to-fertilizer map:\n([0-9 \n]*?)\n\n')[1],
                '\n'
            ),
            ' '
        )::bigint[] AS line
    FROM
        input
),
soil_to_fertilizer AS (
    SELECT
        line[1] AS dst_base,
        line[2] AS src_base,
        line[3] AS len
    FROM
        soil_to_fertilizer_lines
),
fertilizer_to_water_lines AS (
    SELECT
        regexp_split_to_array(
            regexp_split_to_table(
                regexp_match(input, 'fertilizer-to-water map:\n([0-9 \n]*?)\n\n')[1],
                '\n'
            ),
            ' '
        )::bigint[] AS line
    FROM
        input
),
fertilizer_to_water AS (
    SELECT
        line[1] AS dst_base,
        line[2] AS src_base,
        line[3] AS len
    FROM
        fertilizer_to_water_lines
),
water_to_light_lines AS (
    SELECT
        regexp_split_to_array(
            regexp_split_to_table(
                regexp_match(input, 'water-to-light map:\n([0-9 \n]*?)\n\n')[1],
                '\n'
            ),
            ' '
        )::bigint[] AS line
    FROM
        input
),
water_to_light AS (
    SELECT
        line[1] AS dst_base,
        line[2] AS src_base,
        line[3] AS len
    FROM
        water_to_light_lines
),
light_to_temperature_lines AS (
    SELECT
        regexp_split_to_array(
            regexp_split_to_table(
                regexp_match(input, 'light-to-temperature map:\n([0-9 \n]*?)\n\n')[1],
                '\n'
            ),
            ' '
        )::bigint[] AS line
    FROM
        input
),
light_to_temperature AS (
    SELECT
        line[1] AS dst_base,
        line[2] AS src_base,
        line[3] AS len
    FROM
        light_to_temperature_lines
),
temperature_to_humidity_lines AS (
    SELECT
        regexp_split_to_array(
            regexp_split_to_table(
                regexp_match(input, 'temperature-to-humidity map:\n([0-9 \n]*?)\n\n')[1],
                '\n'
            ),
            ' '
        )::bigint[] AS line
    FROM
        input
),
temperature_to_humidity AS (
    SELECT
        line[1] AS dst_base,
        line[2] AS src_base,
        line[3] AS len
    FROM
        temperature_to_humidity_lines
),
humidity_to_location_lines AS (
    SELECT
        regexp_split_to_array(
            regexp_split_to_table(
                regexp_match(input, 'humidity-to-location map:\n([0-9 \n]*)')[1],
                '\n'
            ),
            ' '
        )::bigint[] AS line
    FROM
        input
),
humidity_to_location AS (
    SELECT
        line[1] AS dst_base,
        line[2] AS src_base,
        line[3] AS len
    FROM
        humidity_to_location_lines
),
soil AS (
    SELECT
        seed,
        coalesce(
            MAX(
                CASE
                    WHEN seed >= src_base AND seed < src_base + len
                    THEN dst_base + (seed - src_base)
                    ELSE null
                END
            ),
            seed
        ) AS soil
    FROM
        seeds, seed_to_soil
    GROUP BY seed
),
fertilizer AS (
    SELECT
        soil,
        coalesce(
            MAX(
                CASE
                    WHEN soil >= src_base AND soil < src_base + len
                    THEN dst_base + (soil - src_base)
                    ELSE null
                END
            ),
            soil
        ) AS fertilizer
    FROM
        soil, soil_to_fertilizer
    GROUP BY soil
),
water AS (
    SELECT
        fertilizer,
        coalesce(
            MAX(
                CASE
                    when fertilizer >= src_base AND fertilizer < src_base + len
                    then dst_base + (fertilizer - src_base)
                    else null
                END
            ),
            fertilizer
        ) AS water
    FROM
        fertilizer, fertilizer_to_water
    GROUP BY fertilizer
),
light AS (
    SELECT
        water,
        coalesce(
            MAX(
                CASE
                    WHEN water >= src_base AND water < src_base + len
                    THEN dst_base + (water - src_base)
                    ELSE null
                END
            ),
            water
        ) AS light
    FROM
        water, water_to_light
    GROUP BY water
),
temperature AS (
    SELECT
        light,
        coalesce(
            MAX(
                CASE
                    WHEN light >= src_base AND light < src_base + len
                    THEN dst_base + (light - src_base)
                    ELSE null
                END
            ),
            light
        ) AS temperature
    FROM
        light, light_to_temperature
    GROUP BY light
),
humidity AS (
    SELECT
        temperature,
        coalesce(
            MAX(
                CASE
                    WHEN temperature >= src_base AND temperature < src_base + len
                    THEN dst_base + (temperature - src_base)
                    ELSE null
                END
            ),
            temperature
        ) AS humidity
    FROM
        temperature, temperature_to_humidity
    GROUP BY temperature
),
location AS (
    SELECT
        humidity,
        coalesce(
            MAX(
                CASE
                    WHEN humidity >= src_base AND humidity < src_base + len
                    THEN dst_base + (humidity - src_base)
                    ELSE null
                END
            ),
            humidity
        ) AS location
    FROM
        humidity, humidity_to_location
    GROUP BY humidity
)
SELECT
    MIN(location) AS answer
FROM
    location;
----
2364832465

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
WITH seeds AS (
            SELECT
                regexp_split_to_table(
                    regexp_split_to_array(
                        regexp_split_to_array(input, '\n')[1],
                        ': '
                    )[2],
                    ' '
                )::bigint AS seed
            FROM
                input
        ),
        seed_to_soil_lines AS (
            SELECT
                regexp_split_to_array(
                    regexp_split_to_table(
                        regexp_match(input, 'seed-to-soil map:\n([0-9 \n]*?)\n\n')[1],
                        '\n'
                    ),
                    ' '
                )::bigint[] AS line
            FROM
                input
        ),
        seed_to_soil AS (
            SELECT
                line[1] AS dst_base,
                line[2] AS src_base,
                line[3] AS len
            FROM
                seed_to_soil_lines
        ),
        soil_to_fertilizer_lines AS (
            SELECT
                regexp_split_to_array(
                    regexp_split_to_table(
                        regexp_match(input, 'soil-to-fertilizer map:\n([0-9 \n]*?)\n\n')[1],
                        '\n'
                    ),
                    ' '
                )::bigint[] AS line
            FROM
                input
        ),
        soil_to_fertilizer AS (
            SELECT
                line[1] AS dst_base,
                line[2] AS src_base,
                line[3] AS len
            FROM
                soil_to_fertilizer_lines
        ),
        fertilizer_to_water_lines AS (
            SELECT
                regexp_split_to_array(
                    regexp_split_to_table(
                        regexp_match(input, 'fertilizer-to-water map:\n([0-9 \n]*?)\n\n')[1],
                        '\n'
                    ),
                    ' '
                )::bigint[] AS line
            FROM
                input
        ),
        fertilizer_to_water AS (
            SELECT
                line[1] AS dst_base,
                line[2] AS src_base,
                line[3] AS len
            FROM
                fertilizer_to_water_lines
        ),
        water_to_light_lines AS (
            SELECT
                regexp_split_to_array(
                    regexp_split_to_table(
                        regexp_match(input, 'water-to-light map:\n([0-9 \n]*?)\n\n')[1],
                        '\n'
                    ),
                    ' '
                )::bigint[] AS line
            FROM
                input
        ),
        water_to_light AS (
            SELECT
                line[1] AS dst_base,
                line[2] AS src_base,
                line[3] AS len
            FROM
                water_to_light_lines
        ),
        light_to_temperature_lines AS (
            SELECT
                regexp_split_to_array(
                    regexp_split_to_table(
                        regexp_match(input, 'light-to-temperature map:\n([0-9 \n]*?)\n\n')[1],
                        '\n'
                    ),
                    ' '
                )::bigint[] AS line
            FROM
                input
        ),
        light_to_temperature AS (
            SELECT
                line[1] AS dst_base,
                line[2] AS src_base,
                line[3] AS len
            FROM
                light_to_temperature_lines
        ),
        temperature_to_humidity_lines AS (
            SELECT
                regexp_split_to_array(
                    regexp_split_to_table(
                        regexp_match(input, 'temperature-to-humidity map:\n([0-9 \n]*?)\n\n')[1],
                        '\n'
                    ),
                    ' '
                )::bigint[] AS line
            FROM
                input
        ),
        temperature_to_humidity AS (
            SELECT
                line[1] AS dst_base,
                line[2] AS src_base,
                line[3] AS len
            FROM
                temperature_to_humidity_lines
        ),
        humidity_to_location_lines AS (
            SELECT
                regexp_split_to_array(
                    regexp_split_to_table(
                        regexp_match(input, 'humidity-to-location map:\n([0-9 \n]*)')[1],
                        '\n'
                    ),
                    ' '
                )::bigint[] AS line
            FROM
                input
        ),
        humidity_to_location AS (
            SELECT
                line[1] AS dst_base,
                line[2] AS src_base,
                line[3] AS len
            FROM
                humidity_to_location_lines
        ),
        soil AS (
            SELECT
                seed,
                coalesce(
                    MAX(
                        CASE
                            WHEN seed >= src_base AND seed < src_base + len
                            THEN dst_base + (seed - src_base)
                            ELSE null
                        END
                    ),
                    seed
                ) AS soil
            FROM
                seeds, seed_to_soil
            GROUP BY seed
        ),
        fertilizer AS (
            SELECT
                soil,
                coalesce(
                    MAX(
                        CASE
                            WHEN soil >= src_base AND soil < src_base + len
                            THEN dst_base + (soil - src_base)
                            ELSE null
                        END
                    ),
                    soil
                ) AS fertilizer
            FROM
                soil, soil_to_fertilizer
            GROUP BY soil
        ),
        water AS (
            SELECT
                fertilizer,
                coalesce(
                    MAX(
                        CASE
                            when fertilizer >= src_base AND fertilizer < src_base + len
                            then dst_base + (fertilizer - src_base)
                            else null
                        END
                    ),
                    fertilizer
                ) AS water
            FROM
                fertilizer, fertilizer_to_water
            GROUP BY fertilizer
        ),
        light AS (
            SELECT
                water,
                coalesce(
                    MAX(
                        CASE
                            WHEN water >= src_base AND water < src_base + len
                            THEN dst_base + (water - src_base)
                            ELSE null
                        END
                    ),
                    water
                ) AS light
            FROM
                water, water_to_light
            GROUP BY water
        ),
        temperature AS (
            SELECT
                light,
                coalesce(
                    MAX(
                        CASE
                            WHEN light >= src_base AND light < src_base + len
                            THEN dst_base + (light - src_base)
                            ELSE null
                        END
                    ),
                    light
                ) AS temperature
            FROM
                light, light_to_temperature
            GROUP BY light
        ),
        humidity AS (
            SELECT
                temperature,
                coalesce(
                    MAX(
                        CASE
                            WHEN temperature >= src_base AND temperature < src_base + len
                            THEN dst_base + (temperature - src_base)
                            ELSE null
                        END
                    ),
                    temperature
                ) AS humidity
            FROM
                temperature, temperature_to_humidity
            GROUP BY temperature
        ),
        location AS (
            SELECT
                humidity,
                coalesce(
                    MAX(
                        CASE
                            WHEN humidity >= src_base AND humidity < src_base + len
                            THEN dst_base + (humidity - src_base)
                            ELSE null
                        END
                    ),
                    humidity
                ) AS location
            FROM
                humidity, humidity_to_location
            GROUP BY humidity
        )
        SELECT
            MIN(location) AS answer
        FROM
            location;
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[min(coalesce(#1{max}, #0{humidity}))] // { arity: 1 }
        Reduce group_by=[#0] aggregates=[max(case when ((#0{humidity} < (#2{src_base} + #3{len})) AND (#0{humidity} >= #2{src_base})) then (#1{dst_base} + (#0{humidity} - #2{src_base})) else null end)] // { arity: 2 }
          CrossJoin type=differential // { arity: 4 }
            implementation
              %0[×] » %1[×]
            ArrangeBy keys=[[]] // { arity: 1 }
              Project (#2) // { arity: 1 }
                Map (coalesce(#1{max}, #0{temperature})) // { arity: 3 }
                  Reduce group_by=[#0] aggregates=[max(case when ((#0{temperature} < (#2{src_base} + #3{len})) AND (#0{temperature} >= #2{src_base})) then (#1{dst_base} + (#0{temperature} - #2{src_base})) else null end)] // { arity: 2 }
                    CrossJoin type=differential // { arity: 4 }
                      implementation
                        %0[×] » %1[×]
                      ArrangeBy keys=[[]] // { arity: 1 }
                        Project (#2) // { arity: 1 }
                          Map (coalesce(#1{max}, #0{light})) // { arity: 3 }
                            Reduce group_by=[#0] aggregates=[max(case when ((#0{light} < (#2{src_base} + #3{len})) AND (#0{light} >= #2{src_base})) then (#1{dst_base} + (#0{light} - #2{src_base})) else null end)] // { arity: 2 }
                              CrossJoin type=differential // { arity: 4 }
                                implementation
                                  %0[×] » %1[×]
                                ArrangeBy keys=[[]] // { arity: 1 }
                                  Project (#2) // { arity: 1 }
                                    Map (coalesce(#1{max}, #0{water})) // { arity: 3 }
                                      Reduce group_by=[#0] aggregates=[max(case when ((#0{water} < (#2{src_base} + #3{len})) AND (#0{water} >= #2{src_base})) then (#1{dst_base} + (#0{water} - #2{src_base})) else null end)] // { arity: 2 }
                                        CrossJoin type=differential // { arity: 4 }
                                          implementation
                                            %0[×] » %1[×]
                                          ArrangeBy keys=[[]] // { arity: 1 }
                                            Project (#2) // { arity: 1 }
                                              Map (coalesce(#1{max}, #0{fertilizer})) // { arity: 3 }
                                                Reduce group_by=[#0] aggregates=[max(case when ((#0{fertilizer} < (#2{src_base} + #3{len})) AND (#0{fertilizer} >= #2{src_base})) then (#1{dst_base} + (#0{fertilizer} - #2{src_base})) else null end)] // { arity: 2 }
                                                  CrossJoin type=differential // { arity: 4 }
                                                    implementation
                                                      %0[×] » %1[×]
                                                    ArrangeBy keys=[[]] // { arity: 1 }
                                                      Project (#2) // { arity: 1 }
                                                        Map (coalesce(#1{max}, #0{soil})) // { arity: 3 }
                                                          Reduce group_by=[#0] aggregates=[max(case when ((#0{soil} < (#2{src_base} + #3{len})) AND (#0{soil} >= #2{src_base})) then (#1{dst_base} + (#0{soil} - #2{src_base})) else null end)] // { arity: 2 }
                                                            CrossJoin type=differential // { arity: 4 }
                                                              implementation
                                                                %0[×] » %1[×]
                                                              ArrangeBy keys=[[]] // { arity: 1 }
                                                                Project (#2) // { arity: 1 }
                                                                  Map (coalesce(#1{max}, #0{seed})) // { arity: 3 }
                                                                    Reduce group_by=[#0] aggregates=[max(case when ((#0{seed} < (#2{src_base} + #3{len})) AND (#0{seed} >= #2{src_base})) then (#1{dst_base} + (#0{seed} - #2{src_base})) else null end)] // { arity: 2 }
                                                                      CrossJoin type=differential // { arity: 4 }
                                                                        implementation
                                                                          %0[×] » %1[×]
                                                                        ArrangeBy keys=[[]] // { arity: 1 }
                                                                          Project (#2) // { arity: 1 }
                                                                            Map (text_to_bigint(#1{unnest})) // { arity: 3 }
                                                                              FlatMap unnest_array(regexp_split_to_array[" ", case_insensitive=false](array_index(regexp_split_to_array[": ", case_insensitive=false](array_index(regexp_split_to_array["\n", case_insensitive=false](#0{input}), 1)), 2))) // { arity: 2 }
                                                                                ReadStorage materialize.public.input // { arity: 1 }
                                                                        ArrangeBy keys=[[]] // { arity: 3 }
                                                                          Project (#3..=#5) // { arity: 3 }
                                                                            Map (arraytoarray(regexp_split_to_array[" ", case_insensitive=false](#1{unnest})), array_index(#2{line}, 1), array_index(#2{line}, 2), array_index(#2{line}, 3)) // { arity: 6 }
                                                                              FlatMap unnest_array(regexp_split_to_array["\n", case_insensitive=false](array_index(regexp_match["seed-to-soil map:\n([0-9 \n]*?)\n\n", case_insensitive=false](#0{input}), 1))) // { arity: 2 }
                                                                                ReadStorage materialize.public.input // { arity: 1 }
                                                              ArrangeBy keys=[[]] // { arity: 3 }
                                                                Project (#3..=#5) // { arity: 3 }
                                                                  Map (arraytoarray(regexp_split_to_array[" ", case_insensitive=false](#1{unnest})), array_index(#2{line}, 1), array_index(#2{line}, 2), array_index(#2{line}, 3)) // { arity: 6 }
                                                                    FlatMap unnest_array(regexp_split_to_array["\n", case_insensitive=false](array_index(regexp_match["soil-to-fertilizer map:\n([0-9 \n]*?)\n\n", case_insensitive=false](#0{input}), 1))) // { arity: 2 }
                                                                      ReadStorage materialize.public.input // { arity: 1 }
                                                    ArrangeBy keys=[[]] // { arity: 3 }
                                                      Project (#3..=#5) // { arity: 3 }
                                                        Map (arraytoarray(regexp_split_to_array[" ", case_insensitive=false](#1{unnest})), array_index(#2{line}, 1), array_index(#2{line}, 2), array_index(#2{line}, 3)) // { arity: 6 }
                                                          FlatMap unnest_array(regexp_split_to_array["\n", case_insensitive=false](array_index(regexp_match["fertilizer-to-water map:\n([0-9 \n]*?)\n\n", case_insensitive=false](#0{input}), 1))) // { arity: 2 }
                                                            ReadStorage materialize.public.input // { arity: 1 }
                                          ArrangeBy keys=[[]] // { arity: 3 }
                                            Project (#3..=#5) // { arity: 3 }
                                              Map (arraytoarray(regexp_split_to_array[" ", case_insensitive=false](#1{unnest})), array_index(#2{line}, 1), array_index(#2{line}, 2), array_index(#2{line}, 3)) // { arity: 6 }
                                                FlatMap unnest_array(regexp_split_to_array["\n", case_insensitive=false](array_index(regexp_match["water-to-light map:\n([0-9 \n]*?)\n\n", case_insensitive=false](#0{input}), 1))) // { arity: 2 }
                                                  ReadStorage materialize.public.input // { arity: 1 }
                                ArrangeBy keys=[[]] // { arity: 3 }
                                  Project (#3..=#5) // { arity: 3 }
                                    Map (arraytoarray(regexp_split_to_array[" ", case_insensitive=false](#1{unnest})), array_index(#2{line}, 1), array_index(#2{line}, 2), array_index(#2{line}, 3)) // { arity: 6 }
                                      FlatMap unnest_array(regexp_split_to_array["\n", case_insensitive=false](array_index(regexp_match["light-to-temperature map:\n([0-9 \n]*?)\n\n", case_insensitive=false](#0{input}), 1))) // { arity: 2 }
                                        ReadStorage materialize.public.input // { arity: 1 }
                      ArrangeBy keys=[[]] // { arity: 3 }
                        Project (#3..=#5) // { arity: 3 }
                          Map (arraytoarray(regexp_split_to_array[" ", case_insensitive=false](#1{unnest})), array_index(#2{line}, 1), array_index(#2{line}, 2), array_index(#2{line}, 3)) // { arity: 6 }
                            FlatMap unnest_array(regexp_split_to_array["\n", case_insensitive=false](array_index(regexp_match["temperature-to-humidity map:\n([0-9 \n]*?)\n\n", case_insensitive=false](#0{input}), 1))) // { arity: 2 }
                              ReadStorage materialize.public.input // { arity: 1 }
            ArrangeBy keys=[[]] // { arity: 3 }
              Project (#3..=#5) // { arity: 3 }
                Map (arraytoarray(regexp_split_to_array[" ", case_insensitive=false](#1{unnest})), array_index(#2{line}, 1), array_index(#2{line}, 2), array_index(#2{line}, 3)) // { arity: 6 }
                  FlatMap unnest_array(regexp_split_to_array["\n", case_insensitive=false](array_index(regexp_match["humidity-to-location map:\n([0-9 \n]*)", case_insensitive=false](#0{input}), 1))) // { arity: 2 }
                    ReadStorage materialize.public.input // { arity: 1 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l0 // { arity: 1 }
      Map (null) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l0 // { arity: 1 }
          Constant // { arity: 0 }
            - ()

Source materialize.public.input

Target cluster: quickstart

EOF


query II
WITH MUTUALLY RECURSIVE
    blocks(head TEXT, body TEXT) AS (
        SELECT
            split_part(regexp_split_to_table(input, '\n\n'), ':', 1),
            split_part(regexp_split_to_table(input, '\n\n'), ':', 2)
        FROM
            input
    ),
    seeds(seed BIGINT) AS (
        SELECT regexp_split_to_table(trim(body), ' ')::BIGINT
        FROM blocks
        WHERE head = 'seeds'
    ),
    entry0(src_name TEXT, dst_name TEXT, dst_idx TEXT, src_idx TEXT, len TEXT) AS (
        SELECT
            split_part(split_part(head, ' ', 1), '-', 1),
            split_part(split_part(head, ' ', 1), '-', 3),
            split_part(regexp_split_to_table(body, '\n'), ' ', 1),
            split_part(regexp_split_to_table(body, '\n'), ' ', 2),
            split_part(regexp_split_to_table(body, '\n'), ' ', 3)
        FROM
            blocks
        WHERE
            head != 'seeds'
    ),
    entry(src_name TEXT, dst_name TEXT, src_idx BIGINT, dst_idx BIGINT, len BIGINT) AS (
        SELECT
            src_name,
            dst_name,
            src_idx::BIGINT,
            dst_idx::BIGINT,
            len::BIGINT
        FROM
            entry0
        WHERE
            src_idx != ''
    ),

    -- PART 1
    -- Our active inventory of .. "stuff"
    active(name TEXT, idx BIGINT) AS (
        SELECT 'seed', seed FROM seeds
        UNION ALL
        SELECT
            intent.dst_name,
            COALESCE(intent.idx + (entry.dst_idx - entry.src_idx), idx)
        FROM intent LEFT JOIN entry ON (
            intent.src_name = entry.src_name AND
            intent.dst_name = entry.dst_name AND
            intent.idx BETWEEN entry.src_idx AND entry.src_idx + len - 1)
    ),
    -- We would like to perform this mapping, but must find a range.
    intent(src_name TEXT, dst_name TEXT, idx BIGINT) AS (
        SELECT DISTINCT entry.src_name, dst_name, idx
        FROM active, entry
        WHERE active.name = entry.src_name
    ),
    part1(part1 BIGINT) AS (
        SELECT MIN(idx) FROM active WHERE name = 'location'
    ),

    -- PART 2
    -- Now we are doing *ranges* of seeds, rather than seed identifiers.
    -- They are big ranges, so we'll need to be smarter!
    seeds2(start_idx BIGINT, end_idx BIGINT) AS (
        SELECT
            regexp_split_to_array(trim(body), ' ')[2*x-1]::BIGINT,
            regexp_split_to_array(trim(body), ' ')[2*x-1]::BIGINT + regexp_split_to_array(trim(body), ' ')[2*x]::BIGINT
        FROM
            blocks,
            generate_series(1, array_length(regexp_split_to_array(trim(body), ' '), 1)/2) x
        WHERE head = 'seeds'
    ),
    active2(name TEXT, start_idx BIGINT, end_idx BIGINT) AS (
        SELECT 'seed', start_idx, end_idx
        FROM seeds2
        UNION
        SELECT
            dst_name,
            clipped_start + (entry_dst - entry_start),
            clipped_end   + (entry_dst - entry_start)
        FROM intersection
        UNION
        SELECT
            name,
            start_idx,
            end_idx
        FROM hole
    ),
    -- We would like to perform this mapping, but must find a range.
    intent2(src_name TEXT, dst_name TEXT, start_idx BIGINT, end_idx BIGINT) AS (
        SELECT DISTINCT entry.src_name, dst_name, start_idx, end_idx
        FROM active2, entry
        WHERE active2.name = entry.src_name
    ),
    -- Each mapping has a potential intersection with a requested range.
    intersection(src_name TEXT, dst_name TEXT, start_idx BIGINT, end_idx BIGINT, entry_start BIGINT, entry_end BIGINT, clipped_start BIGINT, clipped_end BIGINT, entry_dst BIGINT) AS (
        SELECT
            intent2.src_name,
            intent2.dst_name,
            intent2.start_idx,
            intent2.end_idx,
            entry.src_idx,
            entry.src_idx + entry.len,
            GREATEST(start_idx, entry.src_idx),
            LEAST(end_idx, entry.src_idx + entry.len),
            entry.dst_idx
        FROM intent2, entry
        WHERE intent2.src_name = entry.src_name
          AND intent2.dst_name = entry.dst_name
          AND GREATEST(intent2.start_idx, entry.src_idx)
            < LEAST(intent2.end_idx, entry.src_idx + entry.len)
    ),
    -- We may have holes in our intervals. Each intersection's start and end is the end and
    -- start, respectively, of some hole we may have that needs to remain the identity.
    hole(name TEXT, start_idx BIGINT, end_idx BIGINT) AS (
        SELECT * FROM (
            SELECT
                dst_name,
                clipped_end start_idx,
                (
                    SELECT COALESCE(MIN(i2.clipped_start), i1.end_idx)
                    FROM intersection i2
                    WHERE i2.clipped_start >= i1.clipped_end
                    AND i2.clipped_start < i1.end_idx
                    AND i1.src_name = i2.src_name
                    AND i1.dst_name = i2.dst_name
                    AND i1.start_idx = i2.start_idx
                    AND i1.end_idx = i2.end_idx
                ) end_idx
            FROM intersection i1
            UNION
            SELECT DISTINCT
                dst_name,
                start_idx,
                (
                    SELECT COALESCE(MIN(i2.clipped_start), i1.end_idx)
                    FROM intersection i2
                    WHERE i2.clipped_start >= i1.start_idx
                    AND i2.clipped_start < i1.end_idx
                    AND i1.src_name = i2.src_name
                    AND i1.dst_name = i2.dst_name
                    AND i1.start_idx = i2.start_idx
                    AND i1.end_idx = i2.end_idx
                )
            FROM intent2 i1
        )
        WHERE start_idx < end_idx
    ),
    part2(part2 BIGINT) AS ( SELECT MIN(start_idx) FROM active2 WHERE name = 'location')

SELECT * FROM part1, part2;
----
60602459  5

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
    blocks(head TEXT, body TEXT) AS (
        SELECT
            split_part(regexp_split_to_table(input, '\n\n'), ':', 1),
            split_part(regexp_split_to_table(input, '\n\n'), ':', 2)
        FROM
            input
    ),
    seeds(seed BIGINT) AS (
        SELECT regexp_split_to_table(trim(body), ' ')::BIGINT
        FROM blocks
        WHERE head = 'seeds'
    ),
    entry0(src_name TEXT, dst_name TEXT, dst_idx TEXT, src_idx TEXT, len TEXT) AS (
        SELECT
            split_part(split_part(head, ' ', 1), '-', 1),
            split_part(split_part(head, ' ', 1), '-', 3),
            split_part(regexp_split_to_table(body, '\n'), ' ', 1),
            split_part(regexp_split_to_table(body, '\n'), ' ', 2),
            split_part(regexp_split_to_table(body, '\n'), ' ', 3)
        FROM
            blocks
        WHERE
            head != 'seeds'
    ),
    entry(src_name TEXT, dst_name TEXT, src_idx BIGINT, dst_idx BIGINT, len BIGINT) AS (
        SELECT
            src_name,
            dst_name,
            src_idx::BIGINT,
            dst_idx::BIGINT,
            len::BIGINT
        FROM
            entry0
        WHERE
            src_idx != ''
    ),

    -- PART 1
    -- Our active inventory of .. "stuff"
    active(name TEXT, idx BIGINT) AS (
        SELECT 'seed', seed FROM seeds
        UNION ALL
        SELECT
            intent.dst_name,
            COALESCE(intent.idx + (entry.dst_idx - entry.src_idx), idx)
        FROM intent LEFT JOIN entry ON (
            intent.src_name = entry.src_name AND
            intent.dst_name = entry.dst_name AND
            intent.idx BETWEEN entry.src_idx AND entry.src_idx + len - 1)
    ),
    -- We would like to perform this mapping, but must find a range.
    intent(src_name TEXT, dst_name TEXT, idx BIGINT) AS (
        SELECT DISTINCT entry.src_name, dst_name, idx
        FROM active, entry
        WHERE active.name = entry.src_name
    ),
    part1(part1 BIGINT) AS (
        SELECT MIN(idx) FROM active WHERE name = 'location'
    ),

    -- PART 2
    -- Now we are doing *ranges* of seeds, rather than seed identifiers.
    -- They are big ranges, so we'll need to be smarter!
    seeds2(start_idx BIGINT, end_idx BIGINT) AS (
        SELECT
            regexp_split_to_array(trim(body), ' ')[2*x-1]::BIGINT,
            regexp_split_to_array(trim(body), ' ')[2*x-1]::BIGINT + regexp_split_to_array(trim(body), ' ')[2*x]::BIGINT
        FROM
            blocks,
            generate_series(1, array_length(regexp_split_to_array(trim(body), ' '), 1)/2) x
        WHERE head = 'seeds'
    ),
    active2(name TEXT, start_idx BIGINT, end_idx BIGINT) AS (
        SELECT 'seed', start_idx, end_idx
        FROM seeds2
        UNION
        SELECT
            dst_name,
            clipped_start + (entry_dst - entry_start),
            clipped_end   + (entry_dst - entry_start)
        FROM intersection
        UNION
        SELECT
            name,
            start_idx,
            end_idx
        FROM hole
    ),
    -- We would like to perform this mapping, but must find a range.
    intent2(src_name TEXT, dst_name TEXT, start_idx BIGINT, end_idx BIGINT) AS (
        SELECT DISTINCT entry.src_name, dst_name, start_idx, end_idx
        FROM active2, entry
        WHERE active2.name = entry.src_name
    ),
    -- Each mapping has a potential intersection with a requested range.
    intersection(src_name TEXT, dst_name TEXT, start_idx BIGINT, end_idx BIGINT, entry_start BIGINT, entry_end BIGINT, clipped_start BIGINT, clipped_end BIGINT, entry_dst BIGINT) AS (
        SELECT
            intent2.src_name,
            intent2.dst_name,
            intent2.start_idx,
            intent2.end_idx,
            entry.src_idx,
            entry.src_idx + entry.len,
            GREATEST(start_idx, entry.src_idx),
            LEAST(end_idx, entry.src_idx + entry.len),
            entry.dst_idx
        FROM intent2, entry
        WHERE intent2.src_name = entry.src_name
          AND intent2.dst_name = entry.dst_name
          AND GREATEST(intent2.start_idx, entry.src_idx)
            < LEAST(intent2.end_idx, entry.src_idx + entry.len)
    ),
    -- We may have holes in our intervals. Each intersection's start and end is the end and
    -- start, respectively, of some hole we may have that needs to remain the identity.
    hole(name TEXT, start_idx BIGINT, end_idx BIGINT) AS (
        SELECT * FROM (
            SELECT
                dst_name,
                clipped_end start_idx,
                (
                    SELECT COALESCE(MIN(i2.clipped_start), i1.end_idx)
                    FROM intersection i2
                    WHERE i2.clipped_start >= i1.clipped_end
                    AND i2.clipped_start < i1.end_idx
                    AND i1.src_name = i2.src_name
                    AND i1.dst_name = i2.dst_name
                    AND i1.start_idx = i2.start_idx
                    AND i1.end_idx = i2.end_idx
                ) end_idx
            FROM intersection i1
            UNION
            SELECT DISTINCT
                dst_name,
                start_idx,
                (
                    SELECT COALESCE(MIN(i2.clipped_start), i1.end_idx)
                    FROM intersection i2
                    WHERE i2.clipped_start >= i1.start_idx
                    AND i2.clipped_start < i1.end_idx
                    AND i1.src_name = i2.src_name
                    AND i1.dst_name = i2.dst_name
                    AND i1.start_idx = i2.start_idx
                    AND i1.end_idx = i2.end_idx
                )
            FROM intent2 i1
        )
        WHERE start_idx < end_idx
    ),
    part2(part2 BIGINT) AS ( SELECT MIN(start_idx) FROM active2 WHERE name = 'location')
SELECT * FROM part1, part2;
----
Explained Query:
  With
    cte l0 =
      Project (#2, #3) // { arity: 2 }
        Map (split_string(#1{unnest}, ":", 1), split_string(#1{unnest}, ":", 2)) // { arity: 4 }
          FlatMap unnest_array(regexp_split_to_array["\n\n", case_insensitive=false](#0{input})) // { arity: 2 }
            ReadStorage materialize.public.input // { arity: 1 }
    cte l1 =
      Project (#5..=#9) // { arity: 5 }
        Filter (#3 != "") // { arity: 10 }
          Map (split_string(#2{unnest}, " ", 2), split_string(#0{head}, " ", 1), split_string(#4, "-", 1), split_string(#4, "-", 3), text_to_bigint(#3{src_idx}), text_to_bigint(split_string(#2{unnest}, " ", 1)), text_to_bigint(split_string(#2{unnest}, " ", 3))) // { arity: 10 }
            FlatMap unnest_array(regexp_split_to_array["\n", case_insensitive=false](#1{body})) // { arity: 3 }
              Filter (#0{head} != "seeds") // { arity: 2 }
                Get l0 // { arity: 2 }
    cte l2 =
      ArrangeBy keys=[[#0{src_name}, #1{dst_name}]] // { arity: 5 }
        Get l1 // { arity: 5 }
    cte l3 =
      Project (#1) // { arity: 1 }
        Filter (#0{head} = "seeds") // { arity: 2 }
          Get l0 // { arity: 2 }
    cte l4 =
      ArrangeBy keys=[[#0{src_name}]] // { arity: 2 }
        Distinct project=[#0{src_name}, #1] // { arity: 2 }
          Project (#0, #1) // { arity: 2 }
            Get l1 // { arity: 5 }
  Return // { arity: 2 }
    With Mutually Recursive
      cte l5 =
        Project (#0..=#2, #5, #6) // { arity: 5 }
          Filter (#2{idx} >= #5{src_idx}) AND (#2{idx} <= ((#5{src_idx} + #7{len}) - 1)) // { arity: 8 }
            Join on=(#0{src_name} = #3{src_name} AND #1{dst_name} = #4{dst_name}) type=differential // { arity: 8 }
              implementation
                %0:l7[#0{src_name}, #1{dst_name}]KK » %1:l2[#0{src_name}, #1{dst_name}]KK
              ArrangeBy keys=[[#0{src_name}, #1{dst_name}]] // { arity: 3 }
                Get l7 // { arity: 3 }
              Get l2 // { arity: 5 }
      cte l6 =
        Union // { arity: 2 }
          Project (#3, #2) // { arity: 2 }
            Map (text_to_bigint(#1{unnest}), "seed") // { arity: 4 }
              FlatMap unnest_array(regexp_split_to_array[" ", case_insensitive=false](btrim(#0{body}))) // { arity: 2 }
                Get l3 // { arity: 1 }
          Project (#0, #4) // { arity: 2 }
            Map (coalesce((#1{idx} + (#3{dst_idx} - #2{src_idx})), #1{idx})) // { arity: 5 }
              Union // { arity: 4 }
                Project (#1..=#4) // { arity: 4 }
                  Get l5 // { arity: 5 }
                Project (#1, #2, #6, #7) // { arity: 4 }
                  Map (null, null) // { arity: 8 }
                    Join on=(#0 = #3 AND #1 = #4 AND #2 = #5) type=differential // { arity: 6 }
                      implementation
                        %0[#0..=#2]KKK » %1:l7[#0..=#2]KKK
                      ArrangeBy keys=[[#0..=#2]] // { arity: 3 }
                        Union // { arity: 3 }
                          Negate // { arity: 3 }
                            Distinct project=[#0..=#2] // { arity: 3 }
                              Project (#0..=#2) // { arity: 3 }
                                Get l5 // { arity: 5 }
                          Get l7 // { arity: 3 }
                      ArrangeBy keys=[[#0..=#2]] // { arity: 3 }
                        Get l7 // { arity: 3 }
      cte l7 =
        Project (#0{name}, #3, #1) // { arity: 3 }
          Join on=(#0{name} = #2{src_name}) type=differential // { arity: 4 }
            implementation
              %0[#0]K » %1:l4[#0]K
            ArrangeBy keys=[[#0{name}]] // { arity: 2 }
              Distinct project=[#0{name}, #1] // { arity: 2 }
                Get l6 // { arity: 2 }
            Get l4 // { arity: 2 }
      cte l8 =
        Reduce aggregates=[min(#0{idx})] // { arity: 1 }
          Project (#1) // { arity: 1 }
            Filter (#0{name} = "location") // { arity: 2 }
              Get l6 // { arity: 2 }
      cte l9 =
        Distinct project=[#0..=#2] // { arity: 3 }
          Union // { arity: 3 }
            Project (#6, #4, #5) // { arity: 3 }
              Map (regexp_split_to_array[" ", case_insensitive=false](btrim(#0{body})), (2 * #1{x}), text_to_bigint(array_index(#2, integer_to_bigint((#3 - 1)))), (#4{"?column?"} + text_to_bigint(array_index(#2, integer_to_bigint(#3)))), "seed") // { arity: 7 }
                FlatMap generate_series(1, ((regexp_split_to_array[" ", case_insensitive=false](btrim(#0{body})) array_length 1) / 2), 1) // { arity: 2 }
                  Get l3 // { arity: 1 }
            Project (#1, #10, #11) // { arity: 3 }
              Map ((#8{entry_dst} - #4{entry_start}), (#6{clipped_start} + #9), (#7{clipped_end} + #9)) // { arity: 12 }
                Get l11 // { arity: 9 }
            Get l19 // { arity: 3 }
      cte l10 =
        Project (#0..=#2, #4) // { arity: 4 }
          Join on=(#0 = #3{src_name}) type=differential // { arity: 5 }
            implementation
              %0:l9[#0]K » %1:l4[#0]K
            ArrangeBy keys=[[#0]] // { arity: 3 }
              Get l9 // { arity: 3 }
            Get l4 // { arity: 2 }
      cte l11 =
        Project (#0, #3, #1, #2, #6, #10, #9, #11, #7) // { arity: 9 }
          Filter (#9 < #11) // { arity: 12 }
            Map (greatest(#1{start_idx}, #6{src_idx}), (#6{src_idx} + #8{len}), least(#2{end_idx}, #10)) // { arity: 12 }
              Join on=(#0{src_name} = #4{src_name} AND #3{dst_name} = #5{dst_name}) type=differential // { arity: 9 }
                implementation
                  %0:l10[#0{src_name}, #3{dst_name}]KK » %1:l2[#0{src_name}, #1{dst_name}]KK
                ArrangeBy keys=[[#0{src_name}, #3{dst_name}]] // { arity: 4 }
                  Get l10 // { arity: 4 }
                Get l2 // { arity: 5 }
      cte l12 =
        Distinct project=[#0..=#8] // { arity: 9 }
          Get l11 // { arity: 9 }
      cte l13 =
        Distinct project=[#0..=#4] // { arity: 5 }
          Project (#0..=#3, #7) // { arity: 5 }
            Get l12 // { arity: 9 }
      cte l14 =
        Reduce group_by=[#0..=#4] aggregates=[min(#5{clipped_start})] // { arity: 6 }
          Project (#0..=#4, #9) // { arity: 6 }
            Filter (#9{clipped_start} >= #4{clipped_end}) // { arity: 10 }
              Join on=(#0{src_name} = #5{src_name} AND #1{dst_name} = #6{dst_name} AND #2{start_idx} = #7{start_idx} AND #3{end_idx} = #8{end_idx}) type=differential // { arity: 10 }
                implementation
                  %1:l11[#0{src_name}..=#3{end_idx}]KKKKf » %0:l13[#0{src_name}..=#3{end_idx}]KKKKf
                ArrangeBy keys=[[#0{src_name}..=#3{end_idx}]] // { arity: 5 }
                  Filter (#2{start_idx}) IS NOT NULL AND (#3{end_idx}) IS NOT NULL // { arity: 5 }
                    Get l13 // { arity: 5 }
                ArrangeBy keys=[[#0{src_name}..=#3{end_idx}]] // { arity: 5 }
                  Project (#0..=#3, #6) // { arity: 5 }
                    Filter (#2{start_idx}) IS NOT NULL AND (#6{clipped_start} < #3{end_idx}) // { arity: 9 }
                      Get l11 // { arity: 9 }
      cte l15 =
        Project (#0..=#4, #6) // { arity: 6 }
          Map (coalesce(#5{min}, #3{end_idx})) // { arity: 7 }
            Union // { arity: 6 }
              Get l14 // { arity: 6 }
              Project (#0..=#4, #10) // { arity: 6 }
                Map (null) // { arity: 11 }
                  Join on=(#0 = #5 AND #1 = #6 AND #2 = #7 AND #3 = #8 AND #4 = #9) type=differential // { arity: 10 }
                    implementation
                      %1:l13[#0..=#4]UKKKKK » %0[#0..=#4]KKKKK
                    ArrangeBy keys=[[#0..=#4]] // { arity: 5 }
                      Union // { arity: 5 }
                        Negate // { arity: 5 }
                          Project (#0..=#4) // { arity: 5 }
                            Get l14 // { arity: 6 }
                        Get l13 // { arity: 5 }
                    ArrangeBy keys=[[#0..=#4]] // { arity: 5 }
                      Get l13 // { arity: 5 }
      cte l16 =
        Reduce group_by=[#0, #3, #1, #2] aggregates=[min(#4{clipped_start})] // { arity: 5 }
          Project (#0..=#3, #8) // { arity: 5 }
            Join on=(#0{src_name} = #4{src_name} AND #1{start_idx} = #6{start_idx} AND #2{end_idx} = #7{end_idx} AND #3{dst_name} = #5{dst_name}) type=differential // { arity: 9 }
              implementation
                %1:l11[#0{src_name}, #2{start_idx}, #3{end_idx}, #1{dst_name}]KKKKf » %0:l10[#0{src_name}..=#3{dst_name}]KKKKf
              ArrangeBy keys=[[#0{src_name}..=#3{dst_name}]] // { arity: 4 }
                Filter (#1{start_idx}) IS NOT NULL AND (#2{end_idx}) IS NOT NULL // { arity: 4 }
                  Get l10 // { arity: 4 }
              ArrangeBy keys=[[#0{src_name}, #2{start_idx}, #3{end_idx}, #1{dst_name}]] // { arity: 5 }
                Project (#0..=#3, #6) // { arity: 5 }
                  Filter (#6{clipped_start} < #3{end_idx}) AND (#6{clipped_start} >= #2{start_idx}) // { arity: 9 }
                    Get l11 // { arity: 9 }
      cte l17 =
        ArrangeBy keys=[[#0..=#3]] // { arity: 4 }
          Get l10 // { arity: 4 }
      cte l18 =
        Project (#0..=#3, #5) // { arity: 5 }
          Map (coalesce(#4{min}, #3{end_idx})) // { arity: 6 }
            Union // { arity: 5 }
              Get l16 // { arity: 5 }
              Project (#0..=#3, #8) // { arity: 5 }
                Map (null) // { arity: 9 }
                  Join on=(#0 = #4 AND #1 = #7 AND #2 = #5 AND #3 = #6) type=differential // { arity: 8 }
                    implementation
                      %0[#0, #2, #3, #1]KKKK » %1:l17[#0..=#3]KKKK
                    ArrangeBy keys=[[#0, #2, #3, #1]] // { arity: 4 }
                      Union // { arity: 4 }
                        Negate // { arity: 4 }
                          Project (#0..=#3) // { arity: 4 }
                            Get l16 // { arity: 5 }
                        Project (#0, #3, #1, #2) // { arity: 4 }
                          Get l10 // { arity: 4 }
                    Get l17 // { arity: 4 }
      cte l19 =
        Distinct project=[#0..=#2] // { arity: 3 }
          Union // { arity: 3 }
            Project (#1, #7, #23) // { arity: 3 }
              Join on=(#0 = #9 = #18 AND #1 = #10 = #19 AND #2 = #11 = #20 AND #3 = #12 = #21 AND #4 = #13 AND #5 = #14 AND #6 = #15 AND #7 = #16 = #22 AND #8 = #17) type=delta // { arity: 24 }
                implementation
                  %0:l11 » %1:l12[#0..=#8]UKKKKKKKKK » %2[#0..=#4]KKKKK
                  %1:l12 » %0:l11[#0..=#8]KKKKKKKKK » %2[#0..=#4]KKKKK
                  %2 » %0:l11[#0..=#3, #7]KKKKK » %1:l12[#0..=#8]UKKKKKKKKK
                ArrangeBy keys=[[#0..=#8], [#0..=#3, #7]] // { arity: 9 }
                  Get l11 // { arity: 9 }
                ArrangeBy keys=[[#0..=#8]] // { arity: 9 }
                  Get l12 // { arity: 9 }
                ArrangeBy keys=[[#0..=#4]] // { arity: 6 }
                  Union // { arity: 6 }
                    Filter (#4 < #5) // { arity: 6 }
                      Get l15 // { arity: 6 }
                    Project (#0..=#4, #6) // { arity: 6 }
                      Filter (#4 < #6) // { arity: 7 }
                        FlatMap guard_subquery_size(#5{count}) // { arity: 7 }
                          Reduce group_by=[#0..=#4] aggregates=[count(*)] // { arity: 6 }
                            Project (#0..=#4) // { arity: 5 }
                              Get l15 // { arity: 6 }
            Distinct project=[#1, #0, #2] // { arity: 3 }
              Project (#1, #3, #12) // { arity: 3 }
                Join on=(#0 = #4 = #8 AND #1 = #5 = #10 AND #2 = #6 = #11 AND #3 = #7 = #9) type=delta // { arity: 13 }
                  implementation
                    %0:l17 » %1:l17[#0..=#3]KKKK » %2[#0..=#3]KKKK
                    %1:l17 » %0:l17[#0..=#3]KKKK » %2[#0..=#3]KKKK
                    %2 » %0:l17[#0..=#3]KKKK » %1:l17[#0..=#3]KKKK
                  Get l17 // { arity: 4 }
                  Get l17 // { arity: 4 }
                  ArrangeBy keys=[[#0..=#3]] // { arity: 5 }
                    Union // { arity: 5 }
                      Filter (#2 < #4) // { arity: 5 }
                        Get l18 // { arity: 5 }
                      Project (#0..=#3, #5) // { arity: 5 }
                        Filter (#2 < #5) // { arity: 6 }
                          FlatMap guard_subquery_size(#4{count}) // { arity: 6 }
                            Reduce group_by=[#0..=#3] aggregates=[count(*)] // { arity: 5 }
                              Project (#0..=#3) // { arity: 4 }
                                Get l18 // { arity: 5 }
    Return // { arity: 2 }
      With
        cte l20 =
          Reduce aggregates=[min(#0{start_idx})] // { arity: 1 }
            Project (#1) // { arity: 1 }
              Filter (#0{name} = "location") // { arity: 3 }
                Get l9 // { arity: 3 }
      Return // { arity: 2 }
        CrossJoin type=differential // { arity: 2 }
          implementation
            %0[×]U » %1[×]U
          ArrangeBy keys=[[]] // { arity: 1 }
            Union // { arity: 1 }
              Get l8 // { arity: 1 }
              Map (null) // { arity: 1 }
                Union // { arity: 0 }
                  Negate // { arity: 0 }
                    Project () // { arity: 0 }
                      Get l8 // { arity: 1 }
                  Constant // { arity: 0 }
                    - ()
          ArrangeBy keys=[[]] // { arity: 1 }
            Union // { arity: 1 }
              Get l20 // { arity: 1 }
              Map (null) // { arity: 1 }
                Union // { arity: 0 }
                  Negate // { arity: 0 }
                    Project () // { arity: 0 }
                      Get l20 // { arity: 1 }
                  Constant // { arity: 0 }
                    - ()

Source materialize.public.input

Target cluster: quickstart

EOF