Nested condition groups in entity queries are broken

Suggested commit message

git commit -m 'Issue #2527064 by jboxberger, tstoeckler: Nested condition groups in entity queries are broken'

Problem/Motivation

When queries with nested condition groups are compiled the conditions are not compiled correctly leading to invalid SQL being executed and a corresponding exception in PHP.

Consider the following query (taken from the test in the patch):

    $first_and = $query->andConditionGroup()
      ->condition($this->figures . '.color', 'red')
      ->condition($this->figures . '.shape', 'triangle');
    $second_and = $query->andConditionGroup()
      ->condition($this->greetings . '.value', 'merhaba')
      ->condition($this->greetings . '.format', 'format-tr');

    $or = $query->orConditionGroup()
      ->condition($first_and)
      ->condition($second_and);

    $this->queryResults = $query
      ->condition($or)
      ->condition('type', reset($this->bundles))
      ->sort('id')
      ->execute();

The expected SQL in the WHERE clause is something like

WHERE ((color = 'red' AND shape = 'triangle') OR (value = 'merhaba' AND format = 'format-tr')) AND type = 'abc'

The actual SQL, however is something like

WHERE (color = 'red' AND shape = 'triangle') AND (value = 'merhaba' AND format = 'format-tr') AND () AND type = 'abc'

The culprit is the \Drupal\Core\Entity\Query\Sql\Condition::compile() method. It compiles the SQL condition but instead of attaching that to the SQL condition container it always attaches it to the top-level SQL query. That's why the nesting and the inner operator is lost and it also causes the empty AND() because the condition container gets itself added to the query, but it never got any conditions put inside it. Because in the case of non-nested groups the SQL condition container is the top-level SQL query this bug only surfaces for nested groups.

The extensive inline code comment already documents this situation but the code does not match the comment:

    // If this is not the top level condition group then the sql query is
    // added to the $conditionContainer object by this function itself. The
    // SQL query object is only necessary to pass to Query::addField() so it
    // can join tables as necessary. On the other hand, conditions need to be
    // added to the $conditionContainer object to keep grouping.
    $sql_query = $conditionContainer instanceof SelectInterface ? $conditionContainer : $conditionContainer->sqlQuery;

      ...

            $sql_query->condition($sql_condition);

Proposed resolution

Attach the SQL condition to the SQL condition container instead of the SQL query:

-            $sql_query->condition($sql_condition);
+            $conditionContainer->condition($sql_condition);

Remaining tasks

User interface changes

None.

API changes

None.

Data model changes

None.

Beta phase evaluation