From 0fca2269425df6a1e2224bb3185a76d3a2608fbf Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Tue, 23 Aug 2011 15:13:17 +0200 Subject: Bug#11766879/Bug#60106: DIFF BETWEEN # OF INDEXES IN MYSQL VS INNODB, PARTITONING, ON INDEX CREATE If the first partition succeeded in adding a index, but a successive partition failed, then the first partition had still the new index. The fix reverts the added indexes from previous partitions on failure. --- mysql-test/t/partition_innodb_plugin.test | 27 +++++++++++++++++++++++++-- 1 file changed, 25 insertions(+), 2 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/partition_innodb_plugin.test b/mysql-test/t/partition_innodb_plugin.test index 626e5d19b99..efcf5c282a7 100644 --- a/mysql-test/t/partition_innodb_plugin.test +++ b/mysql-test/t/partition_innodb_plugin.test @@ -1,10 +1,33 @@ --source include/have_partition.inc --source include/have_innodb_plugin.inc -# Remove the line below when bug#53307 is solved. ---source include/not_valgrind.inc let $MYSQLD_DATADIR= `SELECT @@datadir`; +--echo # +--echo # Bug#11766879/Bug#60106: DIFF BETWEEN # OF INDEXES IN MYSQL VS INNODB, +--echo # PARTITONING, ON INDEX CREATE +--echo # +CREATE TABLE t1 ( + id bigint NOT NULL AUTO_INCREMENT, + time date, + id2 bigint not null, + PRIMARY KEY (id,time) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +/*!50100 PARTITION BY RANGE(TO_DAYS(time)) +(PARTITION p10 VALUES LESS THAN (734708) ENGINE = InnoDB, + PARTITION p20 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */; + +INSERT INTO t1 (time,id2) VALUES ('2011-07-24',1); +INSERT INTO t1 (time,id2) VALUES ('2011-07-25',1); +INSERT INTO t1 (time,id2) VALUES ('2011-07-25',1); + +--error ER_DUP_ENTRY +CREATE UNIQUE INDEX uk_time_id2 on t1(time,id2); + +SELECT COUNT(*) FROM t1; + +DROP TABLE t1; + call mtr.add_suppression("nnoDB: Error: table `test`.`t1` .* Partition.* InnoDB internal"); --echo # --echo # Bug#55091: Server crashes on ADD PARTITION after a failed attempt -- cgit v1.2.1 From 8932ae216674f08880a6f914b9651262037175be Mon Sep 17 00:00:00 2001 From: Tatjana Azundris Nuernberg Date: Thu, 29 Sep 2011 10:47:11 +0100 Subject: Bug#11765687 (MySQL58677): No privilege on table / view, but can know #rows / underlying table's name 1 - If a user had SHOW VIEW and SELECT privileges on a view and this view was referencing another view, EXPLAIN SELECT on the outer view (that the user had privileges on) could reveal the structure of the underlying "inner" view as well as the number of rows in the underlying tables, even if the user had privileges on none of these referenced objects. This happened because we used DEFINER's UID ("SUID") not just for the view given in EXPLAIN, but also when checking privileges on the underlying views (where we should use the UID of the EXPLAIN's INVOKER instead). We no longer run the EXPLAIN SUID (with DEFINER's privileges). This prevents a possible exploit and makes permissions more orthogonal. 2 - EXPLAIN SELECT would reveal a view's structure even if the user did not have SHOW VIEW privileges for that view, as long as they had SELECT privilege on the underlying tables. Instead of requiring both SHOW VIEW privilege on a view and SELECT privilege on all underlying tables, we were checking for presence of either of them. We now explicitly require SHOW VIEW and SELECT privileges on the view we run EXPLAIN SELECT on, as well as all its underlying views. We also require SELECT on all relevant tables. mysql-test/r/view_grant.result: add extensive tests to illustrate desired behavior and prevent regressions (as always). mysql-test/t/view_grant.test: add extensive tests to illustrate desired behavior and prevent regressions (as always). sql/sql_view.cc: We no longer run the EXPLAIN SUID (with DEFINER's privileges). To achieve this, we use a temporary, SUID-less TABLE_LIST for the views while checking privileges. --- mysql-test/t/view_grant.test | 362 ++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 358 insertions(+), 4 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index ff17cde5184..f06f0d58c8a 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -121,21 +121,26 @@ create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1; create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2; create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2; +# v5: SHOW VIEW, but no SELECT +create view mysqltest.v5 (c,d) as select a+1,b+1 from mysqltest.t1; grant select on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.v2 to mysqltest_1@localhost; grant select on mysqltest.v3 to mysqltest_1@localhost; grant select on mysqltest.v4 to mysqltest_1@localhost; +grant show view on mysqltest.v5 to mysqltest_1@localhost; connection user1; -# all selects works +# all SELECTs works, except v5 which lacks SELECT privs select c from mysqltest.v1; select c from mysqltest.v2; select c from mysqltest.v3; select c from mysqltest.v4; +--error ER_TABLEACCESS_DENIED_ERROR +select c from mysqltest.v5; # test of show coluns show columns from mysqltest.v1; show columns from mysqltest.v2; -# but explain/show do not +# explain/show fail --error ER_VIEW_NO_EXPLAIN explain select c from mysqltest.v1; --error ER_TABLEACCESS_DENIED_ERROR @@ -152,15 +157,26 @@ show create view mysqltest.v3; explain select c from mysqltest.v4; --error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v4; +--error ER_TABLEACCESS_DENIED_ERROR +explain select c from mysqltest.v5; +show create view mysqltest.v5; +# missing SELECT on underlying t1, no SHOW VIEW on v1 either. +--error ER_VIEW_NO_EXPLAIN +explain select c from mysqltest.v1; +# missing SHOW VIEW +--error ER_TABLEACCESS_DENIED_ERROR +show create view mysqltest.v1; # allow to see one of underlying table connection root; +grant show view on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.t1 to mysqltest_1@localhost; connection user1; -# EXPLAIN of view on above table works +# EXPLAIN works explain select c from mysqltest.v1; ---error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v1; +# missing SHOW VIEW +--error ER_VIEW_NO_EXPLAIN explain select c from mysqltest.v2; --error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v2; @@ -173,6 +189,11 @@ show create view mysqltest.v3; explain select c from mysqltest.v4; --error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v4; +# we have SHOW VIEW on v5, and SELECT on t1 -- not enough +--error ER_TABLEACCESS_DENIED_ERROR +explain select c from mysqltest.v5; +# we can SHOW CREATE VIEW though +show create view mysqltest.v5; # allow to see any view in mysqltest database connection root; @@ -182,8 +203,12 @@ explain select c from mysqltest.v1; show create view mysqltest.v1; explain select c from mysqltest.v2; show create view mysqltest.v2; +# have SHOW VIEW | SELECT on v3, but no SELECT on t2 +--error ER_VIEW_NO_EXPLAIN explain select c from mysqltest.v3; show create view mysqltest.v3; +# have SHOW VIEW | SELECT on v4, but no SELECT on t2 +--error ER_VIEW_NO_EXPLAIN explain select c from mysqltest.v4; show create view mysqltest.v4; @@ -1232,7 +1257,336 @@ DROP TABLE db1.t1; DROP DATABASE db1; connection default; + + +--echo Bug #11765687/#58677: +--echo No privilege on table/view, but can know #rows / underlying table's name + +# As a root-like user +connect (root,localhost,root,,test); +connection root; + +create database mysqltest1; +create table mysqltest1.t1 (i int); +create table mysqltest1.t2 (j int); +create table mysqltest1.t3 (k int, secret int); + +create user alice@localhost; +create user bob@localhost; +create user cecil@localhost; +create user dan@localhost; +create user eugene@localhost; +create user fiona@localhost; +create user greg@localhost; +create user han@localhost; +create user inga@localhost; +create user jamie@localhost; +create user karl@localhost; +create user lena@localhost; +create user mhairi@localhost; +create user noam@localhost; +create user olga@localhost; +create user pjotr@localhost; +create user quintessa@localhost; + +grant all privileges on mysqltest1.* to alice@localhost with grant option; + +# +--echo ... as alice +connect (test11765687,localhost,alice,,mysqltest1); +connection test11765687; + +create view v1 as select * from t1; +create view v2 as select * from v1, t2; +create view v3 as select k from t3; + +grant select on mysqltest1.v1 to bob@localhost; + +grant show view on mysqltest1.v1 to cecil@localhost; + +grant select, show view on mysqltest1.v1 to dan@localhost; +grant select on mysqltest1.t1 to dan@localhost; + +grant select on mysqltest1.* to eugene@localhost; + +grant select, show view on mysqltest1.v2 to fiona@localhost; + +grant select, show view on mysqltest1.v2 to greg@localhost; +grant show view on mysqltest1.v1 to greg@localhost; + +grant select(k) on mysqltest1.t3 to han@localhost; +grant select, show view on mysqltest1.v3 to han@localhost; + +grant select on mysqltest1.t1 to inga@localhost; +grant select on mysqltest1.t2 to inga@localhost; +grant select on mysqltest1.v1 to inga@localhost; +grant select, show view on mysqltest1.v2 to inga@localhost; + +grant select on mysqltest1.t1 to jamie@localhost; +grant select on mysqltest1.t2 to jamie@localhost; +grant show view on mysqltest1.v1 to jamie@localhost; +grant select, show view on mysqltest1.v2 to jamie@localhost; + +grant select on mysqltest1.t1 to karl@localhost; +grant select on mysqltest1.t2 to karl@localhost; +grant select, show view on mysqltest1.v1 to karl@localhost; +grant select on mysqltest1.v2 to karl@localhost; + +grant select on mysqltest1.t1 to lena@localhost; +grant select on mysqltest1.t2 to lena@localhost; +grant select, show view on mysqltest1.v1 to lena@localhost; +grant show view on mysqltest1.v2 to lena@localhost; + +grant select on mysqltest1.t1 to mhairi@localhost; +grant select on mysqltest1.t2 to mhairi@localhost; +grant select, show view on mysqltest1.v1 to mhairi@localhost; +grant select, show view on mysqltest1.v2 to mhairi@localhost; + +grant select on mysqltest1.t1 to noam@localhost; +grant select, show view on mysqltest1.v1 to noam@localhost; +grant select, show view on mysqltest1.v2 to noam@localhost; + +grant select on mysqltest1.t2 to olga@localhost; +grant select, show view on mysqltest1.v1 to olga@localhost; +grant select, show view on mysqltest1.v2 to olga@localhost; + +grant select on mysqltest1.t1 to pjotr@localhost; +grant select on mysqltest1.t2 to pjotr@localhost; +grant select, show view on mysqltest1.v2 to pjotr@localhost; + +grant select, show view on mysqltest1.v1 to quintessa@localhost; + +disconnect test11765687; + +# +--echo ... as bob +connect (test11765687,localhost,bob,,mysqltest1); +connection test11765687; + +select * from v1; # Should succeed. +--error ER_VIEW_NO_EXPLAIN +explain select * from v1; # fail, no SHOW_VIEW + +disconnect test11765687; + +# +--echo ... as cecil +connect (test11765687,localhost,cecil,,mysqltest1); +connection test11765687; + +--error ER_TABLEACCESS_DENIED_ERROR +select * from v1; # fail, no SELECT +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from v1; # fail, no SELECT + +disconnect test11765687; + +# +--echo ... as dan +connect (test11765687,localhost,dan,,mysqltest1); +connection test11765687; + +select * from v1; # Should succeed. +explain select * from v1; # Should succeed. + +disconnect test11765687; + +# +--echo ... as eugene +connect (test11765687,localhost,eugene,,mysqltest1); +connection test11765687; + +select * from v1; # Should succeed. +--error ER_VIEW_NO_EXPLAIN +explain select * from v1; # fail, no SHOW_VIEW + +disconnect test11765687; + +# +--echo ... as fiona +connect (test11765687,localhost,fiona,,mysqltest1); +connection test11765687; + +select * from v2; # Should succeed. +show create view v2; # Should succeed, but... +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from t1; # fail, shouldn't see t1! +--error ER_TABLEACCESS_DENIED_ERROR +# err msg must give view name, no table names!! +explain select * from v1; # fail, have no privs on v1! +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from t2; # fail, have no privs on t2! +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; # fail, shouldn't see t2! + +disconnect test11765687; + +# +--echo ... as greg +connect (test11765687,localhost,greg,,mysqltest1); +connection test11765687; + +select * from v2; # Should succeed. +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from v1; # fail; no SELECT on v1! +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; # fail; no SELECT on v1! + +disconnect test11765687; + +# +--echo ... as han +connect (test11765687,localhost,han,,mysqltest1); +connection test11765687; + +--error ER_TABLEACCESS_DENIED_ERROR +select * from t3; # don't have privs on all columns, +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from t3; # so EXPLAIN on "forbidden" columns should fail. +select k from t3; # but we do have SELECT on column k though, +explain select k from t3; # so EXPLAIN just on k should work, +select * from v3; # and so should SELECT on view only using allowed columns +explain select * from v3; # as should the associated EXPLAIN + +disconnect test11765687; + +# +--echo ... as inga +connect (test11765687,localhost,inga,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel on t1/t2, only sel v1 +# fail: lacks show on v1 +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as jamie +connect (test11765687,localhost,jamie,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel on t1/t2, only show v1 +# fail: lacks sel on v1 +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as karl +connect (test11765687,localhost,karl,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel only on v2, sel on t1/t2, sel/show v1 +# fail: lacks show on v2 +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as lena + +connect (test11765687,localhost,lena,,mysqltest1); +connection test11765687; +--error ER_TABLEACCESS_DENIED_ERROR +select * from v2; +# has show only on v2, sel on t1/t2, sel/show v1 +# fail: lacks sel on v2 +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from v2; +disconnect test11765687; + +# +--echo ... as mhairi +connect (test11765687,localhost,mhairi,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel on t1/t2, sel/show v1 +explain select * from v2; +disconnect test11765687; + +# +--echo ... as noam +connect (test11765687,localhost,noam,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel only on t1, sel/show v1 (no sel on t2!) +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as olga +connect (test11765687,localhost,olga,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel only on t2, sel/show v1 (no sel on t1!) +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as pjotr +connect (test11765687,localhost,pjotr,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel only on t2, nothing on v1 +# fail: lacks show on v1 +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as quintessa +connect (test11765687,localhost,quintessa,,mysqltest1); +connection test11765687; + +select * from v1; # Should succeed. +--error ER_VIEW_NO_EXPLAIN +explain select * from v1; # fail: lacks select on t1 + +disconnect test11765687; + +# cleanup + +# +--echo ... as root again at last: clean-up time! +connection root; + +drop user alice@localhost; +drop user bob@localhost; +drop user cecil@localhost; +drop user dan@localhost; +drop user eugene@localhost; +drop user fiona@localhost; +drop user greg@localhost; +drop user han@localhost; +drop user inga@localhost; +drop user jamie@localhost; +drop user karl@localhost; +drop user lena@localhost; +drop user mhairi@localhost; +drop user noam@localhost; +drop user olga@localhost; +drop user pjotr@localhost; +drop user quintessa@localhost; + +drop database mysqltest1; + +disconnect root; + +connection default; + --echo End of 5.0 tests. + + # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc -- cgit v1.2.1 From a6145f4b62bd264ad32d2dade98eda0cc6de0dba Mon Sep 17 00:00:00 2001 From: Tor Didriksen Date: Fri, 14 Oct 2011 10:09:53 +0200 Subject: Bug#12563865 ROUNDED,TMP_BUF,DECIMAL_VALUE STACK CORRUPTION IN ALL VERSIONS >=5.0 Buffer over-run on all platforms, crash on windows, wrong result on other platforms, when rounding numbers which start with 999999999 and have precision = 9 or 18 or 27 or 36 ... mysql-test/r/type_newdecimal.result: New test cases. mysql-test/t/type_newdecimal.test: New test cases. sql/my_decimal.h: Add sanity checking code, to catch buffer over/under-run. strings/decimal.c: The original initialization of intg1 (add 1 if buf[0] == DIG_MAX) will set p1 to point outside the buffer, and the loop to copy the original value while (buf0 < p0) *(--p1) = *(--p0); will overwrite memory outside the my_decimal object. --- mysql-test/t/type_newdecimal.test | 21 +++++++++++++++++++++ 1 file changed, 21 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index a5331582df6..a55951caf63 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1235,4 +1235,25 @@ show create table t1; select * from t1; DROP TABLE t1; +--echo # +--echo # Bug#12563865 +--echo # ROUNDED,TMP_BUF,DECIMAL_VALUE STACK CORRUPTION IN ALL VERSIONS >=5.0 +--echo # + +let $nine_81= +999999999999999999999999999999999999999999999999999999999999999999999999999999999; + +eval SELECT substring(('M') FROM ($nine_81)) AS foo; +eval SELECT min($nine_81) AS foo; +eval SELECT multipolygonfromtext(('4294967294.1'),($nine_81)) AS foo; +eval SELECT convert(($nine_81), decimal(30,30)) AS foo; +eval SELECT bit_xor($nine_81) AS foo; +eval SELECT -($nine_81) AS foo; +eval SELECT date_sub(($nine_81), + interval ((SELECT date_add((0x77500000), + interval ('Oml') second))) + day_minute) +AS foo; +eval SELECT truncate($nine_81, 28) AS foo; + --echo End of 5.0 tests -- cgit v1.2.1 From 14dff92d58569657c126bc8cc8755d0341581ea4 Mon Sep 17 00:00:00 2001 From: Bjorn Munch Date: Wed, 19 Oct 2011 13:36:57 +0200 Subject: Remove copyright header from parser_stack.test --- mysql-test/t/parser_stack.test | 15 --------------- 1 file changed, 15 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/parser_stack.test b/mysql-test/t/parser_stack.test index e58459044f0..f8291ce8766 100644 --- a/mysql-test/t/parser_stack.test +++ b/mysql-test/t/parser_stack.test @@ -1,18 +1,3 @@ -# Copyright (c) 2008 MySQL AB -# -# This program is free software; you can redistribute it and/or modify -# it under the terms of the GNU General Public License as published by -# the Free Software Foundation; version 2 of the License. -# -# This program is distributed in the hope that it will be useful, -# but WITHOUT ANY WARRANTY; without even the implied warranty of -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -# GNU General Public License for more details. -# -# You should have received a copy of the GNU General Public License -# along with this program; if not, write to the Free Software -# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA - # # These tests are designed to cause an internal parser stack overflow, # and trigger my_yyoverflow(). -- cgit v1.2.1 From de8c70e70b3fd335e1d34a5635fc4357adb43d95 Mon Sep 17 00:00:00 2001 From: Sergey Vojtovich Date: Thu, 20 Oct 2011 15:03:22 +0400 Subject: BUG#11757032 - 49030: OPTIMIZE TABLE BREAKS MYISAM TABLE WHEN USING MYISAM_USE_MMAP ON WINDOWS When OPTIMIZE/REPAIR TABLE is switching to new data file, old data file is removed while memory mapping is still active. With 5.1 implementation of nt_share_delete() it is not permitted to remove mmaped file. This fix disables memory mapping for mi_repair() operations. mysql-test/r/myisam.result: A test case for BUG#11757032. mysql-test/t/myisam.test: A test case for BUG#11757032. storage/myisam/ha_myisam.cc: mi_repair*() functions family use file I/O even if memory mapping is available. Since mixing mmap I/O and file I/O may cause various artifacts, memory mapping must be disabled. storage/myisam/mi_delete_all.c: Clean-up: do not attempt to remap file after truncate, since there is nothing to map. --- mysql-test/t/myisam.test | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 4abd7dd2b1b..5340ddcaa48 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -1645,4 +1645,22 @@ DROP TABLE t1; SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; SET myisam_repair_threads=@@global.myisam_repair_threads; +--echo # +--echo # BUG#11757032 - 49030: OPTIMIZE TABLE BREAKS MYISAM TABLE WHEN +--echo # USING MYISAM_USE_MMAP ON WINDOWS +--echo # +SET GLOBAL myisam_use_mmap=1; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(1),(2); +DELETE FROM t1 WHERE a=1; +FLUSH TABLE t1; +LOCK TABLE t1 WRITE; +OPTIMIZE TABLE t1; +INSERT INTO t1 VALUES(3); +UNLOCK TABLES; +SELECT * FROM t1; +CHECK TABLE t1; +DROP TABLE t1; +SET GLOBAL myisam_use_mmap=default; + --echo End of 5.1 tests -- cgit v1.2.1 From 7f746fbe74e08d79217bdf7c7cba628e3b6bef85 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sat, 12 Nov 2011 20:50:11 +0200 Subject: BUG#887468: Second assertion `keypart_map' failed in maria_rkey with semijoin - in advance_sj_state: Do not try to construct LooseScan strategy if we're already behind the last LooseScan table. --- mysql-test/t/subselect_sj_aria.test | 76 +++++++++++++++++++++++++++++++++++++ 1 file changed, 76 insertions(+) create mode 100644 mysql-test/t/subselect_sj_aria.test (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj_aria.test b/mysql-test/t/subselect_sj_aria.test new file mode 100644 index 00000000000..806688b3f87 --- /dev/null +++ b/mysql-test/t/subselect_sj_aria.test @@ -0,0 +1,76 @@ +# +# Semi-join tests that require Aria +# +--disable_warnings +drop table if exists t1,t2,t3,t4; +--enable_warnings + + +--echo # +--echo # BUG#887468: Second assertion `keypart_map' failed in maria_rkey with semijoin +--echo # + +CREATE TABLE t1 ( + pk int(11) NOT NULL AUTO_INCREMENT, + col_int_key int(11) DEFAULT NULL, + col_varchar_key varchar(1) DEFAULT NULL, + dummy char(30), + PRIMARY KEY (pk), + KEY col_varchar_key (col_varchar_key,col_int_key) +) ENGINE=Aria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; + +INSERT INTO t1 (pk, col_varchar_key, col_int_key) VALUES +(10,NULL,0), (11,'d',4), (12,'g',8), (13,'x',NULL), (14,'f',NULL), +(15,'p',0), (16,'j',NULL), (17,'c',8), (18,'z',8), (19,'j',6), (20,NULL,2), +(21,'p',3), (22,'w',1), (23,'c',NULL), (24,'j',1), (25,'f',10), (26,'v',2), +(27,'f',103), (28,'q',3), (29,'y',6); + +CREATE TABLE t2 ( + pk int(11) NOT NULL AUTO_INCREMENT, + col_int_key int(11) DEFAULT NULL, + dummy char(36), + PRIMARY KEY (pk), + KEY col_int_key (col_int_key) +) ENGINE=Aria AUTO_INCREMENT=101 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; + +INSERT INTO t2 ( pk, col_int_key) VALUES +(1,8), (2,2), (3,9), (4,6), (5,NULL), (6,NULL), (7,48), (8,228), (9,3), (10,5), +(11,39), (12,6), (13,8), (14,3), (15,NULL), (16,2), (17,6), (18,3), (19,1), (20,4), +(21,3), (22,1), (23,NULL), (24,97), (25,0), (26,0), (27,9), (28,5), (29,9), (30,0), +(31,2), (32,172), (33,NULL), (34,5), (35,119), (36,1), (37,4), (38,8), (39,NULL), (40,6), +(41,5), (42,5), (43,1), (44,7), (45,2), (46,8), (47,9), (48,NULL), (49,NULL), (50,3), +(51,172), (52,NULL), (53,6), (54,6), (55,5), (56,4), (57,3), (58,2), (59,7), (60,4), +(61,6), (62,0), (63,8), (64,5), (65,8), (66,2), (67,9), (68,7), (69,5), (70,7), +(71,0), (72,4), (73,3), (74,1), (75,0), (76,6), (77,2), (78,NULL), (79,8), (80,NULL), +(81,NULL), (82,NULL), (83,3), (84,7), (85,3), (86,5), (87,5), (88,1), (89,2), (90,1), +(91,7), (92,1), (93,9), (94,9), (95,8), (96,3), (97,7), (98,4), (99,9), (100,0); + +CREATE TABLE t3 ( + pk int(11) NOT NULL AUTO_INCREMENT, + dummy char(34), + col_varchar_key varchar(1) DEFAULT NULL, + col_int_key int(11) DEFAULT NULL, + PRIMARY KEY (pk), + KEY col_varchar_key (col_varchar_key,col_int_key) +) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; + +INSERT INTO t3 (pk, col_varchar_key) VALUES (1,'v'), (2,'c'), (3,NULL); + +CREATE TABLE t4 ( + pk int(11) NOT NULL AUTO_INCREMENT, + dummy char (38), + PRIMARY KEY (pk) +) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; + +INSERT INTO t4 (pk) VALUES (1), (2), (3); + +SELECT * +FROM t1 +JOIN t2 +ON ( t2.col_int_key = t1.pk ) +WHERE t1.col_varchar_key IN ( + SELECT t3.col_varchar_key FROM t3, t4 +); + +drop table t1, t2, t3, t4; + -- cgit v1.2.1 From ef45b799245d4c33770339619dab4d717b1e552f Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Tue, 15 Nov 2011 13:14:54 +0200 Subject: Fix for lp:780425 sql_buffer_result=1 gives wrong result for GROUP BY with a +# constant expression" mysql-test/r/select.result: Test case for lp:780425 mysql-test/r/select_pkeycache.result: lp:780425 mysql-test/t/select.test: lp:780425 sql/sql_select.cc: Added DBUG_ASSERT to be prove some logic and later be able to simplify the code Set implicit_grouping if we delete a GROUP BY to signal do_select() that a grouping needs to be done. --- mysql-test/t/select.test | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 1139e7acc60..ef1894a5405 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -4121,4 +4121,21 @@ SET SESSION join_buffer_size = DEFAULT; DROP TABLE t1,t2,t3,t4,t5,t6; +# +# Bug #780425: "sql_buffer_result=1 gives wrong result for GROUP BY with a +# constant expression" +# + +CREATE TABLE t1(f1 int UNSIGNED) engine=myisam; +INSERT INTO t1 VALUES (3),(2),(1); +set sql_buffer_result=0; +SELECT f1 FROM t1 GROUP BY 1; +SELECT f1 FROM t1 GROUP BY '123' = 'abc'; +SELECT 1 FROM t1 GROUP BY 1; +set sql_buffer_result=1; +SELECT f1 FROM t1 GROUP BY 1; +SELECT f1 FROM t1 GROUP BY '123' = 'abc'; +SELECT 1 FROM t1 GROUP BY 1; +drop table t1; + --echo End of 5.1 tests -- cgit v1.2.1 From c8768a091ac2d876216582813aaab7d9663008f7 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Wed, 23 Nov 2011 10:25:27 +0200 Subject: Fixes of testcases after merge with MySQL 5.1.59 --- mysql-test/t/endspace.test | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/endspace.test b/mysql-test/t/endspace.test index b223c683cde..072000ce3cc 100644 --- a/mysql-test/t/endspace.test +++ b/mysql-test/t/endspace.test @@ -93,7 +93,9 @@ alter table t1 modify text1 text not null, pack_keys=1; select * from t1 where text1 like 'teststring_%'; # The following gives wrong result in InnoDB +--sorted_result select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%'; +--sorted_result select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t'; select concat('|', text1, '|') from t1 order by text1; drop table t1; -- cgit v1.2.1 From fd3295e0acb782790eb185352a401473fd4eba99 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 23 Nov 2011 23:13:51 +0200 Subject: Fix bug lp:893486 Analysis: The bug is a result of an incomplete fix for bug lp:869036. That fix didn't take into account that there may be a case when ther are no NULLs in the materialized subquery, however all columns without NULLs may not be grouped in the only non-null index. This is the case when the left subquery expression has nullable columns. Solution: The patch handles two missing sub-cases of the case when there are no value (non-null matches) for any outer expression, and there are both NULLs and non-NUll values in the outer reference. a) If the materialized subquery contains no NULLs there cannot be a partial match, because there are no NULLs in those columns where the outer reference has no NULLs. b) If the materialized subquery contains NULLs, but there exists a column, such that its corresponding outer expression has no NULL, and this column also has no NULL. Then there cannot be a partial match either. --- mysql-test/t/subselect_partial_match.test | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_partial_match.test b/mysql-test/t/subselect_partial_match.test index be78360c76b..45386efd266 100644 --- a/mysql-test/t/subselect_partial_match.test +++ b/mysql-test/t/subselect_partial_match.test @@ -775,4 +775,26 @@ SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq); drop table outer_sq, inner_sq; +--echo # +--echo # LP BUG#893486 Wrong result with partial_match_rowid_merge , NOT IN , NULLs +--echo # + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (0,NULL),(2,NULL); + +CREATE TABLE t2 (c int, d int); +INSERT INTO t2 VALUES (2,3),(4,5),(6, NULL); + +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,in_to_exists=off'; + +EXPLAIN SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT c, d FROM t2); +SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT c, d FROM t2); +SELECT a, b, (a, b) NOT IN (SELECT c, d FROM t2) subq_res FROM t1; + +EXPLAIN SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT c, d FROM t2 WHERE d is not NULL); +SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT c, d FROM t2 WHERE d is not NULL); +SELECT a, b, (a, b) NOT IN (SELECT c, d FROM t2 WHERE d is not NULL) subq_res FROM t1; + +drop table t1,t2; + set @@optimizer_switch=@save_optimizer_switch; -- cgit v1.2.1 From 7b08d996277a5019f1e357f595ba78a3455841cc Mon Sep 17 00:00:00 2001 From: Alexey Botchkov Date: Thu, 24 Nov 2011 16:26:13 +0400 Subject: fixes to make compilers happy. per-file comments: mysql-test/t/gis-precise.test number-to-string conversion differs on Windows. Have to tolerate this while GIS data is stored in doubles. sql/spatial.cc prev_x initialization added. --- mysql-test/t/gis-precise.test | 3 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/gis-precise.test b/mysql-test/t/gis-precise.test index 1de8726a7bc..b48da25c3c9 100644 --- a/mysql-test/t/gis-precise.test +++ b/mysql-test/t/gis-precise.test @@ -78,6 +78,7 @@ select astext(ST_symdifference(geomfromtext('polygon((0 0, 1 0, 0 1, 0 0))'), ge select astext(ST_UNION(GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), GeomFromText('LINESTRING(-10 -10, 200 200, 199 201, -11 -9)'))); # Buffer() tests +--replace_result 0012045437948276 00120454379482759 select astext(ST_buffer(geometryfromtext('point(1 1)'), 1)); create table t1(geom geometrycollection); insert into t1 values (geomfromtext('POLYGON((0 0, 10 10, 0 8, 0 0))')); @@ -87,6 +88,7 @@ select astext(ST_buffer(geom,2)) from t1; set @geom=geomfromtext('LINESTRING(2 1, 4 2, 2 3, 2 5)'); set @buff=ST_buffer(@geom,1); +--replace_result 40278744502097 40278744502096 select astext(@buff); # cleanup @@ -133,6 +135,7 @@ SELECT ASTEXT(ST_INTERSECTION( #bug 804324 Assertion 0 in Gcalc_scan_iterator::pop_suitable_intersection +--replace_result 61538461538462 61538461538461 SELECT ASTEXT(ST_UNION( MULTILINESTRINGFROMTEXT('MULTILINESTRING((6 2,4 0,3 5,3 6,4 3,6 4,3 9,0 7,3 7,8 4,2 9,5 0), (8 2,1 3,9 0,4 4))'), -- cgit v1.2.1 From 6fbf8f1926851ecc377b70ad1313b1d213d51010 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 24 Nov 2011 15:12:10 +0200 Subject: Fix for LP BUG#859375 and LP BUG#887458. Stop attempts to apply IN/ALL/ANY optimizations to so called "fake_select" (used for ordering and filtering results of union) in union subquery execution. --- mysql-test/t/subselect.test | 56 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 56 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index e8a778232bc..a39b7ea49b8 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -4778,4 +4778,60 @@ set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquer select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1))))))))))))))))))))))))))))); drop table t1; +--echo # +--echo # LP bug #859375: Assertion `0' failed in st_select_lex_unit::optimize +--echo # with view , UNION and prepared statement (rewriting fake_select +--echo # condition). +--echo # + +CREATE TABLE t1 ( f1 int NOT NULL, f4 varchar(1) NOT NULL) ; +INSERT INTO t1 VALUES (6,'d'),(7,'y'); + +CREATE TABLE t2 ( f1 int NOT NULL, f2 int NOT NULL) ; +INSERT INTO t2 VALUES (10,7); + +CREATE VIEW v2 AS SELECT * FROM t2; + +PREPARE st1 FROM " + SELECT * + FROM t1 + LEFT JOIN v2 ON ( v2.f2 = t1.f1 ) + WHERE v2.f1 NOT IN ( + SELECT 1 UNION + SELECT 247 + ) +"; + +EXECUTE st1; +deallocate prepare st1; + +DROP VIEW v2; +DROP TABLE t1,t2; + +--echo # +--echo # LP bug #887458 Crash in subselect_union_engine::no_rows with +--echo # double UNION and join_cache_level=3,8 +--echo # (IN/ALL/ANY optimizations should not be applied to fake_select) + +CREATE TABLE t2 ( a int, b varchar(1)) ; +INSERT IGNORE INTO t2 VALUES (8,'y'),(8,'y'); + +CREATE TABLE t1 ( b varchar(1)) ; +INSERT IGNORE INTO t1 VALUES (NULL),(NULL); + +set @save_join_cache_level=@@join_cache_level; +SET SESSION join_cache_level=3; + +SELECT * +FROM t1, t2 +WHERE t2.b IN ( + SELECT 'm' UNION + SELECT 'm' +) OR t1.b <> SOME ( + SELECT 'v' UNION + SELECT 't' +); + +set @@join_cache_level= @save_join_cache_level; +drop table t1,t2; set optimizer_switch=@subselect_tmp; -- cgit v1.2.1 From d26aefb0775048128495eaab151ee4118f8f7afd Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Thu, 24 Nov 2011 16:04:19 +0200 Subject: Fixes for build failuers found by buildbot mysql-test/mysql-test-run.pl: Rename MYSQLD -> MYSQLD_SIMPLE_CMD to avoid conflict with new MYSQLD variable from MySQL 5.1 mysql-test/r/innodb_file_format.result: Remove old duplicated test mysql-test/suite/pbxt/r/endspace.result: Update test to last version mysql-test/suite/pbxt/r/heap.result: Removed heap test (not part of pbxt) mysql-test/suite/pbxt/r/select_safe.result: Updated results after error message change mysql-test/suite/pbxt/r/view_grant.result: Removed view test (not part of pbxt) mysql-test/suite/pbxt/t/endspace.test: Update test to last version mysql-test/suite/pbxt/t/heap.test: Removed heap test (not part of pbxt) mysql-test/suite/pbxt/t/view_grant.test: Removed view test (not part of pbxt) mysql-test/t/innodb_file_format.test: Remove old duplicated test mysql-test/t/mysqld_option_err.test: Use renamed variable sql/my_decimal.h: Fixed wrong define storage/maria/ma_loghandler.c: Fixed compiler warning --- mysql-test/t/innodb_file_format.test | 31 ------------------------------- mysql-test/t/mysqld_option_err.test | 14 +++++++------- 2 files changed, 7 insertions(+), 38 deletions(-) delete mode 100644 mysql-test/t/innodb_file_format.test (limited to 'mysql-test/t') diff --git a/mysql-test/t/innodb_file_format.test b/mysql-test/t/innodb_file_format.test deleted file mode 100644 index 6de026ad97f..00000000000 --- a/mysql-test/t/innodb_file_format.test +++ /dev/null @@ -1,31 +0,0 @@ --- source include/have_innodb.inc - -call mtr.add_suppression("InnoDB: invalid innodb_file_format_check value"); - -select @@innodb_file_format; -select @@innodb_file_format_check; -set global innodb_file_format=antelope; -set global innodb_file_format=barracuda; ---error ER_WRONG_ARGUMENTS -set global innodb_file_format=cheetah; -select @@innodb_file_format; -set global innodb_file_format=default; -select @@innodb_file_format; ---error ER_WRONG_ARGUMENTS -set global innodb_file_format=on; ---error ER_WRONG_ARGUMENTS -set global innodb_file_format=off; -select @@innodb_file_format; -set global innodb_file_format_check=antelope; -set global innodb_file_format_check=barracuda; ---error ER_WRONG_ARGUMENTS -set global innodb_file_format_check=cheetah; -select @@innodb_file_format_check; -set global innodb_file_format_check=default; -select @@innodb_file_format_check; ---error ER_WRONG_ARGUMENTS -set global innodb_file_format=on; ---error ER_WRONG_ARGUMENTS -set global innodb_file_format=off; -select @@innodb_file_format_check; -set global innodb_file_format_check=antelope; diff --git a/mysql-test/t/mysqld_option_err.test b/mysql-test/t/mysqld_option_err.test index 9c02dec51e6..5e35f924b15 100644 --- a/mysql-test/t/mysqld_option_err.test +++ b/mysql-test/t/mysqld_option_err.test @@ -21,22 +21,22 @@ mkdir $MYSQLTEST_VARDIR/tmp/mysqld_option_err; --echo Test that unknown option is not silently ignored. --error 2 ---exec $MYSQLD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --nonexistentoption >$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 +--exec $MYSQLD_SIMPLE_CMD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --nonexistentoption --loose-skip-innodb --loose-skip-pbxt >$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 --echo Test bad binlog format. --error 1 ---exec $MYSQLD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --log-bin --binlog-format=badformat >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 +--exec $MYSQLD_SIMPLE_CMD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --log-bin --binlog-format=badformat >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 --echo Test bad default storage engine. --error 1 ---exec $MYSQLD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --default-storage-engine=nonexistentengine >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 +--exec $MYSQLD_SIMPLE_CMD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --default-storage-engine=nonexistentengine >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 --echo Test non-numeric value passed to number option. --error 1 ---exec $MYSQLD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --min-examined-row-limit=notanumber >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 +--exec $MYSQLD_SIMPLE_CMD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --min-examined-row-limit=notanumber >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 # Test for MBug#423035: error in parsing enum value for plugin @@ -44,16 +44,16 @@ mkdir $MYSQLTEST_VARDIR/tmp/mysqld_option_err; # See also Bug#32034. --echo Test that bad value for plugin enum option is rejected correctly. --error 7 ---exec $MYSQLD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --plugin-dir=$MYSQLTEST_VARDIR/plugins --plugin-load=example=ha_example.so --plugin-example-enum-var=noexist >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 +--exec $MYSQLD_SIMPLE_CMD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --plugin-dir=$MYSQLTEST_VARDIR/plugins --plugin-load=example=ha_example.so --plugin-example-enum-var=noexist >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 # # Test that an wrong option with --help --verbose gives an error # --echo Test that --help --verbose works ---exec $MYSQLD --help --verbose >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 +--exec $MYSQLD_SIMPLE_CMD --help --verbose >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 --echo Test that --not-known-option --help --verbose gives error --error 2 ---exec $MYSQLD --not-known-option --help --verbose >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 +--exec $MYSQLD_SIMPLE_CMD --not-known-option --help --verbose >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 --echo Done. -- cgit v1.2.1 From f84dbf4b205f311f379a8fd0c6dc1f0fd893e073 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Fri, 25 Nov 2011 05:56:58 +0400 Subject: Semi-join optimizations code cleanup part 2: - Make EXPLAIN display "Start temporary" at the start of the fanout (it used to display at the first table whose rowid gets into temp. table which is not that useful for the user) - Updated test results (all checked) --- mysql-test/t/subselect_sj2.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index b2721574deb..2ec15d4dfae 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -7,7 +7,7 @@ set @subselect_sj2_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; --disable_warnings -drop table if exists t0, t1, t2, t3; +drop table if exists t0, t1, t2, t3, t4, t5; drop view if exists v1; --enable_warnings -- cgit v1.2.1 From 17b4e4a194ea513f776bab5010d88d24a51b9d9e Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 26 Nov 2011 14:23:00 -0800 Subject: Set new default values for the optimizer switch flags 'derived_merge' and 'derived_with_keys'. Now they are set on by default. --- mysql-test/t/derived.test | 6 ++++++ mysql-test/t/explain.test | 3 +++ mysql-test/t/func_group.test | 3 +++ mysql-test/t/func_str.test | 6 +++++- mysql-test/t/index_merge_innodb.test | 3 +++ mysql-test/t/information_schema.test | 3 +++ mysql-test/t/ps.test | 6 ++++++ mysql-test/t/subselect.test | 6 ++++++ mysql-test/t/subselect3.test | 3 +++ mysql-test/t/subselect_mat_cost_bugs.test | 8 ++++++++ mysql-test/t/subselect_partial_match.test | 3 +++ mysql-test/t/subselect_sj.test | 5 +++++ mysql-test/t/subselect_sj_mat.test | 3 +++ 13 files changed, 57 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 962cec95add..7defc55b473 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -3,6 +3,9 @@ drop table if exists t1,t2,t3; --enable_warnings +set @save_derived_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + select * from (select 2 from DUAL) b; -- error 1054 SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b; @@ -106,6 +109,7 @@ create user mysqltest_1; create table t1 select 1 as a; connect (con1,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK); connection con1; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; --error 1046 select 2 as a from (select * from t1) b; use test; @@ -315,3 +319,5 @@ WHERE j = SUBSTRING('12', (SELECT * FROM (SELECT MIN(j) FROM t1) t2))) t3; DROP TABLE t1; --echo # End of 5.0 tests + +set optimizer_switch=@save_derived_optimizer_switch; diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 186a00af16a..94548fd5fde 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -279,8 +279,11 @@ CREATE TABLE t1 (a int) ; CREATE TABLE t2 (a int) ; INSERT INTO t2 VALUES (8); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN EXTENDED SELECT * FROM ( SELECT t1.a FROM t1,t2 WHERE t2.a = t1.a ) AS t; +set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2; diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 874b26f7549..65b5b3936cc 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -1250,12 +1250,15 @@ SELECT v FROM t2); --echo +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT MIN(a) FROM (SELECT a FROM empty1) tt HAVING ('m') IN ( SELECT v FROM t2); +set optimizer_switch=@tmp_optimizer_switch; --echo --echo # diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 92c4bae5327..4302bf5e7eb 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1314,9 +1314,13 @@ DROP TABLE t1, t2; drop table if exists t1; --enable_warnings create table t1(f1 tinyint default null)engine=myisam; -insert into t1 values (-1),(null); +insert into t1 values (-1),(null); + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select 1 as a from t1,(select decode(f1,f1) as b from t1) a; explain select 1 as a from t1,(select encode(f1,f1) as b from t1) a; +set optimizer_switch=@tmp_optimizer_switch; drop table t1; --echo # diff --git a/mysql-test/t/index_merge_innodb.test b/mysql-test/t/index_merge_innodb.test index fa05c96a6f9..ae568c5b5d9 100644 --- a/mysql-test/t/index_merge_innodb.test +++ b/mysql-test/t/index_merge_innodb.test @@ -68,6 +68,8 @@ INSERT INTO t1(a,b) SELECT a,b FROM t1; INSERT INTO t1 VALUES (1000000, 0, 0); SET SESSION sort_buffer_size = 1024*36; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; # We have to use FORCE INDEX here as Innodb gives inconsistent estimates # which causes different query plans. @@ -89,6 +91,7 @@ SELECT COUNT(*) FROM WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; DROP TABLE t1; +set optimizer_switch=@tmp_optimizer_switch; --echo # --echo # Testcase Backport: BUG#48093: 6.0 Server not processing equivalent IN clauses properly diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index e78b180caf7..e1962255cf6 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -962,6 +962,8 @@ DROP FUNCTION get_value; # # Bug#22413 EXPLAIN SELECT FROM view with ORDER BY yield server crash # +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; create view v1 as select table_schema as object_schema, table_name as object_name, @@ -970,6 +972,7 @@ from information_schema.tables order by object_schema; explain select * from v1; explain select * from (select table_name from information_schema.tables) as a; +set optimizer_switch=@tmp_optimizer_switch; drop view v1; # diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index f4e27f3ba42..357e7d4fe8f 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -166,12 +166,18 @@ create table t2 like t1; set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch="partial_match_rowid_merge=off,partial_match_table_scan=off"; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + set @stmt= ' explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25 ' ; prepare stmt1 from @stmt ; execute stmt1 ; execute stmt1 ; explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25; deallocate prepare stmt1; + +set optimizer_switch=@tmp_optimizer_switch; + drop tables t1,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index a39b7ea49b8..abce58348da 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -94,8 +94,11 @@ explain extended (select * from t2 where t2.b=(select a from t3 order by 1 desc select (select a from t3 where a1) as tt; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from (select * from t2 where a>1) as tt; +set optimizer_switch=@tmp_optimizer_switch; select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1); select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a > t1.a) order by 1 desc limit 1); select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3 where t3.a < t1.a) order by 1 desc limit 1); @@ -4648,7 +4651,10 @@ CREATE TABLE t1 (a INT, b INT, INDEX (a)); INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20); --echo +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t; +set optimizer_switch=@tmp_optimizer_switch; --echo EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7); diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index 7e4943d85ba..aadc08e18e0 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -925,8 +925,11 @@ set @@optimizer_switch='materialization=off'; # # FirstMatch referring to a derived table # +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select * from (select a from t0) X where a in (select a from t1); drop table t0, t1; +set optimizer_switch=@tmp_optimizer_switch; # # LooseScan: Check if we can pick it together with range access diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test index 4ce19012e0c..463685bbdf0 100644 --- a/mysql-test/t/subselect_mat_cost_bugs.test +++ b/mysql-test/t/subselect_mat_cost_bugs.test @@ -183,6 +183,9 @@ CREATE TABLE t2 ( f2 int(11)) ; CREATE TABLE t1 ( f3 int(11), KEY (f3)) ; INSERT INTO t1 VALUES (6),(4); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + EXPLAIN SELECT * FROM (SELECT * FROM t2) AS a2 WHERE (SELECT distinct SUM(distinct f3 ) FROM t1); @@ -192,6 +195,7 @@ EXPLAIN SELECT * FROM (SELECT * FROM t2) AS a2 WHERE (SELECT distinct SUM(distinct f3 ) FROM t1); +set optimizer_switch=@tmp_optimizer_switch; drop table t1,t2; --echo # @@ -204,6 +208,9 @@ INSERT INTO t1 VALUES (28),(29); CREATE TABLE t2 ( f2 int(11), f3 int(11), f10 varchar(1)) ; INSERT INTO t2 VALUES (NULL,6,'f'),(4,2,'d'); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + EXPLAIN SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 @@ -223,6 +230,7 @@ WHERE ( ) ORDER BY field1 ; +set optimizer_switch=@tmp_optimizer_switch; drop table t1,t2; --echo # diff --git a/mysql-test/t/subselect_partial_match.test b/mysql-test/t/subselect_partial_match.test index 45386efd266..fd1e6de716c 100644 --- a/mysql-test/t/subselect_partial_match.test +++ b/mysql-test/t/subselect_partial_match.test @@ -622,8 +622,11 @@ INSERT INTO t2 VALUES (NULL,0); set @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on'; +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM t1 WHERE a1 NOT IN (SELECT b2 FROM t2)) table1; +set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1, t2; diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 5ae968742aa..a2f46c76fce 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -1690,6 +1690,9 @@ INSERT INTO t2 VALUES (1,2,4,'22:34:09','v','v'), (18,3,9,'19:39:02','v','v'),(19,9,1,NULL,NULL,NULL), (20,6,5,'20:58:33','r','r'); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; + explain SELECT alias1.a, alias1.b, alias1.c, alias1.d, alias1.e, alias1.f, @@ -1720,6 +1723,8 @@ WHERE FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2) LIMIT 100; +set optimizer_switch=@tmp_optimizer_switch; + drop table t1,t2, t3; set optimizer_switch=@tmp_830993; set join_buffer_size= @tmp_830993_jbs; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index a72128bf5ed..4ddc19f49f5 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1022,12 +1022,15 @@ CREATE TABLE t1 (a INTEGER); CREATE TABLE t2 (b INTEGER); INSERT INTO t2 VALUES (1); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; let $query = SELECT a FROM ( SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1) ) table1; eval explain $query; eval $query; +set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1, t2; -- cgit v1.2.1 From 5412e82c01aa126448af8c64279e2cb9a7ffdd38 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 28 Nov 2011 12:42:14 +0200 Subject: Fixed LP BUG#747278 The problem was that when we have single row subquery with no rows Item_cache(es) which represent result row was not null and being requested via element_index() returned random value. The fix is setting all Item_cache(es) in NULL before executing the query (reset() method) which guaranty NULL value of whole query or its elements requested in any way if no rows was found. set_null() method was added to Item_cache to guaranty correct NULL value in case of reseting the cache. --- mysql-test/t/subselect.test | 45 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 45 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index abce58348da..848509d9a7c 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -4840,4 +4840,49 @@ WHERE t2.b IN ( set @@join_cache_level= @save_join_cache_level; drop table t1,t2; + +--echo # +--echo # LP BUG#747278 incorrect values of the NULL (no rows) single +--echo # row subquery requested via element_index() interface +--echo # + +CREATE TABLE t1 (f1a int, f1b int) ; +INSERT IGNORE INTO t1 VALUES (1,1),(2,2); +CREATE TABLE t2 ( f2 int); +INSERT IGNORE INTO t2 VALUES (3),(4); +CREATE TABLE t3 (f3a int default 1, f3b int default 2); +INSERT INTO t3 VALUES (1,1),(2,2); + +# check different IN with switches where the bug was found +set @old_optimizer_switch = @@session.optimizer_switch; +set @@optimizer_switch='materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,semijoin=off'; + +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); + +set @@session.optimizer_switch=@old_optimizer_switch; + +# check different IN with default switches +SELECT (SELECT f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a FROM t1) FROM t2; +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) NOT IN (SELECT f1a,f1a FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) NOT IN (SELECT f1a, f1b FROM t1); +SELECT (SELECT f3a FROM t3 where f3a > 3) IN (SELECT f1a FROM t1) FROM t2; +SELECT (SELECT f3a,f3a FROM t3 where f3a > 3) IN (SELECT f1a,f1a FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1) FROM t2; +SELECT (SELECT f3a, f3b FROM t3 where f3a > 3) IN (SELECT f1a, f1b FROM t1); + +# other row operation with NULL single row subquery also should work +select (null, null) = (null, null); +SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0); + +drop tables t1,t2,t3; + +--echo # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; -- cgit v1.2.1 From 62e7ab3ac7a7dc6c89308f9883610d9951d85a73 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 28 Nov 2011 15:24:07 +0200 Subject: Fix bugs lp:833777, lp:894397 Analysis: lp:894397 was a consequence of a prior incorrect fix of lp:833777 which didn't take into account that even when all tables are constant there may be correlated conditions, and the where clause is not equivalent to the constant conditions. Solution: When there are constant tables only, evaluate only the conditions that reference outer fields, because the constant conditions are already checked, and the where clause doesn't have other conditions than constant ones, and outer referencing ones. The fix for lp:894397 also fixes lp:833777. --- mysql-test/t/subselect.test | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 848509d9a7c..92df79d7777 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -4782,8 +4782,22 @@ create table t1 (a int not null, b char(10) not null); insert into t1 values (1, 'a'); set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off'; select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1))))))))))))))))))))))))))))); +set @@optimizer_switch=@subselect_tmp; drop table t1; +--echo # +--echo # LP BUG#894397 Wrong result with in_to_exists, constant table , semijoin=OFF,materialization=OFF +--echo # + +CREATE TABLE t1 (a varchar(3)); +INSERT INTO t1 VALUES ('AAA'),('BBB'); +CREATE TABLE t2 (a varchar(3)); +INSERT INTO t2 VALUES ('CCC'); +set @@optimizer_switch='semijoin=off,materialization=off,in_to_exists=on,subquery_cache=off'; +SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.a < 'ZZZ'); +set @@optimizer_switch=@subselect_tmp; +drop table t1, t2; + --echo # --echo # LP bug #859375: Assertion `0' failed in st_select_lex_unit::optimize --echo # with view , UNION and prepared statement (rewriting fake_select -- cgit v1.2.1 From 625cdb8078550d30399209d58edcb38cdfcc411d Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 29 Nov 2011 23:06:39 +0200 Subject: Fixed bug lp:825051 The cause of the wrong result was that Item_ref_null_helper::get_date() didn't use a method of the *_result() family, and fetched the data for the field from the current row instead of result_field. Changed to use the correct *_result() method, like to all other similar methods of Item_ref_null_helper. --- mysql-test/t/subselect.test | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 92df79d7777..aac06e349b5 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -4898,5 +4898,21 @@ SELECT (SELECT f3a, f3a FROM t3 where f3a > 3) = (0, 0); drop tables t1,t2,t3; +--echo # +--echo # LP BUG#825051 Wrong result with date/datetime and subquery with GROUP BY and in_to_exists +--echo # + +CREATE TABLE t1 (a date, KEY (a)) ; +INSERT INTO t1 VALUES ('2009-01-01'),('2009-02-02'); +set @old_optimizer_switch = @@optimizer_switch; +SET @@optimizer_switch='semijoin=off,materialization=off,in_to_exists=on,subquery_cache=off'; +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); +SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); +SET @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=off,subquery_cache=off'; +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); +SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); +set @@optimizer_switch=@old_optimizer_switch; +drop table t1; + --echo # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; -- cgit v1.2.1 From 264aaf111d0493f0472e704ad7dda426f81376ea Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 29 Nov 2011 23:09:06 +0200 Subject: Added test suite for the LP BUG#885162 (fixed by the patch for LP BUG#859375 and LP BUG#887458). --- mysql-test/t/subselect.test | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 92df79d7777..26ba75a6f46 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -4855,6 +4855,24 @@ WHERE t2.b IN ( set @@join_cache_level= @save_join_cache_level; drop table t1,t2; + +--echo # +--echo # LP bug #885162 Got error 124 from storage engine with UNION inside +--echo # subquery and join_cache_level=3..8 +--echo # (IN/ALL/ANY optimizations should not be applied to fake_select) +--echo # + +CREATE TABLE t1 ( + f1 varchar(1) DEFAULT NULL + ); +INSERT INTO t1 VALUES ('c'); +set @save_join_cache_level=@@join_cache_level; +SET SESSION join_cache_level=8; +SELECT * FROM t1 WHERE t1.f1 IN ( SELECT 'k' UNION SELECT 'e' ); +set @@join_cache_level= @save_join_cache_level; +drop table t1; + + --echo # --echo # LP BUG#747278 incorrect values of the NULL (no rows) single --echo # row subquery requested via element_index() interface -- cgit v1.2.1 From 2f9734172f2552c6fdb0483e33423a92acd9285b Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 30 Nov 2011 10:22:53 -0800 Subject: Fixed LP bug #898073. The tables from the same semi-join or outer join nest cannot use join buffers if in the join sequence of the query execution plan they are separated by a table that is planned to be joined without usage of a join buffer. --- mysql-test/t/subselect_sj2_jcl6.test | 32 ++++++++++++++++++++++++++++++++ 1 file changed, 32 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj2_jcl6.test b/mysql-test/t/subselect_sj2_jcl6.test index e4ae249c711..9628bd5c4b9 100644 --- a/mysql-test/t/subselect_sj2_jcl6.test +++ b/mysql-test/t/subselect_sj2_jcl6.test @@ -13,6 +13,38 @@ show variables like 'join_cache_level'; --source t/subselect_sj2.test +--echo # +--echo # Bug #898073: potential incremental join cache for semijoin +--echo # + +CREATE TABLE t1 (a int, b varchar(1), KEY (b,a)); +INSERT INTO t1 VALUES (0,'x'), (5,'r'); + +CREATE TABLE t2 (a int) ENGINE=InnoDB; +INSERT INTO t2 VALUES (8); + +CREATE TABLE t3 (b varchar(1), c varchar(1)) ENGINE=InnoDB; +INSERT INTO t3 VALUES ('x','x'); + +CREATE TABLE t4 (a int NOT NULL, b varchar(1)) ENGINE=InnoDB; +INSERT INTO t4 VALUES (20,'r'), (10,'x'); + +set @tmp_optimizer_switch=@@optimizer_switch; + +SET SESSION optimizer_switch='semijoin_with_cache=on'; + +SET SESSION join_cache_level=2; +EXPLAIN +SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b + WHERE c IN (SELECT t4.b FROM t4 JOIN t2); +SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b + WHERE c IN (SELECT t4.b FROM t4 JOIN t2); + +set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=default; + +DROP TABLE t1,t2,t3,t4; + set join_cache_level=default; show variables like 'join_cache_level'; -- cgit v1.2.1 From 1054de8699cc64174ec57771fb631ea14a48158e Mon Sep 17 00:00:00 2001 From: Vladislav Vaintroub Date: Thu, 1 Dec 2011 22:37:45 +0100 Subject: Fix intermittently failing variables-notembedded test case. After sending packet that is too large, clienrt can get either an error packet with ER_NET_PACKET_TOO_LARGE, or a socket error. Both cases are valid, since the server does not ensure reply was fully read by client, before shutting down and closing the socket. --- mysql-test/t/variables-notembedded.test | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/variables-notembedded.test b/mysql-test/t/variables-notembedded.test index b440cfa47b0..471212bf38f 100644 --- a/mysql-test/t/variables-notembedded.test +++ b/mysql-test/t/variables-notembedded.test @@ -123,7 +123,12 @@ CONNECT (con1,localhost,root,,test); SHOW SESSION VARIABLES LIKE 'max_allowed_packet'; SHOW SESSION VARIABLES LIKE 'net_buffer_length'; --disable_query_log ---error ER_NET_PACKET_TOO_LARGE +#Sending a packet that is too big can result in either +#ER_NET_PACKET_TOO_LARGE or a socket error on the client side (2013= CR_SERVER_LOST) +#The server does not make any attempts to gracefully close client connection and ensuring +#client fully read the last packet. Server just closes the socket after it has send. +#Client thus can get either a socket error, or EOF, or an error packet with ER_NET_PACKET_TOO_LARGE +--error ER_NET_PACKET_TOO_LARGE,2013 INSERT INTO t1 VALUES ('123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'); --enable_query_log -- cgit v1.2.1 From d5fd757a4279f4fa8f032c6dd63d1d121d8e1fea Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 2 Dec 2011 16:26:43 +0100 Subject: 1. add --plugin-dir and --default-auth to mysqltest. 2. dialog plugin now always returns mysql->password if non-empty and the first question is of password type 3. split get_tty_password into get_tty_password_buff and strdup. 4. dialog plugin now uses get_tty_password by default 5. dialog.test 6. moved small tests of individual plugins into a dedicated suite --- mysql-test/t/feedback_plugin_install.opt | 1 - mysql-test/t/feedback_plugin_install.test | 15 --------------- mysql-test/t/feedback_plugin_load.opt | 2 -- mysql-test/t/feedback_plugin_load.test | 10 ---------- mysql-test/t/feedback_plugin_send.test | 32 ------------------------------- mysql-test/t/fulltext_plugin.test | 11 ----------- 6 files changed, 71 deletions(-) delete mode 100644 mysql-test/t/feedback_plugin_install.opt delete mode 100644 mysql-test/t/feedback_plugin_install.test delete mode 100644 mysql-test/t/feedback_plugin_load.opt delete mode 100644 mysql-test/t/feedback_plugin_load.test delete mode 100644 mysql-test/t/feedback_plugin_send.test delete mode 100644 mysql-test/t/fulltext_plugin.test (limited to 'mysql-test/t') diff --git a/mysql-test/t/feedback_plugin_install.opt b/mysql-test/t/feedback_plugin_install.opt deleted file mode 100644 index a711ae94e69..00000000000 --- a/mysql-test/t/feedback_plugin_install.opt +++ /dev/null @@ -1 +0,0 @@ ---loose-feedback diff --git a/mysql-test/t/feedback_plugin_install.test b/mysql-test/t/feedback_plugin_install.test deleted file mode 100644 index 81343c436c3..00000000000 --- a/mysql-test/t/feedback_plugin_install.test +++ /dev/null @@ -1,15 +0,0 @@ ---source include/not_embedded.inc - -if (`select length('$FEEDBACK_SO') = 0`) { - skip No feedback plugin; -} - ---replace_regex /\.dll/.so/ -eval install plugin feedback soname '$FEEDBACK_SO'; -select plugin_status from information_schema.plugins where plugin_name='feedback'; ---replace_result https http ---sorted_result -select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid'; -uninstall plugin feedback; - diff --git a/mysql-test/t/feedback_plugin_load.opt b/mysql-test/t/feedback_plugin_load.opt deleted file mode 100644 index 5fbb2f83954..00000000000 --- a/mysql-test/t/feedback_plugin_load.opt +++ /dev/null @@ -1,2 +0,0 @@ ---loose-feedback ---plugin-load=$FEEDBACK_SO diff --git a/mysql-test/t/feedback_plugin_load.test b/mysql-test/t/feedback_plugin_load.test deleted file mode 100644 index 5ad301667b4..00000000000 --- a/mysql-test/t/feedback_plugin_load.test +++ /dev/null @@ -1,10 +0,0 @@ -if (`select count(*) = 0 from information_schema.plugins where plugin_name = 'feedback' and plugin_status='active'`) -{ - --skip Feedback plugin is not active -} - -select plugin_status from information_schema.plugins where plugin_name='feedback'; ---replace_result https http ---sorted_result -select * from information_schema.feedback where variable_name like 'feed%' - and variable_name not like '%_uid'; diff --git a/mysql-test/t/feedback_plugin_send.test b/mysql-test/t/feedback_plugin_send.test deleted file mode 100644 index d882539cfea..00000000000 --- a/mysql-test/t/feedback_plugin_send.test +++ /dev/null @@ -1,32 +0,0 @@ -source t/feedback_plugin_load.test; - -if (!$MTR_FEEDBACK_PLUGIN) { - skip MTR_FEEDBACK_PLUGIN is not set; -} - -# -# Yep. The plugin waits 5 minutes before sending anything, -# and there's no way to force it to send anything sooner. -# Let's wait, and hope that mtr is started with --parallel and -# is doing some work in other workers. -# -sleep 310; -source include/restart_mysqld.inc; - -replace_result https http; -perl; - $log_error= $ENV{'MYSQLTEST_VARDIR'} . '/log/mysqld.1.err'; - open(LOG, '<', $log_error) or die "open(< $log_error): $!"; - - # Get the first few rows (as there may be different number rows in the log) - $i= 0; - while ($_=) - { - if (/feedback plugin:.*/) - { - print "$&\n"; - break if ($i++ >= 3); - } - } - close LOG; -EOF diff --git a/mysql-test/t/fulltext_plugin.test b/mysql-test/t/fulltext_plugin.test deleted file mode 100644 index 0e2f53d5b15..00000000000 --- a/mysql-test/t/fulltext_plugin.test +++ /dev/null @@ -1,11 +0,0 @@ ---source include/have_simple_parser.inc - -# -# BUG#39746 - Debug flag breaks struct definition (server crash) -# ---replace_result .dll .so -eval INSTALL PLUGIN simple_parser SONAME '$MYPLUGLIB_SO'; -CREATE TABLE t1(a TEXT, b TEXT, FULLTEXT(a) WITH PARSER simple_parser); -ALTER TABLE t1 ADD FULLTEXT(b) WITH PARSER simple_parser; -DROP TABLE t1; -UNINSTALL PLUGIN simple_parser; -- cgit v1.2.1 From 2e34f1828ec6b1bf897eaa23dec8fce544047278 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Sat, 3 Dec 2011 20:29:15 +0200 Subject: Added suppressions Fixed feedback_plugin_send to not generate a random number of lines. mysql-test/t/feedback_plugin_send.test: Don't print more than 4 lines (sometimes there are 6 feedback lines in the log...) mysql-test/valgrind.supp: Added suppression for failure on work support-files/compiler_warnings.supp: Suppress warning from xtradb --- mysql-test/t/feedback_plugin_send.test | 13 +++++++++++-- 1 file changed, 11 insertions(+), 2 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/feedback_plugin_send.test b/mysql-test/t/feedback_plugin_send.test index 14765ee9543..d882539cfea 100644 --- a/mysql-test/t/feedback_plugin_send.test +++ b/mysql-test/t/feedback_plugin_send.test @@ -17,7 +17,16 @@ replace_result https http; perl; $log_error= $ENV{'MYSQLTEST_VARDIR'} . '/log/mysqld.1.err'; open(LOG, '<', $log_error) or die "open(< $log_error): $!"; - /feedback plugin:.*/ && print "$&\n" while $_=; + + # Get the first few rows (as there may be different number rows in the log) + $i= 0; + while ($_=) + { + if (/feedback plugin:.*/) + { + print "$&\n"; + break if ($i++ >= 3); + } + } close LOG; EOF - -- cgit v1.2.1 From b5a05df61ea263aa3c3b9df78c56148adf029f04 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sun, 4 Dec 2011 07:43:33 -0800 Subject: Fixed LP bug #899696. If has been decided that the first match strategy is to be used to join table T from a semi-join nest while no buffer can be employed to join this table then no join buffer can be used to join any table in the join sequence between the first one belonging to the semi-join nest and table T. --- mysql-test/t/subselect_sj2_jcl6.test | 28 ++++++++++++++++++++++++++++ 1 file changed, 28 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj2_jcl6.test b/mysql-test/t/subselect_sj2_jcl6.test index 9628bd5c4b9..374fbd946c3 100644 --- a/mysql-test/t/subselect_sj2_jcl6.test +++ b/mysql-test/t/subselect_sj2_jcl6.test @@ -45,6 +45,34 @@ set join_cache_level=default; DROP TABLE t1,t2,t3,t4; +--echo # +--echo # Bug #899696: potential incremental join cache for semijoin +--echo # + +CREATE TABLE t1 (pk int PRIMARY KEY, a int); +INSERT INTO t1 VALUES (1, 6), (2, 8); +CREATE TABLE t2 (b int) ENGINE=InnoDB; +INSERT INTO t2 VALUES (8); +CREATE TABLE t3 (pk int PRIMARY KEY, a int); +INSERT INTO t3 VALUES (1, 6), (2, 8); +CREATE TABLE t4 (b int) ENGINE=InnoDB; +INSERT INTO t4 VALUES (2); + +set @tmp_optimizer_switch=@@optimizer_switch; + +SET optimizer_switch = 'semijoin_with_cache=on'; +SET join_cache_level = 2; + +EXPLAIN +SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk); +SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk); + +set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=default; + +DROP TABLE t1,t2,t3,t4; + + set join_cache_level=default; show variables like 'join_cache_level'; -- cgit v1.2.1 From 7d1f41265c7d9e36b8d85af33225b68a4eec1a2f Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 5 Dec 2011 09:50:24 -0800 Subject: Fixed LP bug #899777. KEYUSE elements for a possible hash join key are not sorted by field numbers of the second table T of the hash join operation. Besides some of these KEYUSE elements cannot be used to build any key as their key expressions depend on the tables that are planned to be accessed after the table T. The code before the patch did not take this into account and, as a result, execition of a query the employing block-based hash join algorithm could cause a crash or return a wrong result set. --- mysql-test/t/join_cache.test | 40 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 40 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 530528ead2c..66381150ede 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3254,5 +3254,45 @@ SET optimizer_switch=@tmp887479_optimizer_switch; DROP TABLE t1,t2; +--echo # +--echo # Bug #899777: join_cache_level=4 + semijoin=on +--echo # + +CREATE TABLE t1 (a int, b int, c int, UNIQUE INDEX idx (a)); +INSERT INTO t1 VALUES (1,8,6), (2,2,8); +CREATE TABLE t2 (a int, b int, c int, UNIQUE INDEX idx (a)); +INSERT INTO t2 VALUES (1,8,6), (2,2,8); +CREATE TABLE t3 (a int, b int, c int, UNIQUE INDEX idx (a)); +INSERT INTO t3 VALUES (1,8,6), (2,2,8); +CREATE TABLE t4 (a int, b int, c int, UNIQUE INDEX idx (a)); +INSERT INTO t4 VALUES (1,8,6), (2,2,8); + +SET @tmp_optimizer_switch=@@optimizer_switch; +SET SESSION optimizer_switch='semijoin=on'; +SET SESSION optimizer_switch='semijoin_with_cache=on'; + +SET SESSION join_cache_level=1; +EXPLAIN +SELECT t1.* FROM t1,t2 + WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b) + AND t1.a = 1; +SELECT t1.* FROM t1,t2 + WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b) + AND t1.a = 1; + +SET SESSION join_cache_level=4; +EXPLAIN +SELECT t1.* FROM t1,t2 + WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b) + AND t1.a = 1; +SELECT t1.* FROM t1,t2 + WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b) + AND t1.a = 1; + +SET SESSION join_cache_level = DEFAULT; +SET optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2,t3,t4; + # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; -- cgit v1.2.1 From 136408b1cfa9c197aaf2c5b8698db76144474077 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 6 Dec 2011 01:04:27 +0400 Subject: Bug #899962: materialized subquery with join_cache_level=3 - Make create_tmp_table() set KEY_PART_INFO attributes for the keys it creates. This wasn't needed before but is needed now, when temp. tables that are results of SJ-Materialization are being used for joins. This particular bug depended on HA_VAR_LENGTH_PART being set, but also added code to set HA_BLOB_PART and HA_NULL_PART when appropriate. --- mysql-test/t/subselect_sj2_jcl6.test | 24 ++++++++++++++++++++++++ 1 file changed, 24 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj2_jcl6.test b/mysql-test/t/subselect_sj2_jcl6.test index 374fbd946c3..95f84a9e89e 100644 --- a/mysql-test/t/subselect_sj2_jcl6.test +++ b/mysql-test/t/subselect_sj2_jcl6.test @@ -73,6 +73,30 @@ set join_cache_level=default; DROP TABLE t1,t2,t3,t4; +--echo # +--echo # Bug #899962: materialized subquery with join_cache_level=3 +--echo # + +CREATE TABLE t1 (a varchar(1), b varchar(1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('v','v'); +CREATE TABLE t2 (a varchar(1), b varchar(1)) ENGINE=InnoDB; +INSERT INTO t2 VALUES ('v','v'); + +set @tmp_optimizer_switch=@@optimizer_switch; + +SET optimizer_switch = 'semijoin_with_cache=on'; +SET join_cache_level = 3; + +EXPLAIN +SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a); +SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a); + +set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=default; + +DROP TABLE t1,t2; + + set join_cache_level=default; show variables like 'join_cache_level'; -- cgit v1.2.1 From b4c9fa321dd1fc08227333439695ec4314fb34c8 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 6 Dec 2011 02:46:42 -0800 Subject: Fixed LP bug #899509. The optimizer must ignore any possible hash join key when looking for the query execution plan with join_cache_level set to 0. --- mysql-test/t/join_cache.test | 29 +++++++++++++++++++++++++++++ 1 file changed, 29 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 66381150ede..ac92ff1631b 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3294,5 +3294,34 @@ SET optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2,t3,t4; +--echo # +--echo # Bug #899509: an attempt to use hash join with join_cache_level=0 +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (8), (7); +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES (8), (7); +CREATE TABLE t3 (a int); +INSERT INTO t3 VALUES (8), (7); + +SET @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch=default; +set @@optimizer_switch='semijoin_with_cache=off'; +set @@optimizer_switch='outer_join_with_cache=off'; +SET optimizer_switch='derived_merge=off,derived_with_keys=off'; +SET join_cache_level=0; + +EXPLAIN +SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3); +SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3); + +SELECT * FROM ( SELECT ta.* FROM t1 AS ta, t1 ) tb WHERE a IN ( SELECT * FROM t1 ); + +SET SESSION join_cache_level = DEFAULT; +SET optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2,t3; + # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; -- cgit v1.2.1 From 8e25dcfcd7bc1ccf9d65b2f12eba4543ed1bf9f4 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 7 Dec 2011 01:03:00 +0400 Subject: BUG#868908: Crash in check_simple_equality() with semijoin + materialization + prepared statement - Part 1 of the fix: for semi-join merged subqueries, calling child_join->optimize() until we're done with all PS-lifetime optimizations in the parent. --- mysql-test/t/subselect_sj_mat.test | 23 +++++++++++++++++++++++ 1 file changed, 23 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 4ddc19f49f5..fc3c7089db9 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1427,7 +1427,30 @@ ON ( t2.f5 ) IN ( ); DROP TABLE t1, t2, t3, t4, t5; +--echo # +--echo # BUG#868908: Crash in check_simple_equality() with semijoin + materialization + prepared statement +--echo # + +CREATE TABLE t1 ( a int ); +CREATE TABLE t3 ( b int, c int) ; +CREATE TABLE t2 ( a int ) ; +CREATE TABLE t4 ( a int , c int) ; + +PREPARE st1 FROM " +SELECT STRAIGHT_JOIN * +FROM t1 +WHERE ( 3 ) IN ( + SELECT t3.b + FROM t3 + LEFT JOIN ( + t2 STRAIGHT_JOIN t4 ON ( t4.c = t2.a ) + ) ON ( t4.a = t3.c ) +); +"; +EXECUTE st1; +EXECUTE st1; +DROP TABLE t1,t2,t3,t4; --echo # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; -- cgit v1.2.1 From 7414a0b6d6307f8c2644660cf2f8daf986c25970 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 6 Dec 2011 13:42:18 -0800 Subject: Fixed LP bug #900469. The execution plan cannot use sorting on the first table from the sequence of the joined tables if it plans to employ the block-based hash join algorithm. --- mysql-test/t/join_cache.test | 38 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 38 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index ac92ff1631b..ef64e8caea1 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3323,5 +3323,43 @@ SET optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2,t3; +--echo # +--echo # Bug #900469: semijoin + BNLH + ORDER BY +--echo # + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (8,10); + +CREATE TABLE t2 (c int, d int); +INSERT INTO t2 VALUES (8,10); +INSERT INTO t2 VALUES (9,11); + +CREATE TABLE t3 (c int, d int); +INSERT INTO t3 VALUES (8,10); +INSERT INTO t3 VALUES (9,11); + +SET @tmp_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='semijoin_with_cache=on'; + +SET join_cache_level=1; +EXPLAIN +SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; +SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; + +SET join_cache_level=3; +EXPLAIN +SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c); +SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c); + +SET join_cache_level=3; +EXPLAIN +SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; +SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; + +SET SESSION join_cache_level = DEFAULT; +SET optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2,t3; + # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; -- cgit v1.2.1 From 49ecc880699b9627a1fbec8fa5c4d025ccb9a7cf Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Thu, 8 Dec 2011 02:12:48 +0400 Subject: BUG#901032: Wrong result for MIN/MAX on an indexed column with materialization and semijoin - opt_sum_query() should not assume that join tables from sj-materialization have known numbers of rows. --- mysql-test/t/subselect_sj_mat.test | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 4ddc19f49f5..c2badddba5b 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1428,6 +1428,19 @@ ON ( t2.f5 ) IN ( DROP TABLE t1, t2, t3, t4, t5; +--echo # +--echo # BUG#901032: Wrong result for MIN/MAX on an indexed column with materialization and semijoin +--echo # +CREATE TABLE t1 ( a INT, KEY(a) ); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 ( b INT ); +INSERT INTO t2 VALUES (2); +CREATE TABLE t3 ( c INT ); +INSERT INTO t3 VALUES (2); + +SELECT MIN(a) FROM t1, t2 WHERE b IN (SELECT c FROM t3 GROUP BY c); + +DROP TABLE t1,t2,t3; --echo # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; -- cgit v1.2.1 From 314c377422dd13c86591a4de32162467eb540c33 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 8 Dec 2011 12:05:52 +0200 Subject: Fixed bug lp:888456 Analysis: The class member QUICK_GROUP_MIN_MAX_SELECT::seen_first_key was not reset between subquery re-executions. Thus each subsequent execution continued from the group that was reached by the previous subquery execution. As a result loose scan reached end of file much earlier, and returned empty result where it shouldn't. Solution: Reset seen_first_key before each re-execution of the loose scan. --- mysql-test/t/group_min_max.test | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index 8ab7e1c9cb4..bd2cbd8a9f0 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -1099,5 +1099,19 @@ ORDER BY min_a; DROP TABLE t1; +--echo # +--echo # LP BUG#888456 Wrong result with DISTINCT , ANY , subquery_cache=off , NOT NULL +--echo # + +CREATE TABLE t1 ( a int NOT NULL) ; +INSERT INTO t1 VALUES (28),(29),(9); + +CREATE TABLE t2 ( a int, KEY (a)) ; +INSERT INTO t2 VALUES (1),(1),(1),(4),(4),(5),(5),(8),(8),(9); + +explain select (select t2.a from t2 where t2.a >= t1.a group by t2.a) from t1; +select (select t2.a from t2 where t2.a >= t1.a group by t2.a) from t1; + +drop table t1, t2; --echo End of 5.1 tests -- cgit v1.2.1 From 8a09adb3eaf036b652b11afea7d9d86a978fa566 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 9 Dec 2011 14:30:50 -0800 Subject: Fixed LP bug #901312. The function setup_sj_materialization_part1() forgot to set the value of TABLE::map for any materialized IN subquery. This could lead to wrong results for queries with subqueries that were converted to queries with semijoins. --- mysql-test/t/subselect_sj.test | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index a2f46c76fce..130a5251846 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -1915,5 +1915,27 @@ set optimizer_switch= @tmp_otimizer_switch; DROP TABLE t1,t2,t3; +--echo # +--echo # Bug #901312: materialized semijoin + right join +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (4), (1); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (4), (1); +CREATE TABLE t3 (c int); +INSERT INTO t3 VALUES (4), (1); + +set @tmp_otimizer_switch= @@optimizer_switch; +SET SESSION optimizer_switch='semijoin=on,materialization=on'; + +EXPLAIN +SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3); +SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3); + +set optimizer_switch= @tmp_otimizer_switch; + +DROP TABLE t1,t2,t3; + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; -- cgit v1.2.1 From fa29f18ffbc8c60c4aec3c74b4013056c7c32169 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sun, 11 Dec 2011 12:56:06 -0800 Subject: Fixed LP bug #901478. If the duplicate elimination strategy is used for a semi-join and potentially one of the block-based join algorithms can be employed to join the inner tables of the semi-join then sorting of the head (first non-constant) table for a query with ORDER BY / GROUP BY cannot be used. --- mysql-test/t/join_cache.test | 63 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 63 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index ef64e8caea1..5a48f7653e4 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3361,5 +3361,68 @@ SET optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2,t3; +--echo # +--echo # Bug #901478: semijoin + ORDER BY + join_cache_level=4|6 +--echo # + +CREATE TABLE t1 (a char(1)); +INSERT INTO t1 VALUES ('x'); +CREATE TABLE t2 (a int, b int, c int, KEY(a), KEY(b), KEY(c)); +INSERT INTO t2 VALUES + (9,1,0), (7,2,8), (2,3,5), (4,2,9), (8,3,8), (3,4,1), (5,5,4); +CREATE TABLE t3 (a CHAR(1)); +INSERT INTO t3 VALUES ('x'); +CREATE TABLE t4 (a int, b int, c int, KEY(b), KEY(c)); +INSERT INTO t4 VALUES + (9,1,0), (7,2,8), (2,3,5), (4,2,9), (8,3,8), (3,4,1), (5,5,4); +INSERT INTO t4 VALUES + (19,11,10), (17,12,18), (12,13,15), (14,12,19), + (18,13,18), (13,14,11), (15,15,14); + +SET @tmp_optimizer_switch=@@optimizer_switch; +SET @@optimizer_switch='semijoin=on'; +SET @@optimizer_switch='firstmatch=off'; +SET @@optimizer_switch='mrr=off'; +SET @@optimizer_switch='semijoin_with_cache=off'; + +set join_cache_level=1; +EXPLAIN +SELECT * FROM t1,t2 + WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND + t2.a BETWEEN 4 and 5 + ORDER BY t2.b; +SELECT * FROM t1,t2 + WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND + t2.a BETWEEN 4 and 5 + ORDER BY t2.b; + +set join_cache_level=4; +EXPLAIN +SELECT * FROM t1,t2 + WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND + t2.a BETWEEN 4 and 5 + ORDER BY t2.b; +SELECT * FROM t1,t2 + WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND + t2.a BETWEEN 4 and 5 + ORDER BY t2.b; + +SET @@optimizer_switch='semijoin_with_cache=on'; +set join_cache_level=6; +EXPLAIN +SELECT * FROM t1,t2 + WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND + t2.a BETWEEN 4 and 5 + ORDER BY t2.b; +SELECT * FROM t1,t2 + WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND + t2.a BETWEEN 4 and 5 + ORDER BY t2.b; + +SET join_cache_level = DEFAULT; +SET optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2,t3,t4; + # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; -- cgit v1.2.1 From 63d32c115dd962b53cf7bcaa340472ee2f44f9e2 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sun, 11 Dec 2011 19:41:53 -0800 Subject: Fixed LP bug #901709. The cause of the reported assertion failure was a division of a double value by 0. --- mysql-test/t/subselect_sj.test | 30 ++++++++++++++++++++++++++++++ 1 file changed, 30 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 130a5251846..4f8ed7f1643 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -1937,5 +1937,35 @@ set optimizer_switch= @tmp_otimizer_switch; DROP TABLE t1,t2,t3; +--echo # +--echo # Bug #901709: assertion failure with record count == 0 +--echo # + +CREATE TABLE t1 (a int, KEY (a)); +INSERT INTO t1 VALUES (4), (6); +CREATE TABLE t2 (a int, KEY (a)); +INSERT INTO t2 VALUES (4), (6); +CREATE TABLE t3 (b int); +INSERT INTO t3 VALUES (4); +CREATE TABLE t4 (c int); + +SET @tmp_optimizer_switch=@@optimizer_switch; +SET @@optimizer_switch='semijoin=on'; +SET @@optimizer_switch='materialization=on'; +SET @@optimizer_switch='firstmatch=on'; +SET optimizer_switch='semijoin_with_cache=on'; +SET optimizer_prune_level=0; + +EXPLAIN +SELECT * FROM t1, t2 + WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); +SELECT * FROM t1, t2 + WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); + +SET optimizer_prune_level=DEFAULT; +SET optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2,t3,t4; + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; -- cgit v1.2.1 From 6404504d0c10d58ad5861bdb72edd54508f1364c Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 12 Dec 2011 12:36:46 +0200 Subject: Fixed bug lp:900375 The range optimizer incorrectly chose a loose scan for group by when there is a correlated WHERE condition. This range access method cannot be executed for correlated conditions also with the "range checked for each record" because generally the range access method can change for each outer record. Loose scan destructively changes the query plan and removes the GROUP operation, which will result in wrong query plans if another range access is chosen dynamically. --- mysql-test/t/group_min_max.test | 23 +++++++++++++++++++++++ 1 file changed, 23 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index bd2cbd8a9f0..7c9c2b05eda 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -1114,4 +1114,27 @@ select (select t2.a from t2 where t2.a >= t1.a group by t2.a) from t1; drop table t1, t2; +--echo # +--echo # LP BUG#900375 Wrong result with derived_merge=ON, DISTINCT or GROUP BY, EXISTS +--echo # + +CREATE TABLE t1 ( a INT, b INT, KEY (b) ); +INSERT INTO t1 VALUES +(100,10),(101,11),(102,12),(103,13),(104,14), +(105,15),(106,16),(107,17),(108,18),(109,19); + +EXPLAIN +SELECT alias1.* FROM t1, (SELECT * FROM t1) AS alias1 +WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ; +SELECT alias1.* FROM t1, (SELECT * FROM t1) AS alias1 +WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ; + +EXPLAIN +SELECT alias1.* FROM t1, t1 AS alias1 +WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ; +SELECT alias1.* FROM t1, t1 AS alias1 +WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ; + +drop table t1; + --echo End of 5.1 tests -- cgit v1.2.1 From b653115c8e0ce1015b74f9aeab3ad30f71ce4379 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Tue, 13 Dec 2011 14:00:20 +0200 Subject: Fixed valgrind error when storing db_name_length in query_cache. - Changed storage to be 2 bytes instead of sizeof(size_t) (simple optimization) - Fixed bug when using query_cache_strip_comments and query that started with '(' - Fixed DBUG_PRINT() that used wrong (not initialized) variables. mysql-test/mysql-test-run.pl: Added some space to make output more readable. mysql-test/r/query_cache.result: Updated test results mysql-test/t/query_cache.test: Added test with query_cache_strip_comments sql/mysql_priv.h: Added QUERY_CACHE_DB_LENGTH_SIZE sql/sql_cache.cc: Fixed bug when using query_cache_strip_comments and query that started with '(' Store db length in 2 characters instead of size_t. Get db length from correct position (earlier we had an error when query started with ' ') Fixed DBUG_PRINT() that used wrong (not initialized) variables. --- mysql-test/t/query_cache.test | 12 ++++++++++++ 1 file changed, 12 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index 0d57a5640bf..dc403a30732 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -776,7 +776,19 @@ show status like "Qcache_hits"; show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; + +set global query_cache_strip_comments=1; +(select a from t1) union (select a from t1); +(select a from t1) /* */union (select a from t1); +set global query_cache_strip_comments=0; +(select a from t1) union (select a from t1); +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; + drop table t1; + +# # SP cursors and selects with query cache (BUG#9715) # create table t1 (a int); -- cgit v1.2.1 From 190aa085577fb6e08aa861138036e50d7fc25313 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 14 Dec 2011 02:15:15 +0400 Subject: BUG#902632: Crash or invalid read at st_join_table::cleanup, st_table::disable_keyread - Do a "more thorough" cleanup of SJ-Materialization join tab in JOIN_TAB::cleanup. The bug was due to the fact that JOIN_TAB::cleanup() may be called multiple times for the same tab if the join has grouping. --- mysql-test/t/subselect_sj_mat.test | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index a8dbeded84a..9614a41767e 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1465,6 +1465,23 @@ SELECT MIN(a) FROM t1, t2 WHERE b IN (SELECT c FROM t3 GROUP BY c); DROP TABLE t1,t2,t3; +--echo # +--echo # BUG#902632: Crash or invalid read at st_join_table::cleanup, st_table::disable_keyread +--echo # +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 ( b INT ); +INSERT INTO t2 VALUES (3), (4); +CREATE TABLE t3 ( c INT ); +INSERT INTO t3 VALUES (5), (6); + +SELECT * FROM t1 WHERE EXISTS ( + SELECT DISTINCT b FROM t2 + WHERE b <= a + AND b IN ( SELECT c FROM t3 GROUP BY c ) + ); +DROP TABLE t1,t2,t3; + --echo # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; -- cgit v1.2.1 From d274e32c8c0363073e924446f02ff18f7c3c4821 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 13 Dec 2011 14:20:47 -0800 Subject: Fixed LP bug #902356. A memory overwrite in the function test_if_skip_sort_order() could cause a crash for some queries with subqueries. --- mysql-test/t/subselect2.test | 31 +++++++++++++++++++++++++++++++ 1 file changed, 31 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect2.test b/mysql-test/t/subselect2.test index 5f819ed39ba..8d2939bdb53 100644 --- a/mysql-test/t/subselect2.test +++ b/mysql-test/t/subselect2.test @@ -172,5 +172,36 @@ SELECT t1.* FROM t1 WHERE (SELECT COUNT(*) FROM t3,t2 WHERE t3.c=t2.a DROP TABLE t1,t2,t3; +--echo # +--echo # Bug #902356: DISTINCT in materialized subquery +--echo # + +CREATE TABLE t1 (pk int PRIMARY KEY, a int, KEY(a)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (0, 4), (8, 6); + +CREATE TABLE t2 (pk int PRIMARY KEY, a int, KEY(a)) ENGINE=InnoDB; +INSERT INTO t2 VALUES (0, 4), (8, 6); + +CREATE TABLE t3 (b INT, KEY(b)); +INSERT INTO t3 VALUES (7), (0), (4), (2); + +CREATE VIEW v1 AS SELECT * FROM t1; + +SET @tmp_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='materialization=on,in_to_exists=on'; + +EXPLAIN +SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM t1) OR a = b; +SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM t1) OR a = b; + +EXPLAIN +SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM v1) OR a = b; +SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM v1) OR a = b; + +SET optimizer_switch=@tmp_optimizer_switch; + +DROP VIEW v1; +DROP TABLE t1,t2,t3; + set optimizer_switch=@subselect2_test_tmp; -- cgit v1.2.1 From 05e0127478c39437be53668f0db1d674071e2485 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 14 Dec 2011 04:39:29 +0400 Subject: BUG#901506: Crash in TABLE_LIST::print on EXPLAIN EXTENDED - Let JTBM optimization code handle the case where the subquery is degenerate and doesn't have a join query plan. Regular materialization would fall back to IN->EXISTS for such cases. Semi-Join materialization does not have such option, instead we introduce and use "constant JTBM join tabs". --- mysql-test/t/subselect_sj_mat.test | 12 ++++++++++++ 1 file changed, 12 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index a8dbeded84a..12d04097a51 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1465,6 +1465,18 @@ SELECT MIN(a) FROM t1, t2 WHERE b IN (SELECT c FROM t3 GROUP BY c); DROP TABLE t1,t2,t3; +--echo # +--echo # BUG#901506: Crash in TABLE_LIST::print on EXPLAIN EXTENDED +--echo # +CREATE TABLE t1 ( a INT, KEY(a) ); +INSERT INTO t1 VALUES (8); + +EXPLAIN EXTENDED + SELECT * FROM t1 + WHERE a IN ( SELECT MIN(a) FROM t1 ); + +DROP TABLE t1; + --echo # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; -- cgit v1.2.1 From f5dac20f38fcf581b0616562cd2da21fb8c50218 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 15 Dec 2011 00:21:15 -0800 Subject: Made the optimizer switch flags 'outer_join_with_cache', 'semijoin_with_cache' set to 'on' by default. --- mysql-test/t/derived_view.test | 13 +++++++++++++ mysql-test/t/error_simulation.test | 5 +++++ mysql-test/t/explain.test | 3 +++ mysql-test/t/func_group.test | 3 +++ mysql-test/t/func_op.test | 3 +++ mysql-test/t/group_by.test | 9 +++++++++ mysql-test/t/group_min_max.test | 6 ++++++ mysql-test/t/join.test | 4 ++++ mysql-test/t/join_nested.test | 5 +++++ mysql-test/t/join_nested_jcl6.test | 3 +++ mysql-test/t/join_outer.test | 6 ++++++ mysql-test/t/join_outer_jcl6.test | 3 +++ mysql-test/t/myisam_icp.test | 5 +++++ mysql-test/t/order_by.test | 5 +++++ mysql-test/t/pool_of_threads.test | 4 +++- mysql-test/t/ps.test | 3 +++ mysql-test/t/select.test | 5 +++++ mysql-test/t/select_jcl6.test | 3 +++ mysql-test/t/subselect.test | 5 +++++ mysql-test/t/subselect4.test | 8 ++++++++ mysql-test/t/subselect_cache.test | 5 +++++ mysql-test/t/subselect_sj.test | 3 +++ mysql-test/t/subselect_sj2.test | 4 +++- mysql-test/t/subselect_sj2_jcl6.test | 3 +++ mysql-test/t/subselect_sj_jcl6.test | 3 +++ mysql-test/t/subselect_sj_nonmerged.test | 6 ++++++ mysql-test/t/table_elim.test | 4 ++++ mysql-test/t/type_datetime.test | 5 +++++ mysql-test/t/union.test | 3 +++ mysql-test/t/view.test | 5 +++++ 30 files changed, 140 insertions(+), 2 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index e35aca9b718..953a4c8fcef 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -925,6 +925,8 @@ INSERT INTO t2 VALUES (4,3,'r'); CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; SET SESSION optimizer_switch='derived_with_keys=off'; EXPLAIN SELECT * FROM t3 @@ -942,6 +944,7 @@ SELECT * FROM t3 SELECT * FROM t3 WHERE t3.b IN (SELECT v1.b FROM v1, t2 WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); +SET optimizer_switch=@save_optimizer_switch; DROP VIEW v1; DROP TABLE t1,t2,t3; @@ -1071,6 +1074,9 @@ INSERT INTO t2 VALUES (29,8,'c'), (39,7,'b'); CREATE TABLE t3 (b int); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + EXPLAIN EXTENDED SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t @@ -1105,6 +1111,8 @@ SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b <> 0 AND t.c = t1.a; +SET optimizer_switch=@save_optimizer_switch; + DROP TABLE t1,t2,t3; --echo # @@ -1119,6 +1127,9 @@ INSERT INTO t2 VALUES (6); CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + SET SESSION optimizer_switch = 'derived_with_keys=on'; SET SESSION join_cache_level = 4; @@ -1137,6 +1148,8 @@ SELECT * FROM t3 SET SESSION join_cache_level = default; +SET optimizer_switch=@save_optimizer_switch; + DROP VIEW v2; DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/error_simulation.test b/mysql-test/t/error_simulation.test index 15d172cc7e6..5dcc1fa9dcf 100644 --- a/mysql-test/t/error_simulation.test +++ b/mysql-test/t/error_simulation.test @@ -78,6 +78,9 @@ INSERT INTO t2 VALUES (1, 1, 'data'); --echo # we would need to have thousands of records and/or more columns in both --echo # tables so that the join buffer is filled and re-scans are triggered). +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + SET SESSION debug = '+d,only_one_Unique_may_be_created'; --replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x @@ -87,6 +90,8 @@ SELECT * FROM t1 LEFT JOIN t2 ON ( t2.a < 10 OR t2.b < 10 ); SET SESSION debug = DEFAULT; +SET optimizer_switch=@save_optimizer_switch; + DROP TABLE t1, t2; diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 6dbbdf5f28e..8700cd48c45 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -180,6 +180,8 @@ create table t2 (dt datetime not null); insert into t1 values ('2001-01-01 1:1:1', '1:1:1'), ('2001-01-01 1:1:1', '1:1:1'); insert into t2 values ('2001-01-01 1:1:1'), ('2001-01-01 1:1:1'); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; flush tables; EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); flush tables; @@ -188,6 +190,7 @@ flush tables; EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); flush tables; SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); +SET optimizer_switch=@save_optimizer_switch; drop tables t1, t2; --echo # diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 65b5b3936cc..de0eac10927 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -138,6 +138,8 @@ insert into t2 values('AAA', 10, 0.5); insert into t2 values('BBB', 20, 1.0); select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9; select max(t2.a1), max(t1.a1) from t1, t2 where t2.a2=9; select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10; @@ -146,6 +148,7 @@ select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=20; select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=10; select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1='AAA'; select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10; +SET optimizer_switch=@save_optimizer_switch; drop table t1,t2; # diff --git a/mysql-test/t/func_op.test b/mysql-test/t/func_op.test index 0a4f5034f4c..13fa40b513b 100644 --- a/mysql-test/t/func_op.test +++ b/mysql-test/t/func_op.test @@ -28,7 +28,10 @@ create table t1(a int); create table t2(a int, b int); insert into t1 values (1), (2), (3); insert into t2 values (1, 7), (3, 7); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; select t1.a, t2.a, t2.b, bit_count(t2.b) from t1 left join t2 on t1.a=t2.a; +SET optimizer_switch=@save_optimizer_switch; drop table t1, t2; # End of 4.1 tests diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index ec64aaed18f..5d7421904d2 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -338,8 +338,11 @@ drop table t1,t2; CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID )); insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; select S.ID as xID, S.ID1 as xID1 from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2; select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2 group by xID order by xID1; +SET optimizer_switch=@save_optimizer_switch; drop table t1; # @@ -1031,16 +1034,22 @@ EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2) USE INDEX FOR JOIN (i2,i2); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; EXPLAIN SELECT 1 FROM t1 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); +SET optimizer_switch=@save_optimizer_switch; CREATE TABLE t2 (a INT, b INT, KEY(a)); INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; EXPLAIN SELECT 1 FROM t2 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); +SET optimizer_switch=@save_optimizer_switch; SHOW VARIABLES LIKE 'old'; --error ER_INCORRECT_GLOBAL_LOCAL_VAR diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index 391d997d8b6..c3fc1f4eab7 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -428,6 +428,8 @@ select a1,a2,b,c,min(c), max(c) from t1 where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' ) group by a1,a2,b; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; explain select a1,a2,b,c,min(c), max(c) from t1 where exists ( select * from t2 where t2.c in (select c from t3 where t3.c > t1.b) and @@ -439,6 +441,7 @@ where exists ( select * from t2 where t2.c in (select c from t3 where t3.c > t1.b) and t2.c > 'b1' ) group by a1,a2,b; +SET optimizer_switch=@save_optimizer_switch; # correlated subselect that references the min/max argument explain select a1,a2,b,c,min(c), max(c) from t1 @@ -449,6 +452,8 @@ select a1,a2,b,c,min(c), max(c) from t1 where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' ) group by a1,a2,b; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; explain select a1,a2,b,c,min(c), max(c) from t1 where exists ( select * from t2 where t2.c in (select c from t3 where t3.c > t1.c) and @@ -460,6 +465,7 @@ where exists ( select * from t2 where t2.c in (select c from t3 where t3.c > t1.c) and t2.c > 'b1' ) group by a1,a2,b; +SET optimizer_switch=@save_optimizer_switch; # A,B,C) Predicates referencing mixed classes of attributes diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 82e67904e9f..bfe5f085e93 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -5,6 +5,9 @@ drop table if exists t1,t2,t3; drop view if exists v1,v2; --enable_warnings +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + # # Test different join syntaxes # @@ -1025,3 +1028,4 @@ insert into t2 values ('1:1:1'); select t2.i from t1 left join t2 on t2.i = t1.i where t1.i = '1:1:1'; drop table t1,t2; +SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index b617331de38..fa7b59e84c3 100644 --- a/mysql-test/t/join_nested.test +++ b/mysql-test/t/join_nested.test @@ -3,6 +3,9 @@ DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; --enable_warnings +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=ifnull(@optimizer_switch_for_join_nested_test,'outer_join_with_cache=off'); + CREATE TABLE t0 (a int, b int, c int); CREATE TABLE t1 (a int, b int, c int); CREATE TABLE t2 (a int, b int, c int); @@ -1271,5 +1274,7 @@ SELECT * FROM t1 LEFT JOIN DROP TABLE t1,t2,t3,t4; +SET optimizer_switch=@save_optimizer_switch; + --echo End of 5.0 tests diff --git a/mysql-test/t/join_nested_jcl6.test b/mysql-test/t/join_nested_jcl6.test index 809755b1fbf..f250702da7e 100644 --- a/mysql-test/t/join_nested_jcl6.test +++ b/mysql-test/t/join_nested_jcl6.test @@ -11,6 +11,8 @@ set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set join_cache_level=6; show variables like 'join_cache_level'; +set @optimizer_switch_for_join_nested_test=@@optimizer_switch; + --source t/join_nested.test # @@ -104,3 +106,4 @@ set join_cache_level=default; show variables like 'join_cache_level'; set @@optimizer_switch=@save_optimizer_switch_jcl6; +set @optimizer_switch_for_join_nested_test=NULL; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index f88759c7b67..3bd5532ada0 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -8,6 +8,10 @@ drop table if exists t0,t1,t2,t3,t4,t5; --enable_warnings +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=ifnull(@optimizer_switch_for_join_outer_test,'outer_join_with_cache=off'); + + CREATE TABLE t1 ( grp int(11) default NULL, a bigint(20) unsigned default NULL, @@ -1209,3 +1213,5 @@ DEALLOCATE PREPARE stmt; SET SESSION join_cache_level=default; DROP TABLE t1,t2,t3; + +SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/join_outer_jcl6.test b/mysql-test/t/join_outer_jcl6.test index 025e44493af..ba0a6686a51 100644 --- a/mysql-test/t/join_outer_jcl6.test +++ b/mysql-test/t/join_outer_jcl6.test @@ -11,9 +11,12 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set join_cache_level=6; show variables like 'join_cache_level'; +set @optimizer_switch_for_join_outer_test=@@optimizer_switch; + --source t/join_outer.test set join_cache_level=default; show variables like 'join_cache_level'; set @@optimizer_switch=@save_optimizer_switch_jcl6; +set @optimizer_switch_for_join_outer_test=NULL; diff --git a/mysql-test/t/myisam_icp.test b/mysql-test/t/myisam_icp.test index bbff6c30e56..0e306a850c5 100644 --- a/mysql-test/t/myisam_icp.test +++ b/mysql-test/t/myisam_icp.test @@ -245,6 +245,9 @@ CREATE TABLE t4 ( b int, c varchar(1), KEY (b)); INSERT INTO t4 VALUES (7,'c'); INSERT INTO t4 VALUES (7,'c'); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + --echo # Must be t1,t2,t3,t4, with t4 having Full-scan-on-NULL but not Using index condition explain SELECT * FROM t1 LEFT JOIN t2 ON t1.c=t2.b @@ -255,6 +258,8 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.c=t2.b WHERE t2.b NOT IN (SELECT t4.b FROM t3 STRAIGHT_JOIN t4 WHERE t4.b <= 2 AND t4.c = t3.c); +SET optimizer_switch=@save_optimizer_switch; + DROP TABLE t1,t2,t3,t4; set optimizer_switch=@myisam_icp_tmp; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 0fc0d125c1a..c7a958b293a 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -157,6 +157,9 @@ INSERT INTO t2 VALUES (2,25); INSERT INTO t3 VALUES (1,'123 Park Place'); INSERT INTO t3 VALUES (2,'453 Boardwalk'); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + SELECT a,b,if(b = 1,i,if(b = 2,v,'')) FROM t1 LEFT JOIN t2 USING(c) @@ -179,6 +182,8 @@ LEFT JOIN t2 ON t1.c = t2.c LEFT JOIN t3 ON t3.c = t1.c ORDER BY a; +SET optimizer_switch=@save_optimizer_switch; + drop table t1,t2,t3; # diff --git a/mysql-test/t/pool_of_threads.test b/mysql-test/t/pool_of_threads.test index 530038cee91..5cde9a9fb4b 100644 --- a/mysql-test/t/pool_of_threads.test +++ b/mysql-test/t/pool_of_threads.test @@ -2,11 +2,13 @@ # and run a number of tests -- source include/have_pool_of_threads.inc +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; # Slow test, don't run during staging part -- source include/not_staging.inc -- source include/long_test.inc -- source include/common-tests.inc - +SET optimizer_switch=@save_optimizer_switch; # Test that we cannot have more simultaneous connections than # --thread-pool-size on the standard port, but _can_ have additional diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 357e7d4fe8f..a3b064acb04 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -3095,10 +3095,13 @@ DROP TABLE t1; --echo # CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1),(2); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; PREPARE stmt FROM 'EXPLAIN EXTENDED SELECT 1 FROM t1 RIGHT JOIN t1 t2 ON 1'; EXECUTE stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; +SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1; --echo # diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 0106c3d54fb..9c037fa9106 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -13,6 +13,9 @@ drop table if exists t1_1,t1_2,t9_1,t9_2,t1aa,t2aa; drop view if exists v1; --enable_warnings +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=ifnull(@optimizer_switch_for_select_test,'outer_join_with_cache=off'); + CREATE TABLE t1 ( Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL @@ -4259,3 +4262,5 @@ SELECT * FROM t1 WHERE f1 = 'zz' AND f1 <= 'aa' ; SELECT * FROM v1 HAVING f1 = 'zz' AND f1 <= 'aa' ; DROP TABLE t1; DROP VIEW v1; + +SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/select_jcl6.test b/mysql-test/t/select_jcl6.test index 295efa632db..29c86679515 100644 --- a/mysql-test/t/select_jcl6.test +++ b/mysql-test/t/select_jcl6.test @@ -11,9 +11,12 @@ set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set join_cache_level=6; show variables like 'join_cache_level'; +set @optimizer_switch_for_select_test=@@optimizer_switch; + --source t/select.test set join_cache_level=default; show variables like 'join_cache_level'; set @@optimizer_switch=@save_optimizer_switch_jcl6; +set @optimizer_switch_for_select_test=NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 4f6999bf611..13bbc01af17 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -4587,6 +4587,9 @@ INSERT INTO t2 VALUES (20,9),(20,9); create table t3 (d int, e int); insert into t3 values (2, 9), (3,10); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + EXPLAIN SELECT t2.b , t1.c FROM t2 LEFT JOIN t1 ON t1.c < 3 @@ -4596,6 +4599,8 @@ SELECT t2.b , t1.c FROM t2 LEFT JOIN t1 ON t1.c < 3 WHERE (t2.b, t1.c) NOT IN (SELECT * from t3); +SET optimizer_switch=@save_optimizer_switch; + drop table t1, t2, t3; --echo End of 5.3 tests diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 0fd89bee42b..aa3cb30c6f3 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -991,6 +991,9 @@ INSERT INTO t3 VALUES ('a'), ('b'), ('c'); CREATE TABLE t4 (c1 varchar(1) primary key); INSERT INTO t4 VALUES ('k'), ('d'); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); @@ -1003,6 +1006,9 @@ SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SEL EXPLAIN SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2); + +SET optimizer_switch=@save_optimizer_switch; + drop table t1, t2, t3, t4; --echo # @@ -1297,6 +1303,8 @@ INSERT INTO t2 VALUES (10,5,'d1d'); set @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + set @@optimizer_switch = 'materialization=off'; EXPLAIN diff --git a/mysql-test/t/subselect_cache.test b/mysql-test/t/subselect_cache.test index 6bf5028b6cf..0abd20cc50e 100644 --- a/mysql-test/t/subselect_cache.test +++ b/mysql-test/t/subselect_cache.test @@ -4,6 +4,9 @@ drop table if exists t1,t2,t3,t4,t5; drop view if exists v1; --enable_warnings +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; +SET optimizer_switch='semijoin_with_cache=off'; set optimizer_switch='subquery_cache=on'; @@ -1698,5 +1701,7 @@ SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 ); drop view v1; drop table t1,t2,t3,t4; +SET optimizer_switch=@save_optimizer_switch; + --echo # restore default set @@optimizer_switch= default; diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 4f8ed7f1643..3b419dc0d09 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -10,6 +10,9 @@ drop procedure if exists p1; set @subselect_sj_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'outer_join_with_cache=off'); +SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'semijoin_with_cache=off'); + # The 'default' value within the scope of this test: set @save_optimizer_switch=@@optimizer_switch; diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index 2ec15d4dfae..5229546c08e 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -6,6 +6,9 @@ set @subselect_sj2_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'outer_join_with_cache=off'); +SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'semijoin_with_cache=off'); + --disable_warnings drop table if exists t0, t1, t2, t3, t4, t5; drop view if exists v1; @@ -46,7 +49,6 @@ create table t3 ( insert into t3 select a,a, a,a,a from t0; insert into t3 select a,a, a+100,a+100,a+100 from t0; - explain select * from t3 where b in (select a from t1); select * from t3 where b in (select a from t1); diff --git a/mysql-test/t/subselect_sj2_jcl6.test b/mysql-test/t/subselect_sj2_jcl6.test index 95f84a9e89e..0b20c39c183 100644 --- a/mysql-test/t/subselect_sj2_jcl6.test +++ b/mysql-test/t/subselect_sj2_jcl6.test @@ -11,6 +11,8 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set join_cache_level=6; show variables like 'join_cache_level'; +set @optimizer_switch_for_subselect_sj2_test=@@optimizer_switch; + --source t/subselect_sj2.test --echo # @@ -101,4 +103,5 @@ set join_cache_level=default; show variables like 'join_cache_level'; set @@optimizer_switch=@save_optimizer_switch_jcl6; +set @optimizer_switch_for_subselect_sj2_test=NULL; diff --git a/mysql-test/t/subselect_sj_jcl6.test b/mysql-test/t/subselect_sj_jcl6.test index fc539ec1a01..bfb867830c4 100644 --- a/mysql-test/t/subselect_sj_jcl6.test +++ b/mysql-test/t/subselect_sj_jcl6.test @@ -13,6 +13,8 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set join_cache_level=6; show variables like 'join_cache_level'; +set @optimizer_switch_for_subselect_sj_test=@@optimizer_switch; + --source t/subselect_sj.test --echo # @@ -91,3 +93,4 @@ set join_cache_level=default; show variables like 'join_cache_level'; set @@optimizer_switch=@save_optimizer_switch_jcl6; +set @optimizer_switch_for_subselect_sj_test=NULL; diff --git a/mysql-test/t/subselect_sj_nonmerged.test b/mysql-test/t/subselect_sj_nonmerged.test index 4f50b4cbc4d..e47e72ffe97 100644 --- a/mysql-test/t/subselect_sj_nonmerged.test +++ b/mysql-test/t/subselect_sj_nonmerged.test @@ -55,13 +55,19 @@ explain select * from t3 where a in (select max(t2.a) from t1, t2 group by t2.b) --echo # Compare to this which really will have 50 record combinations: explain select * from t3 where a in (select max(t2.a) from t1, t2 group by t2.b, t1.b); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + --echo # Outer joins also work: explain select * from t3 where a in (select max(t2.a) from t1 left join t2 on t1.a=t2.a group by t2.b, t1.b); +SET optimizer_switch=@save_optimizer_switch; + # # Check if joins on the outer side also work # + create table t4 (a int, b int, filler char(20), unique key(a,b)); insert into t4 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t0 A, t0 B; # 100 rows explain select * from t0, t4 where diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test index 3b584ce2b38..dc32618eb8c 100644 --- a/mysql-test/t/table_elim.test +++ b/mysql-test/t/table_elim.test @@ -6,6 +6,9 @@ drop table if exists t0, t1, t2, t3, t4, t5, t6; drop view if exists v1, v2; --enable_warnings +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + create table t1 (a int); insert into t1 values (0),(1),(2),(3); create table t0 as select * from t1; @@ -518,3 +521,4 @@ EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b ); drop view v1; DROP TABLE t1,t2,t3; +SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index 4e091e14b30..f9ee8dfd5d3 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -359,6 +359,9 @@ create table t2 (id int(10) not null, cur_date date not null); insert into t1 (id, cur_date) values (1, '2007-04-25 18:30:22'); insert into t2 (id, cur_date) values (1, '2007-04-25'); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; + explain extended select * from t1 where id in (select id from t1 as x1 where (t1.cur_date is null)); @@ -386,6 +389,8 @@ where id in (select id from t2 as x1 where (t2.cur_date is null)); select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); +SET optimizer_switch=@save_optimizer_switch; + drop table t1,t2; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 507f0ffa09f..d3bc94961ef 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -563,7 +563,10 @@ drop table t1; # create table t1 ( RID int(11) not null default '0', IID int(11) not null default '0', nada varchar(50) not null,NAME varchar(50) not null,PHONE varchar(50) not null) engine=MyISAM; insert into t1 ( RID,IID,nada,NAME,PHONE) values (1, 1, 'main', 'a', '111'), (2, 1, 'main', 'b', '222'), (3, 1, 'main', 'c', '333'), (4, 1, 'main', 'd', '444'), (5, 1, 'main', 'e', '555'), (6, 2, 'main', 'c', '333'), (7, 2, 'main', 'd', '454'), (8, 2, 'main', 'e', '555'), (9, 2, 'main', 'f', '666'), (10, 2, 'main', 'g', '777'); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 A left join t1 B on A.NAME = B.NAME and B.IID = 2 where A.IID = 1 and (A.PHONE <> B.PHONE or B.NAME is null) union select A.NAME, A.PHONE, B.NAME, B.PHONE from t1 B left join t1 A on B.NAME = A.NAME and A.IID = 1 where B.IID = 2 and (A.PHONE <> B.PHONE or A.NAME is null); +SET optimizer_switch=@save_optimizer_switch; drop table t1; # diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 7486ffc38f8..821bbe055e6 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -5,6 +5,9 @@ drop database if exists mysqltest; --enable_warnings use test; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='outer_join_with_cache=off'; + # # some basic test of views and its functionality # @@ -4308,3 +4311,5 @@ SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM DROP VIEW v2; DROP TABLE t1, t2, t3; + +SET optimizer_switch=@save_optimizer_switch; -- cgit v1.2.1 From a910e8ef5b5d33cd600acaba9ec3cc8c49881196 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 15 Dec 2011 14:26:59 -0800 Subject: Made join_cache_level == 2 by default. --- mysql-test/t/derived_view.test | 3 +++ mysql-test/t/distinct.test | 6 ++++++ mysql-test/t/greedy_optimizer.test | 5 +++++ mysql-test/t/join_cache.test | 6 +++++- mysql-test/t/join_nested.test | 9 +++++++++ mysql-test/t/join_nested_jcl6.test | 2 ++ mysql-test/t/join_outer.test | 9 ++++++++- mysql-test/t/join_outer_jcl6.test | 2 ++ mysql-test/t/maria_mrr.test | 5 +++++ mysql-test/t/select.test | 8 ++++++++ mysql-test/t/select_jcl6.test | 2 ++ mysql-test/t/subselect.test | 11 ++++++++++- mysql-test/t/subselect3.test | 8 ++++++++ mysql-test/t/subselect3_jcl6.test | 3 +++ mysql-test/t/subselect_no_mat.test | 5 +++++ mysql-test/t/subselect_no_opts.test | 4 ++++ mysql-test/t/subselect_no_scache.test | 4 ++++ mysql-test/t/subselect_no_semijoin.test | 4 ++++ mysql-test/t/subselect_sj.test | 8 ++++++++ mysql-test/t/subselect_sj2.test | 8 ++++++++ mysql-test/t/subselect_sj2_jcl6.test | 2 ++ mysql-test/t/subselect_sj_jcl6.test | 2 ++ mysql-test/t/subselect_sj_mat.test | 3 +++ 23 files changed, 116 insertions(+), 3 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 953a4c8fcef..581d01058db 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -4,9 +4,11 @@ drop view if exists v1,v2,v3,v4; --enable_warnings set @exit_optimizer_switch=@@optimizer_switch; +set @exit_join_cache_level=@@join_cache_level; set optimizer_switch='derived_merge=on,derived_with_keys=on'; # The 'default' value within the scope of this test: set @save_optimizer_switch=@@optimizer_switch; +set join_cache_level=1; create table t1(f1 int, f11 int); create table t2(f2 int, f22 int); @@ -1276,3 +1278,4 @@ drop table t1,t2,t3; # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; +set join_cache_level=@exit_join_cache_level; diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index 796732fa097..0f0cbcf26d0 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -158,6 +158,10 @@ create table t3 ( insert into t1 values (1,'yes'), (2,'no'); insert into t2 values (1,1); insert into t3 values (1,1); + +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=1; + EXPLAIN SELECT DISTINCT t1.id @@ -195,6 +199,8 @@ WHERE AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2)); drop table t1,t2,t3; +set join_cache_level=@save_join_cache_level; + # # Test using DISTINCT on a function that contains a group function # This also test the case when one doesn't use all fields in GROUP BY. diff --git a/mysql-test/t/greedy_optimizer.test b/mysql-test/t/greedy_optimizer.test index 5131c97f122..8f969f2562a 100644 --- a/mysql-test/t/greedy_optimizer.test +++ b/mysql-test/t/greedy_optimizer.test @@ -10,6 +10,9 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7; --enable_warnings +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=1; + create table t1 ( c11 integer,c12 integer,c13 integer,c14 integer,c15 integer,c16 integer, primary key (c11) @@ -384,3 +387,5 @@ SET optimizer_search_depth = DEFAULT; DROP TABLE t1,t2,t2_1,t3,t3_1,t4,t4_1,t5,t5_1; --echo End of 5.0 tests + +set join_cache_level=@save_join_cache_level; diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 5a48f7653e4..0feb4e30af0 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -33,6 +33,8 @@ SELECT COUNT(*) FROM CountryLanguage; show variables like 'join_buffer_size'; +set join_cache_level=1; + show variables like 'join_cache_level'; EXPLAIN @@ -196,7 +198,7 @@ SELECT Country.Name, Country.Population, City.Name, City.Population DROP INDEX City_Population ON City; DROP INDEX City_Name ON City; -set join_cache_level=default; +set join_cache_level=1; set join_buffer_size=256; show variables like 'join_buffer_size'; @@ -2191,6 +2193,8 @@ insert into t2 values (1,1),(2,2); create table t3 (a int, b int); insert into t3 values (1,1),(2,2); +set join_cache_level=1; + explain select t1.* from t1,t2,t3; select t1.* from t1,t2,t3; diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index fa7b59e84c3..3168e95f620 100644 --- a/mysql-test/t/join_nested.test +++ b/mysql-test/t/join_nested.test @@ -5,6 +5,15 @@ DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch=ifnull(@optimizer_switch_for_join_nested_test,'outer_join_with_cache=off'); +if (`select @join_cache_level_for_join_nested_test is null`) +{ + set join_cache_level=1; +} +if (`select @join_cache_level_for_join_nested_test is not null`) +{ + set join_cache_level=@join_cache_level_for_join_nested_test; +} + CREATE TABLE t0 (a int, b int, c int); CREATE TABLE t1 (a int, b int, c int); diff --git a/mysql-test/t/join_nested_jcl6.test b/mysql-test/t/join_nested_jcl6.test index f250702da7e..0e8646bceda 100644 --- a/mysql-test/t/join_nested_jcl6.test +++ b/mysql-test/t/join_nested_jcl6.test @@ -12,6 +12,7 @@ set join_cache_level=6; show variables like 'join_cache_level'; set @optimizer_switch_for_join_nested_test=@@optimizer_switch; +set @join_cache_level_for_join_nested_test=@@join_cache_level; --source t/join_nested.test @@ -107,3 +108,4 @@ show variables like 'join_cache_level'; set @@optimizer_switch=@save_optimizer_switch_jcl6; set @optimizer_switch_for_join_nested_test=NULL; +set @join_cache_level_for_join_nested_test=NULL; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 3bd5532ada0..04816402205 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -10,7 +10,14 @@ drop table if exists t0,t1,t2,t3,t4,t5; SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch=ifnull(@optimizer_switch_for_join_outer_test,'outer_join_with_cache=off'); - +if (`select @join_cache_level_for_join_outer_test is null`) +{ + set join_cache_level=1; +} +if (`select @join_cache_level_for_join_outer_test is not null`) +{ + set join_cache_level=@join_cache_level_for_join_outer_test; +} CREATE TABLE t1 ( grp int(11) default NULL, diff --git a/mysql-test/t/join_outer_jcl6.test b/mysql-test/t/join_outer_jcl6.test index ba0a6686a51..e34cc615216 100644 --- a/mysql-test/t/join_outer_jcl6.test +++ b/mysql-test/t/join_outer_jcl6.test @@ -12,6 +12,7 @@ set join_cache_level=6; show variables like 'join_cache_level'; set @optimizer_switch_for_join_outer_test=@@optimizer_switch; +set @join_cache_level_for_join_outer_test=@@join_cache_level; --source t/join_outer.test @@ -20,3 +21,4 @@ show variables like 'join_cache_level'; set @@optimizer_switch=@save_optimizer_switch_jcl6; set @optimizer_switch_for_join_outer_test=NULL; +set @join_cache_level_for_join_outer_test=NULL; diff --git a/mysql-test/t/maria_mrr.test b/mysql-test/t/maria_mrr.test index 4cd4c277a7f..fe7dc7acc79 100644 --- a/mysql-test/t/maria_mrr.test +++ b/mysql-test/t/maria_mrr.test @@ -78,6 +78,9 @@ INSERT INTO t3 VALUES (88, 442, 'y'), (99, 445, 'w'), (87, 442, 'z'), (98, 445, 'v'), (86, 442, 'x'), (97, 445, 't'), (85, 442, 'b'), (96, 445, 'l'), (84, 442, 'a'), (95, 445, 'k'); +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=1; + SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx) WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; EXPLAIN @@ -90,6 +93,8 @@ EXPLAIN SELECT COUNT(t1.v) FROM t1, t2, t3 WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; +set join_cache_level=@save_join_cache_level; + DROP TABLE t1,t2,t3; --echo # diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 9c037fa9106..75ea88b6bde 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -15,6 +15,14 @@ drop view if exists v1; SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch=ifnull(@optimizer_switch_for_select_test,'outer_join_with_cache=off'); +if (`select @join_cache_level_for_select_test is null`) +{ + set join_cache_level=1; +} +if (`select @join_cache_level_for_select_test is not null`) +{ + set join_cache_level=@join_cache_level_for_select_test; +} CREATE TABLE t1 ( Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, diff --git a/mysql-test/t/select_jcl6.test b/mysql-test/t/select_jcl6.test index 29c86679515..f7c1aa988c7 100644 --- a/mysql-test/t/select_jcl6.test +++ b/mysql-test/t/select_jcl6.test @@ -12,6 +12,7 @@ set join_cache_level=6; show variables like 'join_cache_level'; set @optimizer_switch_for_select_test=@@optimizer_switch; +set @join_cache_level_for_select_test=@@join_cache_level; --source t/select.test @@ -20,3 +21,4 @@ show variables like 'join_cache_level'; set @@optimizer_switch=@save_optimizer_switch_jcl6; set @optimizer_switch_for_select_test=NULL; +set @join_cache_level_for_select_test=NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 13bbc01af17..d53ba706388 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -15,7 +15,16 @@ drop view if exists v2; set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); -set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; + +if (`select @join_cache_level_for_subselect_test is null`) +{ + set join_cache_level=1; +} +if (`select @join_cache_level_for_subselect_test is not null`) +{ + set join_cache_level=@join_cache_level_for_subselect_test; +} + set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; select (select 2); explain extended select (select 2); diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index aadc08e18e0..ec6ceff8822 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -4,6 +4,14 @@ drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22; set @subselect3_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'; +if (`select @join_cache_level_for_subselect3_test is null`) +{ + set join_cache_level=1; +} +if (`select @join_cache_level_for_subselect3_test is not null`) +{ + set join_cache_level=@join_cache_level_for_subselect3_test; +} # # 1. Subquery with GROUP/HAVING diff --git a/mysql-test/t/subselect3_jcl6.test b/mysql-test/t/subselect3_jcl6.test index 8d880809476..e4b1c144b93 100644 --- a/mysql-test/t/subselect3_jcl6.test +++ b/mysql-test/t/subselect3_jcl6.test @@ -11,9 +11,12 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set join_cache_level=6; show variables like 'join_cache_level'; +set @join_cache_level_for_subselect3_test=@@join_cache_level; + --source t/subselect3.test set join_cache_level=default; show variables like 'join_cache_level'; set @@optimizer_switch=@save_optimizer_switch; +set @join_cache_level_for_subselect3_test=NULL; diff --git a/mysql-test/t/subselect_no_mat.test b/mysql-test/t/subselect_no_mat.test index 0265ec91e88..ccd93af64ce 100644 --- a/mysql-test/t/subselect_no_mat.test +++ b/mysql-test/t/subselect_no_mat.test @@ -5,8 +5,13 @@ select @@optimizer_switch like '%materialization=on%'; set optimizer_switch='materialization=off'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set join_cache_level=1; +set @join_cache_level_for_subselect_test=@@join_cache_level; + --source t/subselect.test set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; +set @join_cache_level_for_subselect_test=NULL; + diff --git a/mysql-test/t/subselect_no_opts.test b/mysql-test/t/subselect_no_opts.test index 724cbab6310..8a699fefaf7 100644 --- a/mysql-test/t/subselect_no_opts.test +++ b/mysql-test/t/subselect_no_opts.test @@ -4,6 +4,10 @@ set @optimizer_switch_for_subselect_test='materialization=off,semijoin=off,subquery_cache=off,mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; + +set join_cache_level=1; +set @join_cache_level_for_subselect_test=@@join_cache_level; + --source t/subselect.test set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/t/subselect_no_scache.test b/mysql-test/t/subselect_no_scache.test index fe8ff749a59..a8ff559b82b 100644 --- a/mysql-test/t/subselect_no_scache.test +++ b/mysql-test/t/subselect_no_scache.test @@ -4,8 +4,12 @@ select @@optimizer_switch like '%subquery_cache=on%'; set optimizer_switch='subquery_cache=off'; +set join_cache_level=1; +set @join_cache_level_for_subselect_test=@@join_cache_level; + --source t/subselect.test set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; +set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect_no_semijoin.test b/mysql-test/t/subselect_no_semijoin.test index c836c12ec50..46791667173 100644 --- a/mysql-test/t/subselect_no_semijoin.test +++ b/mysql-test/t/subselect_no_semijoin.test @@ -3,6 +3,10 @@ # set @optimizer_switch_for_subselect_test='semijoin=off,mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set join_cache_level=1; +set @join_cache_level_for_subselect_test=@@join_cache_level; + --source t/subselect.test set @optimizer_switch_for_subselect_test=null; +set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 3b419dc0d09..6cd74b9689d 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -12,6 +12,14 @@ set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'outer_join_with_cache=off'); SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj_test,'semijoin_with_cache=off'); +if (`select @join_cache_level_for_subselect_sj_test is null`) +{ + set join_cache_level=1; +} +if (`select @join_cache_level_for_subselect_sj_test is not null`) +{ + set join_cache_level=@join_cache_level_for_subselect_sj_test; +} # The 'default' value within the scope of this test: set @save_optimizer_switch=@@optimizer_switch; diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index 5229546c08e..9a664ee9881 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -8,6 +8,14 @@ set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'outer_join_with_cache=off'); SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'semijoin_with_cache=off'); +if (`select @join_cache_level_for_subselect_sj2_test is null`) +{ + set join_cache_level=1; +} +if (`select @join_cache_level_for_subselect_sj2_test is not null`) +{ + set join_cache_level=@join_cache_level_for_subselect_sj2_test; +} --disable_warnings drop table if exists t0, t1, t2, t3, t4, t5; diff --git a/mysql-test/t/subselect_sj2_jcl6.test b/mysql-test/t/subselect_sj2_jcl6.test index 0b20c39c183..09212ba63d0 100644 --- a/mysql-test/t/subselect_sj2_jcl6.test +++ b/mysql-test/t/subselect_sj2_jcl6.test @@ -12,6 +12,7 @@ set join_cache_level=6; show variables like 'join_cache_level'; set @optimizer_switch_for_subselect_sj2_test=@@optimizer_switch; +set @join_cache_level_for_subselect_sj2_test=@@join_cache_level; --source t/subselect_sj2.test @@ -104,4 +105,5 @@ show variables like 'join_cache_level'; set @@optimizer_switch=@save_optimizer_switch_jcl6; set @optimizer_switch_for_subselect_sj2_test=NULL; +set @join_cache_level_subselect_sj2_test=NULL; diff --git a/mysql-test/t/subselect_sj_jcl6.test b/mysql-test/t/subselect_sj_jcl6.test index bfb867830c4..4eeaa465b11 100644 --- a/mysql-test/t/subselect_sj_jcl6.test +++ b/mysql-test/t/subselect_sj_jcl6.test @@ -14,6 +14,7 @@ set join_cache_level=6; show variables like 'join_cache_level'; set @optimizer_switch_for_subselect_sj_test=@@optimizer_switch; +set @join_cache_level_for_subselect_sj_test=@@join_cache_level; --source t/subselect_sj.test @@ -94,3 +95,4 @@ show variables like 'join_cache_level'; set @@optimizer_switch=@save_optimizer_switch_jcl6; set @optimizer_switch_for_subselect_sj_test=NULL; +set @join_cache_level_subselect_sj_test=NULL; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index e660c63df5b..a0d24aa53ed 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -7,6 +7,8 @@ set @subselect_sj_mat_tmp= @@optimizer_switch; set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'); set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set @optimizer_switch_local_default= @@optimizer_switch; +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=1; --disable_warnings drop table if exists t1, t2, t3, t4, t5, t1i, t2i, t3i; @@ -1497,4 +1499,5 @@ DROP TABLE t1; --echo # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; +set join_cache_level=@save_join_cache_level; -- cgit v1.2.1 From 04e9004fa32c9066788db6f2633022a912f349e2 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Fri, 16 Dec 2011 03:44:25 +0400 Subject: BUG#901399: Wrong result (extra row) with semijoin=ON, materialization=OFF, optimizer_prune_level=0 - Correctly handle plan refinement stage for LooseScan plans: run create_ref_for_key() if LooseScan plan includes a ref access, and if we don't have any fixed key components, switch to a full index scan. --- mysql-test/t/subselect_sj.test | 29 +++++++++++++++++++++++++++++ 1 file changed, 29 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 4f8ed7f1643..55074787700 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -1967,5 +1967,34 @@ SET optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2,t3,t4; +--echo # +--echo # BUG#901399: Wrong result (extra row) with semijoin=ON, materialization=OFF, optimizer_prune_level=0 +--echo # +set @opl_901399= @@optimizer_prune_level; +set @os_091399= @@optimizer_switch; +SET optimizer_prune_level=0; +SET optimizer_switch = 'materialization=off'; + +CREATE TABLE t1 ( c INT ) ENGINE=MyISAM; +INSERT INTO t1 VALUES + (0),(1),(2),(3),(4),(5), + (6),(7),(8),(9),(10),(11),(12); +CREATE TABLE t2 ( a INT, b INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3,20),(2,21),(3,22); + +SELECT * +FROM t1 AS alias1, t1 AS alias2 +WHERE ( alias1.c, alias2.c ) + IN ( + SELECT alias3.a, alias3.a + FROM t2 AS alias3, t2 alias4 + WHERE alias3.b = alias4.b + ); +set optimizer_prune_level= @opl_901399; +set optimizer_switch= @os_091399; + +DROP TABLE t1,t2; + + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; -- cgit v1.2.1 From be3e52984fe20f5aa7862cf9ace86beb588d3240 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Mon, 19 Dec 2011 20:58:55 +0400 Subject: BUG#904432: Wrong result with LEFT JOIN, constant table, semijoin=ON,materialization=ON - Correct handling for SJ-Materialization + outer joins (details in the comments in the code) --- mysql-test/t/subselect_sj_mat.test | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index a0d24aa53ed..b9e0beaa5e3 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1497,6 +1497,19 @@ EXPLAIN EXTENDED DROP TABLE t1; +--echo # +--echo # BUG#904432: Wrong result with LEFT JOIN, constant table, semijoin=ON,materialization=ON +--echo # +CREATE TABLE t1 ( a INT ) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4); +CREATE TABLE t2 ( b INT NOT NULL, c INT ); +INSERT INTO t2 VALUES (4,2),(4,2),(4,4),(1,1); + +SELECT * FROM t1 LEFT JOIN t2 ON ( a = b ) + WHERE a IN ( SELECT c FROM t2 ); + +DROP TABLE t1,t2; + --echo # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; -- cgit v1.2.1 From 15ea7238e42ea62da32c926c0a1667802f7646d9 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Mon, 19 Dec 2011 22:24:10 +0400 Subject: BUG#906385: EXPLAIN EXTENDED crashes in TABLE_LIST::print with limited max_join_size - Take into account that subquery's optimization can fail because of @@max_join_size error. --- mysql-test/t/subselect_sj2_mat.test | 29 +++++++++++++++++++++++++++++ 1 file changed, 29 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test index fdfa0f311d3..7c3b37b517a 100644 --- a/mysql-test/t/subselect_sj2_mat.test +++ b/mysql-test/t/subselect_sj2_mat.test @@ -8,3 +8,32 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; + +--echo # +--echo # BUG#906385: EXPLAIN EXTENDED crashes in TABLE_LIST::print with limited max_join_size +--echo # +CREATE TABLE t1 ( a INT ); +CREATE TABLE t2 ( b INT ); + +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t2 VALUES + (1),(2),(3),(4),(5), + (6),(7),(8),(9),(10), + (11),(12),(13),(14),(15), + (16),(17),(18),(19),(20); + +set @tmp_906385=@@max_join_size; +SET max_join_size = 80; + +--error ER_TOO_BIG_SELECT +EXPLAIN EXTENDED +SELECT COUNT(*) FROM t1 +WHERE a IN + ( SELECT b FROM t2 GROUP BY b ) + AND ( 6 ) IN + ( SELECT MIN( t2.b ) FROM t2 alias1, t2 ); + +DROP TABLE t1, t2; +set max_join_size= @tmp_906385; + + -- cgit v1.2.1 From a05a566cf0f14bb71740ea7f54f371b4df4f9604 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 20 Dec 2011 00:55:32 +0400 Subject: BUG#906357: Incorrect result with outer join and full text match - The problem was that const-table-reading code would try to evaluate MATCH() before init_ftfuncs() was called. - Fixed by making MATCH function "expensive" so that nobody tries to evaluate it at optimization phase. --- mysql-test/t/fulltext_left_join.test | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/fulltext_left_join.test b/mysql-test/t/fulltext_left_join.test index 8c13ae5cad9..3a81c1a5d1b 100644 --- a/mysql-test/t/fulltext_left_join.test +++ b/mysql-test/t/fulltext_left_join.test @@ -98,3 +98,16 @@ INSERT INTO t1 VALUES(1); INSERT INTO t2(b,c) VALUES(2,'castle'),(3,'castle'); SELECT * FROM t1 LEFT JOIN t2 ON a=b WHERE MATCH(c) AGAINST('+castle' IN BOOLEAN MODE); DROP TABLE t1, t2; + +--echo # +--echo # BUG#906357: Incorrect result with outer join and full text match +--echo # +CREATE TABLE t1(f1 VARCHAR(6) NOT NULL, FULLTEXT KEY(f1), UNIQUE(f1)); +INSERT INTO t1 VALUES ('test'); + +CREATE TABLE t2(f2 VARCHAR(6) NOT NULL, FULLTEXT KEY(f2), UNIQUE(f2)); +INSERT INTO t2 VALUES ('test'); +SELECT * FROM t2 LEFT OUTER JOIN t1 ON (MATCH(f1) against ("")); +SELECT * FROM t1 RIGHT OUTER JOIN t2 ON (MATCH(f1) against ("")); + +DROP table t1,t2; -- cgit v1.2.1 From 072073c09e0308ac58f1dbd2ee8f0fbc53e11467 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 19 Dec 2011 23:05:44 +0200 Subject: Backport of WL#5953 from MySQL 5.6 The patch differs from the original MySQL patch as follows: - All test case differences have been reviewed one by one, and care has been taken to restore the original plan so that each test case executes the code path it was designed for. - A bug was found and fixed in MariaDB 5.3 in Item_allany_subselect::cleanup(). - ORDER BY is not removed because we are unsure of all effects, and it would prevent enabling ORDER BY ... LIMIT subqueries. - ref_pointer_array.m_size is not adjusted because we don't do array bounds checking, and because it looks risky. Original comment by Jorgen Loland: ------------------------------------------------------------- WL#5953 - Optimize away useless subquery clauses For IN/ALL/ANY/SOME/EXISTS subqueries, the following clauses are meaningless: * ORDER BY (since we don't support LIMIT in these subqueries) * DISTINCT * GROUP BY if there is no HAVING clause and no aggregate functions This WL detects and optimizes away these useless parts of the query during JOIN::prepare() --- mysql-test/t/explain.test | 6 ++--- mysql-test/t/subselect.test | 9 +++---- mysql-test/t/subselect4.test | 40 +++++++++++++++---------------- mysql-test/t/subselect_mat_cost_bugs.test | 4 ++-- mysql-test/t/subselect_sj2_jcl6.test | 4 ++++ mysql-test/t/subselect_sj_mat.test | 18 +++++++------- 6 files changed, 43 insertions(+), 38 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 8700cd48c45..d07f36b37a4 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -243,12 +243,12 @@ FULLTEXT KEY(f1),UNIQUE(f1)); INSERT INTO t1 VALUES ('test'); EXPLAIN SELECT 1 FROM t1 -WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) +WHERE 1 > ALL((SELECT t1.f1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1)); PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 - WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a + WHERE 1 > ALL((SELECT t1.f1 FROM t1 RIGHT OUTER JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1))'; @@ -259,7 +259,7 @@ DEALLOCATE PREPARE stmt; PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 - WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a + WHERE 1 > ALL((SELECT t1.f1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1))'; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index d53ba706388..2540232bd3b 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3418,8 +3418,8 @@ drop table t1, t2; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a); -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); DROP TABLE t1; --echo # @@ -4442,7 +4442,8 @@ DROP table t1,t2; CREATE TABLE t1 (a int, b int) ; INSERT INTO t1 VALUES (0,0),(0,0); - +set @optimizer_switch_save=@@optimizer_switch; +set @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=on'; EXPLAIN SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) @@ -4451,7 +4452,7 @@ SELECT b FROM t1 SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; - +set @@optimizer_switch=@optimizer_switch_save; DROP TABLE t1; --echo # diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index aa3cb30c6f3..0e51373fb01 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -642,27 +642,27 @@ CREATE TABLE t2 (f11 varchar(1)) ; INSERT INTO t2 VALUES ('f'),('d'); SET @old_optimizer_switch = @@session.optimizer_switch; -SET SESSION optimizer_switch = 'materialization=on,in_to_exists=off,'; +SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off'; EXPLAIN SELECT * FROM t1 WHERE f3 = ( SELECT t1.f3 FROM t1 - WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 )); + WHERE ( t1.f10 ) IN ( SELECT max(f11) FROM t2 GROUP BY f11 )); SELECT * FROM t1 WHERE f3 = ( SELECT t1.f3 FROM t1 - WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 )); + WHERE ( t1.f10 ) IN ( SELECT max(f11) FROM t2 GROUP BY f11 )); EXPLAIN SELECT * FROM t1 WHERE f3 = ( SELECT f3 FROM t1 - WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); + WHERE ( f10, f10 ) IN ( SELECT max(f11), f11 FROM t2 GROUP BY f11 )); SELECT * FROM t1 WHERE f3 = ( SELECT f3 FROM t1 - WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); + WHERE ( f10, f10 ) IN ( SELECT max(f11), f11 FROM t2 GROUP BY f11 )); SET SESSION optimizer_switch = @old_optimizer_switch; drop table t1,t2; @@ -1084,7 +1084,7 @@ EXPLAIN SELECT * FROM t1 WHERE (SELECT f2 FROM t2 WHERE f4 <= ALL - (SELECT SQ1_t1.f4 + (SELECT max(SQ1_t1.f4) FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 GROUP BY SQ1_t1.f4)); @@ -1305,47 +1305,47 @@ INSERT INTO t2 VALUES set @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='outer_join_with_cache=off'; -set @@optimizer_switch = 'materialization=off'; +set @@optimizer_switch = 'in_to_exists=on,materialization=off,semijoin=off'; EXPLAIN SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); EXPLAIN -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); EXPLAIN -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); -SET @@optimizer_switch = 'materialization=on'; +set @@optimizer_switch = 'in_to_exists=off,materialization=on,semijoin=off'; EXPLAIN SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); EXPLAIN -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); EXPLAIN -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test index 463685bbdf0..8205e94b203 100644 --- a/mysql-test/t/subselect_mat_cost_bugs.test +++ b/mysql-test/t/subselect_mat_cost_bugs.test @@ -167,8 +167,8 @@ CREATE TABLE t3 ( f2 varchar(1)) ; EXPLAIN SELECT f2 FROM t3 WHERE ( SELECT MAX( pk ) FROM t1 WHERE EXISTS ( - SELECT DISTINCT f1 - FROM t2 + SELECT max(f1) + FROM t2 GROUP BY f1 ) ) IS NULL ; diff --git a/mysql-test/t/subselect_sj2_jcl6.test b/mysql-test/t/subselect_sj2_jcl6.test index 09212ba63d0..fbc474f7067 100644 --- a/mysql-test/t/subselect_sj2_jcl6.test +++ b/mysql-test/t/subselect_sj2_jcl6.test @@ -94,6 +94,10 @@ EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a); SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a); +EXPLAIN +SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a); +SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a); + set optimizer_switch=@tmp_optimizer_switch; set join_cache_level=default; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index b9e0beaa5e3..2a5b0f56877 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -88,8 +88,8 @@ select * from t1i where a1 in (select b1 from t2i where b1 > '0'); --replace_column 6 # 8 # 11 # explain extended -select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); -select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); +select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); +select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); --replace_column 7 # --replace_regex /it1.*/_it1_idx/ /test.t2i.*/_ref_/ /Using index$// /Using where$// @@ -99,8 +99,8 @@ select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); --replace_column 6 # 7 # 8 # 11 # explain extended -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); +select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' group by b1); +select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' group by b1); --replace_column 6 # 7 # 8 # 11 # explain extended @@ -835,14 +835,14 @@ create table t2 (b1 int); insert into t1 values (5); # Query with group by, executed via materialization -explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1); -select min(a1) from t1 where 7 in (select b1 from t2 group by b1); +explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); +select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); # Query with group by, executed via IN=>EXISTS set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch=@optimizer_switch_local_default; set @@optimizer_switch='materialization=off,in_to_exists=on'; -explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1); -select min(a1) from t1 where 7 in (select b1 from t2 group by b1); +explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); +select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); # Executed with materialization set @@optimizer_switch=@optimizer_switch_local_default; @@ -900,7 +900,7 @@ WHERE (t1i) IN ( SELECT t2i FROM t2 WHERE (t2i) IN ( - SELECT t3i + SELECT max(t3i) FROM t3 GROUP BY t3i ) -- cgit v1.2.1 From 2b1f0b875775b65496e959db7f54f6eb4220400e Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 24 Dec 2011 08:55:10 -0800 Subject: Back-ported the patch of the mysql-5.6 code line that fixed several defects in the greedy optimization: 1) The greedy optimizer calculated the 'compare-cost' (CPU-cost) for iterating over the partial plan result at each level in the query plan as 'record_count / (double) TIME_FOR_COMPARE' This cost was only used locally for 'best' calculation at each level, and *not* accumulated into the total cost for the query plan. This fix added the 'CPU-cost' of processing 'current_record_count' records at each level to 'current_read_time' *before* it is used as 'accumulated cost' argument to recursive best_extension_by_limited_search() calls. This ensured that the cost of a huge join-fanout early in the QEP was correctly reflected in the cost of the final QEP. To get identical cost for a 'best' optimized query and a straight_join with the same join order, the same change was also applied to optimize_straight_join() and get_partial_join_cost() 2) Furthermore to get equal cost for 'best' optimized query and a straight_join the new code substrcated the same '0.001' in optimize_straight_join() as it had been already done in best_extension_by_limited_search() 3) When best_extension_by_limited_search() aggregated the 'best' plan a plan was 'best' by the check : 'if ((search_depth == 1) || (current_read_time < join->best_read))' The term '(search_depth == 1' incorrectly caused a new best plan to be collected whenever the specified 'search_depth' was reached - even if this partial query plan was more expensive than what we had already found. --- mysql-test/t/derived_view.test | 1 + mysql-test/t/explain.test | 6 ++--- mysql-test/t/subselect.test | 12 +++++++++ mysql-test/t/subselect4.test | 3 +++ mysql-test/t/subselect_sj.test | 55 ++++++++++++++++++------------------------ 5 files changed, 42 insertions(+), 35 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 581d01058db..b674350af0a 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1036,6 +1036,7 @@ CREATE TABLE t3 ( b int NOT NULL, e varchar(1) NOT NULL, d varchar(1) NOT NULL, KEY (e,b) ); INSERT INTO t3 VALUES (4,'x','x'),(9,'w','w'),(4,'d','d'),(8,'e','e'); +INSERT INTO t3 VALUES (14,'a','a'),(19,'b','b'),(14,'c','c'),(18,'d','d'); CREATE TABLE t4 (i int NOT NULL, m varchar(1) NOT NULL) ; INSERT INTO t4 VALUES (8,'m'),(9,'d'),(2,'s'),(4,'r'),(8,'m'); diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index d07f36b37a4..3e585656ba9 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -180,8 +180,8 @@ create table t2 (dt datetime not null); insert into t1 values ('2001-01-01 1:1:1', '1:1:1'), ('2001-01-01 1:1:1', '1:1:1'); insert into t2 values ('2001-01-01 1:1:1'), ('2001-01-01 1:1:1'); -SET @save_optimizer_switch=@@optimizer_switch; -SET optimizer_switch='semijoin_with_cache=off'; +SET @save_join_cache_level=@@join_cache_level; +SET join_cache_level=0; flush tables; EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); flush tables; @@ -190,7 +190,7 @@ flush tables; EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); flush tables; SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); -SET optimizer_switch=@save_optimizer_switch; +SET join_cache_level=@save_join_cache_level; drop tables t1, t2; --echo # diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 2540232bd3b..9d87cbc486c 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3248,6 +3248,10 @@ DROP TABLE t1, t2, t3; # Bug#30788 Inconsistent retrieval of char/varchar # +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; +SET optimizer_switch='materialization=off'; + CREATE TABLE t1 (a CHAR(1), b VARCHAR(10)); INSERT INTO t1 VALUES ('a', 'aa'); INSERT INTO t1 VALUES ('a', 'aaa'); @@ -3269,6 +3273,7 @@ SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); DROP TABLE t1,t2; +SET optimizer_switch= @save_optimizer_switch; # # Bug#32400 Complex SELECT query returns correct result only on some occasions @@ -3418,8 +3423,11 @@ drop table t1, t2; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); +SET @save_join_cache_level=@@join_cache_level; +SET join_cache_level=0; EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a); EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); +SET join_cache_level=@save_join_cache_level; DROP TABLE t1; --echo # @@ -4430,8 +4438,12 @@ INSERT INTO t2 VALUES (11,1); INSERT INTO t2 VALUES (12,2); INSERT INTO t2 VALUES (15,4); + +SET @save_join_cache_level=@@join_cache_level; +SET join_cache_level=0; EXPLAIN SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1); SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1); +SET join_cache_level=@save_join_cache_level; DROP table t1,t2; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 0e51373fb01..b2e15c457d7 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -993,13 +993,16 @@ INSERT INTO t4 VALUES ('k'), ('d'); SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='outer_join_with_cache=off'; +SET optimizer_switch='semijoin_with_cache=off'; +SET optimizer_switch='materialization=off'; EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2); +SET optimizer_switch='materialization=on'; EXPLAIN SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2); SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2); diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 2b64de47439..aee61e9bca3 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -1777,52 +1777,43 @@ DROP TABLE t1, t2, t4, t5; --echo # --echo # BUG#861147: Assertion `fixed == 1' failed in Item_func_eq::val_int() with semijoin + materialization + max_join_size --echo # -CREATE TABLE t1 ( f2 int) ; -CREATE TABLE t2 ( f1 int, f3 int, f4 varchar(3), f5 varchar(35)) ; -INSERT INTO t2 VALUES (4057,9,'USA','Visalia'),(3993,11,'USA','Waco'), - (3948,14,'USA','Warren'),(3813,57,'USA','Washington'), - (4010,11,'USA','Waterbury'),(4017,11,'USA','West Covina'), - (4004,11,'USA','West Valley City'),(4033,10,'USA','Westminster'), - (3842,34,'USA','Wichita'),(4018,10,'USA','Wichita Falls'), - (3899,19,'USA','Winston-Salem'),(3914,17,'USA','Worcester'), - (3888,20,'USA','Yonkers'); +#CREATE TABLE t1 ( f2 int) ; +#CREATE TABLE t2 ( f1 int, f3 int, f4 varchar(3), f5 varchar(35)) ; +#INSERT INTO t2 VALUES (4057,9,'USA','Visalia'),(3993,11,'USA','Waco'), +# (3948,14,'USA','Warren'),(3813,57,'USA','Washington'), +# (4010,11,'USA','Waterbury'),(4017,11,'USA','West Covina'), +# (4004,11,'USA','West Valley City'),(4033,10,'USA','Westminster'), +# (3842,34,'USA','Wichita'),(4018,10,'USA','Wichita Falls'), +# (3899,19,'USA','Winston-Salem'),(3914,17,'USA','Worcester'), +# (3888,20,'USA','Yonkers'); -CREATE TABLE t3 ( f3 int, f4 varchar(3)) ; -INSERT INTO t3 VALUES (86,'USA'); +#CREATE TABLE t3 ( f3 int, f4 varchar(3)) ; +#INSERT INTO t3 VALUES (86,'USA'); -CREATE TABLE t4 ( f3 int, f4 varchar(3), f5 varchar(52)) ; -INSERT INTO t4 VALUES (0,'RUS','Belorussian'),(0,'USA','Portuguese'); +#CREATE TABLE t4 ( f3 int, f4 varchar(3), f5 varchar(52)) ; +#INSERT INTO t4 VALUES (0,'RUS','Belorussian'),(0,'USA','Portuguese'); -CREATE TABLE t5 ( f2 int) ; +#CREATE TABLE t5 ( f2 int) ; -CREATE TABLE t6 ( f4 varchar(3)); -INSERT INTO t6 VALUES ('RUS'),('USA'); +#CREATE TABLE t6 ( f4 varchar(3)); +#INSERT INTO t6 VALUES ('RUS'),('USA'); +create table t1 (a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); set @tmp_mjs_861147= @@max_join_size; SET max_join_size=10; set @tmp_os_861147= @@optimizer_switch; set @@optimizer_switch='semijoin=on,materialization=on'; --error ER_TOO_BIG_SELECT -SELECT * -FROM t1 -WHERE ( 1 , 3 ) IN ( - SELECT t2.f1 , MAX( t3.f3 ) - FROM t2 - JOIN t3 - WHERE t3.f4 IN ( - SELECT t4.f5 - FROM t4 - STRAIGHT_JOIN t5 - WHERE t4.f4 < t2.f5 - ) -) AND ( 'p' , 'k' ) IN ( - SELECT f4 , f4 FROM t6 -); +explain +select * from t1 where a in (select max(A.a + B.a + C.a) from t1 A, t1 B, t1 C); + set max_join_size= @tmp_mjs_861147; set optimizer_switch= @tmp_os_861147; -DROP TABLE t1,t2,t3,t4,t5,t6; +#DROP TABLE t1,t2,t3,t4,t5,t6; +drop table t1; --echo # --echo # BUG#877288: Wrong result with semijoin + materialization + multipart key -- cgit v1.2.1 From c583aaf56ba14484745e7a38bcc07b8652e516d4 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sun, 25 Dec 2011 18:03:03 -0800 Subject: Changed a test case from join_cache.test to make it platform independent. --- mysql-test/t/join_cache.test | 5 ----- 1 file changed, 5 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 0feb4e30af0..e39dcb72916 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -2581,11 +2581,6 @@ INSERT INTO t2 VALUES (1, 12, 102), (8, 81, 801), (7, 70, 700), (12, 120, 1200), (8, 82, 802), (1, 13, 103), (1, 14, 104), (3, 31, 301), (1, 15, 105), (8, 83, 803), (7, 71, 701); -INSERT INTO t2 VALUES - (108, 80, 800), (101, 10, 100), (101, 11, 101), (103, 30, 300), - (101, 12, 102), (108, 81, 801), (107, 70, 700), (1012, 120, 1200), - (108, 82, 802), (101, 13, 103), (101, 14, 104), (103, 31, 301), - (101, 15, 105), (108, 83, 803), (107, 71, 701); SET SESSION join_cache_level = 4; SET SESSION join_buffer_size = 192; -- cgit v1.2.1 From d9fcec5acdbe5aa06428dfbb9b828ebef845e7ca Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Thu, 29 Dec 2011 22:29:02 +0100 Subject: Make test results stable (they weren't, because filesort() used to read from a heap temptable, which uses pointers to records (that is, byte* pointers) as rowids. This meant that for rows with the same sort key value, the order was determined by memory layout. --- mysql-test/t/fulltext_order_by.test | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/fulltext_order_by.test b/mysql-test/t/fulltext_order_by.test index 814cd4a5954..6a5c7eb1615 100644 --- a/mysql-test/t/fulltext_order_by.test +++ b/mysql-test/t/fulltext_order_by.test @@ -28,8 +28,8 @@ SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a=7 and MATC SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY 1; # ORDER BY MATCH -SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel; -SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel; +SELECT IF(a=7,'steve-is-cool',IF(a=4,'cool', 'other')), FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel; +SELECT IF(a=7,'steve-is-cool',IF(a=4,'cool', 'other')), MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel; # # BUG#6635 - test_if_skip_sort_order() thought it can skip filesort -- cgit v1.2.1 From 0346e25f07266b6905f8bd5dbb664e32be3155fd Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Fri, 30 Dec 2011 11:34:29 +0100 Subject: Continuation of the efforts in previous cset. --- mysql-test/t/fulltext_order_by.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/fulltext_order_by.test b/mysql-test/t/fulltext_order_by.test index 6a5c7eb1615..0f800e5c077 100644 --- a/mysql-test/t/fulltext_order_by.test +++ b/mysql-test/t/fulltext_order_by.test @@ -29,7 +29,7 @@ SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE a=7 an # ORDER BY MATCH SELECT IF(a=7,'steve-is-cool',IF(a=4,'cool', 'other')), FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel; -SELECT IF(a=7,'steve-is-cool',IF(a=4,'cool', 'other')), MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel; +SELECT IF(a=7,'match',IF(a=4,'match', 'no-match')), MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel; # # BUG#6635 - test_if_skip_sort_order() thought it can skip filesort -- cgit v1.2.1 From 7714496dc1c72d01fd214cb7737ca4216a982e0f Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Fri, 30 Dec 2011 22:19:05 +0100 Subject: Make test results stable. --- mysql-test/t/union.test | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index d3bc94961ef..fefd67fd2f7 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -1137,6 +1137,7 @@ SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE); --echo # Should not crash +--sorted_result (SELECT * FROM t1) UNION (SELECT * FROM t1) ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE); -- cgit v1.2.1 From cd55894a52b5b2bf4a740eb0f39d91d7a82f673c Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 2 Jan 2012 20:06:36 -0800 Subject: Fixed LP bug #910083. The patch for bug 685411 erroneously removed a call of engine->set_thd() from Item_subselect::fix_fields(). --- mysql-test/t/trigger.test | 40 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 40 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 8d84330ec44..4b261c9f023 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -2434,3 +2434,43 @@ select * from t2; drop table t1,t2; --echo End of 5.2 tests. + +--echo # +--echo # BUG #910083: materialized subquery in a trigger +--echo # + +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='materialization=on'; + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int); + +CREATE TRIGGER tr AFTER UPDATE ON t1 FOR EACH ROW + UPDATE t2 SET b = (SELECT COUNT(a) FROM t1); + +INSERT INTO t1 + VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9); + +INSERT INTO t2 + VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0); + +send + UPDATE t1 SET a = 3; + +connect(con1,localhost,root,,); + SELECT COUNT(*) FROM t1; +disconnect con1; + +connection default; +reap; +SELECT * FROM t2; +UPDATE t1 SET a = 2; +SELECT * FROM t2; + +SET optimizer_switch=@save_optimizer_switch; + +DROP TRIGGER tr; +DROP TABLE t1, t2; + +--echo End of 5.3 tests. + -- cgit v1.2.1 From 0b590282fc116a1911eabe4cf1c7804a66f9bb48 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sun, 8 Jan 2012 14:43:14 +0400 Subject: BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... - Create/use do_copy_nullable_row_to_notnull() function for ref access, which is used when copying from not-NULL field in table that can be NULL-complemented to not-NULL field. --- mysql-test/t/subselect_sj.test | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index aee61e9bca3..f34cf5ba338 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -1997,6 +1997,25 @@ set optimizer_switch= @os_091399; DROP TABLE t1,t2; +--echo # +--echo # BUG#912510: Crash in do_copy_not_null with semijoin=ON, firstmatch=ON, aggregate ... +--echo # +CREATE TABLE t1 ( a VARCHAR(1) NOT NULL ); +INSERT INTO t1 VALUES ('k'),('l'); + +CREATE TABLE t2 ( b VARCHAR(1) NOT NULL, KEY(b) ); +INSERT INTO t2 VALUES ('k'),('l'); + +CREATE TABLE t3 ( c VARCHAR(1) NOT NULL, KEY(c) ); +INSERT INTO t3 VALUES ('m'),('n'); + +SELECT a, COUNT(*) FROM t1 + WHERE a IN ( + SELECT b FROM t2 force index(b), t3 force index(c) + WHERE c = b AND b = a + ); + +DROP TABLE t1, t2, t3; # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; -- cgit v1.2.1 From a148cf7fb092578d85f0dd2936e933b2acbb88e5 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Mon, 9 Jan 2012 13:49:47 +0200 Subject: Fixed that --sorted-result in mysql-test-run also works for exec mysql-test/r/information_schema_all_engines.result: Update result mysql-test/t/information_schema_all_engines.test: Added --sorted-results as tables in information_schema are not sorted. --- mysql-test/t/information_schema_all_engines.test | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/information_schema_all_engines.test b/mysql-test/t/information_schema_all_engines.test index b20ce60985c..9887845cb7e 100644 --- a/mysql-test/t/information_schema_all_engines.test +++ b/mysql-test/t/information_schema_all_engines.test @@ -9,6 +9,7 @@ use INFORMATION_SCHEMA; --replace_result Tables_in_INFORMATION_SCHEMA Tables_in_information_schema +--sorted_result show tables; # @@ -28,7 +29,7 @@ SELECT t.table_name, c1.column_name WHERE c2.table_schema = t.table_schema AND c2.table_name = t.table_name AND c2.column_name LIKE '%SCHEMA%' - ); + ) order by t.table_name; SELECT t.table_name, c1.column_name FROM information_schema.tables t INNER JOIN @@ -42,7 +43,7 @@ SELECT t.table_name, c1.column_name WHERE c2.table_schema = 'information_schema' AND c2.table_name = t.table_name AND c2.column_name LIKE '%SCHEMA%' - ); + ) order by t.table_name; # # Bug#24630 Subselect query crashes mysqld @@ -70,8 +71,11 @@ group by t.table_name order by num1, t.table_name; # # Bug #19147: mysqlshow INFORMATION_SCHEMA does not work # +--sorted_result --exec $MYSQL_SHOW information_schema +--sorted_result --exec $MYSQL_SHOW INFORMATION_SCHEMA +--sorted_result --exec $MYSQL_SHOW inf_rmation_schema # -- cgit v1.2.1 From cf31ccc33c2f5ac5e2ae92c672bd54cc7ce63107 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 10 Jan 2012 23:26:00 +0200 Subject: Fix for LP BUG#908269 Wrong result with subquery in select list, EXISTS, constant MyISAM/Aria table. Problem: When building the condition for JOIN::outer_ref_cond the optimizer forgot to take into account that this condition could depend on constant tables as well. --- mysql-test/t/subselect.test | 28 ++++++++++++++++++++++++++++ 1 file changed, 28 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 9d87cbc486c..b92fb6f961c 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -4990,5 +4990,33 @@ SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); set @@optimizer_switch=@old_optimizer_switch; drop table t1; +--echo # +--echo # LP BUG#908269 incorrect condition in case of subqueries depending +--echo # on constant tables +--echo # +CREATE TABLE t1 ( a INT ); +INSERT INTO t1 VALUES (1),(5); + +# t2 must be MyISAM or Aria and contain 1 row +CREATE TABLE t2 ( b INT ) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1); + +CREATE TABLE t3 ( c INT ); +INSERT INTO t3 VALUES (4),(5); + +SET optimizer_switch='subquery_cache=off'; + +SELECT ( SELECT b FROM t2 WHERE b = a OR EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; + +# This query just for example, it should return the same as above (1 and NULL) +SELECT ( SELECT b FROM t2 WHERE b = a OR b * 0) FROM t1; + +# example with "random" +SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1; + + +drop table t1,t2,t3; + + --echo # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; -- cgit v1.2.1