Repository navigation
sqlc.embed using LEFT JOIN #2997
Description
Version
1.23.0
What happened?
When using sqlc.embed on something in combination with a LEFT JOIN, meaning that the embedded table may be null, sqlc fails to parse the result when null. This happened with SQLite, but considering the nature of the issue, this might be relevant for other databases.
Relevant log output
sql: Scan error on column index 1, name "id": converting NULL to int64 is unsupportedDatabase schema
CREATE TABLE accounts(
id integer NOT NULL PRIMARY KEY AUTOINCREMENT
);
CREATE TABLE orders(
id integer NOT NULL PRIMARY KEY AUTOINCREMENT
);
CREATE TABLE account_orders(
account_id integer NOT NULL REFERENCES accounts(id),
order_id integer NOT NULL REFERENCES orders(id)
);
INSERT INTO accounts (id) VALUES (1);SQL queries
-- name: GetAllAccounts :many
SELECT accounts.id, sqlc.embed(orders)
from accounts
LEFT JOIN account_orders ON accounts.id = account_orders.account_id
LEFT JOIN orders ON orders.id = account_orders.order_id;Configuration
# Although irrelevant for this issue I believe
version: "2"
sql:
- engine: "sqlite"
queries: "infrastructure/sqlite/sqlc/queries"
schema: "infrastructure/sqlite/sqlc/schema.sql"
gen:
go:
package: "dao"
out: "infrastructure/sqlite/sqlc/dao"
emit_interface: truePlayground URL
No response
What operating system are you using?
Linux
What database engines are you using?
SQLite
What type of code are you generating?
Go
Activity
KevenGoncalves commentedon Nov 21, 2023
Seems like you query have some problems but if is not, This is an old problem with sqlc using embed, it cannot generate pointer types for tables, only the normal types, and what is happening is the scan is trying to pass null to non-null type and give that error, there's two workarounds that you can implement, the first one is take care of possibility of null values to be passed to the scan, sometimes that is not viable and the second workaround and most valuable is create a view with a join for that table, so you can pass instead the table, pass the view, because the sqlc generate views with null types check here
KevenGoncalves commentedon Nov 21, 2023
Try query without sqlc.embed in SQLite client, to check the nullity of some fields
ltdangle commentedon Nov 24, 2023
Can confirm the same issue for mysql target.
I guess the solution is not to use sqlc.embed...
KevenGoncalves commentedon Nov 24, 2023
You can try use the view solution
pelletier197 commentedon Nov 24, 2023
Yes, that's what I did. It's just quite inconvenient because I have multiple field
idandnamein my real tables, so sqlc ends up generatingid_1,id_2... fields. I had to manually select each and every field of my entity and name them properly.In the end, this is an annoyance and it does not allow me to re-use my assembler code, but if you feel like there is no way to fix this, then you may just close the issue.
However, if there is a potential fix, I think this would greatly benefit everyone. Thanks for your help 🙂
pelletier197 commentedon Nov 24, 2023
As a potential other idea, would it be easier for sqlc to avoid naming things
id_1andId_2when I doselect accounts.*, orders.*? If it could throw a prefix in there that could be greataccount_id,order_id, and so on.I'm just throwing the idea.
KevenGoncalves commentedon Nov 24, 2023
Yeah I Think the same if could have some thing like sqlc.nembed() that generate the table with null values would be awesome, the only way right now is use the view and have duplicate structs
KevenGoncalves commentedon Nov 24, 2023
The problem is that the sqlc "cannot" see that the id is from a certain table or not, but if i'm not committing a mistake if you use the enhanced queries from the new version that can be solved
brasic commentedon Dec 5, 2023
It looks like #2858 fixes this by erroring if a nullable embed is used in a query.
hstefan commentedon Dec 21, 2023
Ran into this issue just now.
Would it make sense to extend sqlc.embed to use pointers for structs extracted from left joins? That's not as neat as
sql.Null*types, but it's still workable from a user perspective. The hacky part would be determining that all fields are null, I believe.anthonybishopric commentedon Jan 11, 2024
Also running into this. It's a bit of a pain as the path of least resistance to fixing this is breaking out multiple queries and doing the stitching in code myself. This is the worst issue I've run into with sqlc thus far - it's easier to write multiple queries and hurt site performance than any other option.
Agreed with @hstefan - an alternative to a pointer type could be to use a generic
Optional[T]when left joining instead of just embedding the original type, or generating anOptionalThingfor everyThingthat's generated.DesnLee commentedon Jan 20, 2024
I guess the best solution for me now is to split the query and then combine it manually in the code?
ErikKalkoken commentedon Apr 26, 2024
Splitting the query works, but if one is looking for a solution that also performs well in lists, then I think creating views as suggested above by @KevenGoncalves is better. The views can be used in a left join (as requested by OP), because all fields of the generated view models are nullable. The view models still need to be mapped back to the source models. But that is just some go mapping and better then running additional queries for each object (see also n+1 problem).
Jonathan-Landeed commentedon Feb 18, 2025
If you want a more general-purpose view, this can be used in any left/right join.
see #2348 (comment)
feat(sqlc-dev#2348, sqlc-dev#2997): add sqlc.nembed() for nullable em…
Danoctum commentedon Jun 13, 2026
Did anyone ever get this to work in a many-to-many scenario?
Am playing with a many-to-many relationship like the one mentioned in the issue, but I can't seem to get the views correct to have the query work.