[Commits] 3ed3cda97dc: Update test results (2)
revision-id: 3ed3cda97dce533c0511ff81389d05f3792946e8 (mariadb-10.6.1-366-g3ed3cda97dc) parent(s): fe35806da7f279b9438d03f733a2c68db3a0a679 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-03-19 20:16:09 +0300 message: Update test results (2) --- mysql-test/main/mysql_client_test.result | 20 +- mysql-test/main/outfile.result | Bin 2339 -> 2353 bytes mysql-test/main/selectivity_innodb.result | 36 +-- .../engines/iuds/r/strings_update_delete.result | 4 +- mysql-test/suite/funcs_1/r/myisam_views-big.result | 4 +- .../suite/gcol/r/innodb_virtual_basic.result | 4 +- mysql-test/suite/innodb/r/full_crc32_import.result | 4 +- .../suite/innodb/r/innodb_bulk_create_index.result | 48 ++-- mysql-test/suite/innodb/r/innodb_mysql.result | 244 +++++++++++---------- .../rocksdb/mysql-test/rocksdb/r/rocksdb.result | 186 ++++++++-------- tests/mysql_client_test.c | 34 ++- 11 files changed, 313 insertions(+), 271 deletions(-) diff --git a/mysql-test/main/mysql_client_test.result b/mysql-test/main/mysql_client_test.result index 72c703ba9a8..0f5c6eec5e2 100644 --- a/mysql-test/main/mysql_client_test.result +++ b/mysql-test/main/mysql_client_test.result @@ -137,7 +137,7 @@ def MAX(a) MAX(a) 3 11 0 Y 32768 0 63 # cat MYSQL_TMP_DIR/test_explain_meta.out.log # ------------------------------------ SELECT number of fields: 1 -EXPALIN number of fields: 11 +EXPALIN number of fields: 12 - 0: name: 'id'/''; table: ''/''; db: ''; catalog: 'def'; length: 3; max_length: 0; type: 8; decimals: 0 - 1: name: 'select_type'/''; table: ''/''; db: ''; catalog: 'def'; length: 57; max_length: 0; type: 253; decimals: 39 - 2: name: 'table'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 @@ -148,7 +148,8 @@ EXPALIN number of fields: 11 - 7: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 8: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - 9: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - - 10: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 + - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 + - 11: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 EXPALIN JSON number of fields: 1 - 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39 ANALYZE number of fields: 14 @@ -168,7 +169,7 @@ ANALYZE number of fields: 14 - 13: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 ANALYZE JSON number of fields: 1 - 0: name: 'ANALYZE'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39 -EXPALIN INSERT number of fields: 11 +EXPALIN INSERT number of fields: 12 - 0: name: 'id'/''; table: ''/''; db: ''; catalog: 'def'; length: 3; max_length: 0; type: 8; decimals: 0 - 1: name: 'select_type'/''; table: ''/''; db: ''; catalog: 'def'; length: 57; max_length: 0; type: 253; decimals: 39 - 2: name: 'table'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 @@ -179,7 +180,8 @@ EXPALIN INSERT number of fields: 11 - 7: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 8: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - 9: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - - 10: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 + - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 + - 11: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 EXPALIN JSON INSERT number of fields: 1 - 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39 ANALYZE INSERT number of fields: 14 @@ -199,7 +201,7 @@ ANALYZE INSERT number of fields: 14 - 13: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 ANALYZE JSON INSERT number of fields: 1 - 0: name: 'ANALYZE'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39 -EXPALIN UPDATE number of fields: 11 +EXPALIN UPDATE number of fields: 12 - 0: name: 'id'/''; table: ''/''; db: ''; catalog: 'def'; length: 3; max_length: 0; type: 8; decimals: 0 - 1: name: 'select_type'/''; table: ''/''; db: ''; catalog: 'def'; length: 57; max_length: 0; type: 253; decimals: 39 - 2: name: 'table'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 @@ -210,7 +212,8 @@ EXPALIN UPDATE number of fields: 11 - 7: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 8: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - 9: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - - 10: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 + - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 + - 11: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 EXPALIN JSON UPDATE number of fields: 1 - 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39 ANALYZE UPDATE number of fields: 14 @@ -230,7 +233,7 @@ ANALYZE UPDATE number of fields: 14 - 13: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 ANALYZE JSON UPDATE number of fields: 1 - 0: name: 'ANALYZE'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39 -EXPALIN DELETE number of fields: 11 +EXPALIN DELETE number of fields: 12 - 0: name: 'id'/''; table: ''/''; db: ''; catalog: 'def'; length: 3; max_length: 0; type: 8; decimals: 0 - 1: name: 'select_type'/''; table: ''/''; db: ''; catalog: 'def'; length: 57; max_length: 0; type: 253; decimals: 39 - 2: name: 'table'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 @@ -241,7 +244,8 @@ EXPALIN DELETE number of fields: 11 - 7: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 8: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - 9: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - - 10: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 + - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 + - 11: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 EXPALIN JSON DELETE number of fields: 1 - 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39 ANALYZE DELETE number of fields: 14 diff --git a/mysql-test/main/outfile.result b/mysql-test/main/outfile.result index 0fa58ea0a2a..afbf8915164 100644 Binary files a/mysql-test/main/outfile.result and b/mysql-test/main/outfile.result differ diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index b344b91aa2e..ab81abd3b66 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1718,8 +1718,8 @@ CREATE INDEX CountryName ON City(Country,Name); set @@optimizer_use_condition_selectivity=2; EXPLAIN SELECT * FROM City WHERE Country='FIN'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE City NULL ref Country,CountryPopulation,CountryName Country 3 const 7 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE City NULL ref Country,CountryPopulation,CountryName Country 3 const 7 100.00 Using index condition DROP DATABASE world; use test; CREATE TABLE t1 ( @@ -1750,8 +1750,8 @@ INSERT INTO t1 SELECT a + 640, b, c FROM t1; INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; EXPLAIN SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range b,b_2 b 4 NULL 226 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range b,b_2 b 4 NULL 226 98.23 Using where SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; a 2071 @@ -1859,22 +1859,22 @@ explain SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id WHERE A.a=t1.a AND t2.b < 20); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 PRIMARY t1 NULL index NULL a 5 NULL 100 Using where; Using index -2 DEPENDENT SUBQUERY A NULL ref PRIMARY,a a 5 test.t1.a 1 Using index -2 DEPENDENT SUBQUERY t2 NULL ref|filter a,b a|b 5|5 test.A.id 1 (19%) Using where; Using rowid filter +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 NULL index NULL a 5 NULL 100 100.00 Using where; Using index +2 DEPENDENT SUBQUERY A NULL ref PRIMARY,a a 5 test.t1.a 1 100.00 Using index +2 DEPENDENT SUBQUERY t2 NULL ref|filter a,b a|b 5|5 test.A.id 1 (19%) 19.00 Using where; Using rowid filter EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE A NULL const PRIMARY,a PRIMARY 4 const 1 -1 SIMPLE B NULL ref a a 5 const 1 Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE A NULL const PRIMARY,a PRIMARY 4 const 1 100.00 +1 SIMPLE B NULL ref a a 5 const 1 100.00 Using index explain SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id WHERE A.a=t1.a AND t2.b < 20); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 PRIMARY t1 NULL index NULL a 5 NULL 100 Using where; Using index -2 DEPENDENT SUBQUERY A NULL ref PRIMARY,a a 5 test.t1.a 1 Using index -2 DEPENDENT SUBQUERY t2 NULL ref|filter a,b a|b 5|5 test.A.id 1 (19%) Using where; Using rowid filter +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 NULL index NULL a 5 NULL 100 100.00 Using where; Using index +2 DEPENDENT SUBQUERY A NULL ref PRIMARY,a a 5 test.t1.a 1 100.00 Using index +2 DEPENDENT SUBQUERY t2 NULL ref|filter a,b a|b 5|5 test.A.id 1 (19%) 19.00 Using where; Using rowid filter set optimizer_switch= @save_optimizer_switch; set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; drop table t1,t2; @@ -2143,9 +2143,9 @@ TEXT41 TEXT,TEXT42 TEXT,TEXT43 TEXT,TEXT44 TEXT,TEXT45 TEXT, TEXT46 TEXT,TEXT47 TEXT,TEXT48 TEXT,TEXT49 TEXT,TEXT50 TEXT ) ENGINE=InnoDB; EXPLAIN SELECT 1 FROM t1 NATURAL JOIN t1 AS t2; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 Using where -1 SIMPLE t2 NULL hash_ALL NULL #hash#$hj 150 test.t1.TEXT1,test.t1.TEXT2,test.t1.TEXT3,test.t1.TEXT4,test.t1.TEXT5,test.t1.TEXT6,test.t1.TEXT7,test.t1.TEXT8,test.t1.TEXT9,test.t1.TEXT10,test.t1.TEXT11,test.t1.TEXT12,test.t1.TEXT13,test.t1.TEXT14,test.t1.TEXT15,test.t1.TEXT16,test.t1.TEXT17,test.t1.TEXT18,test.t1.TEXT19,test.t1.TEXT20,test.t1.TEXT21,test.t1.TEXT22,test.t1.TEXT23,test.t1.TEXT24,test.t1.TEXT25,test.t1.TEXT26,test.t1.TEXT27,test.t1.TEXT28,test.t1.TEXT29,test.t1.TEXT30,test.t1.TEXT31,test.t1.TEXT32,test.t1.TEXT33,test.t1.TEXT34,test.t1.TEXT35,test.t1.TEXT36,test.t1.TEXT37,test.t1.TEXT38,test.t1.TEXT39,test.t1.TEXT40,test.t1.TEXT41,test.t1.TEXT42,test.t1.TEXT43,test.t1.TEXT44,test.t1.TEXT45,test.t1.TEXT46,test.t1.TEXT47,test.t1.TEXT48,test.t1.TEXT49,test.t1.TEXT50 1 Using where; Using join buffer (flat, BNLH join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where +1 SIMPLE t2 NULL hash_ALL NULL #hash#$hj 150 test.t1.TEXT1,test.t1.TEXT2,test.t1.TEXT3,test.t1.TEXT4,test.t1.TEXT5,test.t1.TEXT6,test.t1.TEXT7,test.t1.TEXT8,test.t1.TEXT9,test.t1.TEXT10,test.t1.TEXT11,test.t1.TEXT12,test.t1.TEXT13,test.t1.TEXT14,test.t1.TEXT15,test.t1.TEXT16,test.t1.TEXT17,test.t1.TEXT18,test.t1.TEXT19,test.t1.TEXT20,test.t1.TEXT21,test.t1.TEXT22,test.t1.TEXT23,test.t1.TEXT24,test.t1.TEXT25,test.t1.TEXT26,test.t1.TEXT27,test.t1.TEXT28,test.t1.TEXT29,test.t1.TEXT30,test.t1.TEXT31,test.t1.TEXT32,test.t1.TEXT33,test.t1.TEXT34,test.t1.TEXT35,test.t1.TEXT36,test.t1.TEXT37,test.t1.TEXT38,test.t1.TEXT39,test.t1.TEXT40,test.t1.TEXT41,test.t1.TEXT42,test.t1.TEXT43,test.t1.TEXT44,test.t1.TEXT45,test.t1.TEXT46,test.t1.TEXT47,test.t1.TEXT48,test.t1.TEXT49,test.t1.TEXT50 1 100.00 Using where; Using join buffer (flat, BNLH join) set join_cache_level=@tmp_jcl; drop table t1; # diff --git a/mysql-test/suite/engines/iuds/r/strings_update_delete.result b/mysql-test/suite/engines/iuds/r/strings_update_delete.result index 8004259128c..80374e9bd83 100644 --- a/mysql-test/suite/engines/iuds/r/strings_update_delete.result +++ b/mysql-test/suite/engines/iuds/r/strings_update_delete.result @@ -70461,8 +70461,8 @@ SELECT * FROM t18 WHERE c2=0; c1 c2 SIZE EXPLAIN SELECT * FROM t18 WHERE c2=0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t18 NULL ALL NULL NULL NULL NULL 2 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t18 NULL ALL NULL NULL NULL NULL 2 100.00 Using where SET sql_mode= 'STRICT_ALL_TABLES'; INSERT INTO t18 VALUES('SIZE','SMALL2'); ERROR 01000: Data truncated for column 'c2' at row 1 diff --git a/mysql-test/suite/funcs_1/r/myisam_views-big.result b/mysql-test/suite/funcs_1/r/myisam_views-big.result index efd5ee1c568..d6245911861 100644 --- a/mysql-test/suite/funcs_1/r/myisam_views-big.result +++ b/mysql-test/suite/funcs_1/r/myisam_views-big.result @@ -23105,8 +23105,8 @@ SELECT * FROM test3.v32; f1 1.000 EXPLAIN SELECT * FROM test3.v32; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system NULL NULL NULL NULL 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL system NULL NULL NULL NULL 1 100.00 DROP VIEW test3.v0; SHOW CREATE VIEW test3.v32; View Create View character_set_client collation_connection diff --git a/mysql-test/suite/gcol/r/innodb_virtual_basic.result b/mysql-test/suite/gcol/r/innodb_virtual_basic.result index 31a3c12510d..fdb9df3fd85 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_basic.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_basic.result @@ -362,8 +362,8 @@ Warnings: Note 1265 Data truncated for column 'col_time_key' at row 1 Note 1265 Data truncated for column 'col_time_key' at row 2 EXPLAIN SELECT col_INT_key FROM b; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE b NULL index NULL col_INT_key 5 NULL 2 Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE b NULL index NULL col_INT_key 5 NULL 2 100.00 Using index SELECT col_INT_key FROM b; col_INT_key 0 diff --git a/mysql-test/suite/innodb/r/full_crc32_import.result b/mysql-test/suite/innodb/r/full_crc32_import.result index c9ca2f9d018..46cfc7c9997 100644 --- a/mysql-test/suite/innodb/r/full_crc32_import.result +++ b/mysql-test/suite/innodb/r/full_crc32_import.result @@ -49,8 +49,8 @@ t1 CREATE TABLE `t1` ( ) ENGINE=InnoDB AUTO_INCREMENT=57 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC UPDATE t1 set b = repeat("de", 100) where b = repeat("cd", 200); explain SELECT a FROM t1 where b = repeat("de", 100); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 Using where SELECT a FROM t1 where b = repeat("de", 100); a 3 diff --git a/mysql-test/suite/innodb/r/innodb_bulk_create_index.result b/mysql-test/suite/innodb/r/innodb_bulk_create_index.result index e8dc04a613d..014f1d3f21e 100644 --- a/mysql-test/suite/innodb/r/innodb_bulk_create_index.result +++ b/mysql-test/suite/innodb/r/innodb_bulk_create_index.result @@ -33,11 +33,11 @@ Table Op Msg_type Msg_text test.t1 check status OK /* Select by index. */ EXPLAIN SELECT * FROM t1 WHERE id = 10; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 100.00 EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 100.00 Using index condition SELECT * FROM t1 WHERE id = 10; class id title 10 10 a10 @@ -205,11 +205,11 @@ Table Op Msg_type Msg_text test.t1 check status OK /* Select by index. */ EXPLAIN SELECT * FROM t1 WHERE id = 10; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 100.00 EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 100.00 Using index condition SELECT * FROM t1 WHERE id = 10; class id title 10 10 a10 @@ -377,11 +377,11 @@ Table Op Msg_type Msg_text test.t1 check status OK /* Select by index. */ EXPLAIN SELECT * FROM t1 WHERE id = 10; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 100.00 EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 100.00 Using index condition SELECT * FROM t1 WHERE id = 10; class id title 10 10 a10 @@ -550,11 +550,11 @@ Table Op Msg_type Msg_text test.t1 check status OK /* Select by index. */ EXPLAIN SELECT * FROM t1 WHERE id = 10; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 100.00 EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 100.00 Using index condition SELECT * FROM t1 WHERE id = 10; class id title 10 10 a10 @@ -724,11 +724,11 @@ Table Op Msg_type Msg_text test.t1 check status OK /* Select by index. */ EXPLAIN SELECT * FROM t1 WHERE id = 10; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 100.00 EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 100.00 Using index condition SELECT * FROM t1 WHERE id = 10; class id title 10 10 a10 @@ -897,11 +897,11 @@ Table Op Msg_type Msg_text test.t1 check status OK /* Select by index. */ EXPLAIN SELECT * FROM t1 WHERE id = 10; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 100.00 EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref idx_title idx_title 103 const 1 100.00 Using index condition SELECT * FROM t1 WHERE id = 10; class id title 10 10 a10 diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index d081dc41f18..7a2dfa7eec8 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -166,13 +166,13 @@ INDEX idx (a6,a7(239),a5) EXPLAIN SELECT a4 FROM t1 WHERE a6=NULL AND a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE t.a6=t.a6 AND t1.a6=NULL AND t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1; create table t1m (a int) engine = MEMORY; create table t1i (a int); @@ -190,9 +190,9 @@ select min(7) from DUAL; min(7) 7 explain select min(7) from t2i join t1i; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1i NULL ALL NULL NULL NULL NULL 0 -1 SIMPLE t2i NULL ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1i NULL ALL NULL NULL NULL NULL 0 0.00 +1 SIMPLE t2i NULL ALL NULL NULL NULL NULL 1 100.00 Using join buffer (flat, BNL join) select min(7) from t2i join t1i; min(7) NULL @@ -206,9 +206,9 @@ select max(7) from DUAL; max(7) 7 explain select max(7) from t2i join t1i; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1i NULL ALL NULL NULL NULL NULL 0 -1 SIMPLE t2i NULL ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1i NULL ALL NULL NULL NULL NULL 0 0.00 +1 SIMPLE t2i NULL ALL NULL NULL NULL NULL 1 100.00 Using join buffer (flat, BNL join) select max(7) from t2i join t1i; max(7) NULL @@ -237,23 +237,23 @@ select 1, max(1) from t1i where 1=99; 1 max(1) 1 NULL explain select count(*), min(7), max(7) from t1m, t1i; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1m NULL system NULL NULL NULL NULL 0 Const row not found -1 SIMPLE t1i NULL ALL NULL NULL NULL NULL 0 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1m NULL system NULL NULL NULL NULL 0 0.00 Const row not found +1 SIMPLE t1i NULL ALL NULL NULL NULL NULL 0 0.00 select count(*), min(7), max(7) from t1m, t1i; count(*) min(7) max(7) 0 NULL NULL explain select count(*), min(7), max(7) from t1m, t2i; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1m NULL system NULL NULL NULL NULL 0 Const row not found -1 SIMPLE t2i NULL ALL NULL NULL NULL NULL 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1m NULL system NULL NULL NULL NULL 0 0.00 Const row not found +1 SIMPLE t2i NULL ALL NULL NULL NULL NULL 1 100.00 select count(*), min(7), max(7) from t1m, t2i; count(*) min(7) max(7) 0 NULL NULL explain select count(*), min(7), max(7) from t2m, t1i; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2m NULL system NULL NULL NULL NULL 1 -1 SIMPLE t1i NULL ALL NULL NULL NULL NULL 0 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2m NULL system NULL NULL NULL NULL 1 100.00 +1 SIMPLE t1i NULL ALL NULL NULL NULL NULL 0 0.00 select count(*), min(7), max(7) from t2m, t1i; count(*) min(7) max(7) 0 NULL NULL @@ -345,11 +345,11 @@ create table t1 (f1 int, f2 char(1), primary key(f1,f2)) stats_persistent=0; insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d"); alter table t1 drop primary key, add primary key (f2, f1); explain select distinct f1 a, f1 b from t1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 5 NULL 4 Using index; Using temporary +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 5 NULL 4 100.00 Using index; Using temporary explain select distinct f1, f2 from t1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 5 NULL 4 Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 5 NULL 4 100.00 Using index drop table t1; CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), INDEX (name)); @@ -360,15 +360,15 @@ INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range PRIMARY,name name 23 NULL 2 Using where; Using index -1 SIMPLE t2 NULL ref fkey fkey 5 test.t1.id 1 Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range PRIMARY,name name 23 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 NULL ref fkey fkey 5 test.t1.id 1 100.00 Using index EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%' OR FALSE; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range PRIMARY,name name 23 NULL 2 Using where; Using index -1 SIMPLE t2 NULL ref fkey fkey 5 test.t1.id 1 Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range PRIMARY,name name 23 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 NULL ref fkey fkey 5 test.t1.id 1 100.00 Using index DROP TABLE t1,t2; CREATE TABLE t1 ( id int NOT NULL, @@ -395,8 +395,8 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK flush tables; EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref name name 22 const 2 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref name name 22 const 2 100.00 Using where; Using index SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; name dept rs5 cs10 @@ -404,8 +404,8 @@ rs5 cs9 DELETE FROM t1; # Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746). EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref name name 22 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref name name 22 const # 100.00 Using where; Using index SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; name dept DROP TABLE t1; @@ -986,8 +986,8 @@ SELECT COUNT(*) FROM t1 WHERE acct_id=785; COUNT(*) 8702 EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range idx1,idx2 idx1 9 NULL 2 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range idx1,idx2 idx1 9 NULL 2 100.00 Using where; Using index INSERT INTO t2 SELECT * FROM t1; OPTIMIZE TABLE t2; Table Op Msg_type Msg_text @@ -1101,8 +1101,8 @@ COUNT(*) EXPLAIN SELECT COUNT(*) FROM t1 WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL idx_b,idx_c NULL NULL NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL idx_b,idx_c NULL NULL NULL # 100.00 Using where SELECT COUNT(*) FROM t1 WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; COUNT(*) @@ -1110,8 +1110,8 @@ COUNT(*) EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c) WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index_merge idx_b,idx_c idx_c,idx_b 5,4 NULL # Using sort_union(idx_c,idx_b); Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index_merge idx_b,idx_c idx_c,idx_b 5,4 NULL # 100.00 Using sort_union(idx_c,idx_b); Using where SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c) WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; COUNT(*) @@ -1192,11 +1192,11 @@ INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1; INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1; INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1; EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL b 5 NULL 128 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL b 5 NULL 128 100.00 EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 100.00 Using filesort DROP TABLE t1; drop table if exists t1; show variables like 'innodb_rollback_on_timeout'; @@ -1290,6 +1290,7 @@ key bkey key_len 5 ref const rows 16 +filtered 100.00 Extra Using where; Using index SELECT * FROM t1 WHERE b=2 ORDER BY a; a b @@ -1320,6 +1321,7 @@ key bkey key_len 5 ref NULL rows 32 +filtered 100.00 Extra Using where; Using index; Using filesort SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; a b @@ -1366,6 +1368,7 @@ key bkey key_len 5 ref NULL rows 32 +filtered 100.00 Extra Using where; Using index SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a; a b @@ -1417,6 +1420,7 @@ key bkey key_len 5 ref const rows 16 +filtered 100.00 Extra Using where; Using index; Using filesort SELECT * FROM t2 WHERE b=1 ORDER BY a; a b c @@ -1447,6 +1451,7 @@ key bkey key_len 10 ref const,const rows 16 +filtered 100.00 Extra Using where; Using index SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a; a b c @@ -1477,6 +1482,7 @@ key bkey key_len 10 ref const,const rows 16 +filtered 100.00 Extra Using where; Using index SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a; a b c @@ -1507,6 +1513,7 @@ key bkey key_len 10 ref const,const rows 16 +filtered 100.00 Extra Using where; Using index SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a; a b c @@ -1603,40 +1610,40 @@ UNIQUE KEY (c,b) ) engine=innodb; INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4); EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using filesort SELECT c,b,d FROM t1 GROUP BY c,b,d; c b d 1 1 50 3 1 4 3 2 40 EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; c b d 1 1 50 3 1 4 3 2 40 EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using filesort SELECT c,b,d FROM t1 ORDER BY c,b,d; c b d 1 1 50 3 1 4 3 2 40 EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL c 8 NULL 3 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL c 8 NULL 3 100.00 SELECT c,b,d FROM t1 GROUP BY c,b; c b d 1 1 50 3 1 4 3 2 40 EXPLAIN SELECT c,b FROM t1 GROUP BY c,b; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL c 8 NULL 3 Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL c 8 NULL 3 100.00 Using index SELECT c,b FROM t1 GROUP BY c,b; c b 1 1 @@ -1656,6 +1663,7 @@ key b key_len 5 ref const rows 2 +filtered 100.00 Extra Using where; Using index SELECT * FROM t1 WHERE b=2 ORDER BY a ASC; a b @@ -1672,6 +1680,7 @@ key b key_len 5 ref const rows 2 +filtered 100.00 Extra Using where; Using index SELECT * FROM t1 WHERE b=2 ORDER BY a DESC; a b @@ -1688,6 +1697,7 @@ key b key_len 5 ref NULL rows 3 +filtered 100.00 Extra Using index SELECT * FROM t1 ORDER BY b ASC, a ASC; a b @@ -1705,6 +1715,7 @@ key b key_len 5 ref NULL rows 3 +filtered 100.00 Extra Using index SELECT * FROM t1 ORDER BY b DESC, a DESC; a b @@ -1722,6 +1733,7 @@ key b key_len 5 ref NULL rows 3 +filtered 100.00 Extra Using index; Using filesort SELECT * FROM t1 ORDER BY b ASC, a DESC; a b @@ -1739,6 +1751,7 @@ key b key_len 5 ref NULL rows 3 +filtered 100.00 Extra Using index; Using filesort SELECT * FROM t1 ORDER BY b DESC, a ASC; a b @@ -1799,8 +1812,8 @@ CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB; INSERT INTO t1 VALUES (191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2); EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL idx NULL NULL NULL 4 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL idx NULL NULL NULL 4 75.00 Using where; Using filesort SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; id type d 191 member 1 @@ -1815,8 +1828,8 @@ ENGINE=InnoDB; INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1); INSERT INTO t1 (a,b,c) SELECT a+4,b,c FROM t1; EXPLAIN SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range t1_b t1_b 5 NULL 8 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range t1_b t1_b 5 NULL 8 100.00 Using where SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; a b c 8 1 1 @@ -1876,10 +1889,10 @@ set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch='in_to_exists=on,materialization=off'; explain select b from t1 where a not in (select max(b) from t1,t2 group by a) group by a; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY t1 NULL system NULL NULL NULL NULL 0 Const row not found -2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t1 NULL system NULL NULL NULL NULL 0 0.00 Const row not found +2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 1 100.00 set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; End of 5.0 tests @@ -2177,8 +2190,8 @@ INSERT INTO t1 VALUES (4,1,3,'pk',NULL),(5,1,3,'c2',NULL), (2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL); EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 12 NULL 16 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 12 NULL 16 25.00 Using where SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC; vid tid idx name type 3 1 4 c_extra NULL @@ -2201,9 +2214,9 @@ FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; EXPLAIN SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 -2 DERIVED t1 NULL ref c3,c2 c3 5 const 2 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED t1 NULL ref c3,c2 c3 5 const 2 100.00 Using where; Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3)) ENGINE=InnoDB; @@ -2215,9 +2228,9 @@ FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; EXPLAIN SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 -2 DERIVED t1 NULL ref c3,c2 c3 9 const 2 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED t1 NULL ref c3,c2 c3 9 const 2 100.00 Using where; Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), KEY (c3), KEY (c2, c3)) @@ -2230,9 +2243,9 @@ FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; EXPLAIN SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 -2 DERIVED t1 NULL ref c3,c2 c3 7 const 2 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED t1 NULL ref c3,c2 c3 7 const 2 100.00 Using where; Using filesort DROP TABLE t1; End of 5.1 tests # @@ -2250,9 +2263,9 @@ KEY a (a,b) insert into t1 values (0),(1),(2),(3),(4); insert into t2 select A.a + 10 *B.a, 1, 'filler' from t1 A, t1 B; explain select * from t1, t2 where t2.a=t1.a and t2.b + 1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 -1 SIMPLE t2 NULL ref a a 4 test.t1.a 1 Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 5 100.00 +1 SIMPLE t2 NULL ref a a 4 test.t1.a 1 100.00 Using index condition select * from t1, t2 where t2.a=t1.a and t2.b + 1; a a b filler 0 0 1 filler @@ -2303,8 +2316,8 @@ create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb; insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C; # this must use key 'a', not PRIMARY: explain select a from t2 where a=b; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL index NULL a 10 # 1000 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL index NULL a 10 # 1000 100.00 Using where; Using index drop table t1, t2; # # Bug #40360: Binlog related errors with binlog off @@ -2362,6 +2375,7 @@ key NULL key_len NULL ref NULL rows 6 +filtered 83.33 Extra Using where EXPLAIN SELECT c FROM foo WHERE b>2;; id 1 @@ -2374,6 +2388,7 @@ key NULL key_len NULL ref NULL rows 6 +filtered 83.33 Extra Using where EXPLAIN SELECT c FROM foo2 WHERE b>2;; id 1 @@ -2386,6 +2401,7 @@ key b key_len 5 ref NULL rows 5 +filtered 100.00 Extra Using where; Using index EXPLAIN SELECT c FROM bar WHERE c>2;; id 1 @@ -2398,6 +2414,7 @@ key NULL key_len NULL ref NULL rows 6 +filtered 100.00 Extra Using where EXPLAIN SELECT c FROM foo WHERE c>2;; id 1 @@ -2410,6 +2427,7 @@ key NULL key_len NULL ref NULL rows 6 +filtered 100.00 Extra Using where EXPLAIN SELECT c FROM foo2 WHERE c>2;; id 1 @@ -2422,6 +2440,7 @@ key b key_len 5 ref NULL rows 6 +filtered 100.00 Extra Using where; Using index DROP TABLE foo, bar, foo2; # @@ -2735,11 +2754,11 @@ test.t1 analyze status OK SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; c1 c2 c3 EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref PRIMARY,c3 PRIMARY 4 const 1 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref PRIMARY,c3 PRIMARY 4 const 1 100.00 Using where; Using filesort EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref PRIMARY PRIMARY 4 const 1 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref PRIMARY PRIMARY 4 const 1 100.00 Using where; Using filesort CREATE TABLE t2 ( c1 int NOT NULL, c2 int NOT NULL, @@ -2748,8 +2767,8 @@ KEY (c1,c2), KEY (c3) ) ENGINE=InnoDB; explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ref c1,c3 c1 4 const 1 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ref c1,c3 c1 4 const 1 100.00 Using where; Using filesort DROP TABLE t1,t2; # # 36259: Optimizing with ORDER BY @@ -2771,14 +2790,14 @@ INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; ANALYZE TABLE t1; EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref i2 i2 8 const,const 1 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref i2 i2 8 const,const 1 100.00 Using where; Using filesort EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref i2 i2 8 const,const 1 Using where; Using filesort +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref i2 i2 8 const,const 1 100.00 Using where; Using filesort EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL {checked} Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL {checked} 0.01 Using where DROP TABLE t1; # # Bug #47963: Wrong results when index is used @@ -2819,8 +2838,8 @@ c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; a b c EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1; # # Bug #46175: NULL read_view and consistent read assertion @@ -2866,9 +2885,9 @@ insert into t2 values (1),(2),(3); analyze table t1; analyze table t2; explain select t1.* from t1 left join t2 using(f1) group by t1.f1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 3 -1 SIMPLE t2 NULL ref f1 f1 4 test.t1.f1 1 Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 3 100.00 +1 SIMPLE t2 NULL ref f1 f1 4 test.t1.f1 1 100.00 Using index drop table t1,t2; # # @@ -2896,6 +2915,7 @@ key b key_len 10 ref NULL rows 10 +filtered 100.00 Extra Using index DROP TABLE t1; # @@ -2989,9 +3009,9 @@ id id NULL 75 EXPLAIN SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0 WHERE t2.id=75 AND t1.id IS NULL; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL const PRIMARY NULL NULL NULL 1 Impossible ON condition -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL const PRIMARY NULL NULL NULL 1 100.00 Impossible ON condition +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where DROP TABLE t1,t2; # # Bug#38999 valgrind warnings for update statement in function compare_record() @@ -3052,8 +3072,8 @@ EXPLAIN SELECT * FROM t1 WHERE f1 IN 3470988,3305175,3329875,3817277,3856380,3796193, 3784744,4180925,4559596,3963734,3856391,4494153) AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index_merge PRIMARY,idx1,idx2 idx2,idx1,PRIMARY 7,60,4 NULL 1 Using intersect(idx2,idx1,PRIMARY); Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index_merge PRIMARY,idx1,idx2 idx2,idx1,PRIMARY 7,60,4 NULL 1 69.39 Using intersect(idx2,idx1,PRIMARY); Using where set optimizer_switch=@tmp_innodb_mysql; DROP TABLE t1; # @@ -3085,8 +3105,8 @@ f1 f2 f3 f4 23 1 9918 1 EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE ORDER BY f1 DESC LIMIT 5; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range f2,f4 f4 1 NULL 22 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range f2,f4 f4 1 NULL 22 100.00 Using where DROP TABLE t1; # # Bug#54117 crash in thr_multi_unlock, temporary table @@ -3113,6 +3133,7 @@ key b key_len 4 ref NULL rows 3 +filtered 100.00 Extra Using index DROP INDEX b ON t1; CREATE INDEX b ON t1(a,b); @@ -3127,6 +3148,7 @@ key b key_len 8 ref NULL rows 3 +filtered 100.00 Extra Using index DROP INDEX b ON t1; CREATE INDEX b ON t1(a,b,c); @@ -3141,6 +3163,7 @@ key PRIMARY key_len 8 ref NULL rows 3 +filtered 100.00 Extra Using index DROP INDEX b ON t1; CREATE INDEX b ON t1(a,b,c,d); @@ -3155,6 +3178,7 @@ key PRIMARY key_len 8 ref NULL rows 3 +filtered 100.00 Extra Using index DROP TABLE t1; # @@ -3202,9 +3226,9 @@ EXPLAIN SELECT COUNT(*) FROM (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> NULL ALL NULL NULL NULL # 1537 -2 DERIVED t1 NULL index_merge PRIMARY,idx idx,PRIMARY 5,4 # 1537 Using sort_union(idx,PRIMARY); Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> NULL ALL NULL NULL NULL # 1537 100.00 +2 DERIVED t1 NULL index_merge PRIMARY,idx idx,PRIMARY 5,4 # 1537 100.00 Using sort_union(idx,PRIMARY); Using where SELECT COUNT(*) FROM (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; @@ -3312,10 +3336,10 @@ INSERT INTO t1 VALUES ('1'), (NULL); INSERT INTO t2 VALUES (1, '1'); EXPLAIN SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 1 -1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t2 NULL index NULL PRIMARY 4 NULL 1 Using index; FirstMatch(t1); Using join buffer (incremental, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 1 100.00 +1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 NULL index NULL PRIMARY 4 NULL 1 100.00 Using index; FirstMatch(t1); Using join buffer (incremental, BNL join) SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2); b 1 @@ -3348,8 +3372,8 @@ EXPLAIN SELECT v2 FROM t1 WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref i,v i 5 const 2 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref i,v i 5 const 2 100.00 Using where DROP TABLE t1; # # Bug#54606 innodb fast alter table + pack_keys=0 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result index e326979294c..5986169a249 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/rocksdb.result @@ -70,11 +70,11 @@ a b 1 1 2 2 explain select * from t2 where a='no-such-key'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain select * from t2 where a='abc'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL const PRIMARY PRIMARY 12 const # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL const PRIMARY PRIMARY 12 const # 100.00 select * from t2 where a='abc'; a b abc def @@ -189,8 +189,8 @@ insert into t8 values ('five', 'funf'); # Delete by PK explain delete from t8 where a='three'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t8 NULL range PRIMARY PRIMARY 12 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t8 NULL range PRIMARY PRIMARY 12 NULL # 100.00 Using where delete from t8 where a='three'; select * from t8; a col1 @@ -279,9 +279,9 @@ j 4 EXPLAIN SELECT * FROM t10, t11 WHERE i=j; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t10 NULL index PRIMARY PRIMARY 4 NULL # Using index -1 SIMPLE t11 NULL eq_ref PRIMARY PRIMARY 4 test.t10.i # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t10 NULL index PRIMARY PRIMARY 4 NULL # 100.00 Using index +1 SIMPLE t11 NULL eq_ref PRIMARY PRIMARY 4 test.t10.i # 100.00 Using index SELECT * FROM t10, t11 WHERE i=j; i j 1 1 @@ -531,22 +531,22 @@ insert into t30 values ('row2', 'row2-key', 'row2-data'); insert into t30 values ('row3', 'row3-key', 'row3-data'); explain select * from t30 where key1='row2-key'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t30 NULL ref key1 key1 18 const # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t30 NULL ref key1 key1 18 const # 100.00 Using index condition select * from t30 where key1='row2-key'; pk key1 col1 row2 row2-key row2-data explain select * from t30 where key1='row1'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t30 NULL ref key1 key1 18 const # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t30 NULL ref key1 key1 18 const # 100.00 Using index condition # This will produce nothing: select * from t30 where key1='row1'; pk key1 col1 explain select key1 from t30; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t30 NULL index NULL key1 18 NULL # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t30 NULL index NULL key1 18 NULL # 100.00 Using index select key1 from t30; key1 row1-key @@ -575,8 +575,8 @@ insert into t30 values ('row5', 'row5-key', 'row5-data'); explain select * from t30 where key1 <='row3-key'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t30 NULL range key1 key1 18 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t30 NULL range key1 key1 18 NULL # 100.00 Using index condition select * from t30 where key1 <='row3-key'; pk key1 col1 row1 row1-key row1-data @@ -584,8 +584,8 @@ row2 row2-key row2-data row3 row3-key row3-data explain select * from t30 where key1 between 'row2-key' and 'row4-key'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t30 NULL range key1 key1 18 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t30 NULL range key1 key1 18 NULL # 100.00 Using index condition select * from t30 where key1 between 'row2-key' and 'row4-key'; pk key1 col1 row2 row2-key row2-data @@ -593,32 +593,32 @@ row3 row3-key row3-data row4 row4-key row4-data explain select * from t30 where key1 in ('row2-key','row4-key'); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t30 NULL range key1 key1 18 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t30 NULL range key1 key1 18 NULL # 100.00 Using index condition select * from t30 where key1 in ('row2-key','row4-key'); pk key1 col1 row2 row2-key row2-data row4 row4-key row4-data explain select key1 from t30 where key1 in ('row2-key','row4-key'); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t30 NULL range key1 key1 18 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t30 NULL range key1 key1 18 NULL # 100.00 Using where; Using index select key1 from t30 where key1 in ('row2-key','row4-key'); key1 row2-key row4-key explain select * from t30 where key1 > 'row1-key' and key1 < 'row4-key'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t30 NULL range key1 key1 18 NULL # Using index condition +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t30 NULL range key1 key1 18 NULL # 100.00 Using index condition select * from t30 where key1 > 'row1-key' and key1 < 'row4-key'; pk key1 col1 row2 row2-key row2-data row3 row3-key row3-data explain select * from t30 order by key1 limit 3; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t30 NULL index NULL key1 18 NULL # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t30 NULL index NULL key1 18 NULL # 100.00 select * from t30 order by key1 limit 3; pk key1 col1 row1 row1-key row1-data @@ -626,8 +626,8 @@ row2 row2-key row2-data row3 row3-key row3-data explain select * from t30 order by key1 desc limit 3; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t30 NULL index NULL key1 18 NULL # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t30 NULL index NULL key1 18 NULL # 100.00 select * from t30 order by key1 desc limit 3; pk key1 col1 row5 row5-key row5-data @@ -638,8 +638,8 @@ row3 row3-key row3-data # explain select * from t30 where pk <='row3'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t30 NULL range PRIMARY PRIMARY 18 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t30 NULL range PRIMARY PRIMARY 18 NULL # 100.00 Using where select * from t30 where pk <='row3'; pk key1 col1 row1 row1-key row1-data @@ -647,8 +647,8 @@ row2 row2-key row2-data row3 row3-key row3-data explain select * from t30 where pk between 'row2' and 'row4'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t30 NULL range PRIMARY PRIMARY 18 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t30 NULL range PRIMARY PRIMARY 18 NULL # 100.00 Using where select * from t30 where pk between 'row2' and 'row4'; pk key1 col1 row2 row2-key row2-data @@ -656,16 +656,16 @@ row3 row3-key row3-data row4 row4-key row4-data explain select * from t30 where pk in ('row2','row4'); -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t30 NULL range PRIMARY PRIMARY 18 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t30 NULL range PRIMARY PRIMARY 18 NULL # 100.00 Using where select * from t30 where pk in ('row2','row4'); pk key1 col1 row2 row2-key row2-data row4 row4-key row4-data explain select * from t30 order by pk limit 3; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t30 NULL index NULL PRIMARY 18 NULL # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t30 NULL index NULL PRIMARY 18 NULL # 100.00 select * from t30 order by pk limit 3; pk key1 col1 row1 row1-key row1-data @@ -1078,8 +1078,8 @@ insert into t1 values (-5,-5,-5); # INT column uses index-only: explain select key1 from t1 where key1=2; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref key1 key1 5 const # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref key1 key1 5 const # 100.00 Using index select key1 from t1 where key1=2; key1 2 @@ -1092,8 +1092,8 @@ insert into t2 values (1,1,1), (2,2,2); # INT UNSIGNED column uses index-only: explain select key1 from t2 where key1=2; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ref key1 key1 5 const # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ref key1 key1 5 const # 100.00 Using index select key1 from t2 where key1=2; key1 2 @@ -1103,8 +1103,8 @@ insert into t3 values (1,1,1), (2,2,2); # BIGINT uses index-only: explain select key1 from t3 where key1=2; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t3 NULL ref key1 key1 9 const # Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 NULL ref key1 key1 9 const # 100.00 Using index select key1 from t3 where key1=2; key1 2 @@ -1121,8 +1121,8 @@ key (key1) insert into t1 values(1, 'one',11), (2,'two',22); explain select key1 from t1 where key1='one'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref key1 key1 11 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref key1 key1 11 const # 100.00 Using where; Using index # The following will produce no rows. This looks like a bug, # but it is actually correct behavior. Binary strings are end-padded # with \0 character (and not space). Comparison does not ignore @@ -1131,8 +1131,8 @@ select key1 from t1 where key1='one'; key1 explain select hex(key1) from t1 where key1='one\0\0\0\0\0\0\0'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref key1 key1 11 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref key1 key1 11 const # 100.00 Using where; Using index select hex(key1) from t1 where key1='one\0\0\0\0\0\0\0'; hex(key1) 6F6E6500000000000000 @@ -1146,8 +1146,8 @@ key (key1) insert into t2 values(1, 'one',11), (2,'two',22); explain select key1 from t2 where key1='one'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ref key1 key1 11 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ref key1 key1 11 const # 100.00 Using where; Using index select key1 from t2 where key1='one'; key1 one @@ -1161,8 +1161,8 @@ key (key1) insert into t3 values(1, 'one',11), (2,'two',22); explain select key1 from t3 where key1='one'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t3 NULL ref key1 key1 31 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 NULL ref key1 key1 31 const # 100.00 Using where; Using index select key1 from t3 where key1='one'; key1 one @@ -1176,8 +1176,8 @@ key(key1) insert into t4 values(1, 'one'), (2,'two'),(3,'threee'),(55,'fifty-five'); explain select key1 from t4 where key1='two'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ref key1 key1 13 const # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ref key1 key1 13 const # 100.00 Using where; Using index select key1 from t4 where key1='two'; key1 two @@ -1186,8 +1186,8 @@ key1 fifty-five explain select key1 from t4 where key1 between 's' and 'u'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL range key1 key1 13 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL range key1 key1 13 NULL # 100.00 Using where; Using index select key1 from t4 where key1 between 's' and 'u'; key1 threee @@ -1856,15 +1856,15 @@ key(col2) comment 'cf4' insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5); explain select * from t1 where col1=2; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref col1 col1 5 const # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref col1 col1 5 const # 100.00 select * from t1 where col1=2; pk col1 col2 2 2 2 explain select * from t1 where col2=3; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref col2 col2 5 const # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref col2 col2 5 const # 100.00 select * from t1 where col2=3; pk col1 col2 3 3 3 @@ -1886,8 +1886,8 @@ primary key (pk) comment 'cf5' insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5); explain select * from t1 where col1=2; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref col1 col1 5 const # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref col1 col1 5 const # 100.00 select * from t1 where col1=2; pk col1 col2 2 2 2 @@ -1933,8 +1933,8 @@ key(id2) insert into t1 select A.a, B.a, 31, 1234 from t0 A, t0 B; explain select * from t1 where id1=30 and value1=30 for update; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ref PRIMARY PRIMARY 4 const # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ref PRIMARY PRIMARY 4 const # 100.00 Using where set @var1=(select variable_value from information_schema.global_status where variable_name='rocksdb_number_keys_read'); @@ -1955,8 +1955,8 @@ create table t1 (id int primary key, value int) engine=rocksdb; insert into t1 values (1,1),(2,2),(3,3); # The following must not use 'Using filesort': explain select * from t1 ORDER BY id; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL # 100.00 drop table t1; # # Issue #26: Index-only scans for DATETIME and TIMESTAMP @@ -1989,8 +1989,8 @@ pk kp1 kp2 col1 explain select kp1,kp2 from t1 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range kp1 kp1 6 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kp1 kp1 6 NULL # 100.00 Using where; Using index select kp1,kp2 from t1 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; kp1 kp2 @@ -2012,8 +2012,8 @@ insert into t2 select * from t1; explain select kp1,kp2 from t2 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 5 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 5 NULL # 100.00 Using where; Using index select kp1,kp2 from t2 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; kp1 kp2 @@ -2049,8 +2049,8 @@ pk kp1 kp2 col1 explain select kp1,kp2 from t1 force index (kp1) where kp1 between '2015-01-01' and '2015-01-05'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range kp1 kp1 4 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kp1 kp1 4 NULL # 100.00 Using where; Using index select kp1,kp2 from t1 force index (kp1) where kp1 between '2015-01-01' and '2015-01-05'; kp1 kp2 @@ -2072,8 +2072,8 @@ insert into t2 select * from t1; explain select kp1,kp2 from t2 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 3 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 3 NULL # 100.00 Using where; Using index select kp1,kp2 from t2 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; kp1 kp2 @@ -2111,8 +2111,8 @@ pk kp1 kp2 col1 explain select kp1,kp2 from t1 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range kp1 kp1 5 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kp1 kp1 5 NULL # 100.00 Using where; Using index select kp1,kp2 from t1 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; kp1 kp2 @@ -2134,8 +2134,8 @@ insert into t2 select * from t1; explain select kp1,kp2 from t2 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 4 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 4 NULL # 100.00 Using where; Using index select kp1,kp2 from t2 force index (kp1) where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59'; kp1 kp2 @@ -2173,8 +2173,8 @@ pk kp1 kp2 col1 explain select kp1,kp2 from t1 force index (kp1) where kp1 between '09:01:00' and '09:05:00'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range kp1 kp1 4 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kp1 kp1 4 NULL # 100.00 Using where; Using index select kp1,kp2 from t1 force index (kp1) where kp1 between '09:01:00' and '09:05:00'; kp1 kp2 @@ -2196,8 +2196,8 @@ insert into t2 select * from t1; explain select kp1,kp2 from t2 force index (kp1) where kp1 between '09:01:00' and '09:05:00'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 3 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 3 NULL # 100.00 Using where; Using index select kp1,kp2 from t2 force index (kp1) where kp1 between '09:01:00' and '09:05:00'; kp1 kp2 @@ -2234,8 +2234,8 @@ pk kp1 kp2 col1 explain select kp1,kp2 from t1 force index (kp1) where kp1 between '2016' and '2020'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range kp1 kp1 2 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range kp1 kp1 2 NULL # 100.00 Using where; Using index select kp1,kp2 from t1 force index (kp1) where kp1 between '2016' and '2020'; kp1 kp2 @@ -2257,8 +2257,8 @@ insert into t2 select * from t1; explain select kp1,kp2 from t2 force index (kp1) where kp1 between '2016' and '2020'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range kp1 kp1 1 NULL # Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range kp1 kp1 1 NULL # 100.00 Using where; Using index select kp1,kp2 from t2 force index (kp1) where kp1 between '2016' and '2020'; kp1 kp2 @@ -2308,11 +2308,11 @@ a b c 2 2abcde 2abcde 3 3abcde 3abcde explain select * from t1 where b like '1%'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range b b 1258 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range b b 1258 NULL # 100.00 Using where explain select b, a from t1 where b like '1%'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range b b 1258 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range b b 1258 NULL # 100.00 Using where update t1 set b= '12345' where b = '2abcde'; select * from t1; a b c @@ -2369,8 +2369,8 @@ INSERT INTO t2 VALUES (1,1,1,1,1,1,1); SELECT * FROM t2 WHERE c1 > 4294967295 ORDER BY c1,c6; c1 c2 c3 c4 c5 c6 c7 EXPLAIN SELECT * FROM t2 WHERE c1 > 4294967295 ORDER BY c1,c6; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range PRIMARY PRIMARY 4 NULL 50 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range PRIMARY PRIMARY 4 NULL 50 100.00 Using where drop table t2; # # Issue #135: register transaction was not being called for statement @@ -2576,8 +2576,8 @@ c1 c2 c3 c1-val3 c2-val3 7 c1-val1 c2-val1 5 explain SELECT * FROM t1 force index(idx) WHERE c1 <> '1' ORDER BY c1 DESC; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range idx idx 32 NULL # Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range idx idx 32 NULL # 100.00 Using where drop table t1; # # Issue#267: MyRocks issue with no matching min/max row and count(*) diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index 5c7f2e5a34e..126124977ea 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -42,6 +42,9 @@ #include "my_valgrind.h" +/* For MAX_PARTITIONS */ +#include "../sql/sql_const.h" + static const my_bool my_true= 1; @@ -7587,7 +7590,7 @@ static void test_explain_bug() if (!opt_silent) fprintf(stdout, "\n total fields in the result: %d", mysql_num_fields(result)); - DIE_UNLESS(11 == mysql_num_fields(result)); + DIE_UNLESS(12 == mysql_num_fields(result)); verify_prepare_field(result, 0, "id", "", MYSQL_TYPE_LONGLONG, "", "", "", 3, 0); @@ -7597,6 +7600,14 @@ static void test_explain_bug() verify_prepare_field(result, 2, "table", "", MYSQL_TYPE_VAR_STRING, "", "", "", NAME_CHAR_LEN, 0); + + /* + The *3 is caused by how blob length is interpreted: + One specifies #chars then which gives max # bytes, using mbmaxlen. + Then, the number of bytes is converted back to chars using mbminlen. + */ + verify_prepare_field(result, 3, "partitions", "", MYSQL_TYPE_MEDIUM_BLOB, + "", "", "", MAX_PARTITIONS * (1 + FN_LEN) * 3, 0); verify_prepare_field(result, 4, "type", "", MYSQL_TYPE_VAR_STRING, "", "", "", 10, 0); @@ -7625,7 +7636,10 @@ static void test_explain_bug() verify_prepare_field(result, 9, "rows", "", MYSQL_TYPE_VAR_STRING, "", "", "", NAME_CHAR_LEN, 0); - verify_prepare_field(result, 10, "Extra", "", MYSQL_TYPE_VAR_STRING, + verify_prepare_field(result, 10, "filtered", "", MYSQL_TYPE_DOUBLE, + "", "", "", 4, 0); + + verify_prepare_field(result, 11, "Extra", "", MYSQL_TYPE_VAR_STRING, "", "", "", 255, 0); mysql_free_result(result); @@ -21003,10 +21017,10 @@ static void test_explain_meta() num_fields= mysql_stmt_field_count(stmt); mct_log("EXPALIN number of fields: %d\n", (int) num_fields); - if (num_fields != 11) + if (num_fields != 12) { mct_close_log(); - DIE("num_fields != 11"); + DIE("num_fields != 12"); } print_metadata(rs_metadata, num_fields); mysql_free_result(rs_metadata); @@ -21075,10 +21089,10 @@ static void test_explain_meta() num_fields= mysql_stmt_field_count(stmt); mct_log("EXPALIN INSERT number of fields: %d\n", (int) num_fields); - if (num_fields != 11) + if (num_fields != 12) { mct_close_log(); - DIE("num_fields != 11"); + DIE("num_fields != 12"); } print_metadata(rs_metadata, num_fields); mysql_free_result(rs_metadata); @@ -21145,10 +21159,10 @@ static void test_explain_meta() num_fields= mysql_stmt_field_count(stmt); mct_log("EXPALIN UPDATE number of fields: %d\n", (int) num_fields); - if (num_fields != 11) + if (num_fields != 12) { mct_close_log(); - DIE("num_fields != 11"); + DIE("num_fields != 12"); } print_metadata(rs_metadata, num_fields); mysql_free_result(rs_metadata); @@ -21215,10 +21229,10 @@ static void test_explain_meta() num_fields= mysql_stmt_field_count(stmt); mct_log("EXPALIN DELETE number of fields: %d\n", (int) num_fields); - if (num_fields != 11) + if (num_fields != 12) { mct_close_log(); - DIE("num_fields != 11"); + DIE("num_fields != 12"); } print_metadata(rs_metadata, num_fields); mysql_free_result(rs_metadata);
participants (1)
-
psergey