diff options
| author | unknown <sanja@askmonty.org> | 2011-09-02 15:10:10 +0300 |
|---|---|---|
| committer | unknown <sanja@askmonty.org> | 2011-09-02 15:10:10 +0300 |
| commit | b152c4c71dff8b2142e220850d9329fe63f4cfbd (patch) | |
| tree | 585b0d57da5c535a64dc5393a268e2d4c81c2066 | |
| parent | ea8aa329099ee28ec6f1266d8d01a6fc664259cf (diff) | |
| parent | 37a8497d494ea256ff4b13a89e62150e06a17dae (diff) | |
| download | mariadb-git-b152c4c71dff8b2142e220850d9329fe63f4cfbd.tar.gz | |
Merge 5.2->5.3
33 files changed, 1709 insertions, 133 deletions
diff --git a/include/my_sys.h b/include/my_sys.h index 553389fc3be..230991002f4 100644 --- a/include/my_sys.h +++ b/include/my_sys.h @@ -49,7 +49,6 @@ extern int NEAR my_errno; /* Last error in mysys */ #define MY_WME 16 /* Write message on error */ #define MY_WAIT_IF_FULL 32 /* Wait and try again if disk full error */ #define MY_IGNORE_BADFD 32 /* my_sync: ignore 'bad descriptor' errors */ -#define MY_SYNC_DIR 1024 /* my_create/delete/rename: sync directory */ #define MY_RAID 64 /* Support for RAID */ #define MY_FULL_IO 512 /* For my_read - loop intil I/O is complete */ #define MY_DONT_CHECK_FILESIZE 128 /* Option to init_io_cache() */ @@ -70,6 +69,7 @@ extern int NEAR my_errno; /* Last error in mysys */ #define MY_DONT_OVERWRITE_FILE 2048 /* my_copy: Don't overwrite file */ #define MY_THREADSAFE 2048 /* my_seek(): lock fd mutex */ #define MY_SYNC 4096 /* my_copy(): sync dst file */ +#define MY_SYNC_DIR 32768 /* my_create/delete/rename: sync directory */ #define MY_CHECK_ERROR 1 /* Params to my_end; Check open-close */ #define MY_GIVE_INFO 2 /* Give time info about process*/ @@ -255,7 +255,7 @@ extern ulong my_file_total_opened; extern ulong my_sync_count; extern uint mysys_usage_id; extern my_bool my_init_done; - +extern myf my_global_flags; /* Set to MY_WME for more error messages */ /* Point to current my_message() */ extern void (*my_sigtstp_cleanup)(void), /* Executed before jump to shell */ diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 30d56e0f8d7..d5e648aa583 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -171,7 +171,7 @@ DROP TABLE t1; # Bug#48295: # explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode # -CREATE TABLE t1 (f1 INT); +CREATE TABLE t1 (f1 INT not null); SELECT @@session.sql_mode INTO @old_sql_mode; SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; EXPLAIN EXTENDED SELECT 1 FROM t1 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 4411d5ffda2..e9b794d36ec 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -922,6 +922,131 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; +# check correct NULL Processing for normal IN/ALL/ANY +# and 2 ways of max/min optimization +create table t1 (a int); +insert into t1 values (1), (100), (NULL), (1000); +create table t2 (a int not null); +# subselect returns empty set (for NULL and non-NULL left part) +select a, a in (select * from t2) from t1; +a a in (select * from t2) +1 0 +100 0 +NULL 0 +1000 0 +select a, a > any (select * from t2) from t1; +a a > any (select * from t2) +1 0 +100 0 +NULL 0 +1000 0 +select a, a > all (select * from t2) from t1; +a a > all (select * from t2) +1 1 +100 1 +NULL 1 +1000 1 +select a from t1 where a in (select * from t2); +a +select a from t1 where a > any (select * from t2); +a +select a from t1 where a > all (select * from t2); +a +1 +100 +NULL +1000 +select a from t1 where a in (select * from t2 group by a); +a +select a from t1 where a > any (select * from t2 group by a); +a +select a from t1 where a > all (select * from t2 group by a); +a +1 +100 +NULL +1000 +insert into t2 values (1),(200); +# sebselect returns non-empty set without NULLs +select a, a in (select * from t2) from t1; +a a in (select * from t2) +1 1 +100 0 +NULL NULL +1000 0 +select a, a > any (select * from t2) from t1; +a a > any (select * from t2) +1 0 +100 1 +NULL NULL +1000 1 +select a, a > all (select * from t2) from t1; +a a > all (select * from t2) +1 0 +100 0 +NULL NULL +1000 1 +select a from t1 where a in (select * from t2); +a +1 +select a from t1 where a > any (select * from t2); +a +100 +1000 +select a from t1 where a > all (select * from t2); +a +1000 +select a from t1 where a in (select * from t2 group by a); +a +1 +select a from t1 where a > any (select * from t2 group by a); +a +100 +1000 +select a from t1 where a > all (select * from t2 group by a); +a +1000 +drop table t2; +create table t2 (a int); +insert into t2 values (1),(NULL),(200); +# sebselect returns non-empty set with NULLs +select a, a in (select * from t2) from t1; +a a in (select * from t2) +1 1 +100 NULL +NULL NULL +1000 NULL +select a, a > any (select * from t2) from t1; +a a > any (select * from t2) +1 NULL +100 1 +NULL NULL +1000 1 +select a, a > all (select * from t2) from t1; +a a > all (select * from t2) +1 0 +100 0 +NULL NULL +1000 NULL +select a from t1 where a in (select * from t2); +a +1 +select a from t1 where a > any (select * from t2); +a +100 +1000 +select a from t1 where a > all (select * from t2); +a +select a from t1 where a in (select * from t2 group by a); +a +1 +select a from t1 where a > any (select * from t2 group by a); +a +100 +1000 +select a from t1 where a > all (select * from t2 group by a); +a +drop table t1, t2; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' @@ -1501,7 +1626,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; -create table t2 (a int, b int); +create table t2 (a int, b int not null); create table t3 (a int); insert into t3 values (6),(7),(3); select * from t3 where a >= all (select b from t2); @@ -5113,6 +5238,148 @@ SELECT 1 as foo FROM t1 WHERE a < SOME ); foo DROP TABLE t1; +CREATE TABLE t1 (a int(11), b varchar(1)); +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +a +5 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +a +7 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +a +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); +a +delete from t1; +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +a +5 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +a +7 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +a +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); +a +drop table t1; +End of 5.2 tests # # BUG#779885: Crash in eliminate_item_equal with materialization=on in # maria-5.3 diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index dd9e9dc3e0e..570a7b1e6a1 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -927,6 +927,131 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; +# check correct NULL Processing for normal IN/ALL/ANY +# and 2 ways of max/min optimization +create table t1 (a int); +insert into t1 values (1), (100), (NULL), (1000); +create table t2 (a int not null); +# subselect returns empty set (for NULL and non-NULL left part) +select a, a in (select * from t2) from t1; +a a in (select * from t2) +1 0 +100 0 +NULL 0 +1000 0 +select a, a > any (select * from t2) from t1; +a a > any (select * from t2) +1 0 +100 0 +NULL 0 +1000 0 +select a, a > all (select * from t2) from t1; +a a > all (select * from t2) +1 1 +100 1 +NULL 1 +1000 1 +select a from t1 where a in (select * from t2); +a +select a from t1 where a > any (select * from t2); +a +select a from t1 where a > all (select * from t2); +a +1 +100 +NULL +1000 +select a from t1 where a in (select * from t2 group by a); +a +select a from t1 where a > any (select * from t2 group by a); +a +select a from t1 where a > all (select * from t2 group by a); +a +1 +100 +NULL +1000 +insert into t2 values (1),(200); +# sebselect returns non-empty set without NULLs +select a, a in (select * from t2) from t1; +a a in (select * from t2) +1 1 +100 0 +NULL NULL +1000 0 +select a, a > any (select * from t2) from t1; +a a > any (select * from t2) +1 0 +100 1 +NULL NULL +1000 1 +select a, a > all (select * from t2) from t1; +a a > all (select * from t2) +1 0 +100 0 +NULL NULL +1000 1 +select a from t1 where a in (select * from t2); +a +1 +select a from t1 where a > any (select * from t2); +a +100 +1000 +select a from t1 where a > all (select * from t2); +a +1000 +select a from t1 where a in (select * from t2 group by a); +a +1 +select a from t1 where a > any (select * from t2 group by a); +a +100 +1000 +select a from t1 where a > all (select * from t2 group by a); +a +1000 +drop table t2; +create table t2 (a int); +insert into t2 values (1),(NULL),(200); +# sebselect returns non-empty set with NULLs +select a, a in (select * from t2) from t1; +a a in (select * from t2) +1 1 +100 NULL +NULL NULL +1000 NULL +select a, a > any (select * from t2) from t1; +a a > any (select * from t2) +1 NULL +100 1 +NULL NULL +1000 1 +select a, a > all (select * from t2) from t1; +a a > all (select * from t2) +1 0 +100 0 +NULL NULL +1000 NULL +select a from t1 where a in (select * from t2); +a +1 +select a from t1 where a > any (select * from t2); +a +100 +1000 +select a from t1 where a > all (select * from t2); +a +select a from t1 where a in (select * from t2 group by a); +a +1 +select a from t1 where a > any (select * from t2 group by a); +a +100 +1000 +select a from t1 where a > all (select * from t2 group by a); +a +drop table t1, t2; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' @@ -1506,7 +1631,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; -create table t2 (a int, b int); +create table t2 (a int, b int not null); create table t3 (a int); insert into t3 values (6),(7),(3); select * from t3 where a >= all (select b from t2); @@ -5118,6 +5243,148 @@ SELECT 1 as foo FROM t1 WHERE a < SOME ); foo DROP TABLE t1; +CREATE TABLE t1 (a int(11), b varchar(1)); +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +a +5 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +a +7 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +a +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); +a +delete from t1; +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +a +5 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +a +7 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +a +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); +a +drop table t1; +End of 5.2 tests # # BUG#779885: Crash in eliminate_item_equal with materialization=on in # maria-5.3 diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 4f1b9f350f8..2a30a8b2457 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -923,6 +923,131 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; +# check correct NULL Processing for normal IN/ALL/ANY +# and 2 ways of max/min optimization +create table t1 (a int); +insert into t1 values (1), (100), (NULL), (1000); +create table t2 (a int not null); +# subselect returns empty set (for NULL and non-NULL left part) +select a, a in (select * from t2) from t1; +a a in (select * from t2) +1 0 +100 0 +NULL 0 +1000 0 +select a, a > any (select * from t2) from t1; +a a > any (select * from t2) +1 0 +100 0 +NULL 0 +1000 0 +select a, a > all (select * from t2) from t1; +a a > all (select * from t2) +1 1 +100 1 +NULL 1 +1000 1 +select a from t1 where a in (select * from t2); +a +select a from t1 where a > any (select * from t2); +a +select a from t1 where a > all (select * from t2); +a +1 +100 +NULL +1000 +select a from t1 where a in (select * from t2 group by a); +a +select a from t1 where a > any (select * from t2 group by a); +a +select a from t1 where a > all (select * from t2 group by a); +a +1 +100 +NULL +1000 +insert into t2 values (1),(200); +# sebselect returns non-empty set without NULLs +select a, a in (select * from t2) from t1; +a a in (select * from t2) +1 1 +100 0 +NULL NULL +1000 0 +select a, a > any (select * from t2) from t1; +a a > any (select * from t2) +1 0 +100 1 +NULL NULL +1000 1 +select a, a > all (select * from t2) from t1; +a a > all (select * from t2) +1 0 +100 0 +NULL NULL +1000 1 +select a from t1 where a in (select * from t2); +a +1 +select a from t1 where a > any (select * from t2); +a +100 +1000 +select a from t1 where a > all (select * from t2); +a +1000 +select a from t1 where a in (select * from t2 group by a); +a +1 +select a from t1 where a > any (select * from t2 group by a); +a +100 +1000 +select a from t1 where a > all (select * from t2 group by a); +a +1000 +drop table t2; +create table t2 (a int); +insert into t2 values (1),(NULL),(200); +# sebselect returns non-empty set with NULLs +select a, a in (select * from t2) from t1; +a a in (select * from t2) +1 1 +100 NULL +NULL NULL +1000 NULL +select a, a > any (select * from t2) from t1; +a a > any (select * from t2) +1 NULL +100 1 +NULL NULL +1000 1 +select a, a > all (select * from t2) from t1; +a a > all (select * from t2) +1 0 +100 0 +NULL NULL +1000 NULL +select a from t1 where a in (select * from t2); +a +1 +select a from t1 where a > any (select * from t2); +a +100 +1000 +select a from t1 where a > all (select * from t2); +a +select a from t1 where a in (select * from t2 group by a); +a +1 +select a from t1 where a > any (select * from t2 group by a); +a +100 +1000 +select a from t1 where a > all (select * from t2 group by a); +a +drop table t1, t2; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' @@ -1502,7 +1627,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; -create table t2 (a int, b int); +create table t2 (a int, b int not null); create table t3 (a int); insert into t3 values (6),(7),(3); select * from t3 where a >= all (select b from t2); @@ -5114,6 +5239,148 @@ SELECT 1 as foo FROM t1 WHERE a < SOME ); foo DROP TABLE t1; +CREATE TABLE t1 (a int(11), b varchar(1)); +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +a +5 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +a +7 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +a +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); +a +delete from t1; +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +a +5 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +a +7 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +a +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); +a +drop table t1; +End of 5.2 tests # # BUG#779885: Crash in eliminate_item_equal with materialization=on in # maria-5.3 diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index d68bbacc7b9..ed6b46e979d 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -923,6 +923,131 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; +# check correct NULL Processing for normal IN/ALL/ANY +# and 2 ways of max/min optimization +create table t1 (a int); +insert into t1 values (1), (100), (NULL), (1000); +create table t2 (a int not null); +# subselect returns empty set (for NULL and non-NULL left part) +select a, a in (select * from t2) from t1; +a a in (select * from t2) +1 0 +100 0 +NULL 0 +1000 0 +select a, a > any (select * from t2) from t1; +a a > any (select * from t2) +1 0 +100 0 +NULL 0 +1000 0 +select a, a > all (select * from t2) from t1; +a a > all (select * from t2) +1 1 +100 1 +NULL 1 +1000 1 +select a from t1 where a in (select * from t2); +a +select a from t1 where a > any (select * from t2); +a +select a from t1 where a > all (select * from t2); +a +1 +100 +NULL +1000 +select a from t1 where a in (select * from t2 group by a); +a +select a from t1 where a > any (select * from t2 group by a); +a +select a from t1 where a > all (select * from t2 group by a); +a +1 +100 +NULL +1000 +insert into t2 values (1),(200); +# sebselect returns non-empty set without NULLs +select a, a in (select * from t2) from t1; +a a in (select * from t2) +1 1 +100 0 +NULL NULL +1000 0 +select a, a > any (select * from t2) from t1; +a a > any (select * from t2) +1 0 +100 1 +NULL NULL +1000 1 +select a, a > all (select * from t2) from t1; +a a > all (select * from t2) +1 0 +100 0 +NULL NULL +1000 1 +select a from t1 where a in (select * from t2); +a +1 +select a from t1 where a > any (select * from t2); +a +100 +1000 +select a from t1 where a > all (select * from t2); +a +1000 +select a from t1 where a in (select * from t2 group by a); +a +1 +select a from t1 where a > any (select * from t2 group by a); +a +100 +1000 +select a from t1 where a > all (select * from t2 group by a); +a +1000 +drop table t2; +create table t2 (a int); +insert into t2 values (1),(NULL),(200); +# sebselect returns non-empty set with NULLs +select a, a in (select * from t2) from t1; +a a in (select * from t2) +1 1 +100 NULL +NULL NULL +1000 NULL +select a, a > any (select * from t2) from t1; +a a > any (select * from t2) +1 NULL +100 1 +NULL NULL +1000 1 +select a, a > all (select * from t2) from t1; +a a > all (select * from t2) +1 0 +100 0 +NULL NULL +1000 NULL +select a from t1 where a in (select * from t2); +a +1 +select a from t1 where a > any (select * from t2); +a +100 +1000 +select a from t1 where a > all (select * from t2); +a +select a from t1 where a in (select * from t2 group by a); +a +1 +select a from t1 where a > any (select * from t2 group by a); +a +100 +1000 +select a from t1 where a > all (select * from t2 group by a); +a +drop table t1, t2; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' @@ -1502,7 +1627,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; -create table t2 (a int, b int); +create table t2 (a int, b int not null); create table t3 (a int); insert into t3 values (6),(7),(3); select * from t3 where a >= all (select b from t2); @@ -5114,6 +5239,148 @@ SELECT 1 as foo FROM t1 WHERE a < SOME ); foo DROP TABLE t1; +CREATE TABLE t1 (a int(11), b varchar(1)); +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +a +5 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +a +7 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +a +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); +a +delete from t1; +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +a +5 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +a +7 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +a +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); +a +drop table t1; +End of 5.2 tests # # BUG#779885: Crash in eliminate_item_equal with materialization=on in # maria-5.3 diff --git a/mysql-test/r/subselect_scache.result b/mysql-test/r/subselect_scache.result index 1dff1ab8921..54011c581f9 100644 --- a/mysql-test/r/subselect_scache.result +++ b/mysql-test/r/subselect_scache.result @@ -926,6 +926,131 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t2`.`a`) and ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or isnull(`test`.`t2`.`a`))) having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1` drop table t1,t2,t3; +# check correct NULL Processing for normal IN/ALL/ANY +# and 2 ways of max/min optimization +create table t1 (a int); +insert into t1 values (1), (100), (NULL), (1000); +create table t2 (a int not null); +# subselect returns empty set (for NULL and non-NULL left part) +select a, a in (select * from t2) from t1; +a a in (select * from t2) +1 0 +100 0 +NULL 0 +1000 0 +select a, a > any (select * from t2) from t1; +a a > any (select * from t2) +1 0 +100 0 +NULL 0 +1000 0 +select a, a > all (select * from t2) from t1; +a a > all (select * from t2) +1 1 +100 1 +NULL 1 +1000 1 +select a from t1 where a in (select * from t2); +a +select a from t1 where a > any (select * from t2); +a +select a from t1 where a > all (select * from t2); +a +1 +100 +NULL +1000 +select a from t1 where a in (select * from t2 group by a); +a +select a from t1 where a > any (select * from t2 group by a); +a +select a from t1 where a > all (select * from t2 group by a); +a +1 +100 +NULL +1000 +insert into t2 values (1),(200); +# sebselect returns non-empty set without NULLs +select a, a in (select * from t2) from t1; +a a in (select * from t2) +1 1 +100 0 +NULL NULL +1000 0 +select a, a > any (select * from t2) from t1; +a a > any (select * from t2) +1 0 +100 1 +NULL NULL +1000 1 +select a, a > all (select * from t2) from t1; +a a > all (select * from t2) +1 0 +100 0 +NULL NULL +1000 1 +select a from t1 where a in (select * from t2); +a +1 +select a from t1 where a > any (select * from t2); +a +100 +1000 +select a from t1 where a > all (select * from t2); +a +1000 +select a from t1 where a in (select * from t2 group by a); +a +1 +select a from t1 where a > any (select * from t2 group by a); +a +100 +1000 +select a from t1 where a > all (select * from t2 group by a); +a +1000 +drop table t2; +create table t2 (a int); +insert into t2 values (1),(NULL),(200); +# sebselect returns non-empty set with NULLs +select a, a in (select * from t2) from t1; +a a in (select * from t2) +1 1 +100 NULL +NULL NULL +1000 NULL +select a, a > any (select * from t2) from t1; +a a > any (select * from t2) +1 NULL +100 1 +NULL NULL +1000 1 +select a, a > all (select * from t2) from t1; +a a > all (select * from t2) +1 0 +100 0 +NULL NULL +1000 NULL +select a from t1 where a in (select * from t2); +a +1 +select a from t1 where a > any (select * from t2); +a +100 +1000 +select a from t1 where a > all (select * from t2); +a +select a from t1 where a in (select * from t2 group by a); +a +1 +select a from t1 where a > any (select * from t2 group by a); +a +100 +1000 +select a from t1 where a > all (select * from t2 group by a); +a +drop table t1, t2; create table t1 (a float); select 10.5 IN (SELECT * from t1 LIMIT 1); ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' @@ -1505,7 +1630,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<expr_cache><`test`.`t1`.`s1`>(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`)))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1` drop table t1,t2; -create table t2 (a int, b int); +create table t2 (a int, b int not null); create table t3 (a int); insert into t3 values (6),(7),(3); select * from t3 where a >= all (select b from t2); @@ -5117,6 +5242,148 @@ SELECT 1 as foo FROM t1 WHERE a < SOME ); foo DROP TABLE t1; +CREATE TABLE t1 (a int(11), b varchar(1)); +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +a +5 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +a +7 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +a +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); +a +delete from t1; +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +a +5 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +a +7 +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +a +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +a +5 +7 +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); +a +5 +7 +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +a +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); +a +drop table t1; +End of 5.2 tests # # BUG#779885: Crash in eliminate_item_equal with materialization=on in # maria-5.3 diff --git a/mysql-test/suite/maria/r/maria3.result b/mysql-test/suite/maria/r/maria3.result index 056147c69c0..47c02999087 100644 --- a/mysql-test/suite/maria/r/maria3.result +++ b/mysql-test/suite/maria/r/maria3.result @@ -305,6 +305,7 @@ select lower(variable_name) as Variable_name, Variable_value as Value from infor Variable_name Value aria_block_size 8192 aria_checkpoint_interval 30 +aria_checkpoint_log_activity 1048576 aria_force_start_after_recovery_failures 0 aria_group_commit none aria_group_commit_interval 0 diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 1cfd78143c7..186a00af16a 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -152,7 +152,7 @@ DROP TABLE t1; --echo # explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode --echo # -CREATE TABLE t1 (f1 INT); +CREATE TABLE t1 (f1 INT not null); SELECT @@session.sql_mode INTO @old_sql_mode; SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index d460eb57667..c635424eeab 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -504,6 +504,54 @@ SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1; drop table t1,t2,t3; +--echo # check correct NULL Processing for normal IN/ALL/ANY +--echo # and 2 ways of max/min optimization +create table t1 (a int); +insert into t1 values (1), (100), (NULL), (1000); +create table t2 (a int not null); + +--echo # subselect returns empty set (for NULL and non-NULL left part) +select a, a in (select * from t2) from t1; +select a, a > any (select * from t2) from t1; +select a, a > all (select * from t2) from t1; +select a from t1 where a in (select * from t2); +select a from t1 where a > any (select * from t2); +select a from t1 where a > all (select * from t2); +select a from t1 where a in (select * from t2 group by a); +select a from t1 where a > any (select * from t2 group by a); +select a from t1 where a > all (select * from t2 group by a); + +insert into t2 values (1),(200); + +--echo # sebselect returns non-empty set without NULLs +select a, a in (select * from t2) from t1; +select a, a > any (select * from t2) from t1; +select a, a > all (select * from t2) from t1; +select a from t1 where a in (select * from t2); +select a from t1 where a > any (select * from t2); +select a from t1 where a > all (select * from t2); +select a from t1 where a in (select * from t2 group by a); +select a from t1 where a > any (select * from t2 group by a); +select a from t1 where a > all (select * from t2 group by a); + +drop table t2; +create table t2 (a int); +insert into t2 values (1),(NULL),(200); + +--echo # sebselect returns non-empty set with NULLs +select a, a in (select * from t2) from t1; +select a, a > any (select * from t2) from t1; +select a, a > all (select * from t2) from t1; +select a from t1 where a in (select * from t2); +select a from t1 where a > any (select * from t2); +select a from t1 where a > all (select * from t2); +select a from t1 where a in (select * from t2 group by a); +select a from t1 where a > any (select * from t2 group by a); +select a from t1 where a > all (select * from t2 group by a); + + +drop table t1, t2; + #LIMIT is not supported now create table t1 (a float); -- error ER_NOT_SUPPORTED_YET @@ -935,7 +983,7 @@ drop table t1,t2; # # correct ALL optimisation # -create table t2 (a int, b int); +create table t2 (a int, b int not null); create table t3 (a int); insert into t3 values (6),(7),(3); select * from t3 where a >= all (select b from t2); @@ -4379,6 +4427,71 @@ SELECT 1 as foo FROM t1 WHERE a < SOME DROP TABLE t1; +# +# LP BUG#823169 NULLs with ALL/ANY and maxmin optimization +# +CREATE TABLE t1 (a int(11), b varchar(1)); +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); + +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); + +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); + +delete from t1; +INSERT INTO t1 VALUES (2,NULL),(5,'d'),(7,'g'); + +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b < ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b > ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <= ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b >= ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ANY ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <> ANY ( SELECT b FROM t1 GROUP BY b ); + +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b < ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b > ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <= ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b >= ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b = ALL ( SELECT b FROM t1 GROUP BY b ); +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 ); +SELECT a FROM t1 WHERE b <> ALL ( SELECT b FROM t1 GROUP BY b ); + +drop table t1; + +--echo End of 5.2 tests + --echo # --echo # BUG#779885: Crash in eliminate_item_equal with materialization=on in --echo # maria-5.3 diff --git a/mysys/mf_iocache.c b/mysys/mf_iocache.c index 1905437cc0a..8add0f9f4cb 100644 --- a/mysys/mf_iocache.c +++ b/mysys/mf_iocache.c @@ -1741,7 +1741,7 @@ int my_b_flush_io_cache(IO_CACHE *info, */ if (!append_cache && info->seek_not_done) { /* File touched, do seek */ - if (my_seek(info->file,pos_in_file,MY_SEEK_SET,MYF(0)) == + if (my_seek(info->file,pos_in_file,MY_SEEK_SET,MYF(info->myflags & MY_WME)) == MY_FILEPOS_ERROR) { UNLOCK_APPEND_BUFFER; diff --git a/mysys/my_init.c b/mysys/my_init.c index 863b636d9d3..bb9900b425c 100644 --- a/mysys/my_init.c +++ b/mysys/my_init.c @@ -77,6 +77,8 @@ my_bool my_init(void) mysys_usage_id++; my_umask= 0660; /* Default umask for new files */ my_umask_dir= 0700; /* Default umask for new directories */ + my_global_flags= 0; + init_glob_errs(); my_progname_short= "unknown"; if (my_progname) diff --git a/mysys/my_malloc.c b/mysys/my_malloc.c index 330ef07ed30..25180edc06f 100644 --- a/mysys/my_malloc.c +++ b/mysys/my_malloc.c @@ -31,6 +31,8 @@ void *my_malloc(size_t size, myf my_flags) void* point; DBUG_ENTER("my_malloc"); DBUG_PRINT("my",("size: %lu my_flags: %d", (ulong) size, my_flags)); + if (!(my_flags & (MY_WME | MY_FAE))) + my_flags|= my_global_flags; if (!size) size=1; /* Safety */ @@ -48,7 +50,9 @@ void *my_malloc(size_t size, myf my_flags) if (my_flags & MY_FAE) error_handler_hook=fatal_error_handler_hook; if (my_flags & (MY_FAE+MY_WME)) - my_error(EE_OUTOFMEMORY, MYF(ME_BELL+ME_WAITTANG+ME_NOREFRESH),size); + my_error(EE_OUTOFMEMORY, + MYF(ME_BELL | ME_WAITTANG | ME_NOREFRESH | (my_flags & ME_JUST_INFO)), + size); DBUG_EXECUTE_IF("simulate_out_of_memory", DBUG_SET("-d,simulate_out_of_memory");); if (my_flags & MY_FAE) diff --git a/mysys/my_open.c b/mysys/my_open.c index b1788506832..dc2fcaf04c6 100644 --- a/mysys/my_open.c +++ b/mysys/my_open.c @@ -41,6 +41,8 @@ File my_open(const char *FileName, int Flags, myf MyFlags) DBUG_ENTER("my_open"); DBUG_PRINT("my",("Name: '%s' Flags: %d MyFlags: %d", FileName, Flags, MyFlags)); + if (!(MyFlags & (MY_WME | MY_FAE | MY_FFNF))) + MyFlags|= my_global_flags; #if defined(_WIN32) fd= my_win_open(FileName, Flags); #elif !defined(NO_OPEN_3) @@ -69,6 +71,8 @@ int my_close(File fd, myf MyFlags) int err; DBUG_ENTER("my_close"); DBUG_PRINT("my",("fd: %d MyFlags: %d",fd, MyFlags)); + if (!(MyFlags & (MY_WME | MY_FAE))) + MyFlags|= my_global_flags; pthread_mutex_lock(&THR_LOCK_open); #ifndef _WIN32 @@ -84,7 +88,8 @@ int my_close(File fd, myf MyFlags) DBUG_PRINT("error",("Got error %d on close",err)); my_errno=errno; if (MyFlags & (MY_FAE | MY_WME)) - my_error(EE_BADCLOSE, MYF(ME_BELL+ME_WAITTANG),my_filename(fd),errno); + my_error(EE_BADCLOSE, MYF(ME_BELL | ME_WAITTANG | (MyFlags & (ME_JUST_INFO | ME_NOREFRESH))), + my_filename(fd),errno); } if ((uint) fd < my_file_limit && my_file_info[fd].type != UNOPEN) { @@ -150,8 +155,8 @@ File my_register_filename(File fd, const char *FileName, enum file_type { if (my_errno == EMFILE) error_message_number= EE_OUT_OF_FILERESOURCES; - DBUG_PRINT("error",("print err: %d",error_message_number)); - my_error(error_message_number, MYF(ME_BELL+ME_WAITTANG), + my_error(error_message_number, + MYF(ME_BELL | ME_WAITTANG | (MyFlags & (ME_JUST_INFO | ME_NOREFRESH))), FileName, my_errno); } DBUG_RETURN(-1); diff --git a/mysys/my_pread.c b/mysys/my_pread.c index 6421a2da601..f8799561c09 100644 --- a/mysys/my_pread.c +++ b/mysys/my_pread.c @@ -56,6 +56,9 @@ size_t my_pread(File Filedes, uchar *Buffer, size_t Count, my_off_t offset, Filedes, ullstr(offset, llbuf), (long) Buffer, (ulong)Count, MyFlags)); #endif + if (!(MyFlags & (MY_WME | MY_FAE | MY_FNABP))) + MyFlags|= my_global_flags; + for (;;) { errno= 0; /* Linux, Windows don't reset this on EOF/success */ @@ -84,11 +87,13 @@ size_t my_pread(File Filedes, uchar *Buffer, size_t Count, my_off_t offset, #endif if (MyFlags & (MY_WME | MY_FAE | MY_FNABP)) { - if (readbytes == (size_t) -1) - my_error(EE_READ, MYF(ME_BELL+ME_WAITTANG), - my_filename(Filedes),my_errno); - else if (MyFlags & (MY_NABP | MY_FNABP)) - my_error(EE_EOFERR, MYF(ME_BELL+ME_WAITTANG), + if (readbytes == (size_t) -1) + my_error(EE_READ, + MYF(ME_BELL | ME_WAITTANG | (MyFlags & (ME_JUST_INFO | ME_NOREFRESH))), + my_filename(Filedes),my_errno); + else if (MyFlags & (MY_NABP | MY_FNABP)) + my_error(EE_EOFERR, + MYF(ME_BELL | ME_WAITTANG | (MyFlags & (ME_JUST_INFO | ME_NOREFRESH))), my_filename(Filedes),my_errno); } if (readbytes == (size_t) -1 || (MyFlags & (MY_FNABP | MY_NABP))) @@ -136,6 +141,8 @@ size_t my_pwrite(int Filedes, const uchar *Buffer, size_t Count, #endif errors= 0; written= 0; + if (!(MyFlags & (MY_WME | MY_FAE | MY_FNABP))) + MyFlags|= my_global_flags; for (;;) { @@ -170,20 +177,19 @@ size_t my_pwrite(int Filedes, const uchar *Buffer, size_t Count, if ((writtenbytes && writtenbytes != (size_t) -1) || my_errno == EINTR) continue; /* Retry */ #endif + + /* Don't give a warning if it's ok that we only write part of the data */ if (MyFlags & (MY_NABP | MY_FNABP)) { if (MyFlags & (MY_WME | MY_FAE | MY_FNABP)) - { - my_error(EE_WRITE, MYF(ME_BELL | ME_WAITTANG), + my_error(EE_WRITE, MYF(ME_BELL | ME_WAITTANG | (MyFlags & (ME_JUST_INFO | ME_NOREFRESH))), my_filename(Filedes),my_errno); - } - DBUG_RETURN(MY_FILE_ERROR); /* Error on read */ + DBUG_RETURN(MY_FILE_ERROR); /* Error on write */ } - else - break; /* Return bytes written */ + break; /* Return bytes written */ } DBUG_EXECUTE_IF("check", my_seek(Filedes, -1, SEEK_SET, MYF(0));); if (MyFlags & (MY_NABP | MY_FNABP)) - DBUG_RETURN(0); /* Want only errors */ - DBUG_RETURN(writtenbytes+written); /* purecov: inspected */ + DBUG_RETURN(0); /* Want only errors */ + DBUG_RETURN(writtenbytes+written); /* purecov: inspected */ } /* my_pwrite */ diff --git a/mysys/my_read.c b/mysys/my_read.c index 5fef4455f62..8ababb31324 100644 --- a/mysys/my_read.c +++ b/mysys/my_read.c @@ -40,6 +40,8 @@ size_t my_read(File Filedes, uchar *Buffer, size_t Count, myf MyFlags) DBUG_PRINT("my",("fd: %d Buffer: 0x%lx Count: %lu MyFlags: %d", Filedes, (long) Buffer, (ulong) Count, MyFlags)); save_count= Count; + if (!(MyFlags & (MY_WME | MY_FAE | MY_FNABP))) + MyFlags|= my_global_flags; for (;;) { @@ -69,10 +71,12 @@ size_t my_read(File Filedes, uchar *Buffer, size_t Count, myf MyFlags) if (MyFlags & (MY_WME | MY_FAE | MY_FNABP)) { if (readbytes == (size_t) -1) - my_error(EE_READ, MYF(ME_BELL+ME_WAITTANG), + my_error(EE_READ, + MYF(ME_BELL | ME_WAITTANG | (MyFlags & (ME_JUST_INFO | ME_NOREFRESH))), my_filename(Filedes),my_errno); else if (MyFlags & (MY_NABP | MY_FNABP)) - my_error(EE_EOFERR, MYF(ME_BELL+ME_WAITTANG), + my_error(EE_EOFERR, + MYF(ME_BELL | ME_WAITTANG | (MyFlags & (ME_JUST_INFO | ME_NOREFRESH))), my_filename(Filedes),my_errno); } if (readbytes == (size_t) -1 || diff --git a/mysys/my_static.c b/mysys/my_static.c index d6b2f9f110c..c33a12c2ca0 100644 --- a/mysys/my_static.c +++ b/mysys/my_static.c @@ -32,6 +32,7 @@ char NEAR curr_dir[FN_REFLEN]= {0}, ulong my_stream_opened=0,my_file_opened=0, my_tmp_file_created=0; ulong my_file_total_opened= 0; int NEAR my_umask=0664, NEAR my_umask_dir=0777; +myf my_global_flags; #ifndef THREAD int NEAR my_errno=0; #endif diff --git a/mysys/my_write.c b/mysys/my_write.c index be493049d5c..ae2eb155068 100644 --- a/mysys/my_write.c +++ b/mysys/my_write.c @@ -28,6 +28,8 @@ size_t my_write(int Filedes, const uchar *Buffer, size_t Count, myf MyFlags) DBUG_PRINT("my",("fd: %d Buffer: 0x%lx Count: %lu MyFlags: %d", Filedes, (long) Buffer, (ulong) Count, MyFlags)); errors=0; written=0; + if (!(MyFlags & (MY_WME | MY_FAE | MY_FNABP))) + MyFlags|= my_global_flags; /* The behavior of write(fd, buf, 0) is not portable */ if (unlikely(!Count)) @@ -88,19 +90,20 @@ size_t my_write(int Filedes, const uchar *Buffer, size_t Count, myf MyFlags) else continue; /* Retry */ #endif + + /* Don't give a warning if it's ok that we only write part of the data */ if (MyFlags & (MY_NABP | MY_FNABP)) { if (MyFlags & (MY_WME | MY_FAE | MY_FNABP)) { - my_error(EE_WRITE, MYF(ME_BELL+ME_WAITTANG), + my_error(EE_WRITE, MYF(ME_BELL | ME_WAITTANG | (MyFlags & (ME_JUST_INFO | ME_NOREFRESH))), my_filename(Filedes),my_errno); } DBUG_RETURN(MY_FILE_ERROR); /* Error on read */ } - else - break; /* Return bytes written */ + break; /* Return bytes written */ } if (MyFlags & (MY_NABP | MY_FNABP)) - DBUG_RETURN(0); /* Want only errors */ + DBUG_RETURN(0); /* Want only errors */ DBUG_RETURN(writtenbytes+written); } /* my_write */ diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 204279115da..aeea568a930 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -822,7 +822,10 @@ Item_maxmin_subselect::Item_maxmin_subselect(THD *thd_param, { DBUG_ENTER("Item_maxmin_subselect::Item_maxmin_subselect"); max= max_arg; - init(select_lex, new select_max_min_finder_subselect(this, max_arg)); + init(select_lex, + new select_max_min_finder_subselect(this, max_arg, + parent->substype() == + Item_subselect::ALL_SUBS)); max_columns= 1; maybe_null= 1; max_columns= 1; @@ -1598,11 +1601,20 @@ bool Item_allany_subselect::transform_into_max_min(JOIN *join) */ DBUG_ASSERT(!substitution); - if (!select_lex->group_list.elements && - !select_lex->having && - !select_lex->with_sum_func && - !(select_lex->next_select()) && - select_lex->table_list.elements) + /* + Check if optimization with aggregate min/max possible + 1 There is no aggregate in the subquery + 2 It is not UNION + 3 There is tables + 4 It is not ALL subquery with possible NULLs in the SELECT list + */ + if (!select_lex->group_list.elements && /*1*/ + !select_lex->having && /*1*/ + !select_lex->with_sum_func && /*1*/ + !(select_lex->next_select()) && /*2*/ + select_lex->table_list.elements && /*3*/ + (!select_lex->ref_pointer_array[0]->maybe_null || /*4*/ + substype() != Item_subselect::ALL_SUBS)) /*4*/ { Item_sum_hybrid *item; nesting_map save_allow_sum_func; diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 1c9b6a19d38..a3417282661 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -3099,7 +3099,7 @@ int my_message_sql(uint error, const char *str, myf MyFlags) MYSQL_ERROR::enum_warning_level level; sql_print_message_func func; DBUG_ENTER("my_message_sql"); - DBUG_PRINT("error", ("error: %u message: '%s'", error, str)); + DBUG_PRINT("error", ("error: %u message: '%s' Flag: %d", error, str, MyFlags)); DBUG_ASSERT(str != NULL); /* @@ -3143,7 +3143,10 @@ int my_message_sql(uint error, const char *str, myf MyFlags) this could be improved by having a common stack of handlers. */ if (thd->handle_error(error, str, level)) + { + DBUG_PRINT("info", ("error handled by handle_error()")); DBUG_RETURN(0); + } if (level == MYSQL_ERROR::WARN_LEVEL_WARN) push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, error, str); @@ -3222,7 +3225,7 @@ to_error_log: /* When simulating OOM, skip writing to error log to avoid mtr errors */ DBUG_EXECUTE_IF("simulate_out_of_memory", DBUG_RETURN(0);); - if (!thd || (MyFlags & ME_NOREFRESH)) + if (!thd || thd->log_all_errors || (MyFlags & ME_NOREFRESH)) (*func)("%s: %s", my_progname_short, str); /* purecov: inspected */ DBUG_RETURN(0); } @@ -6584,8 +6587,7 @@ each time the SQL thread starts.", "log and this option just turns on --log-bin instead.", &opt_update_logname, &opt_update_logname, 0, GET_STR, OPT_ARG, 0, 0, 0, 0, 0, 0}, - {"log-warnings", 'W', "Log some not critical warnings to the general log " - "file.", + {"log-warnings", 'W', "Log some not critical warnings to the general log file. Value can be between 0-6; The higher value, the more warnings", &global_system_variables.log_warnings, &max_system_variables.log_warnings, 0, GET_ULONG, OPT_ARG, 1, 0, 0, 0, 0, 0}, @@ -9577,6 +9579,16 @@ static int get_options(int *argc,char **argv) myisam_block_size=(uint) 1 << my_bit_log2(opt_myisam_block_size); my_crc_dbug_check= opt_my_crc_dbug_check; + /* + Log mysys errors when we don't have a thd or thd->log_all_errors is set (recovery) to + the log. This is mainly useful for debugging strange system errors. + */ + if (global_system_variables.log_warnings >= 10) + my_global_flags= MY_WME | ME_JUST_INFO; + /* Log all errors not handled by thd->handle_error() to my_message_sql() */ + if (global_system_variables.log_warnings >= 11) + my_global_flags|= ME_NOREFRESH; + /* long_query_time is in microseconds */ global_system_variables.long_query_time= max_system_variables.long_query_time= (longlong) (long_query_time * 1000000.0); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index c025011e8e5..d8821f154b7 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -62,7 +62,7 @@ private: bool Prelock_error_handler::handle_error(uint sql_errno, const char * /* message */, - MYSQL_ERROR::enum_warning_level /* level */, + MYSQL_ERROR::enum_warning_level level, THD * /* thd */) { if (sql_errno == ER_NO_SUCH_TABLE) @@ -71,7 +71,8 @@ Prelock_error_handler::handle_error(uint sql_errno, return TRUE; } - m_unhandled_errors++; + if (level == MYSQL_ERROR::WARN_LEVEL_ERROR) + m_unhandled_errors++; return FALSE; } diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 1ed0fbf5ece..6344ac45574 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -664,7 +664,7 @@ THD::THD() Open_tables_state(refresh_version), rli_fake(0), lock_id(&main_lock_id), in_sub_stmt(0), - sql_log_bin_toplevel(false), + sql_log_bin_toplevel(false), log_all_errors(0), binlog_table_maps(0), binlog_flags(0UL), table_map_for_update(0), arg_of_last_insert_id_function(FALSE), @@ -2599,26 +2599,32 @@ bool select_max_min_finder_subselect::cmp_real() { Item *maxmin= ((Item_singlerow_subselect *)item)->element_index(0); double val1= cache->val_real(), val2= maxmin->val_real(); + + /* Ignore NULLs for ANY and keep them for ALL subqueries */ + if (cache->null_value) + return (is_all && !maxmin->null_value) || (!is_all && maxmin->null_value); + if (maxmin->null_value) + return !is_all; + if (fmax) - return (cache->null_value && !maxmin->null_value) || - (!cache->null_value && !maxmin->null_value && - val1 > val2); - return (maxmin->null_value && !cache->null_value) || - (!cache->null_value && !maxmin->null_value && - val1 < val2); + return(val1 > val2); + return (val1 < val2); } bool select_max_min_finder_subselect::cmp_int() { Item *maxmin= ((Item_singlerow_subselect *)item)->element_index(0); longlong val1= cache->val_int(), val2= maxmin->val_int(); + + /* Ignore NULLs for ANY and keep them for ALL subqueries */ + if (cache->null_value) + return (is_all && !maxmin->null_value) || (!is_all && maxmin->null_value); + if (maxmin->null_value) + return !is_all; + if (fmax) - return (cache->null_value && !maxmin->null_value) || - (!cache->null_value && !maxmin->null_value && - val1 > val2); - return (maxmin->null_value && !cache->null_value) || - (!cache->null_value && !maxmin->null_value && - val1 < val2); + return(val1 > val2); + return (val1 < val2); } bool select_max_min_finder_subselect::cmp_decimal() @@ -2626,13 +2632,16 @@ bool select_max_min_finder_subselect::cmp_decimal() Item *maxmin= ((Item_singlerow_subselect *)item)->element_index(0); my_decimal cval, *cvalue= cache->val_decimal(&cval); my_decimal mval, *mvalue= maxmin->val_decimal(&mval); + + /* Ignore NULLs for ANY and keep them for ALL subqueries */ + if (cache->null_value) + return (is_all && !maxmin->null_value) || (!is_all && maxmin->null_value); + if (maxmin->null_value) + return !is_all; + if (fmax) - return (cache->null_value && !maxmin->null_value) || - (!cache->null_value && !maxmin->null_value && - my_decimal_cmp(cvalue, mvalue) > 0) ; - return (maxmin->null_value && !cache->null_value) || - (!cache->null_value && !maxmin->null_value && - my_decimal_cmp(cvalue,mvalue) < 0); + return (my_decimal_cmp(cvalue, mvalue) > 0) ; + return (my_decimal_cmp(cvalue,mvalue) < 0); } bool select_max_min_finder_subselect::cmp_str() @@ -2645,13 +2654,16 @@ bool select_max_min_finder_subselect::cmp_str() */ val1= cache->val_str(&buf1); val2= maxmin->val_str(&buf1); + + /* Ignore NULLs for ANY and keep them for ALL subqueries */ + if (cache->null_value) + return (is_all && !maxmin->null_value) || (!is_all && maxmin->null_value); + if (maxmin->null_value) + return !is_all; + if (fmax) - return (cache->null_value && !maxmin->null_value) || - (!cache->null_value && !maxmin->null_value && - sortcmp(val1, val2, cache->collation.collation) > 0) ; - return (maxmin->null_value && !cache->null_value) || - (!cache->null_value && !maxmin->null_value && - sortcmp(val1, val2, cache->collation.collation) < 0); + return (sortcmp(val1, val2, cache->collation.collation) > 0) ; + return (sortcmp(val1, val2, cache->collation.collation) < 0); } int select_exists_subselect::send_data(List<Item> &items) diff --git a/sql/sql_class.h b/sql/sql_class.h index c97cc34e166..b567ed78eb7 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1596,6 +1596,8 @@ public: bool sql_log_bin_toplevel; /* True when opt_userstat_running is set at start of query */ bool userstat_running; + /* True if we want to log all errors */ + bool log_all_errors; /* container for handler's private per-connection data */ Ha_data ha_data[MAX_HA]; @@ -3227,9 +3229,11 @@ class select_max_min_finder_subselect :public select_subselect Item_cache *cache; bool (select_max_min_finder_subselect::*op)(); bool fmax; + bool is_all; public: - select_max_min_finder_subselect(Item_subselect *item_arg, bool mx) - :select_subselect(item_arg), cache(0), fmax(mx) + select_max_min_finder_subselect(Item_subselect *item_arg, bool mx, + bool all) + :select_subselect(item_arg), cache(0), fmax(mx), is_all(all) {} void cleanup(); int send_data(List<Item> &items); diff --git a/sql/table.cc b/sql/table.cc index ca958f6257a..d54135b5620 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -989,14 +989,13 @@ static int open_binary_frm(THD *thd, TABLE_SHARE *share, uchar *head, #endif next_chunk+= 5 + partition_info_len; } - if (share->mysql_version >= 50110) + if (share->mysql_version >= 50110 && next_chunk < buff_end) { /* New auto_partitioned indicator introduced in 5.1.11 */ #ifdef WITH_PARTITION_STORAGE_ENGINE share->auto_partitioned= *next_chunk; #endif next_chunk++; - DBUG_ASSERT(next_chunk <= buff_end); } keyinfo= share->key_info; for (i= 0; i < keys; i++, keyinfo++) diff --git a/storage/maria/ha_maria.cc b/storage/maria/ha_maria.cc index f378aae6263..2d469ff87c2 100644 --- a/storage/maria/ha_maria.cc +++ b/storage/maria/ha_maria.cc @@ -150,10 +150,16 @@ static MYSQL_SYSVAR_ULONG(block_size, maria_block_size, static MYSQL_SYSVAR_ULONG(checkpoint_interval, checkpoint_interval, PLUGIN_VAR_RQCMDARG, - "Interval between automatic checkpoints, in seconds; 0 means" + "Interval between tries to do an automatic checkpoints. In seconds; 0 means" " 'no automatic checkpoints' which makes sense only for testing.", NULL, update_checkpoint_interval, 30, 0, UINT_MAX, 1); +static MYSQL_SYSVAR_ULONG(checkpoint_log_activity, maria_checkpoint_min_log_activity, + PLUGIN_VAR_RQCMDARG, + "Number of bytes that the transaction log has to grow between checkpoints before a new " + "checkpoint is written to the log.", + NULL, NULL, 1024*1024, 0, UINT_MAX, 1); + static MYSQL_SYSVAR_ULONG(force_start_after_recovery_failures, force_start_after_recovery_failures, /* @@ -304,7 +310,7 @@ static void _ma_check_print_msg(HA_CHECK *param, const char *msg_type, if (!thd->vio_ok()) { - sql_print_error(fmt, args); + sql_print_error("%s.%s: %s", param->db_name, param->table_name, msgbuf); return; } @@ -312,6 +318,8 @@ static void _ma_check_print_msg(HA_CHECK *param, const char *msg_type, (T_CREATE_MISSING_KEYS | T_SAFE_REPAIR | T_AUTO_REPAIR)) { my_message(ER_NOT_KEYFILE, msgbuf, MYF(MY_WME)); + if (thd->variables.log_warnings > 2) + sql_print_error("%s.%s: %s", param->db_name, param->table_name, msgbuf); return; } length= (uint) (strxmov(name, param->db_name, ".", param->table_name, @@ -330,8 +338,11 @@ static void _ma_check_print_msg(HA_CHECK *param, const char *msg_type, protocol->store(msg_type, system_charset_info); protocol->store(msgbuf, msg_length, system_charset_info); if (protocol->write()) - sql_print_error("Failed on my_net_write, writing to stderr instead: %s\n", - msgbuf); + sql_print_error("Failed on my_net_write, writing to stderr instead: %s.%s: %s\n", + param->db_name, param->table_name, msgbuf); + else if (thd->variables.log_warnings > 2) + sql_print_error("%s.%s: %s", param->db_name, param->table_name, msgbuf); + return; } @@ -2184,13 +2195,17 @@ bool ha_maria::check_and_repair(THD *thd) if (crashed) { + bool save_log_all_errors; sql_print_warning("Recovering table: '%s'", table->s->path.str); + save_log_all_errors= thd->log_all_errors; + thd->log_all_errors|= (thd->variables.log_warnings > 2); check_opt.flags= ((maria_recover_options & HA_RECOVER_BACKUP ? T_BACKUP_DATA : 0) | (maria_recover_options & HA_RECOVER_FORCE ? 0 : T_SAFE_REPAIR) | T_AUTO_REPAIR); if (repair(thd, &check_opt)) error= 1; + thd->log_all_errors= save_log_all_errors; } pthread_mutex_lock(&LOCK_thread_count); thd->query_string= old_query; @@ -3556,6 +3571,7 @@ my_bool ha_maria::register_query_cache_table(THD *thd, char *table_name, struct st_mysql_sys_var* system_variables[]= { MYSQL_SYSVAR(block_size), MYSQL_SYSVAR(checkpoint_interval), + MYSQL_SYSVAR(checkpoint_log_activity), MYSQL_SYSVAR(force_start_after_recovery_failures), MYSQL_SYSVAR(group_commit), MYSQL_SYSVAR(group_commit_interval), @@ -3589,6 +3605,7 @@ static void update_checkpoint_interval(MYSQL_THD thd, ma_checkpoint_init(*(ulong *)var_ptr= (ulong)(*(long *)save)); } + /** @brief Updates group commit mode */ diff --git a/storage/maria/ma_check.c b/storage/maria/ma_check.c index 221a96c8285..72c01fc9995 100644 --- a/storage/maria/ma_check.c +++ b/storage/maria/ma_check.c @@ -2344,7 +2344,7 @@ static int initialize_variables_for_repair(HA_CHECK *param, return 1; } - /* Allow us to restore state and check how state changed */ + /* Make a copy to allow us to restore state and check how state changed */ memcpy(org_share, share, sizeof(*share)); /* Repair code relies on share->state.state so we have to update it here */ @@ -2364,6 +2364,14 @@ static int initialize_variables_for_repair(HA_CHECK *param, param->testflag&= ~T_QUICK; param->org_key_map= share->state.key_map; + /* + Clear check variables set by repair. This is needed to allow one to run + several repair's in a row with same param + */ + param->retry_repair= 0; + param->warning_printed= 0; + param->error_printed= 0; + sort_param->sort_info= sort_info; sort_param->fix_datafile= ! rep_quick; sort_param->calc_checksum= test(param->testflag & T_CALC_CHECKSUM); diff --git a/storage/maria/ma_checkpoint.c b/storage/maria/ma_checkpoint.c index 19ec7c9c1d4..ae705fc2c0c 100644 --- a/storage/maria/ma_checkpoint.c +++ b/storage/maria/ma_checkpoint.c @@ -533,8 +533,9 @@ filter_flush_file_evenly(enum pagecache_page_type type, risk could be that while a checkpoint happens no LRD flushing happens. */ -static uint maria_checkpoint_min_activity= 2*1024*1024; - +static ulong maria_checkpoint_min_cache_activity= 10*1024*1024; +/* Set in ha_maria.cc */ +ulong maria_checkpoint_min_log_activity= 1*1024*1024; pthread_handler_t ma_checkpoint_background(void *arg) { @@ -578,53 +579,61 @@ pthread_handler_t ma_checkpoint_background(void *arg) switch (sleeps % interval) { case 0: + { /* If checkpoints are disabled, wait 1 second and try again */ if (maria_checkpoint_disabled) { sleep_time= 1; break; } - /* - With background flushing evenly distributed over the time - between two checkpoints, we should have only little flushing to do - in the checkpoint. - */ - /* - No checkpoint if little work of interest for recovery was done - since last checkpoint. Such work includes log writing (lengthens - recovery, checkpoint would shorten it), page flushing (checkpoint - would decrease the amount of read pages in recovery). - In case of one short statement per minute (very low load), we don't - want to checkpoint every minute, hence the positive - maria_checkpoint_min_activity. - */ - - if (((translog_get_horizon() - log_horizon_at_last_checkpoint) + - (maria_pagecache->global_cache_write - - pagecache_flushes_at_last_checkpoint) * - maria_pagecache->block_size) < maria_checkpoint_min_activity) { - /* don't take checkpoint, so don't know what to flush */ - pages_to_flush_before_next_checkpoint= 0; - sleep_time= interval; - break; + TRANSLOG_ADDRESS horizon= translog_get_horizon(); + + /* + With background flushing evenly distributed over the time + between two checkpoints, we should have only little flushing to do + in the checkpoint. + */ + /* + No checkpoint if little work of interest for recovery was done + since last checkpoint. Such work includes log writing (lengthens + recovery, checkpoint would shorten it), page flushing (checkpoint + would decrease the amount of read pages in recovery). + In case of one short statement per minute (very low load), we don't + want to checkpoint every minute, hence the positive + maria_checkpoint_min_activity. + */ + if (horizon != log_horizon_at_last_checkpoint && + (ulonglong) (horizon - log_horizon_at_last_checkpoint) <= + maria_checkpoint_min_log_activity && + ((ulonglong) (maria_pagecache->global_cache_write - + pagecache_flushes_at_last_checkpoint) * + maria_pagecache->block_size) <= + maria_checkpoint_min_cache_activity) + { + /* don't take checkpoint, so don't know what to flush */ + pages_to_flush_before_next_checkpoint= 0; + sleep_time= interval; + break; + } + sleep_time= 1; + ma_checkpoint_execute(CHECKPOINT_MEDIUM, TRUE); + /* + Snapshot this kind of "state" of the engine. Note that the value below + is possibly greater than last_checkpoint_lsn. + */ + log_horizon_at_last_checkpoint= translog_get_horizon(); + pagecache_flushes_at_last_checkpoint= + maria_pagecache->global_cache_write; + /* + If the checkpoint above succeeded it has set d|kfiles and + d|kfiles_end. If is has failed, it has set + pages_to_flush_before_next_checkpoint to 0 so we will skip flushing + and sleep until the next checkpoint. + */ } - sleep_time= 1; - ma_checkpoint_execute(CHECKPOINT_MEDIUM, TRUE); - /* - Snapshot this kind of "state" of the engine. Note that the value below - is possibly greater than last_checkpoint_lsn. - */ - log_horizon_at_last_checkpoint= translog_get_horizon(); - pagecache_flushes_at_last_checkpoint= - maria_pagecache->global_cache_write; - /* - If the checkpoint above succeeded it has set d|kfiles and - d|kfiles_end. If is has failed, it has set - pages_to_flush_before_next_checkpoint to 0 so we will skip flushing - and sleep until the next checkpoint. - */ break; + } case 1: /* set up parameters for background page flushing */ filter_param.up_to_lsn= last_checkpoint_lsn; diff --git a/storage/maria/ma_recovery.c b/storage/maria/ma_recovery.c index d4c1d91325d..33112a1ec5e 100644 --- a/storage/maria/ma_recovery.c +++ b/storage/maria/ma_recovery.c @@ -3215,9 +3215,11 @@ static LSN parse_checkpoint_record(LSN lsn) tprint(tracef, "Loading data from checkpoint record at LSN (%lu,0x%lx)\n", LSN_IN_PARTS(lsn)); - if ((len= translog_read_record_header(lsn, &rec)) == RECHEADER_READ_ERROR) + if ((len= translog_read_record_header(lsn, &rec)) == RECHEADER_READ_ERROR || + rec.type != LOGREC_CHECKPOINT) { - tprint(tracef, "Cannot find checkpoint record where it should be\n"); + eprint(tracef, "Cannot find checkpoint record at LSN (%lu,0x%lx)", + LSN_IN_PARTS(lsn)); return LSN_ERROR; } diff --git a/storage/maria/maria_def.h b/storage/maria/maria_def.h index 6932ae0d4f1..392e0f8d95c 100644 --- a/storage/maria/maria_def.h +++ b/storage/maria/maria_def.h @@ -828,6 +828,7 @@ extern uchar maria_zero_string[]; extern my_bool maria_inited, maria_in_ha_maria, maria_recovery_changed_data; extern my_bool maria_recovery_verbose, maria_checkpoint_disabled; extern my_bool maria_assert_if_crashed_table; +extern ulong maria_checkpoint_min_log_activity; extern HASH maria_stored_state; extern int (*maria_create_trn_hook)(MARIA_HA *); extern my_bool (*ma_killed)(MARIA_HA *); diff --git a/storage/maria/trnman.c b/storage/maria/trnman.c index 05330baed76..36880a59b51 100644 --- a/storage/maria/trnman.c +++ b/storage/maria/trnman.c @@ -179,6 +179,7 @@ int trnman_init(TrID initial_trid) trnman_allocated_transactions= 0; /* This is needed for recovery and repair */ dummy_transaction_object.min_read_from= ~(TrID) 0; + dummy_transaction_object.first_undo_lsn= TRANSACTION_LOGGED_LONG_ID; pool= 0; global_trid_generator= initial_trid; diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc index ac4386d4012..97815205586 100644 --- a/storage/myisam/ha_myisam.cc +++ b/storage/myisam/ha_myisam.cc @@ -91,14 +91,16 @@ static void mi_check_print_msg(HA_CHECK *param, const char* msg_type, if (!thd->vio_ok()) { - sql_print_error("%s", msgbuf); + sql_print_error("%s.%s: %s", param->db_name, param->table_name, msgbuf); return; } if (param->testflag & (T_CREATE_MISSING_KEYS | T_SAFE_REPAIR | T_AUTO_REPAIR)) { - my_message(ER_NOT_KEYFILE,msgbuf,MYF(MY_WME)); + my_message(ER_NOT_KEYFILE, msgbuf, MYF(MY_WME)); + if (thd->variables.log_warnings > 2 && ! thd->log_all_errors) + sql_print_error("%s.%s: %s", param->db_name, param->table_name, msgbuf); return; } length=(uint) (strxmov(name, param->db_name,".",param->table_name,NullS) - @@ -124,7 +126,7 @@ static void mi_check_print_msg(HA_CHECK *param, const char* msg_type, sql_print_error("Failed on my_net_write, writing to stderr instead: %s\n", msgbuf); else if (thd->variables.log_warnings > 2) - sql_print_error("%s", msgbuf); + sql_print_error("%s.%s: %s", param->db_name, param->table_name, msgbuf); #ifdef THREAD if (param->need_print_msg_lock) @@ -1699,7 +1701,10 @@ bool ha_myisam::check_and_repair(THD *thd) if ((marked_crashed= mi_is_crashed(file)) || check(thd, &check_opt)) { + bool save_log_all_errors; sql_print_warning("Recovering table: '%s'",table->s->path.str); + save_log_all_errors= thd->log_all_errors; + thd->log_all_errors|= (thd->variables.log_warnings > 2); if (myisam_recover_options & HA_RECOVER_FULL_BACKUP) { char buff[MY_BACKUP_NAME_EXTRA_LENGTH+1]; @@ -1717,6 +1722,7 @@ bool ha_myisam::check_and_repair(THD *thd) T_AUTO_REPAIR); if (repair(thd, &check_opt)) error=1; + thd->log_all_errors= save_log_all_errors; } thd->set_query(old_query, old_query_length); DBUG_RETURN(error); diff --git a/storage/myisam/mi_check.c b/storage/myisam/mi_check.c index 91f2cf11fee..d97ae41ecc7 100644 --- a/storage/myisam/mi_check.c +++ b/storage/myisam/mi_check.c @@ -1539,6 +1539,9 @@ int mi_repair(HA_CHECK *param, register MI_INFO *info, got_error=1; new_file= -1; sort_param.sort_info=&sort_info; + param->retry_repair= 0; + param->warning_printed= 0; + param->error_printed= 0; if (!(param->testflag & T_SILENT)) { @@ -1683,7 +1686,7 @@ int mi_repair(HA_CHECK *param, register MI_INFO *info, if (rep_quick && del+sort_info.dupp != info->state->del) { mi_check_print_error(param,"Couldn't fix table with quick recovery: Found wrong number of deleted records"); - mi_check_print_error(param,"Run recovery again without -q"); + mi_check_print_error(param,"Run recovery again without --quick"); got_error=1; param->retry_repair=1; param->testflag|=T_RETRY_WITHOUT_QUICK; @@ -1914,7 +1917,7 @@ int flush_blocks(HA_CHECK *param, KEY_CACHE *key_cache, File file, { if (flush_key_blocks(key_cache, file, dirty_part_map, FLUSH_RELEASE)) { - mi_check_print_error(param,"%d when trying to write bufferts",my_errno); + mi_check_print_error(param,"%d when trying to write buffers",my_errno); return(1); } if (!param->using_global_keycache) @@ -2228,7 +2231,7 @@ int mi_repair_by_sort(HA_CHECK *param, register MI_INFO *info, MYISAM_SHARE *share=info->s; HA_KEYSEG *keyseg; ulong *rec_per_key_part; - char llbuff[22]; + char llbuff[22], llbuff2[22]; MI_SORT_INFO sort_info; ulonglong UNINIT_VAR(key_map); DBUG_ENTER("mi_repair_by_sort"); @@ -2244,12 +2247,16 @@ int mi_repair_by_sort(HA_CHECK *param, register MI_INFO *info, printf("Data records: %s\n", llstr(start_records,llbuff)); } param->testflag|=T_REP; /* for easy checking */ + param->retry_repair= 0; + param->warning_printed= 0; + param->error_printed= 0; if (info->s->options & (HA_OPTION_CHECKSUM | HA_OPTION_COMPRESS_RECORD)) param->testflag|=T_CALC_CHECKSUM; bzero((char*)&sort_info,sizeof(sort_info)); bzero((char *)&sort_param, sizeof(sort_param)); + if (!(sort_info.key_block= alloc_key_blocks(param, (uint) param->sort_key_blocks, @@ -2261,7 +2268,7 @@ int mi_repair_by_sort(HA_CHECK *param, register MI_INFO *info, init_io_cache(&info->rec_cache,info->dfile, (uint) param->write_buffer_length, WRITE_CACHE,new_header_length,1, - MYF(MY_WME | MY_WAIT_IF_FULL) & param->myf_rw))) + MYF((param->myf_rw & MY_WAIT_IF_FULL) | MY_WME)))) goto err; sort_info.key_block_end=sort_info.key_block+param->sort_key_blocks; info->opt_flag|=WRITE_CACHE_USED; @@ -2433,7 +2440,10 @@ int mi_repair_by_sort(HA_CHECK *param, register MI_INFO *info, (my_bool) (!(param->testflag & T_VERBOSE)), (uint) param->sort_buffer_length)) { - param->retry_repair=1; + param->retry_repair= 1; + if (! param->error_printed) + mi_check_print_error(param, "Couldn't fix table with create_index_by_sort(). Error: %d", + my_errno); goto err; } /* No need to calculate checksum again. */ @@ -2462,7 +2472,10 @@ int mi_repair_by_sort(HA_CHECK *param, register MI_INFO *info, /* Don't repair if we loosed more than one row */ if (info->state->records+1 < start_records) { - info->state->records=start_records; + mi_check_print_error(param, + "Couldn't fix table as SAFE_REPAIR was requested and we would loose too many rows. %s -> %s", + llstr(start_records, llbuff), llstr(info->state->records, llbuff2)); + info->state->records= start_records; goto err; } } @@ -2492,7 +2505,7 @@ int mi_repair_by_sort(HA_CHECK *param, register MI_INFO *info, if (rep_quick && del+sort_info.dupp != info->state->del) { mi_check_print_error(param,"Couldn't fix table with quick recovery: Found wrong number of deleted records"); - mi_check_print_error(param,"Run recovery again without -q"); + mi_check_print_error(param,"Run recovery again without --quick"); got_error=1; param->retry_repair=1; param->testflag|=T_RETRY_WITHOUT_QUICK; @@ -2664,6 +2677,9 @@ int mi_repair_parallel(HA_CHECK *param, register MI_INFO *info, printf("Data records: %s\n", llstr(start_records,llbuff)); } param->testflag|=T_REP; /* for easy checking */ + param->retry_repair= 0; + param->warning_printed= 0; + param->error_printed= 0; if (info->s->options & (HA_OPTION_CHECKSUM | HA_OPTION_COMPRESS_RECORD)) param->testflag|=T_CALC_CHECKSUM; diff --git a/storage/myisam/sort.c b/storage/myisam/sort.c index 2b6d5167c93..db589759128 100644 --- a/storage/myisam/sort.c +++ b/storage/myisam/sort.c @@ -151,6 +151,7 @@ int _create_index_by_sort(MI_SORT_PARAM *info,my_bool no_messages, { mi_check_print_error(info->sort_info->param, "myisam_sort_buffer_size is too small"); + my_errno= ENOMEM; goto err; } } @@ -175,7 +176,8 @@ int _create_index_by_sort(MI_SORT_PARAM *info,my_bool no_messages, if (memavl < MIN_SORT_BUFFER) { mi_check_print_error(info->sort_info->param,"MyISAM sort buffer too small"); /* purecov: tested */ - goto err; /* purecov: tested */ + my_errno= ENOMEM; /* purecov: tested */ + goto err; /* purecov: tested */ } (*info->lock_in_memory)(info->sort_info->param);/* Everything is allocated */ |