Deleting PK column does not prevent MSSQL from ordering by PK on LIMIT 1 queries.

[rharriso]

Issue Creation Checklist

• I understand that my issue will be automatically closed if I don't fill in the requested information
• I have read the contribution guidelines

Bug Description

Initially observed in PR #14549 . Setting limit=1 on findOne queries exposed a bug related to MSSQL column deletion:

The below test deletes the User.id column on line 666 🤘. The subsequent findOne query sorts by the User.id column

sequelize/test/integration/associations/belongs-to.test.js

Lines 662 to 682 in 428a85b

	it('should support a non-primary key as the association column on a target without a primary key', async function () {
	const User = this.sequelize.define('User', { username: { type: DataTypes.STRING, unique: true } });
	const Task = this.sequelize.define('Task', { title: DataTypes.STRING });
	User.removeAttribute('id');
	Task.belongsTo(User, { foreignKey: 'user_name', targetKey: 'username' });
	await this.sequelize.sync({ force: true });
	const newUser = await User.create({ username: 'bob' });
	const newTask = await Task.create({ title: 'some task' });
	await newTask.setUser(newUser);
	const foundTask = await Task.findOne({ where: { title: 'some task' } });
	const foundUser = await foundTask.getUser();
	await expect(foundUser.username).to.equal('bob');
	const foreignKeysDescriptions = await this.sequelize.getQueryInterface().getForeignKeyReferencesForTable('Tasks');
	expect(foreignKeysDescriptions[0]).to.includes({
	referencedColumnName: 'username',
	referencedTableName: 'Users',
	columnName: 'user_name',
	});
	});

Logging the sql string in abstract/query-interface.js#select:

SELECT [id], [title], [createdAt], [updatedAt], [user_name] FROM [Tasks] AS [Task] WHERE [Task].[title] = N'some task' ORDER BY [Task].[id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;
SELECT [username], [createdAt], [updatedAt] FROM [Users] AS [User] WHERE [User].[username] = N'bob' ORDER BY [User].[id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

Notice [User].[id] in the order statement.

---

Diging a bit deeper. primaryKeyField is used to order the subqueries. You'll notice in my comment above that the primary key would have been deleted in src/dialects/mssql/query-generator.js#addLimitAndOffeset

See the following console output.

console.log('shouldn't be', Object.keys(model.rawAttributes).join(' 🤝 '));
shouldn't be username 🤝 createdAt 🤝 updatedAt
console.log('still here','${this.quoteTable(options.tableAs || model.name)}.${this.quoteIdentifier(model.primaryKeyField)}'
still here [User].[id]
'😭 ${model.primaryKeyField}'
😭 id

Not quite sure what the resolution is here. If the PK has been deleted.....

Reproducible Example

Run yarn start-mssql-latest && yarn build && yarn test-integration-mssql -- --grep "should support a non-primary key as the association column on a target without a primary ke", on commit fcbea07b3c92a56fc1bf2d992999f2a582222ef4.

What do you expect to happen?

Tests should pass, sorting by primary key might not be necessary.

What is actually happening?

Query attempts to sort by deleted key.

Environment

• Sequelize version: 7.1.0
• Node.js version: 16.14.0
• If TypeScript related: TypeScript version:
• Database & Version: MSSQL latest & oldest
• Connector library & Version:

Would you be 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 will need guidance.
• No, I don't have the time, but my company or I are supporting Sequelize through donations on OpenCollective.
• No, I don't have the time, and I understand that I will need to wait until someone from the community or maintainers is interested in resolving my issue.

---

Indicate your interest in the resolution of this issue by adding the 👍 reaction. Comments such as "+1" will be removed.

Originally posted by @rharriso in #14549 (comment)

[ephys]

Somewhat related to #14194

mssql needs ORDER BY to be specified if a limit or offset is specified.
Sequelize adds the primary key by default if none is specified. It should not try to add it if it does not exist however.

[github-actions]

This issue has been automatically marked as stale because it has been open for 14 days without activity. It will be closed if no further activity occurs within the next 14 days. If this is still an issue, just leave a comment or remove the "stale" label. 🙂