matrixorigin / matrixorigin/matrixone

[Compatibility]: IP predicate functions materialize as BIGINT instead of INT

Open
#28,894 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

`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

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.