Column with alias "does not exist"

verygoodsoftwarenotvirus commented 1 year ago

Version

1.16.0

What happened?

When compiling a query with a column alias, sqlc complains that the column "does not exist"

Relevant log output

docker run --rm --volume `pwd`:/src --workdir /src kjconroy/sqlc:1.16.0 compile
# package generated
internal/database/postgres/queries/webhooks/get_many.sql:16:2: column "filtered_count" does not exist

Database schema

CREATE TABLE IF NOT EXISTS webhooks (
    "id" CHAR(27) NOT NULL PRIMARY KEY,
    "name" TEXT NOT NULL,
    "content_type" TEXT NOT NULL,
    "url" TEXT NOT NULL,
    "method" TEXT NOT NULL,
    "created_at" BIGINT NOT NULL DEFAULT extract(epoch FROM NOW()),
    "last_updated_at" BIGINT DEFAULT NULL,
    "archived_at" BIGINT DEFAULT NULL,
    "belongs_to_household" CHAR(27) NOT NULL REFERENCES households("id") ON DELETE CASCADE
);

CREATE TYPE webhook_event AS ENUM (
    'webhook_created',
    'webhook_updated',
    'webhook_archived'
);

CREATE TABLE IF NOT EXISTS webhook_trigger_events (
    "id" CHAR(27) NOT NULL PRIMARY KEY,
    "trigger_event" webhook_event NOT NULL,
    "belongs_to_webhook" CHAR(27) NOT NULL REFERENCES webhooks("id") ON DELETE CASCADE,
    "created_at" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    "archived_at" TIMESTAMP WITH TIME ZONE,
    UNIQUE("trigger_event", "belongs_to_webhook")
);

SQL queries

SELECT
    webhooks.id,
    webhooks.name,
    webhooks.content_type,
    webhooks.url,
    webhooks.method,
    webhook_trigger_events.id,
    webhook_trigger_events.trigger_event,
    webhook_trigger_events.belongs_to_webhook,
    webhook_trigger_events.created_at,
    webhook_trigger_events.archived_at,
    webhooks.created_at,
    webhooks.last_updated_at,
    webhooks.archived_at,
    webhooks.belongs_to_household,
    (
        SELECT
            COUNT(webhooks.id)
        FROM
            webhooks
        WHERE
            webhooks.archived_at IS NULL
          AND webhooks.created_at > COALESCE($2, (SELECT NOW() - interval '999 years'))
          AND webhooks.created_at < COALESCE($3, (SELECT NOW() + interval '999 years'))
          AND (
                webhooks.last_updated_at IS NULL
                OR webhooks.last_updated_at > COALESCE($4, (SELECT NOW() - interval '999 years'))
            )
          AND (
                webhooks.last_updated_at IS NULL
                OR webhooks.last_updated_at < COALESCE($5, (SELECT NOW() + interval '999 years'))
            )
        OFFSET COALESCE($6, 0)
    ) as filtered_count,
    (
        SELECT
            COUNT(webhooks.id)
        FROM
            webhooks
        WHERE
            webhooks.archived_at IS NULL
    ) as total_count
FROM
    webhook_trigger_events
        JOIN webhooks ON webhook_trigger_events.belongs_to_webhook = webhooks.id
WHERE
    webhooks.archived_at IS NULL
    AND webhook_trigger_events.archived_at IS NULL
    AND webhooks.created_at > COALESCE($2, (SELECT NOW() - interval '999 years'))
    AND webhooks.created_at < COALESCE($3, (SELECT NOW() + interval '999 years'))
    AND (
        webhooks.last_updated_at IS NULL
        OR webhooks.last_updated_at > COALESCE($4, (SELECT NOW() - interval '999 years'))
    )
    AND (
        webhooks.last_updated_at IS NULL
        OR webhooks.last_updated_at < COALESCE($5, (SELECT NOW() + interval '999 years'))
    )
    AND webhooks.belongs_to_household = $1
    OFFSET COALESCE($6, 0);

Configuration

