Subquery as generated column in mysql?

Question

Can I create a generated column in table A which sums up a column in table B with a tableA_id of the row in table A?

Suppose I have a table of of families, and a table of children. I want a sum of the ages of the children for each family.

ALTER TABLE people.families 
ADD COLUMN sumofages DECIMAL(10,2) GENERATED ALWAYS AS 
(SELECT SUM(age) FROM people.children WHERE family_id = people.families.id) STORED;

ERROR 3102: Expression of generated column 'sumofages' contains a disallowed function.

I can't save it as type VIRTUAL either. What am I doing wrong here?

ALTER TABLE people.families 
ADD COLUMN sumofages DECIMAL(10,2) GENERATED ALWAYS AS 
(SELECT SUM(age) FROM people.children WHERE family_id = people.families.id) VIRTUAL;

ERROR 3102: Expression of generated column 'sumofages' contains a disallowed function.

I don't know which function is disallowed. SUM doesn't seem to be it. Maybe SELECT?

Answer 1

https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

Generated column expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.

• Subqueries, parameters, variables, stored functions, and user-defined functions are not permitted.

It's reasonable that the expression for a generated column can reference only columns within the same row. The generated column cannot use subqueries, or reference other tables, or functions with non-deterministic output.

Suppose generated columns did support cross-table references. Particularly consider the case of STORED generated columns.

If you update a table, MySQL would also have to update any references in generated columns elsewhere in the database, if they reference the row you updated. It would be complex and expensive for MySQL to track down all those references.

Then consider add indirect references through stored functions.

Then consider that your update is to an InnoDB table in a transaction, but the generated column may be in a non-transaction (MyISAM, MEMORY, ARCHIVE, etc.) table. Should your update be reflected in those generated columns when you make it? What if you roll back? Should your update be reflected at the time you commit? Then how should MySQL "queue up" changes to apply to those tables? What if multiple transactions commit updates that affect the generated column reference? Which one should win, the one that applied the change last or the one that committed last?

For these reasons, it's not practical or efficient to allow generated columns to reference anything other than the columns of the same row in the same table.

Answer 2

The idea of a computed column is to derive data from the other columns in the record, e.g. combine the country code with the zip code, so you'd store DE and 12345 and you'd get DE-12345 which you could use in an address.

What you are trying to do, however, is something completely different. You are accessing data from another table. But that table's data may change, so when accessing the same record you might suddenly get a completely different result. Computed columns should contain deterministic values, so they don't change as long as your record's data doesn't change. I don't know about MySQL in this regard, but it probably forbids non-deterministic data, such as your subquery.

What you are actually looking for is a view. A view can combine selects from different tables, just as you want it to happen. So use

create view familydata as
(
  select f.*, sum(c.age) as sumofages
  from families f
  join children c on c.family_id = f.id
  group by f.id
);

or

create view familydata as
(
  select f.*,
  (
    select sum(age)
    from children c
    where c.family_id = f.id
  ) as sumofages
  from families f
);

I hope I got the syntax right.