1--- innodb_ext_key.result 2+++ innodb_ext_key,off.result 3@@ -9,7 +9,7 @@ 4 explain 5 select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; 6 id select_type table type possible_keys key key_len ref rows Extra 7-1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 const,const 1 Using index 8+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 const 6 Using where; Using index 9 flush status; 10 select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01'; 11 count(*) 12@@ -19,7 +19,7 @@ 13 Handler_read_first 0 14 Handler_read_key 1 15 Handler_read_last 0 16-Handler_read_next 1 17+Handler_read_next 6 18 Handler_read_prev 0 19 Handler_read_retry 0 20 Handler_read_rnd 0 21@@ -50,7 +50,7 @@ 22 select count(*) from lineitem 23 where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000; 24 id select_type table type possible_keys key key_len ref rows Extra 25-1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 NULL 1 Using where; Using index 26+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 const 6 Using where; Using index 27 flush status; 28 select count(*) from lineitem 29 where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000; 30@@ -61,7 +61,7 @@ 31 Handler_read_first 0 32 Handler_read_key 1 33 Handler_read_last 0 34-Handler_read_next 1 35+Handler_read_next 6 36 Handler_read_prev 0 37 Handler_read_retry 0 38 Handler_read_rnd 0 39@@ -71,7 +71,7 @@ 40 select l_orderkey, l_linenumber from lineitem 41 where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; 42 id select_type table type possible_keys key key_len ref rows Extra 43-1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 8 NULL 3 Using where; Using index 44+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 const 6 Using where; Using index 45 flush status; 46 select l_orderkey, l_linenumber from lineitem 47 where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; 48@@ -84,7 +84,7 @@ 49 Handler_read_first 0 50 Handler_read_key 1 51 Handler_read_last 0 52-Handler_read_next 3 53+Handler_read_next 6 54 Handler_read_prev 0 55 Handler_read_retry 0 56 Handler_read_rnd 0 57@@ -93,7 +93,7 @@ 58 explain 59 select min(l_orderkey) from lineitem where l_shipdate='1992-07-01'; 60 id select_type table type possible_keys key key_len ref rows Extra 61-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 62+1 SIMPLE lineitem ref i_l_shipdate i_l_shipdate 4 const 6 Using index 63 flush status; 64 select min(l_orderkey) from lineitem where l_shipdate='1992-07-01'; 65 min(l_orderkey) 66@@ -103,7 +103,7 @@ 67 Handler_read_first 0 68 Handler_read_key 1 69 Handler_read_last 0 70-Handler_read_next 0 71+Handler_read_next 6 72 Handler_read_prev 0 73 Handler_read_retry 0 74 Handler_read_rnd 0 75@@ -113,7 +113,7 @@ 76 select min(l_orderkey) from lineitem 77 where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; 78 id select_type table type possible_keys key key_len ref rows Extra 79-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 80+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 const 6 Using where; Using index 81 flush status; 82 select min(l_orderkey) from lineitem 83 where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000; 84@@ -124,7 +124,7 @@ 85 Handler_read_first 0 86 Handler_read_key 1 87 Handler_read_last 0 88-Handler_read_next 0 89+Handler_read_next 6 90 Handler_read_prev 0 91 Handler_read_retry 0 92 Handler_read_rnd 0 93@@ -134,7 +134,7 @@ 94 select max(l_linenumber) from lineitem 95 where l_shipdate='1992-07-01' and l_orderkey=130; 96 id select_type table type possible_keys key key_len ref rows Extra 97-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 98+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 const 6 Using where; Using index 99 flush status; 100 select max(l_linenumber) from lineitem 101 where l_shipdate='1992-07-01' and l_orderkey=130; 102@@ -145,7 +145,7 @@ 103 Handler_read_first 0 104 Handler_read_key 1 105 Handler_read_last 0 106-Handler_read_next 0 107+Handler_read_next 6 108 Handler_read_prev 0 109 Handler_read_retry 0 110 Handler_read_rnd 0 111@@ -157,7 +157,7 @@ 112 where l_shipdate='1992-07-01' and l_orderkey=130 113 or l_receiptdate='1992-07-01' and l_orderkey=5603; 114 id select_type table type possible_keys key key_len ref rows Extra 115-1 SIMPLE lineitem index_merge i_l_shipdate,i_l_receiptdate i_l_shipdate,i_l_receiptdate 8,8 NULL 2 Using union(i_l_shipdate,i_l_receiptdate); Using where 116+1 SIMPLE lineitem index_merge i_l_shipdate,i_l_receiptdate i_l_shipdate,i_l_receiptdate 4,4 NULL 9 Using union(i_l_shipdate,i_l_receiptdate); Using where 117 flush status; 118 select l_orderkey, l_linenumber 119 from lineitem use index (i_l_shipdate, i_l_receiptdate) 120@@ -171,10 +171,10 @@ 121 Handler_read_first 0 122 Handler_read_key 2 123 Handler_read_last 0 124-Handler_read_next 2 125+Handler_read_next 9 126 Handler_read_prev 0 127 Handler_read_retry 0 128-Handler_read_rnd 2 129+Handler_read_rnd 9 130 Handler_read_rnd_deleted 0 131 Handler_read_rnd_next 0 132 explain 133@@ -183,7 +183,7 @@ 134 where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 135 or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000; 136 id select_type table type possible_keys key key_len ref rows Extra 137-1 SIMPLE lineitem index_merge i_l_shipdate,i_l_receiptdate i_l_shipdate,i_l_receiptdate # NULL 3 Using sort_union(i_l_shipdate,i_l_receiptdate); Using where 138+1 SIMPLE lineitem index_merge i_l_shipdate,i_l_receiptdate i_l_shipdate,i_l_receiptdate # NULL 9 Using union(i_l_shipdate,i_l_receiptdate); Using where 139 flush status; 140 select l_orderkey, l_linenumber 141 from lineitem use index (i_l_shipdate, i_l_receiptdate) 142@@ -198,10 +198,10 @@ 143 Handler_read_first 0 144 Handler_read_key 2 145 Handler_read_last 0 146-Handler_read_next 3 147+Handler_read_next 9 148 Handler_read_prev 0 149 Handler_read_retry 0 150-Handler_read_rnd 3 151+Handler_read_rnd 9 152 Handler_read_rnd_deleted 0 153 Handler_read_rnd_next 0 154 explain 155@@ -209,7 +209,7 @@ 156 where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 157 or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000; 158 id select_type table type possible_keys key key_len ref rows Extra 159-1 SIMPLE lineitem index_merge PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate,i_l_receiptdate # NULL # Using 160+1 SIMPLE lineitem index_merge PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate,PRIMARY,i_l_receiptdate,PRIMARY # NULL # Using 161 flush status; 162 select l_orderkey, l_linenumber from lineitem 163 where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000 164@@ -220,12 +220,12 @@ 165 5959 3 166 show status like 'handler_read_next'; 167 Variable_name Value 168-Handler_read_next 3 169+Handler_read_next 9 170 explain 171 select max(l_orderkey) from lineitem 172 where l_partkey between 1 and 10 group by l_partkey; 173 id select_type table type possible_keys key key_len ref rows Extra 174-1 SIMPLE lineitem range i_l_suppkey_partkey,i_l_partkey i_l_partkey 5 NULL # Using where; Using index for group-by 175+1 SIMPLE lineitem range i_l_suppkey_partkey,i_l_partkey i_l_partkey 5 NULL # Using where; Using index 176 flush status; 177 select max(l_orderkey) from lineitem 178 where l_partkey between 1 and 10 group by l_partkey; 179@@ -243,9 +243,9 @@ 180 show status like 'handler_read%'; 181 Variable_name Value 182 Handler_read_first 0 183-Handler_read_key 21 184-Handler_read_last 1 185-Handler_read_next 0 186+Handler_read_key 1 187+Handler_read_last 0 188+Handler_read_next 294 189 Handler_read_prev 0 190 Handler_read_retry 0 191 Handler_read_rnd 0 192@@ -255,7 +255,7 @@ 193 select max(l_orderkey) from lineitem 194 where l_suppkey in (1,4) group by l_suppkey; 195 id select_type table type possible_keys key key_len ref rows Extra 196-1 SIMPLE lineitem range i_l_suppkey i_l_suppkey 5 NULL # Using where; Using index for group-by 197+1 SIMPLE lineitem range i_l_suppkey i_l_suppkey 5 NULL # Using where; Using index 198 flush status; 199 select max(l_orderkey) from lineitem 200 where l_suppkey in (1,4) group by l_suppkey; 201@@ -265,9 +265,9 @@ 202 show status like 'handler_read%'; 203 Variable_name Value 204 Handler_read_first 0 205-Handler_read_key 6 206-Handler_read_last 1 207-Handler_read_next 0 208+Handler_read_key 2 209+Handler_read_last 0 210+Handler_read_next 1230 211 Handler_read_prev 0 212 Handler_read_retry 0 213 Handler_read_rnd 0 214@@ -283,7 +283,7 @@ 215 id select_type table type possible_keys key key_len ref rows Extra 216 1 SIMPLE part range i_p_retailprice i_p_retailprice 9 NULL # Using where; Using index 217 1 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const # Using index 218-1 SIMPLE lineitem ref i_l_partkey i_l_partkey 9 dbt3_s001.part.p_partkey,dbt3_s001.orders.o_orderkey # Using index 219+1 SIMPLE lineitem ref i_l_partkey i_l_partkey 5 dbt3_s001.part.p_partkey # Using where; Using index 220 flush status; 221 select o_orderkey, p_partkey 222 from part use index (i_p_retailprice), 223@@ -297,7 +297,7 @@ 224 Handler_read_first 0 225 Handler_read_key 3 226 Handler_read_last 0 227-Handler_read_next 3 228+Handler_read_next 26 229 Handler_read_prev 0 230 Handler_read_retry 0 231 Handler_read_rnd 0 232@@ -314,8 +314,8 @@ 233 select * from t0, part ignore index (primary) 234 where p_partkey=t0.a and p_size=1; 235 id select_type table type possible_keys key key_len ref rows Extra 236-1 SIMPLE t0 ALL NULL NULL NULL NULL 5 Using where 237-1 SIMPLE part eq_ref i_p_size i_p_size 9 const,dbt3_s001.t0.a 1 238+1 SIMPLE t0 ALL NULL NULL NULL NULL 5 239+1 SIMPLE part ref i_p_size i_p_size 5 const 5 Using index condition 240 select * from t0, part ignore index (primary) 241 where p_partkey=t0.a and p_size=1; 242 a p_partkey p_name p_mfgr p_brand p_type p_size p_container p_retailprice p_comment 243@@ -494,7 +494,7 @@ 244 select * from t1, t3 where t3.col1=t1.a and t3.col2=t1.a and t3.pk1=t1.a; 245 id select_type table type possible_keys key key_len ref rows Extra 246 1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where 247-1 SIMPLE t3 ref PRIMARY,col1 col1 12 test.t1.a,test.t1.a,test.t1.a # Using index 248+1 SIMPLE t3 ref PRIMARY,col1 col1 8 test.t1.a,test.t1.a # Using where; Using index 249 drop table t1,t2,t3; 250 # 251 # Bug mdev-4340: performance regression with extended_keys=on 252@@ -714,13 +714,13 @@ 253 select * from t1 force index(index_date_updated) 254 where index_date_updated= 10 and index_id < 800; 255 id select_type table type possible_keys key key_len ref rows Extra 256-1 SIMPLE t1 range index_date_updated index_date_updated 13 NULL # Using index condition 257+1 SIMPLE t1 ref index_date_updated index_date_updated 5 const # Using index condition 258 # This used to work from the start: 259 explain 260 select * from t2 force index(index_date_updated) 261 where index_date_updated= 10 and index_id < 800; 262 id select_type table type possible_keys key key_len ref rows Extra 263-1 SIMPLE t2 range index_date_updated index_date_updated 13 NULL # Using index condition 264+1 SIMPLE t2 ref index_date_updated index_date_updated 5 const # Using index condition 265 drop table t0,t1,t2; 266 # 267 # MDEV-11196: Error:Run-Time Check Failure #2 - Stack around the variable 'key_buff' 268@@ -755,11 +755,12 @@ 269 "select_id": 1, 270 "table": { 271 "table_name": "t1", 272- "access_type": "range", 273+ "access_type": "ref", 274 "possible_keys": ["f2"], 275 "key": "f2", 276- "key_length": "3070", 277- "used_key_parts": ["f2", "pk1"], 278+ "key_length": "3066", 279+ "used_key_parts": ["f2"], 280+ "ref": ["const"], 281 "rows": 1, 282 "filtered": 100, 283 "index_condition": "t1.pk1 <= 5 and t1.pk2 <= 5 and t1.f2 = 'abc'", 284@@ -788,8 +789,8 @@ 285 "access_type": "range", 286 "possible_keys": ["k1"], 287 "key": "k1", 288- "key_length": "3011", 289- "used_key_parts": ["pk1", "f2", "pk2"], 290+ "key_length": "3007", 291+ "used_key_parts": ["pk1", "f2"], 292 "rows": 1, 293 "filtered": 100, 294 "index_condition": "t1.f2 <= 5 and t1.pk2 <= 5 and t1.pk1 = 'abc'", 295