From bad3e4179c5471728fb0dc3d72b81db02129128f Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 19 Dec 2011 14:55:30 -0800 Subject: Fixed LP bug #906322. If the sorted table belongs to a dependent subquery then the function create_sort_index() should not clear TABLE:: select and TABLE::select for this table after the sort of the table has been performed, because these members are needed for the second execution of the subquery. --- mysql-test/suite/innodb/r/innodb_mysql.result | 6 +++--- mysql-test/suite/innodb_plugin/r/innodb_mysql.result | 6 +++--- 2 files changed, 6 insertions(+), 6 deletions(-) (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 42aca8b6464..666d6b7591f 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -1739,7 +1739,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL c3,c2 c3 5 5 Using filesort +2 DERIVED t1 ALL c3,c2 c3 5 5 Using where; Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3)) ENGINE=InnoDB; @@ -1753,7 +1753,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL c3,c2 c3 9 5 Using filesort +2 DERIVED t1 ALL c3,c2 c3 9 5 Using where; Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), KEY (c3), KEY (c2, c3)) @@ -1768,7 +1768,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL c3,c2 c3 7 5 Using filesort +2 DERIVED t1 ALL c3,c2 c3 7 5 Using where; Using filesort DROP TABLE t1; End of 5.1 tests drop table if exists t1, t2, t3; diff --git a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result index 86d83f82b76..987a04eb59d 100644 --- a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result +++ b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result @@ -1739,7 +1739,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL c3,c2 c3 5 5 Using filesort +2 DERIVED t1 ALL c3,c2 c3 5 5 Using where; Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3)) ENGINE=InnoDB; @@ -1753,7 +1753,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL c3,c2 c3 9 5 Using filesort +2 DERIVED t1 ALL c3,c2 c3 9 5 Using where; Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), KEY (c3), KEY (c2, c3)) @@ -1768,7 +1768,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL c3,c2 c3 7 5 Using filesort +2 DERIVED t1 ALL c3,c2 c3 7 5 Using where; Using filesort DROP TABLE t1; End of 5.1 tests drop table if exists t1, t2, t3; -- cgit v1.2.1 From c9259f166bfcc757338c957f806e3d18637da17a Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 27 Dec 2011 13:19:13 -0800 Subject: Fixed LP bug #904345. The MIN/MAX optimizer code from the function opt_sum_query erroneously did not take into account conjunctive conditions that did not depend on any table, yet were not identified as constant items. These could be items containing rand() or PS/SP parameters. These items are supposed to be evaluated at the execution phase. That's why if such conditions can be extracted from the WHERE condition the MIN/MAX optimization is not applied as currently it is always done at the optimization phase. (In 5.3 expensive subqueries are also evaluated only at the execution phase. So, if a constant condition with such subquery can be extracted from the WHERE clause the MIN/MAX optimization should not be applied in 5.3.) IF an IN/ALL/SOME predicate with a constant left part is transformed into an EXISTS subquery the resulting subquery should not be considered uncacheable if the right part of the predicate is not uncacheable. Backported the function dbug_print_item() from 5.3. The function is used only for debugging. --- mysql-test/suite/pbxt/r/ps_11bugs.result | 6 +++--- mysql-test/suite/pbxt/r/select.result | 8 ++++---- mysql-test/suite/pbxt/r/subselect.result | 4 ++-- 3 files changed, 9 insertions(+), 9 deletions(-) (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/pbxt/r/ps_11bugs.result b/mysql-test/suite/pbxt/r/ps_11bugs.result index fad35b97b24..0be781bca27 100644 --- a/mysql-test/suite/pbxt/r/ps_11bugs.result +++ b/mysql-test/suite/pbxt/r/ps_11bugs.result @@ -120,9 +120,9 @@ create table t1 (a int primary key); insert into t1 values (1); explain select * from t1 where 3 in (select (1+1) union select 1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING -3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +3 UNION NULL NULL NULL NULL NULL NULL NULL Impossible HAVING NULL UNION RESULT ALL NULL NULL NULL NULL NULL select * from t1 where 3 in (select (1+1) union select 1); a diff --git a/mysql-test/suite/pbxt/r/select.result b/mysql-test/suite/pbxt/r/select.result index c06dd06ea3e..b98f980f821 100644 --- a/mysql-test/suite/pbxt/r/select.result +++ b/mysql-test/suite/pbxt/r/select.result @@ -2786,10 +2786,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +1 SIMPLE t1 index key1 key1 5 NULL 4 Using where; Using index explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +1 SIMPLE t1 index key1 key1 5 NULL 4 Using where; Using index select max(key1) from t1 where key1 <= 0.6158; max(key1) 0.615800023078918 @@ -2808,10 +2808,10 @@ max(key1) min(key2) 0.615800023078918 1.37619996070862 select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; max(key1) -0.615800023078918 +0.384499996900558 select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; min(key1) -0.376199990510941 +0.384499996900558 DROP TABLE t1,t2; CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL); INSERT INTO t1 VALUES (10); diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result index 0cdec48e192..63b289d259e 100644 --- a/mysql-test/suite/pbxt/r/subselect.result +++ b/mysql-test/suite/pbxt/r/subselect.result @@ -1177,7 +1177,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 select (0,(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); @@ -1187,7 +1187,7 @@ SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 select (0,(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; -- cgit v1.2.1 From 40c42468bcbe215a971f93edb9af27545156b8a7 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 29 Dec 2011 15:09:20 -0800 Subject: Fixed LP bug #806057. A table expression with a natural join or a USING clause is transformed into an equivalent expression with equi-join ON conditions. If a reference to a virtual column happened to occur only in these generated equi-join conditions then it was not erroneously marked in the TABLE::vcol_set bitmap. This could lead to wrong results for queries containing natural join expressions or USING clauses. --- mysql-test/suite/vcol/r/vcol_select_myisam.result | 30 +++++++++++++++++++++++ mysql-test/suite/vcol/t/vcol_select_myisam.test | 20 +++++++++++++++ 2 files changed, 50 insertions(+) (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/vcol/r/vcol_select_myisam.result b/mysql-test/suite/vcol/r/vcol_select_myisam.result index 45e4defd315..04ae7e0f7cd 100644 --- a/mysql-test/suite/vcol/r/vcol_select_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_select_myisam.result @@ -262,3 +262,33 @@ NULL explain select sum(c) from t1 group by b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +# +# Bug #806057: join with USING over a virtual column +# +CREATE TABLE t1 (b int); +INSERT INTO t1 VALUES (NULL),( 78), (185), (0), (154); +CREATE TABLE t2 (a int, b int AS (a) VIRTUAL); +INSERT INTO t2 VALUES (187,187), (9,9), (187,187); +Warnings: +Warning 1647 The value specified for computed column 'b' in table 't2' ignored +Warning 1647 The value specified for computed column 'b' in table 't2' ignored +Warning 1647 The value specified for computed column 'b' in table 't2' ignored +EXPLAIN EXTENDED +SELECT * FROM t1 JOIN t2 USING (b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`b` = `test`.`t2`.`b`) +SELECT * FROM t1 JOIN t2 USING (b); +b a +EXPLAIN EXTENDED +SELECT * FROM t1 NATURAL JOIN t2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`b` = `test`.`t2`.`b`) +SELECT * FROM t1 NATURAL JOIN t2; +b a +DROP TABLE t1,t2; diff --git a/mysql-test/suite/vcol/t/vcol_select_myisam.test b/mysql-test/suite/vcol/t/vcol_select_myisam.test index 855e02ac113..c14faba576d 100644 --- a/mysql-test/suite/vcol/t/vcol_select_myisam.test +++ b/mysql-test/suite/vcol/t/vcol_select_myisam.test @@ -48,3 +48,23 @@ eval SET @@session.storage_engine = 'MyISAM'; #------------------------------------------------------------------------------# # Cleanup --source suite/vcol/inc/vcol_cleanup.inc + +--echo # +--echo # Bug #806057: join with USING over a virtual column +--echo # + +CREATE TABLE t1 (b int); +INSERT INTO t1 VALUES (NULL),( 78), (185), (0), (154); + +CREATE TABLE t2 (a int, b int AS (a) VIRTUAL); +INSERT INTO t2 VALUES (187,187), (9,9), (187,187); + +EXPLAIN EXTENDED +SELECT * FROM t1 JOIN t2 USING (b); +SELECT * FROM t1 JOIN t2 USING (b); + +EXPLAIN EXTENDED +SELECT * FROM t1 NATURAL JOIN t2; +SELECT * FROM t1 NATURAL JOIN t2; + +DROP TABLE t1,t2; -- cgit v1.2.1 From 629cdab80827e7ad6bb23f7c8dc20724eb87f4d9 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Sun, 8 Jan 2012 20:29:05 +0200 Subject: Fixed compiler and test failures found by buildbot configure.in: Added testing of STRNDUP (not found on solaris) mysql-test/include/wait_until_connected_again.inc: Also test for error 2005 (can happen on windows) mysql-test/include/wait_until_disconnected.inc: Also test for error 2005 (can happen on windows) mysql-test/suite/innodb_plugin/r/innodb_bug30423.result: Number of rows is not stable (found difference on Solaris) mysql-test/suite/innodb_plugin/t/innodb_bug30423.test: Number of rows is not stable (found difference on Solaris) plugin/auth_pam/auth_pam.c: Use internal strndup if it doesn't exist on system (solaris) Changed code so that it should also compile on solaris. --- mysql-test/suite/innodb_plugin/r/innodb_bug30423.result | 6 +++--- mysql-test/suite/innodb_plugin/t/innodb_bug30423.test | 1 + 2 files changed, 4 insertions(+), 3 deletions(-) (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/innodb_plugin/r/innodb_bug30423.result b/mysql-test/suite/innodb_plugin/r/innodb_bug30423.result index a19809366ae..6071587e888 100644 --- a/mysql-test/suite/innodb_plugin/r/innodb_bug30423.result +++ b/mysql-test/suite/innodb_plugin/r/innodb_bug30423.result @@ -48,9 +48,9 @@ ON orgs.org_id=sa_opportunities.org_id LEFT JOIN bug30243_2 contacts ON orgs.org_id=contacts.org_id ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE orgs index NULL org_id 4 NULL 128 Using index -1 SIMPLE sa_opportunities ref org_id org_id 5 test.orgs.org_id 1 Using index -1 SIMPLE contacts ref contacts$org_id contacts$org_id 5 test.orgs.org_id 1 Using index +1 SIMPLE orgs index NULL org_id 4 NULL # Using index +1 SIMPLE sa_opportunities ref org_id org_id 5 test.orgs.org_id # Using index +1 SIMPLE contacts ref contacts$org_id contacts$org_id 5 test.orgs.org_id # Using index select @@innodb_stats_method; @@innodb_stats_method nulls_ignored diff --git a/mysql-test/suite/innodb_plugin/t/innodb_bug30423.test b/mysql-test/suite/innodb_plugin/t/innodb_bug30423.test index 458c2967e19..da490589400 100644 --- a/mysql-test/suite/innodb_plugin/t/innodb_bug30423.test +++ b/mysql-test/suite/innodb_plugin/t/innodb_bug30423.test @@ -140,6 +140,7 @@ analyze table bug30243_3; # Following query plan shows that we get the correct rows per # unique value (should be approximately 1 row per value) +--replace_column 9 # explain SELECT COUNT(*), 0 FROM bug30243_1 orgs LEFT JOIN bug30243_3 sa_opportunities -- cgit v1.2.1 From 4de7978a3f3e716a6b1b280ce1f30dd501e31545 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 13 Jan 2012 19:00:50 -0800 Subject: Back-ported the fix and the test case for bug #50257 from mariadb-5.3 code line. Adjusted results for a few test cases. --- mysql-test/suite/innodb/r/innodb_mysql.result | 6 +++--- mysql-test/suite/innodb_plugin/r/innodb_mysql.result | 6 +++--- mysql-test/suite/pbxt/r/subselect.result | 4 ++-- 3 files changed, 8 insertions(+), 8 deletions(-) (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 666d6b7591f..e022bd961d6 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -1739,7 +1739,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL c3,c2 c3 5 5 Using where; Using filesort +2 DERIVED t1 ALL c3,c2 c3 5 const 5 Using where; Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3)) ENGINE=InnoDB; @@ -1753,7 +1753,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL c3,c2 c3 9 5 Using where; Using filesort +2 DERIVED t1 ALL c3,c2 c3 9 const 5 Using where; Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), KEY (c3), KEY (c2, c3)) @@ -1768,7 +1768,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL c3,c2 c3 7 5 Using where; Using filesort +2 DERIVED t1 ALL c3,c2 c3 7 const 5 Using where; Using filesort DROP TABLE t1; End of 5.1 tests drop table if exists t1, t2, t3; diff --git a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result index 987a04eb59d..49e8b44b018 100644 --- a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result +++ b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result @@ -1739,7 +1739,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL c3,c2 c3 5 5 Using where; Using filesort +2 DERIVED t1 ALL c3,c2 c3 5 const 5 Using where; Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3)) ENGINE=InnoDB; @@ -1753,7 +1753,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL c3,c2 c3 9 5 Using where; Using filesort +2 DERIVED t1 ALL c3,c2 c3 9 const 5 Using where; Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), KEY (c3), KEY (c2, c3)) @@ -1768,7 +1768,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL c3,c2 c3 7 5 Using where; Using filesort +2 DERIVED t1 ALL c3,c2 c3 7 const 5 Using where; Using filesort DROP TABLE t1; End of 5.1 tests drop table if exists t1, t2, t3; diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result index 63b289d259e..0b5c98324f4 100644 --- a/mysql-test/suite/pbxt/r/subselect.result +++ b/mysql-test/suite/pbxt/r/subselect.result @@ -363,9 +363,9 @@ INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -4 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index +4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index 2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 -3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index +3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM -- cgit v1.2.1 From b8b7b4eb6bef26d05257272be02674ae5514ded9 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 30 Dec 2011 13:57:03 +0100 Subject: plugin renamed socket_peercred -> unix_socket. test added. --- mysql-test/suite/plugins/r/unix_socket.result | 30 ++++++++++++++ mysql-test/suite/plugins/t/unix_socket.test | 56 +++++++++++++++++++++++++++ 2 files changed, 86 insertions(+) create mode 100644 mysql-test/suite/plugins/r/unix_socket.result create mode 100644 mysql-test/suite/plugins/t/unix_socket.test (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/plugins/r/unix_socket.result b/mysql-test/suite/plugins/r/unix_socket.result new file mode 100644 index 00000000000..45bf608cc93 --- /dev/null +++ b/mysql-test/suite/plugins/r/unix_socket.result @@ -0,0 +1,30 @@ +install plugin unix_socket soname 'auth_socket.so'; +# +# with named user +# +create user USER identified via unix_socket; +# +# name match = ok +# +select user(), current_user(), database(); +user() current_user() database() +USER@localhost USER@% test +# +# name does not match = failure +# +drop user USER; +# +# and now with anonymous user +# +grant SELECT ON test.* TO '' identified via unix_socket; +# +# name match = ok +# +select user(), current_user(), database(); +user() current_user() database() +USER@localhost @% test +# +# name does not match = failure +# +delete from mysql.user where user=''; +uninstall plugin unix_socket; diff --git a/mysql-test/suite/plugins/t/unix_socket.test b/mysql-test/suite/plugins/t/unix_socket.test new file mode 100644 index 00000000000..fc2e6c5b3c6 --- /dev/null +++ b/mysql-test/suite/plugins/t/unix_socket.test @@ -0,0 +1,56 @@ +--source include/not_embedded.inc + +if (!$AUTH_SOCKET_SO) { + skip No auth_socket plugin; +} + +let $plugindir=`SELECT @@global.plugin_dir`; + +eval install plugin unix_socket soname '$AUTH_SOCKET_SO'; + +--echo # +--echo # with named user +--echo # + +--replace_result $USER USER +eval create user $USER identified via unix_socket; + +--write_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt +--replace_result $USER USER +select user(), current_user(), database(); +EOF + +--echo # +--echo # name match = ok +--echo # +--exec $MYSQL_TEST -u $USER --plugin-dir=$plugindir < $MYSQLTEST_VARDIR/tmp/peercred_test.txt + +--echo # +--echo # name does not match = failure +--echo # +--error 1 +--exec $MYSQL_TEST -u foobar --plugin-dir=$plugindir < $MYSQLTEST_VARDIR/tmp/peercred_test.txt + +--replace_result $USER USER +eval drop user $USER; + +--echo # +--echo # and now with anonymous user +--echo # +grant SELECT ON test.* TO '' identified via unix_socket; +--echo # +--echo # name match = ok +--echo # +--exec $MYSQL_TEST -u $USER --plugin-dir=$plugindir < $MYSQLTEST_VARDIR/tmp/peercred_test.txt + +--echo # +--echo # name does not match = failure +--echo # +--error 1 +--exec $MYSQL_TEST -u foobar --plugin-dir=$plugindir < $MYSQLTEST_VARDIR/tmp/peercred_test.txt + +# restoring mysql.user to the original state. +delete from mysql.user where user=''; +uninstall plugin unix_socket; +--remove_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt + -- cgit v1.2.1 From 5438d57315a3cb2b75f63f83de7e50d12645e421 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Fri, 13 Jan 2012 14:35:49 +0200 Subject: Added Handler_read_rnd_deleted, number of deleted rows found with ha_read_rnd_first. --- mysql-test/suite/pbxt/r/null_key.result | 1 + mysql-test/suite/pbxt/r/update.result | 6 ++++++ 2 files changed, 7 insertions(+) (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/pbxt/r/null_key.result b/mysql-test/suite/pbxt/r/null_key.result index bef8c92419a..cc2b417b263 100644 --- a/mysql-test/suite/pbxt/r/null_key.result +++ b/mysql-test/suite/pbxt/r/null_key.result @@ -427,5 +427,6 @@ Handler_read_key 6 Handler_read_next 2 Handler_read_prev 0 Handler_read_rnd 0 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 5 DROP TABLE t1,t2,t3,t4; diff --git a/mysql-test/suite/pbxt/r/update.result b/mysql-test/suite/pbxt/r/update.result index 9154583fa81..46321d6f3bf 100644 --- a/mysql-test/suite/pbxt/r/update.result +++ b/mysql-test/suite/pbxt/r/update.result @@ -276,6 +276,7 @@ Handler_read_key 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 flush status; update t1 set a=9999 order by a limit 1; @@ -290,6 +291,7 @@ Handler_read_key 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 2 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 18 flush status; delete from t1 order by a limit 1; @@ -300,6 +302,7 @@ Handler_read_key 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 1 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 9 flush status; delete from t1 order by a desc limit 1; @@ -310,6 +313,7 @@ Handler_read_key 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 1 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 8 alter table t1 disable keys; Warnings: @@ -323,6 +327,7 @@ Handler_read_key 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 1 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 7 select * from t1; a b @@ -376,6 +381,7 @@ Handler_read_key 1 Handler_read_next 1 Handler_read_prev 0 Handler_read_rnd 1 +Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 drop table t1, t2; create table t1(f1 int, `*f2` int); -- cgit v1.2.1 From 746dbbe583f8528b34065b0669136842128e27b2 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 18 Jan 2012 03:31:20 -0800 Subject: Fixed LP bug #917990. If the expression for a derived table of a query contained a LIMIT clause the estimate of the number of rows in this derived table returned by the EXPLAIN command could be badly off since the optimizer ignored the limit number from the LIMIT clause when getting the estimate. The call of the method SELECT_LEX_UNIT->set_limit added in the code of mysql_derived_optimize() will be needed also in maria-5.5 where parameters in the LIMIT clause are supported. --- mysql-test/suite/vcol/r/vcol_view_innodb.result | 4 ++-- mysql-test/suite/vcol/r/vcol_view_myisam.result | 4 ++-- 2 files changed, 4 insertions(+), 4 deletions(-) (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/vcol/r/vcol_view_innodb.result b/mysql-test/suite/vcol/r/vcol_view_innodb.result index 94c311fb8b9..d117b030f94 100644 --- a/mysql-test/suite/vcol/r/vcol_view_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_view_innodb.result @@ -107,7 +107,7 @@ MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1. MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 4 +1 PRIMARY ALL NULL NULL NULL NULL 2 2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using filesort drop view v1; create view v1 as select c+1 from t1 order by 1 desc limit 2; @@ -119,7 +119,7 @@ MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1. MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 4 +1 PRIMARY ALL NULL NULL NULL NULL 2 2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using filesort drop view v1; drop table t1; diff --git a/mysql-test/suite/vcol/r/vcol_view_myisam.result b/mysql-test/suite/vcol/r/vcol_view_myisam.result index b96e003e1cc..fe48be4c794 100644 --- a/mysql-test/suite/vcol/r/vcol_view_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_view_myisam.result @@ -107,7 +107,7 @@ MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1. MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 4 +1 PRIMARY ALL NULL NULL NULL NULL 2 2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using filesort drop view v1; create view v1 as select c+1 from t1 order by 1 desc limit 2; @@ -119,7 +119,7 @@ MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1. MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 4 +1 PRIMARY ALL NULL NULL NULL NULL 2 2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using filesort drop view v1; drop table t1; -- cgit v1.2.1 From 7ab53e80627e54c6dece486bf0b059436b7e5777 Mon Sep 17 00:00:00 2001 From: Alexey Botchkov Date: Tue, 14 Feb 2012 16:04:06 +0400 Subject: MDEV-59 Review and push crashsafe GIS keys. tests for RTree keys recovery. --- mysql-test/suite/maria/r/maria-gis-recovery.result | 64 ++++++++++++++++++++++ .../suite/maria/t/maria-gis-recovery-master.opt | 1 + mysql-test/suite/maria/t/maria-gis-recovery.test | 64 ++++++++++++++++++++++ 3 files changed, 129 insertions(+) create mode 100644 mysql-test/suite/maria/r/maria-gis-recovery.result create mode 100644 mysql-test/suite/maria/t/maria-gis-recovery-master.opt create mode 100644 mysql-test/suite/maria/t/maria-gis-recovery.test (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/maria/r/maria-gis-recovery.result b/mysql-test/suite/maria/r/maria-gis-recovery.result new file mode 100644 index 00000000000..ca406fc46e5 --- /dev/null +++ b/mysql-test/suite/maria/r/maria-gis-recovery.result @@ -0,0 +1,64 @@ +set global aria_log_file_size=4294967295; +drop database if exists mysqltest; +create database mysqltest; +use mysqltest; +* shut down mysqld, removed logs, restarted it +CREATE TABLE t1 ( +i int, +shape GEOMETRY NOT NULL, +SPATIAL (shape) +) ENGINE=ARIA; +insert into t1 values(1,POINT(1, 1)); +* copied t1 for feeding_recovery +insert into t1 values(2,POINT(2, 2)), (3,POINT(3, 3)), (4,POINT(4, 4)); +flush table t1; +* copied t1 for comparison +SET SESSION debug="+d,maria_flush_whole_log,maria_crash"; +* crashing mysqld intentionally +set global aria_checkpoint_interval=1; +ERROR HY000: Lost connection to MySQL server during query +* copied t1 back for feeding_recovery +* recovery happens +check table t1 extended; +Table Op Msg_type Msg_text +mysqltest.t1 check status OK +* testing that checksum after recovery is as expected +Checksum-check +ok +use mysqltest; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL, + `shape` geometry NOT NULL, + SPATIAL KEY `shape` (`shape`) +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 +* TEST of UPDATE vs state.auto_increment +* copied t1 for feeding_recovery +update t1 set shape=POINT(5, 5) where i=2; +flush table t1; +* copied t1 for comparison +SET SESSION debug="+d,maria_flush_whole_log,maria_crash"; +* crashing mysqld intentionally +set global aria_checkpoint_interval=1; +ERROR HY000: Lost connection to MySQL server during query +* copied t1 back for feeding_recovery +* recovery happens +check table t1 extended; +Table Op Msg_type Msg_text +mysqltest.t1 check status OK +* testing that checksum after recovery is as expected +Checksum-check +ok +use mysqltest; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL, + `shape` geometry NOT NULL, + SPATIAL KEY `shape` (`shape`) +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 +drop table t1; +drop database mysqltest_for_feeding_recovery; +drop database mysqltest_for_comparison; +drop database mysqltest; diff --git a/mysql-test/suite/maria/t/maria-gis-recovery-master.opt b/mysql-test/suite/maria/t/maria-gis-recovery-master.opt new file mode 100644 index 00000000000..58d0d012c54 --- /dev/null +++ b/mysql-test/suite/maria/t/maria-gis-recovery-master.opt @@ -0,0 +1 @@ +--skip-stack-trace --skip-core-file --loose-aria-log-dir-path=$MYSQLTEST_VARDIR/tmp diff --git a/mysql-test/suite/maria/t/maria-gis-recovery.test b/mysql-test/suite/maria/t/maria-gis-recovery.test new file mode 100644 index 00000000000..c40cc3788de --- /dev/null +++ b/mysql-test/suite/maria/t/maria-gis-recovery.test @@ -0,0 +1,64 @@ +--source include/not_embedded.inc +# Don't test this under valgrind, memory leaks will occur as we crash +--source include/not_valgrind.inc +# Binary must be compiled with debug for crash to occur +--source include/have_debug.inc +--source include/have_maria.inc +--source include/have_geometry.inc + +set global aria_log_file_size=4294967295; +let $MARIA_LOG=../../tmp; + +--disable_warnings +drop database if exists mysqltest; +--enable_warnings +create database mysqltest; +let $mms_tname=t; + +# Include scripts can perform SQL. For it to not influence the main test +# they use a separate connection. This way if they use a DDL it would +# not autocommit in the main test. +connect (admin, localhost, root,,mysqltest,,); +--enable_reconnect + +connection default; +use mysqltest; +--enable_reconnect + +-- source include/maria_empty_logs.inc +let $mms_tables=1; +CREATE TABLE t1 ( + i int, + shape GEOMETRY NOT NULL, + SPATIAL (shape) +) ENGINE=ARIA; +insert into t1 values(1,POINT(1, 1)); +-- source include/maria_make_snapshot_for_feeding_recovery.inc +insert into t1 values(2,POINT(2, 2)), (3,POINT(3, 3)), (4,POINT(4, 4)); +-- source include/maria_make_snapshot_for_comparison.inc +let $mvr_restore_old_snapshot=1; +let $mms_compare_physically=0; +let $mvr_debug_option="+d,maria_flush_whole_log,maria_crash"; +let $mvr_crash_statement= set global aria_checkpoint_interval=1; +-- source include/maria_verify_recovery.inc +show create table t1; + +# Test that UPDATE's effect on auto-increment is recovered +--echo * TEST of UPDATE vs state.auto_increment +-- source include/maria_make_snapshot_for_feeding_recovery.inc +update t1 set shape=POINT(5, 5) where i=2; +-- source include/maria_make_snapshot_for_comparison.inc +let $mvr_restore_old_snapshot=1; +let $mms_compare_physically=0; +let $mvr_debug_option="+d,maria_flush_whole_log,maria_crash"; +let $mvr_crash_statement= set global aria_checkpoint_interval=1; +-- source include/maria_verify_recovery.inc +show create table t1; +drop table t1; + +# clean up everything +let $mms_purpose=feeding_recovery; +eval drop database mysqltest_for_$mms_purpose; +let $mms_purpose=comparison; +eval drop database mysqltest_for_$mms_purpose; +drop database mysqltest; -- cgit v1.2.1