You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@iceberg.apache.org by "cccs-jc (via GitHub)" <gi...@apache.org> on 2026/01/08 13:20:23 UTC
[I] Data File Pruning Fails for LIKE Operator with Escaped Underscores in Apache Iceberg [iceberg]
cccs-jc opened a new issue, #14995:
URL: https://github.com/apache/iceberg/issues/14995
### Apache Iceberg version
1.9.1
### Query engine
Spark
### Please describe the bug 🐞
## Description
There appears to be an issue in Apache Iceberg's data file pruning when using the SQL `LIKE` operator containing escaped underscore (`_`) characters. Unlike when using the Spark `startswith` function or simpler `LIKE` patterns without underscores, the presence of escaped special characters in the `LIKE` expression leads to improper query optimizations. As a result, all data files are scanned instead of pruning irrelevant files.
The appropriate file pruning occurs for queries using the Spark `startswith` function or `LIKE` expressions without escaped underscores but fails when underscores are escaped manually (with `\_`) or using an explicit escape character.
---
## Steps to Reproduce
1. Create an Iceberg table (e.g., `prod_catalog.admin.inventory`) with a `TRUNCATE(128)` transformation on the `file_path` column:
```sql
ALTER TABLE prod_catalog.admin.inventory SET TBLPROPERTIES ('write.metadata.metrics.column.file_path'='truncate(128)');
```
2. Run the following query using the Spark `startswith` operator:
```sql
SELECT count(*)
FROM prod_catalog.admin.inventory
WHERE
loaded_at = DATE('2026-01-05') AND
startswith(file_path, 'warehouse/iceberg/good_facts/fact1_table/');
```
- **Execution Plan Observation:** File pruning is successful. Only 11 data files are scanned (`number of result data files: 11`).
3. Replace `startswith` with the SQL `LIKE` operator:
```sql
SELECT count(*)
FROM prod_catalog.admin.inventory
WHERE
loaded_at = DATE('2026-01-05') AND
file_path LIKE 'warehouse/iceberg/good_facts/fact1_table/%';
```
- **Execution Plan Observation:** File pruning fails, and all data files are scanned.
4. Escape special characters in the `LIKE` expression:
- Using a backslash:
```sql
SELECT count(*)
FROM prod_catalog.admin.inventory
WHERE
loaded_at = DATE('2026-01-05') AND
file_path LIKE 'warehouse/iceberg/good\_facts/fact1\_table/%';
```
- Using an explicit escape character (`ESCAPE 'X'`):
```sql
SELECT count(*)
FROM prod_catalog.admin.inventory
WHERE
loaded_at = DATE('2026-01-05') AND
file_path LIKE 'warehouse/iceberg/goodX_facts/fact1X_table/%' ESCAPE 'X';
```
- **Execution Plan Observation:** In both cases, file pruning still fails, and all data files are scanned.
5. Finally, remove the underscore to see what happens:
```sql
SELECT count(*)
FROM prod_catalog.admin.inventory
WHERE
loaded_at = DATE('2026-01-05') AND
file_path LIKE 'warehouse/iceberg/goodfacts/fact1table/%';
```
- **Execution Plan Observation:** File pruning works correctly. The `LIKE` operator is converted into `StartsWith` during the Filter phase, and only relevant data files are scanned.
---
## Expected Behavior
- The presence of escaped underscores in the `LIKE` operator (via `\_` or `ESCAPE`) should not prevent Iceberg from optimizing data file pruning. Iceberg should recognize the `LIKE` pattern and convert it to an equivalent `StartsWith` expression for efficient pruning.
---
## Observed Behavior
- Iceberg does not optimize file pruning when the `LIKE` operator includes escaped special characters like underscores (`_`), leading to all files being scanned. This behavior is inconsistent with both `startswith` and simpler `LIKE` patterns that do not contain escaped characters.
---
## Additional Information
- When the pattern contains no underscores, Iceberg correctly converts the `LIKE` expression to a `StartsWith` during the Filter phase.
- Example of an unoptimized filter condition:
```
(3) Filter [codegen id : 1]
Input [2]: [loaded_at#811, file_path#817]
Condition : ((isnotnull(file_path#817) AND (loaded_at#811 = 2026-01-05)) AND file_path#817 LIKE warehouse/iceberg/goodX_facts/fact1X_table/ ESCAPE 'X')
```
- Example of an optimized filter condition when underscores are removed:
```
(3) Filter [codegen id : 1]
Input [2]: [loaded_at#844, file_path#850]
Condition : ((isnotnull(file_path#850) AND (loaded_at#844 = 2026-01-05)) AND StartsWith(file_path#850, warehouse/iceberg/goodfacts/fact1table/))
```
### Willingness to contribute
- [ ] I can contribute a fix for this bug independently
- [ ] I would be willing to contribute a fix for this bug with guidance from the Iceberg community
- [x] I cannot contribute a fix for this bug at this time
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org
Re: [I] Data File Pruning Fails for LIKE Operator with Escaped Underscores in Apache Iceberg [iceberg]
Posted by "alexandrefimov (via GitHub)" <gi...@apache.org>.
alexandrefimov commented on issue #14995:
URL: https://github.com/apache/iceberg/issues/14995#issuecomment-4604032175
I dug a bit more into the Spark side of this.
This appears to be upstream Spark behavior rather than an Iceberg pruning bug. Iceberg can prune on once Spark passes a / predicate through the scan builder, but in this case Spark keeps the escaped expression above the . That means Iceberg only sees the pushed predicates shown in the node, and there is no / predicate available for Iceberg file pruning.
There is relevant Spark history here:
- SPARK-33677 / apache/spark#30625 intentionally changed to skip patterns containing an escape character.
- SPARK-38168 / apache/spark#35465 later attempted to handle escape characters in , but that PR was closed and the JIRA was resolved as Won't Fix.
The maintainer feedback on apache/spark#35465 was that handling escaped patterns in was a code-simplicity vs performance trade-off, escape-character patterns were considered rare, and users could call / directly instead.
Given that, I don't think there is an Iceberg-side fix unless Spark pushes an equivalent predicate to Iceberg. The practical workaround is to use directly. If this behavior should change, it likely needs a new Spark-side proposal that specifically calls out the DataSource V2 predicate pushdown / Iceberg file-pruning impact.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org
Re: [I] Data File Pruning Fails for LIKE Operator with Escaped Underscores in Apache Iceberg [iceberg]
Posted by "joyhaldar (via GitHub)" <gi...@apache.org>.
joyhaldar commented on issue #14995:
URL: https://github.com/apache/iceberg/issues/14995#issuecomment-3724505484
I think the root cause is in Spark's optimizer, not Iceberg.
From what I understood, Spark's `LikeSimplification` rule normally converts `LIKE 'prefix%'` to `StartsWith("prefix")`. However, when the pattern contains any escape character (like `\_`), Spark [skips this optimization entirely](https://github.com/apache/spark/blob/master/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/expressions.scala#L766-L772):
```
if (pattern.contains(escapeChar)) {
// Although there are patterns can be optimized if we handle the escape first, we just
// skip this rule if pattern contains any escapeChar for simplicity.
None
}
```
Since Spark doesn't convert the `LIKE` to `StartsWith`, it never gets pushed down to Iceberg for file pruning. If this is correct, Iceberg's code is fine, it just never receives the filter.
Could you try running the same query with **Trino** or another query engine against your Iceberg table? If file pruning works there, it could confirm this may be a Spark specific limitation.
In the meantime, using `startswith()` directly should work:
```
WHERE startswith(file_path, 'warehouse/iceberg/good_facts/')
```
Also, please feel free to ignore this entirely, just sharing my observations.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org
Re: [I] Data File Pruning Fails for LIKE Operator with Escaped Underscores in Apache Iceberg [iceberg]
Posted by "cccs-jc (via GitHub)" <gi...@apache.org>.
cccs-jc commented on issue #14995:
URL: https://github.com/apache/iceberg/issues/14995#issuecomment-3728667801
I can confirm that in the execution plan with underscores (escaped or not) the BatchScan does not have a LIKE predicate. Only the `IS NOT NULL`.
So looks like as you said it's a Spark issue. Thanks for the quick response.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org
Re: [I] Data File Pruning Fails for LIKE Operator with Escaped Underscores in Apache Iceberg [iceberg]
Posted by "RussellSpitzer (via GitHub)" <gi...@apache.org>.
RussellSpitzer commented on issue #14995:
URL: https://github.com/apache/iceberg/issues/14995#issuecomment-3724725355
@joyhaldar is correct here if you check the plan you'll see exactly what he is mentioning
> +- *(1) Filter (isnotnull(dep) AND **file_path LIKE warehouse/iceberg/goodX_facts/fact1X_table/% ESCAPE 'X'**)
> +- *(1) ColumnarToRow
> +- BatchScan testhadoop.default.table[id, salary, dep] testhadoop.default.table (branch=null) [filters=file_path IS NOT NULL, groupedBy=] RuntimeFilters: []
Which means in SparkScanBuilder, the only predicate we are given in ``` public Predicate[] pushPredicates(Predicate[] predicates) {```
Is `dep is NOT NULL`
The key thing to notice here, is if you don't see a predicate in the "BatchScan" entry, it means the Iceberg Library doesn't see it
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org