matrixorigin / matrixorigin/matrixone

[Bug]: NULL value in partition table is not correct

Open
#18,684 22 comments 0 reactions 1 assignee Claimed by @ck89119 View on GitHub
area/partition deferred kind/bug
Dominant language
Go
Stars
1.9k
Forks
311
Avg merge
1d 3h
Merged PRs (30d)
768

Description

### Is there an existing issue for the same bug?

- [X] I have checked the existing issues.

### Branch Name

main

### Commit ID

b2d8e4aefb0d7b3b3d7deb19a772c6bbfa973a9f

### Other Environment Information

```Markdown
- Hardware parameters:
- OS type:
- Others:
```

### Actual Behavior

mo:
mysql> create table t1 (a int,b int,c int,key(a,b))
-> partition by range (a)
-> partitions 3
-> (partition x1 values less than (0),
-> partition x2 values less than (10),
-> partition x3 values less than maxvalue);
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> insert into t1 values (NULL, 1, 1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values (0, 1, 1);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 values (12, 1, 1);
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> select partition_name, partition_description, table_rows
-> from information_schema.partitions where table_schema ='test';
+----------------+-----------------------+------------+
| partition_name | partition_description | table_rows |
+----------------+-----------------------+------------+
| x1 | 0 | 0 |
| x2 | 10 | 1 |
| x3 | MAXVALUE | **2** |
+----------------+-----------------------+------------+

the NULL value should not put into to last partitition

mysql:
mysql> create table t1 (a int,b int,c int,key(a,b))
-> partition by range (a)
-> partitions 3
-> (partition x1 values less than (0),
-> partition x2 values less than (10),
-> partition x3 values less than maxvalue);
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> insert into t1 values (NULL, 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (0, 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (12, 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select partition_name, partition_description, table_rows
-> from information_schema.partitions where table_schema ='test';
+----------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+-----------------------+------------+
| NULL | NULL | 2 |
| p0 | 1199116800 | 0 |
| p1 | 1206979200 | 0 |
| p2 | 1214841600 | 0 |
| p3 | 1222790400 | 0 |
| p4 | 1230739200 | 0 |
| p5 | 1238515200 | 0 |
| p6 | 1246377600 | 0 |
| p7 | 1254326400 | 0 |
| p8 | 1262275200 | 0 |
| p9 | MAXVALUE | 0 |
| x1 | 0 | 1 |
| x2 | 10 | 1 |
| x3 | MAXVALUE | **1** |
| NULL | NULL | 0 |
| NULL | NULL | 0 |
| NULL | NULL | 0 |
| NULL | NULL | 0 |
| NULL | NULL | 0 |
+----------------+-----------------------+------------+
19 rows in set (0.00 sec)

### Expected Behavior

_No response_

### Steps to Reproduce

```Markdown
create table t1 (a int,b int,c int,key(a,b))
partition by range (a)
partitions 3
(partition x1 values less than (0),
partition x2 values less than (10),
partition x3 values less than maxvalue);

insert into t1 values (NULL, 1, 1);
insert into t1 values (0, 1, 1);
insert into t1 values (12, 1, 1);

select partition_name, partition_description, table_rows
from information_schema.partitions where table_schema ='test';
SHOW CREATE TABLE t1;
drop table t1;
```

### Additional information

_No response_

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.