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

Unnest into alias doesn't define the column #1596

Closed
Jille opened this issue May 9, 2022 · 6 comments
Closed

Unnest into alias doesn't define the column #1596

Jille opened this issue May 9, 2022 · 6 comments

Comments

@Jille
Copy link
Contributor

Jille commented May 9, 2022

Version

1.12.0

What happened?

I guess the parser doesn't understand that the alias after UNNEST defines a column, so when later trying to determine the type of the column, it thinks the column doesn't exist.

Relevant log output

query.sql:2:8: column "x" does not exist

Database schema

No response

SQL queries

-- name: FromUnnest :many
SELECT x FROM UNNEST(array[5,6]) x;

Configuration

No response

Playground URL

https://play.sqlc.dev/p/01ee6a49034c018482526ca75aa29cf93aa447d3d93417543266cd4a6d1577b0

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

@Jille Jille added bug Something isn't working triage New issues that hasn't been reviewed labels May 9, 2022
@Jille
Copy link
Contributor Author

Jille commented May 9, 2022

$ SQLCDEBUG=dumpast=1 sqlc generate
([]interface {}) (len=1 cap=1) {
 (*ast.RawStmt)(0xc00052cb60)({
  Stmt: (*ast.SelectStmt)(0xc0003f1e40)({
   DistinctClause: (*ast.List)(0xc000642570)({
    Items: ([]ast.Node) <nil>
   }),
   IntoClause: (*ast.IntoClause)(<nil>),
   TargetList: (*ast.List)(0xc000642588)({
    Items: ([]ast.Node) (len=1 cap=1) {
     (*ast.ResTarget)(0xc00063f3e0)({
      Name: (*string)(<nil>),
      Indirection: (*ast.List)(0xc0006425a0)({
       Items: ([]ast.Node) <nil>
      }),
      Val: (*ast.ColumnRef)(0xc00052cae0)({
       Name: (string) "",
       Fields: (*ast.List)(0xc0006425b8)({
        Items: ([]ast.Node) (len=1 cap=1) {
         (*ast.String)(0xc0006445a0)({
          Str: (string) (len=1) "x"
         })
        }
       }),
       Location: (int) 33
      }),
      Location: (int) 33
     })
    }
   }),
   FromClause: (*ast.List)(0xc0006425d0)({
    Items: ([]ast.Node) (len=1 cap=1) {
     (*ast.RangeFunction)(0xc00052cb40)({
      Lateral: (bool) false,
      Ordinality: (bool) false,
      IsRowsfrom: (bool) false,
      Functions: (*ast.List)(0xc0006425e8)({
       Items: ([]ast.Node) (len=1 cap=1) {
        (*ast.List)(0xc000642600)({
         Items: ([]ast.Node) (len=2 cap=2) {
          (*ast.FuncCall)(0xc00063b950)({
           Func: (*ast.FuncName)(0xc00063f440)({
            Catalog: (string) "",
            Schema: (string) "",
            Name: (string) (len=6) "unnest"
           }),
           Funcname: (*ast.List)(0xc000642618)({
            Items: ([]ast.Node) (len=1 cap=1) {
             (*ast.String)(0xc0006445e0)({
              Str: (string) (len=6) "unnest"
             })
            }
           }),
           Args: (*ast.List)(0xc000642630)({
            Items: ([]ast.Node) (len=1 cap=1) {
             (*ast.A_ArrayExpr)(0xc000644610)({
              Elements: (*ast.List)(0xc000642648)({
               Items: ([]ast.Node) (len=2 cap=2) {
                (*ast.A_Const)(0xc000642660)({
                 Val: (*ast.Integer)(0xc000648090)({
                  Ival: (int64) 5
                 }),
                 Location: (int) 53
                }),
                (*ast.A_Const)(0xc000642678)({
                 Val: (*ast.Integer)(0xc000648098)({
                  Ival: (int64) 6
                 }),
                 Location: (int) 55
                })
               }
              }),
              Location: (int) 47
             })
            }
           }),
           AggOrder: (*ast.List)(0xc000642690)({
            Items: ([]ast.Node) <nil>
           }),
           AggFilter: (*ast.TODO)(0x2114930)({
           }),
           AggWithinGroup: (bool) false,
           AggStar: (bool) false,
           AggDistinct: (bool) false,
           FuncVariadic: (bool) false,
           Over: (*ast.WindowDef)(<nil>),
           Location: (int) 40
          }),
          (*ast.TODO)(0x2114930)({
          })
         }
        })
       }
      }),
      Alias: (*ast.Alias)(0xc000644660)({
       Aliasname: (*string)(0xc000644650)((len=1) "x"),
       Colnames: (*ast.List)(0xc0006426a8)({
        Items: ([]ast.Node) <nil>
       })
      }),
      Coldeflist: (*ast.List)(0xc0006426c0)({
       Items: ([]ast.Node) <nil>
      })
     })
    }
   }),
   WhereClause: (*ast.TODO)(0x2114930)({
   }),
   GroupClause: (*ast.List)(0xc0006426d8)({
    Items: ([]ast.Node) <nil>
   }),
   HavingClause: (*ast.TODO)(0x2114930)({
   }),
   WindowClause: (*ast.List)(0xc0006426f0)({
    Items: ([]ast.Node) <nil>
   }),
   ValuesLists: (*ast.List)(0xc000642708)({
    Items: ([]ast.Node) <nil>
   }),
   SortClause: (*ast.List)(0xc000642720)({
    Items: ([]ast.Node) <nil>
   }),
   LimitOffset: (*ast.TODO)(0x2114930)({
   }),
   LimitCount: (*ast.TODO)(0x2114930)({
   }),
   LockingClause: (*ast.List)(0xc000642738)({
    Items: ([]ast.Node) <nil>
   }),
   WithClause: (*ast.WithClause)(<nil>),
   Op: (ast.SetOperation) None,
   All: (bool) false,
   Larg: (*ast.SelectStmt)(<nil>),
   Rarg: (*ast.SelectStmt)(<nil>)
  }),
  StmtLocation: (int) 0,
  StmtLen: (int) 60
 })
}

