[Bug] innerJoinLateral selectAll prepends schema
apologies for the hastily written bug report. i found a workaround so im not super concerned. writing this down in case it's helpful:
1. have a postgres
db variable that's instantiated withSchema pointed at my_schema
2. selectFrom my_table with an innerJoinLateral on my_computed_object
3. select jsonObjectFrom(eb.selectFrom('my_computed_object').selectAll('my_computed_object'))
4. compile query and observe: (select to_json(obj) from (select "my_schema"."my_computed_object".* from "my_schema"."my_computed_object") as obj)
the above results in a runtime error because "my_schema"."my_computed_object" does not exist. the prepended "my_schema". should get dropped
the workaround is to instead do jsonBuildObject({ key1: ref('key1'), ... })
(feel free to close / ignore this if the above is insufficient / not helpful)10 Replies
also got this to happen in a select subquery, so does not seem specific to
innerJoinLateralHey 👋
Can you provide a kyse.link that reproduces this?
im not sure
withSchema works in kyse.link? see https://kyse.link/PMqfF
here's a close enough repro to what's happening in my environment: https://kyse.link/LdtlM
where in the compiled query, the selected "alien_object" comes FROM "skeema"."alien"
when it should come FROM "alien"How should kysely know that?
Solution
Use
withSchema('public') for the subquerythat just shifts the issue:
* now the subquery comes
FROM "public"."alien" (which does not exist)
* eb.withSchema is deprecated so shouldnt use that?There's no need to use
eb there. Just use dbfwiw this is not a solution(see https://discord.com/channels/890118421587578920/1321889015439298611/1321926005941862481)
db cannot selectFrom("alien") since "alien" is not a table
@koskimas this is my workaround by the way: https://kyse.link/DuUwp
as you can see ref works fine, so this is an issue specific to eb
problem with the jsonBuildObject workaround is verbosity, especially as the number of fields grows. this also requires a satsifes Expression<....> type check to make sure you keep getting all the expected fields
im happy to live with that 👆 for as long as needed
this is a good question
a noSchema method on eb (or something else achieving the same effect) would be an ok hack
ideally kysely would track these "virtual" tables as separate from the tables initially configured for db. but this could be a huge lift and not worth the effort?
(FWIW this is not specific to innerJoinLateral... here's the same thing happening with an innerJoin https://kyse.link/pCg6n -- and it's easy enough to make this happen in doubly nested subqueries, etc)Hey 👋🏻
withSchema is a plugin under the hood.
We could possibly track aliases in plugin state and not add schema to them when we find them in other parts of the tree as standalone table names.that would be ideal, from the perspective of all my use cases