Optimizer should simplify lower(col) = lower('literal') to enable predicate pushdown

[ethanyzhang]

When query predicates wrap columns in lower() — for example lower("col") = lower('some_value') — predicate pushdown to connectors is blocked because the optimizer treats the expression as a function call rather than a simple comparison.

This is common in application-generated SQL where case-insensitive matching is needed. The pattern typically looks like:

SELECT ...
FROM table
WHERE lower("AccountIdentifier") = lower('035778000361')
   OR lower("AccountIdentifier") = lower('135006324083')
   ...

Similarly, position('substring' in lower("col")) > 0 prevents pushdown.

Proposed improvement

The optimizer could recognize and simplify these patterns:

1. lower(col) = lower('literal') — Since lower('literal') is a constant, fold it at plan time: lower(col) = 'already_lowercase_literal'. This at minimum eliminates redundant lower() calls on constants, and for connectors that support case-insensitive comparison or LOWER, enables pushdown.

2. lower(col) = 'literal' (already lowercase) — Some connectors (e.g., JDBC) could push this down as LOWER(col) = 'literal' in the generated SQL, letting the underlying database handle it with an index on a computed column or a case-insensitive collation.

3. position('literal' in lower(col)) > 0 — Could be rewritten as a case-insensitive LIKE '%literal%' pattern for connectors that support it.

Impact

Without pushdown, the connector must scan and return all rows, and Presto evaluates the filter in-engine. For large tables this significantly increases I/O and processing time.

[prestodb-ci]

@ethanyzhang imported this issue as lakehouse/tracker #27436

[bertholdreinwald]

There are 2 parts to those predicates:

1. lower('BlaBla'): most compilers apply constant folding, and replace it with: 'blabla'
2. lower("AccountIdentifier"): this prevents skipping at runtime. To overcome that, SQL standard has derived/generated columns. I.e. alter table apptio_test_table_5_append_test alter column accountidentifier_l set generated always as (lower(accountidentifier)).

Both problems are solved in Opt+

[mbasmanova]

@ethanyzhang @bertholdreinwald Doesn't Presto already apply constant folding? What is Opt+?

[ethanyzhang]

@mbasmanova I will defer to Tim for details but my understanding is that the current constant folding does not handle lower() and it should be relatively easy to add?

OPT+ is an IBM internal project to leverage Db2 optimizer to do query rewriting. I think it's good to for OSS Presto to have a way to handle this.

[aditi-pandit]

@mbasmanova : This isn't an issue with constant folding or predicate pushdown. The expectation is that the lower function based predicates apply for row group filtering in Parquet reader. DB2 Opt+ has a solution with derived columns for this. But I'm trying to understand how that wires into any Presto query engine abstractions.

[tdcmeehan]

In theory, one way of going about this is to leverage additional statistics provided by the table format. For example, if Iceberg were to allow additional column statistics on a transform specified on a sort order, then such a predicate could be pushed down into the Iceberg scan planning. Currently this is not supported, however I believe Delta supports this.

In lieu of these statistics, then we'd need some sort of computed column, as sophisticated optimizers such as Db2 allow. Such a feature would be nice, but my sense is for open source Presto it's better to leverage the table format, as it's unclear how computed columns would be supported without explicit table format support.

Oh, and if the column is a partition column, then this should fine now, since we can enumerate those values in the coordinator and skip them during Iceberg scan planning.

[ScrapCodes]

Example 1:

presto:test> explain select lower('AS');
                                                     Query Plan                                                     
--------------------------------------------------------------------------------------------------------------------
 - Output[PlanNodeId 5][_col0] => [lower:varchar(2)]                                                                
         Estimates: {source: CostBasedSourceInfo, rows: 1 (7B), cpu: 7.00, memory: 0.00, network: 0.00}             
         _col0 := lower (1:16)                                                                                      
     - Project[PlanNodeId 1][projectLocality = LOCAL] => [lower:varchar(2)]                                         
             Estimates: {source: CostBasedSourceInfo, rows: 1 (7B), cpu: 7.00, memory: 0.00, network: 0.00}         
             lower := VARCHAR'as'                                                                                   
         - LocalExchange[PlanNodeId 192][ROUND_ROBIN] () => []                                                      
                 Estimates: {source: CostBasedSourceInfo, rows: 1 (0B), cpu: 0.00, memory: 0.00, network: 0.00}     
             - Values[PlanNodeId 0] => []                                                                           
                     Estimates: {source: CostBasedSourceInfo, rows: 1 (0B), cpu: 0.00, memory: 0.00, network: 0.00} 
                     ()                                                                                             
                                                                                                                    
