Dirty read in MSSQL generates invalid SQL query

[KleaTech]

Issue description

When I use setLock('dirty_read') in an inner query the resulted MSSQL query is invalid.

Expected Behavior

The code below should result in a valid MSSQL query.

this.createQueryBuilder('rc')
    .select()
    .setLock('dirty_read')
    .innerJoin(
        query => {
            return query
                .setLock('dirty_read')
                .from(MyEntity, 'rc')
                .select('rc.user_id, MAX(rc.id) AS id')
                .where('rc.section_id = :sectionId', {
                    sectionId: 42
                })
                .groupBy('rc.user_id');
        },
        'last_edit',
        'last_edit.user_id = rc.user_id AND last_edit.id = rc.id'
    )
    .where('section_id = :sectionId', { sectionId: 42 });

Actual Behavior

The code results in an SQL error:
Incorrect syntax near 'WITH'.
and
Incorrect syntax near 'NOLOCK'. Expecting '(', or SELECT.

The generated SQL query is the following:

SELECT  "rc"."id" AS "rc_id", 
        "rc"."user_id" AS "rc_user_id", 
        "rc"."section_id" AS "rc_section_id"
FROM "myTable" "rc" WITH (NOLOCK) 
INNER JOIN (
    SELECT "rc"."user_id", MAX("rc"."id") AS id 
    FROM "myTable" "rc" WITH (NOLOCK) 
    WHERE "rc"."section_id" = @0 
    GROUP BY "rc"."user_id"
) "last_edit" WITH (NOLOCK) ON last_edit.user_id = "rc"."user_id" AND last_edit.id = "rc"."id" 
WHERE section_id = @1

On line 11 after "last_edit" the WITH (NOLOCK) is invalid.

Steps to reproduce

You can use the entity below, create and fill the table according to the code example above.

@Entity({ name: 'myTable' })
export class MyEntity {

    @PrimaryGeneratedColumn()
    id!: number;

    @Column({ name: 'user_id' })
    @Index('IDX_myIndex)
    userId!: string;

    @Column({ name: 'section_id' })
    sectionId!: number;
}

My Environment

Dependency	Version
Operating System	MacOS Ventura
Node.js version	v18.13.0
Typescript version	4.9.4
TypeORM version	0.3.11

Additional Context

This issue was introduced with commit 3284808
as part of feature
#4764

MySQL and SQLite works fine. My assumption is that only MSSQL is affected.

As a workaround I was able to use READ UNCOMMITTED transaction instead of dirty_read on the queries. But I'm not sure if this approach has any unwanted side effects.

Relevant Database Driver(s)

• aurora-mysql
• aurora-postgres
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• spanner
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

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

No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.