error: table name "column_name_less_than_64" specified more than once

scratchmex commented 3 years ago

This is related to https://github.com/typeorm/typeorm/issues/2661 but with a column name with less than 64 characters.

Issue Description

Expected Behavior

Query works.

Actual Behavior

query failed: SELECT "Package"."id" AS "Package_id", "Package"."name" AS "Package_name", "Package"."cost" AS "Package_cost", "Package"."expiration" AS "Package_expiration", "Package__packageProducts"."id" AS "Package__packageProducts_id", "Package__packageProducts"."quantity" AS "Package__packageProducts_quantity", "Package__packageProducts"."productId" AS "Package__packageProducts_productId", "Package__packageProducts"."packageId" AS "Package__packageProducts_packageId", "Package__packageProducts_product"."id" AS "Package__packageProducts_product_id", "Package__packageProducts_product"."name" AS "Package__packageProducts_product_name", "Package__packageProducts_product"."price" AS "Package__packageProducts_product_price", "Package__packageProducts_product"."notes" AS "Package__packageProducts_product_notes", "Package__packageProducts_product"."deletedAt" AS "Package__packageProducts_product_deletedAt", "Package__packageProducts_product"."subtypeId" AS "Package__packageProducts_product_subtypeId", "Package__packageProducts_product_subtype"."id" AS "Package__packageProducts_product_subtype_id", "Package__packageProducts_product_subtype"."name" AS "Package__packageProducts_product_subtype_name", "Package__packageProducts_product_subtype"."deletedAt" AS "Package__packageProducts_product_subtype_deletedAt", "Package__packageProducts_product_subtype"."typeId" AS "Package__packageProducts_product_subtype_typeId", "Package__packageProducts_product_subtype_type"."id" AS "Package__packageProducts_product_subtype_type_id", "Package__packageProducts_product_subtype_type"."name" AS "Package__packageProducts_product_subtype_type_name", "Package__packageProducts_product_subtype_type"."deletedAt" AS "Package__packageProducts_product_subtype_type_deletedAt", "Package__packageSubtypes"."id" AS "Package__packageSubtypes_id", "Package__packageSubtypes"."quantity" AS "Package__packageSubtypes_quantity", "Package__packageSubtypes"."subtypeId" AS "Package__packageSubtypes_subtypeId", "Package__packageSubtypes"."packageId" AS "Package__packageSubtypes_packageId", "Package__packageSubtypes_subtype"."id" AS "Package__packageSubtypes_subtype_id", "Package__packageSubtypes_subtype"."name" AS "Package__packageSubtypes_subtype_name", "Package__packageSubtypes_subtype"."deletedAt" AS "Package__packageSubtypes_subtype_deletedAt", "Package__packageSubtypes_subtype"."typeId" AS "Package__packageSubtypes_subtype_typeId", "Package__packageSubtypes_subtype_type"."id" AS "Package__packageSubtypes_subtype_type_id", "Package__packageSubtypes_subtype_type"."name" AS "Package__packageSubtypes_subtype_type_name", "Package__packageSubtypes_subtype_type"."deletedAt" AS "Package__packageSubtypes_subtype_type_deletedAt", "Package__packageSubtypes_subtype"."id" AS "Package__packageSubtypes_subtype_id", "Package__packageSubtypes_subtype"."name" AS "Package__packageSubtypes_subtype_name", "Package__packageSubtypes_subtype"."deletedAt" AS "Package__packageSubtypes_subtype_deletedAt", "Package__packageSubtypes_subtype"."typeId" AS "Package__packageSubtypes_subtype_typeId", "Package__packageSubtypes_subtype_type"."id" AS "Package__packageSubtypes_subtype_type_id", "Package__packageSubtypes_subtype_type"."name" AS "Package__packageSubtypes_subtype_type_name", "Package__packageSubtypes_subtype_type"."deletedAt" AS "Package__packageSubtypes_subtype_type_deletedAt", "Package__packageProducts_product"."id" AS "Package__packageProducts_product_id", "Package__packageProducts_product"."name" AS "Package__packageProducts_product_name", "Package__packageProducts_product"."price" AS "Package__packageProducts_product_price", "Package__packageProducts_product"."notes" AS "Package__packageProducts_product_notes", "Package__packageProducts_product"."deletedAt" AS "Package__packageProducts_product_deletedAt", "Package__packageProducts_product"."subtypeId" AS "Package__packageProducts_product_subtypeId", "Package__packageProducts_product_subtype"."id" AS "Package__packageProducts_product_subtype_id", "Package__packageProducts_product_subtype"."name" AS "Package__packageProducts_product_subtype_name", "Package__packageProducts_product_subtype"."deletedAt" AS "Package__packageProducts_product_subtype_deletedAt", "Package__packageProducts_product_subtype"."typeId" AS "Package__packageProducts_product_subtype_typeId", "Package__packageProducts_product_subtype_type"."id" AS "Package__packageProducts_product_subtype_type_id", "Package__packageProducts_product_subtype_type"."name" AS "Package__packageProducts_product_subtype_type_name", "Package__packageProducts_product_subtype_type"."deletedAt" AS "Package__packageProducts_product_subtype_type_deletedAt" FROM "package" "Package" LEFT JOIN "package_product" "Package__packageProducts" ON "Package__packageProducts"."packageId"="Package"."id"  LEFT JOIN "product" "Package__packageProducts_product" ON "Package__packageProducts_product"."id"="Package__packageProducts"."productId" AND ("Package__packageProducts_product"."deletedAt" IS NULL)  LEFT JOIN "product_subtype" "Package__packageProducts_product_subtype" ON "Package__packageProducts_product_subtype"."id"="Package__packageProducts_product"."subtypeId" AND ("Package__packageProducts_product_subtype"."deletedAt" IS NULL)  LEFT JOIN "product_type" "Package__packageProducts_product_subtype_type" ON "Package__packageProducts_product_subtype_type"."id"="Package__packageProducts_product_subtype"."typeId" AND ("Package__packageProducts_product_subtype_type"."deletedAt" IS NULL)  LEFT JOIN "package_subtype" "Package__packageSubtypes" ON "Package__packageSubtypes"."packageId"="Package"."id"  LEFT JOIN "product_subtype" "Package__packageSubtypes_subtype" ON "Package__packageSubtypes_subtype"."id"="Package__packageSubtypes"."subtypeId" AND ("Package__packageSubtypes_subtype"."deletedAt" IS NULL)  LEFT JOIN "product_type" "Package__packageSubtypes_subtype_type" ON "Package__packageSubtypes_subtype_type"."id"="Package__packageSubtypes_subtype"."typeId" AND ("Package__packageSubtypes_subtype_type"."deletedAt" IS NULL)  LEFT JOIN "product_subtype" "Package__packageSubtypes_subtype" ON "Package__packageSubtypes_subtype"."id"="Package__packageSubtypes"."subtypeId" AND ("Package__packageSubtypes_subtype"."deletedAt" IS NULL)  LEFT JOIN "product_type" "Package__packageSubtypes_subtype_type" ON "Package__packageSubtypes_subtype_type"."id"="Package__packageSubtypes_subtype"."typeId" AND ("Package__packageSubtypes_subtype_type"."deletedAt" IS NULL)  LEFT JOIN "product" "Package__packageProducts_product" ON "Package__packageProducts_product"."id"="Package__packageProducts"."productId" AND ("Package__packageProducts_product"."deletedAt" IS NULL)  LEFT JOIN "product_subtype" "Package__packageProducts_product_subtype" ON "Package__packageProducts_product_subtype"."id"="Package__packageProducts_product"."subtypeId" AND ("Package__packageProducts_product_subtype"."deletedAt" IS NULL)  LEFT JOIN "product_type" "Package__packageProducts_product_subtype_type" ON "Package__packageProducts_product_subtype_type"."id"="Package__packageProducts_product_subtype"."typeId" AND ("Package__packageProducts_product_subtype_type"."deletedAt" IS NULL)
error: error: table name "Package__packageSubtypes_subtype" specified more than once
    at Parser.parseErrorMessage (/home/ivan/code/empanada/server/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/home/ivan/code/empanada/server/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/home/ivan/code/empanada/server/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/home/ivan/code/empanada/server/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:376:20)
    at Socket.EventEmitter.emit (node:domain:470:12)
    at addChunk (node:internal/streams/readable:304:12)
    at readableAddChunk (node:internal/streams/readable:279:9)
    at Socket.Readable.push (node:internal/streams/readable:218:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:192:23) {
  length: 146,
  severity: 'ERROR',
  code: '42712',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '445',
  routine: 'checkNameSpaceConflicts'
}