version: "2"
sql:
  - schema: "internal/database/postgres/migrations"
    queries:
      - "internal/database/postgres/queries/webhooks"
    engine: "postgresql"
    # strict_function_checks: true
    gen:
      go:
        package: "generated"
        sql_package: "database/sql"
        out: "internal/database/postgres/generated"
        output_files_suffix: "_generated"
        emit_db_tags: true
        emit_prepared_queries: false
        emit_interface: true
        emit_exact_table_names: true
        emit_empty_slices: true
        emit_exported_queries: true
        emit_json_tags: false
        emit_result_struct_pointers: true
        emit_params_struct_pointers: true
        emit_methods_with_db_argument: true
        emit_enum_valid_method: true
        emit_all_enum_values: false

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

richchurcher commented 1 year ago

Here's a playground link demonstrating the issue (with the _on column names fixed): https://play.sqlc.dev/p/a831b4c0caeacd05926392493afed3442b411f279473ecc9c2c67b8f4df23abc

mikemherron commented 1 year ago

Interestingly, as I was trying to reduce the above playground to the simplest possible repro, this variation does not cause the same error:

https://play.sqlc.dev/p/4430ca5fc6dad623727d2f1ec9d5fccd1217b1833e9dc95ba62b5c2cc9dca298

The only change here is the simplification of the filtered_count sub query as well as fixing another on/at issue. Going to dig in to a bit more but wanted to post the above incase it helps someone spot the issue.

mikemherron commented 1 year ago

It looks like replacing the query param placeholders with string literals in the original query also prevents the error:

https://play.sqlc.dev/p/6aa5177a06e1b2e21d46e3474c26d80debb6d5428687ddcdae166ff280720578

mikemherron commented 1 year ago

OK, I think the issue is actually with this line here:

OFFSET COALESCE($6, 0)

It appears twice, in the inner query and at the end of the query.

Replace $6 with test in both places, and everything is fine
Replace $6 with test in the inner query and sqlc fails with query.sql:45:2: column "filtered_count" does not exist
Replace $6 with test at the end of the query and sqlc fails with unsupported reference type: <nil>

Remove the COALESCE entirely and sqlc runs wihthout any problems.

mikemherron commented 1 year ago

Spent some more time looking at this but think I will have to duck out of this one, this area of the code is beyond my understanding at the moment.

I managed to create a minimal repro that can be added as a sqlc end to end test, and identified the place in find_params.go I think needs to be updated to correctly capture the param from the COALESCE:

diff --git a/internal/compiler/find_params.go b/internal/compiler/find_params.go
index 62eb2fb0..73f6898f 100644
--- a/internal/compiler/find_params.go
+++ b/internal/compiler/find_params.go
@@ -161,6 +161,19 @@ func (p paramSearch) Visit(node ast.Node) astutils.Visitor {
                                parent = &limitOffset{}
                        }
                }
+
+               //OFFSET has been specified using COALESCE
+               if coalesce, ok := p.limitOffset.(*ast.CoalesceExpr); ok {
+                       for _, arg := range coalesce.Args.Items {
+                               if argParam, isArgParam := arg.(*ast.ParamRef); isArgParam {
+                                       if n.Number == argParam.Number {
+                                               //Set parent to....what?
+                                               break
+                                       }
+                               }
+                       }
+               }
+
                if _, found := p.seen[n.Location]; found {
                        break
                }

It also seems like there might an assumption in the paramSearch struct that there is only 1 limit / offset per query? Perhaps this is what was causing the difference in behaviour depending on what OFFSET was changed in my comment above.

I'll leave this here and perhaps someone else with better knowledge of the sqlc internals can pick it up - or if someone can give me a few hints / pointers I'll glady give it another shot.

verygoodsoftwarenotvirus commented 1 year ago

Being the original reporter of this issue, and seeing not just how wrong my initial sql was, but also that OFFSET NULL is actually valid postgres, I'm willing to chalk this up to: "verygoodsoftwarenotvirus is not very good at writing SQL" if it means taking this headache off everybody's plate.

sqlc-dev / sqlc