1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
#
# Start of 10.5 tests
#
#
# MDEV-274 The data type for IPv6/IPv4 addresses in MariaDB
#
SET default_storage_engine=InnoDB;
#
# Range optimizer
#
CREATE TABLE t1 (a INET6, INDEX(a));
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` inet6 DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
FOR i IN 0..255
DO
INSERT INTO t1 VALUES (CONCAT('::', HEX(i)));
END FOR
$$
SELECT * FROM t1 WHERE a='::ff';
a
::ff
EXPLAIN SELECT * FROM t1 WHERE a='::ff';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a a 17 const 1 Using where; Using index
SELECT * FROM t1 WHERE a='garbage';
a
Warnings:
Warning 1292 Incorrect inet6 value: 'garbage'
EXPLAIN SELECT * FROM t1 WHERE a='garbage';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
Warning 1292 Incorrect inet6 value: 'garbage'
SELECT * FROM t1 WHERE a>='::fe';
a
::fe
::ff
EXPLAIN SELECT * FROM t1 WHERE a>='::fe';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 17 NULL 2 Using where; Using index
SELECT * FROM t1 WHERE a>='garbage';
a
EXPLAIN SELECT * FROM t1 WHERE a>='garbage';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
SELECT * FROM t1 WHERE a IN ('::80','::a0','::f0');
a
::80
::a0
::f0
EXPLAIN SELECT * FROM t1 WHERE a IN ('::80','::a0','::f0');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 17 NULL 3 Using where; Using index
SELECT * FROM t1 WHERE a IN ('::80','::a0','garbage');
a
::80
::a0
Warnings:
Warning 1292 Incorrect inet6 value: 'garbage'
EXPLAIN SELECT * FROM t1 WHERE a IN ('::80','::a0','garbage');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 17 NULL 2 Using where; Using index
Warnings:
Warning 1292 Incorrect inet6 value: 'garbage'
SELECT * FROM t1 WHERE a BETWEEN '::80' AND '::81';
a
::80
::81
EXPLAIN SELECT * FROM t1 WHERE a BETWEEN '::80' AND '::81';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 17 NULL 2 Using where; Using index
SELECT * FROM t1 WHERE a BETWEEN '::80' AND 'garbage';
a
EXPLAIN SELECT * FROM t1 WHERE a BETWEEN '::80' AND 'garbage';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
SELECT * FROM t1 WHERE a=CAST('::ff' AS INET6);
a
::ff
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST('::ff' AS INET6);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ref a a 17 const 1 100.00 Using where; Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = INET6'::ff'
DROP TABLE t1;
#
# MDEV-26742 Assertion `field->type_handler() == this' failed in FixedBinTypeBundle<NATIVE_LEN, MAX_CHAR_LEN>::Type_handler_fbt::stored_field_cmp_to_item
#
CREATE TABLE t1 (pk inet6, c text) engine=myisam;
INSERT INTO t1 VALUES ('::',1);
CREATE TABLE t2 (d text, KEY (d)) engine=innodb ;
Warnings:
Note 1071 Specified key was too long; max key length is 3072 bytes
INSERT INTO t2 VALUES (2);
SELECT * FROM t2 JOIN t1 ON ( t1.pk > t2.d);
d pk c
Warnings:
Warning 1292 Incorrect inet6 value: '2'
UPDATE t2 JOIN t1 ON ( t1.pk > t2.d) SET t1.c = 1;
ERROR 22007: Incorrect inet6 value: '2'
SET sql_mode='';
UPDATE t2 JOIN t1 ON ( t1.pk > t2.d) SET t1.c = 1;
Warnings:
Warning 1292 Incorrect inet6 value: '2'
SET sql_mode=DEFAULT;
SELECT * FROM t1;
pk c
:: 1
SELECT * FROM t2;
d
2
DROP TABLE t1, t2;
#
# End of 10.5 tests
#
|