Steps to Reproduce

Package.find({ relations: ["packageProducts", "packageSubtypes"] })

@Entity()
export class Package extends BaseEntity {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: String;

    @OneToMany(() => PackageSubtype, (subtype) => subtype.package, {
        nullable: true,
        eager: true,
        cascade: ["remove", "insert", "update"],
    })
    @JoinTable()
    packageSubtypes: PackageSubtype[];

    @OneToMany(() => PackageProduct, (product) => product.package, {
        nullable: true,
        eager: true,
        cascade: ["remove", "insert", "update"],
    })
    @JoinTable()
    packageProducts: PackageProduct[];

    @Column("float")
    cost: number;

    @Column({ nullable: true })
    expiration: Date;
}

My Environment

Dependency	Version
Operating System	Ubuntu
Node.js version	v15.3.0
Typescript version	4.3.5
TypeORM version	0.2.37

Additional Context

On the version 0.2.34 of TypeORM, this bug does not exist.

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`	no
`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.

franzos commented 3 years ago

I'm seeing a similar issue for a specific relationship (v0.2.37)

@ManyToMany(() => UserEntity)
@JoinTable()
affectedUsers: UserEntity[]

When I try to query this like so:

db.findOneOrFail({ where: { id }, relations: ['affectedUsers'] })

This fails with ERROR: table name "ResourceAuthorizationPolicyEntity_ResourceAuthorizationPolicyEn" specified more than once. However, when I use the query builder to leftJoinAndSelect, all is fine.

ailchenkoDynamo commented 2 years ago

I have same issue

ailchenkoDynamo commented 2 years ago

@franzos @scratchmex Did you resolve your issue?

scratchmex commented 2 years ago

@franzos @scratchmex Did you resolve your issue?

To be honest I don't remember. This was long ago :/

franzos commented 2 years ago

Unfortunately no. I checked the commits; this happened during a major refactor, so no trace on how I fixed it.

Looking back, the error also seems unrelated to the specific query / column I mentioned; I'd suggest to double-check that you really don't have any overly long table, or column names.

typeorm / typeorm