sqlc-dev
Unnest into alias doesn't define the column
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
$ 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
})
}
I guess the parser doesn't understand that the alias after UNNEST defines a column
That is indeed the case.
@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.
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
