1#
2# test using Index Condition Pushdown for partitioned tables
3#
4# Test failure of ICP calls -> don't use ICP (MyISAM does not support
5# ICP on BLOB indexes)
6CREATE TABLE t1 (a int PRIMARY KEY, b BLOB, c varchar(16) DEFAULT 'Filler...', INDEX (b(4), a))
7ENGINE = MyISAM
8PARTITION BY HASH (a) PARTITIONS 3;
9Warnings:
10Warning	1287	The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead.
11SHOW CREATE TABLE t1;
12Table	Create Table
13t1	CREATE TABLE `t1` (
14  `a` int(11) NOT NULL,
15  `b` blob,
16  `c` varchar(16) DEFAULT 'Filler...',
17  PRIMARY KEY (`a`),
18  KEY `b` (`b`(4),`a`)
19) ENGINE=MyISAM DEFAULT CHARSET=latin1
20/*!50100 PARTITION BY HASH (a)
21PARTITIONS 3 */
22Warnings:
23Warning	1287	The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead.
24INSERT INTO t1 (a, b) VALUES (1, 0xdeadbeef), (2, "text filler"),
25(3, 'filler...'), (4, " more filler "), (5, "test text"), (6, "testing...");
26ANALYZE TABLE t1;
27Table	Op	Msg_type	Msg_text
28test.t1	analyze	status	OK
29test.t1	analyze	warning	The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead.
30EXPLAIN SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2);
31id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
321	SIMPLE	t1	p0,p1,p2	range	b	b	7	NULL	6	100.00	Using where
33Warnings:
34Warning	1287	The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead.
35Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,hex(`test`.`t1`.`b`) AS `HEX(b)` from `test`.`t1` where ((`test`.`t1`.`b` >= 'te') and (`test`.`t1`.`a` % 2))
36EXPLAIN FORMAT=JSON SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2);
37EXPLAIN
38{
39  "query_block": {
40    "select_id": 1,
41    "cost_info": {
42      "query_cost": "9.41"
43    },
44    "table": {
45      "table_name": "t1",
46      "partitions": [
47        "p0",
48        "p1",
49        "p2"
50      ],
51      "access_type": "range",
52      "possible_keys": [
53        "b"
54      ],
55      "key": "b",
56      "used_key_parts": [
57        "b"
58      ],
59      "key_length": "7",
60      "rows_examined_per_scan": 6,
61      "rows_produced_per_join": 6,
62      "filtered": "100.00",
63      "cost_info": {
64        "read_cost": "8.21",
65        "eval_cost": "1.20",
66        "prefix_cost": "9.41",
67        "data_read_per_join": "240"
68      },
69      "used_columns": [
70        "a",
71        "b"
72      ],
73      "attached_condition": "((`test`.`t1`.`b` >= 'te') and (`test`.`t1`.`a` % 2))"
74    }
75  }
76}
77Warnings:
78Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,hex(`test`.`t1`.`b`) AS `HEX(b)` from `test`.`t1` where ((`test`.`t1`.`b` >= 'te') and (`test`.`t1`.`a` % 2))
79SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2);
80a	HEX(b)
811	DEADBEEF
825	746573742074657874
83Only MyISAM and InnoDB supports both INDEX and BLOBS...
84ALTER TABLE t1 ENGINE = InnoDB;
85ANALYZE TABLE t1;
86Table	Op	Msg_type	Msg_text
87test.t1	analyze	status	OK
88EXPLAIN SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2);
89id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
901	SIMPLE	t1	p0,p1,p2	range	b	b	7	NULL	4	100.00	Using index condition; Using where
91Warnings:
92Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,hex(`test`.`t1`.`b`) AS `HEX(b)` from `test`.`t1` where ((`test`.`t1`.`b` >= 'te') and (`test`.`t1`.`a` % 2))
93EXPLAIN FORMAT=JSON SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2);
94EXPLAIN
95{
96  "query_block": {
97    "select_id": 1,
98    "cost_info": {
99      "query_cost": "6.61"
100    },
101    "table": {
102      "table_name": "t1",
103      "partitions": [
104        "p0",
105        "p1",
106        "p2"
107      ],
108      "access_type": "range",
109      "possible_keys": [
110        "b"
111      ],
112      "key": "b",
113      "used_key_parts": [
114        "b"
115      ],
116      "key_length": "7",
117      "rows_examined_per_scan": 4,
118      "rows_produced_per_join": 4,
119      "filtered": "100.00",
120      "index_condition": "(`test`.`t1`.`a` % 2)",
121      "cost_info": {
122        "read_cost": "5.81",
123        "eval_cost": "0.80",
124        "prefix_cost": "6.61",
125        "data_read_per_join": "160"
126      },
127      "used_columns": [
128        "a",
129        "b"
130      ],
131      "attached_condition": "(`test`.`t1`.`b` >= 'te')"
132    }
133  }
134}
135Warnings:
136Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,hex(`test`.`t1`.`b`) AS `HEX(b)` from `test`.`t1` where ((`test`.`t1`.`b` >= 'te') and (`test`.`t1`.`a` % 2))
137SELECT a, HEX(b) FROM t1 WHERE b >= 'te' and (a % 2);
138a	HEX(b)
1391	DEADBEEF
1405	746573742074657874
141DROP TABLE t1;
142