Bound parameters prevent condition pushdown into a grouped view, while CAST enables it

A prepared SELECT against a grouped view is substantially slower when using bare parameter markers than when wrapping those markers in CAST(... AS CHAR CHARACTER SET utf8mb4).

Both queries return identical results. The variable types are reported as VARCHAR by information_schema.USER_VARIABLES, and the relevant column and variable character sets and collations match.

The issue reproduces directly in SQL without PHP or stored procedures.

Reproduction

The attached SQL script creates an example table and a view that groups by x and y and calculates SUM(amount).

Without casts:

SET @x = 'foo';

SET @y = 'bar';

PREPARE plain_test FROM '

SELECT SQL_NO_CACHE *

FROM cast_demo.example_view

WHERE x = ? AND y = ?

';

EXECUTE plain_test USING @x, @y;

DEALLOCATE PREPARE plain_test;

With casts:

SET @x = 'foo';

SET @y = 'bar';

PREPARE cast_test FROM '

SELECT SQL_NO_CACHE *

FROM cast_demo.example_view

WHERE x = CAST(? AS CHAR CHARACTER SET utf8mb4)

AND y = CAST(? AS CHAR CHARACTER SET utf8mb4)

';

EXECUTE cast_test USING @x, @y;

DEALLOCATE PREPARE cast_test;

Actual behavior

The bare-parameter version does not push the x/y restrictions into the inner grouped query. The cast version does, allowing an indexed lookup on the underlying table using ix_xy.

In a repeated command-line test, without SQL_NO_CACHE added yet:

• Without CAST: approximately 65–68 ms.
• With CAST: approximately 1 ms.

Subsequent tests with SQL_NO_CACHE and reversed execution order retained the performance difference.

Expected behavior

I would expect both forms to allow condition pushdown into the grouped view and efficient access through ix_xy, given the matching string types, character sets and collations.

Optimizer trace observations

With CAST, the outer condition initially contains:

example_view.x = cast('foo' as char charset utf8mb4)

and example_view.y = cast('bar' as char charset utf8mb4)

The constant_propagation step simplifies it to:

example_view.x = 'foo' and example_view.y = 'bar'

Those restrictions then appear in the inner grouped query.

Without CAST, the outer condition is also displayed using the bound values, but the corresponding restrictions do not appear inside the grouped query.

For the underlying example_data table, the traces show estimated rows_for_plan of:

• Without CAST: 100,399.
• With CAST: 100.

These are optimizer estimates, not actual row counts.

Additional checks

• Casting when assigning the user variables, while retaining bare placeholders in the prepared query, does not resolve the slowdown.
• Explicitly setting SET NAMES utf8mb4 COLLATE utf8mb4_general_ci, reassigning the variables and preparing the query again does not resolve it.
• Configuring default-character-set=utf8mb4 for a new command-line client connection does not resolve it; effective connection settings were verified.
• Reversing query execution order, resetting the query cache and using SQL_NO_CACHE do not eliminate the difference.

Workaround

Wrap both parameter markers in CAST(... AS CHAR CHARACTER SET utf8mb4) inside the prepared SQL.

Related issue

MDEV-35561 appears potentially related, but its reproduction uses UNION, whereas this example uses GROUP BY and SUM. I have not established whether the underlying cause is the same.

Discussion

Stack Overflow discussion

Could you confirm whether this is a known condition-pushdown limitation, a separate optimizer issue or potentially expected behavior?