matrixorigin / matrixorigin/matrixone
[Compatibility]: IP predicate functions materialize as BIGINT instead of INT
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
### Problem
`IS_IPV4()`, `IS_IPV6()`, `IS_IPV4_COMPAT()`, and `IS_IPV4_MAPPED()` produce the same `0`/`1` values as MySQL, but MatrixOne exposes and persists their result as a 64-bit integer. MySQL treats these predicate results as one-digit values and materializes them as `INT` in views and CTAS tables.
This changes the schema produced by otherwise compatible SQL and makes clients infer a wider result type on MatrixOne.
### Reproduction
```sql
drop database if exists ip_type_repro;
create database ip_type_repro;
use ip_type_repro;
create table src (
id int primary key,
ip_text varchar(39),
ip_binary varbinary(16)
);
insert into src values
(1, '192.168.1.1', unhex('00000000000000000000ffffc0a80101')),
(2, '2001:db8::1', unhex('20010db8000000000000000000000001'));
create view v as
select id,
is_ipv4(ip_text) as v4,
is_ipv6(ip_text) as v6,
is_ipv4_compat(ip_binary) as compat,
is_ipv4_mapped(ip_binary) as mapped
from src;
create table ctas as select * from v;
select table_name, column_name, data_type, column_type, numeric_precision
from information_schema.columns
where table_schema = database()
and table_name in ('v', 'ctas')
order by table_name, ordinal_position;
```
Also inspect the direct-expression metadata:
```bash
mysql --column-type-info -vvv -e "select is_ipv4('1.2.3.4') as v4"
```
### MatrixOne result
For both `v` and `ctas`, all four predicate columns are:
```text
data_type=bigint, column_type=BIGINT, numeric_precision=19
```
The direct expression is reported as `LONGLONG` with `Length: 64`.
### MySQL 8.0.45 result
For both `v` and `ctas`, all four predicate columns are:
```text
data_type=int, column_type=int, numeric_precision=10
```
The direct expression is reported as `LONGLONG` with `Length: 1`.
### Scope checked
- all four IP predicate functions;
- direct expression metadata;
- view metadata;
- CTAS metadata and stored values;
- valid IPv4, IPv6, IPv4-compatible, IPv4-mapped, invalid, and `NULL` inputs;
- three independent runs with stable results;
- generated columns, generated-column indexes, source updates, transaction rollback, `ALTER` drop/re-add, and prepared execution continue to produce correct values.
### 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.