MySQL: Regression when using take, orderBy, and getMany on a joined relation

[stephenwade]

Issue Description

!!!! This is a regression in the latest patch update. This worked correctly in 0.2.38 and stopped working in 0.2.39.

When I use .take(), .orderBy(), and .getMany(), if the .orderBy() uses a joined relation, I get a database error about a duplicate column.

If I remove .take(), order by a column on the main relation instead of the joined relation (customers instead of contacts in my example), or use .getRawMany() instead of .getMany(), the problem does not occur.

Expected Behavior

These are the queries in the MySQL logs from version 0.2.38:

SELECT
	DISTINCT `distinctAlias`.`customer_id` as "ids_customer_id",
	`distinctAlias`.`contacts_firstName`
FROM
	(
		SELECT
			`customer`.`id` AS `customer_id`,
			`customer`.`name` AS `customer_name`,
			`contacts`.`id` AS `contacts_id`,
			`contacts`.`firstName` AS `contacts_firstName`,
			`contacts`.`lastName` AS `contacts_lastName`,
			`contacts`.`customerId` AS `contacts_customerId`
		FROM
			`Customer` `customer`
			LEFT JOIN `CustomerContact` `contacts` ON `contacts`.`customerId` = `customer`.`id`
	) `distinctAlias`
ORDER BY
	`distinctAlias`.`contacts_firstName` DESC,
	`customer_id` ASC
LIMIT
	10;

SELECT
	`customer`.`id` AS `customer_id`,
	`customer`.`name` AS `customer_name`,
	`contacts`.`id` AS `contacts_id`,
	`contacts`.`firstName` AS `contacts_firstName`,
	`contacts`.`lastName` AS `contacts_lastName`,
	`contacts`.`customerId` AS `contacts_customerId`
FROM
	`Customer` `customer`
	LEFT JOIN `CustomerContact` `contacts` ON `contacts`.`customerId` = `customer`.`id`
WHERE
	`customer`.`id` IN (34, 185, 22, 65, 35, 166, 43, 51, 110, 183)
ORDER BY
	`contacts`.`firstName` DESC

Actual Behavior

This is the query in the MySQL logs from version 0.2.39:

SELECT
	DISTINCT `distinctAlias`.`customer_id` as "ids_customer_id",
	`distinctAlias`.`contacts_firstName`
FROM
	(
		SELECT
			`customer`.`id` AS `customer_id`,
			`customer`.`name` AS `customer_name`,
			`contacts`.`id` AS `contacts_id`,
			`contacts`.`firstName` AS `contacts_firstName`,
			`contacts`.`lastName` AS `contacts_lastName`,
			`contacts`.`customerId` AS `contacts_customerId`,
			`contacts`.`firstName` AS `contacts_firstName`
		FROM
			`Customer` `customer`
			LEFT JOIN `CustomerContact` `contacts` ON `contacts`.`customerId` = `customer`.`id`
	) `distinctAlias`
ORDER BY
	`distinctAlias`.`contacts_firstName` DESC,
	`customer_id` ASC
LIMIT
	10;
-- ^ This query fails

Steps to Reproduce

This is a minimal example. If I remove .take(), use a column from Customer instead of CustomerContact in .orderBy(), or use .getRawMany() instead of .getMany(), the issue does not happen.

I have the following entities:

// customer.entity.ts

import {
	BaseEntity,
	Column,
	Entity,
	OneToMany,
	PrimaryGeneratedColumn,
} from 'typeorm';

import { CustomerContact } from './customer-contact.entity';

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

	@Column()
	name: string;

	@OneToMany(() => CustomerContact, (contact) => contact.customer)
	contacts: CustomerContact[];
}

// customer-contact.entity.ts

import {
	Column,
	Entity,
	Index,
	ManyToOne,
	PrimaryGeneratedColumn,
} from 'typeorm';

import { Customer } from './customer.entity';

@Entity('CustomerContact')
@Index(['firstName', 'lastName'])
export class CustomerContact {
	@PrimaryGeneratedColumn()
	id: number;

	@ManyToOne(() => Customer, (customer) => customer.contacts)
	customer: Customer;

	@Column({ default: '' })
	firstName: string;

	@Column({ default: '' })
	lastName: string;
}

Here is the code that gives an error:

const repo = getRepository(Customer);

const query = repo
	.createQueryBuilder('customer')
	.leftJoinAndSelect('customer.contacts', 'contacts');

query.take(10).orderBy('contacts.firstName', 'DESC').getMany();

await query.getMany();

My Environment

Dependency	Version
Operating System	macOS Big Sur 11.6.1
Node.js version	14.18.1
Typescript version	4.3.5
TypeORM version	0.2.39

Relevant Database Driver(s)

I have only tested this in MySQL.

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

[antonku]

@stephenwade Thanks for the report
This issue is most likely caused by #8118
I will look into this

[antonku]

@pleerock I've opened a PR to revert the PR that led to this regression, please take a look: #8352