I have a query that is constructed from several CTEs (in order to organize the query). I constructed it in my SQL workbench and was happy with the test results on a smaller dataset, so I converted it to SQLalchemy code for executing it on the production database. Now, SQLalchemy warns me of a cross join:
SELECT statement has a cartesian product between FROM element(s) "vm_per_bus" and FROM element "voltages". Apply join condition(s) between each element to resolve.
In my query (see below), this happens in the last CTE. My question is now twofold:
- Is there really an accidential cartesian join?
- How do I join the two CTEs?
This is the query:
WITH voltages AS
(SELECT muscle_actions.id AS id,
regexp_replace(CAST(experiment_runs.document_json['schedule_config'][0]['phase_0']['environments'][0]['environment']['params']['reward']['name'] AS TEXT), '^[^:]+:([^"]+)"', '\1') AS reward_function,
agents.uid AS agent,
jsonb_array_elements(jsonb_path_query_array(CAST(muscle_actions.sensor_readings AS JSONB), '$.*.value')) AS vm
FROM muscle_actions
JOIN agents ON agents.id = muscle_actions.agent_id
JOIN experiment_run_phases ON experiment_run_phases.id = agents.experiment_run_phase_id
JOIN experiment_run_instances ON experiment_run_instances.id = experiment_run_phases.experiment_run_instance_id
JOIN experiment_runs ON experiment_runs.id = experiment_run_instances.experiment_run_id
WHERE experiment_runs.id IN (8,
11,
19,
25,
29,
33,
37,
41,
45,
49,
53,
57,
61,
65,
69,
73,
77,
81,
85,
89,
93,
97,
120,
122,
137,
127,
132,
142,
147,
152,
157,
162,
199,
204,
209,
214,
219,
224,
229)
AND experiment_run_phases.uid = 'phase_1'),
vm_per_bus AS
(SELECT voltages.id AS id,
voltages.reward_function AS reward_function,
voltages.agent AS agent,
voltages.vm AS vm,
abs(1.0 - CAST(voltages.vm AS REAL)) AS vm_deviation,
abs(1.0 - CAST(voltages.vm AS REAL)) >= 0.15 AS vm_violation
FROM voltages),
vm AS
(SELECT vm_per_bus.id AS id,
max(voltages.reward_function) AS reward_function,
max(voltages.agent) AS agent,
array_agg(vm_per_bus.vm) AS array_agg_1,
max(vm_per_bus.vm_deviation) AS max_vm_deviation,
sum(CAST(vm_per_bus.vm_violation AS INTEGER)) AS num_vm_violations
FROM vm_per_bus,
voltages
GROUP BY vm_per_bus.id)
SELECT vm.reward_function,
vm.agent,
max(vm.max_vm_deviation) AS max_abs_violation,
sum(vm.num_vm_violations) AS num_violations
FROM vm
GROUP BY vm.reward_function,
vm.agent
vm
. You don't need to join these two tables though, because everything you need is available fromvm_per_bus
alone.