[FLINK-34146] JDBC lookup joins fail with RDB column names containing colons

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.18.1
• Component/s: Connectors / JDBC
• Assignee: Unassigned
• Reporter: david radley
• Created: 18/Jan/24 09:34
• Updated: 18/Jan/24 10:36

Description

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 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); }