[jira] [Created] (FLINK-34146) JDBC lookup joins fail with RDB column names containing colons

["david radley (Jira)" <ji...@apache.org>]

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)