ryanmeowy opened a new issue, #13144:
URL: https://github.com/apache/seatunnel/issues/13144
### Search before asking
- [x] I had searched in the issues and found no similar issues.
### What happened
In the default SQL transform engine (ZETA), a `WHERE` predicate whose LIKE
pattern has a `%` or
`_` anywhere except the very beginning or the very end silently matches
nothing. There is no
error and no warning — rows just disappear from the output. `NOT LIKE` with
the same pattern has
the opposite problem: it keeps every row.
```sql
select id, name from fake where name like 'A%ce' -- returns 0 rows even
though 'Alice' exists
select id, name from fake where name like 'Al%' -- works
select id, name from fake where name like '%lic%' -- works
select id, name from fake where name not like 'A%ce' -- keeps every row
```
Because the failure is silent, a job looks healthy while producing an
incomplete result set.
Two related defects live on the same lines:
- Every other regex metacharacter in the pattern is interpreted as regex
syntax instead of a
literal, so `like '1.5'` matches `125`.
- `col LIKE NULL` throws a `NullPointerException` instead of filtering the
row out, and a pattern
containing an unclosed group (e.g. `'a(b%'`) throws
`PatternSyntaxException`, which fails the
whole transform.
### SeaTunnel Version
dev (`a3743d7f52e9200a7733857ca813a0836bf66bba`)
### SeaTunnel Config
```conf
env {
job.mode = "BATCH"
}
source {
FakeSource {
plugin_input = "fake"
row.num = 2
# FakeSource generates random alphabetic strings by default, so pin the
values
# to make the reproduction deterministic
string.template = ["Alice", "Bob"]
schema {
fields {
id = int
name = string
}
}
}
}
transform {
Sql {
plugin_input = "fake"
plugin_output = "fake1"
query = "select id, name from fake where name like 'A%ce'"
}
}
sink {
Console {
plugin_input = "fake1"
}
}
```
Swap the query for `where name like 'Al%'` to see the working shape: that
one keeps `Alice`.
With `where name not like 'A%ce'` both rows are kept, which is the inverse
symptom.
### Running Command
```shell
$SEATUNNEL_HOME/bin/seatunnel.sh --config ./like-bug.conf -m local
```
### Error Exception
Case 1 — silent wrong result (no exception at all; expected one row, actual
zero):
```
+----------+
| id | name|
+----------+
(no rows)
```
Case 2 — `where name like 'a(b%'` throws (error code `TRANSFORM_COMMON-07`):
```
org.apache.seatunnel.transform.exception.TransformException:
ErrorCode:[TRANSFORM_COMMON-07],
ErrorDescription:[The where statement 'name LIKE 'a(b%'' of SQL transform
execute failed]
Caused by: java.util.regex.PatternSyntaxException: Unclosed group near index
5
a(b.*
at java.util.regex.Pattern.error(Pattern.java:2028)
at java.util.regex.Pattern.group0(Pattern.java:3023)
at java.util.regex.Pattern.sequence(Pattern.java:2077)
... (java.util.regex frames elided, they differ between JDK versions)
at
org.apache.seatunnel.transform.sql.zeta.ZetaSQLFilter.likeExpr(ZetaSQLFilter.java:222)
at
org.apache.seatunnel.transform.sql.zeta.ZetaSQLFilter.executeFilter(ZetaSQLFilter.java:87)
at
org.apache.seatunnel.transform.sql.zeta.ZetaSQLEngine.transformBySQL(ZetaSQLEngine.java:338)
```
Case 3 — `where name like null` throws:
```
java.lang.NullPointerException: Cannot invoke "Object.toString()" because
"rightVal" is null
at
org.apache.seatunnel.transform.sql.zeta.ZetaSQLFilter.likeExpr(ZetaSQLFilter.java:199)
at
org.apache.seatunnel.transform.sql.zeta.ZetaSQLFilter.executeFilter(ZetaSQLFilter.java:87)
at
org.apache.seatunnel.transform.sql.zeta.ZetaSQLEngine.transformBySQL(ZetaSQLEngine.java:338)
```
### Zeta or Flink or Spark Version
Zeta (ZETA is the default SQL transform engine; the code is engine-side, not
connector-side)
### Java or Scala Version
JDK 17 (`17.0.14`) and JDK 21 (`21.0.12.1`)
### Are you willing to submit PR?
- [x] Yes I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of
Conduct](https://www.apache.org/foundation/policies/conduct)
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]