matrixorigin / matrixorigin/matrixone
[Compatibility]: TO_BASE64 materializes bounded VARBINARY input as TEXT and prevents indexing
- 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
Assessment
This issue has not been assessed yet.