SEPURI-SAI-KRISHNA opened a new issue, #12995: URL: https://github.com/apache/seatunnel/issues/12995
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/seatunnel/issues?q=is%3Aissue+label%3A%22Bug%22) and found no similar issues. ### What happened The JDBC source has two guards that stop it auto-adding `where_condition` columns to SQL where doing so would change the query's meaning. Both guards uppercase the user's SQL with the JVM default locale and then match keyword literals, so on a Turkish or Azerbaijani locale worker every guard keyword containing the letter `i` stops matching and the guard is bypassed. The result is that SeaTunnel rewrites the user's query in exactly the cases its own comments say it must not. For a `UNION`, it adds a column to only the first branch, which changes that branch's arity and the database rejects the query. For `DISTINCT`, it adds a column to the distinct list, which silently changes how many rows come back. ### SeaTunnel Version dev (`7b686d838`) ### SeaTunnel Config ```conf source { Jdbc { url = "jdbc:mysql://localhost:3306/test" driver = "com.mysql.cj.jdbc.Driver" user = "root" password = "root" query = "select id from a union select id from b" where_condition = "where name = 'x'" } } ``` Nothing locale-specific is configured. The worker only has to be started with a Turkish default locale, for example `-Duser.language=tr -Duser.country=TR`. ### Measured behaviour Driven through the real public method `SqlWhereConditionHelper.applyWhereConditionWithWrap(sql, "name = 'x'", true)`, which is what `ChunkSplitter` calls: | Input query | Locale | Emitted SQL | | --- | --- | --- | | `select id from a union select id from b` | en-US | `SELECT * FROM (select id from a union select id from b) tmp WHERE name = 'x'` | | `select id from a union select id from b` | **tr-TR** | `SELECT * FROM (select id , name from a union select id from b) tmp WHERE name = 'x'` | | `select distinct id from t` | en-US | `SELECT * FROM (select distinct id from t) tmp WHERE name = 'x'` | | `select distinct id from t` | **tr-TR** | `SELECT * FROM (select distinct id , name from t) tmp WHERE name = 'x'` | | `select id from a except select id from b` | both | identical (control, `EXCEPT` has no `i`) | The `UNION` row is the clearest damage: `name` is added to the first branch only, so the two branches no longer have the same number of columns and the database rejects the statement. The `DISTINCT` row is the more dangerous one, because it does not fail. `select distinct id , name from t` is distinct over two columns instead of one, so the job silently returns a different number of rows. At the guard level, measured on the real static methods: ```text hasTopLevelSetOperator en-US tr-TR UNION true false *** BYPASSED *** UNION ALL true false *** BYPASSED *** INTERSECT true false *** BYPASSED *** MINUS true false *** BYPASSED *** EXCEPT true true same hasComplexClause en-US tr-TR DISTINCT true false *** BYPASSED *** HAVING true false *** BYPASSED *** LIMIT true false *** BYPASSED *** WITH (CTE) true false *** BYPASSED *** GROUP BY true true same OFFSET true true same OVER true true same ``` Seven distinct keywords are affected, and the rule is simply whether the keyword contains an `i`: `toUpperCase()` maps `i` to `İ` (U+0130) under these locales, so `union` becomes `UNİON` and never matches `UNION`. `EXCEPT`, `GROUP BY`, `OFFSET` and `OVER` contain no `i` and are unaffected, which is why the bug is partial rather than total. ### Root cause `connector-jdbc/.../source/SqlWhereConditionHelper.java` has seven bare `toUpperCase()` calls, all of them matching SQL keywords: ```java public static boolean hasTopLevelSetOperator(String sql) { String upper = sql.toUpperCase(); // :174 ... public static boolean hasComplexClause(String sql) { String upper = sql.toUpperCase(); // :338 ... if (columnList.toUpperCase().startsWith("DISTINCT ")) { // :278 } else if (columnList.toUpperCase().startsWith("ALL ")) { // :280 ``` Four of the seven have a measured consequence: `:174` and `:338` are the two guards, and `:278`/`:280` fail to strip a leading `DISTINCT`/`ALL` from the column list, which is the second half of the `DISTINCT` damage above. The other three (`:240`, `:505`, `:566`) only match keywords with no `i` (`SELECT`, `FROM`, `AND`, `OR`, `NOT`, `NULL`, `TRUE`, `FALSE`) so they have no consequence today, but they are the same defect in the same class. The guards' own comments state the contract they fail to keep: > For UNION/INTERSECT/EXCEPT queries, skip auto-adding columns because it's unsafe to modify only the first branch. > For complex SQL (GROUP BY, HAVING, DISTINCT, LIMIT, window functions, CTE), skip auto-adding columns to avoid breaking query semantics. This is the same class of defect as #12495 and #12618, which made the Zeta SQL transform's keyword lookups locale independent, and the project has already settled on `Locale.ROOT` as the fix. ### What you expected to happen The guards match SQL keywords, not user-facing text, so they should be independent of the JVM default locale: a `UNION` or `DISTINCT` query must be detected and left unrewritten on every locale. ### Are you willing to submit a 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]
