The following query seems to work perfectly:
SELECT
"houses"."id",
"houses"."name",
"houses"."address",
ARRAY(
SELECT
"users"."id",
"users"."name"
FROM
"users"
WHERE
"users"."house_id" = "houses"."id"
) AS "users"
FROM
"houses";
However, if I modify the inner ARRAY() query for "users" to be:
SELECT
"users"."id",
"users"."name",
ARRAY(
SELECT
"todos"."id",
"todos"."description"
FROM
"todos"
WHERE
"todos"."user_id" = "users"."id"
) AS "todos"
FROM
"users"
===================================================
Unable to implement EnumerableNestedLoopJoin(condition=[true],
joinType=[inner]): rowcount = 22500.0, cumulative cost = {227855.0 rows,
18242.0 cpu, 0.0 io}, id = 12787
EnumerableTableScan(table=[[example, houses]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12718
EnumerableCollect(field=[x]): rowcount = 225.0, cumulative cost = {2755.0
rows, 18141.0 cpu, 0.0 io}, id = 12785
EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}], todos=[$t3]):
rowcount = 225.0, cumulative cost = {2530.0 rows, 17916.0 cpu, 0.0 io}, id
= 12793
EnumerableCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[{0}]): rowcount = 225.0, cumulative cost = {2305.0 rows,
16341.0 cpu, 0.0 io}, id = 12781
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[$cor0], expr#4=[$t3.id],
expr#5=[=($t2, $t4)], proj#0..2=[{exprs}], $condition=[$t5]): rowcount =
15.0, cumulative cost = {115.0 rows, 1101.0 cpu, 0.0 io}, id = 12789
EnumerableTableScan(table=[[example, users]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12722
EnumerableCollect(field=[x]): rowcount = 15.0, cumulative cost =
{130.0 rows, 1016.0 cpu, 0.0 io}, id = 12779
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[$cor1], expr#4=[$
t3.id], expr#5=[=($t1, $t4)], id=[$t0], description=[$t2],
$condition=[$t5]): rowcount = 15.0, cumulative cost = {115.0 rows, 1001.0
cpu, 0.0 io}, id = 12797
EnumerableTableScan(table=[[example, todos]]): rowcount =
100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12728
(state=,code=0)
