Incorrect results for joins with mixed string column types

Details

Detail name Value
Changelog Number 31555
Type Bug
Status Resolved
Affected Versions Exasol 7.1.0, Exasol 8.0.0, Exasol 8.29.0, Exasol 2025.1.0, Exasol 2026.1.0
Fix Versions Exasol 2025.1.14, Exasol 2026.1.2
Resolution Date 2026-08-06

Description

A query could return too few rows when multiple equality join conditions compare a common string expression with expressions that use different CHAR or VARCHAR type definitions.

Consider equality join conditions of the form x = y and y = z, where:

  • x has type VARCHAR(N);
  • y, the common expression, has type CHAR(M) and
  • z has type CHAR(L).

The problem can occur when:

  • M <= N, allowing x to contain a value whose actual length is M;
  • M != L;
  • the value in x has an actual length of M and matches the stored value of y; and
  • the values in y and z have the same non-blank content and differ only in trailing spaces.

Under these conditions, x = y evaluates to true because the VARCHAR value matches the stored CHAR(M) value. The comparison y = z also evaluates to true because both operands are CHAR values and blank padding is ignored. However, x = z evaluates to false because the VARCHAR value retains its runtime length and is not padded to the declared length of z.

Both specified join conditions can therefore evaluate to true even though the direct comparison between the other two
expressions evaluates to false. The query could then incorrectly discard a matching row. No additional ordering between N and L is required, and the same condition applies if the positions of x and z are reversed.

This is not limited to values for which trailing spaces were explicitly supplied. Padding introduced when storing a value in a {{CHAR}}column can also produce the affected condition.

Examples

The following query uses a CHAR(2) expression in equality conditions with VARCHAR(2) and CHAR(3) expressions:

  CREATE TABLE scan_t_c2(a CHAR(2));
  CREATE TABLE index_t_v2c3(x VARCHAR(2), y CHAR(3));

  INSERT INTO scan_t_c2 VALUES ('a ');
  INSERT INTO index_t_v2c3 VALUES ('a ', 'a  ');

  -- Before fix: 0 rows
  -- Expected: 1 row
  SELECT *
  FROM scan_t_c2 scant
  JOIN index_t_v2c3 indext
    ON scant.a = indext.x
   AND scant.a = indext.y;

Workaround

Where appropriate, cast the string expressions used in the related join conditions to the same string type, length and character set. Ensure that the selected common type provides the intended blank-padding and comparison behavior for the query.

Example 

  CREATE TABLE scan_t_c2(a CHAR(2));
  CREATE TABLE index_t_v2c3(x VARCHAR(2), y CHAR(3));

  INSERT INTO scan_t_c2 VALUES ('a ');
  INSERT INTO index_t_v2c3 VALUES ('a ', 'a  ');

  SELECT *
  FROM scan_t_c2 scant
  JOIN index_t_v2c3 indext
    ON CAST(scant.a AS CHAR(3)) = CAST(indext.x AS CHAR(3))
   AND CAST(scant.a AS CHAR(3)) = CAST(indext.y AS CHAR(3));

Fix

The query returns the rows that match its specified join conditions.