ClickHouse: /co lookup fails when results include item rows (alias 0 as data shadows the data column)

[smoleyxd]

Description

On the ClickHouse backend (development branch), /co lookup and /co inspect fail whenever the result set includes rows from the item table. The lookup thread throws and the command returns nothing:

java.sql.SQLException: Method: getBytes("10") encountered an exception.
	at com.clickhouse.jdbc.internal.ExceptionUtils.toSqlState(ExceptionUtils.java:70)
	at com.clickhouse.jdbc.ResultSetImpl.getBytes(ResultSetImpl.java:246)
	at net.coreprotect.utility.DatabaseUtils.getBytes(DatabaseUtils.java:34)
	at net.coreprotect.database.LookupRaw.performLookupRaw(LookupRaw.java:224)
	at net.coreprotect.database.Lookup.performPartialLookup(Lookup.java:268)
	at net.coreprotect.command.lookup.StandardLookupThread.run(StandardLookupThread.java:287)
Caused by: com.clickhouse.client.api.ClientException: Column is not of array type
	at com.clickhouse.client.api.data_formats.internal.AbstractBinaryFormatReader.getPrimitiveArray(AbstractBinaryFormatReader.java:520)
java.lang.NullPointerException: Cannot invoke "java.util.List.iterator()" because "lookupList" is null
	at net.coreprotect.command.lookup.StandardLookupThread.run(StandardLookupThread.java:292)

Writes/logging are unaffected; only the read path breaks.

Root cause

ClickHouse resolves identifiers to select-list aliases before table columns (default prefer_column_name_to_alias = 0). The item lookup SQL generated by LookupRaw reuses a real column name as an alias:

SELECT ... type, data as metadata, 0 as data, amount, ... FROM co_item WHERE ...

The data in data as metadata resolves to the 0 as data alias instead of the co_item.data column, so metadata comes back as the literal 0 (UInt8) rather than the serialized item blob (Array(Int8)). Minimal repro against any populated database:

SELECT toTypeName(metadata) FROM (SELECT data as metadata, 0 as data FROM co_item LIMIT 1)
-- returns: UInt8
SELECT toTypeName(metadata) FROM (SELECT data as metadata FROM co_item LIMIT 1)
-- returns: Array(Int8)

Consequences:

• Standalone item lookups: metadata is UInt8 0; the JDBC client (clickhouse-jdbc 0.9.8) throws Column is not of array type when DatabaseUtils.getBytes reads it, aborting the whole lookup.
• Mixed lookups (block/container/entity_container/item UNION ALL): the branches disagree (Array(Int8) vs UInt8), and ClickHouse 26.x silently unifies them to Variant(Array(Int8), UInt8). The server reports success, then the client fails the same way while reading rows.
• WHERE clauses referencing the shadowed column also mis-resolve, e.g. WHERE notEmpty(data) on that item query fails with ILLEGAL_TYPE_OF_ARGUMENT: Illegal type UInt8 of argument of function notEmpty.

SQLite and MySQL resolve the original column in this pattern, which is why the queries work there and this only surfaces on ClickHouse.

Suggested fix

Setting the session setting on the connection restores the standard-SQL alias semantics the queries assume. Verified working on a live production database (ClickHouse 26.7.1, ~85M events across two servers) — one line in ClickHouseJdbcConfig:

properties.setProperty(SERVER_SETTING_PREFIX + "prefer_column_name_to_alias", "1");

With that set, item lookups return the real blobs and the union columns unify to Array(Int8) again. Alternatively the generated SQL could avoid reusing real column names as aliases (e.g. qualify with a table alias: SELECT t.data as metadata, 0 as data FROM co_item t), which would also fix it without the setting.

Environment

• CoreProtect: development branch build (reproduced at 2a9f123; the query pattern is still present on current master)
• ClickHouse server: 26.7.1
• clickhouse-jdbc: 0.9.8 (as pinned in pom.xml)
• Server: Paper-based 1.21.11 (Folia scheduling), Java 25