@kyleconroy kyleconroy added 📚 postgresql 💻 linux 🔧 golang and removed triage New issues that hasn't been reviewed labels Jun 4, 2022
@kyleconroy
Copy link
Collaborator

I guess the parser doesn't understand that the alias after UNNEST defines a column

That is indeed the case.

@ryan-berger
Copy link
Contributor

ryan-berger commented Jun 21, 2022

@kyleconroy I took a stab at solving this issue, but I'm stuck on where to go and wanted to get feedback. There is buildQueryCatalog which will create the catalog for a query, which includes the WITH statement/a copy of the whole catalog.

This seems like a good place to do a search for the alias, but adding it in likely means that we add some sort of pseudo table/pseudo column if we want to fit within the existing structure of the QueryCatalog.

I think it is best to break from constructing a fake table, and I propose that we add a map of string to *ast.Alias, which also keeps track of the type. I say this since the semantics of an alias are a bit different because UNNEST(...) x and UNNEST(...) x(column_name) are both valid, and you can use x and x.column_name as valid columns (respectively). (Note, that last sentence is on shaky ground, I need to do more research).

The search for aliases also a bit tricky due to this issue but isn't too hard of a problem. Essentially we need to search for a RangeFunction or JoinExpression with a function call next to an alias (i.e. ast.List{Items: []{*ast.FunctionCall, *ast.Alias}}. A bit of a walk down the tree, but it shouldn't be terrible.

@ryan-berger
Copy link
Contributor

I had another idea right after I hit send.

The other option we have is to use a rewrite pass for these aliases since ast.Alias is a sqlc owned type.

During a rewrite we can pattern match the *pg_ast.List{*pg_ast.FunctionCall, *pg_ast.Alias} and transform it to something along the lines of: *ast.List{*ast.Alias{Node: *ast.FunctionCall}}.

This would require updating the *ast.Alias type to have new fields, but should theoretically be backwards compatible, and I believe that since it is in internal/ it shouldn't be breaking for any outward facing APIs

@pyc92
Copy link

pyc92 commented Dec 21, 2022

any workaround for this issue?

@kyleconroy
Copy link
Collaborator

Closing as a duplicate of #1322

@kyleconroy kyleconroy closed this as not planned Won't fix, can't repro, duplicate, stale Sep 26, 2023
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

4 participants