david radley created FLINK-34146: ------------------------------------ Summary: JDBC lookup joins fail with RDB column names containing colons Key: FLINK-34146 URL: https://issues.apache.org/jira/browse/FLINK-34146 Project: Flink Issue Type: Bug Components: Connectors / JDBC, Table SQL / JDBC Affects Versions: 1.18.1 Reporter: david radley
[https://github.com/apache/flink-connector-jdbc/pull/79] adds filter support for lookup joins. This was implemented using FieldNamedPreparedStatements in line with the way that the join key was implemented. The [FieldNamedPreparedStatementImpl logic|https://github.com/apache/flink-connector-jdbc/blob/e3dd84160cd665ae17672da8b6e742e61a72a32d/flink-connector-jdbc/src/main/java/org/apache/flink/connector/jdbc/statement/FieldNamedPreparedStatementImpl.java#L221] explicitly tests for the colon key and can incorrectly pickup column names. So JDBC lookup joins fail with RDB column names containing colons when used in filters and lookup keys. It looks like we have used the approach from [https://stackoverflow.com/questions/2309970/named-parameters-in-jdbc]. It says {{Please note that the above simple example does not handle using named parameter twice. Nor does it handle using the : sign inside quotes.}} It looks like we could play with some Regex Patterns to see if we can get one that works well for us. A junit that shows the issue can be added to FieldNamedPreparedStatementImplTest ... private final String[] fieldNames2 = new String[] \{"id?:", "name:?", "email", "ts", "field1", "field_2", "__field_3__"}; private final String[] keyFields2 = new String[] \{"id?:", "__field_3__"}; ... @Test void testSelectStatementWithWeirdCharacters() { String selectStmt = dialect.getSelectFromStatement(tableName, fieldNames2, keyFields2); assertThat(selectStmt) .isEqualTo( "SELECT `id?:`, `name:?`, `email`, `ts`, `field1`, `field_2`, `__field_3__` FROM `tbl` " + "WHERE `id?:` = :id?: AND `__field_3__` = :__field_3__"); NamedStatementMatcher.parsedSql( "SELECT `id?:`, `name:?`, `email`, `ts`, `field1`, `field_2`, `__field_3__` FROM `tbl` " + "WHERE `id?:` = ? AND `__field_3__` = ?") .parameter("id", singletonList(1)) .parameter("__field_3__", singletonList(2)) .matches(selectStmt); } -- This message was sent by Atlassian Jira (v8.20.10#820010)