Non-existant column in INSERT INTO compiles instead of erroring out

[skariel]

The following query asks to insert to a non-existing field is_private yet it compiles without error:

CREATE SCHEMA td4;

CREATE TABLE td4.test_codes (
    id SERIAL PRIMARY KEY,

    created_by text     NOT NULL,
    updated_by text     NOT NULL,
    test_code_id SERIAL NOT NULL,
    code text           NOT NULL,

    total_pass integer NOT NULL DEFAULT 0,
    total_fail integer NOT NULL DEFAULT 0,
    total_wip  integer NOT NULL DEFAULT 0,
    total_pending integer NOT NULL DEFAULT 0
);

-- name: InsertSolutionCode :one
INSERT INTO td4.test_codes(created_by, updated_by, test_code_id, code, is_private)
VALUES ($1, $1, $2, $3, false)
RETURNING *;

https://play.sqlc.dev/p/4b81a695a126e4a21cdfe0af6527d001902fdf3068b0b5b09d1c3d4d265b7701

[kyleconroy]

@skariel Updated your example to be self-contained. I can confirm this is this still an issue.

[andrewmbenton]

The issue here appears to be that sqlc doesn't validate inserted columns exist unless the value is parameterized. In other words, specifying a parameter of $4 instead of false for is_private does produce the expected error: https://play.sqlc.dev/p/c37f92a32113ab50582b342de14c75d92b4f28d841e6fc84c0bcf914a5d45f77

[kyleconroy]

This is fixed in v1.23.0 by enabling the database-backed query analyzer. We added a test case for this issue so it won’t break in the future.

You can play around with the working example on the playground