Hello!

Today, my colleague@samsonradu and I noticed an issue while using Oracle database and regarding theDoctrineDbalAdapter, potentially introduced by PR#50507.

Basically, by making positional parameters lazily injected, the order of those parameters are no more preserved, and we end up with totally broken SQL queries. Here are more details:

Failed to save key "ulm_ac_rbac_action_roles" of type array: An exception occurred while executingMERGE INTO cache_items USING DUAL ON (item_id = ?) WHEN NOT MATCHED THEN INSERT (item_id, item_data, item_lifetime, item_time) VALUES (?, ?, ?, ?) WHEN MATCHED THEN UPDATE SET item_data = ?, item_lifetime = ?, item_time = ? with params [  3600,   1709126855,   3600,   1709126855,   "EeA4QGjhnZ:ulm_ac_rbac_action_roles",   "EeA4QGjhnZ:ulm_ac_rbac_action_roles",   "\x00...\x69..."]

The introduction of a lazy loading mechanism in this PR has led to issues with the ordering of bound values in SQL statements. New keys added by the lazy loading process are appended at the end of the parameters array, disrupting the intended logical order. See it here:

/app/vendor/symfony/cache/Adapter/DoctrineDbalAdapter.php:354:object(Doctrine\DBAL\Statement)[1878]  protected 'sql' => string 'MERGE INTO cache_items USING DUAL ON (item_id = ?) WHEN NOT MATCHED THEN INSERT (item_id, item_data, item_lifetime, item_time) VALUES (?, ?, ?, ?) WHEN MATCHED THEN UPDATE SET item_data = ?, item_lifetime = ?, item_time = ?' (length=223)  protected 'params' =>     array (size=8)      4 => int 3600      5 => int 1709713419      7 => int 3600      8 => int 1709713419      1 => string 'q55PWWD1R0:ulm_ac_rbac_action_roles' (length=35)      2 => string 'q55PWWD1R0:ulm_ac_rbac_action_roles' (length=35)      3 => string 'value1'      6 => string 'value1'

In here, you can see that theparams are not correctly ordered and this is breaking the SQL query.

IMHO, I think this issue should be fixed indoctrine/dbal with a simpleksort onparams, but I'm not sure, that's the reason why I comment in here.
Alternatively, we could implement explicit parameter mapping instead of positional indexes to ensure each bound value maintains its correct order, regardless of when it's added.

From thedoctrine/dbal doc, I see:

Support for positional and named prepared statements varies between the different database extensions. PDO implements its own client side parser so that both approaches are feasible for all PDO drivers. OCI8/Oracle only supports named parameters, but Doctrine implements a client side parser to allow positional parameters also.

Maybe this is something to fix somewhere else?