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]

Reply via email to