Long JSON string causes "memory access out of bounds" error

[ankrgyl]

What happens?

Running the SELECT query in this repro in the DuckDB WASM library causes a memory out of bounds error.

To Reproduce

Visit https://shell.duckdb.org/

INSTALL json;
LOAD json;

Run this query

Browser/Environment:

DuckDB shell

Device:

macbook pro

DuckDB-Wasm Version:

@duckdb/duckdb-wasm@1.27.1-dev134.0

DuckDB-Wasm Deployment:

shell.duckdb.org

Full Name:

Ankur Goyal

Affiliation:

Braintrust Data

[ankrgyl]

Of note -- this does not repro in the python library or duckdb binary.

[carlopi]

Thanks for the reproduction, I do reproduce, and at the very least this should throw an error visible to the user.

I am not sure whether proper supporting any query length is in scope, and note that DuckDB MacOS CLI, at least on my machine also hangs here (they are likely two different problems, but I am not sure if SQL of any length are properly supported).
Note that DuckDB CLI, at least on my machine, this also hangs.

[ankrgyl]

Interesting, on my machine, both in the CLI and Python lib for 0.8 and 0.9, it completes instantly.

[ankrgyl]

It also does not seem to repro for me on duckdb-wasm using duckdb 0.8

[ankrgyl]

I also benchmarked it using time -l on duckdb 0.9.1 vs duckdb 0.8.1 on the CLI:

0.9.1:

            19333120  maximum resident set size
                   0  average shared memory size
                   0  average unshared data size
                   0  average unshared stack size
                1344  page reclaims
                   0  page faults
                   0  swaps
                   0  block input operations
                   0  block output operations
                   0  messages sent
                   0  messages received
                   0  signals received
                 321  voluntary context switches
                  43  involuntary context switches
           214117088  instructions retired
            65685719  cycles elapsed
             7766976  peak memory footprint

0.8.1:

            18333696  maximum resident set size
                   0  average shared memory size
                   0  average unshared data size
                   0  average unshared stack size
                1287  page reclaims
                   0  page faults
                   0  swaps
                   0  block input operations
                   0  block output operations
                   0  messages sent
                   0  messages received
                   0  signals received
                 322  voluntary context switches
                  78  involuntary context switches
           193522811  instructions retired
            64555370  cycles elapsed
             7472064  peak memory footprint

0.9.1 uses about 5% more memory

[pmm-motif]

I am seeing the same error when reading query results from a specific Parquet file using DuckDB WASM 1.28.0. This bug does not show-off with v1.27.0 or v1.25.0. It does not occur with Node library or standalone version od DuckDB. Because of that (and due to structs involved in both cases), I suspect it might have the same root case as the problem reported in this issue

I was trying to pinpoint what exactly triggers it in my case case but had hard time figuring it out. The file contains ~150 columns, out of which ~5 are structs. Removing one of the columns was usually enough to make it work. The said Parquet file contains sensitive data, so I cannot share it unfortunately. I tried to create a minimal reproducible example but the error goes away even with small changes to the contents

[Robert-M-Muench]

I have the same problem with an object array with 280 entries and every object about 30 entries (columns). The generated query string for loadObjects is about 400KB big.

This is the error message:

RuntimeError: memory access out of bounds
    at wasm://wasm/045118ee:wasm-function[200]:0x174d0
    at wasm://wasm/045118ee:wasm-function[61]:0xf3c2
    at wasm://wasm/045118ee:wasm-function[14490]:0xc8b622
    at wasm://wasm/045118ee:wasm-function[14394]:0xc78def
    at e._duckdb_web_query_run (https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.0/dist/duckdb-browser-eh.worker.js:1:25535)
    at Object._f [as ccall] (https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.0/dist/duckdb-browser-eh.worker.js:1:22592)
    at it (https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.0/dist/duckdb-browser-eh.worker.js:1:241030)
    at ko.runQuery (https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.0/dist/duckdb-browser-eh.worker.js:1:243820)
    at Qa.onMessage (https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.0/dist/duckdb-browser-eh.worker.js:1:231844)
    at Ru.globalThis.onmessage (https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.0/dist/duckdb-browser-eh.worker.js:1:265410)

I will try to make the query small to see if there is a barrier from which the error kicks in.

[Robert-M-Muench]

Shortening the query string to around 25KB and it works as expected.

[Robert-M-Muench]

Loading the CSV file by loadCSV providing a URL works.

[Robert-M-Muench]

Does anyone know how to work around this problem or track it down and fix it?

It looks like something fundamental, which requires knowing the source code very well.

[ankrgyl]

I'm unfortunately still on duckdb v0.8 due to this :(

[ankrgyl]

It doesn't seem to have anything to do with JSON, btw. SELECT LENGTH(...) also fails

[csjh]

Appears to kick in at length 66720, repro'ing with

const q = `SELECT list_extract([${Array.from({ length: 66_688 }, () => 1).join("")}], 0) as q`;

Edit: Actually the number seems to change with the query somehow, kicks in at 67424 with this one:

const q = `SELECT list_distinct([${Array.from({ length: 33_697 }, () => 1).join(",")}]) as q  `;

Also, only seems to happen at certain numbers? Example:

const works  = `SELECT 1` + " ".repeat(68_600);
const doesnt = `SELECT 1` + " ".repeat(66_600);

[jankramer]

I'm running in to the same issue: getting a memory access out of bounds error when executing a rather large query (approx. 80KiB of SQL).

Could this be caused by exceeding the default total stack size of 64k set by Emscripten? If I understand correctly, the query string is passed as a stack allocated string. If that's the case, is there any reason to not allocate the query string on the heap and pass in a pointer?

[carlopi]

I believe this to be solved via #1768, where instead of (implicitly) passing a JS string as stack allocated it's now passed as heap-allocated within the Uin8Array memory space.

[carlopi]

I also added a test with a query of length 1 million, and it's handled fine, I think the limits here will be the limit for allocating a contiguous block of memory.
But then other limits will kick in first.

[ankrgyl]

Can you ping once #1768 is live on shell? I can sanity check the original repro.

[carlopi]

I think it's already on the shell, possibly you might need to reload due to caching.
Also on npm.

[ankrgyl]

This repro just hangs now. I let it run for ~an hour.

Note that when I filed the task, the old version of duckdb-wasm I was using (I believe pinned to 0.8 of duckdb main), it completed after a few seconds.

[ankrgyl]

@carlopi following up on this, given that the fix does not actually fix the repro, I would request that we reopen this task. I'm actually a bit worried with the new changes that it may hang or otherwise break cases that work (or were formerly returning errors).

