Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sqlc generate error: table alias "amounts" does not exist #1990

Closed
sxwebdev opened this issue Dec 7, 2022 · 1 comment
Closed

sqlc generate error: table alias "amounts" does not exist #1990

sxwebdev opened this issue Dec 7, 2022 · 1 comment

Comments

@sxwebdev
Copy link

sxwebdev commented Dec 7, 2022

Version

1.15.0

What happened?

This sql query does not work.

I am getting the following error:

table alias "amounts" does not exist

Relevant log output

table alias "amounts" does not exist

Database schema

CREATE TABLE IF NOT EXISTS wallets
(
    id uuid NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
    address VARCHAR(44) NOT NULL,
    type VARCHAR(44) NOT NULL,
    balance DECIMAL(32, 18) NULL
);

SQL queries

-- name: FindWallets :many
select id, address, balance, total_balance from 
(
	select id, address, balance,
	  sum(balance) over (order by balance desc rows between unbounded preceding and current row) as total_balance,
	  sum(balance) over (order by balance desc rows between unbounded preceding and current row) - balance as last_balance
	from wallets
	where type=$1
) amounts
where amounts.last_balance < $2;

Configuration

version: 2
sql:
  - schema: "sql/migrations"
    queries: "sql/queries"
    engine: "postgresql"
    gen:
      go:
        sql_package: "pgx/v4"
        out: "internal/store"
        emit_prepared_queries: false
        emit_json_tags: true
        emit_exported_queries: false
        emit_db_tags: true
        emit_interface: true
        emit_exact_table_names: false
        emit_empty_slices: true
        emit_result_struct_pointers: true
        emit_params_struct_pointers: false
        emit_enum_valid_method: true
        emit_all_enum_values: true
        overrides:
          - go_type: "github.com/shopspring/decimal.Decimal"
            db_type: "pg_catalog.numeric"
          - go_type: "github.com/shopspring/decimal.NullDecimal"
            db_type: "pg_catalog.numeric"
            nullable: true

Playground URL

https://play.sqlc.dev/p/3011dd28fdcc2ee1e3c0c02e19f0fae102e794177017135f63500e43ee0910fe

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

@sxwebdev sxwebdev added bug Something isn't working triage New issues that hasn't been reviewed labels Dec 7, 2022
@andrewmbenton andrewmbenton added 📚 postgresql 🔧 golang 💻 darwin and removed triage New issues that hasn't been reviewed labels Jun 6, 2023
kyleconroy added a commit that referenced this issue Oct 17, 2023
kyleconroy added a commit that referenced this issue Oct 18, 2023
* fix(compiler): Pull in array information from analyzer
Fixes #1532
* test(analyzer): Add testcase for #1574
* test: Added test for #1634
* test: Add test case for #1646
* test: Add test for #1714
* Fixes #1912
* test: Add case for #1916
* test: Add two test cases
#1917
#1545
* test: Add case for #1979
* test: Add case for #1990
@kyleconroy
Copy link
Collaborator

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants