Hello Gavin,
what's the full stack trace that you get?
On Mon, Feb 14, 2022 at 3:24 PM Gavin Ray <ray.gavi...@gmail.com> wrote:
> 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)
>
