Sample Header Ad - 728x90

Is there a cross join in these CTEs?

0 votes
0 answers
140 views
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: 1. Is there really an accidential cartesian join? 2. 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']['phase_0']['environments']['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
Asked by Technaton (171 rep)
May 27, 2022, 08:06 PM