Coalesce DML express return more than one Element

[AnsonHwang86]

• EdgeDB Version: v4 on Cloud

1.defind schema

module default {
    type Foo {
	title: str;
        bar : Bar;
    }

    type Bar {
	title: str; 
    }

}

2.insert Foo

insert Foo {title:="Foo1"}
Here is the return value.
[
  {
    "id": "64682096-7d0a-11ee-8549-df022b184faa"
  }
]

3. runing following edgedql

with data := <json>$data, 
upserts := (
  for item in json_array_unpack(data["bar"]) union (
    (update Bar filter .id = <uuid>json_get(item, "barId") set { title:=<str>json_get(item,"barTitle") })??
    (insert Bar { title:=<str>json_get(item,"barTitle") })
  )), 
update Foo filter .id = <uuid>data["fooId"] set {bar:=assert_single(upserts)}

Here is variable

{"fooId":"64682096-7d0a-11ee-8549-df022b184faa","bar":[{"barId":"1735ff7b-0a7c-40fa-9ac3-e5f1219ca9b7","barTitle":"bar1"}]}
** Pls note that the Bar of id "1735ff7b-0a7c-40fa-9ac3-e5f1219ca9b7" is not yet save in database yet, it was randomly provided by the customer

4. by runing above, it will return a error

CardinalityViolationError: assert_single violation: more than one element returned by an expression

let's simply edgedb to show more exactly by removing array.

modify edgedb to

with data := <json>$data, 
upserts := (
 
    (update Bar filter .id = <uuid>json_get(data, "bar", "barId") set { title:=<str>json_get(data,"bar","barTitle") })??
    (insert Bar { title:=<str>json_get(data, "bar","barTitle") })
), 
update Foo filter .id = <uuid>data["fooId"] set {bar:=assert_single(upserts)}

here is data

{"fooId":"64682096-7d0a-11ee-8549-df022b184faa","bar":{"barId":"1735ff7b-0a7c-40fa-9ac3-e5f1219ca9b7","barTitle":"bar2"}}

Here is error

InternalServerError: more than one row returned by a subquery used as an expression

While I run

with data := <json>$data, 
upserts := (
 
    (update Bar filter .id = <uuid>json_get(data, "bar", "barId") set { title:=<str>json_get(data,"bar","barTitle") })??
    (insert Bar { title:=<str>json_get(data, "bar","barTitle") })
), 
select upserts

here is data

{"fooId":"64682096-7d0a-11ee-8549-df022b184faa","bar":{"barId":"1735ff7b-0a7c-40fa-9ac3-e5f1219ca9b7","barTitle":"bar2"}}

It did return only one element.

If I change id of Bar to one of id has existed in database, it will run correctly without this bug.

[AnsonHwang86]

I notice there are serveral bug of Coalesce DML are fixed recently. Is this possible be fixed together? Thanks.

[msullivan]

Fascinatingly, I think the bug here is actually in assert_single. See this failure:

create type Nully { create property x -> str; create property y -> str; };
insert Nully { y := 'x' };

Then both of these fail spuriously:

select Nully { xy := assert_single({.x, .y}) };
select Nully { xy := assert_single({.x, .x}) };

[msullivan]

You can probably work around the issue by doing limit 1 instead of assert_single for now

[AnsonHwang86]

Great done.

You can probably work around the issue by doing limit 1 instead of assert_single for now

Yes, but it must following order by .id desc otherwise the limit 1 will get the empty set.

update Foo filter .id = <uuid>data["fooId"] set {bar:=(select upserts order by .id desc limit 1)}

[msullivan]

Yes, but it must following order by .id desc otherwise the limit 1 will get the empty set.

update Foo filter .id = <uuid>data["fooId"] set {bar:=(select upserts order by .id desc limit 1)}

Yup that was also a bug :(. Fixed in #5690 too.

[AnsonHwang86]

@msullivan #5690 is like a typo fix. Are you link a wrong issue?