CONCAT produces extra trailing bytes for a BLOB argument in a UTF-16le database

Open
#1,483 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
48/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
java, sql, sqlite

Research direction

Start by reproducing the SQL script against a newly created database, setting UTF-16le encoding before creating tables; compare the HEX results for the direct cast and CONCAT. The issue names no source files or tests, so trace CONCAT's handling of BLOB arguments and BLOB-to-text conversion in the SQLite code bundled with sqlite-jdbc. Done means the single-argument CONCAT result matches the expected text without trailing bytes, with the UTF-16le case verified.

Written by the indexing model from the issue text.

Description

triage

I observed unexpected trailing bytes when passing a BLOB argument to CONCAT() in a UTF-16le database.

Reproduction

Run this script against a newly created, empty database. The encoding must be set before creating any tables.

PRAGMA encoding = 'UTF-16le';

CREATE TABLE t(x);
INSERT INTO t VALUES(960613696.0);

PRAGMA encoding;

SELECT
    HEX(CAST(CAST(x AS BLOB) AS TEXT)) AS direct_text,
    HEX(CONCAT(CAST(x AS BLOB))) AS concatenated
FROM t;

SELECT sqlite_version(), sqlite_source_id();
Actual result
direct_text:
3900360030003600310033003600390036002E003000

concatenated:
3900360030003600310033003600390036002E00300000000300000000000000000000000000000000000000

The CONCAT result contains the expected prefix followed by additional bytes.

Expected result

I would expect CONCAT() with this single non-NULL argument to produce the same text content as the explicit conversion to TEXT, without additional trailing bytes.

Version tested
SQLite 3.51.1
2025-11-28 17:28:25
281fc0e9afc38674b9b0991943b9e9d1e64c6cbdb133d35f6f5c87ff6af38a88

I also tested a development Fiddle build:

SQLite 3.54.0
2026-10-05 14:22:13
4bfc6e53a95d710b7f40fa9c80d8cdd1df50d69ed8614e9800f7e6a1ca5a3c29

However, that test produced UTF-8 output, so it did not reproduce the same encoding conditions. I have not yet verified whether the issue persists in the development build with a UTF-16le database.

Is this a known or already-fixed issue involving CONCAT(), BLOB-to-text conversion, and UTF-16 database encoding?

Dominant language
Java
Stars
3.3k
Forks
676
Avg merge
1d 15h
Merged PRs (30d)
18

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from xerial/sqlite-jdbc

All issues in xerial/sqlite-jdbc

Similar issues

More Java issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.