[
https://issues.apache.org/jira/browse/FLINK-38822?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18074698#comment-18074698
]
featzhang commented on FLINK-38822:
-----------------------------------
Thanks for the feedback, [~snuyanzin]!
I understand the concern about introducing functions that other vendors don't
provide.
Let me address this directly before presenting my updated proposal.
---
**Regarding the "no other vendor supports this" concern:**
While it's true that no mainstream SQL system currently offers recursive or
tolerant
URL decoding as a built-in function, I'd argue this is not the right criterion
in
this case, for two reasons:
1. **Flink already has precedent for error-tolerant variants that go beyond
what
other vendors offer.** For example, TRY_CAST provides fault-tolerant type
conversion semantics. It is not universally available across all SQL systems
either,
yet Flink intentionally provides it to make pipelines more robust for
real-world data.
2. **The problem of multi-level URL encoding is well-documented and
production-relevant.**
Double/multi-level URL encoding is a well-known real-world phenomenon,
recognized
by both OWASP (https://owasp.org/www-community/Double_Encoding) and MITRE
(https://capec.mitre.org/data/definitions/120.html). It commonly arises in
tracking pipelines, redirect chains, and ad-tech systems — all common data
sources
in streaming ingestion scenarios.
---
**Reconsidering the design:**
With that context in mind, I've also reconsidered the original proposal.
Rather than extending URL_DECODE with a recursive parameter, I now believe the
more
principled approach is to introduce a new function with explicit tolerant
semantics:
URL_DECODE_TOLERANT(str)
This function would:
- Iteratively apply URL decoding
- Stop when decoding encounters an invalid sequence (e.g., %2G) or the value
stabilizes
- Return the last successfully decoded result
For example:
URL_DECODE_TOLERANT('abc%2520def%2Gxyz')
-- Result: 'abc def%2Gxyz'
-- Explanation:
-- '%2520' is first decoded to '%25', then to '%'
-- '%2G' is invalid, so decoding stops and it is preserved as-is
This is semantically distinct from simply chaining multiple URL_DECODE calls,
because
the current URL_DECODE throws an exception on invalid sequences rather than
stopping
gracefully. This means the "just call it twice" workaround is not safe for
real-world
dirty data.
This addresses a real data robustness gap — and is consistent with Flink's
broader
philosophy of providing tolerant variants (like TRY_CAST) for functions that
can
encounter invalid input in production data.
I'm happy to refine the design or naming convention further. Would you be open
to
discussing this direction?
> Extend URL_DECODE with a recursive parameter for multi-level decoding
> ---------------------------------------------------------------------
>
> Key: FLINK-38822
> URL: https://issues.apache.org/jira/browse/FLINK-38822
> Project: Flink
> Issue Type: Improvement
> Components: Table SQL / API
> Affects Versions: 2.0-preview
> Reporter: featzhang
> Assignee: featzhang
> Priority: Major
> Labels: pull-request-available
> Original Estimate: 48h
> Remaining Estimate: 48h
>
> h3. Background
> Apache Flink SQL currently provides the built-in function
> {{{}URL_DECODE(str){}}}, which decodes a string in
> {{application/x-www-form-urlencoded}} format. This function was introduced in
> FLINK-34108 and is aligned with the corresponding functionality in Apache
> Calcite.
> However, in real-world data processing scenarios, especially in log
> processing, tracking data, and external system integrations, it is common to
> encounter {*}multi-level URL-encoded strings{*}, for example:
> * Values that are URL-encoded multiple times by upstream systems
> * Nested encoding caused by redirects, proxies, or intermediate
> transformations
> In such cases, calling {{URL_DECODE}} only once is insufficient.
> h3. Problem
> The current {{URL_DECODE(str)}} function only performs {*}a single decoding
> pass{*}. Users who need to fully decode multi-level encoded values must
> repeatedly apply the function manually, which:
> * Reduces readability of SQL queries
> * Makes the decoding intent less explicit
> * Is inconvenient and error-prone in complex SQL pipelines
> h3. Proposal
> Extend the existing {{URL_DECODE}} function with an {*}optional boolean
> parameter {{recursive}}{*}, which controls whether decoding should be applied
> repeatedly until the value can no longer be decoded.
> Proposed function signatures:
>
> {{URL_DECODE(str)
> URL_DECODE(str, recursive)}}
> Where:
> * {{recursive = false}} (default): preserves the current behavior
> (single-pass decoding)
> * {{{}recursive = true{}}}: repeatedly applies URL decoding until the result
> no longer changes
> h3. Examples
>
> {{-- Single-pass decoding (current behavior)
> SELECT URL_DECODE('%252Fpath%252Fto%252Fresource');
> -- Result: '%2Fpath%2Fto%2Fresource'
> -- Recursive decoding
> SELECT URL_DECODE('%252Fpath%252Fto%252Fresource', true);
> -- Result: '/path/to/resource'}}
> h3. Compatibility
> * This change is *fully backward-compatible*
> * Existing queries using {{URL_DECODE(str)}} will continue to work without
> any behavior changes
> * The new parameter is optional and only extends functionality
> h3. Additional Notes
> * The recursive decoding should stop when the decoded result is identical to
> the previous value
> * This proposal builds directly on the existing implementation introduced in
> FLINK-34108
> * Similar behavior is commonly required in data cleansing and normalization
> use cases
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
