Pushdown filter can produce wrong results

[jakobbraun]

This virtual schema pushes down filters to DynamoDB. In case the mapping did convert the type of the column, this filter will not find the converted fields.

Example:

Dynamodb table with row id that has the value 1, 2 and 3.

Open questions:

• Does "row id" mean "partition key"?
• Should the row id contain integers or strings?

In EDML mapping this row is mapped to an Exasol column using a toStringMapping with nonStringBehaviour set to CONVERT_OR_ABORT. See https://github.com/exasol/virtual-schema-common-document/blob/main/doc/user_guide/edml_user_guide.md#supported-conversion

So in the Exasol tables there are the strings "1", "2", and "3".

Open questions:

• Does this mean the attribute "row id" is mapped to a column?
• Is the column named ID?

Consider now the following query:

SELECT ID FROM TEST.TEST WHERE ID = '1'

In case the virtual schema adapter sends this filter to DynamoDB, the query will return no results.

That's wrong behavior since a query with no selection will return the results:

SELECT ID FROM TEST.TEST

Workaround

Don't convert values in mappings for columns you want to filter.

Options for Mitigation

• O1) Add a prominent note to the user-guide that columns with convert enabled don't support filter pushdown.
• O2) Add a mechanism to this virtual schema adapter that prevents converted columns from being pushed down

Please note that option O2 will make query results correct but very inefficient, because EXASOL DB needs to read all data from DynamoDB before resolving the WHERE clause.

The current ticket therefore proposes to apply option O1.

[ckunki]

Changed ticket label to "documentation".