Postgres entity queries doesn't treat multiple IN conditions correctly

just tagging...

Maybe I am doing something wrong, but with a PostgreSQL backend, I have made the following query:

    $query = db_select('test', 't1');
    $query->addField('t1', 'name');
    $query->join('test', 't2', 't1.id = t2.id');
    $jobs = ['Singer', 'Drummer'];
    $query->condition('t2.job', $jobs, 'IN');
    $ages = [25, 26, 28];
    $query->condition('t2.age', $ages, 'IN');
    $query->preExecute();
    $query->getArguments();
    $str = (string) $query;
var_dump($str);

The result of that query is:

SELECT t1.name AS name
FROM {test} t1
INNER JOIN {test} t2 ON t1.id = t2.id
WHERE  (t2.job IN (:db_condition_placeholder_0, :db_condition_placeholder_1))
AND (t2.age IN (:db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4))

As far as I can see is everything as it is suppoost to be.

Moving this to the entity system, because:

this issue only occurs for entity queries

Same bug here with Postgresql DB, triggered by this code:

    $storage = \Drupal::entityTypeManager()->getStorage('webform_submission');
    $webform_submissions = $storage->loadByProperties([
        'entity_type' => 'node',
        'entity_id' => $nid,
      ]);

the code doesn't return any result with two conditions (there are entity that match in DB) , return results with only one condition the first or the second.
After a debug session I have found this code in core/lib/Drupal/Core/Entity/Query/Sql/pgsql/Condition.php

      $n = 1;
      // Only use the array values in case an associative array is passed as an
      // argument following similar pattern in
      // \Drupal\Core\Database\Connection::expandArguments().
      foreach ($condition['value'] as $value) {
        $condition['where'] .= 'LOWER(:value' . $n . '),';
        $condition['where_args'][':value' . $n] = $value;
        $n++;
      }

this seems wrong because it restart the placeholder number on every call and this leads to duplicate on multiple conditions.
The proposed patch declare $n as static and this solve the placeholder duplication.

Could you please try to implement a test based on the last steps, which seem to be able to reproduce the problem?
We test core with PostgreSQL and I will be surprised if we don't have Entity queries with multiple conditions.

This issue is a duplicate of #3162603: EntityStorageBase::loadByProperties() is broken on PostgreSQL when using two or more case insensitive properties and that issue has a patch with a fix and testing for that fix.

Thanks @daffie, let's continue the work there.