revision-id: d5cd8d3d5f6ecf283df9ea2610abe7b4abfd69c7 (mariadb-10.6.1-368-gd5cd8d3d5f6) parent(s): b70981f52056baeebd611790c4b1a638dc7e01ec author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-03-21 10:47:16 +0300 message: Update test results (4) --- mysql-test/include/icp_tests.inc | 1 + mysql-test/main/group_min_max.result | 5 +-- mysql-test/main/group_min_max.test | 4 ++- mysql-test/main/innodb_icp.result | 2 +- mysql-test/main/join_cache.result | 14 ++++---- mysql-test/main/join_cache.test | 3 ++ mysql-test/main/named_pipe.result | 62 ++++++++++++++++++------------------ mysql-test/main/windows.result | 6 ++-- mysql-test/suite/s3/basic.test | 2 +- 9 files changed, 53 insertions(+), 46 deletions(-) diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc index a2327f5782f..c2e27480cb3 100644 --- a/mysql-test/include/icp_tests.inc +++ b/mysql-test/include/icp_tests.inc @@ -432,6 +432,7 @@ WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 ORDER BY c1 LIMIT 1; +--replace_column 10 ROWS 11 FLTRD EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index 69111ff5569..abd294e31b0 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -4057,9 +4057,10 @@ KEY `index_t1_on_owner_id_and_foo` (`owner_id`,`foo`) INSERT INTO t1 (owner_id, foo, whatever) VALUES (1, TRUE, "yello"), (1, FALSE, "yello"), (2, TRUE, "yello"), (2, TRUE, "yello"), (2, FALSE, "yello"); -EXPLAIN SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); +EXPLAIN +SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL index NULL index_t1_on_owner_id_and_foo 7 NULL 5 100.00 Using where; Using index +1 SIMPLE t1 NULL index NULL index_t1_on_owner_id_and_foo 7 NULL ROWS 100.00 Using where; Using index SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); owner_id 1 diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test index eceba51e443..8bded508372 100644 --- a/mysql-test/main/group_min_max.test +++ b/mysql-test/main/group_min_max.test @@ -1718,7 +1718,9 @@ CREATE TABLE `t1` ( INSERT INTO t1 (owner_id, foo, whatever) VALUES (1, TRUE, "yello"), (1, FALSE, "yello"), (2, TRUE, "yello"), (2, TRUE, "yello"), (2, FALSE, "yello"); -EXPLAIN SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); +--replace_column 10 ROWS +EXPLAIN +SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); DROP TABLE t1; diff --git a/mysql-test/main/innodb_icp.result b/mysql-test/main/innodb_icp.result index b7535c1c695..bd0956db2f2 100644 --- a/mysql-test/main/innodb_icp.result +++ b/mysql-test/main/innodb_icp.result @@ -414,7 +414,7 @@ WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 ORDER BY c1 LIMIT 1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL range PRIMARY,k1 k1 5 NULL 4 37.50 Using where; Using index +1 SIMPLE t1 NULL range PRIMARY,k1 k1 5 NULL ROWS FLTRD Using where; Using index DROP TABLE t1; # # diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index ed5bb94e8a1..02c87eb8195 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -214,8 +214,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where -1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -244,9 +244,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where -1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 17.09 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 4.17 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 FLTRD Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -895,8 +895,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.60 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index 38ca79799ad..1bea4cfcbf8 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -113,6 +113,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language set join_cache_level=3; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -123,6 +124,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -400,6 +402,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND diff --git a/mysql-test/main/named_pipe.result b/mysql-test/main/named_pipe.result index 3630891ec91..e512e2a0f5f 100644 --- a/mysql-test/main/named_pipe.result +++ b/mysql-test/main/named_pipe.result @@ -128,20 +128,20 @@ fld3 select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%'; fld3 explain select t2.fld3 from t2 where fld3 = 'honeysuckle'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ref fld3 fld3 30 const 1 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ref fld3 fld3 30 const 1 100.00 Using where; Using index explain select fld3 from t2 ignore index (fld3) where fld3 = 'honeysuckle'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where explain select fld3 from t2 use index (fld1) where fld3 = 'honeysuckle'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where explain select fld3 from t2 use index (fld3) where fld3 = 'honeysuckle'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ref fld3 fld3 30 const 1 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ref fld3 fld3 30 const 1 100.00 Using where; Using index explain select fld3 from t2 use index (fld1,fld3) where fld3 = 'honeysuckle'; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ref fld3 fld3 30 const 1 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ref fld3 fld3 30 const 1 100.00 Using where; Using index explain select fld3 from t2 ignore index (fld3,not_used); ERROR 42000: Key 'not_used' doesn't exist in table 't2' explain select fld3 from t2 use index (not_used); @@ -151,8 +151,8 @@ fld3 honeysuckle honoring explain select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range fld3 fld3 30 NULL 2 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range fld3 fld3 30 NULL 2 100.00 Using where; Using index select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3; fld1 fld3 148504 Colombo @@ -171,8 +171,8 @@ fld1 250501 250502 explain select fld1 from t2 where fld1=250501 or fld1="250502"; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range fld1 fld1 4 NULL 2 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range fld1 fld1 4 NULL 2 100.00 Using where; Using index select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; fld1 250501 @@ -180,8 +180,8 @@ fld1 250505 250601 explain select fld1 from t2 where fld1=250501 or fld1=250502 or fld1 >= 250505 and fld1 <= 250601 or fld1 between 250501 and 250502; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL range fld1 fld1 4 NULL 4 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL range fld1 fld1 4 NULL 4 100.00 Using where; Using index select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; fld1 fld3 012001 flanking @@ -597,21 +597,21 @@ companynr rtrim(space(512+companynr)) select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by fld3; fld3 explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ALL fld1 NULL NULL NULL 1199 Using where; Using temporary; Using filesort -1 SIMPLE t3 NULL eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 Using where; Using index +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ALL fld1 NULL NULL NULL 1199 100.00 Using where; Using temporary; Using filesort +1 SIMPLE t3 NULL eq_ref PRIMARY PRIMARY 4 test.t2.fld1 1 100.00 Using where; Using index explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL ALL period NULL NULL NULL 41810 Using filesort -1 SIMPLE t3 NULL ref period period 4 test.t1.period 4181 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL ALL period NULL NULL NULL 41810 100.00 Using filesort +1 SIMPLE t3 NULL ref period period 4 test.t1.period 4181 100.00 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t3 NULL index period period 4 NULL 1 -1 SIMPLE t1 NULL ref period period 4 test.t3.period 4181 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 NULL index period period 4 NULL 1 100.00 +1 SIMPLE t1 NULL ref period period 4 test.t3.period 4181 100.00 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL index period period 4 NULL 1 -1 SIMPLE t3 NULL ref period period 4 test.t1.period 4181 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL index period period 4 NULL 1 100.00 +1 SIMPLE t3 NULL ref period period 4 test.t1.period 4181 100.00 select period from t1; period 9410 @@ -624,9 +624,9 @@ select fld3,period from t2,t3 where t2.fld1 = 011401 and t2.fld1=t3.t2nr and t3. fld3 period breaking 1001 explain select fld3,period from t2,t3 where t2.fld1 = 011401 and t3.t2nr=t2.fld1 and 1001 = t3.period; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL const fld1 fld1 4 const 1 -1 SIMPLE t3 NULL const PRIMARY,period PRIMARY 4 const 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL const fld1 fld1 4 const 1 100.00 +1 SIMPLE t3 NULL const PRIMARY,period PRIMARY 4 const 1 100.00 select fld3,period from t2,t1 where companynr*10 = 37*10; fld3 period breaking 9410 diff --git a/mysql-test/main/windows.result b/mysql-test/main/windows.result index 1e4db32606d..9972293e019 100644 --- a/mysql-test/main/windows.result +++ b/mysql-test/main/windows.result @@ -15,9 +15,9 @@ drop table t1; CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (1,1); EXPLAIN SELECT * FROM t1 WHERE b = (SELECT max(2)); -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 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +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 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used DROP TABLE t1; CREATE DATABASE `TESTDB`; USE `TESTDB`; diff --git a/mysql-test/suite/s3/basic.test b/mysql-test/suite/s3/basic.test index 4b5a2f755a6..5306e7f0454 100644 --- a/mysql-test/suite/s3/basic.test +++ b/mysql-test/suite/s3/basic.test @@ -18,7 +18,7 @@ insert into t1 select seq, seq+10, repeat(char(65+ mod(seq, 20)),mod(seq,1000)) alter table t1 engine=s3; show create table t1; ----replace_column 2 # 11 # 15 # 16 # 17 # 23 # +--replace_column 2 # 11 # 15 # 16 # 17 # 23 # --replace_result $database database --eval select * from information_schema.tables where table_schema="$database" and table_name="t1"; --replace_column 8 # 12 # 13 # 14 # 19 #