Weird results from oracle query with indexes and PreparedStatement

Recently, after upgrading our test env with a new version of application and introducing some new features I've stumbled upon some weird oracle behavior, which shows itself in query results that doesn't make sense and seem to be index dependant.
After doing some investigation I was able to create a minimal conditions to reproduce the issue.

I was experimenting on Oracle21 started in with docker:

docker run --restart always -d -p 1521:1521 -e ORACLE_PASSWORD=system --name oracle-21c-01 gvenzl/oracle-xe:21-slim

Then logging in as system user (system/system) I created another one with few permissions:

CREATE USER test PROFILE DEFAULT IDENTIFIED BY test ACCOUNT UNLOCK;
GRANT CONNECT TO test;
GRANT UNLIMITED TABLESPACE TO test;
GRANT CREATE TABLE TO test;

After that I logged in under this new user (test/test) and executed the following:

CREATE TABLE TEST_BUG
(
    ID         NUMBER(10)    NOT NULL
        CONSTRAINT PK_TEST_BUG PRIMARY KEY,
    TENANT     NUMBER(10)    NOT NULL,
    IDENTIFIER VARCHAR2(255) NOT NULL,
    NAME       VARCHAR2(255) NOT NULL
);

INSERT INTO TEST_BUG
VALUES (10, 1, 'IDENTIFIER', 'TESTBUG');

ALTER TABLE TEST_BUG
    ADD CONSTRAINT UK_NAME_TENANT UNIQUE (NAME, TENANT);
ALTER TABLE TEST_BUG
    ADD CONSTRAINT UK_IDENTIFIER_TENANT UNIQUE (IDENTIFIER, TENANT);

ALTER INDEX PK_TEST_BUG REBUILD;

As you can see here I'm just creating a very simple table with a couple of indexes, but the statements must be executed in this particular order (Also only with index rebuild on the last line I was able to reproduce the issue we're having on the actual environment, even though we do not do it anywhere in the update scripts).

With all that in mind, executing the following simle java code gives me weird results:

public class Main {

    public static void main(String[] args) throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        try (Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "test", "test")) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(
                    "select id from test_bug where tenant = ? and name=? and id<>?"
            )) {
                preparedStatement.setInt(1, 1);
                preparedStatement.setString(2, "TESTBUG");
                preparedStatement.setInt(3, 10);
                ResultSet resultSet = preparedStatement.executeQuery();
                System.out.println(resultSet.next());
                System.out.println(resultSet.getInt(1));
            }
        }
    }
}

And results are:

true
10

Meaning that even though I've inserted a single record with id=10 and in the query asking specifically that id<>10, I still get that recoded as an output instead of getting nothing!

Couple more notes:

• This only works with PreparedStatement, if I try to execute this query with sql developer - works just fine.
• If I remove put id condition explicitly in query (like select id from test_bug where tenant = ? and name=? and id<>10) and remove preparedStatement.setInt(3, 10); - works also fine (no results)
• If I disable PK index with ALTER INDEX PK_TEST_BUG UNUSABLE; - works also fine, no results, until I get index back with ALTER INDEX PK_TEST_BUG REBUILD;
• For oracle driver I'm using here ojdbc11 21.9.0.0, but I've played with different versions - no difference.

Any ideas kindly appreciated!

This is an optimizer bug. If I run the java unchanged and do a SQL trace on it, you see the following the trace file

select id 
from
 test_bug where tenant = :1  and name=:2  and id != :3 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          1           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          1           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID BATCHED TEST_BUG (cr=3 pr=0 pw=0 time=32 us starts=1 cost=0 size=155 card=1)
         1          1          1   BITMAP CONVERSION TO ROWIDS (cr=2 pr=0 pw=0 time=25 us starts=1)
         1          1          1    BITMAP AND  (cr=2 pr=0 pw=0 time=23 us starts=1)
         1          1          1     BITMAP CONVERSION FROM ROWIDS (cr=1 pr=0 pw=0 time=9 us starts=1)
         1          1          1      INDEX RANGE SCAN UK_NAME_TENANT (cr=1 pr=0 pw=0 time=8 us starts=1 cost=0 size=0 card=1)(object id 100690)
         1          1          1     BITMAP CONVERSION FROM ROWIDS (cr=1 pr=0 pw=0 time=13 us starts=1)
         1          1          1      SORT ORDER BY (cr=1 pr=0 pw=0 time=11 us starts=1)
         1          1          1       INDEX RANGE SCAN PK_TEST_BUG (cr=1 pr=0 pw=0 time=3 us starts=1 cost=0 size=0 card=1)(object id 100689)

with the Fetch line Rows column of 1 meaning we found a row. If I disable that plan from use by changing the statement to:

con.prepareStatement("select /*+ full(t) */ id from test_bug t where tenant = ? and name=? and id != ?");

then with the altered plan, we get the result you expect

select /*+ full(t) */ id 
from
 test_bug t where tenant = :1  and name=:2  and id != :3 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          1           0
Fetch        1      0.00       0.00          0          7          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          7          1           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  TABLE ACCESS FULL TEST_BUG (cr=7 pr=0 pw=0 time=32 us starts=1 cost=3 size=155 card=1)

This is not to say that you should just put in a FULL hint and forget about it but its worth logging an SR to see if that's an existing bug or if patches are available.

If you want a quick workaround, then:

select /*+ opt_param('_b_tree_bitmap_plans','false') */ id from test_bug where tenant = :b1 and name= :b2 and id<> :b3;

should take care of it.