1#
2# Bug#20443863 USE OF WORST_SEEKS IN FIND_BEST_REF() CAN LEAD TO
3#              WRONG QUERY PLAN
4#
5CREATE TABLE t1 (
6i1 INTEGER,
7i2 INTEGER,
8i3 INTEGER,
9KEY(i1,i2)
10) ENGINE=InnoDB;
11INSERT INTO t1 VALUES (1, 1, 1), (1, 1, 1),(1, 1, 1),(1, 1, 1),
12(2, 2, 1), (2, 2, 1),(2, 2, 1),(2, 2, 1),
13(3, 3, 1), (3, 3, 1),(3, 3, 1),(3, 3, 1);
14EXPLAIN FORMAT=JSON SELECT i3 FROM t1 WHERE i1 = 1 AND i2 = 1;
15EXPLAIN
16{
17  "query_block": {
18    "select_id": 1,
19    "cost_info": {
20      "query_cost": "2.80"
21    },
22    "table": {
23      "table_name": "t1",
24      "access_type": "ref",
25      "possible_keys": [
26        "i1"
27      ],
28      "key": "i1",
29      "used_key_parts": [
30        "i1",
31        "i2"
32      ],
33      "key_length": "10",
34      "ref": [
35        "const",
36        "const"
37      ],
38      "rows_examined_per_scan": 4,
39      "rows_produced_per_join": 4,
40      "filtered": "100.00",
41      "cost_info": {
42        "read_cost": "2.00",
43        "eval_cost": "0.80",
44        "prefix_cost": "2.80",
45        "data_read_per_join": "64"
46      },
47      "used_columns": [
48        "i1",
49        "i2",
50        "i3"
51      ]
52    }
53  }
54}
55Warnings:
56Note	1003	/* select#1 */ select `test`.`t1`.`i3` AS `i3` from `test`.`t1` where ((`test`.`t1`.`i2` = 1) and (`test`.`t1`.`i1` = 1))
57EXPLAIN FORMAT=JSON SELECT i3 FROM t1 WHERE i1 = 1 AND i3 = 1;
58EXPLAIN
59{
60  "query_block": {
61    "select_id": 1,
62    "cost_info": {
63      "query_cost": "2.80"
64    },
65    "table": {
66      "table_name": "t1",
67      "access_type": "ref",
68      "possible_keys": [
69        "i1"
70      ],
71      "key": "i1",
72      "used_key_parts": [
73        "i1"
74      ],
75      "key_length": "5",
76      "ref": [
77        "const"
78      ],
79      "rows_examined_per_scan": 4,
80      "rows_produced_per_join": 0,
81      "filtered": "10.00",
82      "cost_info": {
83        "read_cost": "2.00",
84        "eval_cost": "0.08",
85        "prefix_cost": "2.80",
86        "data_read_per_join": "6"
87      },
88      "used_columns": [
89        "i1",
90        "i3"
91      ],
92      "attached_condition": "(`test`.`t1`.`i3` = 1)"
93    }
94  }
95}
96Warnings:
97Note	1003	/* select#1 */ select `test`.`t1`.`i3` AS `i3` from `test`.`t1` where ((`test`.`t1`.`i3` = 1) and (`test`.`t1`.`i1` = 1))
98UPDATE mysql.server_cost
99SET cost_value=0.2
100WHERE cost_name="row_evaluate_cost";
101UPDATE mysql.server_cost
102SET cost_value=0.1
103WHERE cost_name="key_compare_cost";
104UPDATE mysql.server_cost
105SET cost_value=2.0
106WHERE cost_name="memory_temptable_create_cost";
107UPDATE mysql.server_cost
108SET cost_value=0.2
109WHERE cost_name="memory_temptable_row_cost";
110UPDATE mysql.server_cost
111SET cost_value=40
112WHERE cost_name="disk_temptable_create_cost";
113UPDATE mysql.server_cost
114SET cost_value=1.0
115WHERE cost_name="disk_temptable_row_cost";
116UPDATE mysql.engine_cost
117SET cost_value=1.0
118WHERE cost_name="memory_block_read_cost";
119UPDATE mysql.engine_cost
120SET cost_value=1.0
121WHERE cost_name="io_block_read_cost";
122UPDATE mysql.server_cost
123SET cost_value = 2 * cost_value;
124UPDATE mysql.engine_cost
125SET cost_value = 2 * cost_value;
126SELECT cost_name, cost_value FROM mysql.server_cost;
127cost_name	cost_value
128disk_temptable_create_cost	80
129disk_temptable_row_cost	2
130key_compare_cost	0.2
131memory_temptable_create_cost	4
132memory_temptable_row_cost	0.4
133row_evaluate_cost	0.4
134SELECT cost_name, cost_value FROM mysql.engine_cost;
135cost_name	cost_value
136io_block_read_cost	2
137memory_block_read_cost	2
138FLUSH OPTIMIZER_COSTS;
139EXPLAIN FORMAT=JSON SELECT i3 FROM t1 WHERE i1 = 1 AND i2 = 1;
140EXPLAIN
141{
142  "query_block": {
143    "select_id": 1,
144    "cost_info": {
145      "query_cost": "5.60"
146    },
147    "table": {
148      "table_name": "t1",
149      "access_type": "ref",
150      "possible_keys": [
151        "i1"
152      ],
153      "key": "i1",
154      "used_key_parts": [
155        "i1",
156        "i2"
157      ],
158      "key_length": "10",
159      "ref": [
160        "const",
161        "const"
162      ],
163      "rows_examined_per_scan": 4,
164      "rows_produced_per_join": 4,
165      "filtered": "100.00",
166      "cost_info": {
167        "read_cost": "4.00",
168        "eval_cost": "1.60",
169        "prefix_cost": "5.60",
170        "data_read_per_join": "64"
171      },
172      "used_columns": [
173        "i1",
174        "i2",
175        "i3"
176      ]
177    }
178  }
179}
180Warnings:
181Note	1003	/* select#1 */ select `test`.`t1`.`i3` AS `i3` from `test`.`t1` where ((`test`.`t1`.`i2` = 1) and (`test`.`t1`.`i1` = 1))
182EXPLAIN FORMAT=JSON SELECT i3 FROM t1 WHERE i1 = 1 AND i3 = 1;
183EXPLAIN
184{
185  "query_block": {
186    "select_id": 1,
187    "cost_info": {
188      "query_cost": "5.60"
189    },
190    "table": {
191      "table_name": "t1",
192      "access_type": "ref",
193      "possible_keys": [
194        "i1"
195      ],
196      "key": "i1",
197      "used_key_parts": [
198        "i1"
199      ],
200      "key_length": "5",
201      "ref": [
202        "const"
203      ],
204      "rows_examined_per_scan": 4,
205      "rows_produced_per_join": 0,
206      "filtered": "10.00",
207      "cost_info": {
208        "read_cost": "4.00",
209        "eval_cost": "0.16",
210        "prefix_cost": "5.60",
211        "data_read_per_join": "6"
212      },
213      "used_columns": [
214        "i1",
215        "i3"
216      ],
217      "attached_condition": "(`test`.`t1`.`i3` = 1)"
218    }
219  }
220}
221Warnings:
222Note	1003	/* select#1 */ select `test`.`t1`.`i3` AS `i3` from `test`.`t1` where ((`test`.`t1`.`i3` = 1) and (`test`.`t1`.`i1` = 1))
223UPDATE mysql.server_cost
224SET cost_value=DEFAULT;
225UPDATE mysql.engine_cost
226SET cost_value=DEFAULT;
227FLUSH OPTIMIZER_COSTS;
228DROP TABLE t1;
229#
230# Bug#20947871 INDEX SCAN COST IN TEST_IF_CHEAPER_ORDERING() DOES
231#              NOT USE COST CONSTANTS
232#
233CREATE TABLE t1 (
234pk INTEGER PRIMARY KEY,
235a INTEGER,
236b INTEGER,
237c CHAR(255),
238UNIQUE KEY k1 (a)
239);
240INSERT INTO t1 VALUES (1, 1, NULL, "Abc"), (2, 2, NULL, "Abc"),
241(3, 3, NULL, "Abc"), (4, 4, NULL, "Abc");
242INSERT INTO t1 SELECT a + 4, a + 4, b, c FROM t1;
243INSERT INTO t1 SELECT a + 8, a + 8, b, c FROM t1;
244INSERT INTO t1 SELECT a + 16, a + 16, b, c FROM t1;
245INSERT INTO t1 SELECT a + 32, a + 32, b, c FROM t1;
246INSERT INTO t1 SELECT a + 64, a + 64, b, c FROM t1;
247INSERT INTO t1 SELECT a + 128, a + 128, b, c FROM t1;
248CREATE TABLE t2 (
249d INTEGER PRIMARY KEY,
250e INTEGER
251);
252INSERT INTO t2 SELECT a, b FROM t1;
253# Query should be optimized for the LIMIT. Query plan should
254# use index without filesort
255EXPLAIN FORMAT=JSON SELECT * FROM t1 JOIN t2 ON b=d ORDER BY a LIMIT 4;
256EXPLAIN
257{
258  "query_block": {
259    "select_id": 1,
260    "cost_info": {
261      "query_cost": "365.40"
262    },
263    "ordering_operation": {
264      "using_filesort": false,
265      "nested_loop": [
266        {
267          "table": {
268            "table_name": "t1",
269            "access_type": "index",
270            "key": "k1",
271            "used_key_parts": [
272              "a"
273            ],
274            "key_length": "5",
275            "rows_examined_per_scan": 4,
276            "rows_produced_per_join": 256,
277            "filtered": "100.00",
278            "cost_info": {
279              "read_cost": "7.00",
280              "eval_cost": "51.20",
281              "prefix_cost": "58.20",
282              "data_read_per_join": "68K"
283            },
284            "used_columns": [
285              "pk",
286              "a",
287              "b",
288              "c"
289            ],
290            "attached_condition": "(`test`.`t1`.`b` is not null)"
291          }
292        },
293        {
294          "table": {
295            "table_name": "t2",
296            "access_type": "eq_ref",
297            "possible_keys": [
298              "PRIMARY"
299            ],
300            "key": "PRIMARY",
301            "used_key_parts": [
302              "d"
303            ],
304            "key_length": "4",
305            "ref": [
306              "test.t1.b"
307            ],
308            "rows_examined_per_scan": 1,
309            "rows_produced_per_join": 256,
310            "filtered": "100.00",
311            "cost_info": {
312              "read_cost": "256.00",
313              "eval_cost": "51.20",
314              "prefix_cost": "365.40",
315              "data_read_per_join": "4K"
316            },
317            "used_columns": [
318              "d",
319              "e"
320            ]
321          }
322        }
323      ]
324    }
325  }
326}
327Warnings:
328Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t2`.`e` AS `e` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`d` = `test`.`t1`.`b`) order by `test`.`t1`.`a` limit 4
329UPDATE mysql.server_cost
330SET cost_value=0.2
331WHERE cost_name="row_evaluate_cost";
332UPDATE mysql.server_cost
333SET cost_value=0.1
334WHERE cost_name="key_compare_cost";
335UPDATE mysql.server_cost
336SET cost_value=2.0
337WHERE cost_name="memory_temptable_create_cost";
338UPDATE mysql.server_cost
339SET cost_value=0.2
340WHERE cost_name="memory_temptable_row_cost";
341UPDATE mysql.server_cost
342SET cost_value=40
343WHERE cost_name="disk_temptable_create_cost";
344UPDATE mysql.server_cost
345SET cost_value=1.0
346WHERE cost_name="disk_temptable_row_cost";
347UPDATE mysql.engine_cost
348SET cost_value=1.0
349WHERE cost_name="memory_block_read_cost";
350UPDATE mysql.engine_cost
351SET cost_value=1.0
352WHERE cost_name="io_block_read_cost";
353UPDATE mysql.server_cost
354SET cost_value = 0.5 * cost_value;
355UPDATE mysql.engine_cost
356SET cost_value = 0.5 * cost_value;
357SELECT cost_name, cost_value FROM mysql.server_cost;
358cost_name	cost_value
359disk_temptable_create_cost	20
360disk_temptable_row_cost	0.5
361key_compare_cost	0.05
362memory_temptable_create_cost	1
363memory_temptable_row_cost	0.1
364row_evaluate_cost	0.1
365SELECT cost_name, cost_value FROM mysql.engine_cost;
366cost_name	cost_value
367io_block_read_cost	0.5
368memory_block_read_cost	0.5
369FLUSH OPTIMIZER_COSTS;
370# This should be optimized for the LIMIT. Query plan should
371# use index without filesort
372EXPLAIN FORMAT=JSON SELECT * FROM t1 JOIN t2 ON b=d ORDER BY a LIMIT 4;
373EXPLAIN
374{
375  "query_block": {
376    "select_id": 1,
377    "cost_info": {
378      "query_cost": "182.70"
379    },
380    "ordering_operation": {
381      "using_filesort": false,
382      "nested_loop": [
383        {
384          "table": {
385            "table_name": "t1",
386            "access_type": "index",
387            "key": "k1",
388            "used_key_parts": [
389              "a"
390            ],
391            "key_length": "5",
392            "rows_examined_per_scan": 4,
393            "rows_produced_per_join": 256,
394            "filtered": "100.00",
395            "cost_info": {
396              "read_cost": "3.50",
397              "eval_cost": "25.60",
398              "prefix_cost": "29.10",
399              "data_read_per_join": "68K"
400            },
401            "used_columns": [
402              "pk",
403              "a",
404              "b",
405              "c"
406            ],
407            "attached_condition": "(`test`.`t1`.`b` is not null)"
408          }
409        },
410        {
411          "table": {
412            "table_name": "t2",
413            "access_type": "eq_ref",
414            "possible_keys": [
415              "PRIMARY"
416            ],
417            "key": "PRIMARY",
418            "used_key_parts": [
419              "d"
420            ],
421            "key_length": "4",
422            "ref": [
423              "test.t1.b"
424            ],
425            "rows_examined_per_scan": 1,
426            "rows_produced_per_join": 256,
427            "filtered": "100.00",
428            "cost_info": {
429              "read_cost": "128.00",
430              "eval_cost": "25.60",
431              "prefix_cost": "182.70",
432              "data_read_per_join": "4K"
433            },
434            "used_columns": [
435              "d",
436              "e"
437            ]
438          }
439        }
440      ]
441    }
442  }
443}
444Warnings:
445Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t2`.`e` AS `e` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`d` = `test`.`t1`.`b`) order by `test`.`t1`.`a` limit 4
446UPDATE mysql.server_cost
447SET cost_value=DEFAULT;
448UPDATE mysql.engine_cost
449SET cost_value=DEFAULT;
450FLUSH OPTIMIZER_COSTS;
451DROP TABLE t1, t2;
452