3701 Evgeny Potemkin 2009-11-09 [merge]
Auto-merged.
modified:
mysql-test/r/ctype_utf8.result
mysql-test/t/ctype_utf8.test
storage/example/Makefile.am
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result 2009-03-19 17:03:58 +0000
+++ b/mysql-test/r/subselect_sj.result 2009-09-01 14:44:35 +0000
@@ -327,3 +327,62 @@ AND OUTR . varchar_nokey <= 'w'
HAVING X > '2012-12-12';
X
drop table t1, t2;
+#
+# Bug#45191: Incorrectly initialized semi-join led to a wrong result.
+#
+CREATE TABLE STAFF (EMPNUM CHAR(3) NOT NULL,
+EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15));
+CREATE TABLE PROJ (PNUM CHAR(3) NOT NULL,
+PNAME CHAR(20), PTYPE CHAR(6),
+BUDGET DECIMAL(9),
+CITY CHAR(15));
+CREATE TABLE WORKS (EMPNUM CHAR(3) NOT NULL,
+PNUM CHAR(3) NOT NULL, HOURS DECIMAL(5));
+INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale');
+INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna');
+INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna');
+INSERT INTO STAFF VALUES ('E4','Don',12,'Deale');
+INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron');
+INSERT INTO PROJ VALUES ('P1','MXSS','Design',10000,'Deale');
+INSERT INTO PROJ VALUES ('P2','CALM','Code',30000,'Vienna');
+INSERT INTO PROJ VALUES ('P3','SDP','Test',30000,'Tampa');
+INSERT INTO PROJ VALUES ('P4','SDP','Design',20000,'Deale');
+INSERT INTO PROJ VALUES ('P5','IRM','Test',10000,'Vienna');
+INSERT INTO PROJ VALUES ('P6','PAYR','Design',50000,'Deale');
+INSERT INTO WORKS VALUES ('E1','P1',40);
+INSERT INTO WORKS VALUES ('E1','P2',20);
+INSERT INTO WORKS VALUES ('E1','P3',80);
+INSERT INTO WORKS VALUES ('E1','P4',20);
+INSERT INTO WORKS VALUES ('E1','P5',12);
+INSERT INTO WORKS VALUES ('E1','P6',12);
+INSERT INTO WORKS VALUES ('E2','P1',40);
+INSERT INTO WORKS VALUES ('E2','P2',80);
+INSERT INTO WORKS VALUES ('E3','P2',20);
+INSERT INTO WORKS VALUES ('E4','P2',20);
+INSERT INTO WORKS VALUES ('E4','P4',40);
+INSERT INTO WORKS VALUES ('E4','P5',80);
+set optimizer_switch='default,materialization=off';
+explain SELECT EMPNUM, EMPNAME
+FROM STAFF
+WHERE EMPNUM IN
+(SELECT EMPNUM FROM WORKS
+WHERE PNUM IN
+(SELECT PNUM FROM PROJ));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY STAFF ALL NULL NULL NULL NULL 5
+1 PRIMARY PROJ ALL NULL NULL NULL NULL 6
+1 PRIMARY WORKS ALL NULL NULL NULL NULL 12 Using where; FirstMatch(STAFF)
+SELECT EMPNUM, EMPNAME
+FROM STAFF
+WHERE EMPNUM IN
+(SELECT EMPNUM FROM WORKS
+WHERE PNUM IN
+(SELECT PNUM FROM PROJ));
+EMPNUM EMPNAME
+E1 Alice
+E2 Betty
+E3 Carmen
+E4 Don
+set optimizer_switch='default';
+drop table STAFF,WORKS,PROJ;
+# End of bug#45191
=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result 2009-03-19 17:03:58 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result 2009-09-01 14:44:35 +0000
@@ -331,6 +331,65 @@ AND OUTR . varchar_nokey <= 'w'
HAVING X > '2012-12-12';
X
drop table t1, t2;
+#
+# Bug#45191: Incorrectly initialized semi-join led to a wrong result.
+#
+CREATE TABLE STAFF (EMPNUM CHAR(3) NOT NULL,
+EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15));
+CREATE TABLE PROJ (PNUM CHAR(3) NOT NULL,
+PNAME CHAR(20), PTYPE CHAR(6),
+BUDGET DECIMAL(9),
+CITY CHAR(15));
+CREATE TABLE WORKS (EMPNUM CHAR(3) NOT NULL,
+PNUM CHAR(3) NOT NULL, HOURS DECIMAL(5));
+INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale');
+INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna');
+INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna');
+INSERT INTO STAFF VALUES ('E4','Don',12,'Deale');
+INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron');
+INSERT INTO PROJ VALUES ('P1','MXSS','Design',10000,'Deale');
+INSERT INTO PROJ VALUES ('P2','CALM','Code',30000,'Vienna');
+INSERT INTO PROJ VALUES ('P3','SDP','Test',30000,'Tampa');
+INSERT INTO PROJ VALUES ('P4','SDP','Design',20000,'Deale');
+INSERT INTO PROJ VALUES ('P5','IRM','Test',10000,'Vienna');
+INSERT INTO PROJ VALUES ('P6','PAYR','Design',50000,'Deale');
+INSERT INTO WORKS VALUES ('E1','P1',40);
+INSERT INTO WORKS VALUES ('E1','P2',20);
+INSERT INTO WORKS VALUES ('E1','P3',80);
+INSERT INTO WORKS VALUES ('E1','P4',20);
+INSERT INTO WORKS VALUES ('E1','P5',12);
+INSERT INTO WORKS VALUES ('E1','P6',12);
+INSERT INTO WORKS VALUES ('E2','P1',40);
+INSERT INTO WORKS VALUES ('E2','P2',80);
+INSERT INTO WORKS VALUES ('E3','P2',20);
+INSERT INTO WORKS VALUES ('E4','P2',20);
+INSERT INTO WORKS VALUES ('E4','P4',40);
+INSERT INTO WORKS VALUES ('E4','P5',80);
+set optimizer_switch='default,materialization=off';
+explain SELECT EMPNUM, EMPNAME
+FROM STAFF
+WHERE EMPNUM IN
+(SELECT EMPNUM FROM WORKS
+WHERE PNUM IN
+(SELECT PNUM FROM PROJ));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY STAFF ALL NULL NULL NULL NULL 5
+1 PRIMARY PROJ ALL NULL NULL NULL NULL 6
+1 PRIMARY WORKS ALL NULL NULL NULL NULL 12 Using where; FirstMatch(STAFF)
+SELECT EMPNUM, EMPNAME
+FROM STAFF
+WHERE EMPNUM IN
+(SELECT EMPNUM FROM WORKS
+WHERE PNUM IN
+(SELECT PNUM FROM PROJ));
+EMPNUM EMPNAME
+E1 Alice
+E2 Betty
+E3 Carmen
+E4 Don
+set optimizer_switch='default';
+drop table STAFF,WORKS,PROJ;
+# End of bug#45191
set join_cache_level=default;
show variables like 'join_cache_level';
Variable_name Value
=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test 2009-03-19 17:03:58 +0000
+++ b/mysql-test/t/subselect_sj.test 2009-09-01 14:44:35 +0000
@@ -216,4 +216,64 @@ WHERE
HAVING X > '2012-12-12';
drop table t1, t2;
+--echo #
+--echo # Bug#45191: Incorrectly initialized semi-join led to a wrong result.
+--echo #
+CREATE TABLE STAFF (EMPNUM CHAR(3) NOT NULL,
+ EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15));
+
+CREATE TABLE PROJ (PNUM CHAR(3) NOT NULL,
+ PNAME CHAR(20), PTYPE CHAR(6),
+ BUDGET DECIMAL(9),
+ CITY CHAR(15));
+
+CREATE TABLE WORKS (EMPNUM CHAR(3) NOT NULL,
+ PNUM CHAR(3) NOT NULL, HOURS DECIMAL(5));
+INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale');
+INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna');
+INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna');
+INSERT INTO STAFF VALUES ('E4','Don',12,'Deale');
+INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron');
+
+INSERT INTO PROJ VALUES ('P1','MXSS','Design',10000,'Deale');
+INSERT INTO PROJ VALUES ('P2','CALM','Code',30000,'Vienna');
+INSERT INTO PROJ VALUES ('P3','SDP','Test',30000,'Tampa');
+INSERT INTO PROJ VALUES ('P4','SDP','Design',20000,'Deale');
+INSERT INTO PROJ VALUES ('P5','IRM','Test',10000,'Vienna');
+INSERT INTO PROJ VALUES ('P6','PAYR','Design',50000,'Deale');
+
+INSERT INTO WORKS VALUES ('E1','P1',40);
+INSERT INTO WORKS VALUES ('E1','P2',20);
+INSERT INTO WORKS VALUES ('E1','P3',80);
+INSERT INTO WORKS VALUES ('E1','P4',20);
+INSERT INTO WORKS VALUES ('E1','P5',12);
+INSERT INTO WORKS VALUES ('E1','P6',12);
+INSERT INTO WORKS VALUES ('E2','P1',40);
+INSERT INTO WORKS VALUES ('E2','P2',80);
+INSERT INTO WORKS VALUES ('E3','P2',20);
+INSERT INTO WORKS VALUES ('E4','P2',20);
+INSERT INTO WORKS VALUES ('E4','P4',40);
+INSERT INTO WORKS VALUES ('E4','P5',80);
+
+set optimizer_switch='default,materialization=off';
+
+explain SELECT EMPNUM, EMPNAME
+FROM STAFF
+WHERE EMPNUM IN
+ (SELECT EMPNUM FROM WORKS
+ WHERE PNUM IN
+ (SELECT PNUM FROM PROJ));
+
+SELECT EMPNUM, EMPNAME
+FROM STAFF
+WHERE EMPNUM IN
+ (SELECT EMPNUM FROM WORKS
+ WHERE PNUM IN
+ (SELECT PNUM FROM PROJ));
+
+set optimizer_switch='default';
+
+drop table STAFF,WORKS,PROJ;
+
+--echo # End of bug#45191
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-11-04 08:53:57 +0000
+++ b/sql/sql_select.cc 2009-11-09 13:18:48 +0000
@@ -1322,8 +1322,8 @@ int setup_semijoin_dups_elimination(JOIN
jump_to= tab;
else
{
- tab->first_sj_inner_tab= tab;
- tab->last_sj_inner_tab= tab + pos->n_sj_tables - 1;
+ j->first_sj_inner_tab= tab;
+ j->last_sj_inner_tab= tab + pos->n_sj_tables - 1;
}
}
j[-1].do_firstmatch= jump_to;
@@ -10095,15 +10095,19 @@ uint check_join_cache_usage(JOIN_TAB *ta
*/
if (tab->use_quick == 2)
goto no_join_cache;
-
+ /*
+ Use join cache with FirstMatch semi-join strategy only when semi-join
+ contains only one table.
+ */
+ if (tab->is_inner_table_of_semi_join_with_first_match() &&
+ !tab->is_single_inner_of_semi_join_with_first_match())
+ goto no_join_cache;
/*
Non-linked join buffers can't guarantee one match
*/
if (force_unlinked_cache &&
- ((tab->is_inner_table_of_semi_join_with_first_match() &&
- !tab->is_single_inner_of_semi_join_with_first_match()) ||
- (tab->is_inner_table_of_outer_join() &&
- !tab->is_single_inner_of_outer_join())))
+ (tab->is_inner_table_of_outer_join() &&
+ !tab->is_single_inner_of_outer_join()))
goto no_join_cache;
/*
Attachment: [text/bzr-bundle] bzr/epotemkin@mysql.com-20091109132131-ad1gk2d2tn9o5i3l.bundle
| Thread |
|---|
| • bzr push into mysql-6.0-codebase-bugfixing branch (epotemkin:3701) | Evgeny Potemkin | 9 Nov |
