[php-src] Issue #12588: Unexpected empty result in Oracle v23 database when bind params are used
| From: | mvorisek | Date: | Wed, 01 Nov 2023 15:09:17 +0000 |
| Subject: | [php-src] Issue #12588: Unexpected empty result in Oracle v23 database when bind params are used | ||
| Groups: | php.bugs | ||
| Request: | Send a blank email to php-bugs+get-245714@lists.php.net to get a copy of this message | ||
Issue: https://github.com/php/php-src/issues/12588
Author: mvorisek
### Description
The following code:
```
<?php
$log = [
['CREATE TABLE "t" ("id" NUMBER(10) NOT NULL, "name"
VARCHAR2(1020) DEFAULT NULL NULL, PRIMARY KEY("id"))', []],
['insert into "t" ("id", "name") values (1, :xxaaaa)',
[':xxaaaa' => 'James']],
['insert into "t" ("id", "name") values (2, :xxaaaa)',
[':xxaaaa' => 'Roman']],
['insert into "t" ("id", "name") values (3, :xxaaaa)',
[':xxaaaa' => 'Jennifer']],
['insert into "t" ("id", "name") values (4, :xxaaaa)',
[':xxaaaa' => 'John']],
['select "id", "name" from "t" where "id" =
4', []],
[
'select "id", "name" from "t"'
. ' where ('
. '(select count(*) from "t" "ti" where "name" like
\'J%\' and "ti"."name" = "t"."name"'
. ') = cast(:a as INTEGER)'
. ' ) and "id" = cast(:b as INTEGER)',
[
':a' => 1,
':b' => 4,
],
],
];
$conn = oci_connect('system', 'atk4_pass', '127.0.0.1/free');
var_dump(get_debug_type($conn)); // resource (oci8 connection)
foreach ($log as $l) {
$statement = oci_parse($conn, $l[0]);
foreach ($l[1] as $k => $v) {
oci_bind_by_name($statement, $k, $v);
}
oci_execute($statement);
try {
if (oci_fetch_all($statement, $rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW | OCI_ASSOC))
{
}
} catch (\Exception $e) {
if (str_contains($e->getMessage(), 'ORA-24374: define not done before fetch or
execute and fetch')) {
$rows = [];
}
}
if (str_starts_with($l[0], 'select ')) {
print_r($l);
print_r($rows);
}
}
```
Resulted in this output:
```diff
- actual
+ expected
string(26) "resource (oci8 connection)"
Array
(
[0] => select "id", "name" from "t" where "id" = 4
[1] => Array
(
)
)
Array
(
[0] => Array
(
[id] => 4
[name] => John
)
)
Array
(
[0] => select "id", "name" from "t" where ((select count(*)
from "t" "ti" where "name" like 'J%' and
"ti"."name" = "t"."name") = cast(:xxaaac as INTEGER) ) and
"id" = cast(:xxaaad as INTEGER)
[1] => Array
(
[:xxaaac] => 1
[:xxaaad] => 4
)
)
Array
(
+ [0] => Array
+ (
+ [id] => 4
+ [name] => John
+ )
)
```
The issue is present only when:
- Oracle v23 database is used (when Oracle database v18 or v21 is used, the issue is not present)
- and bind params are used (both, when
1 or 4 is hardcoded in the query,
the issue is not present)
- present with oci8 and also with pdo_oci extension
Verified with Docker image:
```
docker run -it -p 1521:1521 -eORACLE_PASSWORD=atk4_pass gvenzl/oracle-free:23-slim-faststart
```
With Oracle v18 database Docker image:
```
docker run -it -p 1521:1521 -eORACLE_PASSWORD=atk4_pass gvenzl/oracle-xe:18-slim-faststart
```
the issue is not present (DSN /xe vs /free must be used to connect).
Is it php-src issue or is this a bug in Oracle v23 database?
### PHP Version
any
### Operating System
any