MySQL FIELD() error when an empty asset relation is normalised with fixedOrder()

[romainpoirier]

Description

On Craft CMS 5.10.13.1 with MySQL 8.0, opening a Control Panel entry editor can fail with:

SQLSTATE[42000]: Syntax error or access violation: 1582 Incorrect parameter count in the call to native function 'FIELD'

The generated SQL contains:

ORDER BY FIELD(`elements`.`id`)

Suspected cause

craft\\fields\\Assets::normalizeValue() calls fixedOrder() after filtering the submitted asset IDs, without checking whether the resulting array is empty:

$query
    ->id(array_values(array_filter($value)))
    ->fixedOrder();

Craft’s MySQL query builder then generates FIELD(column) with no values. BaseRelationField::normalizeValue() already guards its equivalent FixedOrderExpression call with if (!empty($value)), which suggests the Assets-specific path may be missing the same guard.

Environment

• Craft CMS: 5.10.13.1
• Yii: 2.0.55
• MySQL: 8.0.x
• PHP: 8.4.x

Expected behaviour

An empty asset relation should produce an empty result or skip fixed ordering, without generating invalid SQL.

Actual behaviour

The request fails with MySQL error 1582 because FIELD() is called with only its column argument.

Additional context

The error was observed while opening an existing entry in the Control Panel, not while saving content. A temporary application-level query-builder override avoids the exception, but a guard in the relation-field normalisation path would be safer and more targeted.

[linear-code]

CMS-2321

[romainpoirier]

Follow-up: exact reproduction narrowed down

The failure is reproducible while rendering the CP editor, not only during draft application.

For an entry with an empty hierarchical Categories field, Craft 5.10.13.1 reaches craft\\fields\\Categories::normalizeValue() with an empty ID set. The method then unconditionally creates:

$finalIds = array_map(fn(Category $category) => $category->id, $categories);
$query->where(['elements.id' => $finalIds]);
$query->orderBy([new FixedOrderExpression('elements.id', $finalIds, Craft::$app->getDb())]);

When $finalIds is empty, MySQL receives:

WHERE (0=1) ... ORDER BY FIELD(`elements`.`id`)

The affected field in the reproduced entry is a hierarchical Categories field with maintainHierarchy: true and no selected categories. Other relation fields on the same entry contain valid IDs. The stack trace is:

Categories::inputHtml()
BaseRelationField::normalizeValueForInput()
ElementQuery::all()
SQLSTATE[42000] ... Incorrect parameter count ... FIELD

This suggests the guard should be added before creating FixedOrderExpression when $finalIds is empty, or the query should be short-circuited as an empty result.

[i-just]

Hi, thanks for reporting! I raised a PR for this.

[brandonkelly]

Craft 5.10.13.2 is out with a fix. Thanks again.