matrixorigin / matrixorigin/matrixone

[Compatibility]: TO_BASE64 materializes bounded VARBINARY input as TEXT and prevents indexing

Open
#28,902 1 comment 0 reactions 1 assignee Assigned to @XuPeng-SH View on GitHub
area/compatibility kind/bug needs-triage
Dominant language
Go
Stars
1.9k
Forks
311
Avg merge
1d 3h
Merged PRs (30d)
768

Description

### Problem

`TO_BASE64` exposes every bounded string/binary input as unbounded `TEXT`. For a small `VARBINARY` input, this changes the View/CTAS schema and prevents ordinary secondary indexing even though the encoded result has a small deterministic maximum size.

### Reproduction

```sql
drop database if exists to_base64_type_repro;
create database to_base64_type_repro;
use to_base64_type_repro;

create table src(id int primary key, raw varbinary(128));
insert into src values (1,unhex('4100FF7A')),(2,unhex('612C622C2C63')),(3,null);

create view v as select id,to_base64(raw) b64 from src;
create table ctas as select * from v;

select table_name,column_name,data_type,column_type,character_maximum_length
from information_schema.columns
where table_schema=database() and table_name in ('v','ctas')
order by table_name,ordinal_position;

create index idx_b64 on ctas(b64);
```

### MatrixOne result

- `b64` is declared as `TEXT`, with `character_maximum_length=65535`, in both the View and CTAS table.
- `CREATE INDEX` returns `not supported: TEXT column 'b64' cannot be in index`.
- Scalar values themselves are correct (`QQD/eg==`, `YSxiLCxj`, and NULL).

### MySQL 8.0.45 result

- The same expression over `VARBINARY(128)` is declared as `VARCHAR(174)` in both the View and CTAS table.
- The secondary index is created and an indexed equality lookup returns the expected row.

### Controls and scope

NULL and embedded `0x00`/`0xff` bytes are included. Direct selection, View, CTAS, index creation, forced-index lookup, and prepared `TO_BASE64`/`FROM_BASE64` round trips are covered. Three independent executions produced identical results on each database.

The functional impact is not only metadata: bounded encoded values lose index eligibility after materialization.

### Code location

The scalar string overload in `pkg/sql/plan/function/list_builtIn.go` returns `types.T_text.ToType()` unconditionally. Its return bound can instead be derived from the declared input byte bound and Base64 expansion/line-wrap rules; large inputs may still require a LOB type.

### Environment

- MatrixOne: official `main` commit `0c3a04f390adaf6281fd592a49778ea5b1155e67`
- MySQL control: 8.0.45
- Local single-node MatrixOne deployment

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.