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.