[BUG]: In the Query API generated identifier exceeds 63-bytes length limit #1378
Description
What version of drizzle-orm are you using?
0.28.6
What version of drizzle-kit are you using?
0.19.13
Describe the Bug
I have a query using Query API that is doing a lot of nested joins. I've chcecked the generated query and it appears that the generated identifier name is getting truncated because it's too long. From what I read Postgres has a limit of 63-bytes long identifiers. Here is the generated query (I've replaced params with actual values):
SELECT
"microcycles"."id",
"microcycles"."name",
"microcycles"."created_at",
"microcycles"."updated_at",
"microcycles_microcycleDays"."data" AS "microcycleDays"
FROM
"microcycles"
LEFT JOIN lateral (
SELECT
coalesce(
json_agg(
json_build_array(
"microcycles_microcycleDays"."id",
"microcycles_microcycleDays"."weekday",
"microcycles_microcycleDays"."created_at",
"microcycles_microcycleDays"."updated_at",
"microcycles_microcycleDays"."training_template_id",
"microcycles_microcycleDays"."microcycle_id",
"microcycles_microcycleDays_trainingTemplate"."data"
)
),
'[]' :: json
) AS "data"
FROM
"microcycle_days" "microcycles_microcycleDays"
LEFT JOIN lateral (
SELECT
json_build_array(
"microcycles_microcycleDays_trainingTemplate"."id",
"microcycles_microcycleDays_trainingTemplate"."name",
"microcycles_microcycleDays_trainingTemplate"."created_at",
"microcycles_microcycleDays_trainingTemplate"."updated_at",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."data"
) AS "data"
FROM
(
SELECT
*
FROM
"training_templates" "microcycles_microcycleDays_trainingTemplate"
WHERE
"microcycles_microcycleDays_trainingTemplate"."id" = "microcycles_microcycleDays"."training_template_id"
LIMIT
1
) "microcycles_microcycleDays_trainingTemplate"
LEFT JOIN lateral (
SELECT
coalesce(
json_agg(
json_build_array(
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."id",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."gender",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."order",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."default_sets",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."default_reps",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."default_weight",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."default_time",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."default_kcal",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."reps_progression",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."weight_progression",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."time_progression",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."kcal_progression",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."created_at",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."updated_at",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."exercise_id",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."training_template_id",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."data"
)
),
'[]' :: json
) AS "data"
FROM
"training_step_templates" "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"
LEFT JOIN lateral (
SELECT
json_build_array(
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."id",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."has_rep_set",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."has_weight_set",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."has_time_set",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."has_kcal_set",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."default_sets",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."default_reps",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."default_weight",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."default_time",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."default_kcal",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."reps_progression",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."weight_progression",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."time_progression",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."kcal_progression",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."created_at",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."updated_at",
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."weight_list_id"
) AS "data"
FROM
(
SELECT
*
FROM
"exercises" "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"
WHERE
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"."id" = "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."exercise_id"
LIMIT
1
) "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise"
) "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates_exercise" ON TRUE
WHERE
"microcycles_microcycleDays_trainingTemplate_trainingStepTemplates"."training_template_id" = "microcycles_microcycleDays_trainingTemplate"."id"
) "microcycles_microcycleDays_trainingTemplate_trainingStepTemplates" ON TRUE
) "microcycles_microcycleDays_trainingTemplate" ON TRUE
WHERE
"microcycles_microcycleDays"."microcycle_id" = "microcycles"."id"
) "microcycles_microcycleDays" ON TRUE
WHERE
"microcycles"."id" = '93c737a0-d54c-420c-9d47-ebf53cb1215d'
LIMIT
1;And the error says:
ERROR: column microcycles_microcycleDays_trainingTemplate_trainingStepTemplat.exercise_id does not exist
LINE 103: ...ngTemplate_trainingStepTemplates_exercise"."id" = "microcycl...
^
HINT: There is a column named "exercise_id" in table "microcycles_microcycleDays_trainingTemplate_trainingStepTemplat", but it cannot be referenced from this part of the query.
Expected behavior
When identifier length is too long it should try to maybe generate acronyms of tables. Instead of training_step_templates maybe it should use tst or maybe even random names to avoid conflicts.
Environment & setup
No response