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 and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.