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)