[archiewood]

Also ran into this with the following query (v1.28)

Query String

with ops_rev as (
        select
            'Ops Revenue' as metric,
            'usd0' as fmt1,
            'pct0' as fmt2,
            sum(ops_revenue_actual_wtd) as actual_wtd,
            sum(ops_revenue_forecast_wtd) as forecast_wtd,
            sum(ops_revenue_actual_wtd) - sum(ops_revenue_forecast_wtd) as variance_mtd,
            (sum(ops_revenue_actual_wtd) - sum(ops_revenue_forecast_wtd)) / nullif(sum(ops_revenue_forecast_wtd), 0) as variance_pct_wtd,
            sum(ops_revenue_actual_mtd) as actual_mtd,
            sum(ops_revenue_forecast_mtd) as forecast_mtd,
            sum(ops_revenue_actual_prior_month) as actual_prior_month,
            sum(ops_revenue_actual_t3m_avg) as actual_t3m_avg
        from (with base_all_clinics as (
        select
            clinics.clinic_id,
            clinics.clinic,
            case when clinics.clinic_id = 1 then 'Virtual Care' else clinics.region end as region
        from warehouse.clinics
    ),
    
    base as (
        select
            base_all_clinics.clinic,
            base_all_clinics.region,

            --Ops Revenue
            sum(
                case
                    when clinic_ops_revenue.last_day_of_week::date = '2024-06-30'::date
                    then ops_revenue
                end
            ) as ops_revenue_actual_wtd,
            sum(
                case
                    when clinic_ops_revenue.last_day_of_week::date = '2024-06-30'::date
                    then ops_revenue_forecast
                end
            ) as ops_revenue_forecast_wtd,
            sum(
                case 
                    when clinic_ops_revenue.first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then ops_revenue
                end
            ) as ops_revenue_actual_mtd,
            sum(
                case
                    when clinic_ops_revenue.first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then ops_revenue_forecast
                end
            ) as ops_revenue_forecast_mtd,
            sum(
                case
                    when clinic_ops_revenue.first_day_of_month::date = (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                    then ops_revenue
                end
            ) as ops_revenue_actual_prior_month,
            sum(
                case
                    when 
                        clinic_ops_revenue.first_day_of_month::date <= (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                        and clinic_ops_revenue.first_day_of_month::date >= (date_trunc('month', '2024-06-30'::date)::date - interval '3 month')
                    then ops_revenue
                end
            ) / 3.0 as ops_revenue_actual_t3m_avg,
            
            --VN/VA Revenue
            sum(
                case
                    when clinic_ops_revenue.last_day_of_week::date = '2024-06-30'::date
                    then vnva_revenue
                end
            ) as vnva_revenue_actual_wtd,
            sum(
                case
                    when clinic_ops_revenue.last_day_of_week::date = '2024-06-30'::date
                    then vnva_revenue_forecast
                end
            ) as vnva_revenue_forecast_wtd,
            sum(
                case 
                    when clinic_ops_revenue.first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then vnva_revenue
                end
            ) as vnva_revenue_actual_mtd,
            sum(
                case
                    when clinic_ops_revenue.first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then vnva_revenue_forecast
                end
            ) as vnva_revenue_forecast_mtd,
            sum(
                case
                    when clinic_ops_revenue.first_day_of_month::date = (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                    then vnva_revenue
                end
            ) as vnva_revenue_actual_prior_month,
            sum(
                case
                    when 
                        clinic_ops_revenue.first_day_of_month::date <= (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                        and clinic_ops_revenue.first_day_of_month::date >= (date_trunc('month', '2024-06-30'::date)::date - interval '3 month')
                    then vnva_revenue
                end
            ) / 3.0 as vnva_revenue_actual_t3m_avg
        from base_all_clinics
        left join warehouse.clinic_ops_revenue using (clinic_id)
        where ops_revenue > 0
        and clinic_ops_revenue.first_day_of_month::date >= (date_trunc('month', '2024-06-30'::date)::date - interval '3 month')
        group by 1, 2
    )

    select
        clinic,
        region,

        -- Ops Revenue
        ops_revenue_actual_wtd,
        ops_revenue_forecast_wtd,
        ops_revenue_actual_wtd - ops_revenue_forecast_wtd as ops_revenue_wtd_variance,
        (ops_revenue_actual_wtd - ops_revenue_forecast_wtd)/ops_revenue_forecast_wtd as ops_revenue_wtd_variance_pct,
        ops_revenue_actual_mtd,
        ops_revenue_forecast_mtd,
        ops_revenue_actual_mtd - ops_revenue_forecast_mtd as ops_revenue_mtd_variance,
        (ops_revenue_actual_mtd - ops_revenue_forecast_mtd)/ops_revenue_forecast_mtd as ops_revenue_mtd_variance_pct,
        ops_revenue_actual_prior_month,
        ops_revenue_actual_t3m_avg,

        -- VN/VA Revenue
        vnva_revenue_actual_wtd,
        vnva_revenue_forecast_wtd,
        vnva_revenue_actual_wtd - vnva_revenue_forecast_wtd as vnva_revenue_wtd_variance,
        (vnva_revenue_actual_wtd - vnva_revenue_forecast_wtd)/vnva_revenue_forecast_wtd as vnva_revenue_wtd_variance_pct,
        vnva_revenue_actual_mtd,
        vnva_revenue_forecast_mtd,
        vnva_revenue_actual_mtd - vnva_revenue_forecast_mtd as vnva_revenue_mtd_variance,
        (vnva_revenue_actual_mtd - vnva_revenue_forecast_mtd)/vnva_revenue_forecast_mtd as vnva_revenue_mtd_variance_pct,
        vnva_revenue_actual_prior_month,
        vnva_revenue_actual_t3m_avg

    from base
    order by clinic)
        group by 1, 2, 3
    ),

    flow_metrics as (
        select
            *
        from (with base_all_clinics as (
        select
            clinics.clinic_id,
            clinics.clinic,
            case when clinics.clinic_id = 1 then 'Virtual Care' else clinics.region end as region
        from warehouse.clinics
    ),

    base as (
        select
            base_all_clinics.clinic,
            base_all_clinics.region,
            
            -- Total Flows
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then total_flows_actual
                end
            ) as total_flows_wtd,
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then total_flows_forecast
                end
            ) as total_flows_forecast_wtd,
            sum(
                case 
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then total_flows_actual
                end
            ) as total_flows_mtd,
            sum(
                case
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then total_flows_forecast
                end
            ) as total_flows_forecast_mtd,
            sum(
                case
                    when first_day_of_month::date = (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                    then total_flows_actual
                end
            ) as total_flows_actual_prior_month,
            sum(
                case
                    when 
                        first_day_of_month::date <= (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                        and first_day_of_month::date >= (date_trunc('month', '2024-06-30'::date)::date - interval '3 month')
                    then total_flows_actual
                end
            ) / 3.0 as total_flows_actual_t3m_avg,

            -- Appt flows
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then appt_flows_actual
                end
            ) as appt_flows_actual_wtd,
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then appt_flows_forecast
                end
            ) as appt_flows_forecast_wtd,
            sum(
                case 
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then appt_flows_actual
                end
            ) as appt_flows_actual_mtd,
            sum(
                case
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then appt_flows_forecast
                end
            ) as appt_flows_forecast_mtd,
            sum(
                case
                    when first_day_of_month::date = (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                    then appt_flows_actual
                end
            ) as appt_flows_actual_prior_month,
            sum(
                case
                    when 
                        first_day_of_month::date <= (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                        and first_day_of_month::date >= (date_trunc('month', '2024-06-30'::date)::date - interval '3 month')
                    then appt_flows_actual
                end
            ) / 3.0 as appt_flows_actual_t3m_avg,

            -- Appt Flow Revenue
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then appt_flow_revenue_actual
                end
            ) as appt_flow_revenue_actual_wtd,
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then appt_flow_revenue_forecast
                end
            ) as appt_flow_revenue_forecast_wtd,
            sum(
                case 
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then appt_flow_revenue_actual
                end
            ) as appt_flow_revenue_actual_mtd,
            sum(
                case
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then appt_flow_revenue_forecast
                end
            ) as appt_flow_revenue_forecast_mtd,
            sum(
                case
                    when first_day_of_month::date = (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                    then appt_flow_revenue_actual
                end
            ) as appt_flow_revenue_actual_prior_month,
            sum(
                case
                    when 
                        first_day_of_month::date <= (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                        and first_day_of_month::date >= (date_trunc('month', '2024-06-30'::date)::date - interval '3 month')
                    then appt_flow_revenue_actual
                end
            ) / 3.0 as appt_flow_revenue_actual_t3m_avg,

            -- Completed Appointment Count
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then completed_appointments_actual
                end
            ) as completed_appointments_actual_wtd,
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then completed_appointments_forecast
                end
            ) as completed_appointments_forecast_wtd,
            sum(
                case 
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then completed_appointments_actual
                end
            ) as completed_appointments_actual_mtd,
            sum(
                case
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then completed_appointments_forecast
                end
            ) as completed_appointments_forecast_mtd,
            sum(
                case
                    when first_day_of_month::date = (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                    then completed_appointments_actual
                end
            ) as completed_appointments_actual_prior_month,
            sum(
                case
                    when 
                        first_day_of_month::date <= (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                        and first_day_of_month::date >= (date_trunc('month', '2024-06-30'::date)::date - interval '3 month')
                    then completed_appointments_actual
                end
            ) / 3.0 as completed_appointments_actual_t3m_avg,

            -- Procedure Flows
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then procedure_flows_actual
                end
            ) as procedure_flows_actual_wtd,
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then procedure_flows_forecast
                end
            ) as procedure_flows_forecast_wtd,
            sum(
                case 
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then procedure_flows_actual
                end
            ) as procedure_flows_actual_mtd,
            sum(
                case
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then procedure_flows_forecast
                end
            ) as procedure_flows_forecast_mtd,
            sum(
                case
                    when first_day_of_month::date = (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                    then procedure_flows_actual
                end
            ) as procedure_flows_actual_prior_month,
            sum(
                case
                    when 
                        first_day_of_month::date <= (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                        and first_day_of_month::date >= (date_trunc('month', '2024-06-30'::date)::date - interval '3 month')
                    then procedure_flows_actual
                end
            ) / 3.0 as procedure_flows_actual_t3m_avg,

            -- Procedure Flow Revenue
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then procedure_flow_revenue_actual
                end
            ) as procedure_flow_revenue_actual_wtd,
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then procedure_flow_revenue_forecast
                end
            ) as procedure_flow_revenue_forecast_wtd,
            sum(
                case 
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then procedure_flow_revenue_actual
                end
            ) as procedure_flow_revenue_actual_mtd,
            sum(
                case
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then procedure_flow_revenue_forecast
                end
            ) as procedure_flow_revenue_forecast_mtd,
            sum(
                case
                    when first_day_of_month::date = (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                    then procedure_flow_revenue_actual
                end
            ) as procedure_flow_revenue_actual_prior_month,
            sum(
                case
                    when 
                        first_day_of_month::date <= (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                        and first_day_of_month::date >= (date_trunc('month', '2024-06-30'::date)::date - interval '3 month')
                    then procedure_flow_revenue_actual
                end
            ) / 3.0 as procedure_flow_revenue_actual_t3m_avg,

            -- Completed Procedure Count
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then completed_procedures_actual
                end
            ) as completed_procedures_actual_wtd,
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then completed_procedures_forecast
                end
            ) as completed_procedures_forecast_wtd,
            sum(
                case 
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then completed_procedures_actual
                end
            ) as completed_procedures_actual_mtd,
            sum(
                case
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then completed_procedures_forecast
                end
            ) as completed_procedures_forecast_mtd,
            sum(
                case
                    when first_day_of_month::date = (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                    then completed_procedures_actual
                end
            ) as completed_procedures_actual_prior_month,
            sum(
                case
                    when 
                        first_day_of_month::date <= (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                        and first_day_of_month::date >= (date_trunc('month', '2024-06-30'::date)::date - interval '3 month')
                    then completed_procedures_actual
                end
            ) / 3.0 as completed_procedures_actual_t3m_avg,

            -- Procedure Point Utilization
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then procedure_points_booked_actual
                end
            ) / sum (
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then procedural_capacity_actual
                end
            )::float as procedure_point_utilization_actual_wtd,
            sum(
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then procedure_points_booked_actual
                end
            ) as procedure_points_booked_actual_wtd,
            sum (
                case
                    when last_day_of_week::date = '2024-06-30'::date
                    then procedural_capacity_actual
                end
            ) as procedural_capacity_actual_wtd,
            0.90 as procedure_point_utilization_forecast_wtd,
            sum(
                case 
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then procedure_points_booked_actual
                end
            ) / sum (
                case
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then procedural_capacity_actual
                end
            )::float as procedure_point_utilization_actual_mtd,
            sum(
                case 
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then procedure_points_booked_actual
                end
            ) as procedure_points_booked_actual_mtd,
            sum (
                case
                    when first_day_of_month::date = date_trunc('month', '2024-06-30'::date)::date
                    then procedural_capacity_actual
                end
            ) as procedural_capacity_actual_mtd,
            0.90 as procedure_point_utilization_forecast_mtd,
            sum(
                case
                    when first_day_of_month::date = (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                    then procedure_points_booked_actual
                end
            ) / sum(
                case
                    when first_day_of_month::date = (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                    then procedural_capacity_actual
                end
            )::float as procedure_point_utilization_actual_prior_month,
            sum(
                case
                    when first_day_of_month::date = (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                    then procedure_points_booked_actual
                end
            ) as procedure_points_booked_actual_prior_month,
            sum(
                case
                    when first_day_of_month::date = (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                    then procedural_capacity_actual
                end
            ) as procedural_capacity_actual_prior_month,
            sum(
                case
                    when 
                        first_day_of_month::date <= (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                        and first_day_of_month::date >= (date_trunc('month', '2024-06-30'::date)::date - interval '3 month')
                    then procedure_points_booked_actual
                end
            ) / sum(
                case
                    when 
                        first_day_of_month::date <= (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                        and first_day_of_month::date >= (date_trunc('month', '2024-06-30'::date)::date - interval '3 month')
                    then procedural_capacity_actual
                end
            )::float as procedure_point_utilization_actual_t3m_avg,
            sum(
                case
                    when 
                        first_day_of_month::date <= (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                        and first_day_of_month::date >= (date_trunc('month', '2024-06-30'::date)::date - interval '3 month')
                    then procedure_points_booked_actual
                end
            ) as procedure_points_booked_actual_t3m_avg,
            sum(
                case
                    when 
                        first_day_of_month::date <= (date_trunc('month', '2024-06-30'::date)::date - interval '1 month')
                        and first_day_of_month::date >= (date_trunc('month', '2024-06-30'::date)::date - interval '3 month')
                    then procedural_capacity_actual
                end
            ) as procedural_capacity_actual_t3m_avg

        from base_all_clinics
        left join warehouse.clinic_flows using (clinic_id)
        where first_day_of_month::date >= (date_trunc('month', '2024-06-30'::date)::date - interval '3 month')
        group by 1, 2
    )
    
    select
        clinic,
        region,

        -- Total Flows
        total_flows_wtd,
        total_flows_forecast_wtd,
        total_flows_wtd - total_flows_forecast_wtd as total_flows_wtd_variance,
        (total_flows_wtd - total_flows_forecast_wtd) / total_flows_forecast_wtd as total_flows_wtd_variance_pct,
        total_flows_mtd,
        total_flows_forecast_mtd,
        total_flows_mtd - total_flows_forecast_mtd as total_flows_mtd_variance,
        (total_flows_mtd - total_flows_forecast_mtd) / total_flows_forecast_mtd as total_flows_mtd_variance_pct,
        total_flows_actual_prior_month,
        total_flows_actual_t3m_avg,

        -- Appt Flows
        appt_flows_actual_wtd,
        appt_flows_forecast_wtd,
        appt_flows_actual_wtd - appt_flows_forecast_wtd as appt_flows_wtd_variance,
        (appt_flows_actual_wtd - appt_flows_forecast_wtd) / appt_flows_forecast_wtd as appt_flows_wtd_variance_pct,
        appt_flows_actual_mtd,
        appt_flows_forecast_mtd,
        appt_flows_actual_mtd - appt_flows_forecast_mtd as appt_flows_mtd_variance,
        (appt_flows_actual_mtd - appt_flows_forecast_mtd) / appt_flows_forecast_mtd as appt_flows_mtd_variance_pct,
        appt_flows_actual_prior_month,
        appt_flows_actual_t3m_avg,

        -- Appt Flow Revenue (total)
        appt_flow_revenue_actual_wtd,
        appt_flow_revenue_forecast_wtd,
        appt_flow_revenue_actual_wtd - appt_flow_revenue_forecast_wtd as appt_flow_revenue_wtd_variance,
        (appt_flow_revenue_actual_wtd - appt_flow_revenue_forecast_wtd) / appt_flow_revenue_forecast_wtd as appt_flow_revenue_wtd_variance_pct,
        appt_flow_revenue_actual_mtd,
        appt_flow_revenue_forecast_mtd,
        appt_flow_revenue_actual_mtd - appt_flow_revenue_forecast_mtd as appt_flow_revenue_mtd_variance,
        (appt_flow_revenue_actual_mtd - appt_flow_revenue_forecast_mtd) / appt_flow_revenue_forecast_mtd as appt_flow_revenue_mtd_variance_pct,
        appt_flow_revenue_actual_prior_month,
        appt_flow_revenue_actual_t3m_avg,

        -- Revenue / Appt flow
        appt_flow_revenue_actual_wtd / appt_flows_actual_wtd as rev_per_appt_flow_wtd,
        appt_flow_revenue_forecast_wtd / appt_flows_forecast_wtd as rev_per_appt_flow_forecast_wtd,
        (appt_flow_revenue_actual_wtd / appt_flows_actual_wtd) - (appt_flow_revenue_forecast_wtd / appt_flows_forecast_wtd) as rev_per_appt_flow_wtd_variance,
        (
            (appt_flow_revenue_actual_wtd / appt_flows_actual_wtd)
            - (appt_flow_revenue_forecast_wtd / appt_flows_forecast_wtd)
        ) / (
            appt_flow_revenue_forecast_wtd / appt_flows_forecast_wtd
        ) as rev_per_appt_flow_wtd_variance_pct,
        appt_flow_revenue_actual_mtd / appt_flows_actual_mtd as rev_per_appt_flow_mtd,
        appt_flow_revenue_forecast_mtd / appt_flows_forecast_mtd as rev_per_appt_flow_forecast_mtd,
        (appt_flow_revenue_actual_mtd / appt_flows_actual_mtd) - (appt_flow_revenue_forecast_mtd / appt_flows_forecast_mtd) as rev_per_appt_flow_mtd_variance,
        (
            (appt_flow_revenue_actual_mtd / appt_flows_actual_mtd)
            - (appt_flow_revenue_forecast_mtd / appt_flows_forecast_mtd)
        ) / (
            appt_flow_revenue_forecast_mtd / appt_flows_forecast_mtd
        ) as rev_per_appt_flow_mtd_variance_pct,
        appt_flow_revenue_actual_prior_month / appt_flows_actual_prior_month as rev_per_appt_flow_prior_month,
        appt_flow_revenue_actual_t3m_avg / appt_flows_actual_t3m_avg as rev_per_appt_flow_t3m_avg,

        -- Completed Appointments
        completed_appointments_actual_wtd,
        completed_appointments_forecast_wtd,
        completed_appointments_actual_wtd - completed_appointments_forecast_wtd as completed_appointments_wtd_variance,
        (completed_appointments_actual_wtd - completed_appointments_forecast_wtd) / completed_appointments_forecast_wtd as completed_appointments_wtd_variance_pct,
        completed_appointments_actual_mtd,
        completed_appointments_forecast_mtd,
        completed_appointments_actual_mtd - completed_appointments_forecast_mtd as completed_appointments_mtd_variance,
        (completed_appointments_actual_mtd - completed_appointments_forecast_mtd) / completed_appointments_forecast_mtd as completed_appointments_mtd_variance_pct,
        completed_appointments_actual_prior_month,
        completed_appointments_actual_t3m_avg,

        -- Completed Appointments / Appt Flow
        completed_appointments_actual_wtd / appt_flows_actual_wtd as appts_per_appt_flow_wtd,
        completed_appointments_forecast_wtd / appt_flows_forecast_wtd as appts_per_appt_flow_forecast_wtd,
        (completed_appointments_actual_wtd / appt_flows_actual_wtd) - (completed_appointments_forecast_wtd / appt_flows_forecast_wtd) as appts_per_appt_flow_wtd_variance,
        (
            (completed_appointments_actual_wtd / appt_flows_actual_wtd)
            - (completed_appointments_forecast_wtd / appt_flows_forecast_wtd)
        ) / (
            completed_appointments_forecast_wtd / appt_flows_forecast_wtd
        ) as appts_per_appt_flow_wtd_variance_pct,
        completed_appointments_actual_mtd / appt_flows_actual_mtd as appts_per_appt_flow_mtd,
        completed_appointments_forecast_mtd / appt_flows_forecast_mtd as appts_per_appt_flow_forecast_mtd,
        (completed_appointments_actual_mtd / appt_flows_actual_mtd) - (completed_appointments_forecast_mtd / appt_flows_forecast_mtd) as appts_per_appt_flow_mtd_variance,
        (
            (completed_appointments_actual_mtd / appt_flows_actual_mtd)
            - (completed_appointments_forecast_mtd / appt_flows_forecast_mtd)
        ) / (
            completed_appointments_forecast_mtd / appt_flows_forecast_mtd
        ) as appts_per_appt_flow_mtd_variance_pct,
        completed_appointments_actual_prior_month / appt_flows_actual_prior_month as appts_per_appt_flow_prior_month,
        completed_appointments_actual_t3m_avg / appt_flows_actual_t3m_avg as appts_per_appt_flow_t3m_avg,

        -- Appointment Flow ATC
        appt_flow_revenue_actual_wtd / completed_appointments_actual_wtd as appt_flow_atc_actual_wtd,
        appt_flow_revenue_forecast_wtd / completed_appointments_forecast_wtd as appt_flow_atc_forecast_wtd,
        (appt_flow_revenue_actual_wtd / completed_appointments_actual_wtd) - (appt_flow_revenue_forecast_wtd / completed_appointments_forecast_wtd) as appt_flow_atc_wtd_variance,
        (
            (appt_flow_revenue_actual_wtd / completed_appointments_actual_wtd)
            - (appt_flow_revenue_forecast_wtd / completed_appointments_forecast_wtd)
        ) / (
            appt_flow_revenue_forecast_wtd / completed_appointments_forecast_wtd
        ) as appt_flow_atc_wtd_variance_pct,
        appt_flow_revenue_actual_mtd / completed_appointments_actual_mtd as appt_flow_atc_actual_mtd,
        appt_flow_revenue_forecast_mtd / completed_appointments_forecast_mtd as appt_flow_atc_forecast_mtd,
        (appt_flow_revenue_actual_mtd / completed_appointments_actual_mtd) - (appt_flow_revenue_forecast_mtd / completed_appointments_forecast_mtd) as appt_flow_atc_mtd_variance,
        (
            (appt_flow_revenue_actual_mtd / completed_appointments_actual_mtd)
            - (appt_flow_revenue_forecast_mtd / completed_appointments_forecast_mtd)
        ) / (
            appt_flow_revenue_forecast_mtd / completed_appointments_forecast_mtd
        ) as appt_flow_atc_mtd_variance_pct,
        appt_flow_revenue_actual_prior_month / completed_appointments_actual_prior_month as appt_flow_atc_prior_month,
        appt_flow_revenue_actual_t3m_avg / completed_appointments_actual_t3m_avg as appt_flow_atc_t3m_avg,

        -- Procedure Flows
        procedure_flows_actual_wtd,
        procedure_flows_forecast_wtd,
        procedure_flows_actual_wtd - procedure_flows_forecast_wtd as procedure_flows_wtd_variance,
        (procedure_flows_actual_wtd - procedure_flows_forecast_wtd) / procedure_flows_forecast_wtd as procedure_flows_wtd_variance_pct,
        procedure_flows_actual_mtd,
        procedure_flows_forecast_mtd,
        procedure_flows_actual_mtd - procedure_flows_forecast_mtd as procedure_flows_mtd_variance,
        (procedure_flows_actual_mtd - procedure_flows_forecast_mtd) / procedure_flows_forecast_mtd as procedure_flows_mtd_variance_pct,
        procedure_flows_actual_prior_month,
        procedure_flows_actual_t3m_avg,

        -- Procedure Flow Revenue (total)
        procedure_flow_revenue_actual_wtd,
        procedure_flow_revenue_forecast_wtd,
        procedure_flow_revenue_actual_wtd - procedure_flow_revenue_forecast_wtd as procedure_flow_revenue_wtd_variance,
        (procedure_flow_revenue_actual_wtd - procedure_flow_revenue_forecast_wtd) / procedure_flow_revenue_forecast_wtd as procedure_flow_revenue_wtd_variance_pct,
        procedure_flow_revenue_actual_mtd,
        procedure_flow_revenue_forecast_mtd,
        procedure_flow_revenue_actual_mtd - procedure_flow_revenue_forecast_mtd as procedure_flow_revenue_mtd_variance,
        (procedure_flow_revenue_actual_mtd - procedure_flow_revenue_forecast_mtd) / procedure_flow_revenue_forecast_mtd as procedure_flow_revenue_mtd_variance_pct,
        procedure_flow_revenue_actual_prior_month,
        procedure_flow_revenue_actual_t3m_avg,

        -- Revenue / Procedure flow
        procedure_flow_revenue_actual_wtd / procedure_flows_actual_wtd as rev_per_procedure_flow_wtd,
        procedure_flow_revenue_forecast_wtd / procedure_flows_forecast_wtd as rev_per_procedure_flow_forecast_wtd,
        (procedure_flow_revenue_actual_wtd / procedure_flows_actual_wtd) - (procedure_flow_revenue_forecast_wtd / procedure_flows_forecast_wtd) as rev_per_procedure_flow_wtd_variance,
        (
            (procedure_flow_revenue_actual_wtd / procedure_flows_actual_wtd)
            - (procedure_flow_revenue_forecast_wtd / procedure_flows_forecast_wtd)
        ) / (
            procedure_flow_revenue_forecast_wtd / procedure_flows_forecast_wtd
        ) as rev_per_procedure_flow_wtd_variance_pct,

        procedure_flow_revenue_actual_mtd / procedure_flows_actual_mtd as rev_per_procedure_flow_mtd,
        procedure_flow_revenue_forecast_mtd / procedure_flows_forecast_mtd as rev_per_procedure_flow_forecast_mtd,
        (procedure_flow_revenue_actual_mtd / procedure_flows_actual_mtd) - (procedure_flow_revenue_forecast_mtd / procedure_flows_forecast_mtd) as rev_per_procedure_flow_mtd_variance,
        (
            (procedure_flow_revenue_actual_mtd / procedure_flows_actual_mtd)
            - (procedure_flow_revenue_forecast_mtd / procedure_flows_forecast_mtd)
        ) / (
            procedure_flow_revenue_forecast_mtd / procedure_flows_forecast_mtd
        ) as rev_per_procedure_flow_mtd_variance_pct,
        procedure_flow_revenue_actual_prior_month / procedure_flows_actual_prior_month as rev_per_procedure_flow_prior_month,
        procedure_flow_revenue_actual_t3m_avg / procedure_flows_actual_t3m_avg as rev_per_procedure_flow_t3m_avg,

        -- Completed Procedures
        completed_procedures_actual_wtd,
        completed_procedures_forecast_wtd,
        completed_procedures_actual_wtd - completed_procedures_forecast_wtd as completed_procedures_wtd_variance,
        (completed_procedures_actual_wtd - completed_procedures_forecast_wtd) / completed_procedures_forecast_wtd as completed_procedures_wtd_variance_pct,
        completed_procedures_actual_mtd,
        completed_procedures_forecast_mtd,
        completed_procedures_actual_mtd - completed_procedures_forecast_mtd as completed_procedures_mtd_variance,
        (completed_procedures_actual_mtd - completed_procedures_forecast_mtd) / completed_procedures_forecast_mtd as completed_procedures_mtd_variance_pct,
        completed_procedures_actual_prior_month,
        completed_procedures_actual_t3m_avg,

        -- Completed Procedures / Procedure Flow
        completed_procedures_actual_wtd / procedure_flows_actual_wtd as procedures_per_procedure_flow_wtd,
        completed_procedures_forecast_wtd / procedure_flows_forecast_wtd as procedures_per_procedure_flow_forecast_wtd,
        (completed_procedures_actual_wtd / procedure_flows_actual_wtd) - (completed_procedures_forecast_wtd / procedure_flows_forecast_wtd) as procedures_per_procedure_flow_wtd_variance,
        (
            (completed_procedures_actual_wtd / procedure_flows_actual_wtd)
            - (completed_procedures_forecast_wtd / procedure_flows_forecast_wtd)
        ) / (
            completed_procedures_forecast_wtd / procedure_flows_forecast_wtd
        ) as procedures_per_procedure_flow_wtd_variance_pct,
        completed_procedures_actual_mtd / procedure_flows_actual_mtd as procedures_per_procedure_flow_mtd,
        completed_procedures_forecast_mtd / procedure_flows_forecast_mtd as procedures_per_procedure_flow_forecast_mtd,
        (completed_procedures_actual_mtd / procedure_flows_actual_mtd) - (completed_procedures_forecast_mtd / procedure_flows_forecast_mtd) as procedures_per_procedure_flow_mtd_variance,
        (
            (completed_procedures_actual_mtd / procedure_flows_actual_mtd)
            - (completed_procedures_forecast_mtd / procedure_flows_forecast_mtd)
        ) / (
            completed_procedures_forecast_mtd / procedure_flows_forecast_mtd
        ) as procedures_per_procedure_flow_mtd_variance_pct,
        completed_procedures_actual_prior_month / procedure_flows_actual_prior_month as procedures_per_procedure_flow_prior_month,
        completed_procedures_actual_t3m_avg / procedure_flows_actual_t3m_avg as procedures_per_procedure_flow_t3m_avg,

        -- Procedure Flow ATC
        procedure_flow_revenue_actual_wtd / completed_procedures_actual_wtd as procedure_flow_atc_actual_wtd,
        procedure_flow_revenue_forecast_wtd / completed_procedures_forecast_wtd as procedure_flow_atc_forecast_wtd,
        (procedure_flow_revenue_actual_wtd / completed_procedures_actual_wtd) - (procedure_flow_revenue_forecast_wtd / completed_procedures_forecast_wtd) as procedure_flow_atc_wtd_variance,
        (
            (procedure_flow_revenue_actual_wtd / completed_procedures_actual_wtd)
            - (procedure_flow_revenue_forecast_wtd / completed_procedures_forecast_wtd)
        ) / (
            procedure_flow_revenue_forecast_wtd / completed_procedures_forecast_wtd
        ) as procedure_flow_atc_wtd_variance_pct,

        procedure_flow_revenue_actual_mtd / completed_procedures_actual_mtd as procedure_flow_atc_actual_mtd,
        
        procedure_flow_revenue_forecast_mtd / completed_procedures_forecast_mtd as procedure_flow_atc_forecast_mtd,
        (procedure_flow_revenue_actual_mtd / completed_procedures_actual_mtd) - (procedure_flow_revenue_forecast_mtd / completed_procedures_forecast_mtd) as procedure_flow_atc_mtd_variance,
        (
            (procedure_flow_revenue_actual_mtd / completed_procedures_actual_mtd)
            - (procedure_flow_revenue_forecast_mtd / completed_procedures_forecast_mtd)
        ) / (
            procedure_flow_revenue_forecast_mtd / completed_procedures_forecast_mtd
        ) as procedure_flow_atc_mtd_variance_pct,
        procedure_flow_revenue_actual_prior_month / completed_procedures_actual_prior_month as procedure_flow_atc_prior_month,
        procedure_flow_revenue_actual_t3m_avg / completed_procedures_actual_t3m_avg as procedure_flow_atc_t3m_avg,

        -- Procedure Point Utilization
        procedure_point_utilization_actual_wtd,
        procedure_points_booked_actual_wtd,
        procedural_capacity_actual_wtd,
        procedure_point_utilization_forecast_wtd,
        procedure_point_utilization_actual_wtd - procedure_point_utilization_forecast_wtd as procedure_point_utilization_wtd_variance,
        (procedure_point_utilization_actual_wtd - procedure_point_utilization_forecast_wtd) / procedure_point_utilization_forecast_wtd as procedure_point_utilization_wtd_variance_pct,
        procedure_point_utilization_actual_mtd,
        procedure_points_booked_actual_mtd,
        procedural_capacity_actual_mtd,
        procedure_point_utilization_forecast_mtd,
        procedure_point_utilization_actual_mtd - procedure_point_utilization_forecast_mtd as procedure_point_utilization_mtd_variance,
        (procedure_point_utilization_actual_mtd - procedure_point_utilization_forecast_mtd) / procedure_point_utilization_forecast_mtd as procedure_point_utilization_mtd_variance_pct,
        procedure_point_utilization_actual_prior_month,
        procedure_points_booked_actual_prior_month,
        procedural_capacity_actual_prior_month,
        procedure_point_utilization_actual_t3m_avg,
        procedure_points_booked_actual_t3m_avg,
        procedural_capacity_actual_t3m_avg
    from base
    order by 2, 1)
    ),

    flows as (
        select
            'Completed Flows',
            'num0' as fmt,
            'pct0' as fmt2,
            sum(total_flows_wtd) as actual_wtd,
            sum(total_flows_forecast_wtd) as forecast_wtd,
            sum(total_flows_wtd) - sum(total_flows_forecast_wtd) as variance_wtd,
            (sum(total_flows_wtd) - sum(total_flows_forecast_wtd)) / nullif(sum(total_flows_forecast_wtd), 0) as variance_pct_wtd,
            sum(total_flows_mtd) as actual_mtd,
            sum(total_flows_forecast_mtd) as forecast_mtd,
            sum(total_flows_actual_prior_month) as actual_prior_month,
            sum(total_flows_actual_t3m_avg) as actual_t3m_avg
        from flow_metrics
        group by 1, 2, 3
    ),

    rev_per_appt_flow as (
        select
            'Revenue / Appt Flow' as metric,
            'usd0' as fmt1,
            'pct0' as fmt2,
            sum(appt_flow_revenue_actual_wtd) / sum(appt_flows_actual_wtd) as actual_wtd,
            sum(appt_flow_revenue_forecast_wtd) / sum(appt_flows_forecast_wtd) as forecast_wtd,
            (
                (sum(appt_flow_revenue_actual_wtd) / sum(appt_flows_actual_wtd))
                - (sum(appt_flow_revenue_forecast_wtd) / sum(appt_flows_forecast_wtd))
            ) as variance_wtd,
            (
                (
                    (sum(appt_flow_revenue_actual_wtd) / sum(appt_flows_actual_wtd))
                    - (sum(appt_flow_revenue_forecast_wtd) / sum(appt_flows_forecast_wtd))
                ) / nullif(
                    sum(appt_flow_revenue_forecast_wtd) / sum(appt_flows_forecast_wtd),
                    0)
            ) as variance_pct_wtd,
            sum(appt_flow_revenue_actual_mtd) / sum(appt_flows_actual_mtd) as actual_mtd,
            sum(appt_flow_revenue_forecast_mtd) / sum(appt_flows_forecast_mtd) as forecast_mtd,
            sum(appt_flow_revenue_actual_prior_month) / sum(appt_flows_actual_prior_month) as actual_prior_month,
            sum(appt_flow_revenue_actual_t3m_avg) / sum(appt_flows_actual_t3m_avg) as actual_t3m_avg
        from flow_metrics
        group by 1, 2, 3
    ),

    rev_per_procedure_flow as (
        select
            'Revenue / Procedure Flow' as metric,
            'usd0' as fmt1,
            'pct0' as fmt2,
            sum(procedure_flow_revenue_actual_wtd) / sum(procedure_flows_actual_wtd) as actual_wtd,
            sum(procedure_flow_revenue_forecast_wtd) / sum(procedure_flows_forecast_wtd) as forecast_wtd,
            (
                sum(procedure_flow_revenue_actual_wtd) / sum(procedure_flows_actual_wtd)
                - sum(procedure_flow_revenue_forecast_wtd) / sum(procedure_flows_forecast_wtd)
            ) as variance_wtd,
            (
                (
                    sum(procedure_flow_revenue_actual_wtd) / sum(procedure_flows_actual_wtd)
                    - sum(procedure_flow_revenue_forecast_wtd) / sum(procedure_flows_forecast_wtd)
                ) / nullif(
                    sum(procedure_flow_revenue_forecast_wtd) / sum(procedure_flows_forecast_wtd),
                    0
                )
             ) as variance_pct_wtd,
            sum(procedure_flow_revenue_actual_mtd) / sum(procedure_flows_actual_mtd) as actual_mtd,
            sum(procedure_flow_revenue_forecast_mtd) / sum(procedure_flows_forecast_mtd) as forecast_mtd,
            sum(procedure_flow_revenue_actual_prior_month) / sum(procedure_flows_actual_prior_month) as actual_prior_month,
            sum(procedure_flow_revenue_actual_t3m_avg) / sum(procedure_flows_actual_t3m_avg) as actual_t3m_avg
        from flow_metrics
        group by 1, 2, 3
    ),

    appt_utilization as (
        select
            'Appts / Flow' as metric,
            'num0' as fmt1,
            'pct0' as fmt2,
            sum(completed_appointments_actual_wtd) / sum(appt_flows_actual_wtd) as actual_wtd,
            sum(completed_appointments_forecast_wtd) / sum(appt_flows_forecast_wtd) as forecast_wtd,
            (
                sum(completed_appointments_actual_wtd) / sum(appt_flows_actual_wtd)
                - sum(completed_appointments_forecast_wtd) / sum(appt_flows_forecast_wtd)
            ) as variance_wtd,
            (
                (
                    sum(completed_appointments_actual_wtd) / sum(appt_flows_actual_wtd)
                    - sum(completed_appointments_forecast_wtd) / sum(appt_flows_forecast_wtd)
                ) / nullif(
                    sum(completed_appointments_forecast_wtd) / sum(appt_flows_forecast_wtd),
                    0
                )
            ) as variance_pct_wtd,
            sum(completed_appointments_actual_mtd) / sum(appt_flows_actual_mtd) as actual_mtd,
            sum(completed_appointments_forecast_mtd) / sum(appt_flows_forecast_mtd) as forecast_mtd,
            sum(completed_appointments_actual_prior_month) / sum(appt_flows_actual_prior_month) as actual_prior_month,
            sum(completed_appointments_actual_t3m_avg) / sum(appt_flows_actual_t3m_avg) as actual_t3m_avg
        from flow_metrics
        group by 1, 2, 3
    ),

    procedure_points_booked as (
        select
            'Procedure Point Utilization' as metric,
            'pct0' as fmt1,
            'pct0' as fmt2,
            sum(procedure_points_booked_actual_wtd) / sum(procedural_capacity_actual_wtd) as actual_wtd,
            0.9 as forecast_wtd,
            (sum(procedure_points_booked_actual_wtd) / sum(procedural_capacity_actual_wtd)) - 0.9 as variance_wtd,
            (
                (sum(procedure_points_booked_actual_wtd) / sum(procedural_capacity_actual_wtd) - 0.9)
                / 0.9
            ) as variance_pct_wtd,
            sum(procedure_points_booked_actual_mtd) / sum(procedural_capacity_actual_mtd) as actual_mtd,
            0.9 as forecast_mtd,
            sum(procedure_points_booked_actual_prior_month) / sum(procedural_capacity_actual_prior_month) as actual_prior_month,
            sum(procedure_points_booked_actual_t3m_avg) / sum(procedural_capacity_actual_t3m_avg) as actual_t3m_avg
        from flow_metrics
        group by 1, 2, 3
    ),

    appt_atc as (
        select
            'Appt Flow ATC' as metric,
            'usd1' as fmt1,
            'pct0' as fmt2,
            sum(appt_flow_revenue_actual_wtd) / sum(completed_appointments_actual_wtd) as actual_wtd,
            sum(appt_flow_revenue_forecast_wtd) / sum(completed_appointments_forecast_wtd) as forecast_wtd,
            (
                (sum(appt_flow_revenue_actual_wtd) / sum(completed_appointments_actual_wtd))
                - (sum(appt_flow_revenue_forecast_wtd) / sum(completed_appointments_forecast_wtd))
            ) as variance_wtd,
            (
                (
                    (sum(appt_flow_revenue_actual_wtd) / sum(completed_appointments_actual_wtd))
                    - (sum(appt_flow_revenue_forecast_wtd) / sum(completed_appointments_forecast_wtd))
                ) / nullif(
                    sum(appt_flow_revenue_forecast_wtd) / sum(completed_appointments_forecast_wtd),
                    0
                ) 
            ) as variance_pct_wtd,
            sum(appt_flow_revenue_actual_mtd) / sum(completed_appointments_actual_mtd) as actual_mtd,
            sum(appt_flow_revenue_forecast_mtd) / sum(completed_appointments_forecast_mtd) as forecast_mtd,
            sum(appt_flow_revenue_actual_prior_month) / sum(completed_appointments_actual_prior_month) as actual_prior_month,
            sum(appt_flow_revenue_actual_t3m_avg) / sum(completed_appointments_actual_t3m_avg) as actual_t3m_avg
        from flow_metrics
        group by 1, 2, 3
    ),
    
    procedure_atc as (
        select
            'Procedure Flow ATC' as metric,
            'usd0' as fmt1,
            'pct0' as fmt2,
            sum(procedure_flow_revenue_actual_wtd) / sum(completed_procedures_actual_wtd) as actual_wtd,
            sum(procedure_flow_revenue_forecast_wtd) / sum(completed_procedures_forecast_wtd) as forecast_wtd,
            (
                sum(procedure_flow_revenue_actual_wtd) / sum(completed_procedures_actual_wtd)
                - sum(procedure_flow_revenue_forecast_wtd) / sum(completed_procedures_forecast_wtd)
            ) as variance_wtd,
            (
                (
                    sum(procedure_flow_revenue_actual_wtd) / sum(completed_procedures_actual_wtd)
                    - sum(procedure_flow_revenue_forecast_wtd) / sum(completed_procedures_forecast_wtd)
                ) / nullif(
                    (sum(procedure_flow_revenue_forecast_wtd) / sum(completed_procedures_forecast_wtd)),
                    0
                )
            ) as variance_pct_wtd,
            sum(procedure_flow_revenue_actual_mtd) / sum(completed_procedures_actual_mtd) as actual_mtd,
            sum(procedure_flow_revenue_forecast_mtd) / sum(completed_procedures_forecast_mtd) as forecast_mtd,
            sum(procedure_flow_revenue_actual_prior_month) / sum(completed_procedures_actual_prior_month) as actual_prior_month,
            sum(procedure_flow_revenue_actual_t3m_avg) / sum(completed_procedures_actual_t3m_avg) as actual_t3m_avg
        from flow_metrics
        group by 1, 2, 3
    )

    select * from ops_rev
    union
    select * from flows
    union
    select * from rev_per_appt_flow
    union
    select * from rev_per_procedure_flow
    union
    select * from appt_utilization
    union
    select * from procedure_points_booked
    union
    select * from appt_atc
    union
    select * from procedure_atc

It's not a problem per se, but it feels like it should throw an error that the user can understand

"Your query contains too many characters"

[chapeaupaul]

Hello, do you have any visibility on this error? Do you know if a fix will be released soon? Thank you.

[rudnik1991]

@carlopi Hey! Do you think this will be fixed any time soon?

[prusswan]

I'm getting this on Chrome (but not Firefox) with dev258 build:

[hamilton]

@ankrgyl I believe your original post no longer reproduces now thanks to this fix.

[ankrgyl]

Confirmed. I'll hold off on closing given the other related concerns. But confirmed my repro has been solved 🙏

[hamilton]

@archiewood does your query now work w/ the latest duckdb-wasm?

[archiewood]

We haven't upgraded yet, and i don't have a minimal repro.

Will be upgrading next week I think.