Generated asExpression columns not in RETURNING clause on save

[Migushthe2nd]

Issue Description

When an entity with a STORED generated column is saved, the generated column is not included in the RETURNING clause, causing the object not to get populated with the generated column's value.

Expected Behavior

query: START TRANSACTION
query: INSERT INTO "typeorm"."user"("generated", "email", "username", "password") VALUES (DEFAULT, $1, $2, $3) RETURNING "counter", "generated" -- PARAMETERS: ["email@example.com","username","hash"]
query: COMMIT

The generated generated column is in the RETURNING statement.

BEFORE SAVE: UserEntity {
  email: 'email@example.com',
  username: 'username',
  password: 'hash'
}
AFTER SAVE: UserEntity {
  counter: 2,
  generated: 4,
  email: 'email@example.com',
  username: 'username',
  password: 'hash'
}
WHEN FETCHING MANUALLY: UserEntity {
  counter: 2,
  generated: 4,
  email: 'email@example.com',
  username: 'username',
  password: 'hash'
}

Actual Behavior

Using the new stored generated column type:

query: START TRANSACTION
query: INSERT INTO "typeorm"."user"("generated", "email", "username", "password") VALUES (DEFAULT, $1, $2, $3) RETURNING "counter" -- PARAMETERS: ["email@example.com","username","hash"]
query: COMMIT

As you can see, the column generated is not in the RETURNING statement, which causes the entity not to get populated properly with the column's value:

BEFORE SAVE: UserEntity {
  email: 'email@example.com',
  username: 'username',
  password: 'hash'
}
AFTER SAVE: UserEntity {
  counter: 2,
  email: 'email@example.com',
  username: 'username',
  password: 'hash'
}
WHEN FETCHING MANUALLY: UserEntity {
  counter: 2,
  generated: 4,
  email: 'email@example.com',
  username: 'username',
  password: 'hash'
}

Steps to Reproduce

@Entity()
export class UserEntity extends BaseEntity {

    @PrimaryGeneratedColumn()
    counter: number;

    // generated column defined here
    @Column({
        type: "int",
        generatedType: "STORED",
        asExpression: `counter * 2`,
    })
    generated: number;

    @Column({unique: true, nullable: true})
    email?: string;

    @Column({length: 32})
    username: string;

    @Column({nullable: true})
    password?: string;

}

The code I use to test this:

const user = UserEntity.create({email, password, username});
console.log("BEFORE SAVE:", user)
user.save();
console.log("AFTER SAVE:", user)

const userFound = await UserEntity.find({where: {email}})
console.log("WHEN FETCHING MANUALLY:", userFound)

My Environment

Dependency	Version
Operating System	Windows 11
Node.js version	v16.13.1
Typescript version	4.4.4
TypeORM version	0.2.41

Additional Context

The generated column feature was implemented in the PostgreSQL driver by #6469, so perhaps @TheNoim knows what's missing.

The column seems to be missing from this array:

typeorm/src/query-builder/ReturningResultsEntityUpdator.ts

Lines 172 to 187 in 90a8deb

	getInsertionReturningColumns(): ColumnMetadata[] {
	// for databases which support returning statement we need to return extra columns like id
	// for other databases we don't need to return id column since its returned by a driver already
	const needToCheckGenerated = this.queryRunner.connection.driver.isReturningSqlSupported();
	// filter out the columns of which we need database inserted values to update our entity
	return this.expressionMap.mainAlias!.metadata.columns.filter(column => {
	return column.default !== undefined ||
	(needToCheckGenerated && column.isGenerated) ||
	column.isCreateDate ||
	column.isUpdateDate ||
	column.isDeleteDate ||
	column.isVersion;
	});
	}

column.isGenerated is false, to be specific.

Relevant Database Driver(s)

DB Type	Reproducible
aurora-data-api	no
aurora-data-api-pg	no
better-sqlite3	no
cockroachdb	no
cordova	no
expo	no
mongodb	no
mysql	yes
nativescript	no
oracle	no
postgres	yes
react-native	no
sap	no
sqlite	no
sqlite-abstract	no
sqljs	no
sqlserver	no

Are you willing to resolve this issue by submitting a Pull Request?

• ✅ Yes, I have the time, and I know how to start.
• ✖️Yes, I have the time, but I don't know how to start. I would need guidance.
• ✖️ No, I don’t have the time, but I can support (using donations) development.
• ✖️ No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

[TheNoim]

I never intended this feature for usage with PrimaryColumn. Is this the only way how you could archive this?

[Migushthe2nd]

I have thought of making counter the primary column, and making id unique and adding an index. I would still need to add a foreign key referencing id which is not possible.

Addition: using the computed id as primary key reduces complexity. It would allow my system to identify entities by a single type of id, instead of allowing both a computed id and a numeric id everywhere. These two might get mixed up unintentionally (numeric ids are in the range of the computed ids)

[Migushthe2nd]

It seems the issue of not returning the generated column is not specific to cascaded inserts, nor to primary columns. I have updated the issue quite a bit, so please read it again.

[maks-rafalko-unagi]

I can confirm this bug still exists on 0.3.*

#8472 is closed for some reason, but @pleerock can you advice was this PR a right fix?

With the current master, this is how we fixed it thanks to https://www.npmjs.com/package/patch-package

diff --git a/node_modules/typeorm/metadata/EntityMetadata.js b/node_modules/typeorm/metadata/EntityMetadata.js
index f12c352..8c47ebe 100644
--- a/node_modules/typeorm/metadata/EntityMetadata.js
+++ b/node_modules/typeorm/metadata/EntityMetadata.js
@@ -678,6 +678,7 @@ class EntityMetadata {
     getInsertionReturningColumns() {
         return this.columns.filter((column) => {
             return (column.default !== undefined ||
+                column.asExpression ||
                 column.isGenerated ||
                 column.isCreateDate ||
                 column.isUpdateDate ||