keyword with causing "column does not exist"

[kubik51]

Version

1.12.0

What happened?

I wanted to make an update query that also returns some more detailed data from other tables. The update query itself works fine, but the "WITH" statement causes the "column does not exist" error during code generation in already tested update query.

Relevant log output

No response

Database schema

create table attribute_value
(
    id              bigserial not null,
    val             text      not null,
    attribute       bigint    not null
);

create table attribute
(
    id              bigserial      not null,
    name            text           not null
);

SQL queries

-- name: UpdateAttributeValueDoesntWork :one
with updated_attribute as (UPDATE attribute_value
    SET
        val = CASE WHEN @filter_value::bool THEN @value ELSE val END
    WHERE attribute_value.id = @id
    RETURNING id,attribute,val)
select updated_attribute.id, val, name
from updated_attribute
         left join attribute on updated_attribute.attribute = attribute.id;
		 
-- name: UpdateAttributeValueWorks :one
UPDATE attribute_value
    SET
        val = CASE WHEN @filter_value::bool THEN @value ELSE val END
    WHERE attribute_value.id = @id
    RETURNING id,attribute,val;

Configuration

No response

Playground URL

https://play.sqlc.dev/p/ba79ccad12759729ce4f260f70f3250de323a0050d2172e53d06f5ec1f83ee0d

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[DarkHeros09]

Hi @kubik51 i just wrote my own sql query with "WITH" keyword, and it worked as intended no error.

i know its not helpful, but maybe its something else.

[kubik51]

@DarkHeros09 hmmm... it could be something else. Its just, the "WITH" query itself is valid and the update query inside it is also valid. Maybe its the combination of database structure and the "WITH" query syntax.

[DarkHeros09]

@kubik51 did you test your query on the database using sql editor before generating it?

[kubik51]

@DarkHeros09 yes I did. The weird part about it is that when I try to SET any other table column instead of "val" with the same query, the code generator works fine. ( Yes I triple checked the database and the db schema script, also tried to rename the column )

[DarkHeros09]

@kubik51 hmm if the query worked on the sql editor, then there's definitely an issue here. sorry that i can't help you any further, but i hope someone will.

Best of luck.

[DarkHeros09]

one last thing try using double quotes on val --> "val"

-- name: UpdateAttributeValueDoesntWork :one
WITH updated_attribute AS (
UPDATE attribute_value
    SET
        "val" = CASE WHEN @filter_value::bool THEN @value ELSE "val" END
    WHERE attribute_value.id = @id
    RETURNING id,attribute,"val")
select updated_attribute.id, "val", name
from updated_attribute
         left join attribute on updated_attribute.attribute = attribute.id;

[kubik51]

@DarkHeros09 tried that too :| still the same error message. Anyway, thank you for your effort.

[peteole]

Got a similar issue here: I've got a definition for a view that makes sqlc fail:

CREATE VIEW public.projects_ranked AS
 SELECT project_saves.id,
    project_saves.name,
    project_saves.description,
    project_saves.created_at,
    project_saves.creator,
    project_saves.location,
    project_saves.twitter_account,
    project_saves.public,
    project_saves.last_description_update,
    project_saves.save_count,
    project_saves.project_id,
    update_timestamps.last_update_timestamp,
    GREATEST(project_saves.created_at, update_timestamps.last_update_timestamp) AS last_change,
    (sqrt((COALESCE(project_saves.save_count, (0)::bigint))::double precision) + COALESCE(((4)::double precision * exp(((('-0.1'::numeric)::double precision * date_part('epoch'::text, age(GREATEST(project_saves.last_description_update, update_timestamps.last_update_timestamp)))) / (((60 * 60) * 24))::double precision))), (0)::double precision)) AS score
   FROM ((public.projects
     LEFT JOIN ( SELECT count(*) AS save_count,
            project_saves_1.project_id
           FROM public.project_saves project_saves_1
          GROUP BY project_saves_1.project_id) save_counts ON ((projects.id = save_counts.project_id))) project_saves
     LEFT JOIN ( SELECT max(updates."timestamp") AS last_update_timestamp,
            updates.project_id
           FROM public.updates
          GROUP BY updates.project_id) update_timestamps ON ((update_timestamps.project_id = project_saves.id)))
  WHERE (project_saves.public = true);

Here the "projects" table should have the "id" property (and therefore also "project-saves"), but sqlc generate throws an error:

# package sqlc
schema.sql:178:9: column "id" does not exist

Note that the view itself works perfectly in postgres. Might this be a similar issue since both have to do with a missing column in a "virtual table" (subselect or view)?

[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