Column-level collation gives different results depending on whether pushdown is applied or not in oracle connector

[krvikash]

When case-insensitivity search is enable for a column (https://oracle-base.com/articles/12c/column-level-collation-and-case-insensitive-database-12cr2#column-level) then with pushdown and without pushdown gives different result in oracle connector.

Here is an example:

-- Table created using Oracle
create table table_1 (id int, c_varchar_50 varchar(50) COLLATE BINARY_CI);
Insert into table_1 values(1, 'INDIA');
Insert into table_1 values(2, 'India');

When Pushdown happens then trino return results by comparing as case-insensitivity.

trino:admin> select * from table_1 where CAST(c_varchar_50 AS VARCHAR(50)) = 'INDIA';
 id | c_varchar_50
----+--------------
  1 | INDIA
  2 | India
(2 rows)

trino:admin> explain select * from table_1 where CAST(c_varchar_50 AS VARCHAR(50)) = 'INDIA';
                                        Query Plan
-------------------------------------------------------------------------------------------
 Trino version: dev
 Fragment 0 [SOURCE]
     Output layout: [id, c_varchar]
     Output partitioning: SINGLE []
     Output[columnNames = [id, c_varchar_50]]
     │   Layout: [id:decimal(38,0), c_varchar:varchar(50)]
     │   Estimates: {rows: ? (?), cpu: 0, memory: 0B, network: 0B}
     │   c_varchar_50 := c_varchar
     └─ TableScan[table = oracle:admin.table_1 ADMIN.TABLE_1 constraint on [C_VARCHAR_50]]
            Layout: [id:decimal(38,0), c_varchar:varchar(50)]
            Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: 0B}
            c_varchar := C_VARCHAR_50:varchar(50):VARCHAR2
            id := ID:decimal(38,0):NUMBER

Whereas, When Pushdown does not happen then trino return results by comparing as case-sensitivity.

trino:admin> select * from table_1 where CAST(c_varchar_50 AS VARCHAR(20)) = 'INDIA';
 id | c_varchar_50
----+--------------
  1 | INDIA
(1 row)

trino:admin> explain select * from table_1 where CAST(c_varchar_50 AS VARCHAR(20)) = 'INDIA';
                                                               Query Plan
-----------------------------------------------------------------------------------------------------------------------------------------
 Trino version: dev
 Fragment 0 [SOURCE]
     Output layout: [id, c_varchar]
     Output partitioning: SINGLE []
     Output[columnNames = [id, c_varchar_50]]
     │   Layout: [id:decimal(38,0), c_varchar:varchar(50)]
     │   Estimates: {rows: ? (?), cpu: 0, memory: 0B, network: 0B}
     │   c_varchar_50 := c_varchar
     └─ ScanFilter[table = oracle:admin.table_1 ADMIN.TABLE_1, filterPredicate = (CAST(c_varchar AS varchar(20)) = varchar(20) 'INDIA')]
            Layout: [id:decimal(38,0), c_varchar:varchar(50)]
            Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: 0B}/{rows: ? (?), cpu: ?, memory: 0B, network: 0B}
            c_varchar := C_VARCHAR_50:varchar(50):VARCHAR2
            id := ID:decimal(38,0):NUMBER

[krvikash]

CC: @Praveen2112 @SemionPar @mayankvadariya