3218 Tor Didriksen 2010-09-20 [merge]
Manual merge from next-mr-opt-backporting
modified:
mysql-test/r/subquery_sj_firstmatch_jcl6.result
mysql-test/r/subquery_sj_firstmatch_jcl7.result
mysql-test/suite/perfschema/include/upgrade_check.inc
storage/perfschema/ha_perfschema.cc
3217 Evgeny Potemkin 2010-09-10 [merge]
Auto-merged.
modified:
mysql-test/suite/perfschema/r/privilege_table_io.result
sql/sql_show.cc
storage/perfschema/ha_perfschema.cc
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-09-14 17:21:35 +0000
@@ -5127,6 +5127,89 @@ a
1
1
drop table t1,t2,t3;
+#
+# Bug#51457 Firstmatch semijoin strategy gives wrong results for
+# certain query plans
+#
+SET @@default_storage_engine='innodb';
+SET @@optimizer_switch='semijoin=on,materialization=off,firstmatch=on,loosescan=off';
+SET @@optimizer_join_cache_level=0;
+CREATE TABLE t0(a INTEGER);
+CREATE TABLE t1(a INTEGER);
+INSERT INTO t1 VALUES(1);
+CREATE TABLE t2(a INTEGER);
+INSERT INTO t2 VALUES(5), (8);
+CREATE TABLE t6(a INTEGER);
+INSERT INTO t6 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE t8(a INTEGER);
+INSERT INTO t8 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+EXPLAIN
+SELECT *
+FROM t2 AS nt2
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY it1 ALL NULL NULL NULL NULL 1 Using where; FirstMatch
+1 PRIMARY nt2 ALL NULL NULL NULL NULL 2
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(nt2)
+SELECT *
+FROM t2 AS nt2
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+a
+5
+8
+EXPLAIN
+SELECT *
+FROM t2 AS nt2, t8 AS nt4
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY it1 ALL NULL NULL NULL NULL 1 Using where; FirstMatch
+1 PRIMARY nt2 ALL NULL NULL NULL NULL 2
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(nt2)
+1 PRIMARY nt4 ALL NULL NULL NULL NULL 8
+SELECT *
+FROM t2 AS nt2, t8 AS nt4
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+a a
+5 1
+5 3
+5 5
+5 7
+5 9
+5 7
+5 3
+5 1
+8 1
+8 3
+8 5
+8 7
+8 9
+8 7
+8 3
+8 1
+EXPLAIN
+SELECT *
+FROM t0 AS ot1, t2 AS nt3
+WHERE ot1.a IN (SELECT it2.a
+FROM t1 AS it2 JOIN t8 AS it4 ON it2.a=it4.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 1
+1 PRIMARY it2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(ot1)
+1 PRIMARY nt3 ALL NULL NULL NULL NULL 2
+1 PRIMARY it4 ALL NULL NULL NULL NULL 8 Using where; FirstMatch(nt3)
+SELECT *
+FROM t0 as ot1, t2 AS nt3
+WHERE ot1.a IN (SELECT it2.a
+FROM t1 AS it2 JOIN t8 AS it4 ON it2.a=it4.a);
+a a
+DROP TABLE t0, t1, t2, t6, t8;
+SET @@default_storage_engine=default;
+SET @@optimizer_switch=default;
+SET @@optimizer_join_cache_level=default;
+# End of bug#51457
set optimizer_switch=default;
set optimizer_join_cache_level=default;
show variables like 'optimizer_join_cache_level';
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-09-14 17:21:35 +0000
@@ -5127,6 +5127,89 @@ a
1
1
drop table t1,t2,t3;
+#
+# Bug#51457 Firstmatch semijoin strategy gives wrong results for
+# certain query plans
+#
+SET @@default_storage_engine='innodb';
+SET @@optimizer_switch='semijoin=on,materialization=off,firstmatch=on,loosescan=off';
+SET @@optimizer_join_cache_level=0;
+CREATE TABLE t0(a INTEGER);
+CREATE TABLE t1(a INTEGER);
+INSERT INTO t1 VALUES(1);
+CREATE TABLE t2(a INTEGER);
+INSERT INTO t2 VALUES(5), (8);
+CREATE TABLE t6(a INTEGER);
+INSERT INTO t6 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE t8(a INTEGER);
+INSERT INTO t8 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+EXPLAIN
+SELECT *
+FROM t2 AS nt2
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY it1 ALL NULL NULL NULL NULL 1 Using where; FirstMatch
+1 PRIMARY nt2 ALL NULL NULL NULL NULL 2
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(nt2)
+SELECT *
+FROM t2 AS nt2
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+a
+5
+8
+EXPLAIN
+SELECT *
+FROM t2 AS nt2, t8 AS nt4
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY it1 ALL NULL NULL NULL NULL 1 Using where; FirstMatch
+1 PRIMARY nt2 ALL NULL NULL NULL NULL 2
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(nt2)
+1 PRIMARY nt4 ALL NULL NULL NULL NULL 8
+SELECT *
+FROM t2 AS nt2, t8 AS nt4
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+a a
+5 1
+5 3
+5 5
+5 7
+5 9
+5 7
+5 3
+5 1
+8 1
+8 3
+8 5
+8 7
+8 9
+8 7
+8 3
+8 1
+EXPLAIN
+SELECT *
+FROM t0 AS ot1, t2 AS nt3
+WHERE ot1.a IN (SELECT it2.a
+FROM t1 AS it2 JOIN t8 AS it4 ON it2.a=it4.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 1
+1 PRIMARY it2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(ot1)
+1 PRIMARY nt3 ALL NULL NULL NULL NULL 2
+1 PRIMARY it4 ALL NULL NULL NULL NULL 8 Using where; FirstMatch(nt3)
+SELECT *
+FROM t0 as ot1, t2 AS nt3
+WHERE ot1.a IN (SELECT it2.a
+FROM t1 AS it2 JOIN t8 AS it4 ON it2.a=it4.a);
+a a
+DROP TABLE t0, t1, t2, t6, t8;
+SET @@default_storage_engine=default;
+SET @@optimizer_switch=default;
+SET @@optimizer_join_cache_level=default;
+# End of bug#51457
set optimizer_switch=default;
set optimizer_join_cache_level=default;
show variables like 'optimizer_join_cache_level';
=== modified file 'mysql-test/suite/perfschema/include/upgrade_check.inc'
--- a/mysql-test/suite/perfschema/include/upgrade_check.inc 2010-07-20 19:15:29 +0000
+++ b/mysql-test/suite/perfschema/include/upgrade_check.inc 2010-09-13 23:19:39 +0000
@@ -18,7 +18,7 @@
#
--error 1
---exec $MYSQL_UPGRADE --skip-verbose > $out_file 2> $err_file
+--exec $MYSQL_UPGRADE --skip-verbose --force > $out_file 2> $err_file
# Verify that mysql_upgrade complained about the performance_schema
--cat_file $err_file
=== modified file 'storage/perfschema/ha_perfschema.cc'
--- a/storage/perfschema/ha_perfschema.cc 2010-09-09 20:50:20 +0000
+++ b/storage/perfschema/ha_perfschema.cc 2010-09-10 09:12:36 +0000
@@ -19,8 +19,8 @@
*/
#include "my_global.h"
-#include "my_atomic.h"
#include "my_pthread.h"
+#include "my_atomic.h"
#include "sql_plugin.h"
#include "mysql/plugin.h"
#include "ha_perfschema.h"
Attachment: [text/bzr-bundle] bzr/tor.didriksen@oracle.com-20100920150513-fsju0xkym89zr2t6.bundle
| Thread |
|---|
| • bzr push into mysql-next-mr-opt-team branch (tor.didriksen:3217 to 3218) | Tor Didriksen | 20 Sep |
