Named statement parameters are unnecessarily converted to positional on Oracle

[morozov]

Bug Report

Q	A
Version	2.10.0

Summary

The current library design around handling named and positional parameters in prepared statements is a bit of a mess.

Current behaviour

1. When a statement containing named parameters is executed via the wrapper connection's executeQuery(), the parameters get unconditionally converted to the positional ones via SQLParserUtils::expandListParameters().
2. The OCI8Statement class converts positional parameters back to the named ones via OCI8Statement::convertPositionalToNamedPlaceholders().

Therefore:

1. Parameter names get lost in translation: SELECT :COL1 COL1 FROM DUAL → SELECT :param1 COL1 FROM DUAL.
2. Extra overhead on query parsing and conversion back and forth.
3. The wrapper connection implements driver-specific logic which is wrong by design.

How to reproduce

Currently, there's no mechanism to observe the queries in the form in which they hit the underlying DB driver. See the data flow in the new data item added to queryConversionProvider() added in #3738.

Expected behavior

1. The wrapper statement doesn't contain any conversion logic.
2. The driver statement converts the unsupported parameters to the supported ones.
3. No unnecessary conversion happens.

Underlying driver support for parameter binding

Driver	Positional	Named	Expected improvement
ibm_db2	Y	N
mysqli	Y	N
oci8	N	Y	✓
pdo_*	Y	Y	✓
sqlsrv	Y	N

[RafaelKr]

I just ran into this while writing a migration. It throws with An exception occurred while executing a query: SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters.

My query to update Postgres JSONB columns:

UPDATE
    "my_table"
SET
    data[(:person)][(:key)] = NULL
WHERE
    type='financing'
    AND data[(:person)][(:key)]['#scalar'] = :removed_case_value

used via:

$this->addSql($sql, [
    'person' => $person,
    'key' => $key,
    'removed_case_value' => \sprintf('"%s"', $removedCaseValue),
]);

results in:

UPDATE
    "my_table"
SET
    data[(:person)][(:key)] = NULL
WHERE
    type='financing'
    AND data[(:person)][(:key)]['#scalar'] = ?

with parameters:

[
  0 => '"value"',
]

Edit:
I think there is another issue coming into play here. The Parser doesn't recognize named parameters wrapped inside square brackets ((:person) and (:key)). It's choosing the sqlPattern

dbal/src/SQL/Parser.php

Lines 92 to 94 in 3bf33b5

	$this->sqlPattern => static function (string $sql) use ($visitor): void {
	$visitor->acceptOther($sql);
	},

instead of the self::NAMED_PARAMETER branch. Thus those parameters are not passed to the final query.

Edit:
I'm using the following workaround for now:

$preparedSql = \strtr($sql, [
    '(:person)' => $this->connection->quote($person),
    '(:key)' => $this->connection->quote($key),
]);

$this->addSql($preparedSql, [
    'removed_case_value' => \sprintf('"%s"', $removedCaseValue),
]);

:removed_case_value is still converted to ?, but as there is now only that one positional parameter it works.