diff options
| author | Marko Mäkelä <marko.makela@mariadb.com> | 2019-08-31 06:53:45 +0300 |
|---|---|---|
| committer | Marko Mäkelä <marko.makela@mariadb.com> | 2019-08-31 06:53:45 +0300 |
| commit | db4a27ab738c3ab43173368117583402f995c4f1 (patch) | |
| tree | 01a367ede38cc8ede00038a904c96b76e570de5f | |
| parent | d1ef02e9592b744d6c8dbca9231152f0a58d4384 (diff) | |
| parent | 396da1a70548f80ecf781cf352b0d4a5740c54f6 (diff) | |
| download | mariadb-git-db4a27ab738c3ab43173368117583402f995c4f1.tar.gz | |
Merge 10.3 into 10.4
103 files changed, 2673 insertions, 631 deletions
diff --git a/cmake/build_configurations/mysql_release.cmake b/cmake/build_configurations/mysql_release.cmake index aacb3884a8e..37a6c459260 100644 --- a/cmake/build_configurations/mysql_release.cmake +++ b/cmake/build_configurations/mysql_release.cmake @@ -124,6 +124,7 @@ ENDIF() IF(UNIX) SET(WITH_EXTRA_CHARSETS all CACHE STRING "") + SET(PLUGIN_AUTH_PAM YES) IF(CMAKE_SYSTEM_NAME STREQUAL "Linux") IF(NOT IGNORE_AIO_CHECK) diff --git a/cmake/cpack_rpm.cmake b/cmake/cpack_rpm.cmake index c17cb1ca19a..4c8835f8c08 100644 --- a/cmake/cpack_rpm.cmake +++ b/cmake/cpack_rpm.cmake @@ -37,7 +37,8 @@ IF(CMAKE_VERSION VERSION_LESS "3.6.0") SET(CPACK_PACKAGE_FILE_NAME "${CPACK_RPM_PACKAGE_NAME}-${VERSION}-${RPM}-${CMAKE_SYSTEM_PROCESSOR}") ELSE() SET(CPACK_RPM_FILE_NAME "RPM-DEFAULT") - SET(CPACK_RPM_DEBUGINFO_PACKAGE ON CACHE INTERNAL "") + OPTION(CPACK_RPM_DEBUGINFO_PACKAGE "" ON) + MARK_AS_ADVANCED(CPACK_RPM_DEBUGINFO_PACKAGE) ENDIF() SET(CPACK_RPM_PACKAGE_RELEASE "1%{?dist}") diff --git a/include/my_dbug.h b/include/my_dbug.h index 4b69787c2fe..bce33285788 100644 --- a/include/my_dbug.h +++ b/include/my_dbug.h @@ -1,5 +1,5 @@ /* Copyright (c) 2000, 2010, Oracle and/or its affiliates. - Copyright (C) 2000, 2017, MariaDB Corporation Ab + Copyright (C) 2000, 2019, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -35,6 +35,7 @@ struct _db_stack_frame_ { }; struct _db_code_state_; +extern MYSQL_PLUGIN_IMPORT my_bool my_assert; extern my_bool _dbug_on_; extern my_bool _db_keyword_(struct _db_code_state_ *, const char *, int); extern int _db_explain_(struct _db_code_state_ *cs, char *buf, size_t len); @@ -103,7 +104,10 @@ extern int (*dbug_sanity)(void); #define DBUG_END() _db_end_ () #define DBUG_LOCK_FILE _db_lock_file_() #define DBUG_UNLOCK_FILE _db_unlock_file_() -#define DBUG_ASSERT(A) do { if (!(A)) { _db_flush_(); assert(A); }} while (0) +#define DBUG_ASSERT(A) do { if (!(A)) { _db_flush_(); \ + if (my_assert) assert(A); \ + else fprintf(stderr, "%s:%d: assert: %s\n", __FILE__, __LINE__, #A); \ +}} while (0) #define DBUG_SLOW_ASSERT(A) DBUG_ASSERT(A) #define DBUG_ASSERT_EXISTS #define DBUG_EXPLAIN(buf,len) _db_explain_(0, (buf),(len)) diff --git a/include/my_global.h b/include/my_global.h index 248320e301f..cc513cc1be0 100644 --- a/include/my_global.h +++ b/include/my_global.h @@ -1,6 +1,6 @@ /* Copyright (c) 2001, 2013, Oracle and/or its affiliates. - Copyright (c) 2009, 2017, MariaDB Corporation + Copyright (c) 2009, 2019, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -1037,6 +1037,19 @@ typedef ulong myf; /* Type of MyFlags in my_funcs */ #define reg16 register #endif +/* + MYSQL_PLUGIN_IMPORT macro is used to export mysqld data + (i.e variables) for usage in storage engine loadable plugins. + Outside of Windows, it is dummy. +*/ +#ifndef MYSQL_PLUGIN_IMPORT +#if (defined(_WIN32) && defined(MYSQL_DYNAMIC_PLUGIN)) +#define MYSQL_PLUGIN_IMPORT __declspec(dllimport) +#else +#define MYSQL_PLUGIN_IMPORT +#endif +#endif + #include <my_dbug.h> /* Some helper macros */ @@ -1163,19 +1176,6 @@ typedef struct { const char *dli_fname, dli_fbase; } Dl_info; #endif #endif /* !defined(__func__) */ -/* - MYSQL_PLUGIN_IMPORT macro is used to export mysqld data - (i.e variables) for usage in storage engine loadable plugins. - Outside of Windows, it is dummy. -*/ -#ifndef MYSQL_PLUGIN_IMPORT -#if (defined(_WIN32) && defined(MYSQL_DYNAMIC_PLUGIN)) -#define MYSQL_PLUGIN_IMPORT __declspec(dllimport) -#else -#define MYSQL_PLUGIN_IMPORT -#endif -#endif - /* Defines that are unique to the embedded version of MySQL */ #ifdef EMBEDDED_LIBRARY diff --git a/include/my_sys.h b/include/my_sys.h index eb6129ea238..76f425375bc 100644 --- a/include/my_sys.h +++ b/include/my_sys.h @@ -1,5 +1,5 @@ /* Copyright (c) 2000, 2013, Oracle and/or its affiliates. - Copyright (c) 2010, 2017, MariaDB Corporation. + Copyright (c) 2010, 2019, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -266,6 +266,7 @@ extern ulong my_sync_count; extern uint mysys_usage_id; extern int32 my_file_opened; extern my_bool my_init_done, my_thr_key_mysys_exists; +extern MYSQL_PLUGIN_IMPORT my_bool my_assert; extern my_bool my_assert_on_error; extern myf my_global_flags; /* Set to MY_WME for more error messages */ /* Point to current my_message() */ diff --git a/mysql-test/lib/My/Handles.pm b/mysql-test/lib/My/Handles.pm index 3c9513fec8f..c07e2edb09e 100644 --- a/mysql-test/lib/My/Handles.pm +++ b/mysql-test/lib/My/Handles.pm @@ -32,8 +32,8 @@ if (IS_WINDOWS){ my $list= `handle.exe -? -accepteula 2>&1`; foreach my $line (split('\n', $list)) { - $handle_exe= "$1.$2" - if ($line =~ /Handle v([0-9]*)\.([0-9]*)/); + $handle_exe= "$2.$3" + if ($line =~ /(Nth|H)andle v([0-9]*)\.([0-9]*)/); } if ($handle_exe){ print "Found handle.exe version $handle_exe\n"; diff --git a/mysql-test/main/analyze_stmt_privileges2.result b/mysql-test/main/analyze_stmt_privileges2.result index cf38810b598..f269aaf540b 100644 --- a/mysql-test/main/analyze_stmt_privileges2.result +++ b/mysql-test/main/analyze_stmt_privileges2.result @@ -376,13 +376,13 @@ SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM t1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 0.00 100.00 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 #------------------------------------------------------------------------ # I/R/U/D/S on the inner view @@ -491,14 +491,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -598,14 +598,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 12 12.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 12 12.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #======================================================================== # Test: Grant INSERT on the table @@ -1591,14 +1591,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything: SELECT access to the column `a` @@ -1708,14 +1708,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #======================================================================== # Test: Grant SELECT, INSERT, UPDATE, DELETE on the table @@ -1940,14 +1940,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 4.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -2048,14 +2048,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 8.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL ######################################################################### # Inner view permission tests @@ -2697,14 +2697,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 14 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 14 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 14 14.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 14 14.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -2804,14 +2804,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 18 18.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 18 18.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #======================================================================== # Test: Grant INSERT on the inner view @@ -3987,14 +3987,14 @@ SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 35 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 35 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v1 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 35 35.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 35 35.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #------------------------------------------------------------------------ # I/R/U/D/S on the outer view # Expectation: Can run everything @@ -4094,14 +4094,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 39 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL ######################################################################### # Outer view permission tests @@ -4614,14 +4614,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 39 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 39 39.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL #======================================================================== # Test: Grant INSERT on the outer view @@ -5221,14 +5221,14 @@ SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); a b EXPLAIN SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 44 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 44 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 ANALYZE SELECT * FROM v2 WHERE a IN ( SELECT a FROM t2 ); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 3.00 100.00 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 44 44.00 100.00 0.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 44 44.00 100.00 0.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 NULL 100.00 NULL +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 NULL 100.00 NULL disconnect con1; connection default; DROP USER 'privtest'@localhost; diff --git a/mysql-test/main/constraints.result b/mysql-test/main/constraints.result index 0b7577dd3ac..24f8417d313 100644 --- a/mysql-test/main/constraints.result +++ b/mysql-test/main/constraints.result @@ -129,6 +129,61 @@ t CREATE TABLE `t` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP table test.t; SET @@SQL_MODE=@OLD_SQL_MODE; +# +# MDEV-16932 - ASAN heap-use-after-free in my_charlen_utf8 / +# my_well_formed_char_length_utf8 on 2nd execution of SP with +# ALTER trying to add bad CHECK +# +CREATE TABLE t1 (a INT); +CREATE PROCEDURE sp() ALTER TABLE t1 ADD CONSTRAINT CHECK (b > 0); +CALL sp; +ERROR 42S22: Unknown column 'b' in 'CHECK' +CALL sp; +ERROR 42S22: Unknown column 'b' in 'CHECK' +CALL sp; +ERROR 42S22: Unknown column 'b' in 'CHECK' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 add column b int; +CALL sp; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + CONSTRAINT `CONSTRAINT_1` CHECK (`b` > 0) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +CALL sp; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + CONSTRAINT `CONSTRAINT_1` CHECK (`b` > 0), + CONSTRAINT `CONSTRAINT_2` CHECK (`b` > 0) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP PROCEDURE sp; +DROP TABLE t1; +CREATE TABLE t1 (a INT); +CREATE PROCEDURE sp() ALTER TABLE t1 ADD CONSTRAINT CHECK (b > 0); +CALL sp; +ERROR 42S22: Unknown column 'b' in 'CHECK' +alter table t1 add column b int, add constraint check (b < 10); +CALL sp; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + CONSTRAINT `CONSTRAINT_1` CHECK (`b` < 10), + CONSTRAINT `CONSTRAINT_2` CHECK (`b` > 0) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP PROCEDURE sp; +DROP TABLE t1; +# End of 10.2 tests create table t1 (a int check (a>10)) select 100 as 'a'; show create table t1; Table Create Table diff --git a/mysql-test/main/constraints.test b/mysql-test/main/constraints.test index d7a5b41d708..2f4dadcee9d 100644 --- a/mysql-test/main/constraints.test +++ b/mysql-test/main/constraints.test @@ -116,6 +116,43 @@ SHOW CREATE TABLE t; DROP table test.t; SET @@SQL_MODE=@OLD_SQL_MODE; +--echo # +--echo # MDEV-16932 - ASAN heap-use-after-free in my_charlen_utf8 / +--echo # my_well_formed_char_length_utf8 on 2nd execution of SP with +--echo # ALTER trying to add bad CHECK +--echo # + +CREATE TABLE t1 (a INT); +CREATE PROCEDURE sp() ALTER TABLE t1 ADD CONSTRAINT CHECK (b > 0); +--error ER_BAD_FIELD_ERROR +CALL sp; +--error ER_BAD_FIELD_ERROR +CALL sp; +--error ER_BAD_FIELD_ERROR +CALL sp; +show create table t1; +alter table t1 add column b int; +CALL sp; +show create table t1; +CALL sp; +show create table t1; +# Cleanup +DROP PROCEDURE sp; +DROP TABLE t1; + +CREATE TABLE t1 (a INT); +CREATE PROCEDURE sp() ALTER TABLE t1 ADD CONSTRAINT CHECK (b > 0); +--error ER_BAD_FIELD_ERROR +CALL sp; +alter table t1 add column b int, add constraint check (b < 10); +CALL sp; +show create table t1; +# Cleanup +DROP PROCEDURE sp; +DROP TABLE t1; + +--echo # End of 10.2 tests + # # Check that we don't lose constraints as part of CREATE ... SELECT # diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index 2556fd4b06b..12a7e2ea789 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -1159,9 +1159,9 @@ with cte as union (select a from t1 where a < 2); a -7 -5 4 +5 +7 1 prepare stmt from "with cte as (select a from t1 where a between 4 and 7 group by a) @@ -1170,15 +1170,15 @@ union (select a from t1 where a < 2)"; execute stmt; a -7 -5 4 +5 +7 1 execute stmt; a -7 -5 4 +5 +7 1 deallocate prepare stmt; with cte as @@ -1188,9 +1188,9 @@ union (select a from cte where exists( select a from t1 where cte.a=t1.a )); a 1 -7 -5 4 +5 +7 prepare stmt from "with cte as (select a from t1 where a between 4 and 7 group by a) (select a from t1 where a < 2) @@ -1199,15 +1199,15 @@ union execute stmt; a 1 -7 -5 4 +5 +7 execute stmt; a 1 -7 -5 4 +5 +7 deallocate prepare stmt; with cte as (select a from t1 where a between 4 and 7) diff --git a/mysql-test/main/group_by.result b/mysql-test/main/group_by.result index 2eaec8a08b4..44ebce66b22 100644 --- a/mysql-test/main/group_by.result +++ b/mysql-test/main/group_by.result @@ -1608,7 +1608,8 @@ EXPLAIN SELECT 1 FROM t2 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 4 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t2) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 144 SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1, t2; CREATE TABLE t1( diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 484ee626b98..7e57c916d04 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -558,8 +558,9 @@ where exists (select 1 from t2, t3 where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -1 PRIMARY t3 ALL a,b NULL NULL NULL 1002 Range checked for each record (index map: 0x3); FirstMatch(t1) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +2 MATERIALIZED t3 ALL a,b NULL NULL NULL 1002 Range checked for each record (index map: 0x3) select * from t1 where exists (select 1 from t2, t3 where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result index b4572174fcb..2ec3a9ae951 100644 --- a/mysql-test/main/innodb_ext_key.result +++ b/mysql-test/main/innodb_ext_key.result @@ -732,8 +732,8 @@ SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t index a,b b 7 NULL 10 Using index -1 PRIMARY t1 ref b b 3 test.t.b 2 Using index -1 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; FirstMatch(t) +1 PRIMARY t1 ref b b 3 test.t.b 2 Using index; Start temporary +1 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t.a 1 Using index SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); @@ -745,8 +745,8 @@ SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t index a,b b 7 NULL 10 Using index -1 PRIMARY t1 ref b b 3 test.t.b 2 Using index -1 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; FirstMatch(t) +1 PRIMARY t1 ref b b 3 test.t.b 2 Using index; Start temporary +1 PRIMARY t2 index NULL PRIMARY 4 NULL 11 Using index; End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t.a 1 Using index SELECT a FROM t1 AS t, t2 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b); diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index ab07db38d05..13110f0bc73 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -4229,7 +4229,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "strategy": "FirstMatch", "records": 3, - "read_time": 26.667 + "read_time": 33.867 }, { "strategy": "SJ-Materialization", @@ -4365,11 +4365,11 @@ explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t_outer_1 ALL NULL NULL NULL NULL 3 -1 PRIMARY t_inner_1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t_inner_2 ALL NULL NULL NULL NULL 9 FirstMatch(t_outer_1); Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_1 ALL NULL NULL NULL NULL 3 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY t_inner_2 ALL NULL NULL NULL NULL 9 End temporary; Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_4 ALL NULL NULL NULL NULL 3 Start temporary; Using join buffer (incremental, BNL join) 1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (incremental, BNL join) -1 PRIMARY t_inner_4 ALL NULL NULL NULL NULL 3 Using join buffer (incremental, BNL join) -1 PRIMARY t_inner_3 ALL NULL NULL NULL NULL 9 Using where; FirstMatch(t_outer_2); Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_3 ALL NULL NULL NULL NULL 9 Using where; End temporary; Using join buffer (incremental, BNL join) select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and @@ -4638,7 +4638,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "strategy": "FirstMatch", "records": 3, - "read_time": 26.759 + "read_time": 44.759 }, { "strategy": "DuplicateWeedout", @@ -4646,7 +4646,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "read_time": 37.226 }, { - "chosen_strategy": "FirstMatch" + "chosen_strategy": "DuplicateWeedout" } ], "rest_of_plan": [ @@ -4664,7 +4664,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 27, - "cost_for_plan": 34.174, + "cost_for_plan": 44.641, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -4686,7 +4686,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 81, - "cost_for_plan": 52.379, + "cost_for_plan": 62.846, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -4709,20 +4709,20 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 729, - "cost_for_plan": 200.19, + "cost_for_plan": 210.66, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", "records": 27, - "read_time": 251.56 + "read_time": 424.03 }, { "strategy": "DuplicateWeedout", "records": 27, - "read_time": 313.59 + "read_time": 324.06 }, { - "chosen_strategy": "FirstMatch" + "chosen_strategy": "DuplicateWeedout" } ] } @@ -4747,7 +4747,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 243, - "cost_for_plan": 84.79, + "cost_for_plan": 95.256, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -4767,7 +4767,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 9, - "cost_for_plan": 30.564, + "cost_for_plan": 41.031, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -4789,7 +4789,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 81, - "cost_for_plan": 48.779, + "cost_for_plan": 59.246, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -4812,18 +4812,17 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 729, - "cost_for_plan": 196.59, + "cost_for_plan": 207.06, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", "records": 27, - "read_time": 309.99 + "read_time": 320.46 }, { "chosen_strategy": "DuplicateWeedout" } - ], - "pruned_by_cost": true + ] } ] }, @@ -4846,7 +4845,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 81, - "cost_for_plan": 48.779, + "cost_for_plan": 59.246, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -4866,7 +4865,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, "rows_for_plan": 27, - "cost_for_plan": 34.174, + "cost_for_plan": 44.641, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -4914,7 +4913,88 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "chosen_strategy": "DuplicateWeedout" } ], - "pruned_by_cost": true + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_2" + ], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rows_for_plan": 81, + "cost_for_plan": 304.05, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rows_for_plan": 729, + "cost_for_plan": 451.86, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "records": 27, + "read_time": 565.26 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_2" + ], + "table": "t_inner_3", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rows_for_plan": 243, + "cost_for_plan": 336.46, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + } + ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], @@ -4997,7 +5077,63 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "rows_for_plan": 729, "cost_for_plan": 172.44, "semijoin_strategy_choice": [], - "pruned_by_heuristic": true + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_3" + ], + "table": "t_inner_4", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 3, + "cost": 2.0051, + "chosen": true + } + ] + }, + "rows_for_plan": 2187, + "cost_for_plan": 611.85, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "records": 81, + "read_time": 2232.8 + }, + { + "chosen_strategy": "FirstMatch" + } + ], + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_3" + ], + "table": "t_inner_2", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 9, + "cost": 2.0154, + "chosen": true + } + ] + }, + "rows_for_plan": 6561, + "cost_for_plan": 1486.7, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + } + ] } ] }, @@ -5457,70 +5593,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { - "fix_semijoin_strategies_for_picked_join_order": [ - { - "semi_join_strategy": "FirstMatch", - "join_order": [ - { - "table": "t_inner_4" - }, - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 162.42, - "chosen": true - } - ] - }, - { - "table": "t_inner_3" - }, - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 489.74, - "chosen": true - } - ] - } - ] - }, - { - "semi_join_strategy": "FirstMatch", - "join_order": [ - { - "table": "t_inner_1" - }, - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 3, - "cost": 18.046, - "chosen": true - } - ] - }, - { - "table": "t_inner_2" - }, - "best_access_path": { - "considered_access_paths": [ - { - "access_type": "scan", - "resulting_rows": 9, - "cost": 54.415, - "chosen": true - } - ] - } - ] - } - ] + "fix_semijoin_strategies_for_picked_join_order": [] }, { "attaching_conditions_to_tables": { @@ -5540,11 +5613,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "attached": null }, { - "table": "t_outer_2", + "table": "t_inner_4", "attached": null }, { - "table": "t_inner_4", + "table": "t_outer_2", "attached": null }, { @@ -5956,7 +6029,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "strategy": "FirstMatch", "records": 3, - "read_time": 26.759 + "read_time": 44.759 }, { "strategy": "SJ-Materialization", @@ -6037,7 +6110,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "strategy": "FirstMatch", "records": 27, - "read_time": 232.93 + "read_time": 394.93 }, { "strategy": "SJ-Materialization", diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index d503dab4d99..5329a9f64be 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -46,12 +46,12 @@ a b 2 5 explain extended select * from t1 where a in (1,2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from t1 where a in ( @@ -59,12 +59,12 @@ select * from (values (1),(2)) as tvc_0 ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 # AND-condition with IN-predicates in WHERE-part select * from t1 where a in (1,2) and @@ -90,15 +90,15 @@ explain extended select * from t1 where a in (1,2) and b in (1,5); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`_col_1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where 1 explain extended select * from t1 where a in ( @@ -111,15 +111,15 @@ select * from (values (1),(5)) as tvc_1 ); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where 1 # subquery with IN-predicate select * from t1 where a in @@ -206,12 +206,12 @@ from t1 where a in (1,2) ) as dr_table; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from ( select * @@ -224,12 +224,12 @@ as tvc_0 ) ) as dr_table; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 # non-recursive CTE with IN-predicate with tvc_0 as ( @@ -265,12 +265,12 @@ where a in (1,2) ) select * from tvc_0; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` +Note 1003 with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from ( select * @@ -283,12 +283,12 @@ as tvc_0 ) ) as dr_table; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 # VIEW with IN-predicate create view v1 as select * @@ -316,20 +316,20 @@ a b 2 5 explain extended select * from v1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from v2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 drop view v1,v2; # subselect defined by derived table with IN-predicate select * from t1 @@ -519,12 +519,12 @@ a b 1 2 explain extended select * from t1 where (a,b) in ((1,2),(3,4)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`_col_1` and `test`.`t1`.`b` = `tvc_0`.`_col_2` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where 1 set @@in_predicate_conversion_threshold= 2; # trasformation works for the one IN predicate and doesn't work for the other set @@in_predicate_conversion_threshold= 5; @@ -538,12 +538,12 @@ explain extended select * from t2 where (a,b) in ((1,2),(8,9)) and (a,c) in ((1,3),(8,0),(5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where `test`.`t2`.`a` = `tvc_0`.`_col_1` and `test`.`t2`.`c` = `tvc_0`.`_col_2` and (`tvc_0`.`_col_1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9))) +Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where (`test`.`t2`.`a`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9))) set @@in_predicate_conversion_threshold= 2; # # mdev-14281: conversion of NOT IN predicate into subquery predicate diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index fc0049db997..5e446a171fe 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -5703,8 +5703,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); @@ -5714,8 +5714,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 index idx idx 5 NULL 3 Using index DROP TABLE t1,t2; # diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result index ca151daf146..3c33182b3ef 100644 --- a/mysql-test/main/subselect3.result +++ b/mysql-test/main/subselect3.result @@ -1139,8 +1139,8 @@ create table t3 (a int); insert into t3 select A.a + 10*B.a from t0 A, t0 B; explain select * from t3 where a in (select kp1 from t1 where kp1<20); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) create table t4 (pk int primary key); insert into t4 select a from t3; explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 @@ -1276,12 +1276,12 @@ insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B; create table t2 as select * from t1; explain select * from t2 where a in (select b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 8 Using index; LooseScan -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) explain select * from t2 where (b,a) in (select a,b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 8 Using index; LooseScan -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) drop table t1,t2; set @@optimizer_switch=@save_optimizer_switch; create table t1 (a int, b int); @@ -1339,9 +1339,9 @@ insert into t2 select * from t2; explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where -1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using where -1 PRIMARY Z ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t1) +1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 PRIMARY Z ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join) drop table t0,t1,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result index adb3ec80394..69f720a95b7 100644 --- a/mysql-test/main/subselect3_jcl6.result +++ b/mysql-test/main/subselect3_jcl6.result @@ -1149,8 +1149,8 @@ create table t3 (a int); insert into t3 select A.a + 10*B.a from t0 A, t0 B; explain select * from t3 where a in (select kp1 from t1 where kp1<20); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) create table t4 (pk int primary key); insert into t4 select a from t3; explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 @@ -1286,12 +1286,12 @@ insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B; create table t2 as select * from t1; explain select * from t2 where a in (select b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 8 Using index; LooseScan -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) explain select * from t2 where (b,a) in (select a,b from t1 where a=3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 8 Using index; LooseScan -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; FirstMatch(t2) drop table t1,t2; set @@optimizer_switch=@save_optimizer_switch; create table t1 (a int, b int); @@ -1348,10 +1348,10 @@ create table t2 as select a as a, a as b from t0 where a < 3; insert into t2 select * from t2; explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY Z ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t1); Using join buffer (incremental, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY X hash_ALL NULL #hash#$hj 5 test.t1.a 6 Using where; Start temporary; Using join buffer (flat, BNLH join) +1 PRIMARY Y hash_ALL NULL #hash#$hj 5 test.t1.b 6 Using where; Using join buffer (incremental, BNLH join) +1 PRIMARY Z hash_ALL NULL #hash#$hj 5 test.t1.c 6 Using where; End temporary; Using join buffer (incremental, BNLH join) drop table t0,t1,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 4e03d9663ef..ee680d40b75 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -1510,8 +1510,8 @@ set @@optimizer_switch=@optimizer_switch_local_default; SET @@optimizer_switch='semijoin=on,materialization=on'; EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 7 func,func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); COUNT(*) @@ -2437,8 +2437,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.i1 9 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 9 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2450,8 +2450,8 @@ alter table t1 add key(id); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +1 PRIMARY t1 index id id 4 NULL 9 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2507,8 +2507,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +1 PRIMARY t1 index id id 4 NULL 9 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index 4ebf04b1f55..ecd973da09d 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -5683,9 +5683,9 @@ WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a FROM it2,it3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 -1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using join buffer (flat, BNL join) -1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where -1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(ot4) +1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) +1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (flat, BNL join) DROP TABLE IF EXISTS ot1, ot4, it2, it3; # # Bug#729039: NULL keys used to evaluate subquery @@ -5712,8 +5712,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index idx idx 5 NULL 3 Using index; LooseScan -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY t2 ref idx idx 5 test.t1.a 2 Using index; FirstMatch(t1) DROP TABLE t1,t2; # # BUG#752992: Wrong results for a subquery with 'semijoin=on' diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 71d61e797f3..318ad02d473 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -5709,8 +5709,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 USE INDEX() WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); @@ -5720,8 +5720,8 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE t2.a = t1.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 index idx idx 5 NULL 3 Using index DROP TABLE t1,t2; # diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index 98143246673..7c15cbf721e 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -729,8 +729,8 @@ SELECT int_key FROM ot1 WHERE int_nokey IN (SELECT it2.int_key FROM it1 LEFT JOIN it2 ON it2.datetime_key); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 10 -1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it1 index NULL int_key 4 NULL 2 Using index 2 MATERIALIZED it2 ALL int_key,datetime_key NULL NULL NULL 20 Using where DROP TABLE ot1, it1, it2; @@ -972,11 +972,11 @@ SELECT `varchar_key` , `varchar_nokey` FROM t1 WHERE `varchar_nokey` < 'n' XOR `pk` ) ; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 15 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where Warnings: -Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and `test`.`t2`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`) +Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`) SELECT varchar_nokey FROM t2 WHERE ( `varchar_nokey` , `varchar_nokey` ) IN ( @@ -1246,8 +1246,8 @@ INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1); EXPLAIN SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; LooseScan -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); i 1 @@ -1757,8 +1757,8 @@ insert into t3 values('three'),( 'four'); insert into t3 values('three'),( 'four'); explain select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 8 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func 1 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a); @@ -1991,12 +1991,13 @@ CREATE VIEW v4 AS SELECT DISTINCT f2 FROM t4 ; explain extended SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -1 PRIMARY t4 ref f2 f2 5 test.t2.f3 2 100.00 Using index; FirstMatch(t2) +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t4 index f2 f2 5 NULL 2 100.00 Using index Warnings: -Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f2` = `test`.`t2`.`f3` and `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2` +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where `test`.`t3`.`f1` = `test`.`t1`.`f1` and `test`.`t1`.`f2` = `test`.`t2`.`f2` SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4); f1 f2 f3 f3 2 0 0 0 @@ -2495,8 +2496,8 @@ WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 -1 PRIMARY t1 ref a a 5 const 1 Using index -1 PRIMARY t2 ref a a 5 func 1 Using index +1 PRIMARY t2 ref a a 5 const 1 Using index +1 PRIMARY t1 ref a a 5 func 1 Using index 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 0 SELECT * FROM t1, t2 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); @@ -2706,8 +2707,8 @@ a 19 explain select * from t3 where a in (select kp1 from t1 where kp1<20); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) drop table t0,t1,t3; set optimizer_switch= @tmp_923246; # @@ -2923,8 +2924,8 @@ WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o' ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 -1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key PRIMARY,col_varchar_key 4,4 NULL 2 Using sort_union(PRIMARY,col_varchar_key); Using where -1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); FirstMatch(t2) +1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key PRIMARY,col_varchar_key 4,4 NULL 2 Using sort_union(PRIMARY,col_varchar_key); Using where; Start temporary +1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End temporary SELECT * FROM t2 WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1 @@ -3264,4 +3265,27 @@ create table t2 (a2 varchar(25)) ; insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); drop table t1,t2; # End of 5.5 test +# +# MDEV-20109: Optimizer ignores distinct key created for materialized +# semi-join subquery when searching for best execution plan +# +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; +create table t3 (a int); +create table t4 (a int); +insert into t3 select A.a +1000*B.a from t2 A, t1 B; +insert into t4 select floor(rand()*1000) from t2 limit 500; +# The following must not use this query plan that does a cross join: +# | 1 | PRIMARY | <subquery2> | ALL | distinct_key | ... | 500 | | +# | 1 | PRIMARY | t3 | ALL | NULL | ... | 10000 | Using where; Using join buffer (flat, BNL join) | +# +# Instead, it should use eq_ref on the materialized table. +explain select * from t3 where a in (select a from t4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 10000 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 500 +drop table t1, t2, t3, t4; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test index b693f7b5b93..13f6bece181 100644 --- a/mysql-test/main/subselect_sj.test +++ b/mysql-test/main/subselect_sj.test @@ -2940,5 +2940,30 @@ drop table t1,t2; --echo # End of 5.5 test +--echo # +--echo # MDEV-20109: Optimizer ignores distinct key created for materialized +--echo # semi-join subquery when searching for best execution plan +--echo # +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; + +create table t3 (a int); +create table t4 (a int); +insert into t3 select A.a +1000*B.a from t2 A, t1 B; +insert into t4 select floor(rand()*1000) from t2 limit 500; + +--echo # The following must not use this query plan that does a cross join: +--echo # | 1 | PRIMARY | <subquery2> | ALL | distinct_key | ... | 500 | | +--echo # | 1 | PRIMARY | t3 | ALL | NULL | ... | 10000 | Using where; Using join buffer (flat, BNL join) | +--echo # +--echo # Instead, it should use eq_ref on the materialized table. + +explain select * from t3 where a in (select a from t4); + +drop table t1, t2, t3, t4; + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/main/subselect_sj2.result b/mysql-test/main/subselect_sj2.result index a127c18280e..31453801220 100644 --- a/mysql-test/main/subselect_sj2.result +++ b/mysql-test/main/subselect_sj2.result @@ -131,8 +131,9 @@ set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -1 PRIMARY t2 ref b b 5 test.t1.a 1 Using index; FirstMatch(t1) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 index b b 5 NULL 20 Using index select * from t1; a b 1 1 @@ -159,8 +160,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot ALL NULL NULL NULL NULL 32 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z @@ -227,8 +228,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot ALL NULL NULL NULL NULL 52 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z @@ -730,8 +731,9 @@ alter table t3 add primary key(id), add key(a); The following must use loose index scan over t3, key a: explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index a a 5 NULL 1000 Using where; Using index -1 PRIMARY t3 ref a a 5 test.t2.a 30 Using index; FirstMatch(t2) +1 PRIMARY t2 index a a 5 NULL 1000 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) 1000 @@ -757,9 +759,10 @@ c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t2) +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch((sj-nest)) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 drop table t2, t3; # # BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3 diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result index 56c11e8c9af..3321ba221f3 100644 --- a/mysql-test/main/subselect_sj2_jcl6.result +++ b/mysql-test/main/subselect_sj2_jcl6.result @@ -171,9 +171,9 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot hash_ALL NULL #hash#$hj 5 test.it.a 32 Using where; Using join buffer (flat, BNLH join) -2 MATERIALIZED it ALL NULL NULL NULL NULL 22 Using where +1 PRIMARY ot ALL NULL NULL NULL NULL 32 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -239,9 +239,9 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot hash_ALL NULL #hash#$hj 5 test.it.a 52 Using where; Using join buffer (flat, BNLH join) -2 MATERIALIZED it ALL NULL NULL NULL NULL 22 Using where +1 PRIMARY ot ALL NULL NULL NULL NULL 52 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -1410,9 +1410,10 @@ SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b WHERE c IN (SELECT t4.b FROM t4 JOIN t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) -1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t3); Using join buffer (incremental, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t1 ref b b 4 test.t3.b 1 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b WHERE c IN (SELECT t4.b FROM t4 JOIN t2); b c @@ -1438,9 +1439,10 @@ EXPLAIN SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t4 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.b 1 Using where; FirstMatch(t2) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 1 Using where +2 MATERIALIZED t3 eq_ref PRIMARY PRIMARY 4 test.t4.b 1 SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk); pk a b 1 6 8 diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result index 73f682755da..7245e74b242 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -133,8 +133,9 @@ set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -1 PRIMARY t2 ref b b 5 test.t1.a 1 Using index; FirstMatch(t1) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 index b b 5 NULL 20 Using index select * from t1; a b 1 1 @@ -161,8 +162,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 32 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot ALL NULL NULL NULL NULL 32 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z @@ -229,8 +230,8 @@ explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 22 -1 PRIMARY ot ALL NULL NULL NULL NULL 52 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot ALL NULL NULL NULL NULL 52 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it ALL NULL NULL NULL NULL 22 select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z @@ -732,8 +733,9 @@ alter table t3 add primary key(id), add key(a); The following must use loose index scan over t3, key a: explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index a a 5 NULL 1000 Using where; Using index -1 PRIMARY t3 ref a a 5 test.t2.a 30 Using index; FirstMatch(t2) +1 PRIMARY t2 index a a 5 NULL 1000 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t3 index a a 5 NULL 30000 Using index select count(a) from t2 where a in ( SELECT a FROM t3); count(a) 1000 @@ -759,9 +761,10 @@ c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t2) +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch((sj-nest)) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 drop table t2, t3; # # BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3 @@ -1638,12 +1641,12 @@ set optimizer_switch='materialization=on,semijoin=on'; EXPLAIN EXTENDED SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 -1 PRIMARY t1 hash_ALL NULL #hash#$hj 5 test.t2.i2 5 100.00 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 MATERIALIZED t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 3 100.00 Using where; Using join buffer (flat, BNLH join) Warnings: -Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`i1` = `test`.`t2`.`i2` +Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); i1 7 @@ -1651,12 +1654,12 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 -1 PRIMARY t1 hash_ALL NULL #hash#$hj 5 test.t2.i2 5 100.00 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 MATERIALIZED t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 3 100.00 Using where; Using join buffer (flat, BNLH join) Warnings: -Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`i1` = `test`.`t2`.`i2` and `test`.`t3`.`i3` > 0 +Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t3`.`i3` > 0 SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2); i1 @@ -1748,8 +1751,8 @@ OR ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t index PRIMARY PRIMARY 4 NULL 13 Using where; Using index -2 MATERIALIZED <subquery3> ALL distinct_key NULL NULL NULL 8 -2 MATERIALIZED A ALL PRIMARY NULL NULL NULL 13 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED A ALL PRIMARY NULL NULL NULL 13 +2 MATERIALIZED <subquery3> eq_ref distinct_key distinct_key 67 func 1 3 MATERIALIZED B range PRIMARY PRIMARY 4 NULL 8 Using where SELECT SQL_NO_CACHE t.id FROM t1 t @@ -1914,18 +1917,16 @@ AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 12 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t2_2.id_product 1 Using where; Using index -1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY t2_3 ref id_t2,id_product id_product 5 test.t3.id_product 44 Using index condition; Using where; Start temporary; End temporary +1 PRIMARY t2_5 ref id_t2,id_product id_product 5 test.t3.id_product 44 Using index condition; Using where; Start temporary; End temporary 1 PRIMARY t5 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 PRIMARY <subquery6> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 8 test.t3.id_product,const 1 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where -1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t1 index NULL PRIMARY 8 NULL 73 Using where; Using index; Using join buffer (flat, BNL join) 3 MATERIALIZED t2_2 ref id_t2,id_product id_t2 5 const 12 Using where -5 MATERIALIZED t2_4 range id_t2,id_product id_t2 5 NULL 18 Using index condition; Using where -6 MATERIALIZED t2_5 range id_t2,id_product id_t2 5 NULL 31 Using index condition; Using where 2 MATERIALIZED t2_1 ref id_t2,id_product id_t2 5 const 51 -4 MATERIALIZED t2_3 range id_t2,id_product id_t2 5 NULL 33 Using index condition; Using where +5 MATERIALIZED t2_4 range id_t2,id_product id_t2 5 NULL 18 Using index condition; Using where set optimizer_switch='rowid_filter=default'; drop table t1,t2,t3,t4,t5; set global innodb_stats_persistent= @innodb_stats_persistent_save; diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index acfafde6d7e..91892156e76 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -742,8 +742,8 @@ SELECT int_key FROM ot1 WHERE int_nokey IN (SELECT it2.int_key FROM it1 LEFT JOIN it2 ON it2.datetime_key); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 10 -1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED it1 index NULL int_key 4 NULL 2 Using index 2 MATERIALIZED it2 ALL int_key,datetime_key NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) DROP TABLE ot1, it1, it2; @@ -985,11 +985,11 @@ SELECT `varchar_key` , `varchar_nokey` FROM t1 WHERE `varchar_nokey` < 'n' XOR `pk` ) ; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 15 100.00 -1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00 2 MATERIALIZED t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where Warnings: -Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and `test`.`t2`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`) +Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where `test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key` and (`test`.`t1`.`varchar_key` < 'n' xor `test`.`t1`.`pk`) SELECT varchar_nokey FROM t2 WHERE ( `varchar_nokey` , `varchar_nokey` ) IN ( @@ -1259,8 +1259,8 @@ INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1); EXPLAIN SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; LooseScan -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 index k k 10 NULL 4 Using where; Using index; Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0); i 1 @@ -1770,8 +1770,8 @@ insert into t3 values('three'),( 'four'); insert into t3 values('three'),( 'four'); explain select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 8 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 10 func 1 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a); @@ -2509,8 +2509,8 @@ WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 -1 PRIMARY t1 ref a a 5 const 1 Using index -1 PRIMARY t2 ref a a 5 func 1 Using index +1 PRIMARY t2 ref a a 5 const 1 Using index +1 PRIMARY t1 ref a a 5 func 1 Using index 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 0 SELECT * FROM t1, t2 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); @@ -2720,8 +2720,8 @@ a 19 explain select * from t3 where a in (select kp1 from t1 where kp1<20); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3) drop table t0,t1,t3; set optimizer_switch= @tmp_923246; # @@ -2937,8 +2937,8 @@ WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o' ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 -1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key PRIMARY,col_varchar_key 4,4 NULL 2 Using sort_union(PRIMARY,col_varchar_key); Using where -1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); FirstMatch(t2) +1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key PRIMARY,col_varchar_key 4,4 NULL 2 Using sort_union(PRIMARY,col_varchar_key); Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End temporary SELECT * FROM t2 WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1 @@ -3278,6 +3278,29 @@ create table t2 (a2 varchar(25)) ; insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); drop table t1,t2; # End of 5.5 test +# +# MDEV-20109: Optimizer ignores distinct key created for materialized +# semi-join subquery when searching for best execution plan +# +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; +create table t3 (a int); +create table t4 (a int); +insert into t3 select A.a +1000*B.a from t2 A, t1 B; +insert into t4 select floor(rand()*1000) from t2 limit 500; +# The following must not use this query plan that does a cross join: +# | 1 | PRIMARY | <subquery2> | ALL | distinct_key | ... | 500 | | +# | 1 | PRIMARY | t3 | ALL | NULL | ... | 10000 | Using where; Using join buffer (flat, BNL join) | +# +# Instead, it should use eq_ref on the materialized table. +explain select * from t3 where a in (select a from t4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 10000 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 500 +drop table t1, t2, t3, t4; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off @@ -3485,8 +3508,8 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t range idx_a idx_a 4 NULL 3 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort +1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) @@ -3499,8 +3522,8 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t range idx_a idx_a 4 NULL 3 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort +1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index 203dbee6374..432f6c648fc 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -491,15 +491,15 @@ where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and a1 = c1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 4 100.00 Using where 1 PRIMARY t3 ALL NULL NULL NULL NULL 4 99.22 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 16 func,func 1 100.00 4 MATERIALIZED t3 ALL NULL NULL NULL NULL 4 99.22 Using where 4 MATERIALIZED t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t3.c1,test.t3.c2 1 100.00 Using index 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 99.22 Using where 3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 99.22 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t3` where `test`.`t3`.`c1` = `test`.`t1`.`a1` and `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t3`.`c1` = `test`.`t1`.`a1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and `test`.`t3`.`c2` = `test`.`t3`.`c2` and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select `test`.`t1`.`a1`,`test`.`t1`.`a2` from `test`.`t1` where `test`.`t1`.`a1` > '0' and <cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1` and <cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2` union /* select#3 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where `test`.`t2`.`b1` < '9' and <cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1` and <cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))) and `test`.`t3`.`c2` > '0' +Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t3` where `test`.`t3`.`c1` = `test`.`t1`.`a1` and `test`.`t2i`.`b1` = `test`.`t3`.`c1` and `test`.`t2i`.`b2` = `test`.`t3`.`c2` and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select `test`.`t1`.`a1`,`test`.`t1`.`a2` from `test`.`t1` where `test`.`t1`.`a1` > '0' and <cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1` and <cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2` union /* select#3 */ select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where `test`.`t2`.`b1` < '9' and <cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1` and <cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))) and `test`.`t3`.`c2` > '0' select * from t1, t3 where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and (c1, c2) in (select c1, c2 from t3 @@ -1132,11 +1132,11 @@ insert into t3 values (30); explain extended select a from t1 where a in (select c from t2 where d >= 20); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 6 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t2`.`c` and `test`.`t2`.`d` >= 20 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`d` >= 20 select a from t1 where a in (select c from t2 where d >= 20); a 2 @@ -1543,8 +1543,8 @@ set @@optimizer_switch=@optimizer_switch_local_default; SET @@optimizer_switch='semijoin=on,materialization=on'; EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 7 func,func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); COUNT(*) @@ -2473,8 +2473,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.i1 9 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 9 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2486,8 +2486,8 @@ alter table t1 add key(id); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +1 PRIMARY t1 index id id 4 NULL 9 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2543,8 +2543,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +1 PRIMARY t1 index id id 4 NULL 9 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 51198ea47d6..a9b085fe31e 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -743,21 +743,21 @@ a b explain extended select * from t1 where a in (values (1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 explain extended select * from t1 where a in (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a in (values (1) union select 2); @@ -978,21 +978,21 @@ a b explain extended select * from t1 where a = any (values (1),(2)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from t1 where a = any (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a = any (values (1) union select 2); diff --git a/mysql-test/main/type_date.result b/mysql-test/main/type_date.result index a9d17b8eb83..fbf348c1a2f 100644 --- a/mysql-test/main/type_date.result +++ b/mysql-test/main/type_date.result @@ -890,6 +890,37 @@ DROP TABLE t2; DROP VIEW v1; DROP TABLE t1; # +# MDEV-19699 Server crashes in Item_null_result::field_type upon SELECT with ROLLUP on constant table +# +CREATE TABLE t1 (d DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('1999-11-04'); +SELECT d FROM t1 GROUP BY d WITH ROLLUP HAVING d > '1990-01-01'; +d +1999-11-04 +DROP TABLE t1; +# +# MDEV-20431 GREATEST(int_col,date_col) returns wrong results in a view +# +CREATE TABLE t1 (pk INT NOT NULL, d DATE NOT NULL); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1,'2018-06-22'),(2,'2018-07-11'); +SELECT GREATEST(pk, d) FROM t1; +GREATEST(pk, d) +2018-06-22 +2018-07-11 +Warnings: +Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1 +Warning 1292 Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2 +SELECT GREATEST(pk, d) FROM v1; +GREATEST(pk, d) +2018-06-22 +2018-07-11 +Warnings: +Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1 +Warning 1292 Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2 +DROP VIEW v1; +DROP TABLE t1; +# # End of 10.1 tests # # diff --git a/mysql-test/main/type_date.test b/mysql-test/main/type_date.test index d795a01fd36..bd874ec51fb 100644 --- a/mysql-test/main/type_date.test +++ b/mysql-test/main/type_date.test @@ -606,6 +606,30 @@ DROP TABLE t2; DROP VIEW v1; DROP TABLE t1; + +--echo # +--echo # MDEV-19699 Server crashes in Item_null_result::field_type upon SELECT with ROLLUP on constant table +--echo # + +CREATE TABLE t1 (d DATE) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('1999-11-04'); +SELECT d FROM t1 GROUP BY d WITH ROLLUP HAVING d > '1990-01-01'; +DROP TABLE t1; + + +--echo # +--echo # MDEV-20431 GREATEST(int_col,date_col) returns wrong results in a view +--echo # + +CREATE TABLE t1 (pk INT NOT NULL, d DATE NOT NULL); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1,'2018-06-22'),(2,'2018-07-11'); +SELECT GREATEST(pk, d) FROM t1; +SELECT GREATEST(pk, d) FROM v1; +DROP VIEW v1; +DROP TABLE t1; + + --echo # --echo # End of 10.1 tests --echo # diff --git a/mysql-test/main/type_datetime.result b/mysql-test/main/type_datetime.result index 6e527398f8f..5afb0257757 100644 --- a/mysql-test/main/type_datetime.result +++ b/mysql-test/main/type_datetime.result @@ -1167,6 +1167,37 @@ INSERT INTO t2 SELECT * FROM t1; DROP TABLE t1, t2; SET SQL_MODE=DEFAULT; # +# MDEV-19699 Server crashes in Item_null_result::field_type upon SELECT with ROLLUP on constant table +# +CREATE TABLE t1 (d DATETIME) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('1999-11-04'); +SELECT d FROM t1 GROUP BY d WITH ROLLUP HAVING d > '1990-01-01'; +d +1999-11-04 00:00:00 +DROP TABLE t1; +# +# MDEV-20431 GREATEST(int_col,date_col) returns wrong results in a view +# +CREATE TABLE t1 (pk INT NOT NULL, d DATETIME NOT NULL); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1,'2018-06-22 00:00:00'),(2,'2018-07-11 00:00:00'); +SELECT GREATEST(pk, d) FROM t1; +GREATEST(pk, d) +2018-06-22 00:00:00 +2018-07-11 00:00:00 +Warnings: +Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1 +Warning 1292 Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2 +SELECT GREATEST(pk, d) FROM v1; +GREATEST(pk, d) +2018-06-22 00:00:00 +2018-07-11 00:00:00 +Warnings: +Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1 +Warning 1292 Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2 +DROP VIEW v1; +DROP TABLE t1; +# # End of 10.1 tests # # diff --git a/mysql-test/main/type_datetime.test b/mysql-test/main/type_datetime.test index 3e989df1a2d..b4b5fe95bbf 100644 --- a/mysql-test/main/type_datetime.test +++ b/mysql-test/main/type_datetime.test @@ -725,6 +725,29 @@ SET SQL_MODE=DEFAULT; --echo # +--echo # MDEV-19699 Server crashes in Item_null_result::field_type upon SELECT with ROLLUP on constant table +--echo # + +CREATE TABLE t1 (d DATETIME) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('1999-11-04'); +SELECT d FROM t1 GROUP BY d WITH ROLLUP HAVING d > '1990-01-01'; +DROP TABLE t1; + + +--echo # +--echo # MDEV-20431 GREATEST(int_col,date_col) returns wrong results in a view +--echo # + +CREATE TABLE t1 (pk INT NOT NULL, d DATETIME NOT NULL); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1,'2018-06-22 00:00:00'),(2,'2018-07-11 00:00:00'); +SELECT GREATEST(pk, d) FROM t1; +SELECT GREATEST(pk, d) FROM v1; +DROP VIEW v1; +DROP TABLE t1; + + +--echo # --echo # End of 10.1 tests --echo # diff --git a/mysql-test/suite/binlog/include/drop_temp_table.test b/mysql-test/suite/binlog/include/drop_temp_table.test index 9139ac76017..7c95195eadc 100644 --- a/mysql-test/suite/binlog/include/drop_temp_table.test +++ b/mysql-test/suite/binlog/include/drop_temp_table.test @@ -150,16 +150,16 @@ RESET MASTER; --echo # Test case for DROP query. --connection default -CREATE TABLE t1 (a INT) ENGINE=INNODB; +CREATE TABLE t2 (a INT) ENGINE=INNODB; --connection con1 -CREATE TEMPORARY TABLE t1 (b BLOB) ENGINE=INNODB; +CREATE TEMPORARY TABLE t2 (b BLOB) ENGINE=INNODB; --connection default -DROP TABLE t1; +DROP TABLE t2; --connection con1 -DROP TABLE t1; +DROP TABLE t2; --connection default --exec $MYSQL_BINLOG --force-if-open $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/bug28642318.sql diff --git a/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result b/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result index 60596be480f..10744956995 100644 --- a/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result +++ b/mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result @@ -88,13 +88,13 @@ DROP TABLE IF EXISTS t1; RESET MASTER; # Test case for DROP query. connection default; -CREATE TABLE t1 (a INT) ENGINE=INNODB; +CREATE TABLE t2 (a INT) ENGINE=INNODB; connection con1; -CREATE TEMPORARY TABLE t1 (b BLOB) ENGINE=INNODB; +CREATE TEMPORARY TABLE t2 (b BLOB) ENGINE=INNODB; connection default; -DROP TABLE t1; +DROP TABLE t2; connection con1; -DROP TABLE t1; +DROP TABLE t2; connection default; # DROP table query fails with unknown table error without patch. # Clean up diff --git a/mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result b/mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result index ae7852be9e3..99305abf175 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result +++ b/mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result @@ -116,13 +116,13 @@ DROP TABLE IF EXISTS t1; RESET MASTER; # Test case for DROP query. connection default; -CREATE TABLE t1 (a INT) ENGINE=INNODB; +CREATE TABLE t2 (a INT) ENGINE=INNODB; connection con1; -CREATE TEMPORARY TABLE t1 (b BLOB) ENGINE=INNODB; +CREATE TEMPORARY TABLE t2 (b BLOB) ENGINE=INNODB; connection default; -DROP TABLE t1; +DROP TABLE t2; connection con1; -DROP TABLE t1; +DROP TABLE t2; connection default; # DROP table query fails with unknown table error without patch. # Clean up diff --git a/mysql-test/suite/compat/oracle/r/table_value_constr.result b/mysql-test/suite/compat/oracle/r/table_value_constr.result index 3e72167d43d..4383845cd87 100644 --- a/mysql-test/suite/compat/oracle/r/table_value_constr.result +++ b/mysql-test/suite/compat/oracle/r/table_value_constr.result @@ -741,21 +741,21 @@ a b explain extended select * from t1 where a in (values (1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1" +Note 1003 select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where 1 explain extended select * from t1 where a in (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1" +Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where 1 # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a in (values (1) union select 2); @@ -976,21 +976,21 @@ a b explain extended select * from t1 where a = any (values (1),(2)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1" +Note 1003 select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where 1 explain extended select * from t1 where a = any (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1" +Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where 1 # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a = any (values (1) union select 2); diff --git a/mysql-test/suite/gcol/r/gcol_select_innodb.result b/mysql-test/suite/gcol/r/gcol_select_innodb.result index f933fe22bdc..6c5b6d8c47e 100644 --- a/mysql-test/suite/gcol/r/gcol_select_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_select_innodb.result @@ -699,8 +699,8 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where @@ -754,8 +754,8 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where @@ -810,8 +810,8 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where @@ -874,8 +874,8 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where diff --git a/mysql-test/suite/gcol/r/gcol_select_myisam.result b/mysql-test/suite/gcol/r/gcol_select_myisam.result index 45c3f73ed8e..c19ea3207ca 100644 --- a/mysql-test/suite/gcol/r/gcol_select_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_select_myisam.result @@ -1325,8 +1325,8 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where @@ -1381,8 +1381,8 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY,v_idx,v_idx2 PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where @@ -1439,8 +1439,8 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY,v_idx2 PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where @@ -1506,8 +1506,8 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 1 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where diff --git a/mysql-test/suite/innodb/r/innodb-read-view.result b/mysql-test/suite/innodb/r/innodb-read-view.result new file mode 100644 index 00000000000..e01d8a110e6 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-read-view.result @@ -0,0 +1,222 @@ +CREATE TABLE t1 (c1 INT , c2 CHAR(10), PRIMARY KEY (c1)) ENGINE = InnoDB; +INSERT INTO t1 VALUES(0, "0"); +INSERT INTO t1 VALUES(1, "1"); +INSERT INTO t1 VALUES(2, "2"); +INSERT INTO t1 VALUES(3, "3"); +CREATE TABLE t2 (c1 INT , c2 CHAR(10), PRIMARY KEY (c1)) ENGINE = InnoDB; +INSERT INTO t2 VALUES(0, "a"); +INSERT INTO t2 VALUES(1, "b"); +INSERT INTO t2 VALUES(2, "c"); +INSERT INTO t2 VALUES(3, "d"); +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +'T1' +SET AUTOCOMMIT=0; +BEGIN; +SELECT * FROM t2; +c1 c2 +0 a +1 b +2 c +3 d +connection default; +'T2' +SET AUTOCOMMIT=0; +BEGIN; +SELECT * FROM t1; +c1 c2 +0 0 +1 1 +2 2 +3 3 +connection con2; +'T3' +SET AUTOCOMMIT=0; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SELECT * FROM t1; +c1 c2 +0 0 +1 1 +2 2 +3 3 +SELECT * FROM t2; +c1 c2 +0 a +1 b +2 c +3 d +connection con1; +'T1' +UPDATE t2 SET c1 = c1 + 100; +SELECT * FROM t2; +c1 c2 +100 a +101 b +102 c +103 d +COMMIT; +connection default; +'T2' +UPDATE t1 SET c1 = c1 + 100; +SELECT * FROM t1; +c1 c2 +100 0 +101 1 +102 2 +103 3 +COMMIT; +connection con2; +'T3' +SET DEBUG_SYNC='row_search_for_mysql_before_return WAIT_FOR waiting1'; +SELECT * FROM t1;; +connection default; +'T2' +SET DEBUG_SYNC='now SIGNAL waiting1'; +'Signalled T3' +connection con2; +'T3' +c1 c2 +0 0 +1 1 +2 2 +3 3 +connection con2; +'T3' +SET DEBUG_SYNC='row_search_for_mysql_before_return WAIT_FOR waiting1'; +SELECT * FROM t2;; +connection default; +'T2' +SET DEBUG_SYNC='now SIGNAL waiting1'; +'Signalled T3' +connection con2; +'T3' +c1 c2 +0 a +1 b +2 c +3 d +connection default; +disconnect con1; +disconnect con2; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +'T1' +SET AUTOCOMMIT=0; +BEGIN; +SELECT * FROM t1; +c1 c2 +100 0 +101 1 +102 2 +103 3 +connection default; +'T2' +SET AUTOCOMMIT=0; +BEGIN; +SELECT * FROM t2; +c1 c2 +100 a +101 b +102 c +103 d +UPDATE t2 SET c1 = c1 + 100; +SELECT * FROM t2; +c1 c2 +200 a +201 b +202 c +203 d +COMMIT; +connection con2; +'T3' +SET AUTOCOMMIT=0; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SELECT * FROM t1; +c1 c2 +100 0 +101 1 +102 2 +103 3 +SELECT * FROM t2; +c1 c2 +200 a +201 b +202 c +203 d +connection con1; +'T1' +UPDATE t1 SET c1 = c1 + 100; +SELECT * FROM t1; +c1 c2 +200 0 +201 1 +202 2 +203 3 +COMMIT; +connection con2; +'T3' +SET DEBUG_SYNC='row_select_wait WAIT_FOR waiting1'; +SELECT * FROM t1;; +connection con1; +'T2' +SET DEBUG_SYNC='now SIGNAL waiting1'; +'Signalled T3' +connection con2; +'T3' +c1 c2 +100 0 +101 1 +102 2 +103 3 +connection con2; +'T3' +SET DEBUG_SYNC='row_select_wait WAIT_FOR waiting1'; +SELECT * FROM t2;; +connection default; +'T2' +SET DEBUG_SYNC='now SIGNAL waiting1'; +'Signalled T3' +connection con2; +'T3' +c1 c2 +200 a +201 b +202 c +203 d +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t1; +DROP TABLE t2; +# +# Bug 21433768: NON-REPEATABLE READ WITH REPEATABLE READ ISOLATION +# +connect con1,localhost,root,,; +CREATE TABLE t1(col1 INT PRIMARY KEY, col2 INT) ENGINE = InnoDB; +INSERT INTO t1 values (1, 0), (2, 0); +SELECT * FROM t1 ORDER BY col1; +col1 col2 +1 0 +2 0 +START TRANSACTION; +UPDATE t1 SET col2 = 100; +SET DEBUG_SYNC = 'after_trx_committed_in_memory SIGNAL s1 WAIT_FOR s2'; +COMMIT;; +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR s1'; +UPDATE t1 SET col2 = col2 + 10 where col1 = 1; +COMMIT; +SELECT * FROM t1 ORDER BY col1; +col1 col2 +1 110 +2 100 +SET DEBUG_SYNC = 'now SIGNAL s2'; +connection con1; +disconnect con1; +connection default; +DROP TABLE t1; +SET DEBUG_SYNC= 'RESET'; diff --git a/mysql-test/suite/innodb/r/innodb-system-table-view.result b/mysql-test/suite/innodb/r/innodb-system-table-view.result index 46730ef3aac..65dc0a8e274 100644 --- a/mysql-test/suite/innodb/r/innodb-system-table-view.result +++ b/mysql-test/suite/innodb/r/innodb-system-table-view.result @@ -1,3 +1,5 @@ +SET @save_frequency=@@GLOBAL.innodb_purge_rseg_truncate_frequency; +SET GLOBAL innodb_purge_rseg_truncate_frequency=1; SELECT table_id INTO @table_stats_id FROM information_schema.innodb_sys_tables WHERE name = 'mysql/innodb_table_stats'; SELECT table_id INTO @index_stats_id FROM information_schema.innodb_sys_tables @@ -108,6 +110,7 @@ SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; ID FOR_COL_NAME REF_COL_NAME POS test/constraint_test parent_id id 0 INSERT INTO parent VALUES(1); +InnoDB 0 transactions not purged SELECT name, num_rows, ref_count FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name LIKE "%parent"; @@ -169,6 +172,7 @@ INSERT INTO parent VALUES(1, 9); SELECT * FROM parent WHERE id IN (SELECT id FROM parent); id newid 1 9 +InnoDB 0 transactions not purged SELECT name, num_rows, ref_count FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name LIKE "%parent"; @@ -176,3 +180,4 @@ name num_rows ref_count test/parent 1 2 DROP TABLE child; DROP TABLE parent; +SET GLOBAL innodb_purge_rseg_truncate_frequency=@save_frequency; diff --git a/mysql-test/suite/innodb/r/instant_alter.result b/mysql-test/suite/innodb/r/instant_alter.result index 3122c15fae0..3139bbd152c 100644 --- a/mysql-test/suite/innodb/r/instant_alter.result +++ b/mysql-test/suite/innodb/r/instant_alter.result @@ -498,6 +498,29 @@ DELETE FROM t1; COMMIT; InnoDB 0 transactions not purged DROP TABLE t1; +CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES (7); +ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 0; +ALTER TABLE t1 ADD INDEX (c); +BEGIN; +DELETE FROM t1; +INSERT INTO t1 VALUES (4,0),(7,77); +COMMIT; +BEGIN; +DELETE FROM t1 WHERE a=7; +UPDATE t1 SET a=7; +COMMIT; +SELECT * FROM t1 FORCE INDEX(PRIMARY); +a c +7 0 +SELECT * FROM t1 FORCE INDEX(c); +a c +7 0 +DELETE FROM t1; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; CREATE TABLE t1 (a INT, b INT UNIQUE) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; INSERT INTO t1 (a) VALUES (NULL), (NULL); ALTER TABLE t1 DROP a, ADD COLUMN a INT; @@ -1326,6 +1349,29 @@ DELETE FROM t1; COMMIT; InnoDB 0 transactions not purged DROP TABLE t1; +CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES (7); +ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 0; +ALTER TABLE t1 ADD INDEX (c); +BEGIN; +DELETE FROM t1; +INSERT INTO t1 VALUES (4,0),(7,77); +COMMIT; +BEGIN; +DELETE FROM t1 WHERE a=7; +UPDATE t1 SET a=7; +COMMIT; +SELECT * FROM t1 FORCE INDEX(PRIMARY); +a c +7 0 +SELECT * FROM t1 FORCE INDEX(c); +a c +7 0 +DELETE FROM t1; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; CREATE TABLE t1 (a INT, b INT UNIQUE) ENGINE=InnoDB ROW_FORMAT=COMPACT; INSERT INTO t1 (a) VALUES (NULL), (NULL); ALTER TABLE t1 DROP a, ADD COLUMN a INT; @@ -2154,6 +2200,29 @@ DELETE FROM t1; COMMIT; InnoDB 0 transactions not purged DROP TABLE t1; +CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES (7); +ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 0; +ALTER TABLE t1 ADD INDEX (c); +BEGIN; +DELETE FROM t1; +INSERT INTO t1 VALUES (4,0),(7,77); +COMMIT; +BEGIN; +DELETE FROM t1 WHERE a=7; +UPDATE t1 SET a=7; +COMMIT; +SELECT * FROM t1 FORCE INDEX(PRIMARY); +a c +7 0 +SELECT * FROM t1 FORCE INDEX(c); +a c +7 0 +DELETE FROM t1; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; CREATE TABLE t1 (a INT, b INT UNIQUE) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; INSERT INTO t1 (a) VALUES (NULL), (NULL); ALTER TABLE t1 DROP a, ADD COLUMN a INT; @@ -2544,7 +2613,7 @@ SELECT variable_value-@old_instant instants FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; instants -181 +184 SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency; # # MDEV-18266: Changing an index comment unnecessarily rebuilds index diff --git a/mysql-test/suite/innodb/t/innodb-read-view.test b/mysql-test/suite/innodb/t/innodb-read-view.test new file mode 100644 index 00000000000..425cbeb08c8 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-read-view.test @@ -0,0 +1,208 @@ +# DEBUG_SYNC must be compiled in. +--source include/have_debug_sync.inc +--source include/have_debug.inc + +# We need to test the use case: +# a. Create a transaction T1 that will be promoted to RW. +# b. Create a transaction T2 that will be promoted to RW. +# a. Create a RO transaction T3 +# d. T3 does a select - creates a read view that doesn't include T1 and T2 +# e. T1 & T2 do some updates - this promotes T1 & T2 to RW transactions +# f. T1 & T2 Commit +# g. T3 Does a select - it should not see the changes of T1 & T2 + +--source include/have_innodb.inc +--source include/count_sessions.inc + +CREATE TABLE t1 (c1 INT , c2 CHAR(10), PRIMARY KEY (c1)) ENGINE = InnoDB; +INSERT INTO t1 VALUES(0, "0"); +INSERT INTO t1 VALUES(1, "1"); +INSERT INTO t1 VALUES(2, "2"); +INSERT INTO t1 VALUES(3, "3"); + +CREATE TABLE t2 (c1 INT , c2 CHAR(10), PRIMARY KEY (c1)) ENGINE = InnoDB; +INSERT INTO t2 VALUES(0, "a"); +INSERT INTO t2 VALUES(1, "b"); +INSERT INTO t2 VALUES(2, "c"); +INSERT INTO t2 VALUES(3, "d"); + +--connect (con1,localhost,root,,) +--connect (con2,localhost,root,,) + +connection con1; +--echo 'T1' +SET AUTOCOMMIT=0; +BEGIN; +SELECT * FROM t2; + +connection default; +--echo 'T2' +SET AUTOCOMMIT=0; +BEGIN; +SELECT * FROM t1; + +connection con2; +--echo 'T3' +SET AUTOCOMMIT=0; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SELECT * FROM t1; +SELECT * FROM t2; + +connection con1; +--echo 'T1' +UPDATE t2 SET c1 = c1 + 100; +SELECT * FROM t2; +COMMIT; + +connection default; +--echo 'T2' +UPDATE t1 SET c1 = c1 + 100; +SELECT * FROM t1; +COMMIT; + +connection con2; +--echo 'T3' +SET DEBUG_SYNC='row_search_for_mysql_before_return WAIT_FOR waiting1'; +--send SELECT * FROM t1; + +connection default; +--echo 'T2' +SET DEBUG_SYNC='now SIGNAL waiting1'; +--echo 'Signalled T3' + +connection con2; +--echo 'T3' +reap; + +connection con2; +--echo 'T3' +SET DEBUG_SYNC='row_search_for_mysql_before_return WAIT_FOR waiting1'; +--send SELECT * FROM t2; + +connection default; +--echo 'T2' +SET DEBUG_SYNC='now SIGNAL waiting1'; +--echo 'Signalled T3' + +connection con2; +--echo 'T3' +reap; + +connection default; +disconnect con1; +disconnect con2; + +# We need to test the use case: +# a. Create a transaction T1 that will be promoted to RW. +# b. Create a transaction T2 that will be promoted to RW. +# c. T2 does some updates - this promotes T2 to RW transactions +# d. T2 Commits +# e. Create a RO transaction T3 +# f. T3 does a select - creates a read view that doesn't include T1 +# g. T1 does some updates - this promotes T1 to RW transactions +# h. T1 Commits +# i. T3 Does a select - it should not see the changes made by T1 but should +# see the changes by T2 + +--connect (con1,localhost,root,,) +--connect (con2,localhost,root,,) + +connection con1; +--echo 'T1' +SET AUTOCOMMIT=0; +BEGIN; +SELECT * FROM t1; + +connection default; +--echo 'T2' +SET AUTOCOMMIT=0; +BEGIN; +SELECT * FROM t2; +UPDATE t2 SET c1 = c1 + 100; +SELECT * FROM t2; +COMMIT; + +connection con2; +--echo 'T3' +SET AUTOCOMMIT=0; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +SELECT * FROM t1; +SELECT * FROM t2; + +connection con1; +--echo 'T1' +UPDATE t1 SET c1 = c1 + 100; +SELECT * FROM t1; +COMMIT; + +connection con2; +--echo 'T3' +SET DEBUG_SYNC='row_select_wait WAIT_FOR waiting1'; +--send SELECT * FROM t1; + +connection con1; +--echo 'T2' +SET DEBUG_SYNC='now SIGNAL waiting1'; +--echo 'Signalled T3' + +connection con2; +--echo 'T3' +reap; + +connection con2; +--echo 'T3' +SET DEBUG_SYNC='row_select_wait WAIT_FOR waiting1'; +--send SELECT * FROM t2; + +connection default; +--echo 'T2' +SET DEBUG_SYNC='now SIGNAL waiting1'; +--echo 'Signalled T3' + +connection con2; +--echo 'T3' +reap; + +connection default; +disconnect con1; +disconnect con2; + +DROP TABLE t1; +DROP TABLE t2; + +--echo # +--echo # Bug 21433768: NON-REPEATABLE READ WITH REPEATABLE READ ISOLATION +--echo # + +--connect (con1,localhost,root,,) + +CREATE TABLE t1(col1 INT PRIMARY KEY, col2 INT) ENGINE = InnoDB; +INSERT INTO t1 values (1, 0), (2, 0); +SELECT * FROM t1 ORDER BY col1; + +START TRANSACTION; +UPDATE t1 SET col2 = 100; +SET DEBUG_SYNC = 'after_trx_committed_in_memory SIGNAL s1 WAIT_FOR s2'; +--send COMMIT; + +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR s1'; +UPDATE t1 SET col2 = col2 + 10 where col1 = 1; +COMMIT; + +SELECT * FROM t1 ORDER BY col1; +SET DEBUG_SYNC = 'now SIGNAL s2'; + +connection con1; +reap; +disconnect con1; + +connection default; + +DROP TABLE t1; + +# Clean up resources used in this test case. +SET DEBUG_SYNC= 'RESET'; +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/innodb/t/innodb-system-table-view.test b/mysql-test/suite/innodb/t/innodb-system-table-view.test index 087c0a22eca..4f5111eafbc 100644 --- a/mysql-test/suite/innodb/t/innodb-system-table-view.test +++ b/mysql-test/suite/innodb/t/innodb-system-table-view.test @@ -4,6 +4,8 @@ --source include/innodb_page_size_small.inc +SET @save_frequency=@@GLOBAL.innodb_purge_rseg_truncate_frequency; +SET GLOBAL innodb_purge_rseg_truncate_frequency=1; LET $MYSQLD_DATADIR = `select @@datadir`; LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`; @@ -70,6 +72,8 @@ SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; # INNODB_SYS_TABLESTATS INSERT INTO parent VALUES(1); +--source include/wait_all_purged.inc + --sorted_result SELECT name, num_rows, ref_count FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS @@ -126,6 +130,8 @@ INSERT INTO parent VALUES(1, 9); --sorted_result SELECT * FROM parent WHERE id IN (SELECT id FROM parent); +--source include/wait_all_purged.inc + --sorted_result SELECT name, num_rows, ref_count FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS @@ -134,3 +140,4 @@ WHERE name LIKE "%parent"; DROP TABLE child; DROP TABLE parent; +SET GLOBAL innodb_purge_rseg_truncate_frequency=@save_frequency; diff --git a/mysql-test/suite/innodb/t/instant_alter.test b/mysql-test/suite/innodb/t/instant_alter.test index dc6fe45fa69..c4be8c19f5c 100644 --- a/mysql-test/suite/innodb/t/instant_alter.test +++ b/mysql-test/suite/innodb/t/instant_alter.test @@ -365,6 +365,30 @@ COMMIT; --source include/wait_all_purged.inc DROP TABLE t1; +# +# MDEV-20066 Wrong value on instantly added column after DELETE and UPDATE +# + +eval CREATE TABLE t1(a INT PRIMARY KEY) $engine; +INSERT INTO t1 VALUES (7); + +ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 0; +ALTER TABLE t1 ADD INDEX (c); + +BEGIN; +DELETE FROM t1; +INSERT INTO t1 VALUES (4,0),(7,77); +COMMIT; +BEGIN; +DELETE FROM t1 WHERE a=7; +UPDATE t1 SET a=7; +COMMIT; +SELECT * FROM t1 FORCE INDEX(PRIMARY); +SELECT * FROM t1 FORCE INDEX(c); +DELETE FROM t1; +CHECK TABLE t1; +DROP TABLE t1; + # MDEV-15562 Instant DROP/ADD/reorder columns eval CREATE TABLE t1 (a INT, b INT UNIQUE) $engine; diff --git a/mysql-test/suite/mariabackup/disabled.def b/mysql-test/suite/mariabackup/disabled.def new file mode 100644 index 00000000000..88a66df052d --- /dev/null +++ b/mysql-test/suite/mariabackup/disabled.def @@ -0,0 +1,12 @@ +############################################################################## +# +# List the test cases that are to be disabled temporarily. +# +# Separate the test case name and the comment with ':'. +# +# <testcasename> : BUG#<xxxx> <date disabled> <disabler> <comment> +# +# Do not use any TAB characters for whitespace. +# +############################################################################## +big_innodb_log : MDEV-20421 2019-08-26 wlad Always fails on Windows buildbot
\ No newline at end of file diff --git a/mysql-test/suite/rpl/r/rpl_extra_col_master_innodb.result b/mysql-test/suite/rpl/r/rpl_extra_col_master_innodb.result index 3aa7c07a845..5865b75550f 100644 --- a/mysql-test/suite/rpl/r/rpl_extra_col_master_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_extra_col_master_innodb.result @@ -468,7 +468,7 @@ INSERT INTO t10 () VALUES(1,@b1,DEFAULT,'Kyle',DEFAULT), connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'double' to type 'char(5)'' +Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'double' to type 'char(5 octets) character set latin1'' *** Drop t10 *** connection master; @@ -510,7 +510,7 @@ INSERT INTO t11 () VALUES(1,@b1,'Testing is fun','Kyle',DEFAULT), connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'tinyblob' to type 'varchar(254)'' +Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'blob' to type 'varchar(254 octets) character set latin1'' *** Drop t11 *** connection master; diff --git a/mysql-test/suite/rpl/r/rpl_extra_col_master_myisam.result b/mysql-test/suite/rpl/r/rpl_extra_col_master_myisam.result index 0918364b28e..f47d79b5b86 100644 --- a/mysql-test/suite/rpl/r/rpl_extra_col_master_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_extra_col_master_myisam.result @@ -468,7 +468,7 @@ INSERT INTO t10 () VALUES(1,@b1,DEFAULT,'Kyle',DEFAULT), connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'double' to type 'char(5)'' +Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'double' to type 'char(5 octets) character set latin1'' *** Drop t10 *** connection master; @@ -510,7 +510,7 @@ INSERT INTO t11 () VALUES(1,@b1,'Testing is fun','Kyle',DEFAULT), connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'tinyblob' to type 'varchar(254)'' +Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'blob' to type 'varchar(254 octets) character set latin1'' *** Drop t11 *** connection master; diff --git a/mysql-test/suite/rpl/r/rpl_extra_col_slave_innodb.result b/mysql-test/suite/rpl/r/rpl_extra_col_slave_innodb.result index 456c1c24f8f..a9cbbd9fafe 100644 --- a/mysql-test/suite/rpl/r/rpl_extra_col_slave_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_extra_col_slave_innodb.result @@ -64,7 +64,7 @@ a b c connection slave; START SLAVE; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t2' cannot be converted from type 'char(10)' to type 'char(5)'' +Last_SQL_Error = 'Column 2 of table 'test.t2' cannot be converted from type 'char(10 octets)' to type 'char(5 octets) character set latin1'' STOP SLAVE; RESET SLAVE; SELECT * FROM t2 ORDER BY a; @@ -102,7 +102,7 @@ INSERT INTO t3 () VALUES(@b1,2,'Kyle, TEX'),(@b1,1,'JOE AUSTIN'),(@b1,4,'QA TEST ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 0 of table 'test.t3' cannot be converted from type 'tinyblob' to type 'int(11)'' +Last_SQL_Error = 'Column 0 of table 'test.t3' cannot be converted from type 'blob' to type 'int(11)'' *** Drop t3 *** connection master; DROP TABLE t3; @@ -160,7 +160,7 @@ INSERT INTO t5 () VALUES(1,'Kyle',200.23,1,'b1b1',23.00098), ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'varchar(6)' to type 'char(5)'' +Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'varchar(6 octets)' to type 'char(5 octets) character set latin1'' *** Drop t5 *** connection master; DROP TABLE t5; @@ -188,7 +188,7 @@ INSERT INTO t6 () VALUES(1,'Kyle',200.23,1), ******************************************** connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'varchar(6)' to type 'char(5)'' +Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'varchar(6 octets)' to type 'char(5 octets) character set latin1'' *** Drop t6 *** include/rpl_reset.inc connection master; @@ -310,7 +310,7 @@ INSERT INTO t10 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'char(5)' to type 'double'' +Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'char(5 octets)' to type 'double'' *** Drop t10 *** connection master; DROP TABLE t10; @@ -338,7 +338,7 @@ INSERT INTO t11 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'varchar(254)' to type 'int(11)'' +Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'varchar(254 octets)' to type 'int(11)'' *** Drop t11 *** connection master; DROP TABLE t11; diff --git a/mysql-test/suite/rpl/r/rpl_extra_col_slave_myisam.result b/mysql-test/suite/rpl/r/rpl_extra_col_slave_myisam.result index 7178a2a78b8..11a716f22b6 100644 --- a/mysql-test/suite/rpl/r/rpl_extra_col_slave_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_extra_col_slave_myisam.result @@ -64,7 +64,7 @@ a b c connection slave; START SLAVE; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t2' cannot be converted from type 'char(10)' to type 'char(5)'' +Last_SQL_Error = 'Column 2 of table 'test.t2' cannot be converted from type 'char(10 octets)' to type 'char(5 octets) character set latin1'' STOP SLAVE; RESET SLAVE; SELECT * FROM t2 ORDER BY a; @@ -102,7 +102,7 @@ INSERT INTO t3 () VALUES(@b1,2,'Kyle, TEX'),(@b1,1,'JOE AUSTIN'),(@b1,4,'QA TEST ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 0 of table 'test.t3' cannot be converted from type 'tinyblob' to type 'int(11)'' +Last_SQL_Error = 'Column 0 of table 'test.t3' cannot be converted from type 'blob' to type 'int(11)'' *** Drop t3 *** connection master; DROP TABLE t3; @@ -160,7 +160,7 @@ INSERT INTO t5 () VALUES(1,'Kyle',200.23,1,'b1b1',23.00098), ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'varchar(6)' to type 'char(5)'' +Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'varchar(6 octets)' to type 'char(5 octets) character set latin1'' *** Drop t5 *** connection master; DROP TABLE t5; @@ -188,7 +188,7 @@ INSERT INTO t6 () VALUES(1,'Kyle',200.23,1), ******************************************** connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'varchar(6)' to type 'char(5)'' +Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'varchar(6 octets)' to type 'char(5 octets) character set latin1'' *** Drop t6 *** include/rpl_reset.inc connection master; @@ -310,7 +310,7 @@ INSERT INTO t10 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'char(5)' to type 'double'' +Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'char(5 octets)' to type 'double'' *** Drop t10 *** connection master; DROP TABLE t10; @@ -338,7 +338,7 @@ INSERT INTO t11 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'varchar(254)' to type 'int(11)'' +Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'varchar(254 octets)' to type 'int(11)'' *** Drop t11 *** connection master; DROP TABLE t11; diff --git a/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result b/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result index 1d4b31a4a87..98e95e8cc77 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result @@ -560,7 +560,7 @@ INSERT INTO t5 VALUES (1, "", 1); INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(255)' to type 'char(16)'' +Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(765 octets)' to type 'char(48 octets) character set utf8'' include/rpl_reset.inc [expecting slave to stop] connection master; @@ -568,7 +568,7 @@ INSERT INTO t6 VALUES (1, "", 1); INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(255)' to type 'char(128)'' +Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(765 octets)' to type 'char(384 octets) character set utf8'' include/rpl_reset.inc [expecting slave to replicate correctly] connection master; diff --git a/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result b/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result index 1e3ddd4f289..3203d5394df 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result @@ -565,7 +565,7 @@ INSERT INTO t5 VALUES (1, "", 1); INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(255)' to type 'char(16)'' +Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(765 octets)' to type 'char(48 octets) character set utf8'' include/rpl_reset.inc [expecting slave to stop] connection master; @@ -573,7 +573,7 @@ INSERT INTO t6 VALUES (1, "", 1); INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(255)' to type 'char(128)'' +Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(765 octets)' to type 'char(384 octets) character set utf8'' include/rpl_reset.inc [expecting slave to replicate correctly] connection master; diff --git a/mysql-test/suite/rpl/r/rpl_row_colSize.result b/mysql-test/suite/rpl/r/rpl_row_colSize.result index dd324ef7807..748d83a7f8e 100644 --- a/mysql-test/suite/rpl/r/rpl_row_colSize.result +++ b/mysql-test/suite/rpl/r/rpl_row_colSize.result @@ -185,7 +185,7 @@ INSERT INTO t1 VALUES ('This is a test.'); connection slave; START SLAVE; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'char(20)' to type 'char(10)'' +Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'char(20 octets)' to type 'char(10 octets) character set latin1'' SELECT COUNT(*) FROM t1; COUNT(*) 0 @@ -264,7 +264,7 @@ INSERT INTO t1 VALUES ('This is a test.'); connection slave; START SLAVE; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'varchar(2000)' to type 'varchar(100)'' +Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'varchar(2000 octets)' to type 'varchar(100 octets) character set latin1'' SELECT COUNT(*) FROM t1; COUNT(*) 0 @@ -287,7 +287,7 @@ INSERT INTO t1 VALUES ('This is a test.'); connection slave; START SLAVE; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'varchar(200)' to type 'varchar(10)'' +Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'varchar(200 octets)' to type 'varchar(10 octets) character set latin1'' SELECT COUNT(*) FROM t1; COUNT(*) 0 @@ -310,7 +310,7 @@ INSERT INTO t1 VALUES ('This is a test.'); connection slave; START SLAVE; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'varchar(2000)' to type 'varchar(1000)'' +Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'varchar(2000 octets)' to type 'varchar(1000 octets) character set latin1'' SELECT COUNT(*) FROM t1; COUNT(*) 0 @@ -334,7 +334,7 @@ INSERT INTO t1 VALUES ('This is a test.'); connection slave; START SLAVE; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'tinyblob' to type 'tinyblob'' +Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'longblob' to type 'tinyblob'' SELECT COUNT(*) FROM t1; COUNT(*) 0 diff --git a/mysql-test/suite/rpl/r/rpl_row_type_conv_err_msg.result b/mysql-test/suite/rpl/r/rpl_row_type_conv_err_msg.result new file mode 100644 index 00000000000..4b348425877 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_type_conv_err_msg.result @@ -0,0 +1,164 @@ +include/master-slave.inc +[connection master] +#################################################################### +# Test Case1: Improved error message with charset information +#################################################################### +connection master; +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb3'); +SET SQL_LOG_BIN=1; +connection slave; +CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb4'); +connection master; +INSERT INTO t1 VALUES ('a'); +connection slave; +include/wait_for_slave_sql_error.inc [errno=1677] +FOUND 1 /\'varchar\(3 octets\)\' to type \'varchar\(4 octets\) character set utf8mb4\'/ in mysqld.2.err +connection master; +DROP TABLE t1; +connection slave; +DROP TABLE t1; +include/rpl_reset.inc +#################################################################### +# Test Case2: Improved error message with charset information for CHAR +# type +#################################################################### +connection master; +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 CHAR(1) CHARACTER SET 'utf8mb3'); +SET SQL_LOG_BIN=1; +connection slave; +CREATE TABLE t1 (c1 CHAR(1) CHARACTER SET 'utf8mb4'); +connection master; +INSERT INTO t1 VALUES ('a'); +connection slave; +include/wait_for_slave_sql_error.inc [errno=1677] +FOUND 1 /\'char\(3 octets\)\' to type \'char\(4 octets\) character set utf8mb4\'/ in mysqld.2.err +connection master; +DROP TABLE t1; +connection slave; +DROP TABLE t1; +include/rpl_reset.inc +#################################################################### +# Test Case3: For BLOB type fileds, when type conversion failed on +# slave, the errormessage had incorrect type names. +#################################################################### +connection master; +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 LONGBLOB); +SET SQL_LOG_BIN=1; +connection slave; +CREATE TABLE t1 (c1 TINYBLOB); +connection master; +INSERT INTO t1 VALUES ('a'); +connection slave; +include/wait_for_slave_sql_error.inc [errno=1677] +FOUND 1 /\'longblob\' to type \'tinyblob\'/ in mysqld.2.err +connection master; +DROP TABLE t1; +connection slave; +DROP TABLE t1; +include/rpl_reset.inc +#################################################################### +# Test Case4: Verifies varbinary to binary type conversion failure +# specific error message. +#################################################################### +connection master; +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 VARBINARY(10)); +SET SQL_LOG_BIN=1; +connection slave; +CREATE TABLE t1 (c1 BINARY(10)); +connection master; +INSERT INTO t1 VALUES ('a'); +connection slave; +include/wait_for_slave_sql_error.inc [errno=1677] +FOUND 1 /\'varbinary\(10\)\' to type \'binary\(10\)\'/ in mysqld.2.err +connection master; +DROP TABLE t1; +connection slave; +DROP TABLE t1; +include/rpl_reset.inc +#################################################################### +# Test Case5: Verifies binary to varbinary type conversion failure +# specific error message. +#################################################################### +connection master; +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 BINARY(10)); +SET SQL_LOG_BIN=1; +connection slave; +CREATE TABLE t1 (c1 VARBINARY(10)); +connection master; +INSERT INTO t1 VALUES ('a'); +connection slave; +include/wait_for_slave_sql_error.inc [errno=1677] +FOUND 1 /\'binary\(10\)\' to type \'varbinary\(10\)\'/ in mysqld.2.err +connection master; +DROP TABLE t1; +connection slave; +DROP TABLE t1; +include/rpl_reset.inc +#################################################################### +# Test Case6: Verifies binary to binary type conversion failure +# specific error message. +#################################################################### +connection master; +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 BINARY(1)); +SET SQL_LOG_BIN=1; +connection slave; +CREATE TABLE t1 (c1 BINARY(10)); +connection master; +INSERT INTO t1 VALUES ('a'); +connection slave; +include/wait_for_slave_sql_error.inc [errno=1677] +FOUND 1 /\'binary\(1\)\' to type \'binary\(10\)\'/ in mysqld.2.err +connection master; +DROP TABLE t1; +connection slave; +DROP TABLE t1; +include/rpl_reset.inc +#################################################################### +# Test Case7: Verifies char to blob type conversion failure +# specific error message. BLOB field on slave has no +# associated character set hence the master side field +# is also considered as binary. +#################################################################### +connection master; +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 CHAR(1)); +SET SQL_LOG_BIN=1; +connection slave; +CREATE TABLE t1 (c1 BLOB); +connection master; +INSERT INTO t1 VALUES ('a'); +connection slave; +include/wait_for_slave_sql_error.inc [errno=1677] +FOUND 1 /\'binary\(1\)\' to type \'blob\'/ in mysqld.2.err +connection master; +DROP TABLE t1; +connection slave; +DROP TABLE t1; +include/rpl_reset.inc +#################################################################### +# Test Case8: Verifies char to text type conversion failure +# specific error message. +#################################################################### +connection master; +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 CHAR(1)); +SET SQL_LOG_BIN=1; +connection slave; +CREATE TABLE t1 (c1 TEXT); +connection master; +INSERT INTO t1 VALUES ('a'); +connection slave; +include/wait_for_slave_sql_error.inc [errno=1677] +FOUND 1 /\'char\(1 octets\)\' to type \'text\'/ in mysqld.2.err +connection master; +DROP TABLE t1; +connection slave; +DROP TABLE t1; +include/rpl_reset.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_row_type_conv_err_msg.test b/mysql-test/suite/rpl/t/rpl_row_type_conv_err_msg.test new file mode 100644 index 00000000000..2442f869abc --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_type_conv_err_msg.test @@ -0,0 +1,356 @@ +# ==== Purpose ==== +# +# Test verifies that when slave side type conversion fails in row based +# replication, more informative error message is displayed. It also verifies +# that in the case of blob fields appropriate type name is displayed in error +# message. +# +# ==== Implementation ==== +# +# Steps: +# Test case1: +# 1. Create a table on master with VARCHAR filed and charset +# 'utf8mb3'. +# 2. Create a table on slave with VARCHAR field and charset +# 'utf8mb4'. +# 3. Insert a tuple on master. +# 4. Verify that slave provides more informative error message with +# respect to difference in charsets. +# Test case2: Repeat same steps as above for CHAR field +# Test case3: +# 1. Create a table on master with LONGBLOB field. +# 2. Create a table on slave with TINYBLOB field. +# 3. Insert a tuple on master. +# 4. Verify that error message displayed on slave clearly states type +# conversion failure from 'longblob' to 'tinyblob'. +# 5. Also verify that error message doesn't show additional details +# of charset when not required. +# Test Case4: Verifies varbinary to binary type conversion failure specific +# error message. +# Test Case5: Verifies binary to varbinary type conversion failure specific +# error message. +# Test Case6: Verifies binary to binary type conversion failure specific +# error message. +# Test Case7: Verifies char to blob type conversion failure specific +# error message. +# Test Case8: Verifies char to text type conversion failure specific +# error message. +# ==== References ==== +# +# MDEV-19925: Column ... cannot be converted from type 'varchar(20)' to type +# 'varchar(20)' +# + +--source include/have_binlog_format_row.inc +# Inorder to grep a specific error pattern in error log a fresh error log +# needs to be generated. +--source include/force_restart.inc +--source include/master-slave.inc + +--echo #################################################################### +--echo # Test Case1: Improved error message with charset information +--echo #################################################################### +--connection master +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb3'); +SET SQL_LOG_BIN=1; + +--connection slave +CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb4'); + +--connection master +INSERT INTO t1 VALUES ('a'); + +--connection slave +--let $slave_sql_errno= 1677 +--source include/wait_for_slave_sql_error.inc + +# Check error log for correct messages. +let $log_error_= `SELECT @@GLOBAL.log_error`; +if(!$log_error_) +{ + # MySQL Server on windows is started with --console and thus + # does not know the location of its .err log, use default location + let $log_error_ = $MYSQLTEST_VARDIR/log/mysqld.2.err; +} + +# Error msg before: Column 0 of table 'test.t1' cannot be converted from type 'varchar(3)' to type 'varchar(1)' +# Error msg after : Column 0 of table 'test.t1' cannot be converted from type 'varchar(3 octets)' to type 'varchar(4 octets) character set utf8mb4' +--let SEARCH_FILE=$log_error_ +--let SEARCH_PATTERN=\'varchar\(3 octets\)\' to type \'varchar\(4 octets\) character set utf8mb4\' +--source include/search_pattern_in_file.inc + +--connection master +DROP TABLE t1; +--connection slave +DROP TABLE t1; +--let $rpl_only_running_threads= 1 +--source include/rpl_reset.inc + +--echo #################################################################### +--echo # Test Case2: Improved error message with charset information for CHAR +--echo # type +--echo #################################################################### +--connection master +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 CHAR(1) CHARACTER SET 'utf8mb3'); +SET SQL_LOG_BIN=1; + +--connection slave +CREATE TABLE t1 (c1 CHAR(1) CHARACTER SET 'utf8mb4'); + +--connection master +INSERT INTO t1 VALUES ('a'); + +--connection slave +--let $slave_sql_errno= 1677 +--source include/wait_for_slave_sql_error.inc + +# Error msg before: Column 0 of table 'test.t1' cannot be converted from type 'char(0)' to type 'char(1)' +# Error msg after : Column 0 of table 'test.t1' cannot be converted from type 'char(3 octets)' to type 'char(4 octets) character set utf8mb4)' +--let SEARCH_FILE=$log_error_ +--let SEARCH_PATTERN=\'char\(3 octets\)\' to type \'char\(4 octets\) character set utf8mb4\' +--source include/search_pattern_in_file.inc + +--connection master +DROP TABLE t1; +--connection slave +DROP TABLE t1; +--let $rpl_only_running_threads= 1 +--source include/rpl_reset.inc + +--echo #################################################################### +--echo # Test Case3: For BLOB type fileds, when type conversion failed on +--echo # slave, the errormessage had incorrect type names. +--echo #################################################################### +--connection master +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 LONGBLOB); +SET SQL_LOG_BIN=1; + +--connection slave +CREATE TABLE t1 (c1 TINYBLOB); + +--connection master +INSERT INTO t1 VALUES ('a'); + +--connection slave +--let $slave_sql_errno= 1677 +--source include/wait_for_slave_sql_error.inc + +# Error msg before: Column 0 of table 'test.t1' cannot be converted from type 'tinyblob' to type 'tinyblob' +# Error msg after : Column 0 of table 'test.t1' cannot be converted from type 'longblob' to type 'tinyblob' +--let SEARCH_FILE=$log_error_ +--let SEARCH_PATTERN=\'longblob\' to type \'tinyblob\' +--source include/search_pattern_in_file.inc + +--connection master +DROP TABLE t1; +--connection slave +DROP TABLE t1; +--let $rpl_only_running_threads= 1 +--source include/rpl_reset.inc + +--echo #################################################################### +--echo # Test Case4: Verifies varbinary to binary type conversion failure +--echo # specific error message. +--echo #################################################################### +--connection master +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 VARBINARY(10)); +SET SQL_LOG_BIN=1; + +--connection slave +CREATE TABLE t1 (c1 BINARY(10)); + +--connection master +INSERT INTO t1 VALUES ('a'); + +--connection slave +--let $slave_sql_errno= 1677 +--source include/wait_for_slave_sql_error.inc + +# Check error log for correct messages. +let $log_error_= `SELECT @@GLOBAL.log_error`; +if(!$log_error_) +{ + # MySQL Server on windows is started with --console and thus + # does not know the location of its .err log, use default location + let $log_error_ = $MYSQLTEST_VARDIR/log/mysqld.2.err; +} + +# Expected Error : Column 0 of table 'test.t1' cannot be converted from type 'varbinary(10)' to type 'binary(10)' +--let SEARCH_FILE=$log_error_ +--let SEARCH_PATTERN=\'varbinary\(10\)\' to type \'binary\(10\)\' +--source include/search_pattern_in_file.inc + +--connection master +DROP TABLE t1; +--connection slave +DROP TABLE t1; +--let $rpl_only_running_threads= 1 +--source include/rpl_reset.inc + +--echo #################################################################### +--echo # Test Case5: Verifies binary to varbinary type conversion failure +--echo # specific error message. +--echo #################################################################### +--connection master +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 BINARY(10)); +SET SQL_LOG_BIN=1; + +--connection slave +CREATE TABLE t1 (c1 VARBINARY(10)); + +--connection master +INSERT INTO t1 VALUES ('a'); + +--connection slave +--let $slave_sql_errno= 1677 +--source include/wait_for_slave_sql_error.inc + +# Check error log for correct messages. +let $log_error_= `SELECT @@GLOBAL.log_error`; +if(!$log_error_) +{ + # MySQL Server on windows is started with --console and thus + # does not know the location of its .err log, use default location + let $log_error_ = $MYSQLTEST_VARDIR/log/mysqld.2.err; +} + +# Expected Error : Column 0 of table 'test.t1' cannot be converted from type 'binary(10)' to type 'varbinary(10)' +--let SEARCH_FILE=$log_error_ +--let SEARCH_PATTERN=\'binary\(10\)\' to type \'varbinary\(10\)\' +--source include/search_pattern_in_file.inc + +--connection master +DROP TABLE t1; +--connection slave +DROP TABLE t1; +--let $rpl_only_running_threads= 1 +--source include/rpl_reset.inc + +--echo #################################################################### +--echo # Test Case6: Verifies binary to binary type conversion failure +--echo # specific error message. +--echo #################################################################### +--connection master +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 BINARY(1)); +SET SQL_LOG_BIN=1; + +--connection slave +CREATE TABLE t1 (c1 BINARY(10)); + +--connection master +INSERT INTO t1 VALUES ('a'); + +--connection slave +--let $slave_sql_errno= 1677 +--source include/wait_for_slave_sql_error.inc + +# Check error log for correct messages. +let $log_error_= `SELECT @@GLOBAL.log_error`; +if(!$log_error_) +{ + # MySQL Server on windows is started with --console and thus + # does not know the location of its .err log, use default location + let $log_error_ = $MYSQLTEST_VARDIR/log/mysqld.2.err; +} + +# Expected Error : Column 0 of table 'test.t1' cannot be converted from type 'binary(1)' to type 'binary(10)' +--let SEARCH_FILE=$log_error_ +--let SEARCH_PATTERN=\'binary\(1\)\' to type \'binary\(10\)\' +--source include/search_pattern_in_file.inc + +--connection master +DROP TABLE t1; +--connection slave +DROP TABLE t1; +--let $rpl_only_running_threads= 1 +--source include/rpl_reset.inc + +--echo #################################################################### +--echo # Test Case7: Verifies char to blob type conversion failure +--echo # specific error message. BLOB field on slave has no +--echo # associated character set hence the master side field +--echo # is also considered as binary. +--echo #################################################################### +--connection master +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 CHAR(1)); +SET SQL_LOG_BIN=1; + +--connection slave +CREATE TABLE t1 (c1 BLOB); + +--connection master +INSERT INTO t1 VALUES ('a'); + +--connection slave +--let $slave_sql_errno= 1677 +--source include/wait_for_slave_sql_error.inc + +# Check error log for correct messages. +let $log_error_= `SELECT @@GLOBAL.log_error`; +if(!$log_error_) +{ + # MySQL Server on windows is started with --console and thus + # does not know the location of its .err log, use default location + let $log_error_ = $MYSQLTEST_VARDIR/log/mysqld.2.err; +} + +# Expected Error : Column 0 of table 'test.t1' cannot be converted from type 'binary(1)' to type 'binary(10)' +--let SEARCH_FILE=$log_error_ +--let SEARCH_PATTERN=\'binary\(1\)\' to type \'blob\' +--source include/search_pattern_in_file.inc + +--connection master +DROP TABLE t1; +--connection slave +DROP TABLE t1; +--let $rpl_only_running_threads= 1 +--source include/rpl_reset.inc + +--echo #################################################################### +--echo # Test Case8: Verifies char to text type conversion failure +--echo # specific error message. +--echo #################################################################### +--connection master +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 CHAR(1)); +SET SQL_LOG_BIN=1; + +--connection slave +CREATE TABLE t1 (c1 TEXT); + +--connection master +INSERT INTO t1 VALUES ('a'); + +--connection slave +--let $slave_sql_errno= 1677 +--source include/wait_for_slave_sql_error.inc + +# Check error log for correct messages. +let $log_error_= `SELECT @@GLOBAL.log_error`; +if(!$log_error_) +{ + # MySQL Server on windows is started with --console and thus + # does not know the location of its .err log, use default location + let $log_error_ = $MYSQLTEST_VARDIR/log/mysqld.2.err; +} + +# Expected Error : Column 0 of table 'test.t1' cannot be converted from type 'binary(1)' to type 'binary(10)' +--let SEARCH_FILE=$log_error_ +--let SEARCH_PATTERN=\'char\(1 octets\)\' to type \'text\' +--source include/search_pattern_in_file.inc + +--connection master +DROP TABLE t1; +--connection slave +DROP TABLE t1; +--let $rpl_only_running_threads= 1 +--source include/rpl_reset.inc + +--source include/rpl_end.inc diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result index 3562d016266..2c7f4fed4fe 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result @@ -5,6 +5,7 @@ variable_name not in ( 'innodb_disallow_writes', # only available WITH_WSREP 'innodb_numa_interleave', # only available WITH_NUMA 'innodb_sched_priority_cleaner', # linux only +'innodb_evict_tables_on_commit_debug', # one may want to override this 'innodb_use_native_aio', # default value depends on OS 'innodb_buffer_pool_load_pages_abort') # debug build only, and is only for testing order by variable_name; diff --git a/mysql-test/suite/sys_vars/t/sysvars_innodb.test b/mysql-test/suite/sys_vars/t/sysvars_innodb.test index 9be60723f85..c573c5592b2 100644 --- a/mysql-test/suite/sys_vars/t/sysvars_innodb.test +++ b/mysql-test/suite/sys_vars/t/sysvars_innodb.test @@ -12,6 +12,7 @@ select * from information_schema.system_variables 'innodb_disallow_writes', # only available WITH_WSREP 'innodb_numa_interleave', # only available WITH_NUMA 'innodb_sched_priority_cleaner', # linux only + 'innodb_evict_tables_on_commit_debug', # one may want to override this 'innodb_use_native_aio', # default value depends on OS 'innodb_buffer_pool_load_pages_abort') # debug build only, and is only for testing order by variable_name; diff --git a/mysql-test/suite/versioning/r/cte.result b/mysql-test/suite/versioning/r/cte.result index 44135a83eb6..30003146da7 100644 --- a/mysql-test/suite/versioning/r/cte.result +++ b/mysql-test/suite/versioning/r/cte.result @@ -137,15 +137,15 @@ where e.mgr = a.emp_id ) select name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 4 100.00 -1 PRIMARY emp ALL PRIMARY NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY emp ALL PRIMARY NULL NULL NULL 4 100.00 Using where +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 4 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 4 100.00 2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where 3 RECURSIVE UNION e ALL mgr-fk NULL NULL NULL 4 100.00 Using where 3 RECURSIVE UNION <derived2> ref key0 key0 5 test.e.mgr 2 100.00 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `test`.`emp`.`name` AS `name` from `test`.`emp` FOR SYSTEM_TIME ALL semi join (`ancestors`) where `test`.`emp`.`emp_id` = `ancestors`.`emp_id` and `test`.`emp`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' +Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `test`.`emp`.`name` AS `name` from `test`.`emp` FOR SYSTEM_TIME ALL semi join (`ancestors`) where `test`.`emp`.`row_end` = TIMESTAMP'2038-01-19 03:14:07.999999' with recursive ancestors as diff --git a/mysys/my_static.c b/mysys/my_static.c index 7b367756c11..fc4c60bc237 100644 --- a/mysys/my_static.c +++ b/mysys/my_static.c @@ -1,5 +1,5 @@ /* Copyright (c) 2000, 2011, Oracle and/or its affiliates. - Copyright (c) 2009, 2012, Monty Program Ab. + Copyright (c) 2009, 2019, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -36,6 +36,9 @@ ulong my_file_total_opened= 0; int my_umask=0664, my_umask_dir=0777; myf my_global_flags= 0; +#ifndef DBUG_OFF +my_bool my_assert= 1; +#endif my_bool my_assert_on_error= 0; struct st_my_file_info my_file_info_default[MY_NFILE]; uint my_file_limit= MY_NFILE; diff --git a/scripts/wsrep_sst_common.sh b/scripts/wsrep_sst_common.sh index b1b586868df..16607470f2c 100755 --- a/scripts/wsrep_sst_common.sh +++ b/scripts/wsrep_sst_common.sh @@ -166,7 +166,11 @@ case "$1" in shift while [ $# -gt 0 ]; do option=${1%%=*} - if [ "$option" != "--defaults-file" ]; then + if [[ "$option" != "--defaults-file" && \ + "$option" != "--defaults-extra-file" && \ + "$option" != "--defaults-group-suffix" && \ + "$option" != "--port" && \ + "$option" != "--socket" ]]; then value=${1#*=} case "$option" in '--innodb-data-home-dir') @@ -193,7 +197,7 @@ case "$1" in if [ -z "$original_cmd" ]; then original_cmd="$1" else - original_cmd+=" $1" + original_cmd="$original_cmd $1" fi fi shift @@ -249,7 +253,15 @@ else MY_PRINT_DEFAULTS=my_print_defaults fi -readonly WSREP_SST_OPT_CONF="$WSREP_SST_OPT_DEFAULT $WSREP_SST_OPT_EXTRA_DEFAULT $WSREP_SST_OPT_SUFFIX_DEFAULT" +wsrep_defaults="$WSREP_SST_OPT_DEFAULT" +if [ -n "$wsrep_defaults" ]; then + wsrep_defaults="$wsrep_defaults " +fi +wsrep_defaults="$wsrep_defaults$WSREP_SST_OPT_EXTRA_DEFAULT" +if [ -n "$wsrep_defaults" ]; then + wsrep_defaults="$wsrep_defaults " +fi +readonly WSREP_SST_OPT_CONF="$wsrep_defaults$WSREP_SST_OPT_SUFFIX_DEFAULT" readonly MY_PRINT_DEFAULTS="$MY_PRINT_DEFAULTS $WSREP_SST_OPT_CONF" wsrep_auth_not_set() diff --git a/scripts/wsrep_sst_mariabackup.sh b/scripts/wsrep_sst_mariabackup.sh index 8e7b742d9ee..36deeff779d 100644 --- a/scripts/wsrep_sst_mariabackup.sh +++ b/scripts/wsrep_sst_mariabackup.sh @@ -60,7 +60,7 @@ rebuild=0 rebuildcmd="" payload=0 pvformat="-F '%N => Rate:%r Avg:%a Elapsed:%t %e Bytes: %b %p' " -pvopts="-f -i 10 -N $WSREP_SST_OPT_ROLE " +pvopts="-f -i 10 -N $WSREP_SST_OPT_ROLE " STATDIR="" uextra=0 disver="" @@ -130,7 +130,7 @@ get_keys() if [[ $encrypt -eq 0 ]];then if $MY_PRINT_DEFAULTS xtrabackup | grep -q encrypt;then - wsrep_log_error "Unexpected option combination. SST may fail. Refer to http://www.percona.com/doc/percona-xtradb-cluster/manual/xtrabackup_sst.html " + wsrep_log_error "Unexpected option combination. SST may fail. Refer to http://www.percona.com/doc/percona-xtradb-cluster/manual/xtrabackup_sst.html" fi return fi @@ -464,7 +464,7 @@ cleanup_donor() if [[ -n ${XTRABACKUP_PID:-} ]];then if check_pid $XTRABACKUP_PID then - wsrep_log_error "xtrabackup process is still running. Killing... " + wsrep_log_error "xtrabackup process is still running. Killing..." kill_xtrabackup fi @@ -566,7 +566,7 @@ check_extra() # Xtrabackup works only locally. # Hence, setting host to 127.0.0.1 unconditionally. wsrep_log_info "SST through extra_port $eport" - INNOEXTRA+=" --host=127.0.0.1 --port=$eport " + INNOEXTRA+=" --host=127.0.0.1 --port=$eport" use_socket=0 else wsrep_log_error "Extra port $eport null, failing" @@ -576,8 +576,8 @@ check_extra() wsrep_log_info "Thread pool not set, ignore the option use_extra" fi fi - if [[ $use_socket -eq 1 ]] && [[ -n "${WSREP_SST_OPT_SOCKET}" ]];then - INNOEXTRA+=" --socket=${WSREP_SST_OPT_SOCKET}" + if [[ $use_socket -eq 1 ]] && [[ -n "$WSREP_SST_OPT_SOCKET" ]];then + INNOEXTRA+=" --socket=$WSREP_SST_OPT_SOCKET" fi } @@ -743,8 +743,8 @@ if [[ $ssyslog -eq 1 ]];then logger -p daemon.info -t ${ssystag}wsrep-sst-$WSREP_SST_OPT_ROLE "$@" } - INNOAPPLY="${INNOBACKUPEX_BIN} --innobackupex $disver $iapts \$INNOEXTRA --apply-log \$rebuildcmd \${DATA} 2>&1 | logger -p daemon.err -t ${ssystag}innobackupex-apply " - INNOMOVE="${INNOBACKUPEX_BIN} --innobackupex ${WSREP_SST_OPT_CONF} $disver $impts --move-back --force-non-empty-directories \${DATA} 2>&1 | logger -p daemon.err -t ${ssystag}innobackupex-move " + INNOAPPLY="${INNOBACKUPEX_BIN} --innobackupex $disver $iapts \$INNOEXTRA --apply-log \$rebuildcmd \${DATA} 2>&1 | logger -p daemon.err -t ${ssystag}innobackupex-apply" + INNOMOVE="${INNOBACKUPEX_BIN} --innobackupex ${WSREP_SST_OPT_CONF} $disver $impts --move-back --force-non-empty-directories \${DATA} 2>&1 | logger -p daemon.err -t ${ssystag}innobackupex-move" INNOBACKUP="${INNOBACKUPEX_BIN} --innobackupex ${WSREP_SST_OPT_CONF} $disver $iopts \$tmpopts \$INNOEXTRA --galera-info --stream=\$sfmt \$itmpdir 2> >(logger -p daemon.err -t ${ssystag}innobackupex-backup)" fi @@ -832,7 +832,7 @@ then -z $(parse_cnf --mysqld tmpdir "") && \ -z $(parse_cnf xtrabackup tmpdir "") ]]; then xtmpdir=$(mktemp -d) - tmpopts=" --tmpdir=$xtmpdir " + tmpopts=" --tmpdir=$xtmpdir" wsrep_log_info "Using $xtmpdir as xtrabackup temporary directory" fi @@ -854,13 +854,12 @@ then get_keys if [[ $encrypt -eq 1 ]];then if [[ -n $ekey ]];then - INNOEXTRA+=" --encrypt=$ealgo --encrypt-key=$ekey " + INNOEXTRA+=" --encrypt=$ealgo --encrypt-key=$ekey" else - INNOEXTRA+=" --encrypt=$ealgo --encrypt-key-file=$ekeyfile " + INNOEXTRA+=" --encrypt=$ealgo --encrypt-key-file=$ekeyfile" fi fi - check_extra wsrep_log_info "Streaming GTID file before SST" @@ -881,7 +880,6 @@ then tcmd=" $scomp | $tcmd " fi - send_donor $DATA "${stagemsg}-gtid" tcmd="$ttcmd" @@ -1049,7 +1047,7 @@ then tempdir=$LOG_BIN_ARG if [ -z "$tempdir" ]; then - tempdir=$(parse_cnf mysqld$WSREP_SST_OPT_SUFFIX_VALUE log-bin "") + tempdir=$(parse_cnf mysqld$WSREP_SST_OPT_SUFFIX_VALUE log-bin "") fi if [ -z "$tempdir" ]; then tempdir=$(parse_cnf --mysqld log-bin "") diff --git a/sql/create_options.cc b/sql/create_options.cc index 5adcb2f1e9e..a8d997efaf4 100644 --- a/sql/create_options.cc +++ b/sql/create_options.cc @@ -1,4 +1,4 @@ -/* Copyright (C) 2010, 2017, MariaDB Corporation Ab +/* Copyright (C) 2010, 2019, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -132,7 +132,8 @@ static bool set_one_value(ha_create_table_option *opt, switch (opt->type) { case HA_OPTION_TYPE_SYSVAR: - DBUG_ASSERT(0); // HA_OPTION_TYPE_SYSVAR's are replaced in resolve_sysvars() + // HA_OPTION_TYPE_SYSVAR's are replaced in resolve_sysvars() + break; // to DBUG_ASSERT(0) case HA_OPTION_TYPE_ULL: { ulonglong *val= (ulonglong*)value_ptr(base, opt); diff --git a/sql/field.cc b/sql/field.cc index 969c32a5180..750e5bce651 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -7315,6 +7315,28 @@ void Field_string::sql_type(String &res) const res.append(STRING_WITH_LEN(" binary")); } +/** + For fields which are associated with character sets their length is provided + in octets and their character set information is also provided as part of + type information. + + @param res String which contains filed type and length. +*/ +void Field_string::sql_rpl_type(String *res) const +{ + CHARSET_INFO *cs=charset(); + if (Field_string::has_charset()) + { + size_t length= cs->cset->snprintf(cs, (char*) res->ptr(), + res->alloced_length(), + "char(%u octets) character set %s", + field_length, + charset()->csname); + res->length(length); + } + else + Field_string::sql_type(*res); + } uchar *Field_string::pack(uchar *to, const uchar *from, uint max_length) { @@ -7735,6 +7757,29 @@ void Field_varstring::sql_type(String &res) const res.append(STRING_WITH_LEN(" binary")); } +/** + For fields which are associated with character sets their length is provided + in octets and their character set information is also provided as part of + type information. + + @param res String which contains filed type and length. +*/ +void Field_varstring::sql_rpl_type(String *res) const +{ + CHARSET_INFO *cs=charset(); + if (Field_varstring::has_charset()) + { + size_t length= cs->cset->snprintf(cs, (char*) res->ptr(), + res->alloced_length(), + "varchar(%u octets) character set %s", + field_length, + charset()->csname); + res->length(length); + } + else + Field_varstring::sql_type(*res); +} + uint32 Field_varstring::data_length() { diff --git a/sql/field.h b/sql/field.h index 79bc55d2323..8bbcaed7e39 100644 --- a/sql/field.h +++ b/sql/field.h @@ -1,7 +1,7 @@ #ifndef FIELD_INCLUDED #define FIELD_INCLUDED /* Copyright (c) 2000, 2015, Oracle and/or its affiliates. - Copyright (c) 2008, 2017, MariaDB Corporation. + Copyright (c) 2008, 2019, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -540,6 +540,7 @@ public: /* Flag indicating that the field is physically stored in the database */ bool stored_in_db; bool utf8; /* Already in utf8 */ + bool automatic_name; Item *expr; Lex_ident name; /* Name of constraint */ /* see VCOL_* (VCOL_FIELD_REF, ...) */ @@ -549,7 +550,7 @@ public: :Type_handler_hybrid_field_type(&type_handler_null), vcol_type((enum_vcol_info_type)VCOL_TYPE_NONE), in_partitioning_expr(FALSE), stored_in_db(FALSE), - utf8(TRUE), expr(NULL), flags(0) + utf8(TRUE), automatic_name(FALSE), expr(NULL), flags(0) { name.str= NULL; name.length= 0; @@ -1148,6 +1149,7 @@ public: in str and restore it with set() if needed */ virtual void sql_type(String &str) const =0; + virtual void sql_rpl_type(String *str) const { sql_type(*str); } virtual uint size_of() const =0; // For new field inline bool is_null(my_ptrdiff_t row_offset= 0) const { @@ -3576,6 +3578,7 @@ public: int cmp(const uchar *,const uchar *); void sort_string(uchar *buff,uint length); void sql_type(String &str) const; + void sql_rpl_type(String*) const; bool is_equal(const Column_definition &new_field) const; bool can_be_converted_by_engine(const Column_definition &new_type) const { @@ -3692,6 +3695,7 @@ public: uint get_key_image(uchar *buff,uint length, imagetype type); void set_key_image(const uchar *buff,uint length); void sql_type(String &str) const; + void sql_rpl_type(String*) const; virtual uchar *pack(uchar *to, const uchar *from, uint max_length); virtual const uchar *unpack(uchar* to, const uchar *from, const uchar *from_end, uint param_data); diff --git a/sql/item.cc b/sql/item.cc index eaec0a2a737..24747d1aae1 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -3768,6 +3768,20 @@ my_decimal *Item_null::val_decimal(my_decimal *decimal_value) } +longlong Item_null::val_datetime_packed(THD *) +{ + null_value= true; + return 0; +} + + +longlong Item_null::val_time_packed(THD *) +{ + null_value= true; + return 0; +} + + bool Item_null::get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate) { set_zero_time(ltime, MYSQL_TIMESTAMP_NONE); @@ -8212,6 +8226,24 @@ bool Item_ref::val_native(THD *thd, Native *to) } +longlong Item_ref::val_datetime_packed(THD *thd) +{ + DBUG_ASSERT(fixed); + longlong tmp= (*ref)->val_datetime_packed(thd); + null_value= (*ref)->null_value; + return tmp; +} + + +longlong Item_ref::val_time_packed(THD *thd) +{ + DBUG_ASSERT(fixed); + longlong tmp= (*ref)->val_time_packed(thd); + null_value= (*ref)->null_value; + return tmp; +} + + my_decimal *Item_ref::val_decimal(my_decimal *decimal_value) { my_decimal *val= (*ref)->val_decimal_result(decimal_value); diff --git a/sql/item.h b/sql/item.h index 5cda78d42fa..75c0fbc67fe 100644 --- a/sql/item.h +++ b/sql/item.h @@ -3557,6 +3557,8 @@ public: String *val_str(String *str); my_decimal *val_decimal(my_decimal *); bool get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate); + longlong val_datetime_packed(THD *); + longlong val_time_packed(THD *); int save_in_field(Field *field, bool no_conversions); int save_safe_in_field(Field *field); bool send(Protocol *protocol, st_value *buffer); @@ -5179,6 +5181,8 @@ public: bool val_native(THD *thd, Native *to); bool is_null(); bool get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate); + longlong val_datetime_packed(THD *); + longlong val_time_packed(THD *); double val_result(); longlong val_int_result(); String *str_result(String* tmp); diff --git a/sql/mysqld.cc b/sql/mysqld.cc index fc7e82936ea..a4118ced910 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -6719,6 +6719,10 @@ struct my_option my_long_options[]= 0, GET_INT, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, #endif /* HAVE_REPLICATION */ #ifndef DBUG_OFF + {"debug-assert", 0, + "Allow DBUG_ASSERT() to invoke assert()", + &my_assert, &my_assert, + 0, GET_BOOL, OPT_ARG, 1, 0, 0, 0, 0, 0}, {"debug-assert-on-error", 0, "Do an assert in various functions if we get a fatal error", &my_assert_on_error, &my_assert_on_error, diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 9205380bd19..a10c8de417f 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -2460,7 +2460,7 @@ bool optimize_semijoin_nests(JOIN *join, table_map all_table_map) &subjoin_out_rows); sjm->materialization_cost.convert_from_cost(subjoin_read_time); - sjm->rows= subjoin_out_rows; + sjm->rows_with_duplicates= sjm->rows= subjoin_out_rows; // Don't use the following list because it has "stale" items. use // ref_pointer_array instead: @@ -3123,11 +3123,14 @@ bool Sj_materialization_picker::check_qep(JOIN *join, disable_jbuf, prefix_rec_count, &curpos, &dummy); prefix_rec_count= COST_MULT(prefix_rec_count, curpos.records_read); prefix_cost= COST_ADD(prefix_cost, curpos.read_time); + prefix_cost= COST_ADD(prefix_cost, + prefix_rec_count / (double) TIME_FOR_COMPARE); + //TODO: take into account join condition selectivity here } *strategy= SJ_OPT_MATERIALIZE_SCAN; *read_time= prefix_cost; - *record_count= prefix_rec_count; + *record_count= prefix_rec_count / mat_info->rows_with_duplicates; *handled_fanout= mat_nest->sj_inner_tables; if (unlikely(join->thd->trace_started())) { diff --git a/sql/rpl_utility.cc b/sql/rpl_utility.cc index b36838b9b22..437d58d772f 100644 --- a/sql/rpl_utility.cc +++ b/sql/rpl_utility.cc @@ -352,7 +352,8 @@ uint32 table_def::calc_field_size(uint col, uchar *master_data) const #if !defined(MYSQL_CLIENT) && defined(HAVE_REPLICATION) /** */ -void show_sql_type(enum_field_types type, uint16 metadata, String *str, CHARSET_INFO *field_cs) +void show_sql_type(enum_field_types type, uint16 metadata, String *str, + bool char_with_octets) { DBUG_ENTER("show_sql_type"); DBUG_PRINT("enter", ("type: %d, metadata: 0x%x", type, metadata)); @@ -420,11 +421,13 @@ void show_sql_type(enum_field_types type, uint16 metadata, String *str, CHARSET_ case MYSQL_TYPE_VARCHAR_COMPRESSED: { CHARSET_INFO *cs= str->charset(); - size_t length= - cs->cset->snprintf(cs, (char*) str->ptr(), str->alloced_length(), - "varchar(%u)%s", metadata, - type == MYSQL_TYPE_VARCHAR_COMPRESSED ? " compressed" - : ""); + size_t length=0; + if (char_with_octets) + length= cs->cset->snprintf(cs, (char*) str->ptr(), str->alloced_length(), + "varchar(%u octets)", metadata); + else + length= cs->cset->snprintf(cs, (char*) str->ptr(), str->alloced_length(), + "varbinary(%u)", metadata); str->length(length); } break; @@ -475,22 +478,22 @@ void show_sql_type(enum_field_types type, uint16 metadata, String *str, CHARSET_ it is necessary to check the pack length to figure out what kind of blob it really is. */ - switch (get_blob_type_from_length(metadata)) + switch (metadata) { - case MYSQL_TYPE_TINY_BLOB: + case 1: str->set_ascii(STRING_WITH_LEN("tinyblob")); break; - case MYSQL_TYPE_MEDIUM_BLOB: - str->set_ascii(STRING_WITH_LEN("mediumblob")); + case 2: + str->set_ascii(STRING_WITH_LEN("blob")); break; - case MYSQL_TYPE_LONG_BLOB: - str->set_ascii(STRING_WITH_LEN("longblob")); + case 3: + str->set_ascii(STRING_WITH_LEN("mediumblob")); break; - case MYSQL_TYPE_BLOB: - str->set_ascii(STRING_WITH_LEN("blob")); + case 4: + str->set_ascii(STRING_WITH_LEN("longblob")); break; default: @@ -509,9 +512,13 @@ void show_sql_type(enum_field_types type, uint16 metadata, String *str, CHARSET_ */ CHARSET_INFO *cs= str->charset(); uint bytes= (((metadata >> 4) & 0x300) ^ 0x300) + (metadata & 0x00ff); - size_t length= - cs->cset->snprintf(cs, (char*) str->ptr(), str->alloced_length(), - "char(%d)", bytes / field_cs->mbmaxlen); + size_t length=0; + if (char_with_octets) + length= cs->cset->snprintf(cs, (char*) str->ptr(), str->alloced_length(), + "char(%u octets)", bytes); + else + length= cs->cset->snprintf(cs, (char*) str->ptr(), str->alloced_length(), + "binary(%u)", bytes); str->length(length); } break; @@ -948,9 +955,13 @@ table_def::compatible_with(THD *thd, rpl_group_info *rgi, String source_type(source_buf, sizeof(source_buf), &my_charset_latin1); String target_type(target_buf, sizeof(target_buf), &my_charset_latin1); THD *thd= table->in_use; + bool char_with_octets= field->cmp_type() == STRING_RESULT ? + field->has_charset() : true; + + show_sql_type(type(col), field_metadata(col), &source_type, + char_with_octets); + field->sql_rpl_type(&target_type); - show_sql_type(type(col), field_metadata(col), &source_type, field->charset()); - field->sql_type(target_type); rli->report(ERROR_LEVEL, ER_SLAVE_CONVERSION_FAILED, rgi->gtid_info(), ER_THD(thd, ER_SLAVE_CONVERSION_FAILED), col, db_name, tbl_name, diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index c64f60f3562..c91132c1eae 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -6461,7 +6461,7 @@ ER_MESSAGE_AND_STATEMENT eng "%s Statement: %s" ER_SLAVE_CONVERSION_FAILED - eng "Column %d of table '%-.192s.%-.192s' cannot be converted from type '%-.32s' to type '%-.32s'" + eng "Column %d of table '%-.192s.%-.192s' cannot be converted from type '%-.50s' to type '%-.50s'" ER_SLAVE_CANT_CREATE_CONVERSION eng "Can't create conversion table for table '%-.192s.%-.192s'" ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT diff --git a/sql/sql_class.h b/sql/sql_class.h index 2b46fc69365..5ffc8bde269 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -5995,7 +5995,10 @@ public: uint tables; /* Number of tables in the sj-nest */ - /* Expected #rows in the materialized table */ + /* Number of rows in the materialized table, before the de-duplication */ + double rows_with_duplicates; + + /* Expected #rows in the materialized table, after de-duplication */ double rows; /* diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index a1ee99f29ec..21473c28b84 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2993,6 +2993,7 @@ void st_select_lex_unit::print(String *str, enum_query_type query_type) { default: DBUG_ASSERT(0); + /* fall through */ case UNION_TYPE: str->append(STRING_WITH_LEN(" union ")); if (union_all) diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 1f612ff2266..ab915bf3fcc 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3623,6 +3623,7 @@ mysql_execute_command(THD *thd) case GET_NO_ARG: case GET_DISABLED: DBUG_ASSERT(0); + /* fall through */ case 0: case GET_FLAGSET: case GET_ENUM: diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 150e8008096..bd6d9852f40 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -8788,6 +8788,7 @@ void JOIN::get_prefix_cost_and_fanout(uint n_tables, record_count= COST_MULT(record_count, best_positions[i].records_read); read_time= COST_ADD(read_time, best_positions[i].read_time); } + /* TODO: Take into account condition selectivities here */ } *read_time_arg= read_time;// + record_count / TIME_FOR_COMPARE; *record_count_arg= record_count; @@ -16678,10 +16679,20 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab, reopt_remaining_tables &= ~rs->table->map; rec_count= COST_MULT(rec_count, pos.records_read); cost= COST_ADD(cost, pos.read_time); - - + cost= COST_ADD(cost, rec_count / (double) TIME_FOR_COMPARE); + //TODO: take into account join condition selectivity here + double pushdown_cond_selectivity= 1.0; + table_map real_table_bit= rs->table->map; + if (join->thd->variables.optimizer_use_condition_selectivity > 1) + { + pushdown_cond_selectivity= table_cond_selectivity(join, i, rs, + reopt_remaining_tables & + ~real_table_bit); + } + (*outer_rec_count) *= pushdown_cond_selectivity; if (!rs->emb_sj_nest) *outer_rec_count= COST_MULT(*outer_rec_count, pos.records_read); + } join->cur_sj_inner_tables= save_cur_sj_inner_tables; diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 2b9a5a6ea29..e94bafedb83 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -2593,6 +2593,7 @@ static const LEX_CSTRING *view_algorithm(TABLE_LIST *table) return &merge; default: DBUG_ASSERT(0); // never should happen + /* fall through */ case VIEW_ALGORITHM_UNDEFINED: return &undefined; } diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 36b2174c8fd..d6d6c083432 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -64,7 +64,7 @@ const char *primary_key_name="PRIMARY"; static int check_if_keyname_exists(const char *name,KEY *start, KEY *end); static char *make_unique_key_name(THD *, const char *, KEY *, KEY *); -static void make_unique_constraint_name(THD *, LEX_CSTRING *, const char *, +static bool make_unique_constraint_name(THD *, LEX_CSTRING *, const char *, List<Virtual_column_info> *, uint *); static const char *make_unique_invisible_field_name(THD *, const char *, List<Create_field> *); @@ -76,6 +76,9 @@ static int copy_data_between_tables(THD *, TABLE *,TABLE *, static int mysql_prepare_create_table(THD *, HA_CREATE_INFO *, Alter_info *, uint *, handler *, KEY **, uint *, int); static uint blob_length_by_type(enum_field_types type); +static bool fix_constraints_names(THD *thd, List<Virtual_column_info> + *check_constraint_list, + const HA_CREATE_INFO *create_info); /** @brief Helper function for explain_filename @@ -4311,20 +4314,13 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info, /* Check table level constraints */ create_info->check_constraint_list= &alter_info->check_constraint_list; { - uint nr= 1; List_iterator_fast<Virtual_column_info> c_it(alter_info->check_constraint_list); Virtual_column_info *check; while ((check= c_it++)) { - if (!check->name.length) - { - const char *own_name_base= create_info->period_info.constr == check - ? create_info->period_info.name.str : NULL; + if (!check->name.length || check->automatic_name) + continue; - make_unique_constraint_name(thd, &check->name, own_name_base, - &alter_info->check_constraint_list, - &nr); - } { /* Check that there's no repeating constraint names. */ List_iterator_fast<Virtual_column_info> @@ -4869,6 +4865,10 @@ int create_table_impl(THD *thd, const LEX_CSTRING &orig_db, DBUG_PRINT("enter", ("db: '%s' table: '%s' tmp: %d path: %s", db.str, table_name.str, internal_tmp_table, path)); + if (fix_constraints_names(thd, &alter_info->check_constraint_list, + create_info)) + DBUG_RETURN(1); + if (thd->variables.sql_mode & MODE_NO_DIR_IN_CREATE) { if (create_info->data_file_name) @@ -5367,7 +5367,7 @@ make_unique_key_name(THD *thd, const char *field_name,KEY *start,KEY *end) Make an unique name for constraints without a name */ -static void make_unique_constraint_name(THD *thd, LEX_CSTRING *name, +static bool make_unique_constraint_name(THD *thd, LEX_CSTRING *name, const char *own_name_base, List<Virtual_column_info> *vcol, uint *nr) @@ -5395,9 +5395,10 @@ static void make_unique_constraint_name(THD *thd, LEX_CSTRING *name, { name->length= (size_t) (real_end - buff); name->str= thd->strmake(buff, name->length); - return; + return (name->str == NULL); } } + return FALSE; } /** @@ -6020,10 +6021,11 @@ static bool is_candidate_key(KEY *key) from the list if existing found. RETURN VALUES - NONE + TRUE error + FALSE OK */ -static void +static bool handle_if_exists_options(THD *thd, TABLE *table, Alter_info *alter_info, Table_period_info *period_info) { @@ -6469,6 +6471,7 @@ remove_key: Virtual_column_info *check; TABLE_SHARE *share= table->s; uint c; + while ((check=it++)) { if (!(check->flags & Alter_info::CHECK_CONSTRAINT_IF_NOT_EXISTS) && @@ -6516,7 +6519,48 @@ remove_key: *period_info= {}; } - DBUG_VOID_RETURN; + DBUG_RETURN(false); +} + + +static bool fix_constraints_names(THD *thd, List<Virtual_column_info> + *check_constraint_list, + const HA_CREATE_INFO *create_info) +{ + List_iterator<Virtual_column_info> it((*check_constraint_list)); + Virtual_column_info *check; + uint nr= 1; + DBUG_ENTER("fix_constraints_names"); + if (!check_constraint_list) + DBUG_RETURN(FALSE); + // Prevent accessing freed memory during generating unique names + while ((check=it++)) + { + if (check->automatic_name) + { + check->name.str= NULL; + check->name.length= 0; + } + } + it.rewind(); + // Generate unique names if needed + while ((check=it++)) + { + if (!check->name.length) + { + check->automatic_name= TRUE; + + const char *own_name_base= create_info->period_info.constr == check + ? create_info->period_info.name.str : NULL; + + if (make_unique_constraint_name(thd, &check->name, + own_name_base, + check_constraint_list, + &nr)) + DBUG_RETURN(TRUE); + } + } + DBUG_RETURN(FALSE); } @@ -9657,7 +9701,11 @@ do_continue:; } } - handle_if_exists_options(thd, table, alter_info, &create_info->period_info); + if (handle_if_exists_options(thd, table, alter_info, + &create_info->period_info) || + fix_constraints_names(thd, &alter_info->check_constraint_list, + create_info)) + DBUG_RETURN(true); /* Look if we have to do anything at all. diff --git a/sql/table.cc b/sql/table.cc index da0c4be41b0..4f6399d84ed 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -9444,8 +9444,7 @@ bool vers_select_conds_t::eq(const vers_select_conds_t &conds) const case SYSTEM_TIME_ALL: return true; case SYSTEM_TIME_BEFORE: - DBUG_ASSERT(0); - return false; + break; case SYSTEM_TIME_AS_OF: return start.eq(conds.start); case SYSTEM_TIME_FROM_TO: diff --git a/sql/wsrep_sst.cc b/sql/wsrep_sst.cc index 62b30c0d67d..74a8b9dff05 100644 --- a/sql/wsrep_sst.cc +++ b/sql/wsrep_sst.cc @@ -16,6 +16,7 @@ #include "mariadb.h" #include "wsrep_sst.h" #include <inttypes.h> +#include <ctype.h> #include <mysqld.h> #include <m_ctype.h> #include <strfunc.h> @@ -309,7 +310,31 @@ static char* my_fgets (char* buf, size_t buf_len, FILE* stream) } /* - Generate opt_binlog_opt_val for sst_donate_other(), sst_prepare_other(). + Generate "name 'value'" string. +*/ +static char* generate_name_value(const char* name, const char* value) +{ + size_t name_len= strlen(name); + size_t value_len= strlen(value); + char* buf= + (char*) my_malloc((name_len + value_len + 5) * sizeof(char), MYF(0)); + if (buf) + { + char* ref= buf; + *ref++ = ' '; + memcpy(ref, name, name_len * sizeof(char)); + ref += name_len; + *ref++ = ' '; + *ref++ = '\''; + memcpy(ref, value, value_len * sizeof(char)); + ref += value_len; + *ref++ = '\''; + *ref = 0; + } + return buf; +} +/* + Generate binlog option string for sst_donate_other(), sst_prepare_other(). Returns zero on success, negative error code otherwise. @@ -325,7 +350,9 @@ static int generate_binlog_opt_val(char** ret) { assert(opt_bin_logname); *ret= strcmp(opt_bin_logname, "0") ? - my_strdup(opt_bin_logname, MYF(0)) : my_strdup("", MYF(0)); + generate_name_value(WSREP_SST_OPT_BINLOG, + opt_bin_logname) : + my_strdup("", MYF(0)); } else { @@ -342,7 +369,9 @@ static int generate_binlog_index_opt_val(char** ret) if (opt_binlog_index_name) { *ret= strcmp(opt_binlog_index_name, "0") ? - my_strdup(opt_binlog_index_name, MYF(0)) : my_strdup("", MYF(0)); + generate_name_value(WSREP_SST_OPT_BINLOG_INDEX, + opt_binlog_index_name) : + my_strdup("", MYF(0)); } else { @@ -595,7 +624,86 @@ static size_t estimate_cmd_len (bool* extra_args) { for (int i = 1; i < orig_argc; i++) { - cmd_len += strlen(orig_argv[i]); + const char* arg= orig_argv[i]; + size_t n= strlen(arg); + if (n == 0) continue; + cmd_len += n; + bool quotation= false; + char c; + while ((c = *arg++) != 0) + { + /* A whitespace or a single quote requires double quotation marks: */ + if (isspace(c) || c == '\'') + { + quotation= true; + } + /* + If the equals symbol is encountered, then we need to separately + process the right side: + */ + else if (c == '=') + { + /* Perhaps we need to quote the left part of the argument: */ + if (quotation) + { + cmd_len += 2; + /* + Reset the quotation flag, since now the status for + the right side of the expression will be saved here: + */ + quotation= false; + } + while ((c = *arg++) != 0) + { + /* + A whitespace or a single quote requires double + quotation marks: + */ + if (isspace(c) || c == '\'') + { + quotation= true; + } + /* + Double quotation mark or backslash symbol requires backslash + prefixing: + */ +#ifdef __WIN__ + else if (c == '"' || c == '\\') +#else + /* + The dollar symbol is used to substitute a variable, therefore + it also requires escaping: + */ + else if (c == '"' || c == '\\' || c == '$') +#endif + { + cmd_len++; + } + } + break; + } + /* + Double quotation mark or backslash symbol requires backslash + prefixing: + */ +#ifdef __WIN__ + else if (c == '"' || c == '\\') +#else + /* + The dollar symbol is used to substitute a variable, therefore + it also requires escaping: + */ + else if (c == '"' || c == '\\' || c == '$') +#endif + { + cmd_len++; + } + } + /* Perhaps we need to quote the entire argument or its right part: */ + if (quotation) + { + cmd_len += 2; + } } extra = true; cmd_len += strlen(WSREP_SST_OPT_MYSQLD); @@ -623,10 +731,171 @@ static void copy_orig_argv (char* cmd_str) for (int i = 1; i < orig_argc; i++) { char* arg= orig_argv[i]; - *cmd_str++ = ' '; n = strlen(arg); - memcpy(cmd_str, arg, n * sizeof(char)); - cmd_str += n; + if (n == 0) continue; + *cmd_str++ = ' '; + bool quotation= false; + bool plain= true; + char *arg_scan= arg; + char c; + while ((c = *arg_scan++) != 0) + { + /* A whitespace or a single quote requires double quotation marks: */ + if (isspace(c) || c == '\'') + { + quotation= true; + } + /* + If the equals symbol is encountered, then we need to separately + process the right side: + */ + else if (c == '=') + { + /* Calculate length of the Left part of the argument: */ + size_t m = (size_t) (arg_scan - arg) - 1; + if (m) + { + /* Perhaps we need to quote the left part of the argument: */ + if (quotation) + { + *cmd_str++ = '"'; + } + /* + If there were special characters inside, then we can use + the fast memcpy function: + */ + if (plain) + { + memcpy(cmd_str, arg, m * sizeof(char)); + cmd_str += m; + /* Left part of the argument has already been processed: */ + n -= m; + arg += m; + } + /* Otherwise we need to prefix individual characters: */ + else + { + n -= m; + while (m) + { + c = *arg++; +#ifdef __WIN__ + if (c == '"' || c == '\\') +#else + if (c == '"' || c == '\\' || c == '$') +#endif + { + *cmd_str++ = '\\'; + } + *cmd_str++ = c; + m--; + } + /* + Reset the plain string flag, since now the status for + the right side of the expression will be saved here: + */ + plain= true; + } + /* Perhaps we need to quote the left part of the argument: */ + if (quotation) + { + *cmd_str++ = '"'; + /* + Reset the quotation flag, since now the status for + the right side of the expression will be saved here: + */ + quotation= false; + } + } + /* Copy equals symbol: */ + *cmd_str++ = '='; + arg++; + n--; + /* Let's deal with the left side of the expression: */ + while ((c = *arg_scan++) != 0) + { + /* + A whitespace or a single quote requires double + quotation marks: + */ + if (isspace(c) || c == '\'') + { + quotation= true; + } + /* + Double quotation mark or backslash symbol requires backslash + prefixing: + */ +#ifdef __WIN__ + else if (c == '"' || c == '\\') +#else + /* + The dollar symbol is used to substitute a variable, therefore + it also requires escaping: + */ + else if (c == '"' || c == '\\' || c == '$') +#endif + { + plain= false; + } + } + break; + } + /* + Double quotation mark or backslash symbol requires backslash + prefixing: + */ +#ifdef __WIN__ + else if (c == '"' || c == '\\') +#else + /* + The dollar symbol is used to substitute a variable, therefore + it also requires escaping: + */ + else if (c == '"' || c == '\\' || c == '$') +#endif + { + plain= false; + } + } + if (n) + { + /* Perhaps we need to quote the entire argument or its right part: */ + if (quotation) + { + *cmd_str++ = '"'; + } + /* + If there were no special characters inside, then we can use + the fast memcpy function: + */ + if (plain) + { + memcpy(cmd_str, arg, n * sizeof(char)); + cmd_str += n; + } + /* Otherwise we need to prefix individual characters: */ + else + { + while ((c = *arg++) != 0) + { +#ifdef __WIN__ + if (c == '"' || c == '\\') +#else + if (c == '"' || c == '\\' || c == '$') +#endif + { + *cmd_str++ = '\\'; + } + *cmd_str++ = c; + } + } + /* Perhaps we need to quote the entire argument or its right part: */ + if (quotation) + { + *cmd_str++ = '"'; + } + } } /* Add a terminating null character (not counted in the length, @@ -653,8 +922,6 @@ static ssize_t sst_prepare_other (const char* method, return -ENOMEM; } - const char* binlog_opt= ""; - const char* binlog_index_opt= ""; char* binlog_opt_val= NULL; char* binlog_index_opt_val= NULL; @@ -672,9 +939,6 @@ static ssize_t sst_prepare_other (const char* method, ret); } - if (strlen(binlog_opt_val)) binlog_opt= WSREP_SST_OPT_BINLOG; - if (strlen(binlog_index_opt_val)) binlog_index_opt= WSREP_SST_OPT_BINLOG_INDEX; - make_wsrep_defaults_file(); ret= snprintf (cmd_str(), cmd_len, @@ -682,14 +946,14 @@ static ssize_t sst_prepare_other (const char* method, WSREP_SST_OPT_ROLE " 'joiner' " WSREP_SST_OPT_ADDR " '%s' " WSREP_SST_OPT_DATA " '%s' " - " %s " + "%s" WSREP_SST_OPT_PARENT " '%d'" - " %s '%s'" - " %s '%s'", + "%s" + "%s", method, addr_in, mysql_real_data_home, wsrep_defaults_file, - (int)getpid(), binlog_opt, binlog_opt_val, - binlog_index_opt, binlog_index_opt_val); + (int)getpid(), + binlog_opt_val, binlog_index_opt_val); my_free(binlog_opt_val); my_free(binlog_index_opt_val); @@ -983,7 +1247,7 @@ static int sst_donate_mysqldump (const char* addr, WSREP_SST_OPT_PORT " '%u' " WSREP_SST_OPT_LPORT " '%u' " WSREP_SST_OPT_SOCKET " '%s' " - " %s " + "%s" WSREP_SST_OPT_GTID " '%s:%lld' " WSREP_SST_OPT_GTID_DOMAIN_ID " '%d'" "%s", @@ -1363,8 +1627,6 @@ static int sst_donate_other (const char* method, return -ENOMEM; } - const char* binlog_opt= ""; - const char* binlog_index_opt= ""; char* binlog_opt_val= NULL; char* binlog_index_opt_val= NULL; @@ -1381,9 +1643,6 @@ static int sst_donate_other (const char* method, ret); } - if (strlen(binlog_opt_val)) binlog_opt= WSREP_SST_OPT_BINLOG; - if (strlen(binlog_index_opt_val)) binlog_index_opt= WSREP_SST_OPT_BINLOG_INDEX; - make_wsrep_defaults_file(); std::ostringstream uuid_oss; @@ -1394,18 +1653,18 @@ static int sst_donate_other (const char* method, WSREP_SST_OPT_ADDR " '%s' " WSREP_SST_OPT_SOCKET " '%s' " WSREP_SST_OPT_DATA " '%s' " - " %s " - " %s '%s' " - " %s '%s' " + "%s" WSREP_SST_OPT_GTID " '%s:%lld' " WSREP_SST_OPT_GTID_DOMAIN_ID " '%d'" + "%s" + "%s" "%s", method, addr, mysqld_unix_port, mysql_real_data_home, wsrep_defaults_file, - binlog_opt, binlog_opt_val, - binlog_index_opt, binlog_index_opt_val, uuid_oss.str().c_str(), gtid.seqno().get(), wsrep_gtid_domain_id, + binlog_opt_val, binlog_index_opt_val, bypass ? " " WSREP_SST_OPT_BYPASS : ""); + my_free(binlog_opt_val); my_free(binlog_index_opt_val); diff --git a/storage/connect/ha_connect.cc b/storage/connect/ha_connect.cc index da3569ca30c..268cffed680 100644 --- a/storage/connect/ha_connect.cc +++ b/storage/connect/ha_connect.cc @@ -1315,7 +1315,7 @@ char *ha_connect::GetRealString(PCSZ s) { char *sv; - if (IsPartitioned() && s && partname && *partname) { + if (IsPartitioned() && s && *partname) { sv= (char*)PlugSubAlloc(xp->g, NULL, 0); sprintf(sv, s, partname); PlugSubAlloc(xp->g, NULL, strlen(sv) + 1); diff --git a/storage/innobase/fil/fil0fil.cc b/storage/innobase/fil/fil0fil.cc index 773af26426f..bc032ed6aa0 100644 --- a/storage/innobase/fil/fil0fil.cc +++ b/storage/innobase/fil/fil0fil.cc @@ -725,6 +725,7 @@ static void fil_flush_low(fil_space_t* space, bool metadata = false) switch (space->purpose) { case FIL_TYPE_TEMPORARY: ut_ad(0); // we already checked for this + /* fall through */ case FIL_TYPE_TABLESPACE: case FIL_TYPE_IMPORT: fil_n_pending_tablespace_flushes++; diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 0768aae9072..abe860fea86 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -220,6 +220,7 @@ extern my_bool srv_background_scrub_data_compressed; extern uint srv_background_scrub_data_interval; extern uint srv_background_scrub_data_check_interval; #ifdef UNIV_DEBUG +my_bool innodb_evict_tables_on_commit_debug; extern my_bool srv_scrub_force_testing; #endif @@ -19588,6 +19589,11 @@ static MYSQL_SYSVAR_BOOL(trx_purge_view_update_only_debug, " but the each purges were not done yet.", NULL, NULL, FALSE); +static MYSQL_SYSVAR_BOOL(evict_tables_on_commit_debug, + innodb_evict_tables_on_commit_debug, PLUGIN_VAR_OPCMDARG, + "On transaction commit, try to evict tables from the data dictionary cache.", + NULL, NULL, FALSE); + static MYSQL_SYSVAR_UINT(data_file_size_debug, srv_sys_space_size_debug, PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY, @@ -19953,6 +19959,7 @@ static struct st_mysql_sys_var* innobase_system_variables[]= { MYSQL_SYSVAR(trx_rseg_n_slots_debug), MYSQL_SYSVAR(limit_optimistic_insert_debug), MYSQL_SYSVAR(trx_purge_view_update_only_debug), + MYSQL_SYSVAR(evict_tables_on_commit_debug), MYSQL_SYSVAR(data_file_size_debug), MYSQL_SYSVAR(fil_make_page_dirty_debug), MYSQL_SYSVAR(saved_page_number_debug), diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc index 3d8f068b188..722d75960bb 100644 --- a/storage/innobase/handler/handler0alter.cc +++ b/storage/innobase/handler/handler0alter.cc @@ -3181,7 +3181,7 @@ innobase_col_to_mysql( case DATA_SYS: /* These column types should never be shipped to MySQL. */ ut_ad(0); - + /* fall through */ case DATA_FLOAT: case DATA_DOUBLE: case DATA_DECIMAL: diff --git a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h index fe1be626a23..851e6081f9c 100644 --- a/storage/innobase/include/srv0srv.h +++ b/storage/innobase/include/srv0srv.h @@ -3,7 +3,7 @@ Copyright (c) 1995, 2017, Oracle and/or its affiliates. All rights reserved. Copyright (c) 2008, 2009, Google Inc. Copyright (c) 2009, Percona Inc. -Copyright (c) 2013, 2018, MariaDB Corporation. +Copyright (c) 2013, 2019, MariaDB Corporation. Portions of this file contain modifications contributed and copyrighted by Google, Inc. Those modifications are gratefully acknowledged and are described @@ -533,6 +533,7 @@ extern my_bool srv_ibuf_disable_background_merge; #endif /* UNIV_DEBUG || UNIV_IBUF_DEBUG */ #ifdef UNIV_DEBUG +extern my_bool innodb_evict_tables_on_commit_debug; extern my_bool srv_sync_debug; extern my_bool srv_purge_view_update_only_debug; diff --git a/storage/innobase/include/trx0sys.h b/storage/innobase/include/trx0sys.h index 4175128217b..437459865fe 100644 --- a/storage/innobase/include/trx0sys.h +++ b/storage/innobase/include/trx0sys.h @@ -644,11 +644,8 @@ public: { mutex_enter(&element->mutex); lf_hash_search_unpin(pins); - trx= element->trx; - if (!trx); - else if (UNIV_UNLIKELY(trx_id != trx->id)) - trx= NULL; - else { + if ((trx= element->trx)) { + DBUG_ASSERT(trx_id == trx->id); if (do_ref_count) trx->reference(); ut_d(validate_element(trx)); diff --git a/storage/innobase/lock/lock0lock.cc b/storage/innobase/lock/lock0lock.cc index 21ff8097af7..aebbf5c1cab 100644 --- a/storage/innobase/lock/lock0lock.cc +++ b/storage/innobase/lock/lock0lock.cc @@ -1217,9 +1217,7 @@ lock_sec_rec_some_has_impl( /* Some transaction may have an implicit x-lock on the record only if the max trx id for the page >= min trx id for the trx list, or - database recovery is running. We do not write the changes of a page - max trx id to the log, and therefore during recovery, this value - for a page may be incorrect. */ + database recovery is running. */ if (max_trx_id < trx_sys.get_min_trx_id()) { @@ -6285,9 +6283,6 @@ lock_trx_release_locks( /*--------------------------------------*/ trx_mutex_enter(trx); trx->state = TRX_STATE_COMMITTED_IN_MEMORY; - /* Ensure that rw_trx_hash_t::find() will no longer find - this transaction. */ - trx->id = 0; trx_mutex_exit(trx); /*--------------------------------------*/ diff --git a/storage/innobase/mtr/mtr0mtr.cc b/storage/innobase/mtr/mtr0mtr.cc index 75e6b500c07..2c255478969 100644 --- a/storage/innobase/mtr/mtr0mtr.cc +++ b/storage/innobase/mtr/mtr0mtr.cc @@ -753,7 +753,7 @@ mtr_t::Command::prepare_write() switch (m_impl->m_log_mode) { case MTR_LOG_SHORT_INSERTS: ut_ad(0); - /* fall through (write no redo log) */ + /* fall through */ case MTR_LOG_NO_REDO: case MTR_LOG_NONE: ut_ad(m_impl->m_log.size() == 0); diff --git a/storage/innobase/row/row0log.cc b/storage/innobase/row/row0log.cc index 1a10657cf99..fb0a376dd34 100644 --- a/storage/innobase/row/row0log.cc +++ b/storage/innobase/row/row0log.cc @@ -1795,6 +1795,7 @@ row_log_table_apply_insert( break; default: ut_ad(0); + /* fall through */ case DB_INVALID_NULL: ut_ad(row == NULL); return(error); @@ -2082,6 +2083,7 @@ row_log_table_apply_update( break; default: ut_ad(0); + /* fall through */ case DB_INVALID_NULL: ut_ad(row == NULL); return(error); diff --git a/storage/innobase/row/row0uins.cc b/storage/innobase/row/row0uins.cc index cfed0804373..88dea6c7995 100644 --- a/storage/innobase/row/row0uins.cc +++ b/storage/innobase/row/row0uins.cc @@ -1,7 +1,7 @@ /***************************************************************************** Copyright (c) 1997, 2017, Oracle and/or its affiliates. All Rights Reserved. -Copyright (c) 2017, 2018, MariaDB Corporation. +Copyright (c) 2017, 2019, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software @@ -566,6 +566,7 @@ row_undo_ins( switch (node->rec_type) { default: ut_ad(!"wrong undo record type"); + /* fall through */ case TRX_UNDO_INSERT_REC: /* Skip the clustered index (the first index) */ node->index = dict_table_get_next_index(node->index); diff --git a/storage/innobase/row/row0upd.cc b/storage/innobase/row/row0upd.cc index 5266b467091..8b1ddfc36a6 100644 --- a/storage/innobase/row/row0upd.cc +++ b/storage/innobase/row/row0upd.cc @@ -987,21 +987,20 @@ row_upd_build_difference_binary( TABLE* mysql_table, dberr_t* error) { - upd_field_t* upd_field; ulint len; upd_t* update; ulint n_diff; - ulint i; ulint offsets_[REC_OFFS_NORMAL_SIZE]; - ulint n_fld = dtuple_get_n_fields(entry); - ulint n_v_fld = dtuple_get_n_v_fields(entry); + const ulint n_v_fld = dtuple_get_n_v_fields(entry); rec_offs_init(offsets_); /* This function is used only for a clustered index */ ut_a(dict_index_is_clust(index)); ut_ad(!index->table->skip_alter_undo); + ut_ad(entry->n_fields <= index->n_fields); + ut_ad(entry->n_fields >= index->n_core_fields); - update = upd_create(n_fld + n_v_fld, heap); + update = upd_create(index->n_fields + n_v_fld, heap); n_diff = 0; @@ -1012,7 +1011,7 @@ row_upd_build_difference_binary( ut_ad(rec_offs_validate(rec, index, offsets)); } - for (i = 0; i < n_fld; i++) { + for (ulint i = 0; i < entry->n_fields; i++) { const byte* data = rec_get_nth_cfield(rec, index, offsets, i, &len); const dfield_t* dfield = dtuple_get_nth_field(entry, i); @@ -1027,17 +1026,22 @@ row_upd_build_difference_binary( if (!dfield_is_ext(dfield) != !rec_offs_nth_extern(offsets, i) || !dfield_data_is_binary_equal(dfield, len, data)) { - - upd_field = upd_get_nth_field(update, n_diff); - - dfield_copy(&(upd_field->new_val), dfield); - - upd_field_set_field_no(upd_field, i, index); - - n_diff++; + upd_field_t* uf = upd_get_nth_field(update, n_diff++); + dfield_copy(&uf->new_val, dfield); + upd_field_set_field_no(uf, i, index); } } + for (ulint i = entry->n_fields; i < index->n_fields; i++) { + upd_field_t* uf = upd_get_nth_field(update, n_diff++); + const dict_col_t* col = dict_index_get_nth_col(index, i); + /* upd_create() zero-initialized uf */ + uf->new_val.data = const_cast<byte*>(col->instant_value(&len)); + uf->new_val.len = static_cast<unsigned>(len); + dict_col_copy_type(col, &uf->new_val.type); + upd_field_set_field_no(uf, i, index); + } + /* Check the virtual columns updates. Even if there is no non-virtual column (base columns) change, we will still need to build the indexed virtual column value so that undo log would log them ( @@ -1062,7 +1066,7 @@ row_upd_build_difference_binary( &mysql_table, &record, &vcol_storage); - for (i = 0; i < n_v_fld; i++) { + for (ulint i = 0; i < n_v_fld; i++) { const dict_v_col_t* col = dict_table_get_nth_v_col(index->table, i); @@ -1090,24 +1094,16 @@ row_upd_build_difference_binary( entry, i); if (!dfield_data_is_binary_equal( - dfield, vfield->len, - static_cast<byte*>(vfield->data))) { - upd_field = upd_get_nth_field(update, n_diff); - - upd_field->old_v_val = static_cast<dfield_t*>( - mem_heap_alloc( - heap, - sizeof *upd_field->old_v_val)); - - dfield_copy(upd_field->old_v_val, vfield); - - dfield_copy(&(upd_field->new_val), dfield); - - upd_field_set_v_field_no( - upd_field, i, index); - - n_diff++; - + dfield, vfield->len, + static_cast<byte*>(vfield->data))) { + upd_field_t* uf = upd_get_nth_field(update, + n_diff++); + uf->old_v_val = static_cast<dfield_t*>( + mem_heap_alloc(heap, + sizeof *uf->old_v_val)); + dfield_copy(uf->old_v_val, vfield); + dfield_copy(&uf->new_val, dfield); + upd_field_set_v_field_no(uf, i, index); } } diff --git a/storage/innobase/trx/trx0trx.cc b/storage/innobase/trx/trx0trx.cc index ab67e09b992..e6c4c1cd23b 100644 --- a/storage/innobase/trx/trx0trx.cc +++ b/storage/innobase/trx/trx0trx.cc @@ -497,6 +497,7 @@ trx_free_at_shutdown(trx_t *trx) transaction was never committed and therefore lock_trx_release() was not called. */ trx->lock.table_locks.clear(); + trx->id = 0; trx_free(trx); } @@ -1235,6 +1236,22 @@ trx_update_mod_tables_timestamp( const time_t now = time(NULL); trx_mod_tables_t::const_iterator end = trx->mod_tables.end(); +#ifdef UNIV_DEBUG +# if MYSQL_VERSION_ID >= 100405 +# define dict_sys_mutex dict_sys.mutex +# else +# define dict_sys_mutex dict_sys->mutex +# endif + + const bool preserve_tables = !innodb_evict_tables_on_commit_debug + || trx->is_recovered /* avoid trouble with XA recovery */ +# if 1 /* if dict_stats_exec_sql() were not playing dirty tricks */ + || mutex_own(&dict_sys_mutex) +# else /* this would be more proper way to do it */ + || trx->dict_operation_lock_mode || trx->dict_operation +# endif + ; +#endif for (trx_mod_tables_t::const_iterator it = trx->mod_tables.begin(); it != end; @@ -1248,7 +1265,27 @@ trx_update_mod_tables_timestamp( "garbage" in table->update_time is justified because protecting it with a latch here would be too performance intrusive. */ - it->first->update_time = now; + dict_table_t* table = it->first; + table->update_time = now; +#ifdef UNIV_DEBUG + if (preserve_tables || table->get_ref_count()) { + /* do not evict when committing DDL operations + or if some other transaction is holding the + table handle */ + continue; + } + /* recheck while holding the mutex that blocks + table->acquire() */ + mutex_enter(&dict_sys_mutex); + if (!table->get_ref_count()) { +# if MYSQL_VERSION_ID >= 100405 + dict_sys.remove(table, true); +# else + dict_table_remove_from_cache_low(table, true); +# endif + } + mutex_exit(&dict_sys_mutex); +#endif } trx->mod_tables.clear(); @@ -1333,10 +1370,8 @@ trx_commit_in_memory( trx_sys.deregister_rw(trx); } - /* trx->id will be cleared in lock_trx_release_locks(trx). */ - ut_ad(trx->read_only || !trx->rsegs.m_redo.rseg || trx->id); lock_trx_release_locks(trx); - ut_ad(trx->id == 0); + ut_ad(trx->read_only || !trx->rsegs.m_redo.rseg || trx->id); /* Remove the transaction from the list of active transactions now that it no longer holds any user locks. */ @@ -1356,6 +1391,8 @@ trx_commit_in_memory( UT_LIST_REMOVE(trx->lock.evicted_tables, table); dict_mem_table_free(table); } + + trx->id = 0; } ut_ad(!trx->rsegs.m_redo.undo); diff --git a/storage/perfschema/pfs_instr.cc b/storage/perfschema/pfs_instr.cc index b6736cf3102..f90c6475f22 100644 --- a/storage/perfschema/pfs_instr.cc +++ b/storage/perfschema/pfs_instr.cc @@ -1265,7 +1265,6 @@ find_or_create_file(PFS_thread *thread, PFS_file_class *klass, char dirbuffer[FN_REFLEN]; size_t dirlen; const char *normalized_filename; - int normalized_length; dirlen= dirname_length(safe_filename); if (dirlen == 0) @@ -1296,7 +1295,7 @@ find_or_create_file(PFS_thread *thread, PFS_file_class *klass, *buf_end= '\0'; normalized_filename= buffer; - normalized_length= (int)strlen(normalized_filename); + uint normalized_length= static_cast<uint>(strlen(normalized_filename)); PFS_file **entry; uint retry_count= 0; @@ -1345,7 +1344,7 @@ search: pfs->m_class= klass; pfs->m_enabled= klass->m_enabled && flag_global_instrumentation; pfs->m_timed= klass->m_timed; - strncpy(pfs->m_filename, normalized_filename, normalized_length); + strncpy(pfs->m_filename, normalized_filename, normalized_length + 1); pfs->m_filename[normalized_length]= '\0'; pfs->m_filename_length= normalized_length; pfs->m_file_stat.m_open_count= 1; diff --git a/storage/rocksdb/mysql-test/rocksdb/include/index_merge1.inc b/storage/rocksdb/mysql-test/rocksdb/include/index_merge1.inc index 91bcc68adb4..b5cf7bff763 100644 --- a/storage/rocksdb/mysql-test/rocksdb/include/index_merge1.inc +++ b/storage/rocksdb/mysql-test/rocksdb/include/index_merge1.inc @@ -58,6 +58,10 @@ while ($1) eval set @d=@d*2; dec $1; } +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} --enable_query_log analyze table t0; @@ -329,6 +333,10 @@ alter table t2 add index i321(key3, key2, key1); -- disable_query_log -- disable_result_log analyze table t2; +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} -- enable_result_log -- enable_query_log @@ -371,6 +379,10 @@ insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t4; -- enable_result_log -- enable_query_log @@ -397,6 +409,10 @@ insert into t1 select * from t0; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log @@ -463,6 +479,10 @@ update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t3; -- enable_result_log -- enable_query_log @@ -486,6 +506,10 @@ update t0 set key8=123 where key1 < 3 or key2 < 4; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t0; -- enable_result_log -- enable_query_log @@ -496,6 +520,10 @@ select * from t0 where key1 < 3 or key2 < 4; delete from t0 where key1 < 3 or key2 < 4; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t0; -- enable_result_log -- enable_query_log @@ -509,6 +537,10 @@ create table t4 (a int); insert into t4 values (1),(4),(3); -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t4; -- enable_result_log -- enable_query_log @@ -533,6 +565,10 @@ select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 update t0 set key1=1; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t0; -- enable_result_log -- enable_query_log @@ -556,6 +592,10 @@ update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t0; -- enable_result_log -- enable_query_log @@ -612,6 +652,10 @@ select count(*) from t1; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log @@ -652,6 +696,10 @@ create table t3 ( -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t0; analyze table t1; analyze table t2; @@ -733,6 +781,10 @@ insert into t2 select * from t1; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; analyze table t2; -- enable_result_log @@ -787,6 +839,10 @@ insert into t3 values (1,1,'data'); -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t0; analyze table t1; analyze table t2; @@ -822,6 +878,10 @@ INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log diff --git a/storage/rocksdb/mysql-test/rocksdb/include/index_merge2.inc b/storage/rocksdb/mysql-test/rocksdb/include/index_merge2.inc index 9fd2390c401..7e5cec40a80 100644 --- a/storage/rocksdb/mysql-test/rocksdb/include/index_merge2.inc +++ b/storage/rocksdb/mysql-test/rocksdb/include/index_merge2.inc @@ -43,6 +43,10 @@ while ($1) -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log @@ -68,6 +72,10 @@ alter table t1 add primary key (str1, zeroval, str2, str3); -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log @@ -101,6 +109,10 @@ while ($1) -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log @@ -143,6 +155,10 @@ select count(*) from t1; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t2; -- enable_result_log -- enable_query_log @@ -379,6 +395,10 @@ update t1 set key2=key1,key3=key1; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log @@ -417,6 +437,10 @@ INSERT INTO t1 VALUES -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log @@ -472,6 +496,10 @@ INSERT INTO t1 VALUES (1,1,'a'), (2,2,'b'); -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log diff --git a/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror.inc b/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror.inc index ed270802db1..21219d1aa95 100644 --- a/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror.inc +++ b/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror.inc @@ -119,6 +119,10 @@ select count(*) from t1; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t0; analyze table t1; -- enable_result_log @@ -141,6 +145,10 @@ insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4 -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log @@ -193,6 +201,10 @@ update t1 set key1=200,key2=200 where key1=100 and key2=100; delete from t1 where key1=200 and key2=200; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log @@ -210,6 +222,10 @@ delete from t1 where key3=100 and key4=100; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log @@ -236,6 +252,10 @@ insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log @@ -250,6 +270,10 @@ insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4') -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log @@ -264,6 +288,10 @@ insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3') -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log @@ -392,6 +420,10 @@ select count(a) from t2 where b='BBBBBBBB'; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t2; -- enable_result_log -- enable_query_log @@ -405,6 +437,10 @@ select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA'; insert into t2 values ('ab', 'ab', 'uh', 'oh'); -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t2; -- enable_result_log -- enable_query_log diff --git a/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror_cpk.inc b/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror_cpk.inc index 9f1e2d77621..f0d18a50bff 100644 --- a/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror_cpk.inc +++ b/storage/rocksdb/mysql-test/rocksdb/include/index_merge_ror_cpk.inc @@ -68,6 +68,10 @@ set autocommit=1; -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; -- enable_result_log -- enable_query_log @@ -159,6 +163,10 @@ WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f -- disable_query_log -- disable_result_log +if ($engine_type == RocksDB) +{ + set global rocksdb_force_flush_memtable_now=1; +} analyze table t1; analyze table t2; -- enable_result_log diff --git a/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb2.result b/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb2.result index e0248737381..b34ec78cd03 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb2.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb2.result @@ -23,10 +23,11 @@ INDEX i8(key8) ); analyze table t0; Table Op Msg_type Msg_text +test.t0 analyze status Engine-independent statistics collected test.t0 analyze status OK explain select * from t0 where key1 < 3 or key1 > 1020; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 range i1 i1 4 NULL 2 Using index condition; Using where +1 SIMPLE t0 range i1 i1 4 NULL 4 Using index condition explain select * from t0 where key1 < 3 or key2 > 1020; id select_type table type possible_keys key key_len ref rows Extra @@ -267,12 +268,12 @@ select * from t0,t1 where (t0.key1=t1.key1) and (t0.key1=3 or t0.key2<4) and t1.key1=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ref i1,i2 i1 4 const 2 Using where -1 SIMPLE t1 ref i1 i1 4 const 1 +1 SIMPLE t1 ref i1 i1 4 const 2 explain select * from t0,t1 where t0.key1 = 5 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ref i1 i1 4 const 1 -1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using union(i1,i8); Using where; Using join buffer (flat, BNL join) +1 SIMPLE t0 ref i1 i1 4 const 2 +1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 4 Using union(i1,i8); Using where; Using join buffer (flat, BNL join) explain select * from t0,t1 where t0.key1 < 3 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); id select_type table type possible_keys key key_len ref rows Extra @@ -286,7 +287,7 @@ id select_type table type possible_keys key key_len ref rows Extra NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge i1,i2,i8 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where +1 SIMPLE t1 index_merge i1,i2,i8 i1,i2 4,4 NULL 4 Using union(i1,i2); Using where create table t3 like t0; insert into t3 select * from t0; alter table t3 add key9 int not null, add index i9(key9); @@ -392,10 +393,10 @@ count(*) 8704 explain select * from t1 WHERE cola = 'foo' AND colb = 'bar'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL # Using intersect(cola,colb); Using where +1 SIMPLE t1 ref cola,colb cola 3 const # Using index condition; Using where explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL # Using intersect(cola,colb); Using where +1 SIMPLE t1 ref cola,colb cola 3 const # Using index condition; Using where drop table t1; CREATE TABLE t1(a INT); INSERT INTO t1 VALUES(1); @@ -478,7 +479,7 @@ a filler b must use union, not sort-union: explain select * from t2 where a=4 or b=4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index_merge a,b a,b 5,5 NULL # Using union(a,b); Using where +1 SIMPLE t2 ALL a,b NULL NULL NULL # Using where select * from t2 where a=4 or b=4; a filler b 4 4 0 @@ -603,7 +604,7 @@ count(*) 64801 explain select key1,key2 from t1 where key1=100 and key2=100; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge key1,key2 key2,key1 5,5 NULL # Using intersect(key2,key1); Using where; Using index +1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL # Using intersect(key1,key2); Using where; Using index select key1,key2 from t1 where key1=100 and key2=100; key1 key2 100 100 @@ -789,7 +790,7 @@ show warnings; Level Code Message explain select pk from t1 where key1 = 1 and key2 = 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref key1,key2 key1 5 const 2 Using where +1 SIMPLE t1 index_merge key1,key2 key2,key1 4,5 NULL 1 Using intersect(key2,key1); Using where; Using index select pk from t1 where key2 = 1 and key1 = 1; pk 26 @@ -826,6 +827,7 @@ insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) select key1a, key1b, key2a, key2b, key3a, key3b from t1; analyze table t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK select count(*) from t1; count(*) @@ -1057,7 +1059,7 @@ SELECT a FROM t1 WHERE c = 1 AND b = 1 AND d = 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c,bd c 5 const 2 Using where +1 SIMPLE t1 index_merge c,bd c,bd 5,10 NULL 1 Using intersect(c,bd); Using where; Using index CREATE TABLE t2 ( a INT ) SELECT a FROM t1 @@ -1288,7 +1290,7 @@ primary key (pk1, pk2) ); explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,key1 key1 12 NULL ROWS Using index condition +1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL ROWS Using where select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2 1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2 @@ -1321,16 +1323,16 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref key1 key1 4 const ROWS Using where explain select * from t1 where pk1 < 7500 and key1 = 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,key1 key1 8 NULL ROWS Using index condition +1 SIMPLE t1 range PRIMARY,key1 PRIMARY 4 NULL ROWS Using where explain select * from t1 where pktail1ok=1 and key1=10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge key1,pktail1ok key1,pktail1ok 4,4 NULL 1 Using intersect(key1,pktail1ok); Using where +1 SIMPLE t1 ref key1,pktail1ok key1 4 const 2 Using where explain select * from t1 where pktail2ok=1 and key1=10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge key1,pktail2ok key1,pktail2ok 4,4 NULL 1 Using intersect(key1,pktail2ok); Using where +1 SIMPLE t1 ref key1,pktail2ok key1 4 const 2 Using where explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok PRIMARY,key1 4,4 NULL ROWS Using union(PRIMARY,key1); Using where +1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL ROWS Using sort_union(pktail2ok,key1); Using where explain select * from t1 where pktail3bad=1 and key1=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref key1,pktail3bad EITHER_KEY 4 const ROWS Using where @@ -1393,7 +1395,7 @@ EXPLAIN SELECT t1.f1 FROM t1 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index -2 SUBQUERY t2 ref f2,f3 f2 5 const 1 Using where +2 SUBQUERY t2 index_merge f2,f3 f3,f2 2,5 NULL 1 Using intersect(f3,f2); Using where; Using index DROP TABLE t1,t2; set global rocksdb_force_flush_memtable_now=1; # @@ -1412,5 +1414,5 @@ INSERT INTO t1 SELECT id + 8, id2 + 8, id3 +8 FROM t1; INSERT INTO t1 SELECT id + 16, 7, 0 FROM t1; EXPLAIN SELECT SQL_NO_CACHE count(*) FROM t1 WHERE id2=7 AND id3=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref id2,id3,covering_index covering_index 8 const,const 1 Using index +1 SIMPLE t1 ref id2,id3,covering_index covering_index 8 const,const 2 Using index DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/allow_to_start_after_corruption.test b/storage/rocksdb/mysql-test/rocksdb/t/allow_to_start_after_corruption.test index 67b2d5f96d7..e084b57fbda 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/allow_to_start_after_corruption.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/allow_to_start_after_corruption.test @@ -1,6 +1,8 @@ --source include/have_rocksdb.inc --source include/not_valgrind.inc +--let $restart_noprint=2 + --echo # --echo # Test how MyRocks behaves when RocksDB reports corrupted data. --echo # diff --git a/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug.test b/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug.test index b17548063d9..99e28f3b55e 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/autoinc_debug.test @@ -3,6 +3,8 @@ --source include/have_log_bin.inc --source include/not_valgrind.inc +--let $restart_noprint=2 + --echo # --echo # Testing upgrading from server without merges for auto_increment --echo # to new server with such support. diff --git a/storage/rocksdb/mysql-test/rocksdb/t/disabled.def b/storage/rocksdb/mysql-test/rocksdb/t/disabled.def index 2fb1404219a..45d6767f656 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/disabled.def +++ b/storage/rocksdb/mysql-test/rocksdb/t/disabled.def @@ -85,8 +85,6 @@ rpl_row_triggers : Requires read-free slave. compact_deletes: MDEV-12663 : rocksdb.compact_deletes times out and causes other tests to fail blind_delete_without_tx_api: MDEV-12286: rocksdb.blind_delete_without_tx_api test fails -unique_check: wrong error number -autoinc_vars_thread: debug sync point wait timed out information_schema: MDEV-14372: unstable testcase ## @@ -94,8 +92,5 @@ information_schema: MDEV-14372: unstable testcase ## mysqlbinlog_gtid_skip_empty_trans_rocksdb : MariaRocks: requires GTIDs -autoinc_debug: Fails with wrong results drop_table: Hangs on shutdown -allow_to_start_after_corruption : result difference and assertion failure -index_merge_rocksdb2 : result difference rocksdb_range2 : result difference, update after MDEV-16746 is fixed diff --git a/storage/sphinx/ha_sphinx.cc b/storage/sphinx/ha_sphinx.cc index d720f016408..c8128b4fce9 100644 --- a/storage/sphinx/ha_sphinx.cc +++ b/storage/sphinx/ha_sphinx.cc @@ -2292,7 +2292,8 @@ int ha_sphinx::HandleMysqlError ( MYSQL * pConn, int iErrCode ) CSphSEThreadTable * pTable = GetTls (); if ( pTable ) { - strncpy ( pTable->m_tStats.m_sLastMessage, mysql_error ( pConn ), sizeof ( pTable->m_tStats.m_sLastMessage ) ); + strncpy ( pTable->m_tStats.m_sLastMessage, mysql_error ( pConn ), sizeof pTable->m_tStats.m_sLastMessage - 1 ); + pTable->m_tStats.m_sLastMessage[sizeof pTable->m_tStats.m_sLastMessage - 1] = '\0'; pTable->m_tStats.m_bLastError = true; } @@ -2559,7 +2560,8 @@ bool ha_sphinx::UnpackSchema () CSphSEThreadTable * pTable = GetTls (); if ( pTable ) { - strncpy ( pTable->m_tStats.m_sLastMessage, sMessage, sizeof(pTable->m_tStats.m_sLastMessage) ); + strncpy ( pTable->m_tStats.m_sLastMessage, sMessage, sizeof pTable->m_tStats.m_sLastMessage - 1 ); + pTable->m_tStats.m_sLastMessage[sizeof pTable->m_tStats.m_sLastMessage - 1] = '\0'; pTable->m_tStats.m_bLastError = ( uStatus==SEARCHD_ERROR ); } @@ -2987,7 +2989,8 @@ int ha_sphinx::index_read ( byte * buf, const byte * key, uint key_len, enum ha_ SPH_RET ( HA_ERR_END_OF_FILE ); } - strncpy ( pTable->m_tStats.m_sLastMessage, sMessage, sizeof(pTable->m_tStats.m_sLastMessage) ); + strncpy ( pTable->m_tStats.m_sLastMessage, sMessage, sizeof pTable->m_tStats.m_sLastMessage - 1 ); + pTable->m_tStats.m_sLastMessage[sizeof pTable->m_tStats.m_sLastMessage - 1] = '\0'; SafeDeleteArray ( sMessage ); if ( uRespStatus!=SEARCHD_WARNING ) diff --git a/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_master_tokudb.result b/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_master_tokudb.result index cca18c7c1ab..6982078d2b8 100644 --- a/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_master_tokudb.result +++ b/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_master_tokudb.result @@ -468,7 +468,7 @@ INSERT INTO t10 () VALUES(1,@b1,DEFAULT,'Kyle',DEFAULT), connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'double' to type 'char(5)'' +Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'double' to type 'char(5 octets) character set latin1'' *** Drop t10 *** connection master; @@ -510,7 +510,7 @@ INSERT INTO t11 () VALUES(1,@b1,'Testing is fun','Kyle',DEFAULT), connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'tinyblob' to type 'varchar(254)'' +Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'blob' to type 'varchar(254 octets) character set latin1'' *** Drop t11 *** connection master; diff --git a/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_slave_tokudb.result b/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_slave_tokudb.result index 8906cf31d74..318d5496255 100644 --- a/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_slave_tokudb.result +++ b/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_slave_tokudb.result @@ -64,7 +64,7 @@ a b c connection slave; START SLAVE; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t2' cannot be converted from type 'char(10)' to type 'char(5)'' +Last_SQL_Error = 'Column 2 of table 'test.t2' cannot be converted from type 'char(10 octets)' to type 'char(5 octets) character set latin1'' STOP SLAVE; RESET SLAVE; SELECT * FROM t2 ORDER BY a; @@ -102,7 +102,7 @@ INSERT INTO t3 () VALUES(@b1,2,'Kyle, TEX'),(@b1,1,'JOE AUSTIN'),(@b1,4,'QA TEST ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 0 of table 'test.t3' cannot be converted from type 'tinyblob' to type 'int(11)'' +Last_SQL_Error = 'Column 0 of table 'test.t3' cannot be converted from type 'blob' to type 'int(11)'' *** Drop t3 *** connection master; DROP TABLE t3; @@ -160,7 +160,7 @@ INSERT INTO t5 () VALUES(1,'Kyle',200.23,1,'b1b1',23.00098), ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'varchar(6)' to type 'char(5)'' +Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'varchar(6 octets)' to type 'char(5 octets) character set latin1'' *** Drop t5 *** connection master; DROP TABLE t5; @@ -188,7 +188,7 @@ INSERT INTO t6 () VALUES(1,'Kyle',200.23,1), ******************************************** connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'varchar(6)' to type 'char(5)'' +Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'varchar(6 octets)' to type 'char(5 octets) character set latin1'' *** Drop t6 *** include/rpl_reset.inc connection master; @@ -310,7 +310,7 @@ INSERT INTO t10 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'char(5)' to type 'double'' +Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'char(5 octets)' to type 'double'' *** Drop t10 *** connection master; DROP TABLE t10; @@ -338,7 +338,7 @@ INSERT INTO t11 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'varchar(254)' to type 'int(11)'' +Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'varchar(254 octets)' to type 'int(11)'' *** Drop t11 *** connection master; DROP TABLE t11; diff --git a/storage/tokudb/mysql-test/rpl/r/rpl_row_basic_3tokudb.result b/storage/tokudb/mysql-test/rpl/r/rpl_row_basic_3tokudb.result index 32a42143180..e638a1aab12 100644 --- a/storage/tokudb/mysql-test/rpl/r/rpl_row_basic_3tokudb.result +++ b/storage/tokudb/mysql-test/rpl/r/rpl_row_basic_3tokudb.result @@ -565,7 +565,7 @@ INSERT INTO t5 VALUES (1, "", 1); INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(255)' to type 'char(16)'' +Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(765 octets)' to type 'char(48 octets) character set utf8'' include/rpl_reset.inc [expecting slave to stop] connection master; @@ -573,7 +573,7 @@ INSERT INTO t6 VALUES (1, "", 1); INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(255)' to type 'char(128)'' +Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(765 octets)' to type 'char(384 octets) character set utf8'' include/rpl_reset.inc [expecting slave to replicate correctly] connection master; |