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
|
#
# Start of 10.10 tests
#
#
# MDEV-23287 The INET4 data type
#
SET default_storage_engine=MyISAM;
#
# Range optimizer
#
CREATE TABLE t1 (a INET4, INDEX(a));
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` inet4 DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
FOR i IN 0..255
DO
INSERT INTO t1 VALUES (CONCAT('0.0.0.', i));
END FOR
$$
SELECT * FROM t1 WHERE a='0.0.0.255';
a
0.0.0.255
EXPLAIN SELECT * FROM t1 WHERE a='0.0.0.255';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a a 5 const 1 Using where; Using index
SELECT * FROM t1 WHERE a='garbage';
a
Warnings:
Warning 1292 Incorrect inet4 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 inet4 value: 'garbage'
SELECT * FROM t1 WHERE a>='0.0.0.254';
a
0.0.0.254
0.0.0.255
EXPLAIN SELECT * FROM t1 WHERE a>='0.0.0.254';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 5 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 ('0.0.0.128','0.0.0.160','0.0.0.240');
a
0.0.0.128
0.0.0.160
0.0.0.240
EXPLAIN SELECT * FROM t1 WHERE a IN ('0.0.0.128','0.0.0.160','0.0.0.240');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index
SELECT * FROM t1 WHERE a IN ('0.0.0.128','0.0.0.160','garbage');
a
0.0.0.128
0.0.0.160
Warnings:
Warning 1292 Incorrect inet4 value: 'garbage'
EXPLAIN SELECT * FROM t1 WHERE a IN ('0.0.0.128','0.0.0.160','garbage');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 5 NULL 2 Using where; Using index
Warnings:
Warning 1292 Incorrect inet4 value: 'garbage'
SELECT * FROM t1 WHERE a BETWEEN '0.0.0.128' AND '0.0.0.129';
a
0.0.0.128
0.0.0.129
EXPLAIN SELECT * FROM t1 WHERE a BETWEEN '0.0.0.128' AND '0.0.0.129';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 5 NULL 2 Using where; Using index
SELECT * FROM t1 WHERE a BETWEEN '0.0.0.128' AND 'garbage';
a
EXPLAIN SELECT * FROM t1 WHERE a BETWEEN '0.0.0.128' 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('0.0.0.255' AS INET4);
a
0.0.0.255
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=CAST('0.0.0.255' AS INET4);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ref a a 5 const 1 100.00 Using where; Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = INET4'0.0.0.255'
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 (c varchar(64), key(c)) engine=myisam;
INSERT INTO t1 VALUES ('0.0.0.1'),('0.0.0.1'),('0.0.0.2');
SELECT * FROM t1 WHERE c>CAST('0.0.0.1' AS INET4);
c
0.0.0.2
EXPLAIN SELECT * FROM t1 WHERE c>CAST('0.0.0.1' AS INET4);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index c c 67 NULL 3 Using where; Using index
SELECT * FROM t1 WHERE c=CAST('0.0.0.1' AS INET4);
c
0.0.0.1
0.0.0.1
EXPLAIN SELECT * FROM t1 WHERE c=CAST('0.0.0.1' AS INET4);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index c c 67 NULL 3 Using where; Using index
DROP TABLE t1;
#
# End of 10.10 tests
#
|
