Using `contain()` with subquery strategy and nested `innerJoinWith()` with where condition in the last one produces wrong SQL

[batopa]

Description

Info

CakePHP 5.4.1
MySQL 8.4

It seems an issue introduced with the recent changes of default strategy from select to subquery in hasMany and belongsToMany associations.

Step to reproduce

1. add the fixture core.Profiles in \Cake\Test\TestCase\ORM\Query\QueryRegressionTest
2. add the unit test and launch it (ensure to use MySQL for test)

public function testContainAndNestedInnerJoinWith(): void
{
    static::setAppNamespace();
    $Articles = $this->getTableLocator()->get('Articles');
    $Articles->hasMany('Comments');
    $Articles->Comments->getTarget()->belongsTo('Profiles', [
        'foreignKey' => 'user_id'
    ]);

    $query = $Articles
            ->find()
            ->contain(['Tags'])
            ->innerJoinWith('Comments', function (SelectQuery $q) {
                return $q->innerJoinWith('Profiles', function (SelectQuery $q2) {
                    return $q2->where(['Profiles.last_name' => 'iglesias']);
                });
            })
            ->all();

    debug($query);
}

Expected Behavior

The query is executed correctly.

Actual Behavior

An error occurs.

There was 1 error:

1) Cake\Test\TestCase\ORM\Query\QueryRegressionTest::testContainAndNestedInnerJoinWith
Cake\Database\Exception\QueryException: [test] SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Profiles.last_name' in 'on clause'
Query: SELECT ArticlesTags.article_id AS Tags_CJoin__article_id, ArticlesTags.tag_id AS Tags_CJoin__tag_id, Tags.id AS Tags__id, Tags.name AS Tags__name, Tags.description AS Tags__description, Tags.created AS Tags__created FROM tags Tags INNER JOIN articles_tags ArticlesTags ON Tags.id = ArticlesTags.tag_id INNER JOIN (SELECT Articles.id AS Articles__id FROM articles Articles INNER JOIN comments Comments ON (Profiles.last_name = 'iglesias' AND Articles.id = Comments.article_id) INNER JOIN profiles Profiles ON (Profiles.last_name = 'iglesias' AND Profiles.id = Comments.user_id) GROUP BY Articles.id) Articles ON ArticlesTags.article_id = Articles.Articles__id

You can see that the SQL INNER JOIN comments Comments ON (Profiles.last_name = 'iglesias' AND Articles.id = Comments.article_id) is wrong. The condition Profiles.last_name = 'iglesias' should be stay just in the inner join with profiles table.

Workaround

Forcing to use select strategy resolves the issue.

$query = $Articles
    ->find()
    ->contain(['Tags' => ['strategy' => 'select']])
    ->innerJoinWith('Comments', function (SelectQuery $q) {
        return $q->innerJoinWith('Profiles', function (SelectQuery $q2) {
            return $q2->where(['Profiles.last_name' => 'iglesias']);
        });
    })
    ->all();

It seems that preparing subquery for Tags breaks the main query modifying the inner join conditions.

CakePHP Version

5.4.1

PHP Version

No response

[dereuromark]

Check 5.x
Should be fixed already.

[batopa]

Uhm... it happens in 5.x branch too.

[dereuromark]

I see - you are right and this is a different issue.
I opened a PR you can check, this should solve the issue for you.

[batopa]

Yes, it seems resolved with the related PR! Thanks!