[jira] [Commented] (FLINK-24948) Special character in column names breaks JDBC statement parsing
[ https://issues.apache.org/jira/browse/FLINK-24948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17459696#comment-17459696 ] Martijn Visser commented on FLINK-24948: CC [~afedulov] [~arvid] > Special character in column names breaks JDBC statement parsing > --- > > Key: FLINK-24948 > URL: https://issues.apache.org/jira/browse/FLINK-24948 > Project: Flink > Issue Type: Bug > Components: Connectors / JDBC >Affects Versions: 1.12.4 >Reporter: Paul Lin >Assignee: Paul Lin >Priority: Major > Labels: pull-request-available > > Currently, JDBC connector assumes columns names respect Java identifier > naming restrictions, but Databases that support JDBC may have different > naming restrictions. For example, MySQL allows dots and colons in column > names. In that case, JDBC connector would have trouble parsing the SQL. > We could fix this by validating field names in `JdbcDmlOptions`. In addition, > it'd be good to clarify the naming restrictions of Flink SQL, so users and > connector developers would know the standard. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (FLINK-24948) Special character in column names breaks JDBC statement parsing
[ https://issues.apache.org/jira/browse/FLINK-24948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17459681#comment-17459681 ] Paul Lin commented on FLINK-24948: -- [~jark] I've prepared a PR, PTAL. Thanks! > Special character in column names breaks JDBC statement parsing > --- > > Key: FLINK-24948 > URL: https://issues.apache.org/jira/browse/FLINK-24948 > Project: Flink > Issue Type: Bug > Components: Connectors / JDBC >Affects Versions: 1.12.4 >Reporter: Paul Lin >Assignee: Paul Lin >Priority: Major > Labels: pull-request-available > > Currently, JDBC connector assumes columns names respect Java identifier > naming restrictions, but Databases that support JDBC may have different > naming restrictions. For example, MySQL allows dots and colons in column > names. In that case, JDBC connector would have trouble parsing the SQL. > We could fix this by validating field names in `JdbcDmlOptions`. In addition, > it'd be good to clarify the naming restrictions of Flink SQL, so users and > connector developers would know the standard. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (FLINK-24948) Special character in column names breaks JDBC statement parsing
[ https://issues.apache.org/jira/browse/FLINK-24948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17447848#comment-17447848 ] Jark Wu commented on FLINK-24948: - [~Paul Lin] I think we need to fix the bug in the FieldNamedPreparedStatementImpl. > Special character in column names breaks JDBC statement parsing > --- > > Key: FLINK-24948 > URL: https://issues.apache.org/jira/browse/FLINK-24948 > Project: Flink > Issue Type: Bug > Components: Connectors / JDBC >Affects Versions: 1.12.4 >Reporter: Paul Lin >Assignee: Paul Lin >Priority: Major > > Currently, JDBC connector assumes columns names respect Java identifier > naming restrictions, but Databases that support JDBC may have different > naming restrictions. For example, MySQL allows dots and colons in column > names. In that case, JDBC connector would have trouble parsing the SQL. > We could fix this by validating field names in `JdbcDmlOptions`. In addition, > it'd be good to clarify the naming restrictions of Flink SQL, so users and > connector developers would know the standard. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (FLINK-24948) Special character in column names breaks JDBC statement parsing
[ https://issues.apache.org/jira/browse/FLINK-24948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17447789#comment-17447789 ] Paul Lin commented on FLINK-24948: -- Yes, I'm working on it. May I ask your opinion on the solution? Should we forbid column names with special characters or just fix the parsing of named parameters? > Special character in column names breaks JDBC statement parsing > --- > > Key: FLINK-24948 > URL: https://issues.apache.org/jira/browse/FLINK-24948 > Project: Flink > Issue Type: Bug > Components: Connectors / JDBC >Affects Versions: 1.12.4 >Reporter: Paul Lin >Assignee: Paul Lin >Priority: Major > > Currently, JDBC connector assumes columns names respect Java identifier > naming restrictions, but Databases that support JDBC may have different > naming restrictions. For example, MySQL allows dots and colons in column > names. In that case, JDBC connector would have trouble parsing the SQL. > We could fix this by validating field names in `JdbcDmlOptions`. In addition, > it'd be good to clarify the naming restrictions of Flink SQL, so users and > connector developers would know the standard. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (FLINK-24948) Special character in column names breaks JDBC statement parsing
[ https://issues.apache.org/jira/browse/FLINK-24948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17447761#comment-17447761 ] Jark Wu commented on FLINK-24948: - Thanks [~Paul Lin], do you want to provide a fix for this? > Special character in column names breaks JDBC statement parsing > --- > > Key: FLINK-24948 > URL: https://issues.apache.org/jira/browse/FLINK-24948 > Project: Flink > Issue Type: Bug > Components: Connectors / JDBC >Affects Versions: 1.12.4 >Reporter: Paul Lin >Assignee: Paul Lin >Priority: Major > > Currently, JDBC connector assumes columns names respect Java identifier > naming restrictions, but Databases that support JDBC may have different > naming restrictions. For example, MySQL allows dots and colons in column > names. In that case, JDBC connector would have trouble parsing the SQL. > We could fix this by validating field names in `JdbcDmlOptions`. In addition, > it'd be good to clarify the naming restrictions of Flink SQL, so users and > connector developers would know the standard. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (FLINK-24948) Special character in column names breaks JDBC statement parsing
[ https://issues.apache.org/jira/browse/FLINK-24948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17447746#comment-17447746 ] Paul Lin commented on FLINK-24948: -- [~jark] Sure. For example, a table name tbl_a with schema `(.a varchar(64))` would result in a statement like `insert into tbl_a (`.a`) values (:.a)` (in which the columns names are quoted, for you 2nd question). The statement would be further processed by `FieldNamedPreparedStatementImpl`, in which the named parameter would be extracted by `:` (the delimiter) and characters that are no part of Java identifiers, see [code|https://github.com/apache/flink/blob/e431e6b686e904b6b6f6dabfed8796c67898461b/flink-connectors/flink-connector-jdbc/src/main/java/org/apache/flink/connector/jdbc/statement/FieldNamedPreparedStatementImpl.java#L223]. I think the problem lies in the parsing of named parameters, as it simply extracts the names via some reserved characters, but these characters can also be part of a valid column name. > Special character in column names breaks JDBC statement parsing > --- > > Key: FLINK-24948 > URL: https://issues.apache.org/jira/browse/FLINK-24948 > Project: Flink > Issue Type: Bug > Components: Connectors / JDBC >Affects Versions: 1.12.4 >Reporter: Paul Lin >Assignee: Paul Lin >Priority: Major > > Currently, JDBC connector assumes columns names respect Java identifier > naming restrictions, but Databases that support JDBC may have different > naming restrictions. For example, MySQL allows dots and colons in column > names. In that case, JDBC connector would have trouble parsing the SQL. > We could fix this by validating field names in `JdbcDmlOptions`. In addition, > it'd be good to clarify the naming restrictions of Flink SQL, so users and > connector developers would know the standard. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (FLINK-24948) Special character in column names breaks JDBC statement parsing
[ https://issues.apache.org/jira/browse/FLINK-24948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17447728#comment-17447728 ] Jark Wu commented on FLINK-24948: - [~Paul Lin], could you give an example for the failed case? Is it because the Jdbc dialect didn't quotes the identifiers? > Special character in column names breaks JDBC statement parsing > --- > > Key: FLINK-24948 > URL: https://issues.apache.org/jira/browse/FLINK-24948 > Project: Flink > Issue Type: Bug > Components: Connectors / JDBC >Affects Versions: 1.12.4 >Reporter: Paul Lin >Assignee: Paul Lin >Priority: Major > > Currently, JDBC connector assumes columns names respect Java identifier > naming restrictions, but Databases that support JDBC may have different > naming restrictions. For example, MySQL allows dots and colons in column > names. In that case, JDBC connector would have trouble parsing the SQL. > We could fix this by validating field names in `JdbcDmlOptions`. In addition, > it'd be good to clarify the naming restrictions of Flink SQL, so users and > connector developers would know the standard. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (FLINK-24948) Special character in column names breaks JDBC statement parsing
[ https://issues.apache.org/jira/browse/FLINK-24948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17447256#comment-17447256 ] Martijn Visser commented on FLINK-24948: [~Paul Lin] I've assigned it to you > Special character in column names breaks JDBC statement parsing > --- > > Key: FLINK-24948 > URL: https://issues.apache.org/jira/browse/FLINK-24948 > Project: Flink > Issue Type: Bug > Components: Connectors / JDBC >Affects Versions: 1.12.4 >Reporter: Paul Lin >Assignee: Paul Lin >Priority: Major > > Currently, JDBC connector assumes columns names respect Java identifier > naming restrictions, but Databases that support JDBC may have different > naming restrictions. For example, MySQL allows dots and colons in column > names. In that case, JDBC connector would have trouble parsing the SQL. > We could fix this by validating field names in `JdbcDmlOptions`. In addition, > it'd be good to clarify the naming restrictions of Flink SQL, so users and > connector developers would know the standard. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (FLINK-24948) Special character in column names breaks JDBC statement parsing
[ https://issues.apache.org/jira/browse/FLINK-24948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17445685#comment-17445685 ] Paul Lin commented on FLINK-24948: -- Working on the fix. Please assign this issue to me. > Special character in column names breaks JDBC statement parsing > --- > > Key: FLINK-24948 > URL: https://issues.apache.org/jira/browse/FLINK-24948 > Project: Flink > Issue Type: Bug > Components: Connectors / JDBC >Affects Versions: 1.12.4 >Reporter: Paul Lin >Priority: Major > > Currently, JDBC connector assumes columns names respect Java identifier > naming restrictions, but Databases that support JDBC may have different > naming restrictions. For example, MySQL allows dots and colons in column > names. In that case, JDBC connector would have trouble parsing the SQL. > We could fix this by validating field names in `JdbcDmlOptions`. In addition, > it'd be good to clarify the naming restrictions of Flink SQL, so users and > connector developers would know the standard. -- This message was sent by Atlassian Jira (v8.20.1#820001)
