From aef1982be0a5ff5bd25fb7658d8792fd9ab40c3f Mon Sep 17 00:00:00 2001 From: Venkata Sidagam Date: Thu, 26 Jul 2012 15:09:22 +0530 Subject: Bug #12876932 - INCORRECT SELECT RESULT ON FEDERATED TABLE Problem description: Table 't' created with two colums having compound index on both the columns under innodb/myisam engine at remote machine. In the local machine same table is created undet the federated engine. A select having where clause with along 'AND' operation gives wrong results on local machine. Analysis: The given query at federated engine is wrongly transformed by federated::create_where_from_key() function and the same was sent to the remote machine. Hence the local machine is showing wrong results. Given query "select c1 from t where c1 <= 2 and c2 = 1;" Query transformed, after ha_federated::create_where_from_key() function is: SELECT `c1`, `c2` FROM `t` WHERE (`c1` IS NOT NULL ) AND ( (`c1` >= 2) AND (`c2` <= 1) ) and the same sent to real_query(). In the above the '<=' and '=' conditions were transformed to '>=' and '<=' respectively. ha_federated::create_where_from_key() function behaving as below: The key_range is having both the start_key and end_key. The start_key is used to get "(`c1` IS NOT NULL )" part of the where clause, this transformation is correct. The end_key is used to get "( (`c1` >= 2) AND (`c2` <= 1) )", which is wrong, here the given conditions('<=' and '=') are changed as wrong conditions('>=' and '<='). The end_key is having {key = 0x39fa6d0 "", length = 10, keypart_map = 3, flag = HA_READ_AFTER_KEY} The store_length is having value '5'. Based on store_length and length values the condition values is applied in HA_READ_AFTER_KEY switch case. The switch case 'HA_READ_AFTER_KEY' is applicable to only the last part of the end_key and for previous parts it is going to 'HA_READ_KEY_OR_NEXT' case, here the '>=' is getting added as a condition instead of '<='. Fix: Updated the 'if' condition in 'HA_READ_AFTER_KEY' case to affect for all parts of the end_key. i.e 'i > 0' will used for end_key, Hence added it in the if condition. mysql-test/suite/federated/federated.test: modified the federated.inc file location mysql-test/suite/federated/federated_archive.test: modified the federated.inc file location mysql-test/suite/federated/federated_bug_13118.test: modified the federated.inc file location mysql-test/suite/federated/federated_bug_25714.test: modified the federated.inc file location mysql-test/suite/federated/federated_bug_35333.test: modified the federated.inc file location mysql-test/suite/federated/federated_debug.test: modified the federated.inc file location mysql-test/suite/federated/federated_innodb.test: modified the federated.inc file location mysql-test/suite/federated/federated_server.test: modified the federated.inc file location mysql-test/suite/federated/federated_transactions.test: modified the federated.inc file location mysql-test/suite/federated/include/federated.inc: moved the file from federated suite to federated/include folder mysql-test/suite/federated/include/federated_cleanup.inc: moved the file from federated suite to federated/include folder mysql-test/suite/federated/include/have_federated_db.inc: moved the file from federated suite to federated/include folder storage/federated/ha_federated.cc: updated the 'if condition' in ha_federated::create_where_from_key() function. --- mysql-test/t/partition_federated.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/partition_federated.test b/mysql-test/t/partition_federated.test index 0fe692ecd01..3993aeafd31 100644 --- a/mysql-test/t/partition_federated.test +++ b/mysql-test/t/partition_federated.test @@ -3,7 +3,7 @@ # -- source include/have_partition.inc -- source include/not_embedded.inc --- source suite/federated/have_federated_db.inc +-- source suite/federated/include/have_federated_db.inc --disable_warnings drop table if exists t1; -- cgit v1.2.1 From 18087b049eeadfc07f49b65fc227a6ebd5d12e10 Mon Sep 17 00:00:00 2001 From: Venkata Sidagam Date: Sat, 11 Aug 2012 15:43:04 +0530 Subject: Bug #13115401: -SSL-KEY VALUE IS NOT VALIDATED AND IT ALLOWS INSECURE CONNECTIONS IF SPE Problem description: -ssl-key value is not validated, you can assign any bogus text to --ssl-key and it is not verified that it exists, and more importantly, it allows the client to connect to mysqld. Fix: Added proper validations checks for --ssl-key. Note: 1) Documentation changes require for 5.1, 5.5, 5.6 and trunk in the sections listed below and the details are : http://dev.mysql.com/doc/refman/5.6/en/ssl-options.html#option_general_ssl and REQUIRE SSL section of http://dev.mysql.com/doc/refman/5.6/en/grant.html 2) Client having with option '--ssl', should able to get ssl connection. This will be implemented as part of separate fix in 5.6 and trunk. --- mysql-test/t/openssl_1.test | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/openssl_1.test b/mysql-test/t/openssl_1.test index 8ca70258bc0..d5fbde1d9d4 100644 --- a/mysql-test/t/openssl_1.test +++ b/mysql-test/t/openssl_1.test @@ -73,22 +73,28 @@ drop table t1; # a different cacert # --exec echo "this query should not execute;" > $MYSQLTEST_VARDIR/tmp/test.sql +--replace_regex /2026 SSL connection error.*/2026 SSL connection error: xxxx/ --error 1 --exec $MYSQL_TEST --ssl-ca=$MYSQL_TEST_DIR/std_data/untrusted-cacert.pem --max-connect-retries=1 < $MYSQLTEST_VARDIR/tmp/test.sql 2>&1 +--echo # # Test that we can't open connection to server if we are using # a blank ca # +--replace_regex /2026 SSL connection error.*/2026 SSL connection error: xxxx/ --error 1 --exec $MYSQL_TEST --ssl-ca= --max-connect-retries=1 < $MYSQLTEST_VARDIR/tmp/test.sql 2>&1 +--echo # # Test that we can't open connection to server if we are using # a nonexistent ca file # +--replace_regex /2026 SSL connection error.*/2026 SSL connection error: xxxx/ --error 1 --exec $MYSQL_TEST --ssl-ca=nonexisting_file.pem --max-connect-retries=1 < $MYSQLTEST_VARDIR/tmp/test.sql 2>&1 +--echo # # Test that we can't open connection to server if we are using -- cgit v1.2.1 From 792efd59bce2413191d5620c6f0815e21f18b628 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 20 Sep 2012 12:48:59 +0300 Subject: MDEV-521 fix. After pullout item during single row subselect transformation it should be fixed properly. --- mysql-test/t/subselect.test | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 70682842ee1..bbdf1252334 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -5229,5 +5229,18 @@ set optimizer_switch=@mdev367_optimizer_switch; DROP TABLE t1; +--echo # +--echo # MDEV-521 single value subselect transformation problem +--echo # +CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('u1'),('u2'); + +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); +FLUSH TABLES; +SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) ); + +# Cleanup +DROP TABLE t1; + --echo # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; -- cgit v1.2.1 From 8c2bb705f11956cdc0acb67182466a903dcdd19b Mon Sep 17 00:00:00 2001 From: Alexey Botchkov Date: Thu, 27 Sep 2012 13:18:07 +0500 Subject: MDEV-495 backport --ignore-db-dir. The feature was backported from MySQL 5.6. Some code was added to make commands as SELECT * FROM ignored_db.t1; CALL ignored_db.proc(); USE ignored_db; to take that option into account. per-file comments: mysql-test/r/ignore_db_dirs_basic.result test result added. mysql-test/t/ignore_db_dirs_basic-master.opt options for the test, actually the set of --ignore-db-dir lines. mysql-test/t/ignore_db_dirs_basic.test test for the feature. Same test from 5.6 was taken as a basis, then tests for SELECT, CALL etc were added. per-file comments: sql/mysql_priv.h MDEV-495 backport --ignore-db-dir. interface for db_name_is_in_ignore_list() added. sql/mysqld.cc MDEV-495 backport --ignore-db-dir. --ignore-db-dir handling. sql/set_var.cc MDEV-495 backport --ignore-db-dir. the @@ignore_db_dirs variable added. sql/sql_show.cc MDEV-495 backport --ignore-db-dir. check if the directory is ignored. sql/sql_show.h MDEV-495 backport --ignore-db-dir. interface added for opt_ignored_db_dirs. sql/table.cc MDEV-495 backport --ignore-db-dir. check if the directory is ignored. --- mysql-test/t/ignore_db_dirs_basic-master.opt | 11 ++++++++ mysql-test/t/ignore_db_dirs_basic.test | 38 ++++++++++++++++++++++++++++ 2 files changed, 49 insertions(+) create mode 100644 mysql-test/t/ignore_db_dirs_basic-master.opt create mode 100644 mysql-test/t/ignore_db_dirs_basic.test (limited to 'mysql-test/t') diff --git a/mysql-test/t/ignore_db_dirs_basic-master.opt b/mysql-test/t/ignore_db_dirs_basic-master.opt new file mode 100644 index 00000000000..30ee4f67284 --- /dev/null +++ b/mysql-test/t/ignore_db_dirs_basic-master.opt @@ -0,0 +1,11 @@ +--ignore-db-dir=a +--ignore-db-dir=b +--ignore-db-dir=c +--ignore-db-dir= +--ignore-db-dir=d +--ignore-db-dir x +--ignore-db-dir= +--ignore-db-dir=e +--ignore-db-dir=lost+found +--ignore-db-dir=.mysqlgui +--ignore-db-dir=ignored_db diff --git a/mysql-test/t/ignore_db_dirs_basic.test b/mysql-test/t/ignore_db_dirs_basic.test new file mode 100644 index 00000000000..9544fc540f9 --- /dev/null +++ b/mysql-test/t/ignore_db_dirs_basic.test @@ -0,0 +1,38 @@ +select @@ignore_db_dirs; +let $MYSQLD_DATADIR= `select @@datadir`; + +mkdir $MYSQLD_DATADIR/.mysqlgui; +mkdir $MYSQLD_DATADIR/.otherdir; +mkdir $MYSQLD_DATADIR/lost+found; +mkdir $MYSQLD_DATADIR/ignored_db; +--echo # Check that SHOW DATABASES ignores all directories from +--echo # @@ignore_db_dirs and all directories with names starting +--echo # with '.' +SHOW DATABASES; +--error ER_WRONG_DB_NAME +USE ignored_db; +--error ER_WRONG_DB_NAME +SELECT * FROM ignored_db.t1; +--error ER_WRONG_DB_NAME +CALL ignored_db.p1(); +SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='ignored_db'; +--error ER_WRONG_DB_NAME +CREATE DATABASE ignored_db; +CREATE DATABASE `lost+found`; +USE `lost+found`; +CREATE TABLE t1(id INT); +INSERT INTO t1 VALUES (1), (2); +SELECT * FROM `lost+found`.t1; +SHOW DATABASES; +DROP DATABASE `lost+found`; +rmdir $MYSQLD_DATADIR/.mysqlgui; +rmdir $MYSQLD_DATADIR/.otherdir; +rmdir $MYSQLD_DATADIR/lost+found; +rmdir $MYSQLD_DATADIR/ignored_db; + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.ignore_db_dirs = 'aha'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@local.ignore_db_dirs = 'aha'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@ignore_db_dirs = 'aha'; -- cgit v1.2.1 From 66bd2b56fce6be2a083ef04b593fb0f8e644d5b4 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 29 Sep 2012 22:44:13 -0700 Subject: Fixed LP bug #1058071 (mdev-564). In some rare cases when the value of the system variable join_buffer_size was set to a number less than 256 the function JOIN_CACHE::set_constants determined the size of an offset in the join buffer equal to 1 though the minimal join buffer required more than 256 bytes. This could cause a crash of the server when records from the join buffer were read. --- mysql-test/t/join_cache.test | 49 ++++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/select.test | 2 +- 2 files changed, 50 insertions(+), 1 deletion(-) (limited to 'mysql-test/t') diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 0962f1bf766..eabab4891ce 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3587,5 +3587,54 @@ set optimizer_switch=@tmp_optimizer_switch; DROP TABLE t1,t2,t3; +--echo # +--echo # Bug #1058071: LEFT JOIN using blobs +--echo # (mdev-564) when join buffer size is small +--echo # + +CREATE TABLE t1 ( + col269 decimal(31,10) unsigned DEFAULT NULL, + col280 multipoint DEFAULT NULL, + col281 tinyint(1) DEFAULT NULL, + col282 time NOT NULL, + col284 datetime DEFAULT NULL, + col286 date DEFAULT NULL, + col287 datetime DEFAULT NULL, + col288 decimal(30,29) DEFAULT NULL, + col291 time DEFAULT NULL, + col292 time DEFAULT NULL +) ENGINE=Aria; + +INSERT INTO t1 VALUES +(0.0,PointFromText('POINT(9 0)'),0,'11:24:05','2013-04-14 21:30:28',NULL,'2011-12-20 06:00:34',9.9,'13:04:39',NULL), +(0.0,NULL,127,'05:43:12','2012-09-05 06:15:27','2027-01-01','2011-10-29 10:48:29',0.0,'06:24:05','11:33:37'), +(0.0,NULL,127,'12:54:41','2013-01-12 11:32:58','2011-11-03','2013-01-03 02:00:34',00,'11:54:15','20:19:15'), +(0.0,PointFromText('POINT(9 0)'),0,'19:48:07','2012-07-16 15:45:25','2012-03-25','2013-09-07 17:21:52',0.5,'17:36:54','21:24:19'), +(0.0,PointFromText('POINT(9 0)'),0,'03:43:48','2012-09-28 00:00:00','2012-06-26','2011-11-16 05:01:09',00,'01:25:42','19:30:06'), +(0.0,LineStringFromText('LINESTRING(0 0,9 9,0 0,9 0,0 0)'),127,'11:33:21','2012-03-31 10:29:22','2012-10-10','2012-04-21 19:21:06',NULL,'05:13:22','09:48:34'), +(NULL,PointFromText('POINT(9 0)'),127,'00:00:00','0000-00-00','2012-04-04 21:26:12','2013-03-04',0.0,'12:54:30',NULL), +(NULL,PointFromText('POINT(9 0)'),1,'00:00:00','2013-05-01 22:37:49','2013-06-26','2012-09-22 17:31:03',0.0,'08:09:57','11:15:36'); + +CREATE TABLE t2 (b int) ENGINE=Aria; +INSERT INTO t2 VALUES (NULL); +CREATE TABLE t3 (c int) ENGINE=Aria; +INSERT INTO t3 VALUES (NULL); + +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch = 'outer_join_with_cache=on,join_cache_incremental=on'; +set join_buffer_size=128; + +EXPLAIN +SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1 + GROUP BY elt(t1.col282,1,t1.col280); + +SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1 + GROUP BY elt(t1.col282,1,t1.col280); + +set join_buffer_size=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; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index b937462e3e9..890da70caad 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -4126,7 +4126,7 @@ INSERT INTO t5 VALUES (20),(5); CREATE TABLE t6(f1 int); INSERT INTO t6 VALUES (9),(7); -SET SESSION join_buffer_size = 2048; +SET SESSION join_buffer_size = 2176; EXPLAIN SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6; -- cgit v1.2.1 From c56fd181bff92dd377b4664583d4fe04ead16ac7 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 1 Oct 2012 19:04:17 -0700 Subject: Added the reported test case for LP bug #823237 (a duplicate of bug #823189). --- mysql-test/t/view.test | 40 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 40 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index bd3b485034d..3b590140d56 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -4344,6 +4344,46 @@ DROP VIEW v1; DROP TABLE t1,t2,t3,t4; +--echo # +--echo # LP bug #823237: dependent subquery with LEFT JOIN +--echo # referencing view in WHERE +--echo # (duplicate of LP bug #823189) +--echo # + +CREATE TABLE t1 (a int); + +CREATE TABLE t2 ( b int, d int, e int); +INSERT INTO t2 VALUES (7,8,0); + +CREATE TABLE t3 ( c int); +INSERT INTO t3 VALUES (0); + +CREATE TABLE t4 (a int, b int, c int); +INSERT INTO t4 VALUES (93,1,0), (95,NULL,0); + +CREATE VIEW v4 AS SELECT * FROM t4; + +EXPLAIN EXTENDED +SELECT * FROM t3 , t4 + WHERE t4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d ) + WHERE t2.b > t4.b); +SELECT * FROM t3 , t4 + WHERE t4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d ) + WHERE t2.b > t4.b); + + +EXPLAIN EXTENDED +SELECT * FROM t3, v4 + WHERE v4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d ) + WHERE t2.b > v4.b); + +SELECT * FROM t3, v4 + WHERE v4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d ) + WHERE t2.b > v4.b); + +DROP VIEW v4; +DROP TABLE t1,t2,t3,t4; + --echo # --echo # BUG#833600: Wrong result with view + outer join + uncorrelated subquery (non-semijoin) --echo # -- cgit v1.2.1 From b0d11675fb46f5db458896a9a17f03bd53d98e88 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 5 Oct 2012 12:26:55 +0300 Subject: Fix of MDEV-589. The problem was in incorrect detection of merged views in tem_direct_view_ref::used_tables() . --- mysql-test/t/view.test | 23 +++++++++++++++++++++++ 1 file changed, 23 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 3b590140d56..337af624813 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -4503,6 +4503,29 @@ create algorithm=MERGE view v2 as select 2 as id, id is null as bbb, id as iddqd select t1.*, v2.* from t1 left join v2 on t1.id = v2.id; drop view v2; drop table t1,t2; + +--echo # +--echo # MDEV-589 (LP BUG#1007647) : +--echo # Assertion `vcol_table == 0 || vcol_table == table' failed in +--echo # fill_record(THD*, List&, List&, bool) +--echo # +CREATE TABLE t1 (f1 INT, f2 INT); +CREATE TABLE t2 (f1 INT, f2 INT); +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a1.f1, a2.f2 FROM t1 AS a1, t1 AS a2; +CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM v1; +CREATE ALGORITHM=MERGE VIEW v3 AS SELECT a1.f1, a2.f2 FROM t1 AS a1, t2 AS a2; +CREATE ALGORITHM=MERGE VIEW v4 AS SELECT * FROM v3; +--error ER_VIEW_MULTIUPDATE +INSERT INTO v3 (f1, f2) VALUES (1, 2); +--error ER_VIEW_MULTIUPDATE +INSERT INTO v1 (f1, f2) VALUES (1, 2); +--error ER_VIEW_MULTIUPDATE +INSERT INTO v4 (f1, f2) VALUES (1, 2); +--error ER_VIEW_MULTIUPDATE +INSERT INTO v2 (f1, f2) VALUES (1, 2); +drop view v4,v3,v2,v1; +drop table t1,t2; + --echo # ----------------------------------------------------------------- --echo # -- End of 5.3 tests. --echo # ----------------------------------------------------------------- -- cgit v1.2.1 From d2d6c8b8e8c973f6201b3472b7f451a9e16d0d44 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 10 Oct 2012 09:21:22 +0400 Subject: Backport of: olav.sandstaa@oracle.com-20120516074923-vd0dhp183vqcp2ql .. into MariaDB 5.3 Fix for Bug#12667154 SAME QUERY EXEC AS WHERE SUBQ GIVES DIFFERENT RESULTS ON IN() & NOT IN() COMP #3 This bug causes a wrong result in mysql-trunk when ICP is used and bad performance in mysql-5.5 and mysql-trunk. Using the query from bug report to explain what happens and causes the wrong result from the query when ICP is enabled: 1. The t3 table contains four records. The outer query will read these and for each of these it will execute the subquery. 2. Before the first execution of the subquery it will be optimized. In this case the important is what happens to the first table t1: -make_join_select() will call the range optimizer which decides that t1 should be accessed using a range scan on the k1 index It creates a QUICK_RANGE_SELECT object for this. -As the last part of optimization the ICP code pushes the condition down to the storage engine for table t1 on the k1 index. This produces the following information in the explain for this table: 2 DEPENDENT SUBQUERY t1 range k1 k1 5 NULL 3 Using index condition; Using filesort Note the use of filesort. 3. The first execution of the subquery does (among other things) due to the need for sorting: a. Call create_sort_index() which again will call find_all_keys(): b. find_all_keys() will read the required keys for all qualifying rows from the storage engine. To do this it checks if it has a quick-select for the table. It will use the quick-select for reading records. In this case it will read four records from the storage engine (based on the range criteria). The storage engine will evaluate the pushed index condition for each record. c. At the end of create_sort_index() there is code that cleans up a lot of stuff on the join tab. One of the things that is cleaned is the select object. The result of this is that the quick-select object created in make_join_select is deleted. 4. The second execution of the subquery does the same as the first but the result is different: a. Call create_sort_index() which again will call find_all_keys() (same as for the first execution) b. find_all_keys() will read the keys from the storage engine. To do this it checks if it has a quick-select for the table. Now there is NO quick-select object(!) (since it was deleted in step 3c). So find_all_keys defaults to read the table using a table scan instead. So instead of reading the four relevant records in the range it reads the entire table (6 records). It then evaluates the table's condition (and here it goes wrong). Since the entire condition has been pushed down to the storage engine using ICP all 6 records qualify. (Note that the storage engine will not evaluate the pushed index condition in this case since it was pushed for the k1 index and now we do a table scan without any index being used). The result is that here we return six qualifying key values instead of four due to not evaluating the table's condition. c. As above. 5. The two last execution of the subquery will also produce wrong results for the same reason. Summary: The problem occurs due to all but the first executions of the subquery is done as a table scan without evaluating the table's condition (which is pushed to the storage engine on a different index). This is caused by the create_sort_index() function deleting the quick-select object that should have been used for executing the subquery as a range scan. Note that this bug in addition to causing wrong results also can result in bad performance due to executing the subquery using a table scan instead of a range scan. This is an issue in MySQL 5.5. The fix for this problem is to avoid that the Quick-select-object that the optimizer created is deleted when create_sort_index() is doing clean-up of the join-tab. This will ensure that the quick-select object and the corresponding pushed index condition will be available and used by all following executions of the subquery. --- mysql-test/t/subselect2.test | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect2.test b/mysql-test/t/subselect2.test index 8d2939bdb53..75cf842fbdb 100644 --- a/mysql-test/t/subselect2.test +++ b/mysql-test/t/subselect2.test @@ -203,5 +203,24 @@ SET optimizer_switch=@tmp_optimizer_switch; DROP VIEW v1; DROP TABLE t1,t2,t3; +--echo # +--echo # MDEV-567: Wrong result from a query with correlated subquery if ICP is allowed +--echo # +CREATE TABLE t1 (a int, b int, INDEX idx(a)); +INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,3), (2,1); + +CREATE TABLE t2 (a int, b int, INDEX idx(a)); +INSERT INTO t2 VALUES (2,1), (6,4), (7,6), (9,4); + +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (1,0), (1,1), (1,3); + +SELECT * FROM t3 + WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2 + WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9 + AND t3.b = t1.b + GROUP BY t1.b); +DROP TABLE t1, t2, t3; + set optimizer_switch=@subselect2_test_tmp; -- cgit v1.2.1 From 362c2bca3e170031d33622f27d978c9570d0a9f5 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 10 Oct 2012 22:42:50 +0300 Subject: Fix of MDEV-3799. Find left table in right join (which turned to left join by reordering tables in join list but phisical order of tables of SELECT left as it was). --- mysql-test/t/view.test | 23 +++++++++++++++++++++++ 1 file changed, 23 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 337af624813..3bed7d5dd93 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -4526,6 +4526,29 @@ INSERT INTO v2 (f1, f2) VALUES (1, 2); drop view v4,v3,v2,v1; drop table t1,t2; +--echo # +--echo # MDEV-3799 fix of above bugfix (MDEV-589) +--echo # Wrong result (NULLs instead of real values) with RIGHT JOIN +--echo # in a FROM subquery and derived_merge=on +--echo # + +CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); + +CREATE TABLE t2 (f2 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (7),(8); + +SELECT * FROM ( + SELECT * FROM t1 RIGHT JOIN t2 ON f1 = f2 +) AS alias; + +SELECT * FROM ( + SELECT * FROM t2 LEFT JOIN t1 ON f1 = f2 +) AS alias; + +drop tables t1,t2; + + --echo # ----------------------------------------------------------------- --echo # -- End of 5.3 tests. --echo # ----------------------------------------------------------------- -- cgit v1.2.1 From 8215ce4695e743d313e92f4d30f412f79958439c Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 11 Oct 2012 12:09:21 +0300 Subject: MDEV-3804: MySQL fix for bug#11765413 removed (we have better and more general fix for the problem). Test suite added. --- mysql-test/t/ps_1general.test | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test index b9e84d8d7df..812b1b5ff94 100644 --- a/mysql-test/t/ps_1general.test +++ b/mysql-test/t/ps_1general.test @@ -827,6 +827,16 @@ execute stmt1 ; drop table t1, t5, t9; +--echo # +--echo # testcase for bug#11765413 - Crash with dependent subquery and +--echo # prepared statement +create table t1 (c1 int); +insert into t1 values (1); +prepare stmt1 from "select 1 from t1 where 1=(select 1 from t1 having c1)"; +execute stmt1; +drop prepare stmt1; +drop table t1; + ##### RULES OF THUMB TO PRESERVE THE SYSTEMATICS OF THE PS TEST CASES ##### # # 0. You don't have the time to -- cgit v1.2.1 From e47cdfdfb6b2b6512b13fa097ee092638e05266a Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 12 Oct 2012 16:44:54 +0300 Subject: MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields Fix by Sergey Petrunia. This patch only prevents the evaluation of expensive subqueries during optimization. The crash reported in this bug has been fixed by some other patch. The fix is to call value->is_null() only when !value->is_expensive(), because is_null() may trigger evaluation of the Item, which in turn triggers subquery evaluation if the Item is a subquery. --- mysql-test/t/subselect.test | 23 +++++++++++++++++++++++ 1 file changed, 23 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 9f35ecc43f1..11172b8bf52 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -5756,6 +5756,29 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP B DROP TABLE t1,t2; +--echo # +--echo # MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields +--echo # + +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (8),(0); + +CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,'j'),(6,'v'); + +CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('b'),('c'); + +EXPLAIN +SELECT * FROM t1 +WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; + +SELECT * FROM t1 +WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10; + +drop table t1, t2, t3; + + --echo # --echo # MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery --echo # -- cgit v1.2.1 From abefaab57b4b884b74ff9bd3c63f86c018d0e5de Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Tue, 16 Oct 2012 10:35:05 +0200 Subject: minor test cleanup. one server restart less in mtr --- mysql-test/t/order_fill_sortbuf-master.opt | 1 - mysql-test/t/order_fill_sortbuf.test | 2 ++ 2 files changed, 2 insertions(+), 1 deletion(-) delete mode 100644 mysql-test/t/order_fill_sortbuf-master.opt (limited to 'mysql-test/t') diff --git a/mysql-test/t/order_fill_sortbuf-master.opt b/mysql-test/t/order_fill_sortbuf-master.opt deleted file mode 100644 index 7b6ade99226..00000000000 --- a/mysql-test/t/order_fill_sortbuf-master.opt +++ /dev/null @@ -1 +0,0 @@ ---sort_buffer=32804 diff --git a/mysql-test/t/order_fill_sortbuf.test b/mysql-test/t/order_fill_sortbuf.test index 7a8779b6e55..33c09e34b91 100644 --- a/mysql-test/t/order_fill_sortbuf.test +++ b/mysql-test/t/order_fill_sortbuf.test @@ -7,6 +7,8 @@ drop table if exists t1,t2; --enable_warnings +set @@sort_buffer_size=32804; + CREATE TABLE `t1` ( `id` int(11) NOT NULL default '0', `id2` int(11) NOT NULL default '0', -- cgit v1.2.1