Like operator returns wrong result if string ends with a number

[johannesrue]

What happens?

Hi there,

i think we identified a bug related to the like operator.

Given the example:

attach 'postgres://postgres:1234@localhost:5432/postgres' as "pg" (type postgres, SCHEMA 'my_schema');

use "pg";

create or replace table test as select '2F98ED5A409F4B35B36AF72773618FC9_P1' as id;
select * from test where id like '2F98ED5A409F4B35B36AF72773618FC9%';

The result set is empty for the search string 2F98ED5A409F4B35B36AF72773618FC9%.

If the string ends with a character it just works fine 2F98ED5A409F4B35B36AF72773618FCX%:

attach 'postgres://postgres:1234@localhost:5432/postgres' as "pg" (type postgres, SCHEMA 'my_schema');

use "pg";

create or replace table test as select '2F98ED5A409F4B35B36AF72773618FCX_P1' as id;
select * from test where id like '2F98ED5A409F4B35B36AF72773618FCX%';

We did the test too with the pg client and both cases worked, so we assume the bug can be found in the duckdb_postgres extension.

Thank you for your support.

Cheers
Johannes

To Reproduce

attach 'postgres://postgres:1234@localhost:5432/postgres' as "pg" (type postgres, SCHEMA 'my_schema');

use "pg";

create or replace table test as select '2F98ED5A409F4B35B36AF72773618FC9_P1' as id;
select * from test where id like '2F98ED5A409F4B35B36AF72773618FC9%';

OS:

Linux

PostgreSQL Version:

18.1

DuckDB Version:

1.5.2

DuckDB Client:

JAR (Dbeaver)

Full Name:

Johannes Rückert

Affiliation:

IBIS Prof Thome AG

Have you tried this on the latest main branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[staticlibs]

Hi, thanks for the report! I am confirming the problem, it happens when the search string ends with a digit 9.

Current workaround it to use postgres_query():

FROM postgres_query('pg', 'select * from test where id like ''2F98ED5A409F4B35B36AF72773618FC9%''')

[johannesrue]

Since i am lacking the necessary ccp skills i have here an AI provided fix. I am not sure if it's valid or not:

The problem: val.ToString() doesn't add any type annotation in the generated SQL. When DuckDB computes the upper bound of a LIKE prefix (e.g. 'test1%' → upper bound 'test2'), this becomes WHERE col < 'test2' in the SQL sent to PostgreSQL. PostgreSQL may interpret the sort order of 'test2' differently depending on the collation, especially when the string ends with a digit and the next boundary character is "unexpected" (e.g. the character after '9' in the ASCII table is ':').
Concrete scenario: For LIKE 'abc9%', DuckDB computes the upper bound by incrementing '9' to ':' → col < 'abc:'. With PostgreSQL's C collation this works correctly, but with an ICU or locale-based collation (en_US.UTF-8 etc.), ':' may sort differently than expected.
Possible fix in postgres_filter_pushdown.cpp:

string PostgresFilterPushdown::TransformConstantFilter(string &column_name, 
    ConstantFilter &constant_filter, column_t column_id) {
    string constant_string;
    if (IsVirtualColumn(column_id)) {
        return "FALSE";
    } else {
        constant_string = TransformLiteral(constant_filter.constant);
    }
    auto operator_string = TransformComparison(constant_filter.comparison_type);
    // FIX: Add COLLATE "C" for string comparisons that come from 
    // LIKE optimization, to match DuckDB's binary comparison semantics
    string collation_suffix;
    if (constant_filter.constant.type().id() == LogicalTypeId::VARCHAR) {
        collation_suffix = " COLLATE \"C\"";
    }
    return StringUtil::Format("%s%s %s %s", column_name, collation_suffix, 
                              operator_string, constant_string);
}