JdbcPagingItemReader does not support joining a table onto itself [BATCH-2467]

Marshall Mann-Wood opened BATCH-2467 and commented

When using the SqlPagingQueryProviderFactoryBean a sortKey must be defined. Due to this fix for Derby aliases are stripped from the sort key. This is fine as long as column names are distinct, but if they cannot be made distinct then this can cause issue.

Example:

Using MySQL

CREATE TABLE IF NOT EXISTS temp_table ( 
    field_one VARCHAR(12) PRIMARY KEY NOT NULL,
    field_two VARCHAR(12) DEFAULT NULL,
    ...
);

where field_one and field_two provide some relationship between entities in temp_table.

private static final String SELECT_CLAUSE =
        "SELECT " +
            "t1.field_one, " +
            "t1.field_two, " +
            ...
            "t1.field_n, " +
            "t2.field_one, " +
            "t2.field_two, " +
            ...
            "t2.field_n ";

private static final String FROM_CLAUSE =
        "FROM temp_table AS t1 " +
        "JOIN temp_table AS t2 " +
            "ON t1.field_one = t2.field_two";

private static final String ORDER_KEY = "t1.field_one";

Because the column names cannot be made distinct, an alias on the sort is necessary. Because the stripping of the alias only sometimes happens when sending to SQL and not when Batch reads the sort key, adding a "fake alias" (X.t1.field_one) is not a viable workaround. Because the sort key is sometimes used in the generated WHERE clause

private static final String SELECT_CLAUSE =
        "SELECT " +
            "t1.field_one AS sort_key," +
            "t1.field_one, " +
            ...;
private static final String ORDER_KEY = "sort_key";

is not viable.

I found the following workaround, but don't want to be tied to it in case it causes upgrade incompatibility

public ClassExtendsSqlPagingQueryProviderFactoryBean() throws Exception {
    super();
    super.setSelectClause(SELECT_CLAUSE);
    super.setFromClause(FROM_CLAUSE);
    super.setSortKey(ORDER_KEY);

    // Hack, inject custom provider
    Field providerField = this.getClass().getSuperclass().getDeclaredField("providers");
    providerField.setAccessible(true);
    ((Map) providerField.get(this)).put(DatabaseType.MYSQL, new MySqlPagingQueryProvider() {
        @Override
        public Map<String, Order> getSortKeysWithoutAliases() {
            return super.getSortKeys();
        }
    });
    providerField.setAccessible(false);
    // End hack
}

Affects: 3.0.3

1 votes, 2 watchers

Start with JdbcPagingItemReader, SqlPagingQueryProviderFactoryBean, and MySqlPagingQueryProvider, then reproduce the self-join example using the qualified sort key t1.field_one. Trace how aliases are stripped for SQL and how the sort key is reused in the generated WHERE clause; done means self-joins work without the reflection-based provider workaround.