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

[jaclarke]

• EdgeDB Version: 2.0-dev.6475+local

Steps to Reproduce:

with
  movie := (
    select default::Movie limit 1
  ),
  actors := (
    select movie {
      actors:= (
        select movie.actors {
          linkprop := movie.actors@character_name
        }
      )
    }
  ).actors
select actors {
  linkprop
}

Schema: (schema from edgedb.com/tutorial)

using extension notebook version \'1.0\';
module default {
    type Account {
        multi link watchlist -> default::Content;
        required property username -> std::str {
            constraint std::exclusive;
        };
    };
    abstract type Content {
        multi link actors -> default::Person {
            property character_name -> std::str;
        };
        required property title -> std::str;
    };
    type Movie extending default::Content {
        property release_year -> std::int32;
    };
    type Person {
        link filmography := (.<actors[is default::Content]);
        required property name -> std::str;
    };
    type Season {
        required link show -> default::Show;
        required property number -> std::int32;
    };
    type Show extending default::Content {
        property num_seasons := (std::count(.<show[is default::Season]));
    };
};

[haikyuu]

It happened to me as well with the following query (from the query builder with the sole addition of [IS ObjectType].annotations: { name, @value }) https://github.dev/edgedb/edgedb-js/blob/f01092242a02ae0255ebee64ea8b381d0564710a/src/reflection/queries/getTypes.ts#L118

WITH
      MODULE schema,

      material_scalars := (
        SELECT ScalarType
        FILTER
          (.name LIKE 'std::%' OR .name LIKE 'cal::%')
          AND NOT .is_abstract
      )

    SELECT Type {
      id,
      name,
      is_abstract,

      kind := 'object' IF Type IS ObjectType ELSE
              'scalar' IF Type IS ScalarType ELSE
              'array' IF Type IS Array ELSE
              'tuple' IF Type IS Tuple ELSE
              'unknown',
      
      [IS ObjectType].annotations: { name, @value },
      
[IS ScalarType].enum_values,
      is_seq := 'std::sequence' in [IS ScalarType].ancestors.name,
      # for sequence (abstract type that has non-abstract ancestor)
      single material_id := (
        SELECT x := Type[IS ScalarType].ancestors
        FILTER x IN material_scalars
        LIMIT 1
      ).id,

      [IS InheritingObject].bases: {
        id
      } ORDER BY @index ASC,

      [IS ObjectType].union_of,
      [IS ObjectType].intersection_of,
      [IS ObjectType].pointers: {
        real_cardinality := ("One" IF .required ELSE "AtMostOne") IF <str>.cardinality = "One" ELSE ("AtLeastOne" IF .required ELSE "Many"),
        name,
        target_id := .target.id,
        kind := 'link' IF .__type__.name = 'schema::Link' ELSE 'property',
        is_exclusive := exists (select .constraints filter .name = 'std::exclusive'),
        is_computed := len(.computed_fields) != 0,
        is_readonly := .readonly,
        has_default := EXISTS .default or ("std::sequence" in .target[IS ScalarType].ancestors.name),
        [IS Link].pointers: {
          real_cardinality := ("One" IF .required ELSE "AtMostOne") IF <str>.cardinality = "One" ELSE ("AtLeastOne" IF .required ELSE "Many"),
          name := '@' ++ .name,
          target_id := .target.id,
          kind := 'link' IF .__type__.name = 'schema::Link' ELSE 'property',
          is_computed := len(.computed_fields) != 0,
          is_readonly := .readonly
        } filter .name != '@source' and .name != '@target',
      } FILTER @is_owned,
      backlinks := (SELECT DETACHED Link FILTER .target = Type) {
        real_cardinality := "AtMostOne"
          IF
          EXISTS (select .constraints filter .name = 'std::exclusive')
          ELSE
          "Many",
        name := '<' ++ .name ++ '[is ' ++ std::assert_exists(
          .source.name if .source.name[:9] != 'default::' else .source.name[9:]
        ) ++ ']',
        stub := .name,
        target_id := .source.id,
        kind := 'link',
        is_exclusive := (EXISTS (select .constraints filter .name = 'std::exclusive')) AND <str>.cardinality = "One",
      },
      backlink_stubs := array_agg((
        WITH
          stubs := DISTINCT (SELECT DETACHED Link FILTER .target = Type).name,
          baseObjectId := (SELECT DETACHED ObjectType FILTER .name = 'std::BaseObject' LIMIT 1).id
        FOR stub in { stubs }
        UNION (
          SELECT {
            real_cardinality := "Many",
            name := '<' ++ stub,
            target_id := baseObjectId,
            kind := 'link',
            is_exclusive := false,
          }
        )
      )),
      array_element_id := [IS Array].element_type.id,

      tuple_elements := (SELECT [IS Tuple].element_types {
        target_id := .type.id,
        name
      } ORDER BY @index ASC),
    }
    ORDER BY .name;

[haikyuu]

This is a simpler query that reproduces the error

WITH MODULE schema
 select Type {
    [IS ObjectType].annotations: { name, @value }
  };

[jaclarke]

Just tripped over this bug again. Weirdly it doesn't error directly selecting ObjectType or if you filter out ObjectType:

# These error:

with module schema
select Type {
  [is AnnotationSubject].annotations: { name, @value }
};

with module schema
select Type {
  [is AnnotationSubject].annotations: { name, @value }
} filter Type is ObjectType;



# These both work:

with module schema
select ObjectType {
  [is AnnotationSubject].annotations: { name, @value }
};

with module schema
select Type {
  [is AnnotationSubject].annotations: { name, @value }
} filter Type is not ObjectType;

[msullivan]

All of queries in the follow-up posts have been fixed, I believe by #3987.

The original query is still broken though.

("more than one row returned by a subquery used as an expression" is a pretty common way that things can go wrong, so it won't always be the same issue. It happens here because something is going wrong and the subquery for linkprop is going wild and fetching every Movie.actors@character_name. If you mark linkprop as multi, you can see that directly, since instead of an ISE, it will tell us that Paul Rudd plays every character)

[msullivan]

I think that the particular issue here is that we ought to be materializing linkprop, and aren't. Possibly related to test_edgeql_scope_ref_outer_06b

[izakfilmalter]

Think I ran into this issue, not sure if there is a work around: https://github.com/edgedb/edgedb-js/issues/427

[msullivan]

For my own notes, here is the original query moved to the cards db:

with
  movie := (
    select default::User limit 1
  ),
  actors := (
    select movie {
      actors:= (
        select movie.deck {
          linkprop := movie.deck@count
        }
      )
    }
  ).actors
select actors {
  linkprop
};