.from in .whereIn subquery incorrectly results in parent entity table

[MordantWastrel]

The following Quick scope is producing an erroneous table prefix for the 'where in' clause.

     function scopeNotInCloneQueue( query, required string seasonUID ) {
                return query.whereNotIn( "registrationID", function( sQ ) {
                        sQ.from( "registration_clone_queue" )
                                .select( "registrationID" )
                                .join(
                                        "registration_clone_queue_batches",
                                        "registration_clone_queue.batchID",
                                        "=",
                                        "registration_clone_queue_batches.batchID"
                                )
                                .whereTargetSeasonUID( seasonUID )
                } );
        }

What did you expect to happen?

The subquery should be selecting registrationID from registration_clone_queue.

What actually happened instead?

The SQL produced was to SELECT registrationID FROM registrations (the table of the parent entity where the scope lives). registrationID is the primary key.

A workaround is to specify an alias in the from clause and refer to the registraitonID explicitly with the table prefix.

Environment

List the software versions you're using:

• Quick: 7.3.1 / Lucee 5

[elpete]

I'd love if you could test this on the latest Quick or provide a runnable example

[MordantWastrel]

The query produced is unchanged in qb 13 / quick 12. The workaround still works.

For a runnable example, you can just ditch the seasonUID reference since we don't care about the actual result here and just the query syntax. That would be:

     function scopeNotInCloneQueue( query ) {
                return query.whereNotIn( "registrationID", function( sQ ) {
                        sQ.from( "registration_clone_queue" )
                                .select( "registrationID" )
                                .join(
                                        "registration_clone_queue_batches",
                                        "registration_clone_queue.batchID",
                                        "=",
                                        "registration_clone_queue_batches.batchID"
                                )
                       
                } );
        }