[MDEV-20791] In table partitioned by INET6 all values go into the same partition Created: 2019-10-09  Updated: 2020-06-08

Status: Open
Project: MariaDB Server
Component/s: Data types, Partitioning
Affects Version/s: 10.5
Fix Version/s: 10.5

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-22831 PARTITION BY KEY ALGORITHM={crc32|mur... Stalled
Relates
relates to MDEV-274 The data type for IPv6/IPv4 addresses... Closed
relates to MDEV-20784 Testing for MDEV-274 (INET6, data typ... Closed

 Description    

create table t1 (a inet6) partition by key(a) partitions 2;
 
insert into t1 values ('0:db8::ff00:42:8329'),('2001:db8::ff00:42:8329'),('::'),('::192.0.2.128'),('::ffff:192.0.2.128');
 
select * from t1 partition (p0);
 
select * from t1 partition (p1);

10.5 b37386d8

 
MariaDB [test]> select * from t1 partition (p0);
 
Empty set (0.000 sec)
 
 
 
MariaDB [test]> select * from t1 partition (p1);
 
+------------------------+
 
| a                      |
 
+------------------------+
 
| 0:db8::ff00:42:8329    |
 
| 2001:db8::ff00:42:8329 |
 
| ::                     |
 
| ::192.0.2.128          |
 
| ::ffff:192.0.2.128     |
 
+------------------------+
 
5 rows in set (0.001 sec)

Compare with binary column:

MariaDB [test]> create table t2 (a binary(22)) partition by key(a) partitions 2;
 
Query OK, 0 rows affected (0.342 sec)
 
 
 
MariaDB [test]> insert into t2 values ('0:db8::ff00:42:8329'),('2001:db8::ff00:42:8329'),('::'),('::192.0.2.128'),('::ffff:192.0.2.128');
 
Query OK, 5 rows affected (0.050 sec)
 
Records: 5  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select * from t2 partition (p0);
 
+------------------------+
 
| a                      |
 
+------------------------+
 
| 0:db8::ff00:42:8329    |
 
| ::192.0.2.128          |
 
+------------------------+
 
2 rows in set (0.000 sec)
 
 
 
MariaDB [test]> select * from t2 partition (p1);
 
+------------------------+
 
| a                      |
 
+------------------------+
 
| 2001:db8::ff00:42:8329 |
 
| ::                     |
 
| ::ffff:192.0.2.128     |
 
+------------------------+
 
3 rows in set (0.000 sec)



 Comments   
Comment by Alexander Barkov [ 2019-10-10 ]

This BINARY test (with data equivalent to the above INET6 test) demonstrates that BINARY has the same problem:

CREATE OR REPLACE TABLE t1 (a BINARY(16)) PARTITION BY KEY (a) PARTITIONS 2;
 
INSERT INTO t1 VALUES (X'00000DB8000000000000FF0000428329');
 
INSERT INTO t1 VALUES (X'20010DB8000000000000FF0000428329');
 
INSERT INTO t1 VALUES (X'00000000000000000000000000000000');
 
INSERT INTO t1 VALUES (X'000000000000000000000000C0000280');
 
INSERT INTO t1 VALUES (X'00000000000000000000FFFFC0000280');


SELECT HEX(a) FROM t1 PARTITION (p0);


Empty set (0.001 sec)


SELECT HEX(a) FROM t1 PARTITION (p1);


+----------------------------------+
 
| HEX(a)                           |
 
+----------------------------------+
 
| 00000DB8000000000000FF0000428329 |
 
| 20010DB8000000000000FF0000428329 |
 
| 00000000000000000000000000000000 |
 
| 000000000000000000000000C0000280 |
 
| 00000000000000000000FFFFC0000280 |
 
+----------------------------------+

Passing different values demonstrates it does distribute between partitions:

CREATE OR REPLACE TABLE t1 (a INET6) PARTITION BY KEY (a) PARTITIONS 2;
 
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8320');
 
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8321');
 
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8322');
 
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8323');
 
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8324');
 
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8325');
 
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8326');
 
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8327');
 
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8328');
 
INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');
 
INSERT INTO t1 VALUES ('2002:db8::ff00:42:8329');
 
INSERT INTO t1 VALUES ('2003:db8::ff00:42:8329');
 
INSERT INTO t1 VALUES ('2004:db8::ff00:42:8329');
 
INSERT INTO t1 VALUES ('2005:db8::ff00:42:8329');
 
SELECT a FROM t1 PARTITION (p0);
 
SELECT a FROM t1 PARTITION (p1);


MariaDB [test]> SELECT a FROM t1 PARTITION (p0);
 
+------------------------+
 
| a                      |
 
+------------------------+
 
| 2001:db8::ff00:42:8320 |
 
| 2001:db8::ff00:42:8322 |
 
| 2001:db8::ff00:42:8324 |
 
| 2001:db8::ff00:42:8326 |
 
| 2001:db8::ff00:42:8328 |
 
+------------------------+
 
5 rows in set (0.001 sec)
 
 
 
MariaDB [test]> SELECT a FROM t1 PARTITION (p1);
 
+------------------------+
 
| a                      |
 
+------------------------+
 
| 2001:db8::ff00:42:8321 |
 
| 2001:db8::ff00:42:8323 |
 
| 2001:db8::ff00:42:8325 |
 
| 2001:db8::ff00:42:8327 |
 
| 2001:db8::ff00:42:8329 |
 
| 2002:db8::ff00:42:8329 |
 
| 2003:db8::ff00:42:8329 |
 
| 2004:db8::ff00:42:8329 |
 
| 2005:db8::ff00:42:8329 |
 
+------------------------+
 
9 rows in set (0.001 sec)

Comment by Alexander Barkov [ 2019-10-10 ]

The problem seems to reside in this piece of the code:

#define MY_HASH_ADD(A, B, value) \
 
  do { A^= (((A & 63)+B)*((value)))+ (A << 8); B+=3; } while(0)

It works badly on binary data.

Changing INET6 to use a different hash algorithm, say crc32, will need changes in:

ha_partition::calculate_key_hash_value()

Generated at Thu Feb 08 09:02:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.