<?php

function execute_query($connection, $sql, $params = []) {
    echo "SQL: {$sql}" . PHP_EOL;
    $stmt = oci_parse($connection, $sql);
    foreach ($params as $key => $value) {
        echo "  - with binding :{$key} => {$value}" . PHP_EOL;
        oci_bind_by_name($stmt, $key, $params[$key]);
    }
    oci_execute($stmt);
    if ($params && oci_fetch_all($stmt, $results, null, null, OCI_FETCHSTATEMENT_BY_ROW)) {
        oci_free_statement($stmt);
        return $results;
    }
    oci_free_statement($stmt);
    return [];
}

// Configure this to point to your Oracle instance.
$dbuser = '';
$dbpass = '';
$dbhost = '';
$dbport = 1521;
$dbname = 'XE';

$con = oci_connect($dbuser, $dbpass, $dbhost . ':' . $dbport . '/' . $dbname);

$con || die();

// Get information about the DB.
print_r(
    execute_query($con, 'SELECT version FROM V$INSTANCE WHERE ROWNUM = :rn', ['rn' => 1]));

// Create the tables and add a few records.
execute_query($con, 'CREATE TABLE employees (id NUMBER(10), username VARCHAR2(30))');
// Four employees.
execute_query($con, "INSERT INTO employees (id, username) VALUES (1, 'Emp 1')");
execute_query($con, "INSERT INTO employees (id, username) VALUES (2, 'Emp 2')");
execute_query($con, "INSERT INTO employees (id, username) VALUES (3, 'Emp 3')");
execute_query($con, "INSERT INTO employees (id, username) VALUES (4, 'Emp 4')");

execute_query($con, 'CREATE TABLE departments (id NUMBER(10), name VARCHAR2(30))');
// Two departments.
execute_query($con, "INSERT INTO departments (id, name) VALUES (1, 'Dpto 1')");
execute_query($con, "INSERT INTO departments (id, name) VALUES (2, 'Dpto 2')");

execute_query($con, 'CREATE TABLE contracts (id NUMBER(10), employeeid NUMBER(10),
    departmentid NUMBER(10), tstart NUMBER(10), tend NUMBER(10))');
// Only 2 users with contracts, one of them in 2 departments.
execute_query($con, "INSERT INTO contracts (id, employeeid, departmentid, tstart, tend) VALUES (1, 1, 1, 1000, 3000)");
execute_query($con, "INSERT INTO contracts (id, employeeid, departmentid, tstart, tend) VALUES (2, 2, 1, 0, 1000)");
execute_query($con, "INSERT INTO contracts (id, employeeid, departmentid, tstart, tend) VALUES (3, 2, 2, 1000, 0)");

$query = '
    SELECT e.username, d.name
      FROM contracts c
      JOIN employees e ON e.id = c.employeeid
      JOIN departments d ON d.id = c.departmentid
     WHERE c.tstart <= :now1
       AND (c.tend = 0 OR c.tend >= :now2)';

// Seems to work, it returns the 2 contracts that NOW are enabled (NOW between tstart and tend)
echo PHP_EOL . PHP_EOL;
echo "CORRECT: 2 contracts are actually enabled. 2 records returned" . PHP_EOL;
print_r(
    execute_query($con, $query, ['now1' => 2000, 'now2' => 2000]));

// Let's add a silly condition that all records fulfil (department != 1000000). The same 2 records are returned.
$newquery = $query . " AND d.id <> 1000000";
echo PHP_EOL . PHP_EOL;
echo "CORRECT: The new condition is valid for all contracts, hence, same 2 records returned" . PHP_EOL;
print_r(
    execute_query($con, $newquery, ['now1' => 2000, 'now2' => 2000]));

// Let's pass the same silly condition via binding instead of harcoded. Good habit! Same 2 records are expected.
$newquery = $query . " AND d.id <> :extraparam";
echo PHP_EOL . PHP_EOL;
echo "CORRECT: If the new condition is passed via binding (by value), same 2 records" . PHP_EOL;
print_r(
    execute_query($con, $newquery, ['now1' => 2000, 'now2' => 2000, 'extraparam' => 1000000]));

// Now, let's pass the same silly condition with the value being a defined constant. Same 2 records are expected.
define('MILLION', '1000000');
$newquery = $query . " AND d.id <> :extraparam";
echo PHP_EOL . PHP_EOL;
echo "CORRECT: If the new condition is passed via binding (by constant), same 2 records" . PHP_EOL;
print_r(
    execute_query($con, $newquery, ['now1' => 2000, 'now2' => 2000, 'extraparam' => MILLION]));


// Let's drop the tables for next execution.
execute_query($con, 'DROP TABLE employees');
execute_query($con, 'DROP TABLE departments');
execute_query($con, 'DROP TABLE contracts');

echo "Hence, I've been unable to replicate the problem, so I give up. I leave this script here for the posterity" . PHP_EOL;