[
https://issues.apache.org/jira/browse/FLINK-34146?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Martijn Visser updated FLINK-34146:
-----------------------------------
Component/s: (was: Table SQL / JDBC)
> 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
> Affects Versions: 1.18.1
> Reporter: david radley
> Priority: Major
>
> [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)
