0

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'][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
4
  • 3
    "Is there really an accidential cartesian join?" -- yes there is, the the definition of vm. You don't need to join these two tables though, because everything you need is available from vm_per_bus alone.
    – mustaccio
    May 27, 2022 at 20:12
  • FROM vm_per_bus, voltages. I recomend always using explicit ANSI JOIN instead. Without a WHERE clause this corresponds to FROM vm_per_bus CROSS JOIN voltages May 28, 2022 at 4:44
  • You are right - such a silly mistake. I probably looked at this query far too long. :) But why do you reply in a comment? Because this was the answer I was looking for, and I'd happily upvote/accept it.
    – Technaton
    May 28, 2022 at 20:21
  • @Technaton - if you want to be sure that Lennart sees your comment, put @ followed by his handle Lennart (no space) in the body of your comment - he will be notified!
    – Vérace
    May 30, 2022 at 7:57

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.