(1 row)

Query 20260326_110140_00000_bmix8, FINISHED, 1 node
http://127.0.0.1:8080/ui/query.html?20260326_110140_00000_bmix8
Splits: 1 total, 1 done (100.00%)
[Latency: client-side: 355ms, server-side: 322ms] [0 rows, 0B] [0 rows/s, 0B/s]

Example 2:

presto:test> explain select *from taxis where lower(store_and_fwd_flag) != lower('N');
                                                                                                                                                                                 Query Plan                                                                                                                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 - Output[PlanNodeId 6][vendor_id, trip_id, trip_distance, fare_amount, store_and_fwd_flag] => [vendor_id:bigint, trip_id:bigint, trip_distance:decimal(5,2), fare_amount:double, store_and_fwd_flag:varchar]                                                                                                                                                               
         Estimates: {source: CostBasedSourceInfo, rows: ? (?), cpu: 500.00, memory: 0.00, network: ?}                                                                                                                                                                                                                                                                       
     - RemoteStreamingExchange[PlanNodeId 207][GATHER - COLUMNAR] => [vendor_id:bigint, trip_id:bigint, trip_distance:decimal(5,2), fare_amount:double, store_and_fwd_flag:varchar]                                                                                                                                                                                         
             Estimates: {source: CostBasedSourceInfo, rows: ? (?), cpu: 500.00, memory: 0.00, network: ?}                                                                                                                                                                                                                                                                   
         - ScanFilter[PlanNodeId 0,236][table = TableHandle {connectorId='iceberg2', connectorHandle='taxis$data@2530820414725702192', layout='Optional[taxis$data@2530820414725702192]'}, filterPredicate = (lower(store_and_fwd_flag)) <> (VARCHAR'n')] => [vendor_id:bigint, trip_id:bigint, trip_distance:decimal(5,2), fare_amount:double, store_and_fwd_flag:varchar] 
                 Estimates: {source: CostBasedSourceInfo, rows: 6 (250B), cpu: 250.00, memory: 0.00, network: 0.00}/{source: CostBasedSourceInfo, rows: ? (?), cpu: 500.00, memory: 0.00, network: 0.00}                                                                                                                                                                    
                 vendor_id := 1:vendor_id:bigint (1:22)                                                                                                                                                                                                                                                                                                                     
                 trip_id := 2:trip_id:bigint (1:22)                                                                                                                                                                                                                                                                                                                         
                 fare_amount := 4:fare_amount:double (1:22)                                                                                                                                                                                                                                                                                                                 
                 trip_distance := 3:trip_distance:decimal(5,2) (1:22)                                                                                                                                                                                                                                                                                                       
                 store_and_fwd_flag := 5:store_and_fwd_flag:varchar (1:22)                                                                                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                                                                                                                                                            
(1 row)

Query 20260326_110542_00004_bmix8, FINISHED, 1 node
http://127.0.0.1:8080/ui/query.html?20260326_110542_00004_bmix8
Splits: 1 total, 1 done (100.00%)
[Latency: client-side: 126ms, server-side: 115ms] [0 rows, 0B] [0 rows/s, 0B/s]

1. lower := VARCHAR'as'

2. filterPredicate = (lower(store_and_fwd_flag)) <> (VARCHAR'n')] already part of table scan node.

Looks like constant folding is already present, along with push down.

[ScrapCodes]

I could be missing something, this issue appear to focus on a problem when reading from a slow datasource e.g. JDBC, where we do not want to bring all the rows to presto engine for filtering and incur the high IO cost.

1. Connector configuration update, whether it supports a push down of scalar function - how does the underlying connector tell what all scalar function it supports.
2. Perform the actual push down to the underlying connector.